Your Purchase to Pay - Invoice Processing Data Template
Your Purchase to Pay - Invoice Processing Data Template
- Recommended attributes to collect
- Key activities to track
- Extraction guidance for SAP S/4HANA
Purchase to Pay - Invoice Processing Attributes
| Name | Description | ||
|---|---|---|---|
| Invoice Number InvoiceNumber | The unique identifier for the vendor invoice document, serving as the primary case identifier for the process. | ||
| Description The Invoice Number is the unique identifier assigned to each vendor invoice within SAP S/4HANA. It links all related activities, such as creation, parking, approval, and payment, into a single, cohesive process instance. In process mining, this attribute is fundamental for tracking the end-to-end journey of each invoice. It allows for the reconstruction of the entire process flow, from receipt to final payment, enabling analysis of cycle times, bottlenecks, and process variations at the individual invoice level. Why it matters It is the essential key to connect all related events, allowing for a complete trace of an invoice's lifecycle through the system. Where to get This is the Accounting Document Number, found in table BKPF, field BELNR. Examples 190000000119000000451900000132 | |||
| Activity Name ActivityName | The name of the business activity or event that occurred at a specific point in time for an invoice. | ||
| Description The Activity Name describes a specific step or status change within the invoice processing lifecycle. Examples include 'Invoice Document Created', 'Invoice Sent For Approval', 'Payment Block Set', and 'Payment Executed'. This attribute is crucial for constructing the process map, which visually represents the flow of activities. Analyzing the sequence, frequency, and duration between these activities helps identify bottlenecks, rework loops, and non-compliant process variations. It forms the backbone of any process mining analysis. Why it matters It defines the steps in the process, enabling the visualization of process maps and the analysis of process flows and variations. Where to get Derived from a combination of SAP transaction codes (SY-TCODE), change document object statuses (CDHDR/CDPOS), and specific field values indicating status changes. Examples Invoice ParkedInvoice ApprovedPayment Executed | |||
| Event Time EventTime | The precise date and time when the activity occurred. | ||
| Description Event Time is the timestamp that records exactly when a specific activity happened. This data is essential for calculating durations, cycle times, and waiting times between different steps in the process. In process mining analysis, accurate timestamps are used to measure performance KPIs like 'Average Invoice Cycle Time' and 'Invoice Approval Cycle Time'. By analyzing the time elapsed between activities, organizations can pinpoint bottlenecks where invoices are delayed and identify opportunities for process acceleration. Why it matters This timestamp is the foundation for all time-based analysis, including performance monitoring, bottleneck identification, and SLA tracking. Where to get Typically sourced from change document tables CDHDR (Header) and CDPOS (Item), using fields UDATE and UTIME. For some events, it may come from creation or entry dates in tables like BKPF (CPUDT, CPUTM). Examples 2023-04-15T10:30:00Z2023-04-18T14:05:21Z2023-05-02T09:00:00Z | |||
| Company Code CompanyCode | The organizational unit representing a legally independent company for which financial statements are created. | ||
| Description The Company Code is a fundamental organizational unit in SAP Finance. Each invoice is assigned to a specific company code, which determines the legal entity responsible for the transaction. In process mining, filtering or comparing by Company Code is essential for analyzing process performance across different business units, legal entities, or countries. It helps identify regional differences in efficiency, compliance, and automation levels, supporting targeted improvement initiatives. Why it matters It allows for segmenting and comparing invoice processing performance across different legal entities or geographical locations within the organization. Where to get This is a standard field in the document header table BKPF, field BUKRS. Examples 1000US01DE01 | |||
| Document Type DocumentType | A code that classifies different types of accounting documents, such as vendor invoices or credit memos. | ||
| Description The Document Type is used in SAP to distinguish between different business transactions. For instance, 'KR' typically represents a standard vendor invoice, while 'KG' might be a vendor credit memo. Analyzing by document type allows for segmenting the process to understand how different types of transactions are handled. For example, the process for a credit memo may be significantly different from that of a standard invoice. This segmentation provides more accurate and relevant process insights. Why it matters It helps differentiate between various kinds of financial transactions, such as standard invoices and credit memos, which often follow different process paths. Where to get Found in the document header table BKPF, field BLART. Examples KRREKG | |||
| Invoice Amount AmountInCompanyCodeCurrency | The total gross amount of the invoice in the company code's local currency. | ||
| Description This attribute represents the total value of the invoice. It is a key metric for understanding the financial impact and scale of the invoice processing operation. Analyzing invoice amounts helps to prioritize high-value invoices for faster processing, identify trends in spending, and correlate process issues with financial value. For example, it can be used to investigate if high-value invoices are more likely to be blocked or have longer approval times. Why it matters Provides financial context to the process, allowing for analysis based on monetary value, such as identifying if high-value invoices are processed differently. Where to get This value is typically derived from the sum of relevant line items in table BSEG, field WRBTR (Amount in local currency). Examples 1500.75125000.00850.20 | |||
| Payment Block Reason PaymentBlockReason | A code indicating why an invoice is blocked from being paid. | ||
| Description When an invoice is blocked for payment, this attribute provides the specific reason for the block, such as 'Quantity Discrepancy' or 'Price Mismatch'. These reasons are configured in SAP to standardize exception handling. This attribute is crucial for the 'Payment Block Occurrence & Duration' dashboard. Analyzing the frequency of different block reasons helps identify the root causes of payment delays, such as issues with specific vendors, materials, or internal processes, allowing for targeted corrective actions. Why it matters Provides the specific root cause for payment blocks, enabling targeted analysis to reduce delays and improve first-time-right processing. Where to get Located in the vendor line item of table BSEG, field ZLSPR (Payment Block Key). Examples RIA | |||
| Payment Due Date PaymentDueDate | The date by which the invoice must be paid to avoid being overdue. | ||
| Description The Payment Due Date is the calculated date on which payment to the vendor is due, based on the invoice date and the agreed payment terms. It serves as a critical deadline in the process. This attribute is essential for the 'On-Time Payment Rate' KPI and the 'Vendor Payment Performance' dashboard. By comparing the actual payment date with the due date, a company can measure its ability to meet payment obligations, which affects vendor relationships and financial reputation. Why it matters It is the primary benchmark for measuring on-time payment performance, which is crucial for maintaining good vendor relationships and avoiding late fees. Where to get This date is often directly available in the vendor line item in table BSEG, field ZFBDT (Baseline date for due date calculation). The net due date is calculated from this baseline date and payment terms. Examples 2023-05-302023-06-152023-07-01 | |||
| Purchase Order PurchasingDocument | The purchase order number to which the invoice is related. | ||
| Description The Purchasing Document number links the vendor invoice to the original purchase order (PO). This link is fundamental for the three-way match process, which verifies the invoice against the PO and goods receipt. Analyzing by this attribute helps understand issues related to PO-backed vs. non-PO invoices. It is key to investigating matching discrepancies and understanding the efficiency of the procurement part of the process. Why it matters Links the invoice to the procurement process, which is essential for analyzing matching discrepancies and PO compliance. Where to get This information is typically found in the document segment table BSEG, field EBELN (Purchase Document Number). Examples 450000123445000056784500009012 | |||
| User Name UserName | The SAP user ID of the person or system that performed the activity. | ||
| Description This attribute identifies the user who executed a specific transaction or created a document. It can be an individual's user ID or a system ID for automated batch jobs. Analyzing by user helps in understanding workload distribution, identifying training needs, and spotting unusual user behavior. For instance, it can highlight which users frequently handle exceptions or which invoices are processed automatically (e.g., user 'BATCHUSER'), which is key for calculating the 'Invoice Automation Rate' KPI. Why it matters It attributes process activities to specific users or system accounts, enabling workload analysis, performance comparison, and automation detection. Where to get Sourced from fields like BKPF-USNAM (Entered by) or CDHDR-USERNAME (Changed by). Examples SMITHJMUELLERTWF-BATCH | |||
| Vendor Number VendorNumber | The unique identifier for the vendor who submitted the invoice. | ||
| Description The Vendor Number identifies the supplier or creditor associated with the invoice. It links the invoice transaction to the vendor master data. This attribute is critical for vendor-centric analysis, such as evaluating 'Vendor Payment Performance' or identifying vendors who frequently submit problematic invoices that lead to exceptions or payment blocks. It helps in managing vendor relationships and assessing supplier reliability. Why it matters Enables analysis of process performance by vendor, helping to identify patterns, manage relationships, and assess supplier-related issues. Where to get Typically found in the accounting document segment table BSEG, field LIFNR. Examples 100345700012V9832 | |||
| Approval Cycle Count ApprovalCycleCount | A count of how many times an invoice was sent for approval. | ||
| Description This metric counts the number of times the 'Invoice Sent For Approval' activity occurs for a single invoice. A count greater than one indicates that the invoice was rejected or sent back at least once, requiring a new approval cycle. This attribute directly supports the 'First-Pass Approval Rate' KPI. By analyzing invoices with high approval cycle counts, organizations can identify the reasons for failed approvals, such as insufficient information or incorrect coding, and take steps to improve the process. Why it matters Quantifies rework within the approval sub-process, helping to measure the first-time-right rate and identify reasons for approval rejections. Where to get Calculated by counting the occurrences of the 'Invoice Sent For Approval' activity for each unique InvoiceNumber. Examples 123 | |||
| Clearing Document No. ClearingDocumentNumber | The document number that clears the invoice, typically representing the payment document. | ||
| Description The Clearing Document Number links an open invoice item to the transaction that clears it, which is almost always the payment document. This confirms that the invoice has been paid. This attribute is the definitive link between an invoice and its payment. It is used to identify the 'Payment Executed' activity and its corresponding timestamp, which is essential for calculating the end-to-end cycle time and on-time payment rate. Why it matters Confirms that an invoice has been paid and links it to the specific payment transaction, which is critical for cycle time and payment performance analysis. Where to get Found in the document segment table BSEG, field AUGBL (Clearing Document Number). Examples 150000000115000000231500000088 | |||
| Extraction Timestamp ExtractionTimestamp | The date and time when the data was extracted from the source system. | ||
| Description This attribute records the timestamp of the data extraction event. It reflects the freshness of the data being analyzed in the process mining tool. In analysis, this is used to understand the recency of the insights generated. It is critical for operational monitoring dashboards to ensure that decisions are based on up-to-date information and to manage data refresh cycles effectively. Why it matters Indicates the freshness of the data, ensuring that analysis and reporting are based on the most current information available. Where to get This is not an SAP field. It is generated and added by the data extraction tool or ETL process at the time of data pull. Examples 2023-10-27T02:00:00Z2023-10-28T02:00:00Z2023-10-29T02:00:00Z | |||
| Invoice Date InvoiceDate | The date on which the vendor issued the invoice document. | ||
| Description The Invoice Date, also known as the document date, is the date provided by the vendor on the invoice. It is used as the starting point for calculating the payment due date based on the agreed payment terms. In analysis, this date is fundamental for financial calculations, such as determining invoice aging and eligibility for early payment discounts. It is a key input for the 'Early Payment Discount Capture Rate' KPI. Why it matters Serves as the baseline for calculating payment terms and due dates, which is essential for managing working capital and capturing discounts. Where to get Found in the document header table BKPF, field BLDAT (Document Date). Examples 2023-04-122023-05-152023-06-20 | |||
| Invoice Processing Time InvoiceProcessingTime | The total time elapsed from the first activity to the last activity for an invoice. | ||
| Description This metric calculates the total duration for processing a single invoice, typically from invoice creation or receipt to final payment. It provides a case-level summary of the end-to-end cycle time. This calculated attribute is the basis for the 'Average Invoice Cycle Time' KPI and the 'End-to-End Invoice Cycle Time' dashboard. It allows for quick identification of the longest-running cases and analysis of factors that contribute to extended processing times. Why it matters Measures the end-to-end efficiency of the process for each invoice, highlighting cases with exceptionally long durations that require investigation. Where to get Calculated by taking the difference between the timestamp of the last event and the first event for each unique InvoiceNumber. Examples 10 days 4 hours25 days 1 hour5 days 8 hours | |||
| Is Automated IsAutomated | A flag indicating whether an activity was performed by an automated system user. | ||
| Description This boolean attribute is true if the user associated with an activity is a known system or batch account, such as 'WF-BATCH' or 'SAP_SYSTEM'. It helps distinguish between manual and automated process steps. This attribute is essential for calculating the 'Invoice Automation Rate' KPI. By analyzing which parts of the process are automated, organizations can measure the success of their automation initiatives and identify further opportunities to reduce manual effort and improve efficiency. Why it matters Distinguishes between manual and system-driven activities, which is fundamental for measuring automation rates and identifying opportunities for further automation. Where to get Derived from the UserName attribute. A mapping or rule is created to classify specific user IDs as 'automated'. Examples truefalse | |||
| Is Paid On Time IsPaidOnTime | A flag that is true if the invoice was paid on or before its payment due date. | ||
| Description This boolean attribute is the result of comparing the actual payment date (the timestamp of the 'Payment Executed' activity) with the 'Payment Due Date'. It provides a clear, binary outcome for each invoice's payment status. This is the core calculation for the 'On-Time Payment Rate' KPI. It allows for easy filtering and analysis to understand the characteristics of late payments, such as common vendors, company codes, or invoice amounts associated with delays. Why it matters Directly measures adherence to payment terms, which is a critical KPI for vendor relationship management and financial operations. Where to get Calculated by comparing the EventTime of the 'Payment Executed' activity against the PaymentDueDate attribute. (Payment Date <= PaymentDueDate). Examples truefalse | |||
| Is Rework IsRework | A flag indicating if an invoice has undergone rework activities, such as a rejected approval or a removed payment block. | ||
| Description This attribute flags invoices that have experienced one or more rework loops. Rework is identified by specific sequences of activities, for example, 'Invoice Approved' following 'Invoice Rejected', or 'Payment Block Removed' after 'Payment Block Set'. This attribute simplifies the calculation of the 'Invoice Rework Rate' KPI. It allows analysts to easily isolate and investigate cases with rework to understand the root causes of inefficiency and repeated manual effort. Why it matters Identifies inefficient process flows where work has to be repeated, helping to quantify waste and pinpoint the root causes of process exceptions. Where to get Calculated based on the sequence of activities in the event log. For example, if 'Invoice Rejected' occurs in the trace for an invoice, this flag would be set to true. Examples truefalse | |||
| Payment Terms PaymentTerms | The code defining the payment conditions agreed upon with the vendor, such as due dates and discount periods. | ||
| Description Payment Terms define the rules for paying an invoice, including any available discounts for early payment. For example, 'Z030' might mean 'Payable within 30 days net'. This attribute is essential for financial planning and working capital optimization. In process mining, it is used to calculate the 'Payment Due Date' and to determine eligibility for early payment discounts, directly supporting the 'Early Payment Discount Capture Rate' KPI. Why it matters Defines the rules for payment due dates and discounts, directly impacting on-time payment KPIs and working capital management. Where to get Found in the vendor line item in table BSEG, field ZTERM (Terms of Payment Key). Examples 0001Z030NT60 | |||
| Reversal Reason ReversalReason | A code indicating the reason why an invoice document was reversed. | ||
| Description If an invoice is posted incorrectly, it is often reversed. The Reversal Reason code explains why this action was taken, for example, 'Incorrect posting date' or 'Data entry error'. Analyzing reversal reasons helps identify patterns of errors in the invoice posting process. This insight can be used to improve training, enhance system controls, or address recurring issues that lead to financial rework and administrative overhead. Why it matters Explains why invoices were cancelled, providing direct insight into sources of error and rework in the posting process. Where to get Found in the header of the original document in table BKPF, field STGRD (Reversal reason). Examples 010205 | |||
| Source System ID SourceSystemId | The identifier of the source SAP S/4HANA system from which the data was extracted. | ||
| Description This attribute specifies the originating system, for example, 'S4H_PROD' or 'ERP_EU'. It is particularly important in environments with multiple ERP instances or a mix of legacy and modern systems. For analysis, it allows for comparing process performance across different systems or regions. It ensures data provenance and is crucial for data governance and troubleshooting when data from multiple sources is combined in a central process mining platform. Why it matters It provides context about the data's origin, which is essential for data governance and for comparing processes across different systems or company locations. Where to get This value is typically derived from the SAP system ID (sy-sysid) during data extraction or configured as a static value in the ETL pipeline. Examples S4PS4H_PROD_100ECC_EU | |||
Purchase to Pay - Invoice Processing Activities
| Activity | Description | ||
|---|---|---|---|
| Invoice Approved | This activity signifies that the invoice has been approved by the designated authority. This is captured when the approval workflow concludes successfully or a release indicator is set. | ||
| Why it matters This is a critical milestone that unblocks the invoice for payment. Delays in approvals are a common bottleneck, and tracking this activity helps pinpoint slow approvers or process steps. Where to get This can be inferred from the final release step in an SAP workflow or by tracking changes to release status fields in tables associated with the invoice or its purchasing document. Capture Infer from workflow completion events or changes in a document's release status field. Event type inferred | |||
| Invoice Document Created | This is the first event, marking the creation of an invoice document in SAP. It can be captured when a user saves a new invoice document, which could be in a parked or pre-posted state. | ||
| Why it matters This activity marks the start of the invoice processing lifecycle. Analyzing the time from this event to others is crucial for measuring overall processing lead time. Where to get This event is captured from the creation date and time (CPUDT, CPUTM) in the document header table, typically BKPF or RBKP for logistics invoices. The transaction code (BKPF-TCODE) like FB60, MIRO, or MIR7 indicates the creation method. Capture Use creation timestamp from BKPF-CPUDT and BKPF-CPUTM for the invoice document. Event type explicit | |||
| Invoice Posted | This is a key financial event where the parked or approved invoice is formally posted to the General Ledger. This action recognizes the liability to the vendor. | ||
| Why it matters Posting is a major milestone that separates data entry and approval from the financial settlement phase. The time from invoice creation to posting is a key measure of internal processing efficiency. Where to get This event is identified by the Posting Date (BKPF-BUDAT) on the document header. For documents that are parked first, the transition to a posted status provides the event timestamp. Capture Use the posting date (BKPF-BUDAT) as the event timestamp. Event type explicit | |||
| Invoice Reversed | An activity representing the reversal of a previously posted invoice document. This is a terminal event for an incorrect invoice, which is then often re-entered correctly. | ||
| Why it matters Reversals indicate critical errors that were not caught earlier in the process. Tracking their frequency and root causes is essential for process improvement and reducing financial inaccuracies. Where to get A reversal is identified when a reversing document is created. The original document header (BKPF) will contain the reversing document number (BKPF-STBLG) and vice versa. The posting date of the reversal document is the event time. Capture Identify documents that have a value in the BKPF-STBLG field and use the reversal document's posting date. Event type explicit | |||
| Payment Executed | This is the final activity in the standard process, where the payment is made and the invoice is cleared. This signifies that the funds have been disbursed to the vendor. | ||
| Why it matters This marks the end of the P2P invoice lifecycle. It is essential for calculating the total end-to-end cycle time and for measuring on-time payment performance against the due date. Where to get This event is captured from the clearing document information on the vendor line item. The Clearing Date (BSEG-AUGDT) and Clearing Document (BSEG-AUGBL) indicate that payment has been made. Capture Use the clearing date (BSEG-AUGDT) from the cleared vendor line item. Event type explicit | |||
| Invoice Data Updated | This activity reflects a change made to the invoice document after its initial creation. This is common during rework cycles following a rejection or to correct errors. | ||
| Why it matters Frequent updates signal rework and potential data quality issues at the point of entry. Tracking these changes helps quantify the effort spent on corrections and identify common errors. Where to get Changes to key fields are logged in SAP's change document tables, CDHDR (header) and CDPOS (item). Events can be generated by filtering for changes to the relevant invoice object. Capture Extract change events from tables CDHDR and CDPOS for the invoice object. Event type explicit | |||
| Invoice Parked | Represents an invoice that has been entered into the system but is not yet posted to the general ledger. Parking is used to save incomplete invoices or for later review before posting. | ||
| Why it matters Parking indicates a deliberate pause in the process. Tracking the duration and frequency of parked invoices helps identify reasons for delays before the formal posting and approval cycle begins. Where to get This can be identified by documents created via parking transactions (e.g., MIR7, FV60) or by checking specific status fields in the BKPF table or dedicated parked document tables like VBKPF. Capture Identify documents created via parking transactions or check for a parked document status. Event type explicit | |||
| Invoice Rejected | Represents the rejection of an invoice during the approval process. This event triggers rework, requiring correction and resubmission. | ||
| Why it matters Invoice rejections are a key indicator of process inefficiency and data quality issues. Analyzing rejection frequency and reasons helps identify opportunities for improvement and training. Where to get This is inferred from specific status updates in an SAP workflow, such as a 'rejected' status, or from events that cancel the current approval workflow, sending it back to the processor. Capture Infer from workflow status changes indicating rejection. Event type inferred | |||
| Invoice Sent For Approval | This activity marks the initiation of a formal approval workflow for the invoice. This is often inferred when the invoice's status changes to 'pending approval' or a workflow item is generated. | ||
| Why it matters This is the starting point for measuring the approval cycle time. Understanding when approvals start is essential for identifying bottlenecks in the approval workflow itself. Where to get This is typically inferred from the start of an SAP Business Workflow (SWW_WI2OBJ table) linked to the invoice object (e.g., BUS2081) or a change in a custom status field on the document header. Capture Infer from the creation of a workflow item related to the invoice document. Event type inferred | |||
| Late Payment Executed | This is a calculated event that occurs when an invoice's payment is executed after its calculated due date. It is derived by comparing two date fields. | ||
| Why it matters This activity directly supports on-time payment KPIs and helps identify vendors or business units with frequent late payments, which can harm vendor relationships and lead to penalties. Where to get This is calculated by comparing the Clearing Date (BSEG-AUGDT) with the Net Due Date. The due date is itself calculated from the Baseline Date (BSEG-ZFBDT) and payment terms (BSEG-ZTERM). Capture Derive by comparing BSEG-AUGDT > (BSEG-ZFBDT + payment term days). Event type calculated | |||
| Payment Block Removed | Represents the resolution of an issue, where a previously set payment block is removed. This makes the invoice eligible for payment again. | ||
| Why it matters The time between a block being set and removed represents the resolution time for a process exception. Shortening this duration is key to improving efficiency and vendor relations. Where to get This event is captured when the Payment Block Key field (BSEG-ZLSPR) is cleared. This change is logged in the CDHDR and CDPOS tables, providing a timestamp for the removal. Capture Identify when the BSEG-ZLSPR field is cleared via change documents (CDHDR/CDPOS). Event type explicit | |||
| Payment Block Set | An activity where a block is intentionally placed on an invoice to prevent it from being paid. This is often due to discrepancies in price or quantity, or a pending credit memo. | ||
| Why it matters Payment blocks are a primary cause of late payments and vendor disputes. Analyzing the frequency, duration, and reasons for blocks is critical for improving on-time payment rates. Where to get This event is captured by tracking changes to the Payment Block Key field (BSEG-ZLSPR) in the invoice line item. The change logs in CDHDR and CDPOS provide the timestamp and user for when the block was set. Capture Identify when the BSEG-ZLSPR field is populated via change documents (CDHDR/CDPOS). Event type explicit | |||
| Payment Proposal Created | The invoice is selected and included in a payment proposal as part of a payment run. This is the first step in the automated payment process. | ||
| Why it matters This activity indicates the intent to pay. Delays between this step and final payment execution can reveal issues with the payment run process, approvals, or bank communication. Where to get This can be found in the payment run tables, specifically REGUP, which contains the items included in a payment proposal. The run date in the corresponding REGUH table provides the timestamp. Capture Identify when an invoice appears in the REGUP table from a payment proposal run. Event type explicit | |||
Extraction Guides
Steps
- Prerequisites and Authorization: Ensure the user executing the extraction has the necessary authorizations in SAP S/4HANA to access the required Core Data Services (CDS) views. Key views include
I_InvoiceDocument,I_OperationalAcctgDocItem,I_ChangeDocument,I_ChangeDocumentItem, andI_PaymentProposalItem. The user will also need permissions to execute queries via the chosen interface, such as an OData service or a direct SQL connection. - Identify Your Connection Method: Determine how you will connect to the SAP S/4HANA system to execute the SQL query. Common methods include using SAP Data Services, SAP Data Intelligence, a third-party ETL tool with an SAP connector, or a direct SQL connection to the SAP HANA database if permitted by your organization's security policies.
- Define Extraction Parameters: Before executing the query, define the key parameters. Specify the date range for the extraction, for instance,
CreationDatebetween'YYYY-MM-DD'and'YYYY-MM-DD'. Also, identify the specificCompanyCodevalues you want to include to limit the scope of the data extraction. - Customize the SQL Query: Copy the provided SQL query into your chosen SQL client or data extraction tool. Carefully review the placeholders, such as
'{StartDate}','{EndDate}', and('{CompanyCode1}', '{CompanyCode2}'). Replace these placeholders with the actual values you defined in the previous step. You may also need to adjust field names for workflow status based on your specific SAP configuration. - Execute the Query: Run the complete SQL query against the SAP S/4HANA database or through the appropriate service layer. The query is designed to be comprehensive and may take a significant amount of time to run depending on the data volume and the selected date range. Monitor the execution for any potential errors or timeouts.
- Review Initial Results: Once the query completes, perform a quick review of the output. Check that the columns
InvoiceNumber,ActivityName, andEventTimeare populated. Verify that you see a variety of different activities in theActivityNamecolumn, not just 'Invoice Document Created'. - Address Data Transformation: The query is structured to produce a clean event log format. However, ensure that the
EventTimecolumn is in a consistent timestamp format, such asYYYY-MM-DDTHH:MM:SS. The provided query combines date and time fields into a single timestamp where necessary. - Export the Data: Export the final result set from your tool into a CSV (Comma-Separated Values) file. This format is universally compatible with process mining tools, including ProcessMind.
- Prepare for Upload: Before uploading, confirm the CSV file uses UTF-8 encoding to prevent character issues. Ensure the column headers in the file exactly match the required attributes:
InvoiceNumber,ActivityName,EventTime,UserName,CompanyCode, etc. - Upload to ProcessMind: Upload the prepared CSV file into your process mining project. Map the columns from your file to the corresponding case ID, activity name, and timestamp fields in the tool's data model configuration.
Configuration
- CDS Views Used: The primary data sources are standard SAP CDS views. The key views are
I_InvoiceDocumentfor header data,I_OperationalAcctgDocItemfor financial posting and clearing details, andI_ChangeDocumentwithI_ChangeDocumentItemfor tracking historical changes to invoice attributes like payment blocks and workflow status. - Date Range Filtering: It is critical to filter the data by a specific date range to manage performance. The provided query uses a placeholder for the
CreationDatein theI_InvoiceDocumentview. A recommended starting point is a 3 to 6 month period of data. - Company Code Filter: To ensure the extraction is relevant and manageable, always filter by one or more
CompanyCode. The query includes a placeholderWHERE inv.CompanyCode IN ('{CompanyCode1}', '{CompanyCode2}')for this purpose. - Document Type Filter: You can further refine the extraction by filtering on
InvoiceDocumentType. For example, you may want to include standard vendor invoices (RE) but exclude credit memos. This can be added to theWHEREclause of the initial CTE. - Prerequisites: The user running the query needs appropriate display authorizations for financial and purchasing documents within the specified company codes. Access to the underlying HANA database via a SQL client is not standard and requires special permissions.
- Performance Considerations: Extracting data from change document tables (
I_ChangeDocument,I_ChangeDocumentItem) can be performance-intensive. Applying strict filters on date, company code, and object class (INCOMINGINVOICE) is essential to prevent long execution times.
a Sample Query sql
WITH InvoiceBase AS (
SELECT
inv.InvoiceDocument,
inv.FiscalYear,
inv.CompanyCode,
inv.Supplier AS VendorNumber,
inv.DocumentType,
inv.GrossInvoiceAmountInCoCoCrcy AS AmountInCompanyCodeCurrency,
inv.NetDueDate AS PaymentDueDate,
inv.PurchasingDocument,
inv.CreationDateTime,
inv.CreatedByUser,
accdoc.AccountingDocument,
accdoc.ClearingDate,
accdoc.ClearingJournalEntry,
accdoc.PaymentBlockReason,
accdoc.IsReversed
FROM I_InvoiceDocument AS inv
LEFT JOIN I_OperationalAcctgDocItem AS accdoc
ON inv.AccountingDocument = accdoc.AccountingDocument
AND inv.FiscalYear = accdoc.FiscalYear
AND inv.CompanyCode = accdoc.CompanyCode
WHERE
inv.CreationDate BETWEEN '{StartDate}' AND '{EndDate}'
AND inv.CompanyCode IN ('{CompanyCode1}', '{CompanyCode2}')
)
-- 1. Invoice Document Created
SELECT
InvoiceDocument AS "InvoiceNumber",
'Invoice Document Created' AS "ActivityName",
CreationDateTime AS "EventTime",
CreatedByUser AS "UserName",
CompanyCode AS "CompanyCode",
VendorNumber AS "VendorNumber",
AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
PaymentDueDate AS "PaymentDueDate",
DocumentType AS "DocumentType",
CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
PurchasingDocument AS "PurchasingDocument"
FROM InvoiceBase
UNION ALL
-- 2. Invoice Parked
SELECT
i.InvoiceDocument AS "InvoiceNumber",
'Invoice Parked' AS "ActivityName",
i.CreationDateTime AS "EventTime",
i.CreatedByUser AS "UserName",
i.CompanyCode AS "CompanyCode",
i.Supplier AS "VendorNumber",
i.GrossInvoiceAmountInCoCoCrcy AS "AmountInCompanyCodeCurrency",
i.NetDueDate AS "PaymentDueDate",
i.DocumentType AS "DocumentType",
CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
i.PurchasingDocument AS "PurchasingDocument"
FROM I_InvoiceDocument AS i
WHERE
i.InvoiceDocumentIsParked = 'X'
AND i.CreationDate BETWEEN '{StartDate}' AND '{EndDate}'
AND i.CompanyCode IN ('{CompanyCode1}', '{CompanyCode2}')
UNION ALL
-- 3, 4, 5. Workflow activities (Sent for Approval, Approved, Rejected) from Change Docs
SELECT
cdpos.ObjectValue AS "InvoiceNumber",
CASE
WHEN cdpos.ValueNew = '[StatusSentForApproval]' THEN 'Invoice Sent For Approval'
WHEN cdpos.ValueNew = '[StatusApproved]' THEN 'Invoice Approved'
WHEN cdpos.ValueNew = '[StatusRejected]' THEN 'Invoice Rejected'
END AS "ActivityName",
CAST(cdhdr.ChangeDate AS TIMESTAMP) + CAST(cdhdr.ChangeTime AS TIME) AS "EventTime",
cdhdr.UserName AS "UserName",
inv.CompanyCode AS "CompanyCode",
inv.VendorNumber AS "VendorNumber",
inv.AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
inv.PaymentDueDate AS "PaymentDueDate",
inv.DocumentType AS "DocumentType",
CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
inv.PurchasingDocument AS "PurchasingDocument"
FROM I_ChangeDocument AS cdhdr
JOIN I_ChangeDocumentItem AS cdpos ON cdhdr.ChangeDocument = cdpos.ChangeDocument
JOIN InvoiceBase AS inv ON cdpos.ObjectValue = inv.InvoiceDocument
WHERE
cdhdr.ObjectClassName = 'INCOMINGINVOICE'
AND cdpos.FieldName = '[WorkflowStatusFieldName]'
AND cdpos.ValueNew IN ('[StatusSentForApproval]', '[StatusApproved]', '[StatusRejected]')
UNION ALL
-- 6. Invoice Data Updated
SELECT
cdpos.ObjectValue AS "InvoiceNumber",
'Invoice Data Updated' AS "ActivityName",
CAST(cdhdr.ChangeDate AS TIMESTAMP) + CAST(cdhdr.ChangeTime AS TIME) AS "EventTime",
cdhdr.UserName AS "UserName",
inv.CompanyCode AS "CompanyCode",
inv.VendorNumber AS "VendorNumber",
inv.AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
inv.PaymentDueDate AS "PaymentDueDate",
inv.DocumentType AS "DocumentType",
CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
inv.PurchasingDocument AS "PurchasingDocument"
FROM I_ChangeDocument AS cdhdr
JOIN I_ChangeDocumentItem AS cdpos ON cdhdr.ChangeDocument = cdpos.ChangeDocument
JOIN InvoiceBase AS inv ON cdpos.ObjectValue = inv.InvoiceDocument
WHERE
cdhdr.ObjectClassName = 'INCOMINGINVOICE'
AND cdpos.FieldName IN ('GrossInvoiceAmount', 'DocumentDate', 'PaymentTerms')
AND cdhdr.ChangeDate BETWEEN '{StartDate}' AND '{EndDate}'
UNION ALL
-- 7 & 8. Payment Block Set/Removed
SELECT
inv.InvoiceDocument AS "InvoiceNumber",
CASE
WHEN cdpos.ValueNew <> '' AND cdpos.ValueOld = '' THEN 'Payment Block Set'
WHEN cdpos.ValueNew = '' AND cdpos.ValueOld <> '' THEN 'Payment Block Removed'
END AS "ActivityName",
CAST(cdhdr.ChangeDate AS TIMESTAMP) + CAST(cdhdr.ChangeTime AS TIME) AS "EventTime",
cdhdr.UserName AS "UserName",
inv.CompanyCode AS "CompanyCode",
inv.VendorNumber AS "VendorNumber",
inv.AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
inv.PaymentDueDate AS "PaymentDueDate",
inv.DocumentType AS "DocumentType",
cdpos.ValueNew AS "PaymentBlockReason",
inv.PurchasingDocument AS "PurchasingDocument"
FROM I_ChangeDocument AS cdhdr
JOIN I_ChangeDocumentItem AS cdpos ON cdhdr.ChangeDocument = cdpos.ChangeDocument
JOIN InvoiceBase AS inv ON cdpos.ObjectValue = inv.AccountingDocument
WHERE
cdhdr.ObjectClassName = 'BELEG'
AND cdpos.TableName = 'BSEG'
AND cdpos.FieldName = 'ZLSPR'
AND ( (cdpos.ValueNew <> '' AND cdpos.ValueOld = '') OR (cdpos.ValueNew = '' AND cdpos.ValueOld <> '') )
UNION ALL
-- 9. Invoice Posted
SELECT
inv.InvoiceDocument AS "InvoiceNumber",
'Invoice Posted' AS "ActivityName",
CAST(accdoc.PostingDate AS TIMESTAMP) AS "EventTime",
accdoc.CreatedByUser AS "UserName",
inv.CompanyCode AS "CompanyCode",
inv.VendorNumber AS "VendorNumber",
inv.AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
inv.PaymentDueDate AS "PaymentDueDate",
inv.DocumentType AS "DocumentType",
accdoc.PaymentBlockReason AS "PaymentBlockReason",
inv.PurchasingDocument AS "PurchasingDocument"
FROM InvoiceBase AS inv
JOIN I_OperationalAcctgDocItem AS accdoc ON inv.AccountingDocument = accdoc.AccountingDocument
WHERE inv.AccountingDocument IS NOT NULL AND inv.IsReversed = FALSE
UNION ALL
-- 10. Payment Proposal Created
SELECT
item.InvoiceReference AS "InvoiceNumber",
'Payment Proposal Created' AS "ActivityName",
CAST(prun.PaymentRunDate AS TIMESTAMP) AS "EventTime",
prun.CreatedByUser AS "UserName",
item.CompanyCode AS "CompanyCode",
item.Supplier AS "VendorNumber",
item.AmountInTransactionCurrency AS "AmountInCompanyCodeCurrency",
item.NetDueDate AS "PaymentDueDate",
item.AccountingDocumentType AS "DocumentType",
item.PaymentBlockReason AS "PaymentBlockReason",
item.PurchasingDocument AS "PurchasingDocument"
FROM I_PaymentProposalItem as item
JOIN I_PaymentRun as prun ON item.PaymentRunName = prun.PaymentRunName
JOIN InvoiceBase AS inv ON item.InvoiceReference = inv.InvoiceDocument
UNION ALL
-- 11 & 12. Payment Executed / Late Payment Executed
SELECT
InvoiceDocument AS "InvoiceNumber",
CASE
WHEN ClearingDate > PaymentDueDate THEN 'Late Payment Executed'
ELSE 'Payment Executed'
END AS "ActivityName",
CAST(ClearingDate AS TIMESTAMP) AS "EventTime",
CAST(NULL AS VARCHAR(12)) AS "UserName", -- User for clearing is not always straightforward
CompanyCode AS "CompanyCode",
VendorNumber AS "VendorNumber",
AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
PaymentDueDate AS "PaymentDueDate",
DocumentType AS "DocumentType",
'' AS "PaymentBlockReason",
PurchasingDocument AS "PurchasingDocument"
FROM InvoiceBase
WHERE ClearingDate IS NOT NULL AND IsReversed = FALSE
UNION ALL
-- 13. Invoice Reversed
SELECT
rev.OriginalInvoiceDocument AS "InvoiceNumber",
'Invoice Reversed' AS "ActivityName",
rev.CreationDateTime AS "EventTime",
rev.CreatedByUser AS "UserName",
rev.CompanyCode AS "CompanyCode",
rev.Supplier AS "VendorNumber",
rev.GrossInvoiceAmountInCoCoCrcy AS "AmountInCompanyCodeCurrency",
CAST(NULL AS DATE) AS "PaymentDueDate",
rev.DocumentType AS "DocumentType",
CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
rev.PurchasingDocument AS "PurchasingDocument"
FROM I_InvoiceDocument AS rev
WHERE rev.OriginalInvoiceDocument IN (SELECT InvoiceDocument FROM InvoiceBase) AND rev.IsReversal = 'X'Steps
- Prerequisites and Authorization: Ensure the user executing the extraction has the necessary authorizations in SAP S/4HANA to access the required Core Data Services (CDS) views. Key views include
I_InvoiceDocument,I_OperationalAcctgDocItem,I_ChangeDocument,I_ChangeDocumentItem, andI_PaymentProposalItem. The user will also need permissions to execute queries via the chosen interface, such as an OData service or a direct SQL connection. - Identify Your Connection Method: Determine how you will connect to the SAP S/4HANA system to execute the SQL query. Common methods include using SAP Data Services, SAP Data Intelligence, a third-party ETL tool with an SAP connector, or a direct SQL connection to the SAP HANA database if permitted by your organization's security policies.
- Define Extraction Parameters: Before executing the query, define the key parameters. Specify the date range for the extraction, for instance,
CreationDatebetween'YYYY-MM-DD'and'YYYY-MM-DD'. Also, identify the specificCompanyCodevalues you want to include to limit the scope of the data extraction. - Customize the SQL Query: Copy the provided SQL query into your chosen SQL client or data extraction tool. Carefully review the placeholders, such as
'{StartDate}','{EndDate}', and('{CompanyCode1}', '{CompanyCode2}'). Replace these placeholders with the actual values you defined in the previous step. You may also need to adjust field names for workflow status based on your specific SAP configuration. - Execute the Query: Run the complete SQL query against the SAP S/4HANA database or through the appropriate service layer. The query is designed to be comprehensive and may take a significant amount of time to run depending on the data volume and the selected date range. Monitor the execution for any potential errors or timeouts.
- Review Initial Results: Once the query completes, perform a quick review of the output. Check that the columns
InvoiceNumber,ActivityName, andEventTimeare populated. Verify that you see a variety of different activities in theActivityNamecolumn, not just 'Invoice Document Created'. - Address Data Transformation: The query is structured to produce a clean event log format. However, ensure that the
EventTimecolumn is in a consistent timestamp format, such asYYYY-MM-DDTHH:MM:SS. The provided query combines date and time fields into a single timestamp where necessary. - Export the Data: Export the final result set from your tool into a CSV (Comma-Separated Values) file. This format is universally compatible with process mining tools, including ProcessMind.
- Prepare for Upload: Before uploading, confirm the CSV file uses UTF-8 encoding to prevent character issues. Ensure the column headers in the file exactly match the required attributes:
InvoiceNumber,ActivityName,EventTime,UserName,CompanyCode, etc. - Upload to ProcessMind: Upload the prepared CSV file into your process mining project. Map the columns from your file to the corresponding case ID, activity name, and timestamp fields in the tool's data model configuration.
Configuration
- CDS Views Used: The primary data sources are standard SAP CDS views. The key views are
I_InvoiceDocumentfor header data,I_OperationalAcctgDocItemfor financial posting and clearing details, andI_ChangeDocumentwithI_ChangeDocumentItemfor tracking historical changes to invoice attributes like payment blocks and workflow status. - Date Range Filtering: It is critical to filter the data by a specific date range to manage performance. The provided query uses a placeholder for the
CreationDatein theI_InvoiceDocumentview. A recommended starting point is a 3 to 6 month period of data. - Company Code Filter: To ensure the extraction is relevant and manageable, always filter by one or more
CompanyCode. The query includes a placeholderWHERE inv.CompanyCode IN ('{CompanyCode1}', '{CompanyCode2}')for this purpose. - Document Type Filter: You can further refine the extraction by filtering on
InvoiceDocumentType. For example, you may want to include standard vendor invoices (RE) but exclude credit memos. This can be added to theWHEREclause of the initial CTE. - Prerequisites: The user running the query needs appropriate display authorizations for financial and purchasing documents within the specified company codes. Access to the underlying HANA database via a SQL client is not standard and requires special permissions.
- Performance Considerations: Extracting data from change document tables (
I_ChangeDocument,I_ChangeDocumentItem) can be performance-intensive. Applying strict filters on date, company code, and object class (INCOMINGINVOICE) is essential to prevent long execution times.
a Sample Query sql
WITH InvoiceBase AS (
SELECT
inv.InvoiceDocument,
inv.FiscalYear,
inv.CompanyCode,
inv.Supplier AS VendorNumber,
inv.DocumentType,
inv.GrossInvoiceAmountInCoCoCrcy AS AmountInCompanyCodeCurrency,
inv.NetDueDate AS PaymentDueDate,
inv.PurchasingDocument,
inv.CreationDateTime,
inv.CreatedByUser,
accdoc.AccountingDocument,
accdoc.ClearingDate,
accdoc.ClearingJournalEntry,
accdoc.PaymentBlockReason,
accdoc.IsReversed
FROM I_InvoiceDocument AS inv
LEFT JOIN I_OperationalAcctgDocItem AS accdoc
ON inv.AccountingDocument = accdoc.AccountingDocument
AND inv.FiscalYear = accdoc.FiscalYear
AND inv.CompanyCode = accdoc.CompanyCode
WHERE
inv.CreationDate BETWEEN '{StartDate}' AND '{EndDate}'
AND inv.CompanyCode IN ('{CompanyCode1}', '{CompanyCode2}')
)
-- 1. Invoice Document Created
SELECT
InvoiceDocument AS "InvoiceNumber",
'Invoice Document Created' AS "ActivityName",
CreationDateTime AS "EventTime",
CreatedByUser AS "UserName",
CompanyCode AS "CompanyCode",
VendorNumber AS "VendorNumber",
AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
PaymentDueDate AS "PaymentDueDate",
DocumentType AS "DocumentType",
CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
PurchasingDocument AS "PurchasingDocument"
FROM InvoiceBase
UNION ALL
-- 2. Invoice Parked
SELECT
i.InvoiceDocument AS "InvoiceNumber",
'Invoice Parked' AS "ActivityName",
i.CreationDateTime AS "EventTime",
i.CreatedByUser AS "UserName",
i.CompanyCode AS "CompanyCode",
i.Supplier AS "VendorNumber",
i.GrossInvoiceAmountInCoCoCrcy AS "AmountInCompanyCodeCurrency",
i.NetDueDate AS "PaymentDueDate",
i.DocumentType AS "DocumentType",
CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
i.PurchasingDocument AS "PurchasingDocument"
FROM I_InvoiceDocument AS i
WHERE
i.InvoiceDocumentIsParked = 'X'
AND i.CreationDate BETWEEN '{StartDate}' AND '{EndDate}'
AND i.CompanyCode IN ('{CompanyCode1}', '{CompanyCode2}')
UNION ALL
-- 3, 4, 5. Workflow activities (Sent for Approval, Approved, Rejected) from Change Docs
SELECT
cdpos.ObjectValue AS "InvoiceNumber",
CASE
WHEN cdpos.ValueNew = '[StatusSentForApproval]' THEN 'Invoice Sent For Approval'
WHEN cdpos.ValueNew = '[StatusApproved]' THEN 'Invoice Approved'
WHEN cdpos.ValueNew = '[StatusRejected]' THEN 'Invoice Rejected'
END AS "ActivityName",
CAST(cdhdr.ChangeDate AS TIMESTAMP) + CAST(cdhdr.ChangeTime AS TIME) AS "EventTime",
cdhdr.UserName AS "UserName",
inv.CompanyCode AS "CompanyCode",
inv.VendorNumber AS "VendorNumber",
inv.AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
inv.PaymentDueDate AS "PaymentDueDate",
inv.DocumentType AS "DocumentType",
CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
inv.PurchasingDocument AS "PurchasingDocument"
FROM I_ChangeDocument AS cdhdr
JOIN I_ChangeDocumentItem AS cdpos ON cdhdr.ChangeDocument = cdpos.ChangeDocument
JOIN InvoiceBase AS inv ON cdpos.ObjectValue = inv.InvoiceDocument
WHERE
cdhdr.ObjectClassName = 'INCOMINGINVOICE'
AND cdpos.FieldName = '[WorkflowStatusFieldName]'
AND cdpos.ValueNew IN ('[StatusSentForApproval]', '[StatusApproved]', '[StatusRejected]')
UNION ALL
-- 6. Invoice Data Updated
SELECT
cdpos.ObjectValue AS "InvoiceNumber",
'Invoice Data Updated' AS "ActivityName",
CAST(cdhdr.ChangeDate AS TIMESTAMP) + CAST(cdhdr.ChangeTime AS TIME) AS "EventTime",
cdhdr.UserName AS "UserName",
inv.CompanyCode AS "CompanyCode",
inv.VendorNumber AS "VendorNumber",
inv.AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
inv.PaymentDueDate AS "PaymentDueDate",
inv.DocumentType AS "DocumentType",
CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
inv.PurchasingDocument AS "PurchasingDocument"
FROM I_ChangeDocument AS cdhdr
JOIN I_ChangeDocumentItem AS cdpos ON cdhdr.ChangeDocument = cdpos.ChangeDocument
JOIN InvoiceBase AS inv ON cdpos.ObjectValue = inv.InvoiceDocument
WHERE
cdhdr.ObjectClassName = 'INCOMINGINVOICE'
AND cdpos.FieldName IN ('GrossInvoiceAmount', 'DocumentDate', 'PaymentTerms')
AND cdhdr.ChangeDate BETWEEN '{StartDate}' AND '{EndDate}'
UNION ALL
-- 7 & 8. Payment Block Set/Removed
SELECT
inv.InvoiceDocument AS "InvoiceNumber",
CASE
WHEN cdpos.ValueNew <> '' AND cdpos.ValueOld = '' THEN 'Payment Block Set'
WHEN cdpos.ValueNew = '' AND cdpos.ValueOld <> '' THEN 'Payment Block Removed'
END AS "ActivityName",
CAST(cdhdr.ChangeDate AS TIMESTAMP) + CAST(cdhdr.ChangeTime AS TIME) AS "EventTime",
cdhdr.UserName AS "UserName",
inv.CompanyCode AS "CompanyCode",
inv.VendorNumber AS "VendorNumber",
inv.AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
inv.PaymentDueDate AS "PaymentDueDate",
inv.DocumentType AS "DocumentType",
cdpos.ValueNew AS "PaymentBlockReason",
inv.PurchasingDocument AS "PurchasingDocument"
FROM I_ChangeDocument AS cdhdr
JOIN I_ChangeDocumentItem AS cdpos ON cdhdr.ChangeDocument = cdpos.ChangeDocument
JOIN InvoiceBase AS inv ON cdpos.ObjectValue = inv.AccountingDocument
WHERE
cdhdr.ObjectClassName = 'BELEG'
AND cdpos.TableName = 'BSEG'
AND cdpos.FieldName = 'ZLSPR'
AND ( (cdpos.ValueNew <> '' AND cdpos.ValueOld = '') OR (cdpos.ValueNew = '' AND cdpos.ValueOld <> '') )
UNION ALL
-- 9. Invoice Posted
SELECT
inv.InvoiceDocument AS "InvoiceNumber",
'Invoice Posted' AS "ActivityName",
CAST(accdoc.PostingDate AS TIMESTAMP) AS "EventTime",
accdoc.CreatedByUser AS "UserName",
inv.CompanyCode AS "CompanyCode",
inv.VendorNumber AS "VendorNumber",
inv.AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
inv.PaymentDueDate AS "PaymentDueDate",
inv.DocumentType AS "DocumentType",
accdoc.PaymentBlockReason AS "PaymentBlockReason",
inv.PurchasingDocument AS "PurchasingDocument"
FROM InvoiceBase AS inv
JOIN I_OperationalAcctgDocItem AS accdoc ON inv.AccountingDocument = accdoc.AccountingDocument
WHERE inv.AccountingDocument IS NOT NULL AND inv.IsReversed = FALSE
UNION ALL
-- 10. Payment Proposal Created
SELECT
item.InvoiceReference AS "InvoiceNumber",
'Payment Proposal Created' AS "ActivityName",
CAST(prun.PaymentRunDate AS TIMESTAMP) AS "EventTime",
prun.CreatedByUser AS "UserName",
item.CompanyCode AS "CompanyCode",
item.Supplier AS "VendorNumber",
item.AmountInTransactionCurrency AS "AmountInCompanyCodeCurrency",
item.NetDueDate AS "PaymentDueDate",
item.AccountingDocumentType AS "DocumentType",
item.PaymentBlockReason AS "PaymentBlockReason",
item.PurchasingDocument AS "PurchasingDocument"
FROM I_PaymentProposalItem as item
JOIN I_PaymentRun as prun ON item.PaymentRunName = prun.PaymentRunName
JOIN InvoiceBase AS inv ON item.InvoiceReference = inv.InvoiceDocument
UNION ALL
-- 11 & 12. Payment Executed / Late Payment Executed
SELECT
InvoiceDocument AS "InvoiceNumber",
CASE
WHEN ClearingDate > PaymentDueDate THEN 'Late Payment Executed'
ELSE 'Payment Executed'
END AS "ActivityName",
CAST(ClearingDate AS TIMESTAMP) AS "EventTime",
CAST(NULL AS VARCHAR(12)) AS "UserName", -- User for clearing is not always straightforward
CompanyCode AS "CompanyCode",
VendorNumber AS "VendorNumber",
AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
PaymentDueDate AS "PaymentDueDate",
DocumentType AS "DocumentType",
'' AS "PaymentBlockReason",
PurchasingDocument AS "PurchasingDocument"
FROM InvoiceBase
WHERE ClearingDate IS NOT NULL AND IsReversed = FALSE
UNION ALL
-- 13. Invoice Reversed
SELECT
rev.OriginalInvoiceDocument AS "InvoiceNumber",
'Invoice Reversed' AS "ActivityName",
rev.CreationDateTime AS "EventTime",
rev.CreatedByUser AS "UserName",
rev.CompanyCode AS "CompanyCode",
rev.Supplier AS "VendorNumber",
rev.GrossInvoiceAmountInCoCoCrcy AS "AmountInCompanyCodeCurrency",
CAST(NULL AS DATE) AS "PaymentDueDate",
rev.DocumentType AS "DocumentType",
CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
rev.PurchasingDocument AS "PurchasingDocument"
FROM I_InvoiceDocument AS rev
WHERE rev.OriginalInvoiceDocument IN (SELECT InvoiceDocument FROM InvoiceBase) AND rev.IsReversal = 'X'Steps
- Access the ABAP Editor: Log in to your SAP S/4HANA system. Open transaction
SE38(ABAP Editor). - Create the Program: Enter a name for your new program in the Program field, for example,
Z_PM_INVOICE_EXTRACT, and click the 'Create' button. Provide a title, set the Type to 'Executable Program', and save it in an appropriate package. - Define Program Structure and Selection Screen: In the editor, define the data structures for the final event log output. Then, create a selection screen to allow users to input parameters like a date range for the invoice entry date, company codes, and document types. This makes the program reusable and flexible.
- Implement Data Selection Logic: Write the core ABAP SQL statements to select data from the various SAP tables. The program will sequentially query for each of the 13 required activities.
- Extract Header and Item Data: For foundational events like 'Invoice Document Created' and 'Invoice Posted', select data from primary tables such as
RBKP(Logistics Invoice Header) andBKPF(Accounting Document Header). - Extract Change Document Data: For activities like 'Payment Block Set' and 'Payment Block Removed', query the change document tables
CDHDR(Change document header) andCDPOS(Change document items). You will need to identify changes to specific fields, for example,ZLSPRin tableBSEG. - Extract Payment Data: To capture payment-related activities, query tables like
REGUP(Processed items from payment program) for payment proposals andBSAK(Cleared Vendor Items) for executed payments. Differentiate 'Late Payment Executed' by comparing the clearing date (AUGDT) with the net due date (ZFBDT). - Extract Workflow Data: For approval activities, query SAP Business Workflow tables such as
SWW_WI2OBJto link workflow items to invoice objects. This part is highly dependent on your specific workflow configuration and may require significant adaptation. - Unify Data into Event Log Format: For each activity selected, format the data into a common internal table structure. Each row in this table represents a single event and must contain the case identifier (
InvoiceNumber),ActivityName, andEventTime, along with other recommended attributes. - Generate the Output File: Use ABAP statements
OPEN DATASET,TRANSFER, andCLOSE DATASETto write the contents of the final internal table to a flat file on the SAP application server. A comma-separated value (CSV) format is recommended. - Schedule and Execute: Execute the program in the foreground for testing (using
F8). For production runs, schedule it as a background job using transactionSM36to run during off-peak hours to avoid impacting system performance. - Retrieve and Upload: Use transaction
AL11to navigate to the application server directory where the file was saved. Download the file to your local system. Ensure the file is UTF-8 encoded and formatted correctly before uploading it to the process mining tool.
Configuration
- Date Range: Define a specific date range for the extraction based on the invoice entry date (
RBKP-CPUDT) or posting date (BKPF-BUDAT). For initial analysis, a period of 3 to 6 months is recommended to ensure a manageable data volume. - Company Code (BUKRS): It is critical to filter by one or more company codes. Extracting data for all company codes in a large organization can lead to extremely long runtimes and large files.
- Document Type (BLART): Filter on relevant document types to isolate vendor invoices. Common types include 'RE' (Invoice - Gross) and 'KR' (Vendor Invoice). This helps exclude irrelevant documents from the analysis.
- Vendor Account (LIFNR): The program can include an optional filter for specific vendor numbers, which is useful for targeted analysis or testing.
- Output File Configuration: The program should have parameters to define the output file path on the application server and the field delimiter, for example, a comma or semicolon.
- Prerequisites: The user or system account executing this program requires developer access to create and run ABAP programs (via
SE38) and extensive read authorizations for FI, MM, and Basis tables, includingBKPF,BSEG,RBKP,RSEG,CDHDR,CDPOS, and workflow tables.
a Sample Query abap
REPORT Z_PM_INVOICE_EXTRACT.
* --- Internal table structure for the final event log
TYPES: BEGIN OF ty_s_event_log,
invoicenumber TYPE char25,
activityname TYPE char50,
eventtime TYPE char19, "YYYY-MM-DD HH:MM:SS
username TYPE sy-uname,
companycode TYPE bukrs,
vendornumber TYPE lifnr,
amountincompanycodecurrency TYPE wrbtr,
paymentduedate TYPE char10, "YYYY-MM-DD
documenttype TYPE blart,
paymentblockreason TYPE char1,
purchasingdocument TYPE ebeln,
END OF ty_s_event_log.
DATA: lt_event_log TYPE STANDARD TABLE OF ty_s_event_log.
DATA: ls_event_log TYPE ty_s_event_log.
* --- Selection Screen for user inputs
PARAMETERS: p_path TYPE string DEFAULT '/usr/sap/tmp/invoice_events.csv'.
SELECT-OPTIONS: s_erdat FOR sy-datum OBLIGATORY, " Entry Date
s_bukrs FOR bkpf-bukrs OBLIGATORY, " Company Code
s_blart FOR bkpf-blart. " Document Type
START-OF-SELECTION.
* --- 1. Invoice Document Created (from Logistics Invoice Verification)
SELECT CONCAT( rbkp~belnr, rbkp~gjahr ) AS invoicenumber,
'Invoice Document Created' AS activityname,
CONCAT( rbkp~cpudt, rbkp~cputm ) AS eventtime,
rbkp~usnam AS username,
rbkp~bukrs AS companycode,
rbkp~lifnr AS vendornumber,
rbkp~rmwwr AS amountincompanycodecurrency,
'' AS paymentduedate,
rbkp~blart AS documenttype,
rbkp~zuonr AS paymentblockreason,
'' AS purchasingdocument
FROM rbkp
INTO TABLE @DATA(lt_created)
WHERE rbkp~cpudt IN @s_erdat
AND rbkp~bukrs IN @s_bukrs
AND rbkp~blart IN @s_blart.
LOOP AT lt_created INTO DATA(ls_created).
ls_event_log-invoicenumber = ls_created-invoicenumber.
ls_event_log-activityname = ls_created-activityname.
ls_event_log-eventtime = |{ ls_created-eventtime(8) } { ls_created-eventtime+8(2) }:{ ls_created-eventtime+10(2) }:{ ls_created-eventtime+12(2) }|.
ls_event_log-username = ls_created-username.
ls_event_log-companycode = ls_created-companycode.
ls_event_log-vendornumber = ls_created-vendornumber.
ls_event_log-amountincompanycodecurrency = ls_created-amountincompanycodecurrency.
ls_event_log-paymentduedate = ''.
ls_event_log-documenttype = ls_created-documenttype.
ls_event_log-paymentblockreason = ''.
ls_event_log-purchasingdocument = ls_created-purchasingdocument.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
* --- 2. Invoice Parked (assuming status 'A' or 'B' in RBKP)
SELECT CONCAT( belnr, gjahr ) AS invoicenumber,
'Invoice Parked' AS activityname,
CONCAT( cpudt, cputm ) AS eventtime,
usnam AS username,
bukrs AS companycode,
lifnr AS vendornumber,
rmwwr AS amountincompanycodecurrency,
'' AS paymentduedate,
blart AS documenttype,
'' AS paymentblockreason,
'' AS purchasingdocument
FROM rbkp
INTO TABLE @DATA(lt_parked)
WHERE rbstat IN ('A', 'B')
AND cpudt IN @s_erdat
AND bukrs IN @s_bukrs
AND blart IN @s_blart.
LOOP AT lt_parked INTO DATA(ls_parked).
ls_event_log-invoicenumber = ls_parked-invoicenumber.
ls_event_log-activityname = ls_parked-activityname.
ls_event_log-eventtime = |{ ls_parked-eventtime(8) } { ls_parked-eventtime+8(2) }:{ ls_parked-eventtime+10(2) }:{ ls_parked-eventtime+12(2) }|.
ls_event_log-username = ls_parked-username.
ls_event_log-companycode = ls_parked-companycode.
ls_event_log-vendornumber = ls_parked-vendornumber.
ls_event_log-amountincompanycodecurrency = ls_parked-amountincompanycodecurrency.
ls_event_log-paymentduedate = ''.
ls_event_log-documenttype = ls_parked-documenttype.
ls_event_log-paymentblockreason = ''.
ls_event_log-purchasingdocument = ''.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
* --- 3, 4, 5. Sent For Approval, Approved, Rejected (Placeholder logic, needs adaptation)
* --- This logic is a generic template for SAP Business Workflow.
* --- Your implementation will vary. You must identify the correct workflow tasks.
SELECT obj.instid, wi.wi_cd, wi.wi_ct, wi.wi_stat, wi.wi_aagent
FROM sww_wi2obj AS obj
JOIN swwlog AS wi ON obj~instid = wi~wi_id
INTO TABLE @DATA(lt_workflow)
WHERE obj~typeid = 'BUS2081' " Business Object for Incoming Invoice
AND obj~catid = 'BO'
AND wi~wi_cd IN s_erdat.
LOOP AT lt_workflow INTO DATA(ls_workflow).
* --- This is a placeholder, adapt task IDs and logic
CASE ls_workflow-wi_stat.
WHEN 'STARTED'.
ls_event_log-activityname = 'Invoice Sent For Approval'.
WHEN 'COMPLETED'.
ls_event_log-activityname = 'Invoice Approved'.
WHEN 'CANCELLED'.
ls_event_log-activityname = 'Invoice Rejected'.
WHEN OTHERS.
CONTINUE.
ENDCASE.
* --- Code to get invoice details based on ls_workflow-instid needed here
* --- ... appending to lt_event_log ...
ENDLOOP.
* --- 6, 7, 8. Payment Block Set/Removed, Data Updated (from Change Docs)
SELECT h~objectid, h~username, h~udate, h~utime, p~fname, p~value_new, p~value_old
FROM cdhdr AS h
JOIN cdpos AS p ON h~objectclas = p~objectclas AND h~objectid = p~objectid AND h~changenr = p~changenr
INTO TABLE @DATA(lt_changes)
WHERE h~objectclas = 'BELEGV'
AND h~udate IN s_erdat.
LOOP AT lt_changes INTO DATA(ls_change).
ls_event_log-invoicenumber = |{ ls_change-objectid+10(10) }{ ls_change-objectid(4) }|.
ls_event_log-username = ls_change-username.
ls_event_log-eventtime = |{ ls_change-udate } { ls_change-utime(2) }:{ ls_change-utime+2(2) }:{ ls_change-utime+4(2) }|.
IF ls_change-fname = 'ZLSPR'. " Payment Block
IF ls_change-value_old IS INITIAL AND ls_change-value_new IS NOT INITIAL.
ls_event_log-activityname = 'Payment Block Set'.
ls_event_log-paymentblockreason = ls_change-value_new.
ELSEIF ls_change-value_old IS NOT INITIAL AND ls_change-value_new IS INITIAL.
ls_event_log-activityname = 'Payment Block Removed'.
ls_event_log-paymentblockreason = ''.
ELSE.
CONTINUE.
ENDIF.
ELSE.
ls_event_log-activityname = 'Invoice Data Updated'.
ENDIF.
* --- Need to select other attributes based on invoice number
* --- ... appending to lt_event_log ...
ENDLOOP.
* --- 9. Invoice Posted
SELECT CONCAT( bkpf~belnr, bkpf~gjahr ) AS invoicenumber,
'Invoice Posted' AS activityname,
CONCAT( bkpf~cpudt, bkpf~cputm ) AS eventtime,
bkpf~usnam AS username,
bkpf~bukrs AS companycode,
bseg~lifnr AS vendornumber,
bseg~wrbtr AS amountincompanycodecurrency,
bseg~zfBDT AS paymentduedate,
bkpf~blart AS documenttype,
bseg~zlspr AS paymentblockreason,
bseg~ebeln AS purchasingdocument
FROM bkpf
JOIN bseg ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
INTO TABLE @DATA(lt_posted)
WHERE bkpf~cpudt IN @s_erdat
AND bkpf~bukrs IN @s_bukrs
AND bkpf~blart IN @s_blart
AND bseg~koart = 'K'. " Vendor line
LOOP AT lt_posted INTO DATA(ls_posted).
ls_event_log-invoicenumber = ls_posted-invoicenumber.
ls_event_log-activityname = ls_posted-activityname.
ls_event_log-eventtime = |{ ls_posted-eventtime(8) } { ls_posted-eventtime+8(2) }:{ ls_posted-eventtime+10(2) }:{ ls_posted-eventtime+12(2) }|.
ls_event_log-username = ls_posted-username.
ls_event_log-companycode = ls_posted-companycode.
ls_event_log-vendornumber = ls_posted-vendornumber.
ls_event_log-amountincompanycodecurrency = ls_posted-amountincompanycodecurrency.
ls_event_log-paymentduedate = ls_posted-paymentduedate.
ls_event_log-documenttype = ls_posted-documenttype.
ls_event_log-paymentblockreason = ls_posted-paymentblockreason.
ls_event_log-purchasingdocument = ls_posted-purchasingdocument.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
* --- 10. Payment Proposal Created
SELECT CONCAT( regup~belnr, regup~gjahr ) AS invoicenumber,
'Payment Proposal Created' AS activityname,
CONCAT( reguh~erfdt, reguh~erfzt ) AS eventtime,
reguh~erfbu AS username,
regup~bukrs AS companycode,
regup~lifnr AS vendornumber,
regup~wrbtr AS amountincompanycodecurrency,
'' AS paymentduedate,
regup~blart AS documenttype,
'' AS paymentblockreason,
'' AS purchasingdocument
FROM regup
JOIN reguh ON regup~laufd = reguh~laufd AND regup~laufi = reguh~laufi
INTO TABLE @DATA(lt_proposal)
WHERE reguh~erfdt IN @s_erdat
AND regup~bukrs IN @s_bukrs.
LOOP AT lt_proposal INTO DATA(ls_proposal).
ls_event_log-invoicenumber = ls_proposal-invoicenumber.
ls_event_log-activityname = ls_proposal-activityname.
ls_event_log-eventtime = |{ ls_proposal-eventtime(8) } { ls_proposal-eventtime+8(2) }:{ ls_proposal-eventtime+10(2) }:{ ls_proposal-eventtime+12(2) }|.
ls_event_log-username = ls_proposal-username.
ls_event_log-companycode = ls_proposal-companycode.
ls_event_log-vendornumber = ls_proposal-vendornumber.
ls_event_log-amountincompanycodecurrency = ls_proposal-amountincompanycodecurrency.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
* --- 11, 12. Payment Executed / Late Payment Executed
SELECT CONCAT( belnr, gjahr ) AS invoicenumber,
augdt,
zfBDT
FROM bsak
INTO TABLE @DATA(lt_cleared)
WHERE augdt IN @s_erdat
AND bukrs IN @s_bukrs.
LOOP AT lt_cleared INTO DATA(ls_cleared).
IF ls_cleared-augdt > ls_cleared-zfbdt.
ls_event_log-activityname = 'Late Payment Executed'.
ELSE.
ls_event_log-activityname = 'Payment Executed'.
ENDIF.
ls_event_log-invoicenumber = ls_cleared-invoicenumber.
ls_event_log-eventtime = |{ ls_cleared-augdt } 00:00:00|.
* --- Need to select other attributes based on invoice number
* --- ... appending to lt_event_log ...
ENDLOOP.
* --- 13. Invoice Reversed
SELECT CONCAT( stblg, stjah ) AS invoicenumber,
'Invoice Reversed' AS activityname,
CONCAT( cpudt, cputm ) AS eventtime,
usnam AS username,
bukrs AS companycode,
'' AS vendornumber,
'' AS amountincompanycodecurrency,
'' AS paymentduedate,
blart AS documenttype,
'' AS paymentblockreason,
'' AS purchasingdocument
FROM bkpf
INTO TABLE @DATA(lt_reversed)
WHERE stblg IS NOT NULL
AND cpudt IN @s_erdat
AND bukrs IN @s_bukrs.
LOOP AT lt_reversed INTO DATA(ls_reversed).
ls_event_log-invoicenumber = ls_reversed-invoicenumber.
ls_event_log-activityname = ls_reversed-activityname.
ls_event_log-eventtime = |{ ls_reversed-eventtime(8) } { ls_reversed-eventtime+8(2) }:{ ls_reversed-eventtime+10(2) }:{ ls_reversed-eventtime+12(2) }|.
ls_event_log-username = ls_reversed-username.
ls_event_log-companycode = ls_reversed-companycode.
APPEND ls_event_log TO lt_event_log.
ENDLOOP.
* --- Write internal table to CSV file
OPEN DATASET p_path FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
IF sy-subrc <> 0.
MESSAGE 'Error opening file.' TYPE 'E'.
ENDIF.
DATA: lv_line TYPE string.
FIELD-SYMBOLS: <fs_any> TYPE any.
* --- Header row
lv_line = 'InvoiceNumber,ActivityName,EventTime,UserName,CompanyCode,VendorNumber,AmountInCompanyCodeCurrency,PaymentDueDate,DocumentType,PaymentBlockReason,PurchasingDocument'.
TRANSFER lv_line TO p_path.
LOOP AT lt_event_log INTO ls_event_log.
CLEAR lv_line.
DO.
ASSIGN COMPONENT sy-index OF STRUCTURE ls_event_log TO <fs_any>.
IF sy-subrc <> 0.
EXIT.
ENDIF.
IF sy-index = 1.
lv_line = <fs_any>.
ELSE.
CONCATENATE lv_line <fs_any> INTO lv_line SEPARATED BY ','.
ENDIF.
ENDDO.
TRANSFER lv_line TO p_path.
ENDLOOP.
CLOSE DATASET p_path.
WRITE: / 'Extraction complete. File saved to:', p_path.