Tuesday, August 13, 2013
Monday, August 12, 2013
PS Database Tables Involved
OBJECTIVE OF THE DOCUMENT
The modules involved in logistics in SAP are tightly integrated between each other and works seamlessly in an organization. The module which aligns centrally to all the logistics modules in the planning, execution and management of capacities, resources and costs is Project System popularly called as PS. In the light of this, it is very much important for consultants to know various elements of the project and how these elements are related to each other. Knowing the project structure and its elements along with the database tables will help the consultants to devise customized reports for various scenarios suiting to individual projects.
This document contains
PROJECT
Project is at the highest level and WBS elements are networks are subordinates of the project. The costs are revenues at the network or WBS level will finally accrue as the cost and revenue of the project.
The database table which contains the relevant information about the project is PROJ. In this table the field PROJ-PSPNR represents the project number. There is also another field PROJ-OBJNR is an important field as this field serves as the foreign key for making any queries at the project level. This is the key field for finding the status of the project. The method to find out the status of the project is dealt in the later part of the document.
WBS ELEMENTS
A project can have many WBS elements assigned to it. The details pertaining to the WBS elements are available at the database table PRPS. The table has the field PRPS-PSPNR has the WBS element number. The project related to the WBS element can be obtained from the field PRPS-PSPHI. Hence using this field all the WBS elements assigned a project can be easily found in any custom report. The table PRPS also has got another field called PRPS-OBJNR which is the linking field to any other tables containing various information related to the WBS element. This field is the key field for finding the status of the WBS element. The method to find the status of WBS element using this field is dealt in the later part of this document.
A project can also contain various networks. Networks contain lot of activities which are linked to each other by means of different relationship types. The duration which each activity takes and lead time or lag time between two activities are also available in the network. Some of these activities are also earmarked as Milestones in the project and based on the Milestone completion; revenue generation can be initiates in Sales and Distribution by means of an invoice creation for the relevant Milestone using Milestone billing functionality. In a network enabled business scenario, all the details of the project like Schedule, cost, capacity and reservations will flow from network to the higher level structures like WBS element and project. Various features of the network and their underlying database tables are discussed below here.
NETWORK HEADER
The database table AUFK contains the network header related information. Some of the important fields available in this table are listed one by one as follows.
The field AUFK-AUFNR represents the network number created in the system.
The field AUFK-OBJNR is a field which will be used as a foreign key in case this table needs to be linked with some other table.
The field AUFK-PSPEL represents the WBS element which is linked to the network at the header level. However if the business scenario involves activities within the network, this field may not be of much use as each activity can have different WBS elements of the same project. This field assumes importance if the networks are used without activities.
In case of assembly processing, where a network is created from a sales order using standard project elements the fields AUFK-KDAUF and AUFK-KDPOS represents the sales order number and line item respectively which triggered the network. These fields are seen in the network header in the fields Sales Order Number and Sales Order Item in the Assignments tab.
There is also another table CAUFV which is similar to AUFK but CAUFV has some other additional information related to costing etc. The table CAUFV is very important because this table is the central table using which all the other network related tables are accessed. Some of the important fields in this table are discussed below.
The field CAUFV-AUFNR represents the network number which is same as AUFK-AUFNR.
The field CAUFV-AUFPL represents the connecting link between all the related tables. The field which is read as “Routing number of Operations in the Order” is the main field to be used for any join using this table.
NETWORK ACTIVITIES
The details pertaining to network activities can be obtained from the table AFVC. When a network number is known it should be passed as CAUFV-AUFNR and the resultant CAUFV-AUFPL and CAUFV-BEDID should be obtained.
Now the value of CAUFV-AUFPL should be passed as AFVC-AUFPL and AFVC-BEDID respectively. The resultant list gives the list of all the activities of the network.
Various fields and their importance are provided in detail below.
The field AFVC-VORNR refers to the activity number of the activity in the network. This number can serve as the foreign key in many of the tables associated with the network activities. However there are also other fields which can serve as foreign keys in different tables.
The field AFVC-APLZL refers to the counter number of the activity. This is an important field when the network relationships are to be found out.
There are various fields related to control key of the activity, activity description, plant, work center, calculation type etc which can be seen using this logic and hence those fields are not discussed in detail here.
The data pertaining to planned cost for primary cost activities can also be found in this table in the fields AFVC-PRKST for the planned cost and AFVC- SAKTO for the cost element against which the cost is planned.
There is also another table AFVCP where AFVCP-AUFPL = CAUFV-AUFPL using which the WBS element assigned to each of the activities can be found out.
NETWORK RELATIONSHIP
The details pertaining to the relationship of the network activities can be seen from the table AFAB. In this table following are the fields which are very important.
The field AFAB-AUFPL_VOR represents the CAUFV-AUFPL of the network where the preceding activity is located.
The field AFAB-APLZL_VOR represents the preceding activity counter number of the network. This value is same as the value of the field AFVC-APLZL
The modules involved in logistics in SAP are tightly integrated between each other and works seamlessly in an organization. The module which aligns centrally to all the logistics modules in the planning, execution and management of capacities, resources and costs is Project System popularly called as PS. In the light of this, it is very much important for consultants to know various elements of the project and how these elements are related to each other. Knowing the project structure and its elements along with the database tables will help the consultants to devise customized reports for various scenarios suiting to individual projects.
This document contains
- Database tables for each of the project elements
- Connecting link between various database tables
This document does not contain
- Detailed information about the elements of project though it is precisely mentioned for the purpose of understanding
- Costing related data as my knowledge level is very less in this area
- Detailed information about the module as such and the processes involved
- Transaction codes for all the project elements. However it is included wherever possible.
This document explains the logic only around the functionalities enabled in PS system, but not in any other modules like PP or PM. Even in those modules some of these tables may be used, but the logic mentioned here explains the usage, logic and joins between these tables only in the perspective of PS.
ELEMENTS OF PROJECT AND THEIR TABLES
A project is initiated with the intention of creating a unique purpose; it can be a product a service or combination of both.
In order to manage the project, the project is subdivided into various small deliverables called WBS elements or Work Breakdown Structures. Each such deliverables can be used to plan costs and revenues, resources and capacities.
In some cases, the projects are
ELEMENTS OF PROJECT AND THEIR TABLES
A project is initiated with the intention of creating a unique purpose; it can be a product a service or combination of both.
In order to manage the project, the project is subdivided into various small deliverables called WBS elements or Work Breakdown Structures. Each such deliverables can be used to plan costs and revenues, resources and capacities.
In some cases, the projects are
PROJECT
Project is at the highest level and WBS elements are networks are subordinates of the project. The costs are revenues at the network or WBS level will finally accrue as the cost and revenue of the project.
The database table which contains the relevant information about the project is PROJ. In this table the field PROJ-PSPNR represents the project number. There is also another field PROJ-OBJNR is an important field as this field serves as the foreign key for making any queries at the project level. This is the key field for finding the status of the project. The method to find out the status of the project is dealt in the later part of the document.
WBS ELEMENTS
A project can have many WBS elements assigned to it. The details pertaining to the WBS elements are available at the database table PRPS. The table has the field PRPS-PSPNR has the WBS element number. The project related to the WBS element can be obtained from the field PRPS-PSPHI. Hence using this field all the WBS elements assigned a project can be easily found in any custom report. The table PRPS also has got another field called PRPS-OBJNR which is the linking field to any other tables containing various information related to the WBS element. This field is the key field for finding the status of the WBS element. The method to find the status of WBS element using this field is dealt in the later part of this document.
A project can also contain various networks. Networks contain lot of activities which are linked to each other by means of different relationship types. The duration which each activity takes and lead time or lag time between two activities are also available in the network. Some of these activities are also earmarked as Milestones in the project and based on the Milestone completion; revenue generation can be initiates in Sales and Distribution by means of an invoice creation for the relevant Milestone using Milestone billing functionality. In a network enabled business scenario, all the details of the project like Schedule, cost, capacity and reservations will flow from network to the higher level structures like WBS element and project. Various features of the network and their underlying database tables are discussed below here.
NETWORK HEADER
The database table AUFK contains the network header related information. Some of the important fields available in this table are listed one by one as follows.
The field AUFK-AUFNR represents the network number created in the system.
The field AUFK-OBJNR is a field which will be used as a foreign key in case this table needs to be linked with some other table.
The field AUFK-PSPEL represents the WBS element which is linked to the network at the header level. However if the business scenario involves activities within the network, this field may not be of much use as each activity can have different WBS elements of the same project. This field assumes importance if the networks are used without activities.
In case of assembly processing, where a network is created from a sales order using standard project elements the fields AUFK-KDAUF and AUFK-KDPOS represents the sales order number and line item respectively which triggered the network. These fields are seen in the network header in the fields Sales Order Number and Sales Order Item in the Assignments tab.
There is also another table CAUFV which is similar to AUFK but CAUFV has some other additional information related to costing etc. The table CAUFV is very important because this table is the central table using which all the other network related tables are accessed. Some of the important fields in this table are discussed below.
The field CAUFV-AUFNR represents the network number which is same as AUFK-AUFNR.
The field CAUFV-AUFPL represents the connecting link between all the related tables. The field which is read as “Routing number of Operations in the Order” is the main field to be used for any join using this table.
NETWORK ACTIVITIES
The details pertaining to network activities can be obtained from the table AFVC. When a network number is known it should be passed as CAUFV-AUFNR and the resultant CAUFV-AUFPL and CAUFV-BEDID should be obtained.
Now the value of CAUFV-AUFPL should be passed as AFVC-AUFPL and AFVC-BEDID respectively. The resultant list gives the list of all the activities of the network.
Various fields and their importance are provided in detail below.
The field AFVC-VORNR refers to the activity number of the activity in the network. This number can serve as the foreign key in many of the tables associated with the network activities. However there are also other fields which can serve as foreign keys in different tables.
The field AFVC-APLZL refers to the counter number of the activity. This is an important field when the network relationships are to be found out.
There are various fields related to control key of the activity, activity description, plant, work center, calculation type etc which can be seen using this logic and hence those fields are not discussed in detail here.
The data pertaining to planned cost for primary cost activities can also be found in this table in the fields AFVC-PRKST for the planned cost and AFVC- SAKTO for the cost element against which the cost is planned.
There is also another table AFVCP where AFVCP-AUFPL = CAUFV-AUFPL using which the WBS element assigned to each of the activities can be found out.
NETWORK RELATIONSHIP
The details pertaining to the relationship of the network activities can be seen from the table AFAB. In this table following are the fields which are very important.
The field AFAB-AUFPL_VOR represents the CAUFV-AUFPL of the network where the preceding activity is located.
The field AFAB-APLZL_VOR represents the preceding activity counter number of the network. This value is same as the value of the field AFVC-APLZL
The field AFAB-AUFPL_NCH represents the network number of the succeeding activity.
The field AFAB-APLZL_NCH represents the counter number of the network which is same as the value of the field AFVC-APLZL.
If the relationship of all the activities are within a single network without involvement of any other sub network like a task list or maintenance order etc, then the field values will be same for both AFAB-AUFPL_VOR and AFAB-AUFPL_NCH. In such a scenario the value of the fields AFAB-AUFPL_VOR and AFAB-AUFPL_NCH will be same. In a case where the activities of one network are related to activities of some other network, then these fields will have different value. Hence if the relationship of activities in a network needs to be found, then it is necessary to give the condition that CAUFV-AUFPL should be in either AFAB-AUFPL_VOR or (not and) in the field AFAB-AUFPL_NCH.
NETWORK MILESTONES
Some of the network activities can be maintained as milestones and the list of milestones can be obtained from the database table MLST. In order to get the information of all the milestones of the network the table CAUFV and MLST can be used.
The field CAUFV-AUFPL can be given as an input in MLST-AUFPL and the resultant list provides the list of milestones of the network.
The field MLST_ZAEHL is the Milestone number. This field can be used to get information about milestones from different database tables. For all those purposes, this field can be used as the foreign key.
The field MLST- MLSTN provides the Milestone usage.
If the MS is confirmed then the confirmation number can be obtained from the field MLST- RUECK.
The table also has information related to Trend analysis, Progress analysis, Milestone functions etc which can be analyzed in detail. As the key fields are already mentioned, it should be easy to get all the other necessary details used this table.
NETWORK MILESTONE CONFIRMATION DETAILS
The status of the confirmed milestones of a network can be found from the table AFRU. Some of the important fields in this table are discussed below.
The field AFRU-AUFPL represents the link with all the other tables of the network where this field equals to the field CAUFV-AUFPL or AFVC-AUFPL or MLST-AUFPL. Using this table AFRU can be accessed.
The field AFRU-VORNR represents the activity number whose milestone is confirmed
The field AFRU-APLZL represents the counter number of the activity in the network. This is nothing but the AFVC-APLZL number.
The field AFRU- RUECK represents the confirmation number. This field is available both in AFRU and also in MLST. However MLST gives the information that the milestone is confirmed but AFRU also says whether the confirmation is a final confirmation or not in the field AFRU- AUERU.
Once this information is known, any other confirmation related information can be extracted either from the table MLST or from AFRU based on requirement.
NETWORK MATERIAL COMPONENTS
The details pertaining to material components and their assigned transactions can be seen in the tables RESB, AFWD and AFWI.
The field AFAB-APLZL_NCH represents the counter number of the network which is same as the value of the field AFVC-APLZL.
If the relationship of all the activities are within a single network without involvement of any other sub network like a task list or maintenance order etc, then the field values will be same for both AFAB-AUFPL_VOR and AFAB-AUFPL_NCH. In such a scenario the value of the fields AFAB-AUFPL_VOR and AFAB-AUFPL_NCH will be same. In a case where the activities of one network are related to activities of some other network, then these fields will have different value. Hence if the relationship of activities in a network needs to be found, then it is necessary to give the condition that CAUFV-AUFPL should be in either AFAB-AUFPL_VOR or (not and) in the field AFAB-AUFPL_NCH.
NETWORK MILESTONES
Some of the network activities can be maintained as milestones and the list of milestones can be obtained from the database table MLST. In order to get the information of all the milestones of the network the table CAUFV and MLST can be used.
The field CAUFV-AUFPL can be given as an input in MLST-AUFPL and the resultant list provides the list of milestones of the network.
The field MLST_ZAEHL is the Milestone number. This field can be used to get information about milestones from different database tables. For all those purposes, this field can be used as the foreign key.
The field MLST- MLSTN provides the Milestone usage.
If the MS is confirmed then the confirmation number can be obtained from the field MLST- RUECK.
The table also has information related to Trend analysis, Progress analysis, Milestone functions etc which can be analyzed in detail. As the key fields are already mentioned, it should be easy to get all the other necessary details used this table.
NETWORK MILESTONE CONFIRMATION DETAILS
The status of the confirmed milestones of a network can be found from the table AFRU. Some of the important fields in this table are discussed below.
The field AFRU-AUFPL represents the link with all the other tables of the network where this field equals to the field CAUFV-AUFPL or AFVC-AUFPL or MLST-AUFPL. Using this table AFRU can be accessed.
The field AFRU-VORNR represents the activity number whose milestone is confirmed
The field AFRU-APLZL represents the counter number of the activity in the network. This is nothing but the AFVC-APLZL number.
The field AFRU- RUECK represents the confirmation number. This field is available both in AFRU and also in MLST. However MLST gives the information that the milestone is confirmed but AFRU also says whether the confirmation is a final confirmation or not in the field AFRU- AUERU.
Once this information is known, any other confirmation related information can be extracted either from the table MLST or from AFRU based on requirement.
NETWORK MATERIAL COMPONENTS
The details pertaining to material components and their assigned transactions can be seen in the tables RESB, AFWD and AFWI.
Some key fields of the table RESB are first explained.
The field RESB- AUFNR represents the network number which triggered the reservation. As this table is the central table for reservation happening from various other transactions like sales order, planned order etc, different fields will be updated during different transactions. If the reservation is triggered through the network, then this field will be filled. For items which do not create reservations from material components, this table is not valid.
The field RESB- RSNUM represents the reservation number and this can be used to find out all the reservation related data.
There are other important fields like requirement quantity and requirement date which can be easily found once this key field is known.
Similarly the tables AFWD and AFWI deals with the material movements associated with the confirmation of milestones. Because of my limited knowledge on these tables, I am just mentioning these tables and leave the rest for exploration.
STATUS OF THE PROJECT ELEMENTS
There are two tables related to the status of any transaction provided the transaction is enabled for statuses using a status profile. They are JCDS and JEST. There are also two other tables TJ02 and TJ30 and these tables are also discussed here.
Any transaction with the status profiles enabled will get updated in tables which will definitely have a field called OBJNR. For example the tables relevant for project, WBS element and network namely PROJ, PRPS and AUFK have this field OBJNR. This field forms the basis for finding out the correct status.
In other modules transactions include sales order, production order, planned order, maintenance order, task list etc will also have the object number. In sales order object number will be available for both sales order header and item. Accordingly the statuses can be seen both at sales order header and at item level. Similarly each transaction has its own structure and their respective object number.
The table JCDS shows the change history of the status. The relevant object number OBJNR should be given as the input here and the output should be received.
Here the field JCDS- STAT represents the status. Any status with the prefix E is the user status and the one with the prefix I is the system status.
In order to get the correct system, the value from this field should be passed on into the field TJ02-ISTAT to get the system status.
For user status the field TJ30- ESTAT should be filled with the value of the field JCDS-STAT to know the actual user status. However my knowledge in this table is very limited and there seems to be some more logic involved to find out the correct status. That part is left for exploration. I will update this logic later if I can find this.
The table JEST shows the current status of the object. The same logic should be used as explained earlier.
The fool proof logic for status identification still needs to be explored.
PROJECT VERSIONS
The database table pertaining to the version of the project elements follows the syntax VS<DATABASE TABLE OF THE OBJECT>_CN. Using this logic the project versions of all the objects can be found out without any problem.
CONCLUSION
This document still can be evolved by providing information on the logic for getting the information pertaining to actual costs, actual revenues etc. As of now, I am in the process of trying to standardize the logic for getting these elements and the same will be updated whenever it is ready. For now, let us all make use of this document and enjoy the world of PS ing in SAP.
The field RESB- AUFNR represents the network number which triggered the reservation. As this table is the central table for reservation happening from various other transactions like sales order, planned order etc, different fields will be updated during different transactions. If the reservation is triggered through the network, then this field will be filled. For items which do not create reservations from material components, this table is not valid.
The field RESB- RSNUM represents the reservation number and this can be used to find out all the reservation related data.
There are other important fields like requirement quantity and requirement date which can be easily found once this key field is known.
Similarly the tables AFWD and AFWI deals with the material movements associated with the confirmation of milestones. Because of my limited knowledge on these tables, I am just mentioning these tables and leave the rest for exploration.
STATUS OF THE PROJECT ELEMENTS
There are two tables related to the status of any transaction provided the transaction is enabled for statuses using a status profile. They are JCDS and JEST. There are also two other tables TJ02 and TJ30 and these tables are also discussed here.
Any transaction with the status profiles enabled will get updated in tables which will definitely have a field called OBJNR. For example the tables relevant for project, WBS element and network namely PROJ, PRPS and AUFK have this field OBJNR. This field forms the basis for finding out the correct status.
In other modules transactions include sales order, production order, planned order, maintenance order, task list etc will also have the object number. In sales order object number will be available for both sales order header and item. Accordingly the statuses can be seen both at sales order header and at item level. Similarly each transaction has its own structure and their respective object number.
The table JCDS shows the change history of the status. The relevant object number OBJNR should be given as the input here and the output should be received.
Here the field JCDS- STAT represents the status. Any status with the prefix E is the user status and the one with the prefix I is the system status.
In order to get the correct system, the value from this field should be passed on into the field TJ02-ISTAT to get the system status.
For user status the field TJ30- ESTAT should be filled with the value of the field JCDS-STAT to know the actual user status. However my knowledge in this table is very limited and there seems to be some more logic involved to find out the correct status. That part is left for exploration. I will update this logic later if I can find this.
The table JEST shows the current status of the object. The same logic should be used as explained earlier.
The fool proof logic for status identification still needs to be explored.
PROJECT VERSIONS
The database table pertaining to the version of the project elements follows the syntax VS<DATABASE TABLE OF THE OBJECT>_CN. Using this logic the project versions of all the objects can be found out without any problem.
CONCLUSION
This document still can be evolved by providing information on the logic for getting the information pertaining to actual costs, actual revenues etc. As of now, I am in the process of trying to standardize the logic for getting these elements and the same will be updated whenever it is ready. For now, let us all make use of this document and enjoy the world of PS ing in SAP.
Subscribe to:
Posts (Atom)