Your Supply Chain Management Data Template
Your Supply Chain Management Data Template
- Recommended attributes to collect
- Key activities to track
- Extraction guidance
Supply Chain Management Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Name ActivityName | The name of the business activity or event that occurred at a specific point in the supply chain process. | ||
| Description This attribute describes a specific step or milestone within the logistics order lifecycle, such as 'Purchase Order Issued' or 'Goods Issue Posted'. These activities form the sequence of events that constitute the process. Analyzing the sequence of activities is the core of process mining. It allows for the discovery of the actual process flow, comparison against standard operating procedures, and identification of process steps that cause delays or require rework. Why it matters It defines the steps of the process, which is fundamental for discovering process maps, analyzing variants, and measuring cycle times between activities. Where to get Generated by mapping transaction codes or status changes from various SAP tables (e.g., EKKO, LIKP, VBUK) to user-friendly activity names during data transformation. Examples Purchase Order IssuedGoods Receipt For PO PostedOutbound Delivery CreatedProof Of Delivery Confirmed | |||
| Event Time EventTime | The timestamp indicating when the activity occurred, including the date and time. | ||
| Description This attribute provides the precise date and time for each activity in the process. It serves as the chronological backbone of the event log, ordering the process steps as they happened in reality. The Event Time is crucial for all time-based analysis. It is used to calculate cycle times between activities, measure the total lead time of a process, identify performance trends over time, and pinpoint when bottlenecks are most likely to occur. Why it matters This timestamp is essential for calculating all durations and performance metrics, enabling the analysis of process bottlenecks and lead times. Where to get Extracted from timestamp fields associated with document creation or status changes in core SAP tables, such as CDHDR/CDPOS for change documents, or specific date/time fields like ERDAT/ERZET in header tables. Examples 2023-04-15T10:25:00Z2023-04-18T14:00:00Z2023-04-22T08:15:00Z | |||
| Logistics Order LogisticsOrder | The unique identifier for a single, end-to-end supply chain process, from initial demand to final delivery confirmation. | ||
| Description The Logistics Order serves as the primary case identifier, linking all activities related to a specific demand or fulfillment request within the supply chain. This approach allows for tracking the entire lifecycle of a product's journey, from planning and procurement to production and distribution, ensuring a comprehensive view of the process. In process mining, analyzing by Logistics Order reveals the complete path taken to fulfill a demand. It helps identify common variants, bottlenecks, and deviations across the entire supply chain, rather than looking at siloed processes like procurement or sales in isolation. Why it matters This is the essential key to connect all related events into a single process instance, enabling end-to-end analysis of the supply chain. Where to get This is a conceptual identifier and may need to be constructed by linking various document numbers, such as a Sales Order number with its subsequent Purchase Order and Delivery numbers. Consult SAP S/4HANA Supply Chain documentation or a subject matter expert. Examples LO-4500078192LO-4500078193LO-4500078194 | |||
| Last Data Update LastDataUpdate | The timestamp when the data was last refreshed or extracted from the source system. | ||
| Description This attribute indicates the last time the data for the event log was updated. It provides a 'freshness' date for the data being analyzed, ensuring users are aware of the data's timeliness. In any analysis, understanding the recency of the data is crucial for making informed decisions. This timestamp helps users trust the data and understand the time window covered by the dashboards and KPIs. Why it matters Ensures transparency about data freshness, allowing users to know how current the process analysis is. Where to get This is a metadata field typically generated and populated by the data extraction or ETL tool at the end of a successful data refresh. Examples 2023-10-27T02:00:00Z2023-10-28T02:00:00Z2023-10-29T02:00:00Z | |||
| Source System SourceSystem | The system from which the data originated. | ||
| Description Identifies the specific source system instance where the event data was generated. In a complex landscape, a company might have multiple SAP S/4HANA instances for different regions or business units. This attribute is important for data governance and for segmenting analysis. It allows analysts to compare process performance between different systems or to isolate data from a single, authoritative source. Why it matters Provides critical context for data origin, which is important for data validation and for comparing processes across different system landscapes. Where to get This is typically a static value added during the data extraction process to identify the origin, for example, the SAP System ID (SAPSID). Examples S4H_PROD_EUS4H_PROD_NAS4H_DEV | |||
| Customer Name CustomerName | The name of the customer for whom the order is being fulfilled. | ||
| Description This attribute identifies the end customer associated with a sales order and subsequent fulfillment activities. It provides context for the demand side of the supply chain process. In process mining, analyzing by customer helps identify different fulfillment patterns or performance levels for key accounts. It is used in dashboards like the Proof of Delivery Lag Analysis to see if certain customers consistently delay confirmations. Why it matters Allows for segmentation of analysis by customer, revealing customer-specific behaviors, bottlenecks, or service level agreement performance. Where to get The customer number (KUNNR) is stored in sales documents (e.g., table VBAK). The name is retrieved from the central customer master table KNA1. Examples Retail CorpInnovate SolutionsManufacturing Partners | |||
| Executing User ExecutingUser | The user ID of the person who executed the activity. | ||
| Description This attribute captures the SAP user name or ID of the employee who performed the given activity, such as creating a purchase order or posting a goods receipt. It links process steps to the human resources involved. Analyzing by user helps in understanding workload distribution, identifying training needs, and spotting performance differences between individuals or teams. It is also valuable for compliance analysis to see who performed critical process steps. Why it matters Attributes activities to specific users, enabling workload analysis, performance comparison, and investigation of non-compliant actions. Where to get Found in 'Created by' or 'Changed by' fields in many SAP tables, such as ERNAM in EKKO (PO Header), LIKP (Delivery Header), and VBRK (Invoice Header). Examples CBROWNJSMITHASINGH | |||
| Material Number MaterialNumber | The unique identifier for the product or material being processed. | ||
| Description The Material Number, often called a SKU, is the unique code for a specific product within SAP. It links process activities to the physical or digital goods being manufactured, procured, or shipped. This attribute is vital for product-centric analysis. It helps pinpoint if certain products are more prone to quality issues, production delays, or stockouts. Dashboards can be filtered by material to investigate performance for specific product lines. Why it matters Enables product-level analysis to identify if process issues like delays or quality failures are concentrated on specific materials. Where to get Found in item-level tables for most documents, such as VBAP for Sales Orders, EKPO for Purchase Orders, and LIPS for Deliveries. The field is typically named MATNR. Examples FG-100-ARM-2034-BSA-5500 | |||
| Plant Plant | The manufacturing or distribution facility where the activity took place. | ||
| Description The Plant is an SAP organizational unit representing a location where goods are produced, stored, or distributed. It provides the geographical or functional context for process activities. Analyzing process performance by Plant is a common requirement. It allows for benchmarking and comparison of efficiency, throughput, and compliance across different operational sites, helping to identify best practices or locations that need improvement. Why it matters Provides a geographical or organizational dimension for analysis, enabling performance comparison across different facilities. Where to get Found in many document item tables, such as VBAP (Sales), EKPO (Purchasing), and LIPS (Delivery). The field is typically named WERKS. Examples 100021003500 | |||
| Quality Inspection Result QualityInspectionResult | The outcome of a quality inspection, such as 'Passed' or 'Failed'. | ||
| Description This attribute records the result of a quality check performed on produced or received goods. The outcome determines whether the goods can proceed to the next step or if they require rework, return, or scrapping. This is a critical attribute for the Quality Control Rejection Rates dashboard and KPI. Analyzing the frequency and reasons for failures helps identify systemic quality issues with specific products, suppliers, or production lines. Why it matters Directly supports the calculation of quality-related KPIs and helps pinpoint sources of poor quality in the supply chain. Where to get This information is typically stored in the Quality Management (QM) module. Data may come from inspection lot tables like QALS and usage decision codes in QAVE. Examples PassedFailedRework RequiredPassed with Deviation | |||
| Requested Delivery Date RequestedDeliveryDate | The date on which the supplier was requested to deliver the goods. | ||
| Description This is the delivery date specified in the purchase order, representing the target for the supplier. It serves as the baseline against which actual delivery performance is measured. This date is essential for calculating the Supplier On-Time Delivery Rate KPI. By comparing the actual Goods Receipt date to this requested date, a business can objectively measure whether suppliers are meeting their commitments. Why it matters This is the baseline date used to calculate the 'Supplier On-Time Delivery Rate' KPI, making it critical for supplier performance management. Where to get Found in the purchase order item table EKPO, with the field name EINDT (Delivery Date). Examples 2023-05-20T00:00:00Z2023-06-15T00:00:00Z2023-07-01T00:00:00Z | |||
| Supplier Name SupplierName | The name of the supplier or vendor associated with procurement activities. | ||
| Description This attribute identifies the supplier from whom goods or services are procured. It is typically associated with activities like 'Purchase Requisition Created' and 'Purchase Order Issued'. Supplier Name is essential for the Supplier Delivery Performance dashboard. It allows for filtering and segmenting the data to compare on-time delivery rates, lead times, and quality levels across different suppliers, which is critical for strategic sourcing and supplier relationship management. Why it matters Enables performance analysis by supplier, which is crucial for optimizing the procurement process and evaluating supplier reliability. Where to get The supplier number (LIFNR) is stored in purchasing documents (e.g., table EKKN, EBAN). The name is retrieved from the central vendor master table LFA1. Examples Global Components Inc.Advanced Materials LLCPrecision Parts Co. | |||
| Carrier Name CarrierName | The name of the transportation company or freight forwarder handling the shipment. | ||
| Description The Carrier Name identifies the logistics service provider responsible for transporting goods between locations, for example, from a warehouse to a customer. This attribute is essential for the Transportation Cycle Time Analysis dashboard. It allows for the comparison of transit times, costs, and reliability across different carriers, enabling better logistics planning and carrier selection. Why it matters Enables performance analysis by transportation provider, which is key to optimizing logistics costs and delivery times. Where to get The carrier ID (forwarding agent) is stored in shipment documents, such as table VTTK (Shipment Header). The name is retrieved from master data tables like LFA1. Examples DHLFedExMaersk Logistics | |||
| End Time EndTime | The timestamp indicating when an activity was completed, used for calculating processing time. | ||
| Description The End Time marks the completion of a specific activity. While Start Time (EventTime) indicates when an activity begins, End Time is necessary to understand how long an activity took to execute, especially for activities that are not instantaneous. In analysis, having both a start and end time allows for precise measurement of processing time versus waiting time. This helps distinguish between time spent actively working on a task and time spent waiting for the next step, which is key to identifying efficiency opportunities. Why it matters Enables the calculation of precise activity processing times, helping to differentiate between value-added work and waiting time. Where to get Can be derived from status changes in SAP. For example, the start is a 'In Progress' status update and the end is a 'Completed' status update from tables like JEST/JCDS. For instantaneous events, EndTime may equal StartTime. Examples 2023-04-15T11:30:00Z2023-04-18T14:05:00Z2023-04-22T09:00:00Z | |||
| Is Late Delivery IsLateDelivery | A boolean flag indicating if a supplier delivery was made after the requested delivery date. | ||
| Description This calculated flag provides a simple true or false indicator of whether a delivery was on time. It is derived by comparing the timestamp of the 'Goods Receipt For PO Posted' activity with the 'Requested Delivery Date' attribute. This attribute simplifies the creation of dashboards and KPIs related to supplier performance. It allows for quick filtering and aggregation to calculate the Supplier On-Time Delivery Rate and identify which suppliers, materials, or regions have the most frequent delays. Why it matters Simplifies supplier performance analysis by providing a clear, binary outcome for each delivery, which is essential for the on-time delivery KPI. Where to get This is a calculated field. The logic is: IF ('Goods Receipt For PO Posted'.EventTime > PurchaseOrder.RequestedDeliveryDate) THEN True ELSE False. Examples truefalse | |||
| Is Rework IsRework | A flag indicating if an activity or a sequence of activities represents rework. | ||
| Description This boolean attribute flags activities that are repetitions of earlier steps in the process, indicating some form of rework or correction. For example, if a 'Quality Inspection' is followed by a 'Production Order Created' step and then another 'Quality Inspection', the second inspection could be flagged as rework. Identifying rework is key to understanding process inefficiency and hidden costs. This flag helps quantify the frequency and impact of rework loops, supporting the calculation of the Order Processing Error Rate KPI and highlighting areas for process improvement. Why it matters Helps to quantify process inefficiencies by explicitly identifying and counting activities that are part of a rework loop. Where to get This is typically calculated within the process mining tool by identifying repeated sequences of activities within the same case. Examples truefalse | |||
| Order Quantity OrderQuantity | The quantity of the material in the order line item. | ||
| Description This attribute specifies the number of units of a material requested in a sales order or purchase order line item. It provides a measure of volume for each transaction. Analyzing by quantity helps to prioritize analysis on high-volume orders, which may have a larger business impact. It can also be used to normalize other metrics, such as calculating cost per unit, or to understand how order size affects processing times. Why it matters Provides volume context to transactions, allowing for impact analysis and segmentation of cases by size. Where to get Found in item-level tables, such as MENGE in EKPO (Purchase Order) or KWMENG in VBAP (Sales Order). Examples 100500025 | |||
| Processing Time ProcessingTime | The duration of time spent actively working on an activity. | ||
| Description Processing Time is the calculated duration from an activity's start time to its end time. It represents the actual work or 'touch time' for a process step, as opposed to waiting time between steps. This metric is fundamental to identifying inefficiencies. By isolating processing time from waiting time, analysts can determine whether delays are caused by slow execution of tasks or by long queues and handoff delays between departments or systems. Why it matters Helps distinguish active work time from idle waiting time, which is critical for identifying the true sources of process delays. Where to get Calculated by subtracting the activity's StartTime from its EndTime (EndTime - StartTime). Examples PT1H30MPT8HP2D | |||
| Purchase Order Number PurchaseOrderNumber | The unique identifier for the Purchase Order (PO) document. | ||
| Description The Purchase Order Number is the official document number used to order materials from a supplier. It is a key identifier in the procurement part of the supply chain. This attribute allows for deep dives into the procurement process. It is used in the Procurement End-to-End Cycle Time dashboard and for calculating the Supplier On-Time Delivery Rate KPI, linking requisition, order, receipt, and payment activities. Why it matters Serves as a key identifier for drilling down into the procurement subprocess and linking related procurement events. Where to get Found in the PO header table EKKO and item table EKPO. The field is named EBELN. Examples 450007819245000781934500078194 | |||
| Sales Order Number SalesOrderNumber | The unique identifier for the Sales Order (SO) document. | ||
| Description The Sales Order Number is the official document number confirming a sale to a customer. It is a key identifier in the order-to-cash part of the supply chain. This attribute is crucial for tracking the fulfillment of a specific customer order. It helps link the initial customer request to all subsequent activities like delivery and invoicing, supporting the Order Fulfillment Lead Time Analysis. Why it matters Acts as a key identifier for the order fulfillment subprocess, linking customer demand to logistics and financial settlement. Where to get Found in the SO header table VBAK and item table VBAP. The field is named VBELN. Examples 100023451000234610002347 | |||
Supply Chain Management Activities
| Activity | Description | ||
|---|---|---|---|
| Goods Issue Posted | This activity represents the legal and physical departure of goods from the warehouse. It reduces inventory and posts the cost of goods sold, marking the official shipment of the order. | ||
| Why it matters This is a critical milestone that marks the start of the 'in-transit' period. It is the definitive point where the goods have left the company's possession for delivery. Where to get This is an explicit event that creates a material document. The timestamp is recorded in the material document header (MKPF, field BUDAT) and updates the goods movement status in the delivery document (LIKP, field WBSTK). Capture Use the posting date (MKPF-BUDAT) from the goods issue material document associated with the outbound delivery. Event type explicit | |||
| Goods Produced | This activity represents the confirmation that manufacturing of the goods is complete. It is typically recorded as a goods receipt from the production order, moving the finished product into inventory. | ||
| Why it matters This marks the end of the production cycle. The time between production order creation and this event represents the manufacturing lead time, a key metric for production efficiency. Where to get This can be captured explicitly through a material document posting (goods receipt from production order) or inferred from the final confirmation of the production order (AFRU table) or a status change to 'Delivered'. Capture Infer from the posting date of the final goods receipt material document for the production order or a status change. Event type inferred | |||
| Goods Receipt For PO Posted | Represents the physical receipt of raw materials or goods from a supplier against a purchase order. This event is recorded when the goods are received at the warehouse or plant. | ||
| Why it matters This activity completes the supplier delivery portion of the procurement cycle. It is vital for calculating supplier on-time delivery rates and understanding inbound logistics performance. Where to get This is an explicit event captured via a material document posting. The event timestamp is in the material document header table (MKPF, field BUDAT). The link to the PO is in the item table (MSEG). Capture Use the posting date (MKPF-BUDAT) from the material document associated with the purchase order. Event type explicit | |||
| Proof Of Delivery Confirmed | Represents the formal confirmation from the customer or carrier that the goods have been received as specified. This is often recorded by updating the outbound delivery with POD information. | ||
| Why it matters This activity is the definitive end of the fulfillment process. It is crucial for accurate billing, dispute resolution, and measuring the final leg of the delivery cycle. Where to get This is an explicit event. The proof of delivery date is recorded in the delivery header table (LIKP, field PODAT) or related POD tables (VLPOD). Capture Use the Proof of Delivery Date (LIKP-PODAT) from the delivery document. Event type explicit | |||
| Purchase Order Issued | Marks the formal creation and issuance of a purchase order to an external supplier for required materials. This activity converts a purchase requisition into a legally binding commitment with a vendor. | ||
| Why it matters As a key milestone, this activity is crucial for measuring supplier lead times and procurement cycle times. It provides the baseline for evaluating supplier on-time delivery performance. Where to get This is an explicit event logged upon the creation of the purchase order. The creation timestamp is stored in the purchase order header table (EKKO, field AEDAT). Capture Use the document creation date (EKKO-AEDAT) for the purchase order. Event type explicit | |||
| Sales Order Created | This activity marks the creation of a new sales order, which formally captures a customer's request for goods. It is an explicit event recorded when a user saves a new sales order document in the system. | ||
| Why it matters This is the primary starting point for the order-to-cash process. Analyzing the time from this event to others reveals overall order fulfillment lead time and identifies initial processing delays. Where to get This event is explicitly logged. It can be found in the change documents for sales order tables (CDHDR/CDPOS) or by using the creation timestamp in the sales order header table (VBAK, field ERDAT). Capture Use creation date (VBAK-ERDAT) and time (VBAK-ERZET) for the sales order document. Event type explicit | |||
| Customer Invoice Created | This activity marks the creation of the billing document for the customer based on the delivered goods or services. It initiates the final financial settlement part of the process. | ||
| Why it matters This activity is key for analyzing the billing cycle time. Delays between delivery confirmation and invoicing can negatively impact cash flow. Where to get This is an explicit event. The creation timestamp is recorded in the billing document header table (VBRK, field ERDAT). Capture Use the creation date (VBRK-ERDAT) from the billing document header. Event type explicit | |||
| Goods Unloaded at Destination | This activity signifies the physical unloading of goods at the customer's location. This event may not be explicitly tracked in SAP and often needs to be inferred from carrier data or subsequent events. | ||
| Why it matters This marks the end of the in-transit leg of the journey. The duration from goods issue to this point is the transportation time, which is key for logistics optimization. Where to get This is rarely an explicit event in the core ERP. It may be inferred from carrier EDI messages, data from the Transportation Management (TM) module, or the timestamp of the proof of delivery document if available. Capture Infer from carrier status updates (e.g., EDI 214) or use the Proof of Delivery timestamp as a close proxy. Event type inferred | |||
| Inventory Availability Checked | Represents the system or manual check to determine if the requested items are in stock to fulfill the sales order. This is often an automated step during sales order creation but may not be logged as a discrete event. | ||
| Why it matters Understanding the time between an order and this check, and its outcome, helps analyze how inventory levels impact fulfillment times. Delays here often lead to procurement or production activities. Where to get This is typically not an explicit event. It may be inferred from the creation of the first confirmed schedule line in the sales order item table (VBEP) or from status changes within the order item. Capture Infer from the confirmation status update on the sales order schedule line (VBEP table). Event type inferred | |||
| Outbound Delivery Created | Signifies the creation of a delivery document, which authorizes the picking and shipment of goods to the customer. This activity transitions the process from order management to logistics execution. | ||
| Why it matters This is a key step that initiates the physical fulfillment process. Delays between order creation and delivery creation can indicate planning or availability issues. Where to get This is an explicit event. The creation timestamp is recorded in the delivery header table (LIKP, field ERDAT). Capture Use the creation date (LIKP-ERDAT) and time (LIKP-ERZET) from the delivery document header. Event type explicit | |||
| Picking Completed | This activity marks the completion of the physical process of gathering items from the warehouse for the outbound delivery. In many systems, this is confirmed when the picking status of the delivery document is updated. | ||
| Why it matters Efficient picking is critical to warehouse throughput. Tracking this activity helps identify bottlenecks in warehouse operations and measure picking performance. Where to get This is often not a single discrete event but can be inferred from status changes. It is recorded when the picking status in the delivery item table (LIPS, field KOSTA) is set to 'C' (Completely processed). Capture Infer from change documents when the picking status (LIPS-KOSTA) is updated to fully picked. Event type inferred | |||
| Production Order Created | Indicates that a production order has been created to manufacture the finished goods required for the sales order. This is the formal start of the in-house manufacturing process. | ||
| Why it matters The creation of a production order marks the beginning of the manufacturing lead time. Analyzing this helps identify delays between demand signal and production start. Where to get This is an explicit event. The creation date is recorded in the production order header table (AUFK, field ERDAT). Capture Use the creation date (AUFK-ERDAT) from the order header table. Event type explicit | |||
| Purchase Requisition Created | This activity signifies the creation of an internal request to procure necessary goods or raw materials. It is often triggered when inventory is insufficient to fulfill a sales order or drops below a reorder point. | ||
| Why it matters This is the first step in the procurement cycle. Tracking its creation helps identify delays in initiating procurement and supports analysis of the end-to-end purchasing process. Where to get This is an explicit event. The creation timestamp is recorded in the purchase requisition header table (EBAN, field BADAT). Capture Use creation date (EBAN-BADAT) for the purchase requisition document. Event type explicit | |||
| Quality Inspection Performed | Represents the completion of a quality control check on produced goods. The outcome of this inspection, either pass or fail, is recorded in a usage decision. | ||
| Why it matters This activity is crucial for monitoring product quality and identifying production issues. High rejection rates or long inspection times can be significant bottlenecks. Where to get This is an explicit event captured when a usage decision is made for a quality inspection lot. The decision date is in the QALS table (field PASTRTERM) or QAVE table. Capture Use the timestamp when the usage decision is recorded for the inspection lot (QALS/QAVE tables). Event type explicit | |||
| Shipment Created | Represents the creation of a shipment document, which groups one or more deliveries for transportation planning. This document contains details about the carrier, route, and mode of transport. | ||
| Why it matters This activity is the start of formal transportation planning. Analyzing the time between goods issue and shipment completion reveals the efficiency of the transportation process. Where to get This is an explicit event recorded in the shipment header table (VTTK, field ERDAT). This is part of the LE-TRA module, which may not be used by all organizations. Capture Use the creation date (VTTK-ERDAT) from the shipment document header. Event type explicit | |||
Extraction Guides
Steps
- Prerequisites: Ensure you have a user with sufficient authorizations to access the required SAP S/4HANA Core Data Services (CDS) views. This typically requires a specific role assigned by your SAP security team. You will also need an SQL client, such as DBeaver or SAP HANA Studio, that can connect to the SAP HANA database.
- Establish Database Connection: Configure your SQL client to connect to the SAP S/4HANA database. You will need the database server host, port (e.g., 3xx15, where xx is the instance number), a database username, and a password.
- Prepare the SQL Query: Copy the complete SQL query provided in the query section of this document into your SQL client's editor. This query is designed to extract all specified activities from various logistics and sales modules.
- Set Extraction Parameters: Before running the query, locate the placeholder conditions within the
WHEREclauses of each subquery. You must replace'YourCompanyCode'with the actual company code you are analyzing and adjust the date range placeholders like'YYYY-MM-DD'to define the desired extraction period. - Execute the Query: Run the complete SQL script. The execution time will vary depending on the selected date range and the volume of data in your system. It is advisable to run this during off-peak hours to minimize system load.
- Review Initial Results: Once the query finishes, briefly scan the output in your SQL client. Check for a reasonable number of rows, ensure that columns like LogisticsOrder, ActivityName, and EventTime are populated, and confirm that various activity names appear.
- Export Data to CSV: Export the entire result set from your SQL client to a CSV file. Choose UTF-8 encoding to prevent issues with special characters.
- Format for Upload: Ensure the column headers in the final CSV file exactly match the attribute names required for ingestion (e.g.,
LogisticsOrder,ActivityName,EventTime). No further data transformation should be needed if the SQL query is used as provided.
Configuration
- Prerequisites: Access to the underlying SAP HANA database is required. The database user must have
SELECTprivileges on all CDS views referenced in the query, including but not limited to I_SalesOrderItem, I_PurchaseOrderItem, I_OutboundDeliveryItem, I_MaterialDocumentItem, I_ProductionOrder, I_QualityInspection, I_Shipment, and I_BillingDocumentItem. - Date Range Filtering: The provided query includes a placeholder for a date range filter, typically on a creation date field (
CreationDateorDocumentDate). For an initial analysis, a period of 3 to 6 months is recommended to ensure a representative dataset without causing excessive system load. - Key Business Filters: It is critical to filter the data by specific organizational units to ensure relevance. The query is preconfigured with a placeholder for
CompanyCode. You may also want to add filters forSalesOrganization,DistributionChannel, orPlantdepending on your analysis scope. - Performance Considerations: This is a complex query that joins multiple large CDS views. Execution can consume significant system resources. Schedule extractions during non-business hours. For very large datasets, consider running the query in smaller, sequential batches by month.
a Sample Query sql
WITH SalesOrderLink AS (
SELECT DISTINCT
sd.SalesDocument AS SalesOrder,
pr.PurchaseRequisition AS PurchaseRequisition,
po.PurchaseOrder AS PurchaseOrder,
od.DeliveryDocument AS OutboundDelivery,
bd.BillingDocument AS BillingDocument
FROM I_SalesDocItemProcessFlow AS pf
LEFT JOIN I_SalesDocumentItem AS sd ON pf.PrecedingDocument = sd.SalesDocument AND pf.PrecedingDocumentItem = sd.SalesDocumentItem
LEFT JOIN I_PurchaseRequisitionItem AS pr ON pf.SubsequentDocument = pr.PurchaseRequisition AND pf.SubsequentDocumentItem = pr.PurchaseRequisitionItem
LEFT JOIN I_PurchaseOrderItem AS po ON pf.SubsequentDocument = po.PurchaseOrder AND pf.SubsequentDocumentItem = po.PurchaseOrderItem
LEFT JOIN I_OutboundDeliveryItem AS od ON pf.SubsequentDocument = od.DeliveryDocument AND pf.SubsequentDocumentItem = od.DeliveryDocumentItem
LEFT JOIN I_BillingDocumentItem AS bd ON pf.SubsequentDocument = bd.BillingDocument AND pf.SubsequentDocumentItem = bd.BillingDocumentItem
WHERE sd.SalesDocument IS NOT NULL
)
SELECT
so.SalesOrder AS "LogisticsOrder",
'Sales Order Created' AS "ActivityName",
so.CreationDate || ' ' || so.CreationTime AS "EventTime",
so.CreatedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
soi.Material AS "MaterialNumber",
soi.Plant AS "Plant",
soi.RequestedDeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_SalesOrder AS so
JOIN I_SalesOrderItem AS soi ON so.SalesOrder = soi.SalesOrder
LEFT JOIN I_Customer AS cust ON so.SoldToParty = cust.Customer
WHERE so.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
UNION ALL
SELECT
so.SalesOrder AS "LogisticsOrder",
'Inventory Availability Checked' AS "ActivityName",
so.CreationDate || ' ' || so.CreationTime AS "EventTime",
so.CreatedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
soi.Material AS "MaterialNumber",
soi.Plant AS "Plant",
soi.RequestedDeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_SalesOrder AS so
JOIN I_SalesOrderItem AS soi ON so.SalesOrder = soi.SalesOrder
LEFT JOIN I_Customer AS cust ON so.SoldToParty = cust.Customer
WHERE so.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
UNION ALL
SELECT
sl.SalesOrder AS "LogisticsOrder",
'Purchase Requisition Created' AS "ActivityName",
pr.CreationDate || ' ' || '00:00:00' AS "EventTime",
pr.CreatedByUser AS "ExecutingUser",
NULL AS "CustomerName",
NULL AS "SupplierName",
pri.Material AS "MaterialNumber",
pri.Plant AS "Plant",
pri.DeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_PurchaseRequisition AS pr
JOIN I_PurchaseRequisitionItem AS pri ON pr.PurchaseRequisition = pri.PurchaseRequisition
JOIN SalesOrderLink sl ON pr.PurchaseRequisition = sl.PurchaseRequisition
WHERE pr.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND pr.CompanyCode = 'YourCompanyCode'
UNION ALL
SELECT
sl.SalesOrder AS "LogisticsOrder",
'Purchase Order Issued' AS "ActivityName",
po.PurchaseOrderDate || ' ' || '00:00:00' AS "EventTime",
po.CreatedByUser AS "ExecutingUser",
NULL AS "CustomerName",
sup.SupplierName AS "SupplierName",
poi.Material AS "MaterialNumber",
poi.Plant AS "Plant",
poi.DeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_PurchaseOrder AS po
JOIN I_PurchaseOrderItem AS poi ON po.PurchaseOrder = poi.PurchaseOrder
LEFT JOIN I_Supplier AS sup ON po.Supplier = sup.Supplier
JOIN SalesOrderLink sl ON po.PurchaseOrder = sl.PurchaseOrder
WHERE po.PurchaseOrderDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND po.CompanyCode = 'YourCompanyCode'
UNION ALL
SELECT
sl.SalesOrder AS "LogisticsOrder",
'Goods Receipt For PO Posted' AS "ActivityName",
md.DocumentDate || ' ' || md.CreationTime AS "EventTime",
md.CreatedByUser AS "ExecutingUser",
NULL AS "CustomerName",
sup.SupplierName AS "SupplierName",
mdi.Material AS "MaterialNumber",
mdi.Plant AS "Plant",
poi.DeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_MaterialDocumentHeader AS md
JOIN I_MaterialDocumentItem AS mdi ON md.MaterialDocument = mdi.MaterialDocument AND md.MaterialDocumentYear = mdi.MaterialDocumentYear
JOIN I_PurchaseOrderItem AS poi ON mdi.PurchaseOrder = poi.PurchaseOrder AND mdi.PurchaseOrderItem = poi.PurchaseOrderItem
LEFT JOIN I_Supplier AS sup ON poi.Supplier = sup.Supplier
JOIN SalesOrderLink sl ON poi.PurchaseOrder = sl.PurchaseOrder
WHERE md.DocumentDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND mdi.GoodsMovementType = '101' AND mdi.CompanyCode = 'YourCompanyCode'
UNION ALL
SELECT
sl.SalesOrder AS "LogisticsOrder",
'Production Order Created' AS "ActivityName",
po.CreationDate || ' ' || po.CreationTime AS "EventTime",
po.CreatedByUser AS "ExecutingUser",
NULL AS "CustomerName",
NULL AS "SupplierName",
po.Material AS "MaterialNumber",
po.ProductionPlant AS "Plant",
NULL AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_ProductionOrder AS po
JOIN SalesOrderLink sl ON po.SalesOrder = sl.SalesOrder
WHERE po.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
UNION ALL
SELECT
sl.SalesOrder AS "LogisticsOrder",
'Goods Produced' AS "ActivityName",
md.DocumentDate || ' ' || md.CreationTime AS "EventTime",
md.CreatedByUser AS "ExecutingUser",
NULL AS "CustomerName",
NULL AS "SupplierName",
mdi.Material AS "MaterialNumber",
mdi.Plant AS "Plant",
NULL AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_MaterialDocumentHeader AS md
JOIN I_MaterialDocumentItem AS mdi ON md.MaterialDocument = mdi.MaterialDocument AND md.MaterialDocumentYear = mdi.MaterialDocumentYear
JOIN I_ProductionOrder AS po ON mdi.ManufacturingOrder = po.ManufacturingOrder
JOIN SalesOrderLink sl ON po.SalesOrder = sl.SalesOrder
WHERE md.DocumentDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND mdi.GoodsMovementType = '101'
UNION ALL
SELECT
qi.SalesOrder AS "LogisticsOrder",
'Quality Inspection Performed' AS "ActivityName",
qi.InspLotUsageDecisionDate || ' ' || qi.InspLotUsageDecisionTime AS "EventTime",
qi.InspLotUsageDecisionMadeByUser AS "ExecutingUser",
NULL AS "CustomerName",
NULL AS "SupplierName",
qi.Material AS "MaterialNumber",
qi.Plant AS "Plant",
NULL AS "RequestedDeliveryDate",
qi.InspLotUsageDecisionCode AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_QualityInspection AS qi
WHERE qi.InspLotUsageDecisionDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND qi.SalesOrder IS NOT NULL
UNION ALL
SELECT
odi.SalesOrder AS "LogisticsOrder",
'Outbound Delivery Created' AS "ActivityName",
od.CreationDate || ' ' || od.CreationTime AS "EventTime",
od.CreatedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
odi.Material AS "MaterialNumber",
odi.Plant AS "Plant",
odi.RequestedDeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_OutboundDelivery AS od
JOIN I_OutboundDeliveryItem AS odi ON od.OutboundDelivery = odi.OutboundDelivery
LEFT JOIN I_Customer AS cust ON od.SoldToParty = cust.Customer
WHERE od.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
UNION ALL
SELECT
odi.SalesOrder AS "LogisticsOrder",
'Picking Completed' AS "ActivityName",
od.PickingDate || ' ' || od.PickingTime AS "EventTime",
od.LastChangedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
odi.Material AS "MaterialNumber",
odi.Plant AS "Plant",
odi.RequestedDeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_OutboundDelivery AS od
JOIN I_OutboundDeliveryItem AS odi ON od.OutboundDelivery = odi.OutboundDelivery
LEFT JOIN I_Customer AS cust ON od.SoldToParty = cust.Customer
WHERE od.PickingDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND od.OverallPickingStatus = 'C'
UNION ALL
SELECT
odi.SalesOrder AS "LogisticsOrder",
'Goods Issue Posted' AS "ActivityName",
od.ActualGoodsMovementDate || ' ' || od.ActualGoodsMovementTime AS "EventTime",
od.LastChangedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
odi.Material AS "MaterialNumber",
odi.Plant AS "Plant",
odi.RequestedDeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_OutboundDelivery AS od
JOIN I_OutboundDeliveryItem AS odi ON od.OutboundDelivery = odi.OutboundDelivery
LEFT JOIN I_Customer AS cust ON od.SoldToParty = cust.Customer
WHERE od.ActualGoodsMovementDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND od.OverallGoodsMovementStatus = 'C'
UNION ALL
SELECT
sl.SalesOrder AS "LogisticsOrder",
'Shipment Created' AS "ActivityName",
sh.CreationDate || ' ' || sh.CreationTime AS "EventTime",
sh.CreatedByUser AS "ExecutingUser",
NULL AS "CustomerName",
NULL AS "SupplierName",
NULL AS "MaterialNumber",
sh.ShippingPoint AS "Plant",
NULL AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_Shipment AS sh
JOIN I_ShipmentDelivery AS sd ON sh.Shipment = sd.Shipment
JOIN SalesOrderLink sl ON sd.Delivery = sl.OutboundDelivery
WHERE sh.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
UNION ALL
SELECT
odi.SalesOrder AS "LogisticsOrder",
'Proof Of Delivery Confirmed' AS "ActivityName",
od.PODActualDate || ' ' || '00:00:00' AS "EventTime",
od.LastChangedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
odi.Material AS "MaterialNumber",
odi.Plant AS "Plant",
odi.RequestedDeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_OutboundDelivery AS od
JOIN I_OutboundDeliveryItem AS odi ON od.OutboundDelivery = odi.OutboundDelivery
LEFT JOIN I_Customer AS cust ON od.SoldToParty = cust.Customer
WHERE od.PODActualDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND od.OverallPODStatus = 'C'
UNION ALL
SELECT
bdi.SalesDocument AS "LogisticsOrder",
'Customer Invoice Created' AS "ActivityName",
bd.BillingDocumentDate || ' ' || bd.CreationTime AS "EventTime",
bd.CreatedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
bdi.Material AS "MaterialNumber",
bdi.Plant AS "Plant",
NULL AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_BillingDocument AS bd
JOIN I_BillingDocumentItem AS bdi ON bd.BillingDocument = bdi.BillingDocument
LEFT JOIN I_Customer AS cust ON bd.SoldToParty = cust.Customer
WHERE bd.BillingDocumentDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND bd.CompanyCode = 'YourCompanyCode' AND bdi.SalesDocument IS NOT NULL;Steps
- Prerequisites: Ensure you have a user with sufficient authorizations to access the required SAP S/4HANA Core Data Services (CDS) views. This typically requires a specific role assigned by your SAP security team. You will also need an SQL client, such as DBeaver or SAP HANA Studio, that can connect to the SAP HANA database.
- Establish Database Connection: Configure your SQL client to connect to the SAP S/4HANA database. You will need the database server host, port (e.g., 3xx15, where xx is the instance number), a database username, and a password.
- Prepare the SQL Query: Copy the complete SQL query provided in the query section of this document into your SQL client's editor. This query is designed to extract all specified activities from various logistics and sales modules.
- Set Extraction Parameters: Before running the query, locate the placeholder conditions within the
WHEREclauses of each subquery. You must replace'YourCompanyCode'with the actual company code you are analyzing and adjust the date range placeholders like'YYYY-MM-DD'to define the desired extraction period. - Execute the Query: Run the complete SQL script. The execution time will vary depending on the selected date range and the volume of data in your system. It is advisable to run this during off-peak hours to minimize system load.
- Review Initial Results: Once the query finishes, briefly scan the output in your SQL client. Check for a reasonable number of rows, ensure that columns like LogisticsOrder, ActivityName, and EventTime are populated, and confirm that various activity names appear.
- Export Data to CSV: Export the entire result set from your SQL client to a CSV file. Choose UTF-8 encoding to prevent issues with special characters.
- Format for Upload: Ensure the column headers in the final CSV file exactly match the attribute names required for ingestion (e.g.,
LogisticsOrder,ActivityName,EventTime). No further data transformation should be needed if the SQL query is used as provided.
Configuration
- Prerequisites: Access to the underlying SAP HANA database is required. The database user must have
SELECTprivileges on all CDS views referenced in the query, including but not limited to I_SalesOrderItem, I_PurchaseOrderItem, I_OutboundDeliveryItem, I_MaterialDocumentItem, I_ProductionOrder, I_QualityInspection, I_Shipment, and I_BillingDocumentItem. - Date Range Filtering: The provided query includes a placeholder for a date range filter, typically on a creation date field (
CreationDateorDocumentDate). For an initial analysis, a period of 3 to 6 months is recommended to ensure a representative dataset without causing excessive system load. - Key Business Filters: It is critical to filter the data by specific organizational units to ensure relevance. The query is preconfigured with a placeholder for
CompanyCode. You may also want to add filters forSalesOrganization,DistributionChannel, orPlantdepending on your analysis scope. - Performance Considerations: This is a complex query that joins multiple large CDS views. Execution can consume significant system resources. Schedule extractions during non-business hours. For very large datasets, consider running the query in smaller, sequential batches by month.
a Sample Query sql
WITH SalesOrderLink AS (
SELECT DISTINCT
sd.SalesDocument AS SalesOrder,
pr.PurchaseRequisition AS PurchaseRequisition,
po.PurchaseOrder AS PurchaseOrder,
od.DeliveryDocument AS OutboundDelivery,
bd.BillingDocument AS BillingDocument
FROM I_SalesDocItemProcessFlow AS pf
LEFT JOIN I_SalesDocumentItem AS sd ON pf.PrecedingDocument = sd.SalesDocument AND pf.PrecedingDocumentItem = sd.SalesDocumentItem
LEFT JOIN I_PurchaseRequisitionItem AS pr ON pf.SubsequentDocument = pr.PurchaseRequisition AND pf.SubsequentDocumentItem = pr.PurchaseRequisitionItem
LEFT JOIN I_PurchaseOrderItem AS po ON pf.SubsequentDocument = po.PurchaseOrder AND pf.SubsequentDocumentItem = po.PurchaseOrderItem
LEFT JOIN I_OutboundDeliveryItem AS od ON pf.SubsequentDocument = od.DeliveryDocument AND pf.SubsequentDocumentItem = od.DeliveryDocumentItem
LEFT JOIN I_BillingDocumentItem AS bd ON pf.SubsequentDocument = bd.BillingDocument AND pf.SubsequentDocumentItem = bd.BillingDocumentItem
WHERE sd.SalesDocument IS NOT NULL
)
SELECT
so.SalesOrder AS "LogisticsOrder",
'Sales Order Created' AS "ActivityName",
so.CreationDate || ' ' || so.CreationTime AS "EventTime",
so.CreatedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
soi.Material AS "MaterialNumber",
soi.Plant AS "Plant",
soi.RequestedDeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_SalesOrder AS so
JOIN I_SalesOrderItem AS soi ON so.SalesOrder = soi.SalesOrder
LEFT JOIN I_Customer AS cust ON so.SoldToParty = cust.Customer
WHERE so.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
UNION ALL
SELECT
so.SalesOrder AS "LogisticsOrder",
'Inventory Availability Checked' AS "ActivityName",
so.CreationDate || ' ' || so.CreationTime AS "EventTime",
so.CreatedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
soi.Material AS "MaterialNumber",
soi.Plant AS "Plant",
soi.RequestedDeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_SalesOrder AS so
JOIN I_SalesOrderItem AS soi ON so.SalesOrder = soi.SalesOrder
LEFT JOIN I_Customer AS cust ON so.SoldToParty = cust.Customer
WHERE so.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
UNION ALL
SELECT
sl.SalesOrder AS "LogisticsOrder",
'Purchase Requisition Created' AS "ActivityName",
pr.CreationDate || ' ' || '00:00:00' AS "EventTime",
pr.CreatedByUser AS "ExecutingUser",
NULL AS "CustomerName",
NULL AS "SupplierName",
pri.Material AS "MaterialNumber",
pri.Plant AS "Plant",
pri.DeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_PurchaseRequisition AS pr
JOIN I_PurchaseRequisitionItem AS pri ON pr.PurchaseRequisition = pri.PurchaseRequisition
JOIN SalesOrderLink sl ON pr.PurchaseRequisition = sl.PurchaseRequisition
WHERE pr.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND pr.CompanyCode = 'YourCompanyCode'
UNION ALL
SELECT
sl.SalesOrder AS "LogisticsOrder",
'Purchase Order Issued' AS "ActivityName",
po.PurchaseOrderDate || ' ' || '00:00:00' AS "EventTime",
po.CreatedByUser AS "ExecutingUser",
NULL AS "CustomerName",
sup.SupplierName AS "SupplierName",
poi.Material AS "MaterialNumber",
poi.Plant AS "Plant",
poi.DeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_PurchaseOrder AS po
JOIN I_PurchaseOrderItem AS poi ON po.PurchaseOrder = poi.PurchaseOrder
LEFT JOIN I_Supplier AS sup ON po.Supplier = sup.Supplier
JOIN SalesOrderLink sl ON po.PurchaseOrder = sl.PurchaseOrder
WHERE po.PurchaseOrderDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND po.CompanyCode = 'YourCompanyCode'
UNION ALL
SELECT
sl.SalesOrder AS "LogisticsOrder",
'Goods Receipt For PO Posted' AS "ActivityName",
md.DocumentDate || ' ' || md.CreationTime AS "EventTime",
md.CreatedByUser AS "ExecutingUser",
NULL AS "CustomerName",
sup.SupplierName AS "SupplierName",
mdi.Material AS "MaterialNumber",
mdi.Plant AS "Plant",
poi.DeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_MaterialDocumentHeader AS md
JOIN I_MaterialDocumentItem AS mdi ON md.MaterialDocument = mdi.MaterialDocument AND md.MaterialDocumentYear = mdi.MaterialDocumentYear
JOIN I_PurchaseOrderItem AS poi ON mdi.PurchaseOrder = poi.PurchaseOrder AND mdi.PurchaseOrderItem = poi.PurchaseOrderItem
LEFT JOIN I_Supplier AS sup ON poi.Supplier = sup.Supplier
JOIN SalesOrderLink sl ON poi.PurchaseOrder = sl.PurchaseOrder
WHERE md.DocumentDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND mdi.GoodsMovementType = '101' AND mdi.CompanyCode = 'YourCompanyCode'
UNION ALL
SELECT
sl.SalesOrder AS "LogisticsOrder",
'Production Order Created' AS "ActivityName",
po.CreationDate || ' ' || po.CreationTime AS "EventTime",
po.CreatedByUser AS "ExecutingUser",
NULL AS "CustomerName",
NULL AS "SupplierName",
po.Material AS "MaterialNumber",
po.ProductionPlant AS "Plant",
NULL AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_ProductionOrder AS po
JOIN SalesOrderLink sl ON po.SalesOrder = sl.SalesOrder
WHERE po.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
UNION ALL
SELECT
sl.SalesOrder AS "LogisticsOrder",
'Goods Produced' AS "ActivityName",
md.DocumentDate || ' ' || md.CreationTime AS "EventTime",
md.CreatedByUser AS "ExecutingUser",
NULL AS "CustomerName",
NULL AS "SupplierName",
mdi.Material AS "MaterialNumber",
mdi.Plant AS "Plant",
NULL AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_MaterialDocumentHeader AS md
JOIN I_MaterialDocumentItem AS mdi ON md.MaterialDocument = mdi.MaterialDocument AND md.MaterialDocumentYear = mdi.MaterialDocumentYear
JOIN I_ProductionOrder AS po ON mdi.ManufacturingOrder = po.ManufacturingOrder
JOIN SalesOrderLink sl ON po.SalesOrder = sl.SalesOrder
WHERE md.DocumentDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND mdi.GoodsMovementType = '101'
UNION ALL
SELECT
qi.SalesOrder AS "LogisticsOrder",
'Quality Inspection Performed' AS "ActivityName",
qi.InspLotUsageDecisionDate || ' ' || qi.InspLotUsageDecisionTime AS "EventTime",
qi.InspLotUsageDecisionMadeByUser AS "ExecutingUser",
NULL AS "CustomerName",
NULL AS "SupplierName",
qi.Material AS "MaterialNumber",
qi.Plant AS "Plant",
NULL AS "RequestedDeliveryDate",
qi.InspLotUsageDecisionCode AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_QualityInspection AS qi
WHERE qi.InspLotUsageDecisionDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND qi.SalesOrder IS NOT NULL
UNION ALL
SELECT
odi.SalesOrder AS "LogisticsOrder",
'Outbound Delivery Created' AS "ActivityName",
od.CreationDate || ' ' || od.CreationTime AS "EventTime",
od.CreatedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
odi.Material AS "MaterialNumber",
odi.Plant AS "Plant",
odi.RequestedDeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_OutboundDelivery AS od
JOIN I_OutboundDeliveryItem AS odi ON od.OutboundDelivery = odi.OutboundDelivery
LEFT JOIN I_Customer AS cust ON od.SoldToParty = cust.Customer
WHERE od.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
UNION ALL
SELECT
odi.SalesOrder AS "LogisticsOrder",
'Picking Completed' AS "ActivityName",
od.PickingDate || ' ' || od.PickingTime AS "EventTime",
od.LastChangedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
odi.Material AS "MaterialNumber",
odi.Plant AS "Plant",
odi.RequestedDeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_OutboundDelivery AS od
JOIN I_OutboundDeliveryItem AS odi ON od.OutboundDelivery = odi.OutboundDelivery
LEFT JOIN I_Customer AS cust ON od.SoldToParty = cust.Customer
WHERE od.PickingDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND od.OverallPickingStatus = 'C'
UNION ALL
SELECT
odi.SalesOrder AS "LogisticsOrder",
'Goods Issue Posted' AS "ActivityName",
od.ActualGoodsMovementDate || ' ' || od.ActualGoodsMovementTime AS "EventTime",
od.LastChangedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
odi.Material AS "MaterialNumber",
odi.Plant AS "Plant",
odi.RequestedDeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_OutboundDelivery AS od
JOIN I_OutboundDeliveryItem AS odi ON od.OutboundDelivery = odi.OutboundDelivery
LEFT JOIN I_Customer AS cust ON od.SoldToParty = cust.Customer
WHERE od.ActualGoodsMovementDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND od.OverallGoodsMovementStatus = 'C'
UNION ALL
SELECT
sl.SalesOrder AS "LogisticsOrder",
'Shipment Created' AS "ActivityName",
sh.CreationDate || ' ' || sh.CreationTime AS "EventTime",
sh.CreatedByUser AS "ExecutingUser",
NULL AS "CustomerName",
NULL AS "SupplierName",
NULL AS "MaterialNumber",
sh.ShippingPoint AS "Plant",
NULL AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_Shipment AS sh
JOIN I_ShipmentDelivery AS sd ON sh.Shipment = sd.Shipment
JOIN SalesOrderLink sl ON sd.Delivery = sl.OutboundDelivery
WHERE sh.CreationDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
UNION ALL
SELECT
odi.SalesOrder AS "LogisticsOrder",
'Proof Of Delivery Confirmed' AS "ActivityName",
od.PODActualDate || ' ' || '00:00:00' AS "EventTime",
od.LastChangedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
odi.Material AS "MaterialNumber",
odi.Plant AS "Plant",
odi.RequestedDeliveryDate AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_OutboundDelivery AS od
JOIN I_OutboundDeliveryItem AS odi ON od.OutboundDelivery = odi.OutboundDelivery
LEFT JOIN I_Customer AS cust ON od.SoldToParty = cust.Customer
WHERE od.PODActualDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND od.OverallPODStatus = 'C'
UNION ALL
SELECT
bdi.SalesDocument AS "LogisticsOrder",
'Customer Invoice Created' AS "ActivityName",
bd.BillingDocumentDate || ' ' || bd.CreationTime AS "EventTime",
bd.CreatedByUser AS "ExecutingUser",
cust.CustomerName AS "CustomerName",
NULL AS "SupplierName",
bdi.Material AS "MaterialNumber",
bdi.Plant AS "Plant",
NULL AS "RequestedDeliveryDate",
NULL AS "QualityInspectionResult",
'SAP S/4HANA' AS "SourceSystem",
CURRENT_UTCTIMESTAMP AS "LastDataUpdate"
FROM I_BillingDocument AS bd
JOIN I_BillingDocumentItem AS bdi ON bd.BillingDocument = bdi.BillingDocument
LEFT JOIN I_Customer AS cust ON bd.SoldToParty = cust.Customer
WHERE bd.BillingDocumentDate BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND bd.CompanyCode = 'YourCompanyCode' AND bdi.SalesDocument IS NOT NULL;Steps
- Prerequisites: Ensure you have a developer key and the necessary authorizations in your SAP S/4HANA system to create and execute ABAP programs (access to transaction SE38) and read from supply chain tables like VBAK, LIKP, EKKO, and MKPF.
- Create Program: Open the ABAP Editor using transaction code
SE38. Enter a name for the new program, for example,Z_PM_SCM_EXTRACTION, and click 'Create'. Provide a descriptive title and set the program type to 'Executable Program'. - Insert Code: Copy the complete ABAP code from the 'query' section below and paste it into the ABAP Editor for your new program.
- Define Text Elements: Navigate to Go To > Text Elements > Selection Texts. Activate all proposed selection screen labels to ensure the user interface is clear and user-friendly.
- Activate Program: Save and activate the program by pressing CTRL+S and then CTRL+F3, or by using the activate icon in the toolbar. Address any syntax errors that may arise due to system-specific configurations.
- Execute Program: Run the program by pressing F8 or using the 'Direct Processing' button. A selection screen will appear, allowing you to filter the data to be extracted.
- Set Extraction Parameters: On the selection screen, specify the date range for Sales Order Creation Date. It is highly recommended to also filter by specific Company Codes or Sales Organizations to manage the data volume. Enter the full path for the output file on the SAP application server.
- Run in Background: For large datasets covering several months, it is crucial to execute the program in the background to prevent transaction timeouts. From the menu, choose Program > Execute in Background and schedule the job.
- Retrieve Data File: Once the program execution is complete, use transaction code
AL11to navigate to the directory on the application server you specified. Locate the generated file and download it to your local machine using transactionCG3Y. - Prepare for Upload: Ensure the downloaded file is in a plain text format, such as CSV with comma or semicolon delimiters, and uses UTF-8 encoding. The file is now ready to be uploaded to the process mining tool.
Configuration
- Selection Date Range (S_ERDAT): This is the most critical filter. It defines the time window for the initial selection of sales orders based on their creation date (
VBAK-ERDAT). Start with a focused period, like the last 3 to 6 months, to ensure manageable data volumes. - Sales Organization (S_VKORG): An optional filter to restrict the extraction to specific sales organizations. This is useful for focusing the analysis on a particular business unit or region.
- Company Code (S_BUKRS): An optional filter to limit the data to specific company codes. This is highly recommended to reduce the data scope and improve performance.
- Output File Path (P_FILE): The full path on the SAP application server where the final event log file will be saved. The user executing the program must have write access to this directory. Example:
/usr/sap/trans/tmp/scm_event_log.csv. - Execution Mode: The program can be run in the foreground for small test extractions. For any significant data volume, background processing is mandatory to avoid timeouts and reduce the load on the system during peak hours.
- System Authorizations: The user executing the report requires read access to all underlying tables, including but not limited to VBAK, VBAP, EKKO, EKPO, MSEG, MKPF, LIKP, LIPS, VBRK, AUFK, QAVE, KNA1, and LFA1.
a Sample Query abap
REPORT Z_PM_SCM_EXTRACTION.
*&---------------------------------------------------------------------*
*& Selection Screen
*&---------------------------------------------------------------------*
PARAMETERS: p_file TYPE string LOWER CASE DEFAULT '/usr/sap/trans/tmp/scm_event_log.csv'.
SELECT-OPTIONS: s_erdat FOR sy-datum OBLIGATORY,
s_vkorg FOR vbak-vkorg,
s_bukrs FOR vbak-bukrs.
*&---------------------------------------------------------------------*
*& Data Type Definitions
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_event_log,
LogisticsOrder TYPE vbeln_va,
ActivityName TYPE string,
EventTime TYPE string,
SourceSystem TYPE sysysid,
LastDataUpdate TYPE string,
ExecutingUser TYPE ernam,
SupplierName TYPE name1_gp,
CustomerName TYPE name1_gp,
MaterialNumber TYPE matnr,
Plant TYPE werks_d,
RequestedDeliveryDate TYPE vdatu,
QualityInspectionResult TYPE string,
END OF ty_event_log.
*&---------------------------------------------------------------------*
*& Data Declarations
*&---------------------------------------------------------------------*
DATA: lt_event_log TYPE TABLE OF ty_event_log,
ls_event_log TYPE ty_event_log,
lv_sysid TYPE sysysid,
lv_last_update TYPE string.
*&---------------------------------------------------------------------*
*& START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
lv_sysid = sy-sysid.
CONCATENATE sy-datum sy-uzeit INTO lv_last_update.
" Select base data: Sales Orders
SELECT h~vbeln, h~erdat, h~erzet, h~ernam, h~kunnr, h~bukrs, h~vdatu, i~posnr, i~matnr, i~werks
INTO TABLE @DATA(lt_so)
FROM vbak AS h
INNER JOIN vbap AS i ON h~vbeln = i~vbeln
WHERE h~erdat IN @s_erdat
AND h~vkorg IN @s_vkorg
AND h~bukrs IN @s_bukrs.
IF lt_so IS INITIAL.
MESSAGE 'No sales orders found for the given criteria.' TYPE 'I'.
RETURN.
ENDIF.
" Select related documents using Document Flow (VBFA)
SELECT *
INTO TABLE @DATA(lt_vbfa)
FROM vbfa
FOR ALL ENTRIES IN @lt_so
WHERE vbelv = @lt_so-vbeln
AND posnv = @lt_so-posnr.
" Collect all unique document numbers
DATA: lt_vbeln_pr TYPE RANGE OF banfn,
lt_vbeln_po TYPE RANGE OF ebeln,
lt_vbeln_dn TYPE RANGE OF vbeln_vl,
lt_vbeln_gi TYPE RANGE OF mblnr,
lt_vbeln_auf TYPE RANGE OF aufnr,
lt_vbeln_inv TYPE RANGE OF vbeln_vf,
lt_vbeln_shp TYPE RANGE OF tknum.
LOOP AT lt_vbfa INTO DATA(ls_vbfa).
CASE ls_vbfa-vbtyp_n.
WHEN 'H'. " Purchase Requisition
APPEND ls_vbfa-vbeln TO lt_vbeln_pr.
WHEN 'K'. " Purchase Order
APPEND ls_vbfa-vbeln TO lt_vbeln_po.
WHEN 'J'. " Delivery
APPEND ls_vbfa-vbeln TO lt_vbeln_dn.
WHEN 'R'. " Goods Movement (GI)
APPEND ls_vbfa-vbeln TO lt_vbeln_gi.
WHEN 'L'. " Production Order
APPEND ls_vbfa-vbeln TO lt_vbeln_auf.
WHEN 'M'. " Invoice
APPEND ls_vbfa-vbeln TO lt_vbeln_inv.
WHEN '8'. " Shipment
APPEND ls_vbfa-vbeln TO lt_vbeln_shp.
ENDCASE.
ENDLOOP.
SORT lt_vbeln_pr. DELETE ADJACENT DUPLICATES FROM lt_vbeln_pr.
SORT lt_vbeln_po. DELETE ADJACENT DUPLICATES FROM lt_vbeln_po.
SORT lt_vbeln_dn. DELETE ADJACENT DUPLICATES FROM lt_vbeln_dn.
SORT lt_vbeln_gi. DELETE ADJACENT DUPLICATES FROM lt_vbeln_gi.
SORT lt_vbeln_auf. DELETE ADJACENT DUPLICATES FROM lt_vbeln_auf.
SORT lt_vbeln_inv. DELETE ADJACENT DUPLICATES FROM lt_vbeln_inv.
SORT lt_vbeln_shp. DELETE ADJACENT DUPLICATES FROM lt_vbeln_shp.
" Select detailed data for each document type
SELECT banfn, badat, ernam FROM eban INTO TABLE @DATA(lt_eban) FOR ALL ENTRIES IN @lt_so WHERE bnfpo = @lt_so-posnr AND banfn IN @lt_vbeln_pr.
SELECT ebeln, aedat, ernam, lifnr FROM ekko INTO TABLE @DATA(lt_ekko) WHERE ebeln IN @lt_vbeln_po.
SELECT vbeln, erdat, erzet, ernam, kodat, wadat_ist, podat FROM likp INTO TABLE @DATA(lt_likp) WHERE vbeln IN @lt_vbeln_dn.
SELECT mblnr, mjahr, budat, usnam FROM mkpf INTO TABLE @DATA(lt_mkpf) WHERE mblnr IN @lt_vbeln_gi.
SELECT mblnr, mjahr, zeile, bwart, lfbnr, ebeln, aufnr FROM mseg INTO TABLE @DATA(lt_mseg) FOR ALL ENTRIES IN @lt_mkpf WHERE mblnr = @lt_mkpf-mblnr AND mjahr = @lt_mkpf-mjahr.
SELECT aufnr, erdat, ernam FROM aufk INTO TABLE @DATA(lt_aufk) WHERE aufnr IN @lt_vbeln_auf.
SELECT prueflos, vdatum, vcode FROM qave INTO TABLE @DATA(lt_qave) FOR ALL ENTRIES IN @lt_so WHERE aufnr IN @lt_vbeln_auf.
SELECT vbeln, erdat, erzet, ernam FROM vbrk INTO TABLE @DATA(lt_vbrk) WHERE vbeln IN @lt_vbeln_inv.
SELECT tknum, erdat, erzet FROM vttk INTO TABLE @DATA(lt_vttk) WHERE tknum IN @lt_vbeln_shp.
SELECT kunnr, name1 FROM kna1 INTO TABLE @DATA(lt_kna1) FOR ALL ENTRIES IN @lt_so WHERE kunnr = @lt_so-kunnr.
SELECT lifnr, name1 FROM lfa1 INTO TABLE @DATA(lt_lfa1) FOR ALL ENTRIES IN @lt_ekko WHERE lifnr = @lt_ekko-lifnr.
" Assemble Event Log
LOOP AT lt_so INTO DATA(ls_so).
CLEAR ls_event_log.
READ TABLE lt_kna1 INTO DATA(ls_kna1) WITH KEY kunnr = ls_so-kunnr BINARY SEARCH.
ls_event_log-LogisticsOrder = ls_so-vbeln.
ls_event_log-SourceSystem = lv_sysid.
ls_event_log-LastDataUpdate = lv_last_update.
ls_event_log-CustomerName = ls_kna1-name1.
ls_event_log-MaterialNumber = ls_so-matnr.
ls_event_log-Plant = ls_so-werks.
ls_event_log-RequestedDeliveryDate = ls_so-vdatu.
" 1. Sales Order Created
ls_event_log-ActivityName = 'Sales Order Created'.
CONCATENATE ls_so-erdat ls_so-erzet INTO ls_event_log-EventTime.
ls_event_log-ExecutingUser = ls_so-ernam.
APPEND ls_event_log TO lt_event_log.
" 2. Inventory Availability Checked (proxy event)
ls_event_log-ActivityName = 'Inventory Availability Checked'.
CONCATENATE ls_so-erdat ls_so-erzet INTO ls_event_log-EventTime. " Using SO creation time as a proxy
ls_event_log-ExecutingUser = ls_so-ernam.
APPEND ls_event_log TO lt_event_log.
" Find related documents for this SO item
LOOP AT lt_vbfa INTO ls_vbfa WHERE vbelv = ls_so-vbeln AND posnv = ls_so-posnr.
CASE ls_vbfa-vbtyp_n.
WHEN 'H'. " 3. Purchase Requisition Created
READ TABLE lt_eban INTO DATA(ls_eban) WITH KEY banfn = ls_vbfa-vbeln.
IF sy-subrc = 0.
ls_event_log-ActivityName = 'Purchase Requisition Created'.
CONCATENATE ls_eban-badat '000000' INTO ls_event_log-EventTime. " PR has no time field
ls_event_log-ExecutingUser = ls_eban-ernam.
APPEND ls_event_log TO lt_event_log.
ENDIF.
WHEN 'K'. " 4. Purchase Order Issued
READ TABLE lt_ekko INTO DATA(ls_ekko) WITH KEY ebeln = ls_vbfa-vbeln.
IF sy-subrc = 0.
READ TABLE lt_lfa1 INTO DATA(ls_lfa1) WITH KEY lifnr = ls_ekko-lifnr BINARY SEARCH.
ls_event_log-ActivityName = 'Purchase Order Issued'.
CONCATENATE ls_ekko-aedat '000000' INTO ls_event_log-EventTime.
ls_event_log-ExecutingUser = ls_ekko-ernam.
ls_event_log-SupplierName = ls_lfa1-name1.
APPEND ls_event_log TO lt_event_log.
ENDIF.
WHEN 'L'. " 6. Production Order Created
READ TABLE lt_aufk INTO DATA(ls_aufk) WITH KEY aufnr = ls_vbfa-vbeln.
IF sy-subrc = 0.
ls_event_log-ActivityName = 'Production Order Created'.
CONCATENATE ls_aufk-erdat '000000' INTO ls_event_log-EventTime.
ls_event_log-ExecutingUser = ls_aufk-ernam.
APPEND ls_event_log TO lt_event_log.
" 8. Quality Inspection Performed
READ TABLE lt_qave INTO DATA(ls_qave) WITH KEY prueflos = ls_vbfa-vbeln. " Approximation linking lot to order
IF sy-subrc = 0.
ls_event_log-ActivityName = 'Quality Inspection Performed'.
CONCATENATE ls_qave-vdatum '000000' INTO ls_event_log-EventTime.
ls_event_log-QualityInspectionResult = ls_qave-vcode.
APPEND ls_event_log TO lt_event_log.
CLEAR ls_event_log-QualityInspectionResult.
ENDIF.
ENDIF.
WHEN 'J'. " 9. Outbound Delivery Created
READ TABLE lt_likp INTO DATA(ls_likp) WITH KEY vbeln = ls_vbfa-vbeln.
IF sy-subrc = 0.
ls_event_log-ActivityName = 'Outbound Delivery Created'.
CONCATENATE ls_likp-erdat ls_likp-erzet INTO ls_event_log-EventTime.
ls_event_log-ExecutingUser = ls_likp-ernam.
APPEND ls_event_log TO lt_event_log.
" 10. Picking Completed
IF ls_likp-kodat IS NOT INITIAL.
ls_event_log-ActivityName = 'Picking Completed'.
CONCATENATE ls_likp-kodat '120000' INTO ls_event_log-EventTime. " Using Picking Date as proxy
APPEND ls_event_log TO lt_event_log.
ENDIF.
" 14. Proof Of Delivery Confirmed
IF ls_likp-podat IS NOT INITIAL.
ls_event_log-ActivityName = 'Proof Of Delivery Confirmed'.
CONCATENATE ls_likp-podat '000000' INTO ls_event_log-EventTime.
APPEND ls_event_log TO lt_event_log.
ENDIF.
ENDIF.
WHEN 'M'. " 15. Customer Invoice Created
READ TABLE lt_vbrk INTO DATA(ls_vbrk) WITH KEY vbeln = ls_vbfa-vbeln.
IF sy-subrc = 0.
ls_event_log-ActivityName = 'Customer Invoice Created'.
CONCATENATE ls_vbrk-erdat ls_vbrk-erzet INTO ls_event_log-EventTime.
ls_event_log-ExecutingUser = ls_vbrk-ernam.
APPEND ls_event_log TO lt_event_log.
ENDIF.
WHEN '8'. " 12. Shipment Created
READ TABLE lt_vttk INTO DATA(ls_vttk) WITH KEY tknum = ls_vbfa-vbeln.
IF sy-subrc = 0.
ls_event_log-ActivityName = 'Shipment Created'.
CONCATENATE ls_vttk-erdat ls_vttk-erzet INTO ls_event_log-EventTime.
APPEND ls_event_log TO lt_event_log.
ENDIF.
ENDCASE.
ENDLOOP.
" Find material movements (MSEG) not directly in VBFA
" 5. Goods Receipt For PO Posted
LOOP AT lt_mseg INTO DATA(ls_mseg_po) WHERE ebeln IN (SELECT ebeln FROM ekpo WHERE banfn IN (SELECT banfn FROM eban WHERE vbeln = ls_so-vbeln) ) AND bwart = '101'.
READ TABLE lt_mkpf INTO DATA(ls_mkpf_po) WITH KEY mblnr = ls_mseg_po-mblnr mjahr = ls_mseg_po-mjahr.
IF sy-subrc = 0.
ls_event_log-ActivityName = 'Goods Receipt For PO Posted'.
CONCATENATE ls_mkpf_po-budat '000000' INTO ls_event_log-EventTime.
ls_event_log-ExecutingUser = ls_mkpf_po-usnam.
APPEND ls_event_log TO lt_event_log.
ENDIF.
ENDLOOP.
" 7. Goods Produced
LOOP AT lt_mseg INTO DATA(ls_mseg_pp) WHERE aufnr IN (SELECT aufnr FROM afpo WHERE kdauf = ls_so-vbeln) AND bwart = '101'.
READ TABLE lt_mkpf INTO DATA(ls_mkpf_pp) WITH KEY mblnr = ls_mseg_pp-mblnr mjahr = ls_mseg_pp-mjahr.
IF sy-subrc = 0.
ls_event_log-ActivityName = 'Goods Produced'.
CONCATENATE ls_mkpf_pp-budat '000000' INTO ls_event_log-EventTime.
ls_event_log-ExecutingUser = ls_mkpf_pp-usnam.
APPEND ls_event_log TO lt_event_log.
ENDIF.
ENDLOOP.
" 11. Goods Issue Posted
LOOP AT lt_mseg INTO DATA(ls_mseg_gi) WHERE lfbnr IN (SELECT vbeln FROM lips WHERE vgbel = ls_so-vbeln) AND bwart = '601'.
READ TABLE lt_mkpf INTO DATA(ls_mkpf_gi) WITH KEY mblnr = ls_mseg_gi-mblnr mjahr = ls_mseg_gi-mjahr.
IF sy-subrc = 0.
ls_event_log-ActivityName = 'Goods Issue Posted'.
CONCATENATE ls_mkpf_gi-budat '000000' INTO ls_event_log-EventTime.
ls_event_log-ExecutingUser = ls_mkpf_gi-usnam.
APPEND ls_event_log TO lt_event_log.
ENDIF.
ENDLOOP.
ENDLOOP.
" Remove duplicate events for the same case
SORT lt_event_log BY LogisticsOrder ActivityName EventTime.
DELETE ADJACENT DUPLICATES FROM lt_event_log COMPARING LogisticsOrder ActivityName EventTime.
" Write data to file
DATA: lt_output TYPE TABLE OF string.
APPEND 'LogisticsOrder,ActivityName,EventTime,SourceSystem,LastDataUpdate,ExecutingUser,SupplierName,CustomerName,MaterialNumber,Plant,RequestedDeliveryDate,QualityInspectionResult' TO lt_output.
LOOP AT lt_event_log INTO ls_event_log.
DATA(lv_line) = |
{ ls_event_log-LogisticsOrder },
{ ls_event_log-ActivityName },
{ ls_event_log-EventTime },
{ ls_event_log-SourceSystem },
{ ls_event_log-LastDataUpdate },
{ ls_event_log-ExecutingUser },
{ ls_event_log-SupplierName },
{ ls_event_log-CustomerName },
{ ls_event_log-MaterialNumber },
{ ls_event_log-Plant },
{ ls_event_log-RequestedDeliveryDate },
{ ls_event_log-QualityInspectionResult }|
.
REPLACE ALL OCCURRENCES OF ',' IN lv_line WITH ' '.
REPLACE ALL OCCURRENCES OF REGEX '\s+' IN lv_line WITH '' LEADING.
CONDENSE lv_line.
APPEND lv_line TO lt_output.
ENDLOOP.
cl_gui_frontend_services=>gui_download(
EXPORTING
filename = p_file
filetype = 'ASC'
CHANGING
data_tab = lt_output
EXCEPTIONS
OTHERS = 24
).
IF sy-subrc <> 0.
MESSAGE 'Error downloading file.' TYPE 'E'.
ELSE.
MESSAGE |File downloaded successfully to { p_file }| TYPE 'S'.
ENDIF.