Your Purchase to Pay - Purchase Order Data Template
Your Purchase to Pay - Purchase Order Data Template
- Recommended attributes for detailed analysis
- Key activities to track within the process
- Step-by-step data extraction guidance
Purchase to Pay - Purchase Order Attributes
| Name | Description | ||
|---|---|---|---|
| Activity ActivityName | The name of the business event or step that occurred in the purchase order process. | ||
| Description This attribute describes a specific action or status change within the purchase order's lifecycle, such as 'Purchase Order Created', 'Purchase Order Approved', or 'Goods Receipt Posted'. The sequence of these activities forms the process flow. Analyzing the sequence and frequency of activities is the core of process mining. It helps to discover the actual process, compare it against the designed model, identify bottlenecks (e.g., long waits after 'Invoice Received'), and quantify rework (e.g., repeated 'Purchase Order Changed' activities). Why it matters It defines the steps in the process, allowing for the visualization and analysis of the end-to-end flow, variant analysis, and bottleneck identification. Where to get Typically derived from a combination of tables and fields, such as status fields in EKKO/EKPO or change document logs in CDHDR/CDPOS, to represent key business milestones. Examples Purchase Order CreatedPurchase Order ApprovedGoods Receipt PostedInvoice Received | |||
| Event Time EventTime | The timestamp indicating when the activity occurred. | ||
| Description This attribute records the exact date and time of each activity in the process. It is fundamental for all time-based analysis in process mining. Event Time is used to order the activities chronologically to build the process flow. Furthermore, it is the basis for calculating all duration-based metrics, such as cycle times between activities, waiting times, and processing times, which are critical for performance analysis and bottleneck identification. Why it matters This timestamp is critical for ordering events correctly and calculating all performance metrics, including cycle times, lead times, and waiting times. Where to get Timestamp fields associated with specific activities, such as Creation Date (EKKO-AEDAT for changes) or Posting Date (MKPF-BUDAT for goods receipts). Often requires combining data from multiple tables. Examples 2023-04-15T10:00:00Z2023-04-15T14:30:00Z2023-05-01T09:15:00Z | |||
| Purchase Order PurchaseOrderNumber | The unique identifier for the Purchase Order (PO), serving as the primary case ID for tracking the procurement lifecycle. | ||
| Description The Purchase Order Number is the central identifier that links all related activities, from the initial creation to the final goods receipt and completion. It acts as the case identifier for process mining analysis. In analysis, grouping events by this number allows for the reconstruction of each individual PO's journey. This is essential for calculating cycle times, analyzing process variants, and identifying bottlenecks or deviations specific to a single order. Why it matters It is the essential key to connect all procurement events into a single end-to-end process, enabling a detailed analysis of each purchase order's lifecycle. Where to get This attribute can be found in the SAP S/4HANA table EKKO, field EBELN. Examples 450001712345000171244500017125 | |||
| Last Data Update LastDataUpdate | The timestamp when the data was last refreshed or extracted from the source system. | ||
| Description This attribute indicates the freshness of the data being analyzed. It shows the date and time of the most recent data pull from SAP S/4HANA. Knowing the last data update time is vital for users to understand the timeliness of their analysis. It helps them interpret the findings correctly, knowing whether they are looking at real-time information or a snapshot from a specific point in time, which affects the relevance of any actions taken based on the analysis. Why it matters Informs users about the timeliness of the data, ensuring they understand the context and relevance of their analytical findings. Where to get This is a metadata timestamp added during the data extraction, transformation, and loading (ETL) process. Examples 2024-05-21T02:00:00Z2024-05-20T02:00:00Z2024-05-19T02:00:00Z | |||
| Source System SourceSystem | Identifies the source system from which the data was extracted. | ||
| Description This attribute specifies the system of origin for the event data, for example, 'SAP S/4HANA Production' or 'SAP ECC'. In environments with multiple systems, this field is crucial for data lineage, troubleshooting, and ensuring that data from different sources is correctly interpreted. It helps in understanding the context of the data and can be used to filter analysis for specific system environments. Why it matters Provides essential context about the data's origin, which is critical for data governance, validation, and analysis in multi-system landscapes. Where to get This is typically a static value added during the data extraction, transformation, and loading (ETL) process to label the dataset with its origin. Examples S4H_PROD_100ECC_EU_200S4H_US_300 | |||
| PO Document Type DocumentType | A classification that distinguishes different types of purchase orders, such as standard POs, service POs, or stock transport orders. | ||
| Description The Document Type is a key configuration element in SAP that controls the process flow, number range, and fields for a purchase order. It allows businesses to tailor the procurement process for different scenarios. Analyzing the process by document type is crucial for understanding process variations. For instance, the process for a standard goods PO may be very different from a service PO or a stock transfer. This attribute allows for filtering and comparing these distinct process flows to find specific improvement opportunities. Why it matters It categorizes purchase orders, allowing for the comparison of different procurement processes and helping to explain variations in process flows and cycle times. Where to get This attribute is located in the SAP S/4HANA table EKKO, field BSART. Examples NBFOUB | |||
| Purchase Requisition PurchaseRequisitionNumber | The identifier of the purchase requisition (PR) that initiated the purchase order. | ||
| Description This attribute links the purchase order back to its originating purchase requisition. Not all POs will have a PR if they are created directly. This link is essential for analyzing the full end-to-end procurement process, starting from the initial request. It supports KPIs like 'Purchase Requisition Approval Time' and is fundamental for identifying 'Maverick Spend', where POs are created without a preceding, approved requisition. Why it matters Connects the PO to the initial request, enabling end-to-end process analysis and the identification of non-compliant maverick spend. Where to get This attribute is located in the SAP S/4HANA table EKPO (PO Item level), field BANFN. Examples 1001005110010052 | |||
| Requested Delivery Date RequestedDeliveryDate | The date on which the business requested the vendor to deliver the goods or services. | ||
| Description This attribute specifies the target delivery date agreed upon in the purchase order. It serves as the baseline for measuring supplier delivery performance. In process mining, this date is compared against the actual goods receipt date ('Goods Receipt Posted' timestamp) to calculate the 'Supplier On-Time Delivery Rate' KPI. Analyzing deviations from this date helps in assessing supplier reliability and managing supply chain risks. Why it matters Serves as the baseline for measuring supplier on-time delivery performance, a critical KPI for supply chain management and operational planning. Where to get This can be found in the schedule line table EKET, field EINDT. Examples 2023-06-012023-06-152023-07-01 | |||
| Total Net Amount TotalNetAmount | The total value of the purchase order, excluding taxes and freight costs. | ||
| Description This attribute represents the net monetary value of the purchase order. It is a key financial figure that indicates the size of the procurement transaction. This amount is crucial for financial analysis, such as categorizing POs by value (high-value vs. low-value) to see if their process paths differ. It can also be used to prioritize analysis, focusing on high-value orders that may carry more financial risk or have a greater impact on the business. Why it matters Allows for financial-based analysis, helping to segment purchase orders by value and prioritize process improvement efforts on high-spend areas. Where to get This attribute can be found in the SAP S/4HANA table EKKO, field NETWR. Examples 1500.0025000.50125.75 | |||
| User UserName | The identifier of the user who performed a specific activity. | ||
| Description This attribute captures the SAP user ID responsible for creating, changing, or approving a document. It provides traceability for actions taken within the system. Analyzing by user helps identify training needs, workload distribution, and individual performance. For example, it can be used to see if specific users are consistently associated with long approval times or frequent post-approval changes, which can inform resource management and process improvement initiatives. Why it matters Provides accountability and enables performance analysis at the individual or team level, helping to identify training opportunities or resource constraints. Where to get This information is found in fields like ERNAM (Created by) in EKKO or from the user field in change document tables (CDHDR-USERNAME). Examples CB9980000012JSMITHRROE | |||
| Vendor ID VendorId | The unique identifier for the supplier or vendor providing the goods or services. | ||
| Description The Vendor ID is a critical piece of master data that links a purchase order to a specific supplier. It is used throughout the procurement process for communication, delivery, and payment. In process mining, this attribute allows for performance analysis to be segmented by supplier. It is essential for dashboards like 'Supplier Lead Time Performance' and 'Goods Return Rate by Vendor', helping to identify the most reliable suppliers and those who may be causing delays or quality issues. Why it matters Enables supplier-centric analysis, helping to evaluate performance, identify high- and low-performing vendors, and optimize the supply chain. Where to get This attribute is located in the SAP S/4HANA table EKKO, field LIFNR. Examples 100023100045100088 | |||
| Company Code CompanyCode | The identifier for the legal entity or company for which the purchase order is created. | ||
| Description The Company Code represents an independent accounting unit within an organization. All financial transactions related to a purchase order are posted to a specific company code. This is a fundamental organizational attribute that allows for filtering and comparing procurement processes across different legal entities. Analysis by company code can reveal inconsistencies in process execution, differing levels of efficiency, or varying compliance rates across the organization. Why it matters Enables process analysis to be segmented by legal entity, facilitating comparisons of performance and compliance across different parts of the business. Where to get This attribute is located in the SAP S/4HANA table EKKO, field BUKRS. Examples 101017102000 | |||
| Is Maverick Spend IsMaverickSpend | A calculated flag indicating if a purchase order was created without a preceding, approved purchase requisition. | ||
| Description This boolean flag is derived during data processing. It is set to 'true' if a purchase order lacks an associated purchase requisition or if the PO creation bypasses the standard approval workflow. This attribute directly supports the 'Maverick Spend Identification' dashboard and related KPIs. It helps quantify the extent of non-compliant purchasing behavior, enabling businesses to target specific departments or user groups to reinforce procurement policies and controls. Why it matters Directly identifies non-compliant purchasing, helping to quantify process deviations and enforce financial controls and procurement policies. Where to get Calculated field based on the absence of a value in 'PurchaseRequisitionNumber' for specific document types, or by analyzing the event sequence. Examples truefalse | |||
| Is Rework IsRework | A calculated flag indicating if the purchase order underwent rework, such as a post-approval change or goods return. | ||
| Description This boolean attribute is calculated by analyzing the sequence of activities for each purchase order. It is marked as 'true' if a 'Purchase Order Changed' event occurs after a 'Purchase Order Approved' event, or if a 'Goods Returned' event is present. This flag simplifies the calculation of the 'Straight-Through Processing Rate' KPI. It allows for easy filtering and visualization of all POs that required manual intervention or correction, helping to quantify the cost and frequency of rework. Why it matters Helps quantify process inefficiency by flagging cases with rework, which is crucial for calculating straight-through processing rates and identifying root causes of deviation. Where to get Calculated field based on the sequence of activities. Logic checks if a 'Purchase Order Changed' event follows an approval or if a 'Goods Returned' event exists. Examples truefalse | |||
| Item Category ItemCategory | Classifies a purchase order line item, such as standard, consignment, subcontracting, or service. | ||
| Description The Item Category determines how the procurement of a specific material or service is controlled and processed. It influences subsequent steps like goods receipt and invoice verification. This attribute is important for analyzing process variants based on what is being purchased. For example, the process for a service item (which requires a service entry sheet) differs significantly from a standard stock item. Analyzing by item category helps explain these differences and allows for targeted process improvements. Why it matters Explains process variations by distinguishing between different types of procurement, such as goods, services, or subcontracting. Where to get This attribute is located in the SAP S/4HANA table EKPO, field PSTYP. Examples 093 | |||
| Material Number MaterialNumber | The identifier for the specific material or good being procured. | ||
| Description The Material Number is a unique code assigned to each material master record in SAP. It is used for all transactions related to that material, including procurement, inventory management, and sales. Analyzing by material number or material group allows for commodity-based analysis. It can help identify if procurement processes for certain types of materials are less efficient, have longer lead times, or are more prone to returns, providing insights for category management. Why it matters Enables commodity-based analysis, helping to identify process issues or supplier performance problems related to specific products or materials. Where to get This attribute is located in the SAP S/4HANA table EKPO, field MATNR. Examples RM100-100FG210SERV-CONSULT | |||
| Plant Plant | The operational facility or location to which the goods are delivered or services are rendered. | ||
| Description In SAP, a Plant is a physical location where goods are produced, stored, or services are performed. It is a key element for logistics and planning. Segmenting process analysis by plant can reveal regional or site-specific variations in the procurement process. For instance, it can show if certain plants experience longer delivery times or have higher rates of goods returns, pointing to localized logistics or quality control issues. Why it matters Allows for location-based analysis, highlighting process performance differences between various operational sites, plants, or warehouses. Where to get This attribute is located in the SAP S/4HANA table EKPO, field WERKS. Examples 10101710DE01 | |||
| PO Approval Cycle Time PoApprovalCycleTime | The calculated duration from when a purchase order was created to when it received final approval. | ||
| Description This metric measures the time elapsed between the 'Purchase Order Created' activity and the 'Purchase Order Approved' activity. It is calculated for each purchase order case. This attribute provides a direct measure of internal approval efficiency. It is the primary metric for the 'PO Approval Cycle Time' dashboard and KPI, helping to identify where delays in the approval process occur and pinpoint opportunities for acceleration. Why it matters Directly measures the efficiency of the internal approval process, helping to identify and address bottlenecks that slow down procurement. Where to get Calculated by finding the time difference between the 'Purchase Order Approved' event and the 'Purchase Order Created' event for each Purchase Order. Examples P2D4H30MP0D2H15MP5D | |||
| Purchasing Group PurchasingGroup | The specific group of buyers responsible for certain procurement activities. | ||
| Description A Purchasing Group is a buyer or a group of buyers who are responsible for specific purchasing activities, materials, or suppliers. They are the primary point of contact for vendors. This attribute allows for a more granular analysis of workload and performance than the purchasing organization. It can be used to identify overburdened teams, measure the efficiency of different buyer groups, and understand which groups are more prone to process deviations like maverick spend. Why it matters Provides a granular view of buyer group performance, enabling analysis of workload, efficiency, and process adherence at the team level. Where to get This attribute is located in the SAP S/4HANA table EKKO, field EKGRP. Examples 001002N00 | |||
| Purchasing Organization PurchasingOrganization | The organizational unit responsible for procuring materials and services and negotiating with vendors. | ||
| Description The Purchasing Organization is a key organizational unit in procurement. It can be structured at the corporate, company, or plant level and is responsible for all purchasing activities. Analyzing the process by purchasing organization helps to evaluate the efficiency and performance of different procurement teams or regions. It can highlight differences in supplier negotiation, process compliance, or approval delays between organizational units. Why it matters Allows for performance comparison between different procurement departments or regions, helping to identify best practices and areas for improvement. Where to get This attribute is located in the SAP S/4HANA table EKKO, field EKORG. Examples 10101710US01 | |||
| Supplier On-Time Delivery SupplierOnTimeDelivery | A calculated flag indicating whether the goods receipt was posted on or before the requested delivery date. | ||
| Description This boolean attribute is derived by comparing the timestamp of the 'Goods Receipt Posted' activity with the 'Requested Delivery Date'. If the goods receipt is on or before the requested date, it is marked 'true'. This attribute directly supports the 'Supplier On-Time Delivery Rate' KPI. It simplifies analysis by allowing users to easily filter for on-time or late deliveries, which is essential for vendor performance dashboards and supplier scorecarding. Why it matters Directly measures supplier reliability, forming the basis for the on-time delivery KPI and enabling effective vendor performance management. Where to get Calculated by comparing the timestamp of the 'Goods Receipt Posted' activity with the 'RequestedDeliveryDate' attribute. Examples truefalse | |||
Purchase to Pay - Purchase Order Activities
| Activity | Description | ||
|---|---|---|---|
| Goods Receipt Posted | Represents the physical receipt of goods from the vendor and the corresponding entry in the system. This is an explicit transaction that updates the purchase order history. | ||
| Why it matters This is a major milestone that ends the supplier lead time and begins the internal process of invoice verification. It is essential for tracking on-time delivery rates. Where to get Recorded as a material document in tables MKPF (header) and MSEG (item), and linked in the purchase order history table EKBE with a specific movement type (e.g., 101). Capture Posting date (BUDAT) from the material document header (MKPF) linked via EKBE. Event type explicit | |||
| Invoice Received | Represents the entry of a vendor's invoice into the SAP system, linking it to the corresponding purchase order. This is an explicit financial posting that creates an accounting document. | ||
| Why it matters This is a critical milestone connecting the procurement process with the accounts payable process. It allows analysis of the time between goods receipt and invoice processing. Where to get An accounting document is created in table BKPF (header) and its line items are in BSEG or the universal journal ACDOCA. The document is linked to the PO in table RSEG. Capture Document entry date (CPUDT) from the accounting document header table BKPF. Event type explicit | |||
| Purchase Order Approved | Signifies that the purchase order has received all necessary internal approvals and is authorized for release to the vendor. The event is inferred from a status change in the purchase order's release strategy. | ||
| Why it matters This is a key milestone for measuring approval efficiency and post-approval rework. Analyzing the time between PO creation and approval highlights internal process delays. Where to get Inferred from the release indicator (FRGKE) in the EKKO table. The timestamp is determined by looking at the change history (CDHDR/CDPOS) for when this field was updated to the 'released' status. Capture Inferred from change logs for the release indicator field (FRGKE) in the EKKO table. Event type inferred | |||
| Purchase Order Completed | This activity signifies that a purchase order item is considered closed from a logistics perspective. It is inferred when the 'Delivery Completed' and 'Final Invoice' indicators are both set. | ||
| Why it matters This serves as the end point for the purchase order lifecycle analysis. Measuring the time to this event provides the end-to-end cycle time for procurement operations. Where to get Inferred from status flags on the purchase order item table, EKPO. The event occurs when both the 'Delivery Completed' indicator (ELIKZ) and 'Final Invoice' indicator (EREKZ) are set to true. Capture Inferred from change logs when EKPO fields ELIKZ and EREKZ are both flagged as complete. Event type inferred | |||
| Purchase Order Created | Marks the creation of the official purchase order document, which may be created with or without reference to a purchase requisition. This is an explicit event captured when the PO document is first saved in the system. | ||
| Why it matters This activity can serve as an alternative starting point for the process, especially for maverick buying analysis. It is a fundamental event for tracking overall PO processing time. Where to get Recorded in the purchase order header table EKKO. The creation date (AEDAT) and time are stored directly in this table for the document. Capture Creation timestamp (AEDAT) in the EKKO table for the purchase order document. Event type explicit | |||
| Purchase Requisition Approved | Represents the formal approval of a purchase requisition by a manager or designated approver. This is typically inferred from a status change in the requisition document, signifying it is ready for conversion to a purchase order. | ||
| Why it matters This is a critical milestone for tracking approval cycle times and identifying bottlenecks. Delays here directly impact how quickly a purchase order can be created and sent to a vendor. Where to get Inferred from the release status fields in the EBAN table (e.g., FRGZU - Release indicator). The timestamp is derived from the change documents (CDHDR/CDPOS) that record when the final release status was set. Capture Inferred from change logs (CDHDR/CDPOS) for release status fields in the EBAN table. Event type inferred | |||
| Purchase Requisition Created | This activity marks the formal request for goods or services, initiating the procurement process. The event is captured explicitly when a user saves a new purchase requisition document (e.g., using transaction ME51N). | ||
| Why it matters This is the primary start point for many purchase order lifecycles. Analyzing the time from this event to PO creation helps identify delays in sourcing and internal processing. Where to get Recorded in table EBAN (Purchase Requisition). The creation event timestamp can be found in the change history tables CDHDR and CDPOS for the EBAN object. Capture Event recorded upon creation of a document in the EBAN table. Event type explicit | |||
| Goods Returned | Indicates that previously received goods were returned to the vendor, typically due to quality issues, damage, or incorrect shipments. This is captured as an explicit reversal goods movement. | ||
| Why it matters This activity highlights rework and potential problems with supplier quality or order accuracy. A high frequency of returns for a specific vendor or material signals an issue. Where to get Recorded as a material document with a specific return movement type (e.g., 122). The event is logged in MKPF/MSEG and linked to the PO in the EKBE history table. Capture Posting date from the material document with a return movement type in EKBE. Event type explicit | |||
| Invoice Paid | Marks the final settlement of the vendor invoice through a payment run or manual payment. This is an explicit financial transaction that creates a clearing document. | ||
| Why it matters While technically part of the payment process, including this activity provides a complete view of the procure-to-pay cycle. It is key for analyzing payment terms and performance. Where to get The payment is recorded as a clearing document in BKPF/ACDOCA. The clearing date (AUGDT) on the invoice line item in table BSEG or ACDOCA indicates the payment event. Capture Clearing Date (AUGDT) of the invoice document, found in BSEG or ACDOCA. Event type explicit | |||
| Purchase Order Changed | This activity indicates that a modification was made to the purchase order after its initial creation, such as a change in quantity, price, or delivery date. It is captured explicitly in system change logs. | ||
| Why it matters Tracking changes, especially after approval, is critical for identifying process inefficiencies, rework, and potential compliance issues. Frequent changes can point to poor initial specifications. Where to get Recorded in the change document tables CDHDR (header) and CDPOS (item) for purchase order objects (EINKBELEG). Each change creates a detailed log entry. Capture Event logged for changes to key fields in the EKKO or EKPO tables, recorded in CDHDR/CDPOS. Event type explicit | |||
| Purchase Order Deleted | Represents the cancellation or logical deletion of a purchase order item or the entire document. This is captured by a user setting a deletion flag on the document. | ||
| Why it matters This activity is an alternative end point for the process, indicating a failure or cancellation. Analyzing why POs are deleted can uncover issues in demand planning or requirements definition. Where to get Captured from the deletion indicator flag (LOEKZ) in the purchase order header (EKKO) or item (EKPO) tables. The timestamp is derived from the change documents (CDHDR/CDPOS). Capture Timestamp from change documents (CDHDR/CDPOS) when the deletion flag (LOEKZ) is set. Event type explicit | |||
| Purchase Order Sent to Vendor | Represents the moment the purchase order is communicated to the vendor, for example, via EDI, email, or print. This event is often captured through the system's output management logs. | ||
| Why it matters This activity is the true start of the supplier lead time. It is crucial for accurately measuring supplier performance from the moment they receive the order. Where to get Captured from the output control table NAST, which logs messages sent for a purchasing document. The date and time of the relevant output type (e.g., EDI, email) can be used. Capture Timestamp of the first successful output message for the PO in the NAST table. Event type inferred | |||
| Services Confirmation Entered | This activity marks the confirmation that a service specified on a purchase order has been rendered. It is captured explicitly through the creation of a service entry sheet. | ||
| Why it matters For service-based procurement, this is the equivalent of a goods receipt. It is crucial for tracking service delivery timelines and enabling timely vendor payments. Where to get Recorded via the creation of a service entry sheet, with data stored in tables ESSR (header) and ESLL (lines). The creation date serves as the timestamp. Capture Creation date of the Service Entry Sheet document in table ESSR. Event type explicit | |||
Extraction Guides
Steps
- Prerequisites and Access: Ensure you have a user with appropriate authorizations to query Core Data Services (CDS) views in the SAP S/4HANA system. Access can be through SAP HANA Studio, ABAP Development Tools (ADT) for Eclipse, or a third-party data extraction tool that supports SQL connections to the SAP HANA database.
- Identify System Connection Details: Obtain the necessary connection parameters for your SAP S/4HANA system, including the host, instance number, and your authentication credentials.
- Connect to the Database: Using your preferred SQL client, establish a connection to the SAP S/4HANA database where the CDS views reside.
- Prepare the SQL Query: Copy the complete SQL query provided in the query section of this document into your SQL editor. This query is designed to extract all required activities and attributes.
- Set Filtering Parameters: Locate the placeholder values within the query. Replace _start_date and _end_date with the desired date range for your analysis (e.g., '20230101' and '20231231'). Modify the poh.CompanyCode filter to include the specific company codes you wish to analyze.
- Execute the Query: Run the modified SQL query against the S/4HANA database. Depending on the data volume and the specified date range, this execution may take some time.
- Review Preliminary Results: Once the query finishes, perform a quick review of the output in your SQL client. Check for the presence of different activities, ensure timestamps are populated correctly, and verify that the case ID (PurchaseOrderNumber) is consistent.
- Export the Data: Export the complete result set from your SQL tool to a CSV (Comma Separated Values) file. Ensure the file uses UTF-8 encoding to prevent character issues.
- Prepare for Upload: Before uploading to ProcessMind, open the CSV file and verify that the column headers exactly match the attributes defined in the data requirements (PurchaseOrderNumber, ActivityName, EventTime, etc.). Adjust column names if your export tool has altered them.
- Upload to ProcessMind: Upload the finalized CSV file to your ProcessMind project. Map the columns in your file to the corresponding case ID, activity, and timestamp fields during the import process.
Configuration
- Key CDS Views Used: The extraction logic relies on a set of standard, semantically rich CDS views. The primary views include:
- I_PurchaseOrderItemAPI01: For core purchase order item data.
- I_PurchaseRequisitionItemAPI01: For purchase requisition details.
- I_MaterialDocumentItem: For goods movements like receipts and returns.
- I_ServiceEntrySheetAPI01: For service confirmation events.
- I_SupplierInvoiceAPI01: For vendor invoice information.
- I_OperationalAcctgDocItem: For linking invoices to financial documents for payment tracking.
- I_ChangeDocument: For capturing changes to the purchase order.
- Date Range Filtering: It is critical to apply a date range filter to manage performance and data volume. The query uses placeholders _start_date and _end_date on the purchase order creation date (PurchaseOrderDate). A recommended starting range is 3 to 6 months of data.
- Organizational Filtering: The query should always be filtered by CompanyCode to limit the scope of the extraction to relevant business units. Additional filters on PurchaseOrderType or PurchasingOrganization can be added to the main PO_base common table expression for further refinement.
- Prerequisites: The user executing the query requires SELECT authorization on all the CDS views listed above. Access to these views is typically granted through specific business or analytics roles in S/4HANA. Without proper permissions, the query will fail.
a Sample Query sql
WITH PO_base AS (
SELECT
poh.PurchaseOrder AS PurchaseOrderNumber,
poi.PurchaseOrderItem AS PurchaseOrderItem,
poh.CompanyCode,
poh.PurchaseOrderType AS DocumentType,
poh.Supplier AS VendorId,
poh.PurchaseOrderDate,
poi.PurchaseRequisition AS PurchaseRequisitionNumber,
poi.NetPriceAmount * poi.OrderQuantity AS TotalNetAmount, -- Note: This is item-level net amount
poh.CreationDate AS POCreationDate,
poh.CreationTime AS POCreationTime,
poh.LastChangeDateTime AS POLastChangeDateTime,
poi.IsDeleted,
poi.DeliveryIsCompleted,
poi.FinalInvoiceIsExpected,
poi.GoodsReceiptIsExpected,
poi.LastGoodsReceiptDate,
poi.LastInvoiceReceiptDate
FROM I_PurchaseOrderAPI01 poh
JOIN I_PurchaseOrderItemAPI01 poi
ON poh.PurchaseOrder = poi.PurchaseOrder
WHERE
poh.PurchaseOrderDate BETWEEN '_start_date' AND '_end_date' -- Placeholder: e.g., '20230101' and '20230630'
AND poh.CompanyCode IN ('[YourCompanyCode]') -- Placeholder: e.g., '1010'
)
-- 1. Purchase Requisition Created
SELECT
po.PurchaseOrderNumber,
'Purchase Requisition Created' AS ActivityName,
CAST(CONCAT(pr.CreationDate, 'T', pr.CreationTime) AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem, -- Placeholder
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
pr.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate, -- Available in PR, add if needed
po.DocumentType
FROM I_PurchaseRequisitionItemAPI01 pr
JOIN PO_base po
ON pr.PurchaseRequisition = po.PurchaseRequisitionNumber AND pr.PurchaseRequisitionItem = po.PurchaseOrderItem
UNION ALL
-- 2. Purchase Requisition Approved
SELECT
po.PurchaseOrderNumber,
'Purchase Requisition Approved' AS ActivityName,
CAST(CONCAT(pr.PurReqnReleaseDate, 'T', '000000') AS TIMESTAMP) AS EventTime, -- Time is not available in this view
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
NULL AS UserName, -- Approver info requires complex joins
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_PurchaseRequisitionItemAPI01 pr
JOIN PO_base po
ON pr.PurchaseRequisition = po.PurchaseRequisitionNumber AND pr.PurchaseRequisitionItem = po.PurchaseOrderItem
WHERE
pr.PurReqnReleaseDate IS NOT NULL
UNION ALL
-- 3. Purchase Order Created
SELECT
po.PurchaseOrderNumber,
'Purchase Order Created' AS ActivityName,
CAST(CONCAT(po.POCreationDate, 'T', po.POCreationTime) AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
poh.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
poi.RequestedDeliveryDate,
po.DocumentType
FROM PO_base po
JOIN I_PurchaseOrderAPI01 poh ON po.PurchaseOrderNumber = poh.PurchaseOrder
JOIN I_PurchaseOrderItemAPI01 poi ON po.PurchaseOrderNumber = poi.PurchaseOrder AND po.PurchaseOrderItem = poi.PurchaseOrderItem
UNION ALL
-- 4. Purchase Order Approved
SELECT DISTINCT
po.PurchaseOrderNumber,
'Purchase Order Approved' AS ActivityName,
CAST(poh.ReleaseDate AS TIMESTAMP) AS EventTime, -- Assuming ReleaseDate reflects final approval
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
NULL AS UserName, -- Approver info requires complex joins
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
poi.RequestedDeliveryDate,
po.DocumentType
FROM PO_base po
JOIN I_PurchaseOrderAPI01 poh ON po.PurchaseOrderNumber = poh.PurchaseOrder
JOIN I_PurchaseOrderItemAPI01 poi ON po.PurchaseOrderNumber = poi.PurchaseOrder AND po.PurchaseOrderItem = poi.PurchaseOrderItem
WHERE poh.ReleaseDate IS NOT NULL
UNION ALL
-- 5. Purchase Order Sent to Vendor
SELECT DISTINCT
po.PurchaseOrderNumber,
'Purchase Order Sent to Vendor' AS ActivityName,
CAST(poh.ReleaseDate AS TIMESTAMP) AS EventTime, -- Using ReleaseDate as a proxy for sending time
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
NULL AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
poi.RequestedDeliveryDate,
po.DocumentType
FROM PO_base po
JOIN I_PurchaseOrderAPI01 poh ON po.PurchaseOrderNumber = poh.PurchaseOrder
JOIN I_PurchaseOrderItemAPI01 poi ON po.PurchaseOrderNumber = poi.PurchaseOrder AND po.PurchaseOrderItem = poi.PurchaseOrderItem
WHERE poh.ReleaseDate IS NOT NULL
UNION ALL
-- 6. Purchase Order Changed
SELECT DISTINCT
ch.OBJECTID AS PurchaseOrderNumber,
'Purchase Order Changed' AS ActivityName,
CAST(CONCAT(ch.ChangeDocumentDate, 'T', ch.ChangeDocumentTime) AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
ch.UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_ChangeDocument ch
JOIN PO_base po ON ch.OBJECTID = po.PurchaseOrderNumber
WHERE
ch.ObjectClassName = 'EINKBELEG' -- Object Class for Purchase Documents
AND CAST(CONCAT(ch.ChangeDocumentDate, 'T', ch.ChangeDocumentTime) AS TIMESTAMP) > CAST(CONCAT(po.POCreationDate, 'T', po.POCreationTime) AS TIMESTAMP)
UNION ALL
-- 7. Goods Receipt Posted
SELECT
po.PurchaseOrderNumber,
'Goods Receipt Posted' AS ActivityName,
CAST(CONCAT(md.PostingDate, 'T', md.CreationTime) AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
md.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_MaterialDocumentItem md
JOIN PO_base po
ON md.PurchaseOrder = po.PurchaseOrderNumber AND md.PurchaseOrderItem = po.PurchaseOrderItem
WHERE
md.GoodsMovementType = '101'
UNION ALL
-- 8. Services Confirmation Entered
SELECT
po.PurchaseOrderNumber,
'Services Confirmation Entered' AS ActivityName,
CAST(se.PostingDate AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
se.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_ServiceEntrySheetAPI01 se
JOIN PO_base po
ON se.PurchaseOrder = po.PurchaseOrderNumber AND se.PurchaseOrderItem = po.PurchaseOrderItem
UNION ALL
-- 9. Goods Returned
SELECT
po.PurchaseOrderNumber,
'Goods Returned' AS ActivityName,
CAST(CONCAT(md.PostingDate, 'T', md.CreationTime) AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
md.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_MaterialDocumentItem md
JOIN PO_base po
ON md.PurchaseOrder = po.PurchaseOrderNumber AND md.PurchaseOrderItem = po.PurchaseOrderItem
WHERE
md.GoodsMovementType = '122'
UNION ALL
-- 10. Invoice Received
SELECT
po.PurchaseOrderNumber,
'Invoice Received' AS ActivityName,
CAST(inv.PostingDate AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
inv.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_SupplierInvoiceAPI01 inv
JOIN PO_base po
ON inv.PurchaseOrderReference = po.PurchaseOrderNumber
WHERE
inv.DebitCreditCode = 'H' -- 'H' for Credit (Supplier Invoice)
UNION ALL
-- 11. Invoice Paid
SELECT
po.PurchaseOrderNumber,
'Invoice Paid' AS ActivityName,
CAST(doc.ClearingDate AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
doc.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_SupplierInvoiceAPI01 inv
JOIN I_OperationalAcctgDocItem doc
ON inv.AccountingDocument = doc.AccountingDocument
JOIN PO_base po
ON inv.PurchaseOrderReference = po.PurchaseOrderNumber
WHERE
doc.IsCleared = 'X' AND doc.ClearingDate IS NOT NULL
UNION ALL
-- 12. Purchase Order Completed
SELECT
po.PurchaseOrderNumber,
'Purchase Order Completed' AS ActivityName,
CAST(GREATEST(po.LastGoodsReceiptDate, po.LastInvoiceReceiptDate) AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
'SYSTEM' AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM PO_base po
WHERE
po.DeliveryIsCompleted = 'X'
AND (po.FinalInvoiceIsExpected = 'X' OR po.GoodsReceiptIsExpected = '') -- Logic for completion
AND GREATEST(po.LastGoodsReceiptDate, po.LastInvoiceReceiptDate) IS NOT NULL
UNION ALL
-- 13. Purchase Order Deleted
SELECT
po.PurchaseOrderNumber,
'Purchase Order Deleted' AS ActivityName,
CAST(po.POLastChangeDateTime AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
NULL AS UserName, -- User who set the flag is in change docs
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM PO_base po
WHERE
po.IsDeleted = 'X'Steps
- Prerequisites and Access: Ensure you have a user with appropriate authorizations to query Core Data Services (CDS) views in the SAP S/4HANA system. Access can be through SAP HANA Studio, ABAP Development Tools (ADT) for Eclipse, or a third-party data extraction tool that supports SQL connections to the SAP HANA database.
- Identify System Connection Details: Obtain the necessary connection parameters for your SAP S/4HANA system, including the host, instance number, and your authentication credentials.
- Connect to the Database: Using your preferred SQL client, establish a connection to the SAP S/4HANA database where the CDS views reside.
- Prepare the SQL Query: Copy the complete SQL query provided in the query section of this document into your SQL editor. This query is designed to extract all required activities and attributes.
- Set Filtering Parameters: Locate the placeholder values within the query. Replace _start_date and _end_date with the desired date range for your analysis (e.g., '20230101' and '20231231'). Modify the poh.CompanyCode filter to include the specific company codes you wish to analyze.
- Execute the Query: Run the modified SQL query against the S/4HANA database. Depending on the data volume and the specified date range, this execution may take some time.
- Review Preliminary Results: Once the query finishes, perform a quick review of the output in your SQL client. Check for the presence of different activities, ensure timestamps are populated correctly, and verify that the case ID (PurchaseOrderNumber) is consistent.
- Export the Data: Export the complete result set from your SQL tool to a CSV (Comma Separated Values) file. Ensure the file uses UTF-8 encoding to prevent character issues.
- Prepare for Upload: Before uploading to ProcessMind, open the CSV file and verify that the column headers exactly match the attributes defined in the data requirements (PurchaseOrderNumber, ActivityName, EventTime, etc.). Adjust column names if your export tool has altered them.
- Upload to ProcessMind: Upload the finalized CSV file to your ProcessMind project. Map the columns in your file to the corresponding case ID, activity, and timestamp fields during the import process.
Configuration
- Key CDS Views Used: The extraction logic relies on a set of standard, semantically rich CDS views. The primary views include:
- I_PurchaseOrderItemAPI01: For core purchase order item data.
- I_PurchaseRequisitionItemAPI01: For purchase requisition details.
- I_MaterialDocumentItem: For goods movements like receipts and returns.
- I_ServiceEntrySheetAPI01: For service confirmation events.
- I_SupplierInvoiceAPI01: For vendor invoice information.
- I_OperationalAcctgDocItem: For linking invoices to financial documents for payment tracking.
- I_ChangeDocument: For capturing changes to the purchase order.
- Date Range Filtering: It is critical to apply a date range filter to manage performance and data volume. The query uses placeholders _start_date and _end_date on the purchase order creation date (PurchaseOrderDate). A recommended starting range is 3 to 6 months of data.
- Organizational Filtering: The query should always be filtered by CompanyCode to limit the scope of the extraction to relevant business units. Additional filters on PurchaseOrderType or PurchasingOrganization can be added to the main PO_base common table expression for further refinement.
- Prerequisites: The user executing the query requires SELECT authorization on all the CDS views listed above. Access to these views is typically granted through specific business or analytics roles in S/4HANA. Without proper permissions, the query will fail.
a Sample Query sql
WITH PO_base AS (
SELECT
poh.PurchaseOrder AS PurchaseOrderNumber,
poi.PurchaseOrderItem AS PurchaseOrderItem,
poh.CompanyCode,
poh.PurchaseOrderType AS DocumentType,
poh.Supplier AS VendorId,
poh.PurchaseOrderDate,
poi.PurchaseRequisition AS PurchaseRequisitionNumber,
poi.NetPriceAmount * poi.OrderQuantity AS TotalNetAmount, -- Note: This is item-level net amount
poh.CreationDate AS POCreationDate,
poh.CreationTime AS POCreationTime,
poh.LastChangeDateTime AS POLastChangeDateTime,
poi.IsDeleted,
poi.DeliveryIsCompleted,
poi.FinalInvoiceIsExpected,
poi.GoodsReceiptIsExpected,
poi.LastGoodsReceiptDate,
poi.LastInvoiceReceiptDate
FROM I_PurchaseOrderAPI01 poh
JOIN I_PurchaseOrderItemAPI01 poi
ON poh.PurchaseOrder = poi.PurchaseOrder
WHERE
poh.PurchaseOrderDate BETWEEN '_start_date' AND '_end_date' -- Placeholder: e.g., '20230101' and '20230630'
AND poh.CompanyCode IN ('[YourCompanyCode]') -- Placeholder: e.g., '1010'
)
-- 1. Purchase Requisition Created
SELECT
po.PurchaseOrderNumber,
'Purchase Requisition Created' AS ActivityName,
CAST(CONCAT(pr.CreationDate, 'T', pr.CreationTime) AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem, -- Placeholder
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
pr.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate, -- Available in PR, add if needed
po.DocumentType
FROM I_PurchaseRequisitionItemAPI01 pr
JOIN PO_base po
ON pr.PurchaseRequisition = po.PurchaseRequisitionNumber AND pr.PurchaseRequisitionItem = po.PurchaseOrderItem
UNION ALL
-- 2. Purchase Requisition Approved
SELECT
po.PurchaseOrderNumber,
'Purchase Requisition Approved' AS ActivityName,
CAST(CONCAT(pr.PurReqnReleaseDate, 'T', '000000') AS TIMESTAMP) AS EventTime, -- Time is not available in this view
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
NULL AS UserName, -- Approver info requires complex joins
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_PurchaseRequisitionItemAPI01 pr
JOIN PO_base po
ON pr.PurchaseRequisition = po.PurchaseRequisitionNumber AND pr.PurchaseRequisitionItem = po.PurchaseOrderItem
WHERE
pr.PurReqnReleaseDate IS NOT NULL
UNION ALL
-- 3. Purchase Order Created
SELECT
po.PurchaseOrderNumber,
'Purchase Order Created' AS ActivityName,
CAST(CONCAT(po.POCreationDate, 'T', po.POCreationTime) AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
poh.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
poi.RequestedDeliveryDate,
po.DocumentType
FROM PO_base po
JOIN I_PurchaseOrderAPI01 poh ON po.PurchaseOrderNumber = poh.PurchaseOrder
JOIN I_PurchaseOrderItemAPI01 poi ON po.PurchaseOrderNumber = poi.PurchaseOrder AND po.PurchaseOrderItem = poi.PurchaseOrderItem
UNION ALL
-- 4. Purchase Order Approved
SELECT DISTINCT
po.PurchaseOrderNumber,
'Purchase Order Approved' AS ActivityName,
CAST(poh.ReleaseDate AS TIMESTAMP) AS EventTime, -- Assuming ReleaseDate reflects final approval
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
NULL AS UserName, -- Approver info requires complex joins
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
poi.RequestedDeliveryDate,
po.DocumentType
FROM PO_base po
JOIN I_PurchaseOrderAPI01 poh ON po.PurchaseOrderNumber = poh.PurchaseOrder
JOIN I_PurchaseOrderItemAPI01 poi ON po.PurchaseOrderNumber = poi.PurchaseOrder AND po.PurchaseOrderItem = poi.PurchaseOrderItem
WHERE poh.ReleaseDate IS NOT NULL
UNION ALL
-- 5. Purchase Order Sent to Vendor
SELECT DISTINCT
po.PurchaseOrderNumber,
'Purchase Order Sent to Vendor' AS ActivityName,
CAST(poh.ReleaseDate AS TIMESTAMP) AS EventTime, -- Using ReleaseDate as a proxy for sending time
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
NULL AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
poi.RequestedDeliveryDate,
po.DocumentType
FROM PO_base po
JOIN I_PurchaseOrderAPI01 poh ON po.PurchaseOrderNumber = poh.PurchaseOrder
JOIN I_PurchaseOrderItemAPI01 poi ON po.PurchaseOrderNumber = poi.PurchaseOrder AND po.PurchaseOrderItem = poi.PurchaseOrderItem
WHERE poh.ReleaseDate IS NOT NULL
UNION ALL
-- 6. Purchase Order Changed
SELECT DISTINCT
ch.OBJECTID AS PurchaseOrderNumber,
'Purchase Order Changed' AS ActivityName,
CAST(CONCAT(ch.ChangeDocumentDate, 'T', ch.ChangeDocumentTime) AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
ch.UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_ChangeDocument ch
JOIN PO_base po ON ch.OBJECTID = po.PurchaseOrderNumber
WHERE
ch.ObjectClassName = 'EINKBELEG' -- Object Class for Purchase Documents
AND CAST(CONCAT(ch.ChangeDocumentDate, 'T', ch.ChangeDocumentTime) AS TIMESTAMP) > CAST(CONCAT(po.POCreationDate, 'T', po.POCreationTime) AS TIMESTAMP)
UNION ALL
-- 7. Goods Receipt Posted
SELECT
po.PurchaseOrderNumber,
'Goods Receipt Posted' AS ActivityName,
CAST(CONCAT(md.PostingDate, 'T', md.CreationTime) AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
md.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_MaterialDocumentItem md
JOIN PO_base po
ON md.PurchaseOrder = po.PurchaseOrderNumber AND md.PurchaseOrderItem = po.PurchaseOrderItem
WHERE
md.GoodsMovementType = '101'
UNION ALL
-- 8. Services Confirmation Entered
SELECT
po.PurchaseOrderNumber,
'Services Confirmation Entered' AS ActivityName,
CAST(se.PostingDate AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
se.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_ServiceEntrySheetAPI01 se
JOIN PO_base po
ON se.PurchaseOrder = po.PurchaseOrderNumber AND se.PurchaseOrderItem = po.PurchaseOrderItem
UNION ALL
-- 9. Goods Returned
SELECT
po.PurchaseOrderNumber,
'Goods Returned' AS ActivityName,
CAST(CONCAT(md.PostingDate, 'T', md.CreationTime) AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
md.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_MaterialDocumentItem md
JOIN PO_base po
ON md.PurchaseOrder = po.PurchaseOrderNumber AND md.PurchaseOrderItem = po.PurchaseOrderItem
WHERE
md.GoodsMovementType = '122'
UNION ALL
-- 10. Invoice Received
SELECT
po.PurchaseOrderNumber,
'Invoice Received' AS ActivityName,
CAST(inv.PostingDate AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
inv.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_SupplierInvoiceAPI01 inv
JOIN PO_base po
ON inv.PurchaseOrderReference = po.PurchaseOrderNumber
WHERE
inv.DebitCreditCode = 'H' -- 'H' for Credit (Supplier Invoice)
UNION ALL
-- 11. Invoice Paid
SELECT
po.PurchaseOrderNumber,
'Invoice Paid' AS ActivityName,
CAST(doc.ClearingDate AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
doc.CreatedByUser AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM I_SupplierInvoiceAPI01 inv
JOIN I_OperationalAcctgDocItem doc
ON inv.AccountingDocument = doc.AccountingDocument
JOIN PO_base po
ON inv.PurchaseOrderReference = po.PurchaseOrderNumber
WHERE
doc.IsCleared = 'X' AND doc.ClearingDate IS NOT NULL
UNION ALL
-- 12. Purchase Order Completed
SELECT
po.PurchaseOrderNumber,
'Purchase Order Completed' AS ActivityName,
CAST(GREATEST(po.LastGoodsReceiptDate, po.LastInvoiceReceiptDate) AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
'SYSTEM' AS UserName,
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM PO_base po
WHERE
po.DeliveryIsCompleted = 'X'
AND (po.FinalInvoiceIsExpected = 'X' OR po.GoodsReceiptIsExpected = '') -- Logic for completion
AND GREATEST(po.LastGoodsReceiptDate, po.LastInvoiceReceiptDate) IS NOT NULL
UNION ALL
-- 13. Purchase Order Deleted
SELECT
po.PurchaseOrderNumber,
'Purchase Order Deleted' AS ActivityName,
CAST(po.POLastChangeDateTime AS TIMESTAMP) AS EventTime,
'[Your S/4HANA System ID]' AS SourceSystem,
CURRENT_UTCTIMESTAMP AS LastDataUpdate,
po.VendorId,
NULL AS UserName, -- User who set the flag is in change docs
po.TotalNetAmount,
po.PurchaseRequisitionNumber,
NULL AS RequestedDeliveryDate,
po.DocumentType
FROM PO_base po
WHERE
po.IsDeleted = 'X'Steps
- Specification and Design: Define the final data structure for the event log file, including all required and recommended attributes. Document the specific SAP tables (e.g., EKKO, EKPO, EKBE, CDHDR, CDPOS, BKPF) that will be used to source data for each of the 13 required activities.
- Program Creation: In the SAP GUI, navigate to the ABAP Editor using transaction code SE38 or SE80. Create a new executable program, for example, Z_PM_PO_EXTRACT.
- Define Selection Screen: Code the selection screen for the report. This allows users to filter the data they want to extract. Include parameters for the Purchase Order creation date range (P_AEDAT), Company Code (P_BUKRS), and Purchasing Document Type (P_BSART).
- Data Declarations: Define the internal tables and data structures needed for the program. This includes an internal table for the final event log that matches the structure defined in the specification step.
- Implement Data Selection Logic: Write the core ABAP logic to select data for each of the 13 activities. This involves a series of SELECT statements against the relevant SAP tables, joined where necessary. For change-based events, read from the change log tables CDHDR and CDPOS.
- Transform and Map Data: For each record retrieved, map the SAP table fields to the corresponding columns in your final event log internal table. Set the ActivityName based on the event being processed (e.g., 'Purchase Order Created'). Convert date and time fields into a consistent timestamp format for EventTime.
- Consolidate Event Data: After processing all 13 activity types, ensure all data is collected into a single, unified internal table. This table now represents the complete event log for the selected purchase orders.
- Implement File Output: Add functionality to write the final internal table to a file. The recommended approach is to use the cl_gui_frontend_services=>gui_download method to allow users to save the file as a CSV on their local machine, or use OPEN DATASET to save it to the SAP application server for background processing.
- Create Transaction Code (Optional): To make the program easily accessible to business users, use transaction code SE93 to create a custom transaction code (e.g., ZPM_PO_EXTRACT) that executes your ABAP program.
- Schedule Background Job: For large data volumes or automated extractions, use transaction code SM36 to schedule the program to run as a background job. The output file will be written to the application server path specified in the program logic.
Configuration
- Selection Criteria: The program should include selection parameters to filter the data effectively. Key filters include:
- Date Range: A mandatory date range for the Purchase Order creation date (EKKO-AEDAT). It is recommended to start with a period of 3-6 months to manage data volume and report performance.
- Company Code (BUKRS): Essential for organizations with multiple legal entities to narrow down the extraction scope.
- Purchasing Document Type (BSART): Allows for filtering specific PO types, such as Standard PO, Framework Order, or Stock Transport Order, to focus the analysis.
- Change Log Reading: The extraction of activities like 'Purchase Order Approved' or 'Purchase Order Changed' relies on reading the SAP change log tables (CDHDR, CDPOS). This can be resource-intensive. The ABAP logic should be optimized to select only the necessary object classes (EINKBELEG, BANF) and table/field combinations.
- Authorizations: The user or technical account running this report requires extensive read authorizations for tables across multiple SAP modules, including Materials Management (MM), Financial Accounting (FI), and system-wide tables. This includes tables like EKKO, EKPO, EBAN, EKBE, BKPF, BSAK, RBKP, NAST, CDHDR, and CDPOS.
- Background Execution: For extractions covering more than a few months of data or running in a system with high transaction volume, always execute the program in the background to prevent dialog process timeouts.
a Sample Query abap
REPORT z_pm_po_extract.
" ====================================================================
" SELECTION SCREEN
" ====================================================================
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS: s_aedat FOR sy-datum OBLIGATORY.
SELECT-OPTIONS: s_bukrs FOR ekko-bukrs.
SELECT-OPTIONS: s_bsart FOR ekko-bsart.
PARAMETERS: p_sysid TYPE string DEFAULT '[Your SAP System ID]'.
SELECTION-SCREEN END OF BLOCK b1.
" ====================================================================
" DATA DECLARATIONS
" ====================================================================
TYPES: BEGIN OF ty_event_log,
purchaseordernumber TYPE ebeln,
activityname TYPE string,
eventtime TYPE timestamp,
sourcesystem TYPE string,
lastdataupdate TYPE timestamp,
vendorid TYPE lifnr,
username TYPE ernam,
totalnetamount TYPE netwr,
purchaserequisitionnumber TYPE banfn,
requesteddeliverydate TYPE eedat,
documenttype TYPE bsart,
END OF ty_event_log.
DATA: lt_event_log TYPE TABLE OF ty_event_log,
ls_event_log TYPE ty_event_log.
DATA: lt_ekko TYPE TABLE OF ekko,
lt_ekpo TYPE TABLE OF ekpo.
" ====================================================================
" START OF SELECTION
" ====================================================================
START-OF-SELECTION.
" Get current timestamp for LastDataUpdate
GET TIME STAMP FIELD ls_event_log-lastdataupdate.
ls_event_log-sourcesystem = p_sysid.
" --- Initial Data Selection: Purchase Orders in Scope ---
SELECT * FROM ekko INTO TABLE lt_ekko
WHERE aedat IN s_aedat
AND bukrs IN s_bukrs
AND bsart IN s_bsart.
IF lt_ekko IS INITIAL.
MESSAGE 'No Purchase Orders found for the given criteria.' TYPE 'S' DISPLAY LIKE 'E'.
RETURN.
ENDIF.
SELECT * FROM ekpo INTO TABLE lt_ekpo
FOR ALL ENTRIES IN lt_ekko
WHERE ebeln = lt_ekko-ebeln.
" --- 1. Purchase Requisition Created ---
SELECT ban.banfn, ban.erdat, ban.erzet, ban.ernam,
ekpo.ebeln, ekpo.netwr, ekpo.eindt, ekpo.bsart, ekpo.lifnr, ekko.bukrs
FROM eban AS ban
INNER JOIN ekpo AS ekpo ON ban.banfn = ekpo.banfn AND ban.bnfpo = ekpo.bnfpo
INNER JOIN ekko AS ekko ON ekpo.ebeln = ekko.ebeln
WHERE ekko.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
INTO TABLE @DATA(lt_pr_created).
LOOP AT lt_pr_created INTO DATA(ls_pr_created).
ls_event_log-purchaseordernumber = ls_pr_created-ebeln.
ls_event_log-activityname = 'Purchase Requisition Created'.
CONVERT DATE ls_pr_created-erdat TIME ls_pr_created-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-vendorid = ls_pr_created-lifnr.
ls_event_log-username = ls_pr_created-ernam.
ls_event_log-totalnetamount = ls_pr_created-netwr.
ls_event_log-purchaserequisitionnumber = ls_pr_created-banfn.
ls_event_log-requesteddeliverydate = ls_pr_created-eindt.
ls_event_log-documenttype = ls_pr_created-bsart.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" --- 2. Purchase Requisition Approved (via Change Docs on Release Indicator) ---
SELECT h.objectid, h.udate, h.utime, h.username
FROM cdhdr AS h
INNER JOIN cdpos AS p ON h.objectclas = p.objectclas AND h.objectid = p.objectid AND h.changenr = p.changenr
INNER JOIN ekpo AS ekpo ON h.objectid = ekpo.banfn
INNER JOIN ekko AS ekko ON ekpo.ebeln = ekko.ebeln
WHERE h.objectclas = 'BANF'
AND p.tabname = 'EBAN'
AND p.fname = 'FRGZU'
AND p.value_new = 'X' "Configure based on your system release indicator for 'Approved'
AND ekko.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
INTO TABLE @DATA(lt_pr_approved).
LOOP AT lt_pr_approved INTO DATA(ls_pr_approved).
SELECT SINGLE ebeln FROM ekpo INTO ls_event_log-purchaseordernumber WHERE banfn = ls_pr_approved-objectid.
ls_event_log-activityname = 'Purchase Requisition Approved'.
CONVERT DATE ls_pr_approved-udate TIME ls_pr_approved-utime INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-username = ls_pr_approved-username.
" Other attributes can be populated with another SELECT if needed.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" --- 3. Purchase Order Created ---
LOOP AT lt_ekko INTO DATA(ls_ekko_created).
ls_event_log-purchaseordernumber = ls_ekko_created-ebeln.
ls_event_log-activityname = 'Purchase Order Created'.
CONVERT DATE ls_ekko_created-aedat TIME ls_ekko_created-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-vendorid = ls_ekko_created-lifnr.
ls_event_log-username = ls_ekko_created-ernam.
ls_event_log-totalnetamount = ls_ekko_created-rlwrt.
ls_event_log-purchaserequisitionnumber = ''. "Can be enriched later if needed
ls_event_log-requesteddeliverydate = ''. "Can be enriched from EKPO
ls_event_log-documenttype = ls_ekko_created-bsart.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" --- 4. Purchase Order Approved (via Change Docs on Release Indicator) ---
SELECT h.objectid, h.udate, h.utime, h.username
FROM cdhdr AS h
INNER JOIN cdpos AS p ON h.objectclas = p.objectclas AND h.objectid = p.objectid AND h.changenr = p.changenr
WHERE h.objectclas = 'EINKBELEG'
AND p.tabname = 'EKKO'
AND p.fname = 'FRGKE'
AND p.value_new = 'R' "R for Released
AND h.objectid IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
INTO TABLE @DATA(lt_po_approved).
LOOP AT lt_po_approved INTO DATA(ls_po_approved).
ls_event_log-purchaseordernumber = ls_po_approved-objectid.
ls_event_log-activityname = 'Purchase Order Approved'.
CONVERT DATE ls_po_approved-udate TIME ls_po_approved-utime INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-username = ls_po_approved-username.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" --- 5. Purchase Order Sent to Vendor ---
SELECT n.objky, n.vstat, n.datvr, n.uhrvr, e.ernam
FROM nast AS n
INNER JOIN ekko AS e ON n.objky = e.ebeln
WHERE n.kappl = 'EF' "Application for Purchasing
AND n.kschl = '[Your PO Output Type]' "e.g. NEU
AND n.vstat = '1' "Successfully processed
AND n.objky IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
INTO TABLE @DATA(lt_po_sent).
LOOP AT lt_po_sent INTO DATA(ls_po_sent).
ls_event_log-purchaseordernumber = ls_po_sent-objky.
ls_event_log-activityname = 'Purchase Order Sent to Vendor'.
CONVERT DATE ls_po_sent-datvr TIME ls_po_sent-uhrvr INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-username = ls_po_sent-ernam.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" --- 6. Purchase Order Changed ---
SELECT objectid, udate, utime, username FROM cdhdr
WHERE objectclas = 'EINKBELEG'
AND tcode IN ('ME22', 'ME22N')
AND objectid IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
INTO TABLE @DATA(lt_po_changed).
LOOP AT lt_po_changed INTO DATA(ls_po_changed).
ls_event_log-purchaseordernumber = ls_po_changed-objectid.
ls_event_log-activityname = 'Purchase Order Changed'.
CONVERT DATE ls_po_changed-udate TIME ls_po_changed-utime INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-username = ls_po_changed-username.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" --- 7. Goods Receipt Posted & 9. Goods Returned ---
SELECT e.ebeln, m.budat, m.cpudt, m.cputm, m.usnam, b.shkzg, b.bwart
FROM mkpf AS m
INNER JOIN mseg AS s ON m.mblnr = s.mblnr AND m.mjahr = s.mjahr
INNER JOIN t156 AS t ON s.bwart = t.bwart
INNER JOIN ekbe AS e ON s.ebeln = e.ebeln AND s.ebelp = e.ebelp AND s.mblnr = e.belnr AND s.mjahr = e.gjahr
WHERE e.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
AND e.bwart IN ('101', '102', '122', '123') "GR, GR Reversal, Return
INTO TABLE @DATA(lt_goods_mvmt).
LOOP AT lt_goods_mvmt INTO DATA(ls_goods_mvmt).
ls_event_log-purchaseordernumber = ls_goods_mvmt-ebeln.
IF ls_goods_mvmt-bwart = '101'.
ls_event_log-activityname = 'Goods Receipt Posted'.
ELSE.
ls_event_log-activityname = 'Goods Returned'.
ENDIF.
CONVERT DATE ls_goods_mvmt-cpudt TIME ls_goods_mvmt-cputm INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-username = ls_goods_mvmt-usnam.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" --- 8. Services Confirmation Entered ---
SELECT h.erdat, h.erzeit, h.ernam, l.ebeln
FROM essr AS h
INNER JOIN esll AS l ON h.lblni = l.lblni
WHERE l.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
INTO TABLE @DATA(lt_services).
LOOP AT lt_services INTO DATA(ls_services).
ls_event_log-purchaseordernumber = ls_services-ebeln.
ls_event_log-activityname = 'Services Confirmation Entered'.
CONVERT DATE ls_services-erdat TIME ls_services-erzeit INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-username = ls_services-ernam.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" --- 10. Invoice Received ---
SELECT r.ebeln, r.cpudt, r.cputm, r.usnam
FROM rbkp AS r
WHERE r.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
INTO TABLE @DATA(lt_invoice_rcvd).
LOOP AT lt_invoice_rcvd INTO DATA(ls_invoice_rcvd).
ls_event_log-purchaseordernumber = ls_invoice_rcvd-ebeln.
ls_event_log-activityname = 'Invoice Received'.
CONVERT DATE ls_invoice_rcvd-cpudt TIME ls_invoice_rcvd-cputm INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-username = ls_invoice_rcvd-usnam.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" --- 11. Invoice Paid ---
SELECT b.ebeln, s.augdt, s.augbl, b.usnam
FROM rbkp AS b
INNER JOIN bseg AS e ON b.belnr = e.belnr AND b.gjahr = e.gjahr
INNER JOIN bsak AS s ON e.bukrs = s.bukrs AND e.belnr = s.belnr AND e.gjahr = s.gjahr AND e.buzei = s.buzei
WHERE b.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
AND s.augdt IS NOT NULL
INTO TABLE @DATA(lt_invoice_paid).
LOOP AT lt_invoice_paid INTO DATA(ls_invoice_paid).
ls_event_log-purchaseordernumber = ls_invoice_paid-ebeln.
ls_event_log-activityname = 'Invoice Paid'.
CONVERT DATE ls_invoice_paid-augdt INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-username = ls_invoice_paid-usnam.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" --- 12. Purchase Order Completed & 13. Purchase Order Deleted (via Change Docs) ---
SELECT h.objectid, h.udate, h.utime, h.username, p.fname
FROM cdhdr AS h
INNER JOIN cdpos AS p ON h.changenr = p.changenr
INNER JOIN ekpo AS ekpo ON h.objectid = |{ ekpo.ebeln }{ ekpo.ebelp }|
WHERE h.objectclas = 'EINKBELEG'
AND p.tabname = 'EKPO'
AND p.fname IN ('ELIKZ', 'EREKZ', 'LOEKZ')
AND p.value_new = 'X'
AND ekpo.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
INTO TABLE @DATA(lt_po_status_change).
LOOP AT lt_po_status_change INTO DATA(ls_po_status_change).
ls_event_log-purchaseordernumber = substring( val = ls_po_status_change-objectid, off = 0, len = 10 ).
CASE ls_po_status_change-fname.
WHEN 'LOEKZ'.
ls_event_log-activityname = 'Purchase Order Deleted'.
WHEN 'ELIKZ' OR 'EREKZ'.
"This logic may need refinement to check if both are now set.
ls_event_log-activityname = 'Purchase Order Completed'.
ENDCASE.
CONVERT DATE ls_po_status_change-udate TIME ls_po_status_change-utime INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
ls_event_log-username = ls_po_status_change-username.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
" --- Final Output to CSV ---
CALL METHOD cl_gui_frontend_services=>gui_download
EXPORTING
filename = 'C:\temp\po_event_log.csv'
filetype = 'ASC'
CHANGING
data_tab = lt_event_log.