Your Production Planning Data Template
Your Production Planning Data Template
- Recommended attributes to collect
- Key activities to track
- Practical extraction guidance
Production Planning Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Name ActivityName | The name of the business or system event that occurred at a specific point in time for a production order. | ||
| Description The Activity Name describes a specific step or status change within the production planning process. These events are extracted from change logs, status updates, and transaction records within SAP ECC PP, representing key milestones like 'Production Order Created', 'Material Availability Checked', or 'Goods Receipt Posted'. This attribute forms the backbone of the process map, allowing analysts to visualize the sequence of events, discover process variants, and identify bottlenecks or rework loops. The clarity and granularity of activity names are critical for building an accurate and insightful process model that reflects the reality of operations. Why it matters It defines the steps in the process map, making it possible to visualize, analyze, and understand the production workflow. Where to get Generated from various sources, including status changes in tables JEST/JSTO, transaction logs, and change documents in CDHDR/CDPOS. Examples Production Order ReleasedGoods Issue PostedFinal Confirmation RecordedOrder Technically Completed | |||
| Event Time EventTime | The precise timestamp indicating when the activity occurred. | ||
| Description Event Time records the date and time that a specific activity was executed or a status was logged in the system. This timestamp is fundamental for all time-based process analysis, including calculating cycle times, waiting times, and overall process duration. Accurate timestamps are crucial for sequencing events correctly and for performance analysis. They allow analysts to measure the duration between activities, identify delays, and evaluate performance against schedules or service level agreements. In the context of production planning, this helps in understanding how long each stage of planning and execution takes. Why it matters This attribute provides the chronological sequence of events, which is essential for calculating durations, analyzing performance, and identifying bottlenecks. Where to get Derived from various date and time fields associated with transactions and status changes, such as BUDAT (Posting Date) from material documents or change timestamps from CDHDR. Examples 2023-04-15T10:05:30Z2023-04-15T14:22:00Z2023-04-18T08:59:11Z2023-04-20T16:45:00Z | |||
| Production Order ProductionOrder | The unique identifier for a production order, which serves as the primary case identifier for the production planning and execution process. | ||
| Description The Production Order number is a unique alphanumeric key assigned to each manufacturing order in SAP ECC PP. It acts as the central object that links all related activities, from creation and planning to execution and final settlement. In process mining, this attribute is essential for grouping all events belonging to a single production run. This allows for an end-to-end analysis of the entire lifecycle of a production order, including tracking its progress, measuring cycle times, and identifying any deviations from the standard process. Analyzing processes by Production Order provides a clear view of how individual manufacturing requests are fulfilled. Why it matters This is the core identifier that connects all related process events, enabling the reconstruction and analysis of each production journey from start to finish. Where to get This attribute can be found in the SAP table AUFK, field AUFNR. Examples 1000345100034610003471000348 | |||
| Last Data Update LastDataUpdate | The timestamp indicating when the data for this event was last refreshed or extracted. | ||
| Description This attribute records the date and time the data was pulled from the source system into the process mining tool. It does not represent a business event but is a technical timestamp for data management purposes. Its primary use is to ensure data freshness and to help administrators and analysts understand the recency of the data they are analyzing. It is critical for validating that the process analysis is based on up-to-date information, especially when monitoring ongoing operations. Why it matters It confirms the freshness of the data, ensuring that analyses are based on the most current information available. Where to get This value is typically generated and stamped by the data extraction, transformation, and loading (ETL) tool or script during the data ingestion process. Examples 2023-10-27T02:00:00Z2023-10-28T02:00:00Z2023-10-29T02:00:00Z | |||
| Source System SourceSystem | Identifies the specific system from which the data was extracted. | ||
| Description This attribute specifies the originating system of the record, such as the specific SAP ECC instance name or ID. In organizations with multiple ERP systems or a mix of legacy and modern platforms, this field is crucial for data lineage and context. For analysis, it allows for filtering processes based on their system of origin. This can be useful for comparing process performance across different systems or for isolating data during multi-system data consolidation projects. Why it matters It provides crucial context about data origin, especially in environments with multiple SAP instances or integrated systems. Where to get Typically added during the data extraction process, often derived from the SAP system ID (SY-SYSID). Examples ECC_PROD_100S4H_FIN_200ECC_DE | |||
| Actual End Date ActualEndDate | The timestamp of the final production activity, marking the actual completion of the order. | ||
| Description The Actual End Date is the timestamp of the event that signifies the completion of all production activities for an order. This is commonly derived from the timestamp of the 'Final Confirmation Recorded' or the last 'Goods Receipt Posted' event. This attribute is essential for measuring the total production cycle time and for schedule adherence analysis. Comparing the 'Actual End Date' with the 'Planned End Date' determines if an order was completed on time. Analyzing the duration between the 'Actual Start Date' and 'Actual End Date' yields the true production throughput time. Why it matters It marks the completion of production, allowing for accurate measurement of on-time delivery performance and total execution time. Where to get Derived from the event log by finding the maximum timestamp of key completion activities like 'Final Confirmation Recorded' or 'Goods Receipt Posted'. Examples 2023-05-14T16:00:00Z2023-06-29T11:30:00Z2023-07-05T17:00:00Z | |||
| Actual Start Date ActualStartDate | The timestamp of the first recorded production activity, marking the actual start of execution. | ||
| Description The Actual Start Date is the timestamp of the first event that signifies the beginning of physical production work. This is typically derived from the first production confirmation event or the first goods issue of raw materials to the order. This derived attribute is crucial for schedule adherence analysis. It provides the actual start point to compare against the 'Planned Start Date', enabling the precise calculation of start-time variances. Understanding the gap between planned and actual start dates helps pinpoint pre-production delays related to material staging, machine setup, or resource availability. Why it matters It provides the true starting point of production execution, enabling accurate calculation of start delays and overall production lead time. Where to get Derived from the event log by finding the minimum timestamp of specific execution activities like 'First Confirmation Recorded' or 'Goods Issue Posted' for each production order. Examples 2023-05-11T08:30:00Z2023-06-22T14:00:00Z2023-07-02T09:15:00Z | |||
| Delivered Quantity DeliveredQuantity | The actual quantity of the product that has been produced and received into inventory. | ||
| Description Delivered Quantity, also known as Goods Receipt Quantity, is the total amount of finished product that has been confirmed as produced for a given production order. This value is updated when 'Goods Receipt Posted' events occur. This attribute is the counterpart to 'Planned Quantity' and is essential for calculating the 'Planned vs. Actual Quantity Variance' KPI. It provides a definitive measure of production output, allowing analysts to identify discrepancies that may be caused by scrap, yield loss, or other production issues. Comparing delivered quantities across plants, products, or time periods highlights areas with efficiency problems. Why it matters It measures the actual output of the production process, enabling direct comparison with planned targets to assess performance. Where to get Found in the SAP table AFPO, field WEMNG (Quantity of Goods Received). Examples 984950751255 | |||
| Planned Quantity PlannedQuantity | The total quantity of the product scheduled to be produced in the order. | ||
| Description Planned Quantity represents the target output of a production order as defined during the planning phase. This value serves as the baseline against which actual production output is measured. This attribute is fundamental for KPIs related to production performance and schedule adherence, such as 'Planned vs. Actual Quantity Variance'. It helps in assessing the accuracy of production planning and identifying systemic issues related to overproduction or underproduction. Analyzing this value is key to understanding how well production execution aligns with initial plans. Why it matters This attribute is the baseline for measuring production output variance and assessing planning accuracy. Where to get This value is available in the SAP table AFKO, field GAMNG (Total Order Quantity). Examples 1005000751250 | |||
| Product Code ProductCode | The unique identifier for the material or product being manufactured. | ||
| Description The Product Code, or Material Number, identifies the item that the production order is intended to produce. This is a central master data element that links production planning to inventory management, sales, and costing. In process mining, filtering or segmenting by Product Code is critical for understanding product-specific manufacturing processes. It helps answer questions like, 'Which products have the longest cycle times?' or 'Are certain products more prone to quality issues or material delays?'. This analysis is vital for product portfolio management and identifying opportunities for process standardization or optimization for specific material types. Why it matters It allows for product-specific process analysis, revealing which materials are associated with process inefficiencies, delays, or high costs. Where to get This can be found in the SAP table AFPO, field MATNR. Examples FG-123RM-45AHALB-7890CHEM-22 | |||
| Production Plant ProductionPlant | The manufacturing facility or location where the production order is executed. | ||
| Description The Production Plant is a key organizational unit in SAP that represents a manufacturing facility. It is assigned to each production order and dictates where the production activities will physically take place. Analyzing the process by Production Plant allows for performance comparisons between different locations. This helps identify which plants are more efficient, which ones face more delays or material shortages, and where best practices can be shared. It is a fundamental dimension for root cause analysis in dashboards related to throughput and resource utilization. Why it matters It enables performance comparison across different manufacturing locations, helping to identify site-specific issues or best practices. Where to get This attribute is stored in the SAP table AUFK, field WERKS. Examples 100017102000DE01 | |||
| Cycle Time CycleTime | The total time elapsed from the release of the production order to its final completion. | ||
| Description Cycle Time is a key performance indicator that measures the duration of the production execution phase. It is calculated as the time difference between the 'Production Order Released' event and a completion event like 'Final Confirmation Recorded' or 'Goods Receipt Posted'. This calculated metric is fundamental for assessing manufacturing efficiency. Analyzing cycle time trends and comparing them across products, plants, or time periods helps identify opportunities to reduce lead times, improve throughput, and increase overall production capacity. It is a core metric for many of the required dashboards and KPIs. Why it matters It directly measures the efficiency and speed of the production execution process, highlighting opportunities for lead time reduction. Where to get Calculated field: Timestamp of 'Production Order Completed' minus the timestamp of 'Production Order Released'. Examples 3 days 4 hours10 hours 15 minutes7 days 0 hours1 day 12 hours | |||
| Material Availability MaterialAvailabilityStatus | Indicates whether all required components for the production order are available. | ||
| Description Material Availability Status is a critical indicator of production readiness. It is determined by the system checking the availability of all component materials required for the order. A status of 'Available' means production can proceed without material-related delays, while 'Shortage' indicates a problem. This attribute is essential for the 'Material Shortage Impact Analysis' dashboard. By analyzing the time orders spend waiting with a material shortage, businesses can quantify the impact of supply chain issues on production schedules. This helps in identifying unreliable suppliers or problematic components that frequently cause delays. Why it matters It directly highlights delays caused by material shortages, which is a common and significant root cause of production schedule deviations. Where to get This is not a single stored field. It's the result of an availability check (ATP check). The results are often visible in transaction CO02 or in the collective availability check COHV. The status itself (e.g., MACM - Material committed) might be logged. Consult SAP ECC PP documentation. Examples AvailableShortagePartially AvailableNot Checked | |||
| Planned End Date PlannedEndDate | The scheduled date by which production for the order is planned to be completed. | ||
| Description The Planned End Date is the target completion date for a production order, established during the scheduling process. It defines the expected timeframe for finishing all production activities, including final confirmations and goods receipt. This date is essential for calculating the 'Schedule Adherence Rate' KPI. Comparing the 'Planned End Date' with the actual completion time (e.g., the timestamp of the 'Goods Receipt Posted' or 'Final Confirmation Recorded' activity) reveals whether orders are being finished on time. This analysis helps in evaluating the reliability of production schedules and identifying the root causes of late deliveries. Why it matters It is the benchmark used to determine if production orders are completed on time, directly impacting customer satisfaction and planning reliability. Where to get This is the Basic finish date, found in SAP table AFKO, field GLTRP. Examples 2023-05-152023-06-282023-07-05 | |||
| Planned Start Date PlannedStartDate | The scheduled date on which production for the order is planned to begin. | ||
| Description The Planned Start Date is a key scheduling parameter determined during the production planning and scheduling phase. It represents the target start date for production activities to commence for an order. This attribute is crucial for measuring schedule adherence. By comparing the 'Planned Start Date' with the actual start time of production (e.g., the timestamp of the 'First Confirmation Recorded' activity), organizations can identify delays in starting production. Analyzing the reasons for these delays is a primary use case for process mining in this area. Why it matters It serves as the baseline for measuring schedule adherence and identifying delays in the start of production execution. Where to get This is the Basic start date, found in SAP table AFKO, field GSTRP. Examples 2023-05-102023-06-222023-07-01 | |||
| Planner Group PlannerGroup | The group of MRP controllers or production planners responsible for the order. | ||
| Description The Planner Group is an organizational element that represents a group of individuals responsible for planning and managing a specific set of materials or production orders. Assigning orders to planner groups helps in distributing workload and establishing clear ownership. In process mining, this attribute allows for performance analysis based on the responsible planning team. It can help answer questions such as 'Which planner group experiences the most schedule deviations?' or 'Is there a difference in cycle times for orders managed by different groups?'. This provides insights for workload balancing and identifying training needs. Why it matters It allows for performance analysis to be segmented by the responsible team, helping to identify differences in planning effectiveness and workload. Where to get This is the MRP Controller, found in SAP table AFKO, field DISPO. Examples 001002P01P02 | |||
| Production Order Status ProductionOrderStatus | The current processing status of the production order, such as Created, Released, or Technically Completed. | ||
| Description The Production Order Status indicates the current stage of the order in its lifecycle. SAP uses a system of status codes to manage the progression of the order, for example, CRTD (Created), REL (Released), CNF (Confirmed), DLV (Delivered), and TECO (Technically Completed). Analyzing the time spent in different statuses is a core part of process mining for production. It helps identify how long orders wait for release after creation or how long they remain in a partially delivered state. Tracking status changes as activities provides a high-level view of the process flow. Why it matters It provides a snapshot of an order's progress and helps identify bottlenecks where orders get stuck for extended periods. Where to get Status information is stored in tables JEST and JSTO, linked to the order's object number (AUFK-OBJNR). Status text is in TJ02T. Reading status is often done via function modules like STATUS_TEXT_EDIT. Examples CRTD (Created)REL (Released)DLV (Delivered)TECO (Technically Completed) | |||
| Production Priority ProductionPriority | A classification indicating the urgency or priority of the production order. | ||
| Description Production Priority is a field used to signal the relative importance of a production order. This allows production schedulers and shop floor personnel to prioritize their work, ensuring that critical orders are expedited through the process. Analyzing this attribute helps evaluate the effectiveness of the priority system. By comparing the cycle times of high-priority orders to low-priority ones, a business can determine if urgent orders are actually being processed faster. If not, it may indicate issues with communication, resource allocation, or the overall execution process. Why it matters It helps assess whether the priority management system is effective, ensuring that high-urgency orders are completed faster than standard ones. Where to get This is often not a standard field on the production order header. It may be a custom field, derived from the sales order priority, or part of a classification system. Consult SAP ECC PP documentation or system configuration. Examples HighMediumLowRush | |||
| Quantity Variance QuantityVariance | The difference between the planned production quantity and the actual delivered quantity. | ||
| Description Quantity Variance is a calculated metric that measures the deviation of the production output from the initial plan. It is calculated as 'Delivered Quantity' minus 'Planned Quantity'. A positive variance indicates overproduction, while a negative variance indicates underproduction or yield loss. This attribute is essential for the 'Planned vs. Actual Production Output' dashboard and the corresponding KPI. Analyzing the variance helps to identify issues related to production yield, scrap rates, or data entry errors. It provides a direct measure of the reliability of the production process in meeting its output targets. Why it matters It quantifies the accuracy of production output against plans, highlighting potential issues with yield, scrap, or process control. Where to get Calculated field: 'DeliveredQuantity' - 'PlannedQuantity'. Examples -2-5005 | |||
| Responsible User ResponsibleUser | The user ID of the person who created or last changed the production order. | ||
| Description The Responsible User identifies the SAP user account associated with key events in the production order lifecycle, such as creation or release. This provides accountability and traceability for actions taken within the system. In process mining, this attribute is used to analyze process variations based on user behavior. It can help identify users who might require additional training or who follow non-standard procedures. It also supports resource analysis by showing how work is distributed among different users. Why it matters It links process activities to specific individuals, enabling user-level performance analysis and promoting accountability. Where to get The user who created the order is in AUFK-ERNAM. The user who last changed it is in AUFK-AENAM. Change documents (CDHDR-USERNAME) provide a history of users for specific changes. Examples JSMITHPROD_PLANNER1BATCH_USERAMILLER | |||
| Schedule Adherence Status ScheduleAdherenceStatus | A flag indicating whether the production order was completed by its planned end date. | ||
| Description Schedule Adherence Status is a calculated boolean or categorical attribute that provides a simple, at-a-glance view of on-time performance. It is derived by comparing the 'Actual End Date' to the 'Planned End Date'. If the actual end is on or before the planned end, the status is 'On-Time'; otherwise, it is 'Late'. This attribute simplifies analysis and visualization in dashboards. It allows for easy filtering and aggregation to calculate the 'Schedule Adherence Rate' KPI and to identify the main drivers of late orders, such as specific products, plants, or material shortages. Why it matters It simplifies performance analysis by categorizing orders as 'On-Time' or 'Late', making it easy to measure and report on schedule reliability. Where to get Calculated field: IF('ActualEndDate' <= 'PlannedEndDate', 'On-Time', 'Late'). Examples On-TimeLate | |||
Production Planning Activities
| Activity | Description | ||
|---|---|---|---|
| Final Confirmation Recorded | This activity indicates that all operations for the production order are completed from a shop floor perspective. It is marked by a final confirmation entry for the last operation. | ||
| Why it matters This milestone marks the end of the physical production activities. The duration from the first to the final confirmation represents the actual production execution time. Where to get This is captured when the system status 'CNF' (I0009, Confirmed) is set. This is typically triggered by the final confirmation entry in table AFRU (AFRU-AUERU = 'X'). The status change is logged in JCDS. Capture Identify the timestamp when the 'CNF' status is set, or find the last confirmation record in AFRU. Event type explicit | |||
| First Confirmation Recorded | This event signifies the start of actual production work on the order. It is captured when the first time confirmation for any operation in the production order is entered into the system. | ||
| Why it matters This activity provides the actual start time of production, which is essential for measuring schedule adherence and identifying delays between resource allocation and the start of work. Where to get This is inferred by finding the earliest posting date (AFRU-BUDAT) among all confirmations recorded for the order in the AFRU table. The first entry marks the start of physical production. Capture Identify the first confirmation record for the order based on the posting date in table AFRU. Event type inferred | |||
| Goods Receipt Posted | This event logs the receipt of the finished product from the production order into inventory. It signifies that the manufactured goods are now available for shipment or further processing. | ||
| Why it matters This is a key completion milestone, representing the final output of the production process. The time from release to goods receipt is the total production lead time. Where to get Recorded as a material document in tables MKPF/MSEG with a movement type, typically '101', referencing the production order. The system status 'DLV' (I0012, Delivered) is also set on the order. Capture Capture the posting date from the '101' movement type material document or the timestamp of the 'DLV' status change. Event type explicit | |||
| Order Technically Completed | This is an administrative closing step that finalizes a production order from a logistics point of view. It prevents further material movements or confirmations and allows for financial settlement. | ||
| Why it matters This is the definitive end point for the order's lifecycle in logistics. Analyzing this event helps understand the total process duration, including post-production administrative tasks. Where to get Captured explicitly when the system status 'TECO' (I0045, Technically Completed) is assigned to the order. The change is logged with a timestamp in the JCDS table. Capture Identify the timestamp when the 'TECO' status becomes active for the order in JCDS. Event type explicit | |||
| Production Order Created | This event marks the creation of a new production order, typically initiated from a planning run or manual entry. It is captured explicitly when a new order record is saved in the system, receiving the 'CRTD' (Created) status. | ||
| Why it matters This is the starting point for every production order case. Analyzing the time from this event to release helps identify delays in pre-production planning and administrative setup. Where to get This event is captured from the creation date (AUFK-ERDAT) and time (AUFK-ERZET) of the production order. The system status 'CRTD' (I0001) is assigned, logged in tables JEST and JCDS linked to the order object number (AUFK-OBJNR). Capture Track the creation timestamp and the assignment of the 'CRTD' status. Event type explicit | |||
| Production Order Released | This activity signifies the official approval and release of the production order for execution. Once released, material reservations become active, and production activities like goods issue and confirmations can begin. | ||
| Why it matters This is a critical milestone representing the handover from planning to execution. The time between creation and release is a key indicator of planning efficiency. Where to get Captured explicitly when the system status 'REL' (I0002) is assigned to the production order. The change is logged with a timestamp in the JCDS table, linked to the order's object number (AUFK-OBJNR). Capture Identify the timestamp when the 'REL' status becomes active for the order. Event type explicit | |||
| Goods Issue Posted | This activity represents the withdrawal of component materials from inventory to be used in the production order. It confirms that the necessary raw materials or semi-finished goods have been staged for production. | ||
| Why it matters This event marks the start of material consumption. Delays between order release and goods issue can highlight problems in warehouse logistics, material staging, or inventory accuracy. Where to get Recorded as material documents in tables MKPF (header) and MSEG (item) with a movement type, typically '261', referencing the production order number (MSEG-AUFNR). Capture Capture the posting date (MKPF-BUDAT) of material documents with movement type '261' linked to the order. Event type explicit | |||
| Intermediate Confirmation Recorded | Represents a progress update on a production order through a partial confirmation of an operation. This is common for long-running orders where tracking progress before final completion is necessary. | ||
| Why it matters Analyzing intermediate confirmations helps in monitoring the progress of long production cycles and identifying bottlenecks within the production process itself, not just at the beginning or end. Where to get Captured from confirmation records in the AFRU table that are not marked as the final confirmation. Each entry has a posting date (AFRU-BUDAT) and time. Capture Extract all confirmation records from AFRU that are not flagged as the final confirmation for an operation. Event type explicit | |||
| Material Availability Checked | Represents the execution of a check to determine if all required components for the production order are available. This can be an automated check upon order creation or release, or a manually triggered action. | ||
| Why it matters This activity is crucial for understanding delays caused by material shortages. Frequent checks or long durations until material availability can indicate supply chain or inventory management issues. Where to get The system sets a status like 'MACM' (material committed) or 'MSPT' (material shortage). The execution can be inferred from the timestamp of this status change in tables JEST/JCDS. The results are visible in the order header. Capture Infer from the timestamp of status changes related to material availability, such as 'MACM' or 'MSPT'. Event type inferred | |||
| Order Cancellation Set | This event indicates that a production order has been cancelled before completion. This is typically done by setting a specific status or deletion flag, preventing any further processing. | ||
| Why it matters This is a negative outcome that is important to track. Analyzing cancelled orders can reveal issues with demand forecasting, planning accuracy, or master data management. Where to get This can be inferred from the assignment of the 'DLFL' (I0076, Deletion flag) status or a specific 'Canceled' status if configured. The change is logged with a timestamp in the JCDS table. Capture Identify the timestamp when the deletion flag ('DLFL') or a cancellation status is set for the order. Event type inferred | |||
| Order Data Changed | This event captures significant modifications made to a production order after its initial creation, such as changes to planned quantity, dates, or BOM. These changes are logged by the system for audit purposes. | ||
| Why it matters Tracking these changes helps identify process instability and frequent replanning. A high frequency of changes can point to poor initial planning, demand volatility, or data inaccuracies. Where to get Changes to key fields in tables like AFKO (order header) and AFPO (order item) are logged in the change document tables CDHDR (header) and CDPOS (item). Each relevant change can be extracted as an activity. Capture Extract change log entries from tables CDHDR and CDPOS for the production order object class. Event type explicit | |||
| Order Settlement Ran | Represents the financial settlement of the production order, where collected costs are allocated to the appropriate cost objects. This is the final step in the order's lifecycle from a controlling perspective. | ||
| Why it matters While a financial activity, delays in settlement can indicate issues with cost reporting or order data accuracy. It marks the true financial closure of the production process. Where to get This can be inferred when the order balance becomes zero or when the system status 'SETC' (I0076, Settlement rule created) is followed by settlement run logs. The actual settlement is recorded in controlling tables like COSP and COSS. Capture Infer from settlement documents linked to the order in CO tables or from the activation of the 'SETC' status. Event type inferred | |||
Extraction Guides
Steps
- Create an InfoSet in SAP Query: Use transaction code
SQ02. Create a new InfoSet, for example,ZPP_EVENTLOG. Select the data source option 'Table join using basis table'. UseAUFK(Order master data) as the initial basis table. - Join Required Tables: Add the following tables to the InfoSet join. The system may propose joins, but verify they are correct:
AUFKtoAFKO(Order header data PP orders):AUFK-AUFNR = AFKO-AUFNRAUFKtoJEST(Individual Object Status):AUFK-OBJNR = JEST-OBJNRJESTtoJCDS(Change Documents for System/User Statuses):JEST-OBJNR = JCDS-OBJNRANDJEST-STAT = JCDS-STAT. Note: This join is for reading status change history.AUFKtoAFRU(Order completion confirmations):AUFK-AUFNR = AFRU-AUFNRAUFKtoMSEG(Document Segment: Material):AUFK-AUFNR = MSEG-AUFNRMSEGtoMKPF(Header: Material Document):MSEG-MBLNR = MKPF-MBLNRANDMSEG-MJAHR = MKPF-MJAHR
- Define Field Groups: Drag the required fields from the source tables into the field groups. Ensure all fields needed for the final query, such as
AUFK-AUFNR,AUFK-WERKS,AFKO-GAMNG,AFKO-GASMG,JCDS-UDATE,MKPF-BUDAT, are included. - Add Custom Fields and Code: In the InfoSet, navigate to the 'Extras' tab. You will need to define custom fields for
ActivityName,EventTime,SourceSystem, andLastDataUpdateand write ABAP code to populate them based on the logic defined in the query section. The logic will involve iterating through status changes, confirmations, and material documents to generate a distinct record for each business event. This is the most complex step as you are transforming tabular data into an event log format within the InfoSet's code sections. - Generate and Save the InfoSet: Once all joins, fields, and custom code are in place, save and generate the InfoSet.
- Create an SAP Query: Use transaction code
SQ01. Create a new query, for example,ZPP_EVENTS, based on the InfoSet created in the previous steps. - Design the Query Layout: In the query designer, define the selection screen fields. Good selections include Production Order
AUFNR, PlantWERKS, Order TypeAUART, and a date range for order creationAUFK-ERDAT. - Define the Output List: Select the fields to be displayed in the report output. This list should exactly match the columns required for the event log, including the custom fields like
ActivityNameandEventTime. - Execute the Query: Run the query with the desired selection criteria, for example, for the last 3 to 6 months of production orders.
- Export the Data: Once the query displays the results, export the list. The most common and reliable format for this is 'Spreadsheet' or 'Local File' with a tab-separated or CSV format.
- Format for ProcessMind: Open the exported file in a spreadsheet program. Verify the column headers match the required attributes (
ProductionOrder,ActivityName,EventTime, etc.) and ensure the date and time format forEventTimeis consistent and meets the required standard (e.g., YYYY-MM-DD HH:MI:SS). Save the file as a CSV for upload.
Configuration
- InfoSet Joins: The core of this method relies on a complex InfoSet joining header, status, confirmation, and material movement tables. The primary joins are
AUFK->AFKO,AUFK->JEST->JCDS,AUFK->AFRU, andAUFK->MSEG->MKPF. Incorrect or inefficient joins can lead to poor performance or inaccurate data. - Selection Criteria: It is critical to define a manageable selection range to avoid system performance issues. Key selection fields on the query's startup screen should include:
- Production Plant (
AUFK-WERKS) - Production Order Type (
AUFK-AUART) - Order Creation Date (
AUFK-ERDAT): We recommend extracting data for a rolling period of 3-6 months to keep the dataset size manageable.
- Production Plant (
- System Statuses: The logic for identifying events like 'Created', 'Released', 'Technically Completed', and 'Cancelled' depends on specific system status codes (e.g., I0001, I0002, I0045, I0047). These are generally standard across SAP systems but should be verified.
- Movement Types: Events like 'Goods Issue' and 'Goods Receipt' are identified by their specific Movement Types (
MSEG-BWART), typically '261' and '101' respectively. Confirm these values align with your organization's configuration. - Authorizations: The user running the query needs authorization to access transactions
SQ01,SQ02, andSQ03, as well as display access to all underlying tables (AUFK, AFKO, JEST, JCDS, AFRU, MSEG, MKPF).
a Sample Query abap
/*
The following ABAP-style SQL script represents the complete logic required to build the event log.
This logic must be implemented within the code sections of the SQ02 InfoSet, as a direct SQL query of this nature is not run.
Each SELECT block corresponds to a specific business activity.
*/
-- 1. Production Order Created
SELECT
AUFK.AUFNR AS ProductionOrder,
'Production Order Created' AS ActivityName,
CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0001' AND JCDS.INACT = ''
UNION ALL
-- 2. Material Availability Checked (using 'Material shortage' status as an indicator)
SELECT
AUFK.AUFNR AS ProductionOrder,
'Material Availability Checked' AS ActivityName,
CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0033' AND JCDS.INACT = '' -- I0033 is MSPT (Material Shortage)
UNION ALL
-- 3. Production Order Released
SELECT
AUFK.AUFNR AS ProductionOrder,
'Production Order Released' AS ActivityName,
CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0002' AND JCDS.INACT = ''
UNION ALL
-- 4. Order Data Changed (using last change date as a proxy)
SELECT
AUFK.AUFNR AS ProductionOrder,
'Order Data Changed' AS ActivityName,
CAST(AUFK.AEDAT AS VARCHAR(8)) || ' 000000' AS EventTime, -- Time is not available in AUFK
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
WHERE AUFK.AEDAT IS NOT NULL AND AUFK.AEDAT > AUFK.ERDAT
UNION ALL
-- 5. Goods Issue Posted
SELECT
AUFK.AUFNR AS ProductionOrder,
'Goods Issue Posted' AS ActivityName,
CAST(MKPF.BUDAT AS VARCHAR(8)) || ' ' || CAST(MKPF.CPUTM AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN MSEG ON AUFK.AUFNR = MSEG.AUFNR
JOIN MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.BWART = '261'
UNION ALL
-- 6. First Confirmation Recorded
SELECT
CONF.ProductionOrder,
'First Confirmation Recorded' AS ActivityName,
CONF.EventTime,
CONF.ProductionPlant,
CONF.ProductCode,
CONF.PlannedQuantity,
CONF.DeliveredQuantity,
CONF.ActualStartDate,
CONF.ActualEndDate,
CONF.SourceSystem,
CONF.LastDataUpdate
FROM (
SELECT
AUFK.AUFNR AS ProductionOrder,
CAST(AFRU.BUDAT AS VARCHAR(8)) || ' ' || CAST(AFRU.ERFZEIT AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
ROW_NUMBER() OVER(PARTITION BY AUFK.AUFNR ORDER BY AFRU.BUDAT, AFRU.ERFZEIT) as rn
FROM AUFK
JOIN AFRU ON AUFK.AUFNR = AFRU.AUFNR
) AS CONF
WHERE CONF.rn = 1
UNION ALL
-- 7. Intermediate Confirmation Recorded
SELECT
AUFK.AUFNR AS ProductionOrder,
'Intermediate Confirmation Recorded' AS ActivityName,
CAST(AFRU.BUDAT AS VARCHAR(8)) || ' ' || CAST(AFRU.ERFZEIT AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN AFRU ON AUFK.AUFNR = AFRU.AUFNR
WHERE AFRU.AUERU = 'T' -- T = Partial Confirmation
UNION ALL
-- 8. Final Confirmation Recorded
SELECT
AUFK.AUFNR AS ProductionOrder,
'Final Confirmation Recorded' AS ActivityName,
CAST(AFRU.BUDAT AS VARCHAR(8)) || ' ' || CAST(AFRU.ERFZEIT AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN AFRU ON AUFK.AUFNR = AFRU.AUFNR
WHERE AFRU.AUERU = 'E' -- E = Final Confirmation
UNION ALL
-- 9. Goods Receipt Posted
SELECT
AUFK.AUFNR AS ProductionOrder,
'Goods Receipt Posted' AS ActivityName,
CAST(MKPF.BUDAT AS VARCHAR(8)) || ' ' || CAST(MKPF.CPUTM AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN MSEG ON AUFK.AUFNR = MSEG.AUFNR
JOIN MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.BWART = '101'
UNION ALL
-- 10. Order Technically Completed
SELECT
AUFK.AUFNR AS ProductionOrder,
'Order Technically Completed' AS ActivityName,
CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0045' AND JCDS.INACT = ''
UNION ALL
-- 11. Order Settlement Ran (using 'Closed' status as proxy)
SELECT
AUFK.AUFNR AS ProductionOrder,
'Order Settlement Ran' AS ActivityName,
CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0046' AND JCDS.INACT = '' -- I0046 is CLSD (Closed)
UNION ALL
-- 12. Order Cancellation Set
SELECT
AUFK.AUFNR AS ProductionOrder,
'Order Cancellation Set' AS ActivityName,
CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0047' AND JCDS.INACT = '' -- I0047 is CNF (Confirmed), use I0076 for Canceled. Recorrecting. Use Deletion Flag instead.
-- Corrected Logic for Cancellation:
SELECT
AUFK.AUFNR AS ProductionOrder,
'Order Cancellation Set' AS ActivityName,
CAST(AUFK.AEDAT AS VARCHAR(8)) || ' 000000' AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
WHERE AUFK.LOEKZ = 'X';Steps
- Establish Database Access: Obtain read-only credentials for the underlying SAP ECC database. You will need the server address, port, database name, username, and password. This access is essential and must be coordinated with your SAP Basis and database administration teams.
- Identify SAP Schema: Determine the correct database schema where the SAP tables are stored. This is often
SAPSR3or a similar system-specific name. You will need to replace the[Your SAP Schema]placeholder in the query with this value. - Prepare the SQL Query: Copy the complete SQL query provided in the
querysection. This query is designed to extract all specified activities into a single event log format. - Configure Query Parameters: Before executing, you must modify the placeholders within the query. Set the
[Start Date]and[End Date]to define the time window for the extraction. It is also highly recommended to filter by production plant (WERKS) or order type (AUART) to limit the data volume. - Connect and Execute: Use a standard SQL client, such as DBeaver, SAP HANA Studio, or a custom script (Python, Java), to connect to the SAP database using the credentials from step 1.
- Run the Query: Paste the configured SQL query into your client and execute it. Depending on the date range and system size, this may take several minutes to complete.
- Review Preliminary Results: Once the query finishes, briefly inspect the first few hundred rows to ensure that columns like
ProductionOrder,ActivityName, andEventTimeare populated correctly and make sense. - Export to CSV: Export the entire result set from your SQL client to a CSV file. Ensure the file uses UTF-8 encoding to prevent character issues.
- Finalize CSV for Upload: Open the CSV file and verify the column headers match the required attributes (
ProductionOrder,ActivityName,EventTime,SourceSystem,LastDataUpdate, etc.). Ensure theEventTimecolumn is formatted consistently, for exampleYYYY-MM-DD HH:MI:SS. - Upload to ProcessMind: The prepared CSV file is now ready to be uploaded to the ProcessMind platform for analysis.
Configuration
- Database Schema: You must specify the SAP schema where your production planning tables reside. Replace the
[Your SAP Schema]placeholder in the query with the correct name for your environment, for exampleSAPSR3. - Date Range: Filtering by a specific date range is critical for performance. The query filters on the order creation date (
AUFK.ERDAT). We recommend starting with a recent period of 3 to 6 months to ensure manageable data volume. - Production Order Type: To focus the analysis, filter the query on the production order type field (
AUFK.AUART). This allows you to include or exclude specific types like standard production orders, process orders, or rework orders. - Production Plant: It is highly advisable to filter by one or more production plants (
AFKO.WERKS). Extracting data for all plants at once can be very resource-intensive. - Database Access: This method requires a database user with, at a minimum, read permissions on the following tables:
AUFK,AFKO,JCDS,JEST,AFRU,MSEG,MKPF. Direct database access is not standard for all SAP users and requires special authorization.
a Sample Query sql
WITH Orders AS (
SELECT
aufk.AUFNR AS ProductionOrder,
afko.WERKS AS ProductionPlant,
afko.PLNBEZ AS ProductCode,
afko.GAMNG AS PlannedQuantity,
afko.GMEIN AS PlannedUnit,
afko.GEMNG AS DeliveredQuantity,
CASE
WHEN afko.GSTRI = '00000000' THEN NULL
ELSE TO_TIMESTAMP(afko.GSTRI || afko.GSUZI, 'YYYYMMDDHH24MISS')
END AS ActualStartDate,
CASE
WHEN afko.GETRI = '00000000' THEN NULL
ELSE TO_TIMESTAMP(afko.GETRI || afko.GEUZI, 'YYYYMMDDHH24MISS')
END AS ActualEndDate,
aufk.OBJNR,
aufk.ERDAT AS CreationDate,
aufk.ERFAS AS CreationTime
FROM
"[Your SAP Schema]".AUFK aufk
JOIN
"[Your SAP Schema]".AFKO afko ON aufk.AUFNR = afko.AUFNR
WHERE
aufk.AUTYP = '10' -- Filter for Production Orders
AND aufk.ERDAT BETWEEN '[Start Date]' AND '[End Date]'
-- AND afko.WERKS IN ('[Your Plant]', '[Another Plant]') -- Optional: Filter by Plant
-- AND aufk.AUART IN ('[Your Order Type]') -- Optional: Filter by Order Type
),
Confirmations AS (
SELECT
o.ProductionOrder,
a.RUECK AS ConfirmationNumber,
a.RMZHL AS ConfirmationCounter,
TO_TIMESTAMP(a.BUDAT || a.UZEIT, 'YYYYMMDDHH24MISS') AS EventTime,
a.AUERU AS IsFinalConfirmation,
ROW_NUMBER() OVER(PARTITION BY o.ProductionOrder ORDER BY TO_TIMESTAMP(a.BUDAT || a.UZEIT, 'YYYYMMDDHH24MISS') ASC) as rn_asc,
ROW_NUMBER() OVER(PARTITION BY o.ProductionOrder ORDER BY TO_TIMESTAMP(a.BUDAT || a.UZEIT, 'YYYYMMDDHH24MISS') DESC) as rn_desc
FROM
Orders o
JOIN
"[Your SAP Schema]".AFRU a ON o.ProductionOrder = a.AUFNR
)
-- 1. Production Order Created
SELECT
o.ProductionOrder,
'Production Order Created' AS ActivityName,
TO_TIMESTAMP(o.CreationDate || o.CreationTime, 'YYYYMMDDHH24MISS') AS EventTime,
'[Your Source System]' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
o.ProductionPlant,
o.ProductCode,
o.PlannedQuantity,
o.DeliveredQuantity,
o.ActualStartDate,
o.ActualEndDate
FROM Orders o
UNION ALL
-- 2. Material Availability Checked (using 'Material Committed' status as proxy)
SELECT
o.ProductionOrder,
'Material Availability Checked' AS ActivityName,
TO_TIMESTAMP(jcds.UDATE || jcds.UTIME, 'YYYYMMDDHH24MISS') AS EventTime,
'[Your Source System]' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
o.ProductionPlant,
o.ProductCode,
o.PlannedQuantity,
o.DeliveredQuantity,
o.ActualStartDate,
o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".JCDS jcds ON o.OBJNR = jcds.OBJNR
WHERE jcds.STAT = 'I0006' AND jcds.INACT != 'X'
UNION ALL
-- 3. Production Order Released
SELECT
o.ProductionOrder,
'Production Order Released' AS ActivityName,
TO_TIMESTAMP(jcds.UDATE || jcds.UTIME, 'YYYYMMDDHH24MISS') AS EventTime,
'[Your Source System]' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
o.ProductionPlant,
o.ProductCode,
o.PlannedQuantity,
o.DeliveredQuantity,
o.ActualStartDate,
o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".JCDS jcds ON o.OBJNR = jcds.OBJNR
WHERE jcds.STAT = 'I0002' AND jcds.INACT != 'X'
UNION ALL
-- 4. Order Data Changed
SELECT
o.ProductionOrder,
'Order Data Changed' AS ActivityName,
TO_TIMESTAMP(aufk.AEDAT || aufk.AEZEIT, 'YYYYMMDDHH24MISS') AS EventTime,
'[Your Source System]' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
o.ProductionPlant,
o.ProductCode,
o.PlannedQuantity,
o.DeliveredQuantity,
o.ActualStartDate,
o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".AUFK aufk ON o.ProductionOrder = aufk.AUFNR
WHERE aufk.AEDAT > o.CreationDate
UNION ALL
-- 5. Goods Issue Posted
SELECT
o.ProductionOrder,
'Goods Issue Posted' AS ActivityName,
TO_TIMESTAMP(mkpf.CPUDT || mkpf.CPUTM, 'YYYYMMDDHH24MISS') AS EventTime,
'[Your Source System]' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
o.ProductionPlant,
o.ProductCode,
o.PlannedQuantity,
o.DeliveredQuantity,
o.ActualStartDate,
o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".MSEG mseg ON o.ProductionOrder = mseg.AUFNR
JOIN "[Your SAP Schema]".MKPF mkpf ON mseg.MBLNR = mkpf.MBLNR AND mseg.MJAHR = mkpf.MJAHR
WHERE mseg.BWART = '261'
UNION ALL
-- 6. First Confirmation Recorded
SELECT
c.ProductionOrder,
'First Confirmation Recorded' AS ActivityName,
c.EventTime,
'[Your Source System]' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
o.ProductionPlant,
o.ProductCode,
o.PlannedQuantity,
o.DeliveredQuantity,
o.ActualStartDate,
o.ActualEndDate
FROM Confirmations c
JOIN Orders o ON c.ProductionOrder = o.ProductionOrder
WHERE c.rn_asc = 1
UNION ALL
-- 7. Intermediate Confirmation Recorded
SELECT
c.ProductionOrder,
'Intermediate Confirmation Recorded' AS ActivityName,
c.EventTime,
'[Your Source System]' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
o.ProductionPlant,
o.ProductCode,
o.PlannedQuantity,
o.DeliveredQuantity,
o.ActualStartDate,
o.ActualEndDate
FROM Confirmations c
JOIN Orders o ON c.ProductionOrder = o.ProductionOrder
WHERE c.rn_asc > 1 AND c.IsFinalConfirmation IS NULL
UNION ALL
-- 8. Final Confirmation Recorded
SELECT
c.ProductionOrder,
'Final Confirmation Recorded' AS ActivityName,
c.EventTime,
'[Your Source System]' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
o.ProductionPlant,
o.ProductCode,
o.PlannedQuantity,
o.DeliveredQuantity,
o.ActualStartDate,
o.ActualEndDate
FROM Confirmations c
JOIN Orders o ON c.ProductionOrder = o.ProductionOrder
WHERE c.IsFinalConfirmation = 'X'
UNION ALL
-- 9. Goods Receipt Posted
SELECT
o.ProductionOrder,
'Goods Receipt Posted' AS ActivityName,
TO_TIMESTAMP(mkpf.CPUDT || mkpf.CPUTM, 'YYYYMMDDHH24MISS') AS EventTime,
'[Your Source System]' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
o.ProductionPlant,
o.ProductCode,
o.PlannedQuantity,
o.DeliveredQuantity,
o.ActualStartDate,
o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".MSEG mseg ON o.ProductionOrder = mseg.AUFNR
JOIN "[Your SAP Schema]".MKPF mkpf ON mseg.MBLNR = mkpf.MBLNR AND mseg.MJAHR = mkpf.MJAHR
WHERE mseg.BWART = '101'
UNION ALL
-- 10. Order Technically Completed
SELECT
o.ProductionOrder,
'Order Technically Completed' AS ActivityName,
TO_TIMESTAMP(jcds.UDATE || jcds.UTIME, 'YYYYMMDDHH24MISS') AS EventTime,
'[Your Source System]' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
o.ProductionPlant,
o.ProductCode,
o.PlannedQuantity,
o.DeliveredQuantity,
o.ActualStartDate,
o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".JCDS jcds ON o.OBJNR = jcds.OBJNR
WHERE jcds.STAT = 'I0045' AND jcds.INACT != 'X'
UNION ALL
-- 11. Order Settlement Ran
SELECT
o.ProductionOrder,
'Order Settlement Ran' AS ActivityName,
TO_TIMESTAMP(jcds.UDATE || jcds.UTIME, 'YYYYMMDDHH24MISS') AS EventTime,
'[Your Source System]' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
o.ProductionPlant,
o.ProductCode,
o.PlannedQuantity,
o.DeliveredQuantity,
o.ActualStartDate,
o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".JCDS jcds ON o.OBJNR = jcds.OBJNR
WHERE jcds.STAT = 'I0046' AND jcds.INACT != 'X'
UNION ALL
-- 12. Order Cancellation Set
SELECT
o.ProductionOrder,
'Order Cancellation Set' AS ActivityName,
TO_TIMESTAMP(jcds.UDATE || jcds.UTime, 'YYYYMMDDHH24MISS') AS EventTime,
'[Your Source System]' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
o.ProductionPlant,
o.ProductCode,
o.PlannedQuantity,
o.DeliveredQuantity,
o.ActualStartDate,
o.ActualEndDate
FROM Orders o
JOIN "[Your SAP Schema]".JCDS jcds ON o.OBJNR = jcds.OBJNR
WHERE jcds.STAT = 'I0076' AND jcds.INACT != 'X';Steps
- Create an InfoSet in SAP Query: Use transaction code
SQ02. Create a new InfoSet, for example,ZPP_EVENTLOG. Select the data source option 'Table join using basis table'. UseAUFK(Order master data) as the initial basis table. - Join Required Tables: Add the following tables to the InfoSet join. The system may propose joins, but verify they are correct:
AUFKtoAFKO(Order header data PP orders):AUFK-AUFNR = AFKO-AUFNRAUFKtoJEST(Individual Object Status):AUFK-OBJNR = JEST-OBJNRJESTtoJCDS(Change Documents for System/User Statuses):JEST-OBJNR = JCDS-OBJNRANDJEST-STAT = JCDS-STAT. Note: This join is for reading status change history.AUFKtoAFRU(Order completion confirmations):AUFK-AUFNR = AFRU-AUFNRAUFKtoMSEG(Document Segment: Material):AUFK-AUFNR = MSEG-AUFNRMSEGtoMKPF(Header: Material Document):MSEG-MBLNR = MKPF-MBLNRANDMSEG-MJAHR = MKPF-MJAHR
- Define Field Groups: Drag the required fields from the source tables into the field groups. Ensure all fields needed for the final query, such as
AUFK-AUFNR,AUFK-WERKS,AFKO-GAMNG,AFKO-GASMG,JCDS-UDATE,MKPF-BUDAT, are included. - Add Custom Fields and Code: In the InfoSet, navigate to the 'Extras' tab. You will need to define custom fields for
ActivityName,EventTime,SourceSystem, andLastDataUpdateand write ABAP code to populate them based on the logic defined in the query section. The logic will involve iterating through status changes, confirmations, and material documents to generate a distinct record for each business event. This is the most complex step as you are transforming tabular data into an event log format within the InfoSet's code sections. - Generate and Save the InfoSet: Once all joins, fields, and custom code are in place, save and generate the InfoSet.
- Create an SAP Query: Use transaction code
SQ01. Create a new query, for example,ZPP_EVENTS, based on the InfoSet created in the previous steps. - Design the Query Layout: In the query designer, define the selection screen fields. Good selections include Production Order
AUFNR, PlantWERKS, Order TypeAUART, and a date range for order creationAUFK-ERDAT. - Define the Output List: Select the fields to be displayed in the report output. This list should exactly match the columns required for the event log, including the custom fields like
ActivityNameandEventTime. - Execute the Query: Run the query with the desired selection criteria, for example, for the last 3 to 6 months of production orders.
- Export the Data: Once the query displays the results, export the list. The most common and reliable format for this is 'Spreadsheet' or 'Local File' with a tab-separated or CSV format.
- Format for ProcessMind: Open the exported file in a spreadsheet program. Verify the column headers match the required attributes (
ProductionOrder,ActivityName,EventTime, etc.) and ensure the date and time format forEventTimeis consistent and meets the required standard (e.g., YYYY-MM-DD HH:MI:SS). Save the file as a CSV for upload.
Configuration
- InfoSet Joins: The core of this method relies on a complex InfoSet joining header, status, confirmation, and material movement tables. The primary joins are
AUFK->AFKO,AUFK->JEST->JCDS,AUFK->AFRU, andAUFK->MSEG->MKPF. Incorrect or inefficient joins can lead to poor performance or inaccurate data. - Selection Criteria: It is critical to define a manageable selection range to avoid system performance issues. Key selection fields on the query's startup screen should include:
- Production Plant (
AUFK-WERKS) - Production Order Type (
AUFK-AUART) - Order Creation Date (
AUFK-ERDAT): We recommend extracting data for a rolling period of 3-6 months to keep the dataset size manageable.
- Production Plant (
- System Statuses: The logic for identifying events like 'Created', 'Released', 'Technically Completed', and 'Cancelled' depends on specific system status codes (e.g., I0001, I0002, I0045, I0047). These are generally standard across SAP systems but should be verified.
- Movement Types: Events like 'Goods Issue' and 'Goods Receipt' are identified by their specific Movement Types (
MSEG-BWART), typically '261' and '101' respectively. Confirm these values align with your organization's configuration. - Authorizations: The user running the query needs authorization to access transactions
SQ01,SQ02, andSQ03, as well as display access to all underlying tables (AUFK, AFKO, JEST, JCDS, AFRU, MSEG, MKPF).
a Sample Query abap
/*
The following ABAP-style SQL script represents the complete logic required to build the event log.
This logic must be implemented within the code sections of the SQ02 InfoSet, as a direct SQL query of this nature is not run.
Each SELECT block corresponds to a specific business activity.
*/
-- 1. Production Order Created
SELECT
AUFK.AUFNR AS ProductionOrder,
'Production Order Created' AS ActivityName,
CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0001' AND JCDS.INACT = ''
UNION ALL
-- 2. Material Availability Checked (using 'Material shortage' status as an indicator)
SELECT
AUFK.AUFNR AS ProductionOrder,
'Material Availability Checked' AS ActivityName,
CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0033' AND JCDS.INACT = '' -- I0033 is MSPT (Material Shortage)
UNION ALL
-- 3. Production Order Released
SELECT
AUFK.AUFNR AS ProductionOrder,
'Production Order Released' AS ActivityName,
CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0002' AND JCDS.INACT = ''
UNION ALL
-- 4. Order Data Changed (using last change date as a proxy)
SELECT
AUFK.AUFNR AS ProductionOrder,
'Order Data Changed' AS ActivityName,
CAST(AUFK.AEDAT AS VARCHAR(8)) || ' 000000' AS EventTime, -- Time is not available in AUFK
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
WHERE AUFK.AEDAT IS NOT NULL AND AUFK.AEDAT > AUFK.ERDAT
UNION ALL
-- 5. Goods Issue Posted
SELECT
AUFK.AUFNR AS ProductionOrder,
'Goods Issue Posted' AS ActivityName,
CAST(MKPF.BUDAT AS VARCHAR(8)) || ' ' || CAST(MKPF.CPUTM AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN MSEG ON AUFK.AUFNR = MSEG.AUFNR
JOIN MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.BWART = '261'
UNION ALL
-- 6. First Confirmation Recorded
SELECT
CONF.ProductionOrder,
'First Confirmation Recorded' AS ActivityName,
CONF.EventTime,
CONF.ProductionPlant,
CONF.ProductCode,
CONF.PlannedQuantity,
CONF.DeliveredQuantity,
CONF.ActualStartDate,
CONF.ActualEndDate,
CONF.SourceSystem,
CONF.LastDataUpdate
FROM (
SELECT
AUFK.AUFNR AS ProductionOrder,
CAST(AFRU.BUDAT AS VARCHAR(8)) || ' ' || CAST(AFRU.ERFZEIT AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate,
ROW_NUMBER() OVER(PARTITION BY AUFK.AUFNR ORDER BY AFRU.BUDAT, AFRU.ERFZEIT) as rn
FROM AUFK
JOIN AFRU ON AUFK.AUFNR = AFRU.AUFNR
) AS CONF
WHERE CONF.rn = 1
UNION ALL
-- 7. Intermediate Confirmation Recorded
SELECT
AUFK.AUFNR AS ProductionOrder,
'Intermediate Confirmation Recorded' AS ActivityName,
CAST(AFRU.BUDAT AS VARCHAR(8)) || ' ' || CAST(AFRU.ERFZEIT AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN AFRU ON AUFK.AUFNR = AFRU.AUFNR
WHERE AFRU.AUERU = 'T' -- T = Partial Confirmation
UNION ALL
-- 8. Final Confirmation Recorded
SELECT
AUFK.AUFNR AS ProductionOrder,
'Final Confirmation Recorded' AS ActivityName,
CAST(AFRU.BUDAT AS VARCHAR(8)) || ' ' || CAST(AFRU.ERFZEIT AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN AFRU ON AUFK.AUFNR = AFRU.AUFNR
WHERE AFRU.AUERU = 'E' -- E = Final Confirmation
UNION ALL
-- 9. Goods Receipt Posted
SELECT
AUFK.AUFNR AS ProductionOrder,
'Goods Receipt Posted' AS ActivityName,
CAST(MKPF.BUDAT AS VARCHAR(8)) || ' ' || CAST(MKPF.CPUTM AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN MSEG ON AUFK.AUFNR = MSEG.AUFNR
JOIN MKPF ON MSEG.MBLNR = MKPF.MBLNR AND MSEG.MJAHR = MKPF.MJAHR
WHERE MSEG.BWART = '101'
UNION ALL
-- 10. Order Technically Completed
SELECT
AUFK.AUFNR AS ProductionOrder,
'Order Technically Completed' AS ActivityName,
CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0045' AND JCDS.INACT = ''
UNION ALL
-- 11. Order Settlement Ran (using 'Closed' status as proxy)
SELECT
AUFK.AUFNR AS ProductionOrder,
'Order Settlement Ran' AS ActivityName,
CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0046' AND JCDS.INACT = '' -- I0046 is CLSD (Closed)
UNION ALL
-- 12. Order Cancellation Set
SELECT
AUFK.AUFNR AS ProductionOrder,
'Order Cancellation Set' AS ActivityName,
CAST(JCDS.UDATE AS VARCHAR(8)) || ' ' || CAST(JCDS.UTIME AS VARCHAR(6)) AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
JOIN JCDS ON AUFK.OBJNR = JCDS.OBJNR
WHERE JCDS.STAT = 'I0047' AND JCDS.INACT = '' -- I0047 is CNF (Confirmed), use I0076 for Canceled. Recorrecting. Use Deletion Flag instead.
-- Corrected Logic for Cancellation:
SELECT
AUFK.AUFNR AS ProductionOrder,
'Order Cancellation Set' AS ActivityName,
CAST(AUFK.AEDAT AS VARCHAR(8)) || ' 000000' AS EventTime,
AUFK.WERKS AS ProductionPlant,
AUFK.MATNR AS ProductCode,
AFKO.GAMNG AS PlannedQuantity,
AFKO.GASMG AS DeliveredQuantity,
AUFK.GSTRP AS ActualStartDate,
AUFK.GETRI AS ActualEndDate,
SY-SYSID AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM AUFK
WHERE AUFK.LOEKZ = 'X';