Your Accounts Payable Payment Processing Data Template
Your Accounts Payable Payment Processing Data Template
- Core attributes for vendor and payment analysis
- Essential process milestones for the payment cycle
- Specialized extraction logic for SAP S/4HANA systems
Accounts Payable Payment Processing Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Activity | The specific task or event status change recorded for the invoice. | ||
| Description This attribute represents the distinct process steps occurring during the lifecycle of the invoice. It captures events such as invoice creation, posting, blocking, approval, and payment clearing. The activity names are derived from transaction codes, change log entries, or workflow status updates found in the source system. For analysis, this field is critical for mapping the process flow variant. It allows the process mining engine to visualize the sequence of steps, identify rework loops, and determine where the process deviates from the standard happy path. It is the core component of the event log. Why it matters It defines the nodes in the process map, allowing for the visualization of workflow and bottlenecks. Where to get Derived from Transaction Codes (TCODE) or Change Document Header (CDHDR) and Item (CDPOS) Examples Invoice PostedPayment Block AppliedPayment Run ExecutedInvoice Cleared | |||
| Event Time EventTime | The exact timestamp when the activity occurred. | ||
| Description The Event Time records the specific date and time when an activity was committed to the SAP database. It provides the temporal dimension necessary to order events sequentially within a case. This timestamp is usually constructed by combining the CPU Date and CPU Time fields from the system logs or document headers. In analysis, this attribute is essential for calculating cycle times, duration, and throughput. It enables the measurement of time gaps between steps, such as the time taken between invoice receipt and final approval, which is crucial for identifying bottlenecks and assessing KPI performance like Average Invoice Approval Time. Why it matters It provides the chronological order of events and is the basis for all time-based performance calculations. Where to get SAP Table BKPF field CPUDT (Entry Date) and CPUTM (Entry Time), or CDHDR fields UDATE and UTIME Examples 2023-10-12T08:30:00.000Z2023-10-12T14:15:22.000Z2023-10-15T09:00:00.000Z | |||
| Invoice Number InvoiceNumber | The unique identifier for the vendor invoice being processed. | ||
| Description The Invoice Number is the primary key for tracking the lifecycle of a payable item within the SAP S/4HANA system. It specifically refers to the accounting document number generated upon posting the invoice to the general ledger. In standard SAP terminology, this corresponds to the Document Number (BELNR) found within a specific Company Code and Fiscal Year. In process analysis, this attribute serves as the Case ID. It links all disparate activities—from the initial receipt and parking of the invoice, through various approval blocks and modifications, to the final clearing payment. Grouping events by this identifier allows analysts to reconstruct the full end-to-end history of every payment obligation. Why it matters It serves as the definitive Case ID, enabling the end-to-end reconstruction of the payment process flow. Where to get SAP Table BKPF (Accounting Document Header) field BELNR or ACDOCA field BELNR Examples 1900000523510000289119000006015100003002 | |||
| Last Data Update LastDataUpdate | The timestamp indicating when the record was last extracted or refreshed. | ||
| Description Last Data Update marks the point in time when the data was successfully loaded into the process mining platform. It does not reflect the time of the business event, but rather the freshness of the dataset itself. This is crucial for maintaining trust in the analytics dashboards. In analysis, this attribute helps users understand the currency of the information they are viewing. It is particularly important when monitoring near real-time dashboards like the Payment Block Analysis, ensuring that decisions are made based on the most recent state of the SAP S/4HANA system. Why it matters It informs users of data freshness, critical for operational dashboards. Where to get Generated by the ETL / extraction process Examples 2023-10-27T23:59:59.000Z2023-11-01T06:00:00.000Z | |||
| Source System SourceSystem | The identifier of the SAP S/4HANA instance where the data originated. | ||
| Description This attribute identifies the specific ERP installation or client from which the process data was extracted. In landscapes with multiple SAP instances or legacy systems running in parallel, this field ensures data lineage is maintained and allows for cross-system comparisons. For analysis, this field acts as a high-level filter. It helps analysts segregate data when benchmarking performance across different regional system installations or when validating data consistency during system migration projects. It ensures that any process variations attributed to system configuration are properly contextualized. Why it matters It distinguishes data sources in multi-system environments, ensuring accurate segmentation. Where to get System ID (SY-SYSID) from the SAP installation context Examples SAP_PROD_01S4H_NA_100ERP_EU_200 | |||
| Clearing Date ClearingDate | The date on which the invoice was cleared by payment. | ||
| Description The Clearing Date records when the open item in the accounts payable ledger was balanced off, typically by a payment run or a manual payment posting. This effectively marks the end of the liability. In analysis, this attribute is used to calculate the final cycle time of the process. It is the timestamp used for the 'Payment Cleared' activity and is compared against the Net Due Date to determine On-Time Payment Performance. It feeds directly into the Payment Clearing Efficiency dashboard. Why it matters It marks the completion of the payment process and is used to determine payment timeliness. Where to get SAP Table BSEG or AUGDT field AUGDT Examples 2023-11-012023-11-15 | |||
| Company Code CompanyCode | The organizational unit for which the balance sheet and P&L are created. | ||
| Description The Company Code represents the independent accounting entity within the enterprise. It is the central organizational unit in external accounting and is used to structure the financial data. Every invoice is assigned to exactly one Company Code. In analysis, this attribute allows for the segmentation of KPIs by legal entity or region. It is used in dashboards to compare the efficiency of accounts payable teams across different subsidiaries. For example, it helps identify if a specific branch has a higher rate of manual payment blocks compared to the corporate standard. Why it matters It segments the process by legal entity, facilitating internal benchmarking. Where to get SAP Table BKPF field BUKRS Examples US01DE1010002000 | |||
| Document Type DocumentType | Classifies the accounting document (e.g., Vendor Invoice, Payment, Credit Memo). | ||
| Description The Document Type is a two-character code in SAP that classifies the accounting transaction. Common types include 'KR' for Vendor Invoices, 'KZ' for Vendor Payments, and 'RE' for Invoice Receipt Gross. It dictates the number range and field status of the document. In analysis, this attribute is used to filter the process scope. For instance, an analyst might want to exclude Credit Memos to focus solely on the efficiency of outgoing payments. It also helps in identifying the mix of transaction types being processed and supports the Process Variant Complexity dashboard. Why it matters It categorizes the case (invoice vs. credit memo), allowing for filtered analysis. Where to get SAP Table BKPF field BLART Examples KRREKZKG | |||
| Invoice Amount InvoiceAmount | The total gross amount of the invoice in the document currency. | ||
| Description This attribute reflects the financial value of the invoice as recorded in the source document. It represents the liability that must be settled with the vendor. In SAP S/4HANA, this is typically stored in the Amount in Document Currency field. In analysis, the Invoice Amount is used to prioritize work. Dashboards like Manual Touch Point Distribution use this field to highlight if high-effort manual activities are being wasted on low-value invoices. It allows the organization to focus optimization efforts on high-value transactions where process failures carry a larger financial risk. Why it matters It provides the financial weight of the case, essential for prioritizing high-value process inefficiencies. Where to get SAP Table BKPF or BSEG field WRBTR Examples 1500.00250.5010000.00 | |||
| Is Late Payment IsLatePayment | A boolean flag indicating if the payment was made after the net due date. | ||
| Description This calculated attribute evaluates to true if the Clearing Date is strictly greater than the Net Due Date. It serves as a binary classifier for process performance. In analysis, this flag is used to count the number of non-compliant cases for the Late Payment Penalty Frequency KPI. It simplifies dashboard creation by allowing a simple count of 'True' values rather than requiring complex date math in the visualization layer. Why it matters It simplifies the calculation of on-time performance KPIs. Where to get Calculated: Clearing Date > Net Due Date Examples truefalse | |||
| Is Touchless IsTouchless | A boolean flag indicating if the invoice was processed without manual intervention. | ||
| Description This attribute is calculated by analyzing the event stream for a case. If the case contains only automated activities (e.g., 'system' user, specific background TCODES) and no manual changes or blocks, it is flagged as touchless. In analysis, this is the core metric for the Touchless Invoice Rate KPI. It allows the organization to track the success of automation initiatives and identify which case types (e.g., by Vendor or Region) are successfully flowing through the system without human touches. Why it matters It is the primary measure of process automation and efficiency. Where to get Calculated based on the sequence of activities and user types Examples truefalse | |||
| Net Due Date NetDueDate | The calculated date by which the invoice must be paid to avoid penalties. | ||
| Description The Net Due Date is the final deadline for payment. It is calculated by adding the maximum payment term days to the Baseline Date. While sometimes stored explicitly, it is often a calculated field in analysis views. In analysis, this is the primary benchmark for the Late Payment and Penalty Tracker. Comparing the actual Clearing Date to the Net Due Date generates the 'Days Paid Late' metric, which helps quantify the efficiency of the AP team and the risk of vendor friction. Why it matters It is the target deadline for the process; missing it impacts credit rating and incurs costs. Where to get Calculated: Baseline Date + Max Payment Term Days (ZBD1T/ZBD2T/ZBD3T) Examples 2023-11-302023-12-01 | |||
| Payment Block Reason PaymentBlockReason | The code indicating why an invoice is blocked for payment. | ||
| Description This attribute contains the specific reason code applied to an invoice that prevents the automatic payment run from picking it up. Examples include 'A' for Blocked for Payment, 'R' for Invoice Verification, or manual blocks set by users. In analysis, this field is the primary driver for the Manual Payment Block Analysis dashboard. By aggregating the frequency of different block reasons, the organization can diagnose systemic issues, such as frequent price variances or missing goods receipts, that are stalling the payment process. Why it matters It identifies the specific cause of process stoppages, enabling targeted root cause analysis. Where to get SAP Table BSEG field ZLSPR Examples ABR* | |||
| Payment Terms PaymentTerms | The key representing the agreed conditions for payment and discounts. | ||
| Description Payment Terms define when an invoice is due and whether any cash discounts apply for early payment. This code (e.g., 'Z001') maps to rules such as 'Net 30' or '2% 10, Net 30'. It is copied from the Vendor Master to the Invoice but can be manually changed. In analysis, this attribute is central to the Early Payment Discount Optimizer and Vendor Payment Term Compliance dashboards. It allows the system to calculate the baseline due date and identify if the payment was made within the optimal window to capture savings. Why it matters It dictates the expected timeline and financial incentives, key for discount analysis. Where to get SAP Table BSEG field ZTERM Examples Z001NT300001 | |||
| User Name UserName | The ID of the user who performed the specific activity. | ||
| Description The User Name captures the login ID of the person or system agent responsible for executing a process step. This could be a manual user entering data or a background job ID (e.g., 'BATCH_USER') performing automated tasks. In analysis, this attribute allows for the calculation of the Activity Automation Rate. By distinguishing between human users and system accounts, analysts can measure the level of automation in the process. It is also used in the Manual Touch Point Distribution dashboard to assess workload across teams. Why it matters It distinguishes between manual and automated work, enabling automation rate calculations. Where to get SAP Table BKPF field USNAM or CDHDR field USERNAME Examples BSMITHWF-BATCHRJONES | |||
| Vendor Number VendorNumber | The unique identifier for the supplier associated with the invoice. | ||
| Description The Vendor Number corresponds to the specific creditor account in the SAP sub-ledger. It links the invoice to the master data containing payment terms, bank details, and contact information. In S/4HANA, this is often linked to the Business Partner concept but retains the legacy field name LIFNR in many tables. In analysis, this attribute is fundamental for the Vendor Payment Term Compliance dashboard. It allows analysts to aggregate process performance by supplier, identifying specific vendors that consistently cause blocks, price variances, or delays. It supports strategic sourcing decisions and supplier relationship management. Why it matters It enables performance aggregation by supplier, critical for identifying root causes of delays. Where to get SAP Table BKPF field LIFNR or ACDOCA field LIFNR Examples 100050VEND-US-99200400 | |||
| Baseline Date BaselineDate | The date from which payment terms apply and due dates are calculated. | ||
| Description The Baseline Date is the starting point for calculating the net due date and cash discount periods. It is usually the invoice date or the posting date, depending on configuration and vendor master data. In analysis, this date is a technical prerequisite for calculating the 'Is Late' status. Errors in the baseline date often lead to premature payments (cash flow impact) or late payments (penalty impact). Verifying the accuracy of this date is part of the Vendor Payment Term Compliance analysis. Why it matters It is the anchor point for all due date calculations. Where to get SAP Table BSEG field ZFBDT Examples 2023-10-012023-10-15 | |||
| Cash Discount Days 1 CashDiscountDays1 | The number of days from the baseline date during which the first cash discount is available. | ||
| Description This attribute defines the time window for the most favorable payment terms (e.g., '10' in '2% 10, Net 30'). It comes from the terms stored in the invoice line item. In analysis, this helps determine the 'Target Date' for the Early Payment Discount Optimizer. If the invoice is cleared within this window, the discount is realized. This field helps measure the opportunity cost of slow processing cycles. Why it matters It defines the window of opportunity for financial savings. Where to get SAP Table BSEG field ZBD1T Examples 10140 | |||
| Cash Discount Percentage 1 CashDiscountPercentage1 | The percentage of discount available if paid within the first discount period. | ||
| Description This attribute represents the financial incentive rate offered by the vendor for early payment (e.g., '2' in '2% 10'). In analysis, this is used to calculate the 'Potential Cash Discount' value. By multiplying this percentage by the Invoice Amount, the dashboards can visualize the total money left on the table due to process inefficiencies, supporting the business case for automation. Why it matters It quantifies the potential savings rate, essential for ROI calculations. Where to get SAP Table BSEG field ZBD1P Examples 2.03.00.0 | |||
| Currency Currency | The currency code associated with the invoice amount. | ||
| Description The Currency attribute specifies the denomination of the Invoice Amount, such as USD, EUR, or GBP. It allows for the correct interpretation of financial values and is essential when aggregating data across international company codes. In analysis, this field ensures that financial KPIs are calculated correctly. It is often used to normalize values into a reporting currency for global dashboards. Without this attribute, aggregate metrics like Total Spend or Average Invoice Value would be meaningless in a multi-currency environment. Why it matters It provides context for financial amounts, essential for accurate global reporting. Where to get SAP Table BKPF field WAERS Examples USDEURGBPJPY | |||
| Discount Lost Amount DiscountLostAmount | The monetary value of cash discounts that were available but not taken. | ||
| Description This calculated attribute represents the 'money left on the table.' It is derived by checking if the payment was made after the discount deadline and, if so, calculating the value of the missed discount percentage applied to the invoice amount. In analysis, this is a critical financial metric for the Early Payment Discount Optimizer. It quantifies the cost of inefficiency in hard currency, providing a compelling business case for process improvements. Why it matters It quantifies the direct financial loss due to process delays. Where to get Calculated: If Clearing Date > Discount Date, then Invoice Amount * Discount % Examples 30.000.00150.00 | |||
| Fiscal Year FiscalYear | The financial year to which the invoice belongs. | ||
| Description The Fiscal Year is a time period used for financial reporting. Along with Company Code and Document Number, it forms the composite primary key for a financial document in SAP. In analysis, this is a technical necessity for uniquely identifying cases, but it also supports year-over-year reporting. It ensures that the 'Invoice Number' Case ID remains unique over decades of data history. Why it matters Technical requirement for unique case identification in SAP FI. Where to get SAP Table BKPF field GJAHR Examples 20232024 | |||
| Purchasing Document PurchasingDocument | The Purchase Order number associated with the invoice. | ||
| Description This attribute links the invoice to the upstream procurement process. It contains the Purchase Order (PO) number against which the invoice is being matched. Not all invoices (e.g., miscellaneous expenses) will have a PO reference. In analysis, this field is vital for the Three Way Match Rate Analysis. It allows analysts to separate PO-backed invoices from non-PO invoices, which typically have very different approval workflows. It also facilitates end-to-end process mining by linking Accounts Payable data with Procurement data. Why it matters It links AP to Procurement, enabling 3-way match analysis and process extension. Where to get SAP Table BSEG field EBELN Examples 45000012344500009876 | |||
Accounts Payable Payment Processing Activities
| Activity | Description | ||
|---|---|---|---|
| Invoice Posted | Represents the official recording of the liability in the General Ledger. This activity is derived from the creation timestamp in the BKPF table or the entry date in the ACDOCA table. | ||
| Why it matters This is the primary start point for the financial timeline, establishing the baseline for due dates and aging analysis. Where to get BKPF table, using CPUDT (Entry Date) and CPUTM (Entry Time). Capture Logged when BKPF record created Event type explicit | |||
| Payment Block Applied | Signifies that a payment block has been set on the invoice line item, preventing it from being picked up by the payment run. This is captured by monitoring changes to the ZLSPR field in the BSEG table via change documents. | ||
| Why it matters Blocks are the primary cause of late payments and process friction, directly impacting the Manual Payment Block Analysis dashboard. Where to get CDPOS and CDHDR tables (Change Documents), looking for updates to field BSEG-ZLSPR. Capture Logged when CDPOS records change in ZLSPR Event type explicit | |||
| Payment Block Removed | Indicates that a previously applied payment block has been lifted, effectively releasing the invoice for payment. This is identified when the ZLSPR field in BSEG changes from a value to null or empty. | ||
| Why it matters Often serves as a proxy for 'Invoice Approved' in systems without explicit workflow logs, marking the end of the bottleneck period. Where to get CDPOS and CDHDR tables, looking for BSEG-ZLSPR changing to empty. Capture Logged when CDPOS records ZLSPR removal Event type explicit | |||
| Payment Cleared | Marks the final reconciliation where the open item in the vendor account is cleared against the payment. Captured from the AUGDT (Clearing Date) field in the BSEG table. | ||
| Why it matters The final state of the process, indicating the lifecycle is complete and books are balanced. High manual clearing rates indicate reconciliation inefficiencies. Where to get BSEG table, field AUGDT (Clearing Date). Capture Logged when AUGDT field populated Event type explicit | |||
| Payment Document Created | The generation of the accounting document that credits the bank and debits the vendor. This is found in BKPF with a payment document type (e.g., ZP, KZ). | ||
| Why it matters The financial realization of the payment, used to calculate Days Payable Outstanding (DPO). Where to get BKPF table, filtered by Document Type (BLART) specific to payments. Capture Logged when BKPF payment doc created Event type explicit | |||
| Payment Run Executed | Represents the execution of the payment run where money transfer instructions are generated. This is tracked via the status update in the REGUH or REGUP tables. | ||
| Why it matters The operational commitment to pay, critical for analyzing the efficiency of the payment batching process. Where to get REGUH table, typically correlated with the run date and identification. Capture Logged when Payment Run status updates Event type explicit | |||
| Cash Discount Lost | A calculated event marking the date when the eligibility for a cash discount expired. Derived by comparing the discount due date against the current date or payment date. | ||
| Why it matters Essential for the Early Payment Discount Optimizer to visualize lost financial opportunities. Where to get Calculated: BSEG-ZFBDT + BSEG-ZBD1T (Discount Days 1). Capture Derive from comparing date to Discount Due Date Event type calculated | |||
| Invoice Due | A calculated timestamp representing the moment the invoice reached its net due date. This is derived by adding the payment terms days to the baseline date found in the BSEG table. | ||
| Why it matters Acts as a reference point for On-Time Payment Performance and Late Payment Penalty Tracker. Where to get Calculated: BSEG-ZFBDT (Baseline Date) + BSEG-ZBD1T/ZBD2T/ZBD3T (Days). Capture Derive from comparing current date to Net Due Date Event type calculated | |||
| Invoice Parked | Indicates that an invoice has been entered into SAP but not yet posted to the General Ledger, often used for preliminary data entry. This is captured explicitly from the VBKPF table or by identifying documents in BKPF with a parked status code before they transition to posted. | ||
| Why it matters Parking indicates the start of the data entry phase and helps measure the lag time between receipt and financial obligation. Where to get VBKPF table for header data of parked documents or BKPF with specific document status (BSTAT = V). Capture Logged when VBKPF entry created Event type explicit | |||
| Invoice Reversed | Indicates that the invoice document was reversed or cancelled. Captured by checking the STBLG (Reversal Document) field in the BKPF table. | ||
| Why it matters Represents rework and process failure, identifying waste and potential duplicate effort. Where to get BKPF table, field STBLG is not empty. Capture Logged when STBLG field populated Event type explicit | |||
| Payment Proposal Created | Signifies that the invoice has been included in a payment proposal run (F110), the first step of the automated payment program. Captured from the REGUH table which stores settlement data. | ||
| Why it matters Indicates the invoice has been selected for payment and passed validition checks within the payment program. Where to get REGUH table creation timestamp (LAUFD and LAUFI keys). Capture Logged when REGUH record created Event type explicit | |||
| Payment Terms Changed | Records an update to the payment terms on an open invoice, which alters the due date or discount eligibility. This is tracked via change logs on the ZTERM field in the BSEG table. | ||
| Why it matters Frequent changes suggest master data errors or manual overrides that impact cash flow forecasting and Vendor Payment Term Compliance. Where to get CDPOS and CDHDR tables, looking for updates to field BSEG-ZTERM. Capture Logged when CDPOS records change in ZTERM Event type explicit | |||
| Price Variance Detected | Inferred activity indicating a mismatch between the invoice price and the purchase order price. This is derived by observing a specific Payment Block Key (typically 'R' for Invoice Verification) applied automatically upon posting. | ||
| Why it matters Identifies root causes for manual rework and supports the Three Way Match Rate Analysis. Where to get Inferred from BSEG-ZLSPR value 'R' (or system specific configuration for price blocks) at time of posting. Capture Compare ZLSPR value to 'R' Event type inferred | |||
| Quantity Variance Detected | Inferred activity indicating a discrepancy between the invoiced quantity and the goods receipt quantity. This is derived by observing a specific Payment Block Key (typically 'M' for Quantity Variance) applied to the line item. | ||
| Why it matters Crucial for analyzing matching efficiency and supply chain data quality. Where to get Inferred from BSEG-ZLSPR value 'M' (or system specific configuration for quantity blocks). Capture Compare ZLSPR value to 'M' Event type inferred | |||
Extraction Guides
Steps
Identify Required CDS Views: Confirm the availability of standard SAP S/4HANA CDS views. The primary views required are I_JournalEntry (Header), I_OperationalAcctgDocItem (Items/BSEG equivalent), I_SupplierInvoice (Logistics), I_PaymentProposalItem (F110), and I_ChangeDocument (for logs).
Configure User Permissions: Ensure the database user or technical service user has SELECT privileges on the DDL SQL views associated with the CDS entities. This is typically managed via SAP HANA Studio or the ABAP Eclipse Development Tools (ADT).
Prepare the SQL Environment: Open your SQL interface (e.g., SAP HANA Studio, DBeaver connected to HANA, or a Process Mining connector that accepts SQL). This method assumes direct SQL access to the HANA layer where CDS views are exposed as views.
Define Scope: Determine the Company Code (CompanyCode) and Fiscal Year range to limit the data volume. This is crucial for performance when querying the Operational Accounting Document Item view.
Implement Activity Logic: Copy the SQL query provided below. This query uses UNION ALL to combine 14 distinct logic blocks into a single event log structure. Each block targets a specific activity (e.g., Invoice Posted, Payment Cleared).
Handle Change Documents: The query includes sections for Payment Terms Changes and Block manipulation. These rely on the I_ChangeDocument view. If this view is not active in your specific S/4 release, you may need to wrap the underlying tables (CDHDR/CDPOS) in a custom CDS view.
Address Calculated Events: Review the logic for Invoice Due and Cash Discount Lost. These are calculated events generated by adding days to the baseline date found in the operational item view.
Execute Extraction: Run the query. For large datasets, it is highly recommended to partition the extraction by Fiscal Year or Company Code to avoid memory overflows.
Verify Date Formats: Ensure the EventTime column is formatted as YYYY-MM-DD HH:MM:SS. SAP HANA SQL returns timestamps that may need casting depending on your target application.
Export Data: Save the result set as a CSV or Parquet file. Ensure headers match the columns defined in the final SELECT statement.
Transform for Upload: If your process mining tool requires a specific CSV format (e.g., specific date masking), apply these transformations in a post-processing script or within the SQL using TO_VARCHAR functions.
Final Validation: Load a sample into ProcessMind and verify that the Case ID (Invoice Number) correctly groups all activities from posting to clearing.
Configuration
- Company Code Filter: Restrict the query to specific organizational units (CompanyCode = '1000') to maintain context and performance.
- Date Range: Apply a filter on PostingDate or CreationDate (e.g., last 12 months) to manage data volume.
- Account Type: Filter I_OperationalAcctgDocItem for FinancialAccountType = 'K' (Vendor) to exclude GL and Customer lines.
- CDS View Activation: Ensure views I_JournalEntry, I_OperationalAcctgDocItem, and I_PaymentProposalItem are active and released for SQL access.
- Change Log Performance: Querying I_ChangeDocument can be resource-intensive. Limit these sub-queries by ObjectClass 'BELEG' and specific field names like ZLSPR and ZTERM.
a Sample Query sql
/* SAP S/4HANA CDS View Extraction for Accounts Payable */
/* Combined Event Log Query */
/* 1. Invoice Parked */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Invoice Parked' AS Activity,
JE.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
JE.CreatedByUser AS UserName,
NULL AS ClearingDate,
ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) AS NetDueDate,
CASE WHEN JE.CreationDateTime > ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) THEN 'True' ELSE 'False' END AS IsLatePayment,
'False' AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K' -- Vendor
AND JE.AccountingDocumentCategory = 'V' -- Parked Document
UNION ALL
/* 2. Invoice Posted */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Invoice Posted' AS Activity,
JE.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
JEItem.PaymentBlockingReason AS PaymentBlockReason,
JE.CreatedByUser AS UserName,
NULL AS ClearingDate,
ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) AS NetDueDate,
'False' AS IsLatePayment,
CASE WHEN JE.CreatedByUser = 'BATCH_USER' THEN 'True' ELSE 'False' END AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JE.AccountingDocumentCategory <> 'V' -- Exclude Parked
UNION ALL
/* 3. Price Variance Detected (Inferred at Posting) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Price Variance Detected' AS Activity,
JE.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
JEItem.PaymentBlockingReason AS PaymentBlockReason,
'System' AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JEItem.PaymentBlockingReason = 'R' -- Standard SAP Price Variance Block Key
UNION ALL
/* 4. Quantity Variance Detected (Inferred at Posting) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Quantity Variance Detected' AS Activity,
JE.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
JEItem.PaymentBlockingReason AS PaymentBlockReason,
'System' AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JEItem.PaymentBlockingReason = 'M' -- Standard SAP Quantity Variance Block Key
UNION ALL
/* 5. Payment Block Applied (via Change Document) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Block Applied' AS Activity,
CD.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
CD.NewValue AS PaymentBlockReason,
CD.CreatedByUser AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_ChangeDocument AS CD
JOIN I_JournalEntry AS JE ON CD.ObjectValue = CONCAT(JE.CompanyCode, JE.AccountingDocument)
JOIN I_OperationalAcctgDocItem AS JEItem ON JE.AccountingDocument = JEItem.AccountingDocument AND JE.CompanyCode = JEItem.CompanyCode
WHERE CD.ObjectClass = 'BELEG'
AND CD.TableName = 'BSEG'
AND CD.FieldName = 'ZLSPR'
AND CD.OldValue IS NULL AND CD.NewValue IS NOT NULL
UNION ALL
/* 6. Payment Block Removed (via Change Document) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Block Removed' AS Activity,
CD.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
CD.CreatedByUser AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_ChangeDocument AS CD
JOIN I_JournalEntry AS JE ON CD.ObjectValue = CONCAT(JE.CompanyCode, JE.AccountingDocument)
JOIN I_OperationalAcctgDocItem AS JEItem ON JE.AccountingDocument = JEItem.AccountingDocument AND JE.CompanyCode = JEItem.CompanyCode
WHERE CD.ObjectClass = 'BELEG'
AND CD.TableName = 'BSEG'
AND CD.FieldName = 'ZLSPR'
AND CD.OldValue IS NOT NULL AND (CD.NewValue IS NULL OR CD.NewValue = '')
UNION ALL
/* 7. Payment Terms Changed (via Change Document) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Terms Changed' AS Activity,
CD.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
CD.NewValue AS PaymentTerms,
NULL AS PaymentBlockReason,
CD.CreatedByUser AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_ChangeDocument AS CD
JOIN I_JournalEntry AS JE ON CD.ObjectValue = CONCAT(JE.CompanyCode, JE.AccountingDocument)
JOIN I_OperationalAcctgDocItem AS JEItem ON JE.AccountingDocument = JEItem.AccountingDocument AND JE.CompanyCode = JEItem.CompanyCode
WHERE CD.ObjectClass = 'BELEG'
AND CD.TableName = 'BSEG'
AND CD.FieldName = 'ZTERM'
UNION ALL
/* 8. Invoice Due (Calculated) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Invoice Due' AS Activity,
TO_TIMESTAMP(ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays))) AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
'System' AS UserName,
NULL AS ClearingDate,
ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) < CURRENT_DATE
UNION ALL
/* 9. Cash Discount Lost (Calculated) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Cash Discount Lost' AS Activity,
TO_TIMESTAMP(ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.CashDiscount1Days))) AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
'System' AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JEItem.CashDiscount1Days > 0
AND (JEItem.ClearingDate IS NULL OR JEItem.ClearingDate > ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.CashDiscount1Days)))
UNION ALL
/* 10. Payment Proposal Created */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Proposal Created' AS Activity,
PPI.ProposalRunDate AS EventTime, -- Often just a date, cast to timestamp if needed
JE.CompanyCode,
PPI.Supplier AS VendorNumber,
PPI.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
PPI.CreatedByUser AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_PaymentProposalItem AS PPI
JOIN I_JournalEntry AS JE
ON PPI.CompanyCode = JE.CompanyCode
AND PPI.AccountingDocument = JE.AccountingDocument
AND PPI.FiscalYear = JE.FiscalYear
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JEItem.FinancialAccountType = 'K'
UNION ALL
/* 11. Payment Run Executed */
/* Derived from existence in payment tables with a run ID */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Run Executed' AS Activity,
PPI.PaymentRunDate AS EventTime,
JE.CompanyCode,
PPI.Supplier AS VendorNumber,
PPI.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
PPI.CreatedByUser AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_PaymentProposalItem AS PPI
JOIN I_JournalEntry AS JE
ON PPI.CompanyCode = JE.CompanyCode
AND PPI.AccountingDocument = JE.AccountingDocument
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
WHERE PPI.PaymentRunID IS NOT NULL
UNION ALL
/* 12. Payment Document Created */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Document Created' AS Activity,
PayJE.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
PayJE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
PayJE.CreatedByUser AS UserName,
PayJE.PostingDate AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
JOIN I_JournalEntry AS PayJE
ON JEItem.ClearingJournalEntry = PayJE.AccountingDocument
AND JEItem.ClearingJournalEntryFiscalYear = PayJE.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JEItem.ClearingJournalEntry IS NOT NULL
UNION ALL
/* 13. Payment Cleared */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Cleared' AS Activity,
TO_TIMESTAMP(JEItem.ClearingDate) AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
'System' AS UserName,
JEItem.ClearingDate,
ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) AS NetDueDate,
CASE WHEN JEItem.ClearingDate > ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) THEN 'True' ELSE 'False' END AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JEItem.ClearingDate IS NOT NULL
UNION ALL
/* 14. Invoice Reversed */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Invoice Reversed' AS Activity,
RevJE.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
RevJE.CreatedByUser AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
JOIN I_JournalEntry AS RevJE
ON JE.ReverseDocument = RevJE.AccountingDocument
AND JE.ReverseDocumentFiscalYear = RevJE.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JE.ReverseDocument IS NOT NULLSteps
Identify Required CDS Views: Confirm the availability of standard SAP S/4HANA CDS views. The primary views required are I_JournalEntry (Header), I_OperationalAcctgDocItem (Items/BSEG equivalent), I_SupplierInvoice (Logistics), I_PaymentProposalItem (F110), and I_ChangeDocument (for logs).
Configure User Permissions: Ensure the database user or technical service user has SELECT privileges on the DDL SQL views associated with the CDS entities. This is typically managed via SAP HANA Studio or the ABAP Eclipse Development Tools (ADT).
Prepare the SQL Environment: Open your SQL interface (e.g., SAP HANA Studio, DBeaver connected to HANA, or a Process Mining connector that accepts SQL). This method assumes direct SQL access to the HANA layer where CDS views are exposed as views.
Define Scope: Determine the Company Code (CompanyCode) and Fiscal Year range to limit the data volume. This is crucial for performance when querying the Operational Accounting Document Item view.
Implement Activity Logic: Copy the SQL query provided below. This query uses UNION ALL to combine 14 distinct logic blocks into a single event log structure. Each block targets a specific activity (e.g., Invoice Posted, Payment Cleared).
Handle Change Documents: The query includes sections for Payment Terms Changes and Block manipulation. These rely on the I_ChangeDocument view. If this view is not active in your specific S/4 release, you may need to wrap the underlying tables (CDHDR/CDPOS) in a custom CDS view.
Address Calculated Events: Review the logic for Invoice Due and Cash Discount Lost. These are calculated events generated by adding days to the baseline date found in the operational item view.
Execute Extraction: Run the query. For large datasets, it is highly recommended to partition the extraction by Fiscal Year or Company Code to avoid memory overflows.
Verify Date Formats: Ensure the EventTime column is formatted as YYYY-MM-DD HH:MM:SS. SAP HANA SQL returns timestamps that may need casting depending on your target application.
Export Data: Save the result set as a CSV or Parquet file. Ensure headers match the columns defined in the final SELECT statement.
Transform for Upload: If your process mining tool requires a specific CSV format (e.g., specific date masking), apply these transformations in a post-processing script or within the SQL using TO_VARCHAR functions.
Final Validation: Load a sample into ProcessMind and verify that the Case ID (Invoice Number) correctly groups all activities from posting to clearing.
Configuration
- Company Code Filter: Restrict the query to specific organizational units (CompanyCode = '1000') to maintain context and performance.
- Date Range: Apply a filter on PostingDate or CreationDate (e.g., last 12 months) to manage data volume.
- Account Type: Filter I_OperationalAcctgDocItem for FinancialAccountType = 'K' (Vendor) to exclude GL and Customer lines.
- CDS View Activation: Ensure views I_JournalEntry, I_OperationalAcctgDocItem, and I_PaymentProposalItem are active and released for SQL access.
- Change Log Performance: Querying I_ChangeDocument can be resource-intensive. Limit these sub-queries by ObjectClass 'BELEG' and specific field names like ZLSPR and ZTERM.
a Sample Query sql
/* SAP S/4HANA CDS View Extraction for Accounts Payable */
/* Combined Event Log Query */
/* 1. Invoice Parked */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Invoice Parked' AS Activity,
JE.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
JE.CreatedByUser AS UserName,
NULL AS ClearingDate,
ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) AS NetDueDate,
CASE WHEN JE.CreationDateTime > ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) THEN 'True' ELSE 'False' END AS IsLatePayment,
'False' AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K' -- Vendor
AND JE.AccountingDocumentCategory = 'V' -- Parked Document
UNION ALL
/* 2. Invoice Posted */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Invoice Posted' AS Activity,
JE.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
JEItem.PaymentBlockingReason AS PaymentBlockReason,
JE.CreatedByUser AS UserName,
NULL AS ClearingDate,
ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) AS NetDueDate,
'False' AS IsLatePayment,
CASE WHEN JE.CreatedByUser = 'BATCH_USER' THEN 'True' ELSE 'False' END AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JE.AccountingDocumentCategory <> 'V' -- Exclude Parked
UNION ALL
/* 3. Price Variance Detected (Inferred at Posting) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Price Variance Detected' AS Activity,
JE.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
JEItem.PaymentBlockingReason AS PaymentBlockReason,
'System' AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JEItem.PaymentBlockingReason = 'R' -- Standard SAP Price Variance Block Key
UNION ALL
/* 4. Quantity Variance Detected (Inferred at Posting) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Quantity Variance Detected' AS Activity,
JE.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
JEItem.PaymentBlockingReason AS PaymentBlockReason,
'System' AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JEItem.PaymentBlockingReason = 'M' -- Standard SAP Quantity Variance Block Key
UNION ALL
/* 5. Payment Block Applied (via Change Document) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Block Applied' AS Activity,
CD.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
CD.NewValue AS PaymentBlockReason,
CD.CreatedByUser AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_ChangeDocument AS CD
JOIN I_JournalEntry AS JE ON CD.ObjectValue = CONCAT(JE.CompanyCode, JE.AccountingDocument)
JOIN I_OperationalAcctgDocItem AS JEItem ON JE.AccountingDocument = JEItem.AccountingDocument AND JE.CompanyCode = JEItem.CompanyCode
WHERE CD.ObjectClass = 'BELEG'
AND CD.TableName = 'BSEG'
AND CD.FieldName = 'ZLSPR'
AND CD.OldValue IS NULL AND CD.NewValue IS NOT NULL
UNION ALL
/* 6. Payment Block Removed (via Change Document) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Block Removed' AS Activity,
CD.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
CD.CreatedByUser AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_ChangeDocument AS CD
JOIN I_JournalEntry AS JE ON CD.ObjectValue = CONCAT(JE.CompanyCode, JE.AccountingDocument)
JOIN I_OperationalAcctgDocItem AS JEItem ON JE.AccountingDocument = JEItem.AccountingDocument AND JE.CompanyCode = JEItem.CompanyCode
WHERE CD.ObjectClass = 'BELEG'
AND CD.TableName = 'BSEG'
AND CD.FieldName = 'ZLSPR'
AND CD.OldValue IS NOT NULL AND (CD.NewValue IS NULL OR CD.NewValue = '')
UNION ALL
/* 7. Payment Terms Changed (via Change Document) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Terms Changed' AS Activity,
CD.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
CD.NewValue AS PaymentTerms,
NULL AS PaymentBlockReason,
CD.CreatedByUser AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_ChangeDocument AS CD
JOIN I_JournalEntry AS JE ON CD.ObjectValue = CONCAT(JE.CompanyCode, JE.AccountingDocument)
JOIN I_OperationalAcctgDocItem AS JEItem ON JE.AccountingDocument = JEItem.AccountingDocument AND JE.CompanyCode = JEItem.CompanyCode
WHERE CD.ObjectClass = 'BELEG'
AND CD.TableName = 'BSEG'
AND CD.FieldName = 'ZTERM'
UNION ALL
/* 8. Invoice Due (Calculated) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Invoice Due' AS Activity,
TO_TIMESTAMP(ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays))) AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
'System' AS UserName,
NULL AS ClearingDate,
ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) < CURRENT_DATE
UNION ALL
/* 9. Cash Discount Lost (Calculated) */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Cash Discount Lost' AS Activity,
TO_TIMESTAMP(ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.CashDiscount1Days))) AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
'System' AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JEItem.CashDiscount1Days > 0
AND (JEItem.ClearingDate IS NULL OR JEItem.ClearingDate > ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.CashDiscount1Days)))
UNION ALL
/* 10. Payment Proposal Created */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Proposal Created' AS Activity,
PPI.ProposalRunDate AS EventTime, -- Often just a date, cast to timestamp if needed
JE.CompanyCode,
PPI.Supplier AS VendorNumber,
PPI.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
PPI.CreatedByUser AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_PaymentProposalItem AS PPI
JOIN I_JournalEntry AS JE
ON PPI.CompanyCode = JE.CompanyCode
AND PPI.AccountingDocument = JE.AccountingDocument
AND PPI.FiscalYear = JE.FiscalYear
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JEItem.FinancialAccountType = 'K'
UNION ALL
/* 11. Payment Run Executed */
/* Derived from existence in payment tables with a run ID */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Run Executed' AS Activity,
PPI.PaymentRunDate AS EventTime,
JE.CompanyCode,
PPI.Supplier AS VendorNumber,
PPI.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
PPI.CreatedByUser AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_PaymentProposalItem AS PPI
JOIN I_JournalEntry AS JE
ON PPI.CompanyCode = JE.CompanyCode
AND PPI.AccountingDocument = JE.AccountingDocument
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
WHERE PPI.PaymentRunID IS NOT NULL
UNION ALL
/* 12. Payment Document Created */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Document Created' AS Activity,
PayJE.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
PayJE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
PayJE.CreatedByUser AS UserName,
PayJE.PostingDate AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
JOIN I_JournalEntry AS PayJE
ON JEItem.ClearingJournalEntry = PayJE.AccountingDocument
AND JEItem.ClearingJournalEntryFiscalYear = PayJE.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JEItem.ClearingJournalEntry IS NOT NULL
UNION ALL
/* 13. Payment Cleared */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Payment Cleared' AS Activity,
TO_TIMESTAMP(JEItem.ClearingDate) AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
'System' AS UserName,
JEItem.ClearingDate,
ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) AS NetDueDate,
CASE WHEN JEItem.ClearingDate > ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) THEN 'True' ELSE 'False' END AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JEItem.ClearingDate IS NOT NULL
UNION ALL
/* 14. Invoice Reversed */
SELECT
JE.OriginalReferenceDocument AS InvoiceNumber,
'Invoice Reversed' AS Activity,
RevJE.CreationDateTime AS EventTime,
JE.CompanyCode,
JEItem.Supplier AS VendorNumber,
JEItem.AmountInTransactionCurrency AS InvoiceAmount,
JE.AccountingDocumentType AS DocumentType,
JEItem.PaymentTerms,
NULL AS PaymentBlockReason,
RevJE.CreatedByUser AS UserName,
NULL AS ClearingDate,
NULL AS NetDueDate,
NULL AS IsLatePayment,
NULL AS IsTouchless,
'S4HANA' AS SourceSystem,
CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem
ON JE.CompanyCode = JEItem.CompanyCode
AND JE.AccountingDocument = JEItem.AccountingDocument
AND JE.FiscalYear = JEItem.FiscalYear
JOIN I_JournalEntry AS RevJE
ON JE.ReverseDocument = RevJE.AccountingDocument
AND JE.ReverseDocumentFiscalYear = RevJE.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
AND JE.ReverseDocument IS NOT NULL