Your Order to Cash - Billing & Invoicing Data Template
Your Order to Cash - Billing & Invoicing Data Template
- Recommended attributes for comprehensive analysis
- Key process steps and milestones to track
- Practical data extraction guidance for SAP ECC
Order to Cash - Billing & Invoicing Attributes
| Name | Description | ||
|---|---|---|---|
| Activity ActivityName | The name of the business event or step that occurred within the invoice lifecycle. | ||
| Description This attribute describes a specific action or status change in the billing process, such as 'Invoice Generated', 'Invoice Posted', or 'Customer Payment Received'. These activities are conceptually derived from various system events, status changes in documents, or specific transaction codes executed by users. The sequence of these activities forms the process flow, which is the foundation of process mining analysis. By examining the activities, organizations can understand what steps are taken, in what order, and how frequently, revealing the actual process execution compared to the designed process. Why it matters It defines the steps in the process map, allowing for the visualization and analysis of process flows, deviations, and bottlenecks. Where to get This is a conceptual attribute derived from multiple sources, such as transaction codes (CDHDR-TCODE), document status changes (VBUK-FKSTK), and accounting document postings. Examples Invoice GeneratedInvoice PostedPayment Reminder IssuedInvoice Cleared | |||
| Invoice Number InvoiceNumber | The unique identifier for the billing document, serving as the primary case ID for the invoicing process. | ||
| Description The Invoice Number, known as the Billing Document Number in SAP, uniquely identifies each invoice. In process mining, it acts as the CaseId, grouping all related activities such as creation, posting, sending, payment, and clearing into a single end-to-end process instance. Analyzing processes by Invoice Number allows for a complete view of the lifecycle of each billing transaction, from its inception to its final settlement. This is crucial for calculating key performance indicators like Days Sales Outstanding (DSO) and overall invoice cycle time, providing a clear basis for performance measurement and improvement. Why it matters It is the essential key for tracking an invoice's entire journey, enabling analysis of cycle times, bottlenecks, and variations for each individual billing transaction. Where to get SAP ECC Table: VBRK, Field: VBELN Examples 90001234900012359000123690001237 | |||
| Start Time EventTime | The timestamp indicating when a specific activity or event occurred. | ||
| Description Event Time captures the precise date and time of each activity in the invoice lifecycle. This timestamp is fundamental for all time-based analysis in process mining, including calculating cycle times, identifying bottlenecks, and monitoring process performance against service level agreements. This attribute is typically constructed by combining a date field, like Posting Date (BUDAT), with a time field (UZEIT) from various SAP tables that record changes or document creation. Accurate timestamps are critical for building a reliable event log and ensuring the validity of any performance analysis. Why it matters This attribute is the foundation for all performance analysis, enabling the calculation of cycle times, durations, and waiting times between process steps. Where to get Constructed from various date and time fields across multiple tables, such as BKPF (BUDAT, CPUTM), VBRK (ERDAT, ERZET), and change log tables like CDHDR (UDATE, UTIME). Examples 2023-04-15T10:30:00Z2023-04-16T11:00:00Z2023-05-20T09:00:00Z | |||
| Billing Document Type BillingDocumentType | A code that categorizes the type of billing document, such as an invoice, credit memo, or debit memo. | ||
| Description The Billing Document Type classifies transactions into distinct categories based on their business purpose. For example, 'F2' is a standard customer invoice, while 'G2' represents a credit memo. This classification is configured in SAP to control how different billing documents are processed. For process mining, this attribute is essential for filtering and comparing different billing scenarios. Analysts can examine the process for standard invoices separately from credit memos to understand their unique flows, cycle times, and challenges, leading to more targeted process improvements. Why it matters Allows for the segmentation and analysis of different billing processes, such as standard invoices versus credit memos, which often have very different process flows. Where to get SAP ECC Table: VBRK, Field: FKART Examples F2G2L2IV | |||
| Company Code CompanyCode | The identifier for the legal entity that issued the invoice. | ||
| Description The Company Code represents an independent legal and accounting unit in SAP. All financial transactions, including invoices, are posted to a specific company code. This is a fundamental organizational data element. In a process mining context, Company Code is used to analyze and compare billing process performance across different legal entities within a corporation. This helps identify best practices in one entity that could be applied to others and ensures that analysis respects the company's organizational structure. Why it matters Allows for filtering and comparing processes across different legal entities, which is fundamental for financial analysis and organizational benchmarking. Where to get SAP ECC Table: VBRK, Field: BUKRS Examples 10002000US01DE01 | |||
| Customer Number CustomerNumber | A unique number that identifies the customer to whom the invoice is issued. | ||
| Description The Customer Number links an invoice to a specific client or business partner. This attribute is critical for segmenting and analyzing the billing process based on customer characteristics. Analysts can use this field to compare Days Sales Outstanding (DSO) across different customers, identify which customers frequently pay late, or analyze payment term compliance. Understanding these patterns is key to managing customer relationships and improving cash collection strategies tailored to different customer segments. Why it matters Enables customer-centric analysis, helping to identify payment behaviors, assess DSO by customer, and tailor collection strategies. Where to get SAP ECC Table: VBRK, Field: KUNRG (Payer) or KUNAG (Sold-to party). Examples 100023200541CUST-A487910345 | |||
| Total Invoice Amount TotalInvoiceAmount | The total net value of the billing document. | ||
| Description This attribute represents the total net amount of the invoice, excluding taxes. The invoice amount is a critical piece of financial data associated with the billing process. It is used in various analyses, such as segmenting invoices into high-value and low-value categories to see if their process flows differ. It can also be used to prioritize collections efforts or to investigate why high-value invoices take longer to get approved or paid. This financial context adds a significant layer of depth to process analysis. Why it matters Provides essential financial context, allowing for analysis based on invoice value, such as identifying if high-value invoices follow a different process or take longer to clear. Where to get SAP ECC Table: VBRK, Field: NETWR Examples 1500.7525000.00500.0012345.67 | |||
| User Name UserName | The ID of the user who executed the activity or created the document. | ||
| Description This attribute captures the SAP user ID responsible for a given event, such as creating an invoice or posting a payment. It is essential for analyzing the human element of the process. With this data, it's possible to investigate performance variations between users or teams, identify training needs, and detect potential compliance issues. It is also used to differentiate between manual activities performed by human users and automated steps executed by system or batch users, which is key for calculating automation rates. Why it matters Enables analysis of user performance, workload distribution, and helps distinguish between manual and automated activities, supporting automation and efficiency initiatives. Where to get SAP ECC Table: VBRK, Field: ERNAM (Created by) or BKPF, Field: USNAM (User name) or CDHDR, Field: USERNAME (User). Examples JSMITHBW_BATCHLROSSIMKUMAR | |||
| Clearing Date ClearingDate | The date when the payment was received and the invoice was cleared from accounts receivable. | ||
| Description The Clearing Date is the date on which an open item, like an invoice, is marked as paid or 'cleared' in the financial system. This date effectively represents when the cash is considered collected and reconciled. This is one of the most important dates in the Order to Cash cycle. It serves as the end point for calculating Days Sales Outstanding (DSO) and the overall invoice-to-cash cycle time. Analyzing the clearing date helps measure the effectiveness of the collections process. Why it matters Marks the final step of the invoice lifecycle, serving as the end date for DSO and overall cycle time calculations, and reflecting cash collection efficiency. Where to get SAP ECC Table: BSAD, Field: AUGDT Examples 2023-05-142023-06-012023-06-25 | |||
| Currency Currency | The currency code for the amounts specified in the invoice. | ||
| Description This attribute indicates the currency of the transaction, such as USD, EUR, or JPY. It provides the necessary context for any monetary values, like the Total Invoice Amount. When analyzing data from a multinational organization, the currency field is essential for correctly interpreting and converting financial figures. It allows for consistent reporting and ensures that amounts are not aggregated without proper currency conversion, which would lead to incorrect financial analysis. Why it matters Provides the necessary context for all monetary values, ensuring accurate financial analysis, especially in a multi-currency environment. Where to get SAP ECC Table: VBRK, Field: WAERK Examples USDEURGBPJPY | |||
| Document Date DocumentDate | The date on the original document, provided by the vendor or creator. | ||
| Description The Document Date is the date the original document was issued. For billing, this is typically the date the invoice was created and is often used as the basis for calculating the payment due date. This date is crucial for financial reporting and for calculating key metrics like Days Sales Outstanding (DSO). It represents the starting point of the collection period from the customer's point of view. Analyzing discrepancies between the document date and the posting date can reveal internal delays in processing incoming invoices. Why it matters Serves as the baseline for calculating invoice age and DSO, providing a critical reference point for financial and payment term analysis. Where to get SAP ECC Table: VBRK, Field: FKDAT (Billing Date) Examples 2023-04-152023-04-162023-05-20 | |||
| Invoice Due Date InvoiceDueDate | The date by which the customer is expected to make the payment. | ||
| Description The Invoice Due Date is the deadline for payment as specified by the payment terms. This date is fundamental for managing accounts receivable and initiating collections activities. This attribute is used to calculate the On-Time Payment Rate KPI by comparing it with the actual payment date. Analyzing invoices by their due date helps in forecasting cash flow and prioritizing collection efforts for upcoming or overdue payments. It is typically derived from the baseline date and the payment terms. Why it matters This is the benchmark for measuring on-time payment performance and is essential for managing accounts receivable and cash flow forecasting. Where to get Calculated based on the baseline date (BSEG-ZFBDT) and payment terms (BSEG-ZTERM). It is not always stored in a direct field. Examples 2023-05-152023-05-302023-06-20 | |||
| Is Automated IsAutomated | A flag indicating if an activity was performed by a system user or through automation. | ||
| Description This calculated attribute is a boolean flag that distinguishes between manual and automated activities. It is typically derived by checking the User Name attribute against a list of known system or batch user IDs, like 'BATCHUSER' or 'SAPSYSTEM'. This flag is essential for measuring the level of automation in the billing process, which is a key goal for many organizations seeking to improve efficiency and reduce costs. The Automated Billing Rate KPI is directly calculated from this attribute, helping to track the progress of automation initiatives. Why it matters Directly supports the calculation of the automated billing rate, helping to measure process efficiency and track the impact of automation projects. Where to get Derived from the User Name attribute. The logic would be something like: IF UserName IN ('BATCH', 'SYSTEM', 'RFCUSER') THEN true ELSE false. Examples truefalse | |||
| Is Rework IsRework | A flag indicating if an activity is a rework or correction step. | ||
| Description This calculated attribute identifies activities that represent rework, such as 'Invoice Corrected' or document reversals. It is typically a boolean flag derived from the activity name or from transaction codes associated with corrections and cancellations, like 'VF11' for cancelling a billing document. In process mining, this flag is invaluable for quantifying the amount of rework in the billing process. It directly supports KPIs like the Invoice Correction Rate and helps visualize rework loops in the process map, highlighting inefficiencies and quality issues that increase operational costs and delay payments. Why it matters Helps quantify process inefficiencies and quality issues by highlighting how much effort is spent on correcting errors, directly supporting rework KPIs. Where to get Derived from the Activity Name or Transaction Code. For example, IF ActivityName = 'Invoice Corrected' OR TransactionCode = 'VF11' THEN true ELSE false. Examples truefalse | |||
| Last Data Update LastDataUpdate | The timestamp of the most recent data refresh or extraction from the source system. | ||
| Description This attribute indicates the last time the dataset was updated from the source system. It provides crucial context for any analysis, ensuring that users understand the freshness of the data they are viewing. In dashboards and reports, this timestamp informs stakeholders about the data's timeliness and helps manage expectations about the visibility of very recent transactions. It is typically generated at the end of the data extraction process. Why it matters Informs users about the timeliness of the data, which is critical for making operational decisions based on the analysis. Where to get Generated and stored during the data extraction, transformation, and loading (ETL) process. Examples 2023-10-27T02:00:00Z2023-10-28T02:00:00Z | |||
| Payment Terms PaymentTerms | The conditions under which a seller will complete a sale, including the payment schedule. | ||
| Description Payment Terms define the rules for when a payment is due, such as 'Net 30' or 'Net 60'. These terms are agreed upon with the customer and are a key factor in determining cash flow. Analyzing the process by payment terms can reveal if certain terms are associated with longer payment cycles or higher rates of late payments. This insight can help the business negotiate better terms with customers or adjust its financial planning. It is also a key input for calculating the invoice due date. Why it matters Helps analyze customer payment behavior and cash flow impact based on negotiated terms, providing insights for optimizing commercial agreements. Where to get SAP ECC Table: VBRK, Field: ZTERM Examples Z030Z060Z001 | |||
| Posting Date PostingDate | The date on which the document is posted to the financial accounting books. | ||
| Description The Posting Date determines the fiscal period in which the transaction is recorded in the General Ledger. It is a critical date for accounting and financial reporting. Delays between the document creation date and the posting date can indicate inefficiencies in the internal handling of billing documents. From a process mining view, the posting date marks a key milestone in the invoice lifecycle. The time lag between invoice generation and posting can be a key performance indicator for the billing department's efficiency. Why it matters Marks a key financial milestone and is crucial for accounting. The time lag between invoice creation and posting is a key measure of internal processing efficiency. Where to get SAP ECC Table: BKPF, Field: BUDAT Examples 2023-04-152023-04-172023-05-21 | |||
| Sales Document Number SalesDocumentNumber | The identifier of the original sales order that led to the invoice. | ||
| Description This attribute provides a direct link from the invoice back to the sales order that initiated the transaction. This traceability is crucial for a complete end-to-end Order to Cash analysis. By connecting the billing process with the preceding sales order process, organizations can analyze the total cycle time from customer order to cash receipt. It helps in identifying whether delays in billing are caused by issues in sales, fulfillment, or the billing department itself, providing a more holistic view of the process. Why it matters Links the billing process back to the sales order, enabling a true end-to-end Order to Cash analysis and helping to identify cross-departmental delays. Where to get SAP ECC Table: VBRP, Field: VGBEL Examples 100000451000004610000047 | |||
| Sales Organization SalesOrganization | The organizational unit responsible for the sale of products or services. | ||
| Description The Sales Organization is an organizational unit in SAP responsible for distributing goods and services and negotiating sales conditions. It is a key field for structuring sales and distribution operations. In process mining, this attribute allows for the analysis of the billing process from a sales structure point of view. It enables performance comparison between different sales organizations, helping to identify which regions or business lines are more efficient in their billing processes and supporting initiatives to standardize best practices. Why it matters Enables performance benchmarking and analysis across different sales divisions or regions, helping to identify best practices and areas for improvement. Where to get SAP ECC Table: VBRK, Field: VKORG Examples 1000NA01EU01AP01 | |||
| Source System SourceSystem | Identifies the source system from which the data was extracted. | ||
| Description This attribute specifies the system of record where the data originated. In a corporate environment with multiple ERP instances or integrated systems, this field helps distinguish between data from different sources. For process mining, it is essential for data validation and for analyses that compare processes across different systems or organizational units. It is typically populated as a static value during the data extraction process to label the dataset. Why it matters Provides context for the data's origin, which is crucial in environments with multiple systems for ensuring data integrity and enabling system-specific analysis. Where to get This is typically a static value added during the data extraction, transformation, and loading (ETL) process, identifying the specific SAP ECC instance (e.g., 'ECC_PROD_NA'). Examples SAP_ECC_PRODECC_EU_100SAP_US_FIN | |||
| Transaction Code TransactionCode | The SAP transaction code used to perform an activity. | ||
| Description The Transaction Code, or T-Code, is a unique identifier for a specific function or program in SAP, such as 'VF01' for creating a billing document. Capturing the T-Code for each event provides a technical, system-level view of how a process was executed. This information is highly valuable for root cause analysis. For example, if errors are common, analysts can check if a non-standard transaction code is being used. It also helps in deriving the activity name and understanding which system functionalities are employed in the process. Why it matters Provides technical context for how an activity was performed, enabling root cause analysis of process deviations and helping to identify non-standard user actions. Where to get SAP ECC Table: CDHDR, Field: TCODE Examples VF01VF02FB01F-28 | |||
Order to Cash - Billing & Invoicing Activities
| Activity | Description | ||
|---|---|---|---|
| Customer Payment Received | A payment has been received from the customer and posted in the system as a cash receipt or bank deposit. This creates a separate payment document that is not yet applied to the specific invoice. | ||
| Why it matters This is a major milestone in the cash conversion cycle. The time from invoice sending to payment receipt is a primary component of Days Sales Outstanding (DSO). Where to get Recorded as a new document in BKPF and BSEG, typically with a document type indicating customer payment, such as 'DZ'. The posting date (BUDAT) marks the event. Capture From posting date of the customer payment document in BKPF. Event type explicit | |||
| Invoice Cleared | The final status of a successfully paid invoice, indicating that the open item has been closed by a corresponding payment or credit memo. The invoice is considered fully settled. | ||
| Why it matters Marks the successful completion of the Order to Cash cycle for an invoice. This is the primary end event for measuring the overall Average Invoice Cycle Time. Where to get Occurs when the clearing document (AUGBL) and clearing date (AUGDT) fields are populated for the invoice line item in the BSEG table. Capture Event occurs on the clearing date (AUGDT) recorded in the BSEG table for the invoice line item. Event type explicit | |||
| Invoice Generated | Marks the creation of the billing document in the system. This event is captured when a new entry is created in the accounting document header table (BKPF) with a specific document type for invoices. | ||
| Why it matters This is the starting point for the entire invoicing process. Analyzing the time from this event helps measure the Invoice Creation Cycle Time and is the basis for calculating Days Sales Outstanding (DSO). Where to get Recorded in table BKPF. The creation date (CPUDT) and time (CPUTM) for a specific document number (BELNR) mark this event. The document type (BLART) identifies it as an invoice. Capture From creation timestamp (CPUDT) in table BKPF for the invoice document. Event type explicit | |||
| Invoice Posted | The invoice is formally recorded in the Accounts Receivable sub-ledger and the General Ledger. This event makes the invoice legally binding and reflects the debt owed by the customer. | ||
| Why it matters This is a critical milestone that officially starts the collection clock. The time between generation and posting can highlight internal processing delays affecting cash flow. Where to get Recorded in table BKPF. The posting date (BUDAT) for the document number (BELNR) marks this event. For parked documents, this is when it's converted to a posted document. Capture From posting date (BUDAT) in table BKPF for the invoice document. Event type explicit | |||
| Invoice Sent To Customer | Indicates that the invoice has been dispatched to the customer through a defined output channel like print, email, or EDI. This is typically captured from the output management system logs. | ||
| Why it matters This event is a key milestone that triggers the customer's payment term clock. Delays here directly impact when a payment can be expected and affect the Payment Collection Efficiency. Where to get Can be inferred from the processing date and time in the message status table (NAST) for the output type corresponding to the invoice. Capture Inferred from NAST table entry with processing status '1' (successfully processed). Event type inferred | |||
| Dispute Case Created | A formal dispute has been logged against the invoice, typically due to customer complaints. This is recorded in the SAP Dispute Management system. | ||
| Why it matters Identifies invoices that are at risk of delayed payment and highlights underlying issues causing customer dissatisfaction. It marks the beginning of an important exception handling process. Where to get Captured from the creation of a case in the dispute case table (UDM_CASE) linked to the invoice's accounting document. Capture Logged when a user creates a dispute case via transaction UDM_DISPUTE. Event type explicit | |||
| Invoice Approved | Represents the formal approval of the invoice, allowing it to be posted or sent to the customer. This is often inferred when a parked document is converted into a posted document. | ||
| Why it matters Tracks the internal approval workflow, which is a common source of bottlenecks. Analyzing this activity helps support the Invoice Approval Flow Analysis dashboard by identifying slow approvers. Where to get Can be inferred by the transition of a document from parked status (in VBKPF) to posted status (in BKPF). Alternatively, if a workflow system is used, this may be an explicit event in workflow logs. Capture Compare creation date of parked doc (VBKPF) to posting date of final doc (BKPF). Event type inferred | |||
| Invoice Corrected | Represents a rework activity where an initial invoice was found to be incorrect and was subsequently reversed. This is captured by identifying reversal documents linked to the original invoice. | ||
| Why it matters Highlights process inefficiencies and quality issues. A high frequency of corrections signals problems in the upstream sales or billing data, supporting the Invoice Rework And Error Rates dashboard. Where to get Identified by finding a reversal document where BKPF-STBLG points to the original document. The creation of this reversal document is the event. Capture Logged when a reversal document is created (e.g., via FB08). Event type explicit | |||
| Invoice Due Date Reached | A calculated event marking the day the invoice payment is officially due according to the payment terms. It is not an activity performed by a user or system but a critical point in time. | ||
| Why it matters Essential for analyzing payment behavior and compliance. It is the baseline for determining on-time vs. late payments and calculating the On-Time Payment Rate KPI. Where to get Derived by comparing the current date to the net due date. The due date is found in field BSEG-ZFBDT or calculated from the baseline date and payment terms. Capture Compare system date with the net due date field in the invoice line item (BSEG). Event type calculated | |||
| Invoice Parked | The invoice document has been saved in a preliminary state without being posted to the general ledger. This is often used when information is incomplete or requires review before final posting. | ||
| Why it matters Tracks pre-posting steps and potential delays. A long duration in the parked state can indicate data quality issues or bottlenecks in the pre-approval process. Where to get Parked documents are stored in table VBKPF. The creation of a document here, which is later posted, signifies this activity. Capture Logged upon saving a parked document using a transaction like FV70. Event type explicit | |||
| Invoice Written Off | An alternative final status where the invoice is deemed uncollectible and the outstanding amount is cleared against a bad debt account. This closes the invoice without a customer payment. | ||
| Why it matters Represents a negative process outcome and revenue loss. Tracking these events helps in analyzing bad debt reasons and improving credit management policies. Where to get Inferred by analyzing the clearing transaction for the invoice. If the clearing document posts to a specific bad debt expense G/L account, the invoice is considered written off. Capture Inferred when the clearing transaction involves a posting to a designated bad debt G/L account. Event type inferred | |||
| Payment Applied To Invoice | The received customer payment has been matched and applied against the specific open invoice, marking the item for clearing. This is the reconciliation step that links the payment to the debt. | ||
| Why it matters This activity is crucial for measuring the Cash Application Cycle Time. Delays in applying cash can misrepresent the true accounts receivable status and hide available cash. Where to get Inferred from the clearing transaction, e.g., F-32, which populates the clearing fields in the invoice line item. The event timestamp is the clearing date. Capture Inferred from the clearing date (AUGDT) being populated in the invoice line item table (BSEG). Event type inferred | |||
| Payment Reminder Issued | The system has generated and sent a dunning notice or payment reminder to the customer for an overdue invoice. This is captured from the dunning history logs. | ||
| Why it matters Helps evaluate the effectiveness of the collections strategy. Analyzing the time from reminder to payment receipt is key for the Payment Reminder Effectiveness KPI. Where to get Recorded in the dunning data tables, specifically MHNK (Dunning data header) and MHND (Dunning data line items), which are generated by the dunning run (Transaction F150). Capture Logged upon execution of a dunning run (F150) for the overdue item. Event type explicit | |||
Extraction Guides
Steps
- Access ABAP Editor: Log into your SAP ECC system. Navigate to the ABAP Editor using transaction code
SE38. - Create Program: Enter a name for your new program in the Program field, for example,
Z_PM_O2C_INVOICE_EXTRACT, and click the Create button. Provide a descriptive title and set the program Type to 'Executable Program'. - Define Selection Screen: In the program source code, define the selection screen parameters. This allows users to filter the data for extraction. Key parameters include the document creation date range (
S_ERDAT), Company Code (S_BUKRS), and Billing Document Type (S_VBTYP). - Define Data Structures: Declare an internal table structure that will hold the final event log data. This structure must include fields for
InvoiceNumber,ActivityName,EventTime, and recommended attributes likeUserName,BillingDocumentType,CustomerNumber,CompanyCode, andTotalInvoiceAmount. - Implement Data Selection Logic: Write the core ABAP logic to select data. First, select the primary billing documents from tables
VBRKandBKPFbased on the user's selection screen inputs. Store these in a temporary internal table. - Extract Activities: Loop through the initial list of billing documents. For each document, perform subsequent selections from various tables to identify the 13 required activities. For instance, query table
NASTfor 'Invoice Sent To Customer' events,BSEGfor clearing information ('Invoice Cleared', 'Payment Applied'), andMHNKfor dunning data ('Payment Reminder Issued'). - Build the Event Log Table: For each activity found in the previous step, populate a new record in your final event log internal table. Ensure the
InvoiceNumber,ActivityName,EventTime, and other attributes are correctly mapped from the source tables. - Write to Application Server: Once the loop is complete and the final event log table is fully populated, use the
OPEN DATASET,LOOP AT... TRANSFER, andCLOSE DATASETstatements to write the contents of the internal table to a flat file on the SAP application server. Specify a logical file path that is accessible. - Retrieve the File: Use transaction code
AL11to navigate the application server directories and locate the generated file. Coordinate with your SAP Basis team to download the file from the server to your local machine or a shared network location. - Final Formatting: Open the downloaded file and confirm it is a comma-separated value (CSV) file with a header row. Ensure the file is saved with UTF-8 encoding to be compatible with ProcessMind for upload.
Configuration
- Prerequisites: Access to create and execute ABAP programs (transaction SE38). Authorization to read from FI and SD tables, including
VBRK,VBRP,BKPF,BSEG,NAST,MHNK, andUDM_CASE_ATTR00(for Dispute Management). - Date Range Selection: The program should have a mandatory date range parameter, typically based on the document creation date (
ERDATin VBRK/BKPF). For initial extraction, a range of 3-6 months is recommended to keep the dataset manageable. - Key Filters: Always filter by
CompanyCode(BUKRS) to limit the scope of the extraction. It is also highly recommended to filter by Billing Document Type (VBTYPfrom VBRK) or Accounting Document Type (BLARTfrom BKPF) to include only relevant invoice types, for example, 'RV' for standard accounting invoices, and exclude credit memos or other documents. - Performance Considerations: For large datasets covering more than a few months, the program should be executed as a background job to avoid session timeouts. The ABAP logic should be optimized to use indexed table reads and avoid nested loops with database selections inside them. Selecting data into internal tables first and then processing them is the preferred approach.
- Output File Configuration: The ABAP code must specify the output file path on the application server and the delimiter for the CSV file, typically a comma or semicolon. Ensure the path is a globally configured and accessible directory.
a Sample Query abap
REPORT Z_PM_O2C_INVOICE_EXTRACT.
*&---------------------------------------------------------------------*
*& Tables
*&---------------------------------------------------------------------*
TABLES: VBRK, BKPF.
*&---------------------------------------------------------------------*
*& Type Definitions for Event Log Output
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_event_log,
invoicenumber TYPE vbrk-vbeln,
activityname TYPE string,
eventtime TYPE timestamp,
username TYPE xubname,
billingdocumenttype TYPE vbrk-vbtyp,
customernumber TYPE vbrk-kunnr,
companycode TYPE vbrk-bukrs,
totalinvoiceamount TYPE vbrk-netwr,
END OF ty_event_log.
*&---------------------------------------------------------------------*
*& Data Declarations
*&---------------------------------------------------------------------*
DATA: gt_event_log TYPE TABLE OF ty_event_log,
gs_event_log TYPE ty_event_log.
DATA: BEGIN OF gs_invoice,
vbeln TYPE vbrk-vbeln, " SD Doc (Invoice)
awkey TYPE bkpf-awkey, " Accounting Doc Reference Key
bukrs TYPE vbrk-bukrs, " Company Code
kunnr TYPE vbrk-kunnr, " Customer
vbtyp TYPE vbrk-vbtyp, " SD Doc Type
netwr TYPE vbrk-netwr, " Net Value
waerk TYPE vbrk-waerk, " Currency
fkdat TYPE vbrk-fkdat, " Billing Date
erdat TYPE vbrk-erdat, " Creation Date
erzet TYPE vbrk-erzet, " Creation Time
ernam TYPE vbrk-ernam, " Creator
belnr TYPE bkpf-belnr, " Acct Doc
gjahr TYPE bkpf-gjahr, " Fiscal Year
cpudt TYPE bkpf-cpudt, " Acct Doc Entry Date
cputm TYPE bkpf-cputm, " Acct Doc Entry Time
usnam TYPE bkpf-usnam, " Acct Doc User
stblg TYPE bkpf-stblg, " Reversal Doc
END OF gs_invoice.
DATA: gt_invoices LIKE TABLE OF gs_invoice.
*&---------------------------------------------------------------------*
*& Selection Screen
*&---------------------------------------------------------------------*
SELECT-OPTIONS: s_erdat FOR vbrk-erdat OBLIGATORY,
s_bukrs FOR vbrk-bukrs OBLIGATORY,
s_vbtyp FOR vbrk-vbtyp.
PARAMETERS: p_path TYPE string DEFAULT '/usr/sap/trans/tmp/invoice_extract.csv' OBLIGATORY.
*&---------------------------------------------------------------------*
*& Main Processing Block
*&---------------------------------------------------------------------*
START-OF-SELECTION.
" 1. Select base set of invoices
SELECT vbrk~vbeln, vbrk~bukrs, vbrk~kunnr, vbrk~vbtyp, vbrk~netwr, vbrk~waerk,
vbrk~fkdat, vbrk~erdat, vbrk~erzet, vbrk~ernam,
bkpf~belnr, bkpf~gjahr, bkpf~cpudt, bkpf~cputm, bkpf~usnam, bkpf~stblg, bkpf~awkey
INTO CORRESPONDING FIELDS OF TABLE gt_invoices
FROM vbrk
INNER JOIN bkpf ON bkpf~awkey = vbrk~vbeln AND bkpf~awtyp = 'VBRK'
WHERE vbrk~erdat IN s_erdat
AND vbrk~bukrs IN s_bukrs
AND vbrk~vbtyp IN s_vbtyp.
IF gt_invoices IS INITIAL.
MESSAGE 'No invoices found for the selected criteria.' TYPE 'I'.
RETURN.
ENDIF.
LOOP AT gt_invoices INTO gs_invoice.
CLEAR gs_event_log.
gs_event_log-invoicenumber = gs_invoice-vbeln.
gs_event_log-billingdocumenttype = gs_invoice-vbtyp.
gs_event_log-customernumber = gs_invoice-kunnr.
gs_event_log-companycode = gs_invoice-bukrs.
gs_event_log-totalinvoiceamount = gs_invoice-netwr.
" Activity: Invoice Generated (using accounting doc creation)
gs_event_log-activityname = 'Invoice Generated'.
gs_event_log-username = gs_invoice-usnam.
CONCATENATE gs_invoice-cpudt gs_invoice-cputm INTO DATA(lv_ts_gen).
CONVERT DATE gs_invoice-cpudt TIME gs_invoice-cputm INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
APPEND gs_event_log TO gt_event_log.
" Activity: Invoice Posted (same as generated for non-parked docs)
gs_event_log-activityname = 'Invoice Posted'.
gs_event_log-username = gs_invoice-usnam.
CONVERT DATE gs_invoice-cpudt TIME gs_invoice-cputm INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
APPEND gs_event_log TO gt_event_log.
" Activity: Invoice Approved (inferred by posting)
gs_event_log-activityname = 'Invoice Approved'.
APPEND gs_event_log TO gt_event_log.
" Activity: Invoice Sent To Customer
SELECT SINGLE addat, aduhr FROM nast
INTO (DATA(lv_nast_date), DATA(lv_nast_time))
WHERE kappl = 'V3' AND objky = gs_invoice-vbeln AND vszst > '0'.
IF sy-subrc = 0.
gs_event_log-activityname = 'Invoice Sent To Customer'.
gs_event_log-username = sy-uname.
CONVERT DATE lv_nast_date TIME lv_nast_time INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
APPEND gs_event_log TO gt_event_log.
ENDIF.
" Activity: Invoice Corrected / Reversed
IF gs_invoice-stblg IS NOT INITIAL.
SELECT SINGLE cpudt, cputm, usnam FROM bkpf
INTO (DATA(lv_rev_date), DATA(lv_rev_time), DATA(lv_rev_user))
WHERE belnr = gs_invoice-stblg AND gjahr = gs_invoice-gjahr.
IF sy-subrc = 0.
gs_event_log-activityname = 'Invoice Corrected'.
gs_event_log-username = lv_rev_user.
CONVERT DATE lv_rev_date TIME lv_rev_time INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
APPEND gs_event_log TO gt_event_log.
ENDIF.
ENDIF.
" Activity: Payment Applied, Cleared, Due Date, Written Off (from BSEG)
SELECT SINGLE augdt, augbl, zfBDT, hkont FROM bseg
INTO (DATA(lv_augdt), DATA(lv_augbl), DATA(lv_zfbdt), DATA(lv_hkont))
WHERE bukrs = gs_invoice-bukrs
AND belnr = gs_invoice-belnr
AND gjahr = gs_invoice-gjahr
AND koart = 'D'. " Customer line
IF sy-subrc = 0.
" Due Date Reached (Calculated event)
IF lv_zfbdt IS NOT INITIAL.
gs_event_log-activityname = 'Invoice Due Date Reached'.
gs_event_log-username = 'System'.
CONVERT DATE lv_zfbdt INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
APPEND gs_event_log TO gt_event_log.
ENDIF.
" Cleared, Applied, Write-Off
IF lv_augdt IS NOT INITIAL.
SELECT SINGLE usnam, cpudt, cputm, blart FROM bkpf
INTO (DATA(lv_clear_user), DATA(lv_clear_date), DATA(lv_clear_time), DATA(lv_clear_type))
WHERE belnr = lv_augbl AND bukrs = gs_invoice-bukrs.
IF sy-subrc = 0.
gs_event_log-username = lv_clear_user.
CONVERT DATE lv_clear_date TIME lv_clear_time INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
IF lv_clear_type = 'DZ'. " Standard Customer Payment
gs_event_log-activityname = 'Customer Payment Received'. APPEND gs_event_log TO gt_event_log.
gs_event_log-activityname = 'Payment Applied To Invoice'. APPEND gs_event_log TO gt_event_log.
gs_event_log-activityname = 'Invoice Cleared'. APPEND gs_event_log TO gt_event_log.
ELSE. " Assuming other clearing doc types could be write-offs
gs_event_log-activityname = 'Invoice Written Off'.
APPEND gs_event_log TO gt_event_log.
ENDIF.
ENDIF.
ENDIF.
ENDIF.
" Activity: Payment Reminder Issued (Dunning)
SELECT COUNT(*) FROM mhnk WHERE kunnr = gs_invoice-kunnr AND bukrs = gs_invoice-bukrs AND lafdn > gs_invoice-cpudt.
IF sy-subrc = 0 AND sy-dbcnt > 0.
SELECT SINGLE lafdn FROM mhnk
INTO DATA(lv_dunning_date)
WHERE kunnr = gs_invoice-kunnr AND bukrs = gs_invoice-bukrs AND lafdn > gs_invoice-cpudt.
gs_event_log-activityname = 'Payment Reminder Issued'.
gs_event_log-username = 'System'.
CONVERT DATE lv_dunning_date INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
APPEND gs_event_log TO gt_event_log.
ENDIF.
" Activity: Invoice Parked (Example from VBKPF, may require system specific logic)
SELECT SINGLE cpudt, cputm, usnam FROM vbkpf
INTO (DATA(lv_park_date), DATA(lv_park_time), DATA(lv_park_user))
WHERE awkey = gs_invoice-vbeln AND awsys = 'LOG' AND bstat = 'V'.
IF sy-subrc = 0.
gs_event_log-activityname = 'Invoice Parked'.
gs_event_log-username = lv_park_user.
CONVERT DATE lv_park_date TIME lv_park_time INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
APPEND gs_event_log TO gt_event_log.
ENDIF.
" Activity: Dispute Case Created (Requires Dispute Management module)
SELECT SINGLE create_date, create_time, create_user FROM udm_case_attr00
INTO (DATA(lv_disp_date), DATA(lv_disp_time), DATA(lv_disp_user))
WHERE [Your logic to link invoice to dispute case, e.g., via a custom field or object link].
IF sy-subrc = 0.
gs_event_log-activityname = 'Dispute Case Created'.
gs_event_log-username = lv_disp_user.
CONVERT DATE lv_disp_date TIME lv_disp_time INTO TIME STAMP gs_event_log-eventtime TIME ZONE sy-zonlo.
APPEND gs_event_log TO gt_event_log.
ENDIF.
ENDLOOP.
*&---------------------------------------------------------------------*
*& Write data to file
*&---------------------------------------------------------------------*
OPEN DATASET p_path FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
IF sy-subrc <> 0.
MESSAGE 'Error opening file.' TYPE 'E'.
ENDIF.
" Header
DATA(lv_header) = 'InvoiceNumber,ActivityName,EventTime,UserName,BillingDocumentType,CustomerNumber,CompanyCode,TotalInvoiceAmount'.
TRANSFER lv_header TO p_path.
LOOP AT gt_event_log INTO gs_event_log.
DATA(lv_line) = |
{ gs_event_log-invoicenumber }|
,{ gs_event_log-activityname }|
,{ gs_event_log-eventtime }|
,{ gs_event_log-username }|
,{ gs_event_log-billingdocumenttype }|
,{ gs_event_log-customernumber }|
,{ gs_event_log-companycode }|
,{ gs_event_log-totalinvoiceamount }|.
TRANSFER lv_line TO p_path.
ENDLOOP.
CLOSE DATASET p_path.
WRITE: 'Extraction complete. File created at:', p_path.Steps
- Prerequisites and Access: Ensure you have a database user with read-only access to the necessary SAP ECC tables, including VBRK, BKPF, BSAD, NAST, CDHDR, CDPOS, SCASE, and others specified in the query. This level of access is typically granted only to system administrators or specific data analytics teams.
- Connect to the Database: Use a standard SQL client tool, such as DBeaver, Oracle SQL Developer, or Microsoft SQL Server Management Studio, to establish a connection to the SAP ECC database.
- Prepare the SQL Query: Copy the complete SQL query provided in the 'query' section into your SQL client's editor.
- Customize Placeholders: The query contains several placeholders that you must replace with values specific to your environment. These include:
'YYYYMMDD': Replace all instances with the start and end dates for your desired analysis period. It is crucial to filter the data to a manageable timeframe.'XXXX': Replace with the specific Company Code(s) you wish to analyze.[Your Invoice Output Type]: Specify the output type code used for sending invoices to customers, for example, 'RD00'.[Your Bad Debt G/L Account]: Enter the General Ledger account number used for writing off uncollectible invoices.[Your Dispute Case Invoice Attribute]: Specify the attribute name used to store the invoice number in your Dispute Management configuration, for example, 'INVOICE_ID'.
- Review Timestamp Functions: The query uses a generic
CAST(CONCAT(date_field, time_field) AS TIMESTAMP)syntax. You may need to adjust this to match your specific database system, for example, usingTO_TIMESTAMPfor Oracle orDATETIMEFROMPARTSfor SQL Server. - Execute the Query: Run the modified query. The execution may take a significant amount of time depending on the size of your SAP tables and the selected date range.
- Inspect the Results: Once the query completes, review the output to ensure it contains the expected columns: InvoiceNumber, ActivityName, EventTime, and the recommended attributes. Check for any errors or empty results.
- Export to CSV: Export the complete result set from your SQL client to a CSV file. Ensure the file uses UTF-8 encoding to prevent issues with special characters.
- Prepare for Upload: Before uploading to a process mining tool, confirm that the CSV column headers exactly match the required attribute names, for example,
InvoiceNumber,ActivityName,EventTime,UserName.
Configuration
- Database Connection: A direct, read-only SQL connection to the underlying SAP ECC database is required. This method bypasses the SAP application layer entirely.
- Authorization: The database user must have
SELECTpermissions on all tables used in the query, which span the FI, SD, and potentially FSCM modules. - Date Range: It is critical to filter the query by a specific date range to ensure reasonable performance and data volume. We recommend starting with a period of 3 to 6 months. The date filter placeholders
'YYYYMMDD'must be set in multiple parts of the query. - Company Code Filter: The query is designed to be filtered by Company Code (
BUKRS). Analyzing one or a few company codes at a time is standard practice. - Document Type Configuration: The logic for identifying events like invoice corrections, write-offs, or sent documents depends on standard SAP configurations. You may need to adjust the query if your organization uses custom document types (
BLART), output types (KSCHL), or G/L accounts for these processes. - Performance Considerations: Executing this query against a live production SAP system can consume significant resources and may impact operational performance. It is strongly recommended to run large extractions during off-peak hours or against a dedicated reporting replica of the database.
a Sample Query sql
WITH InvoiceBase AS (
SELECT
VBRK.VBELN AS InvoiceNumber,
VBRK.FKART AS BillingDocumentType,
VBRK.KUNRG AS CustomerNumber,
VBRK.BUKRS AS CompanyCode,
VBRK.NETWR AS TotalInvoiceAmount,
VBRK.ERNAM AS CreatorName,
VBRK.ERDAT AS CreationDate,
VBRK.ERZET AS CreationTime
FROM VBRK
WHERE VBRK.ERDAT BETWEEN '20230101' AND '20231231' -- Filter by Invoice Creation Date
AND VBRK.BUKRS IN ('1000') -- Filter by Company Code
AND VBRK.FKART NOT IN ('S1', 'S2') -- Exclude cancelled invoices
)
-- 1. Invoice Generated
SELECT
ib.InvoiceNumber,
'Invoice Generated' AS ActivityName,
CAST(CONCAT(ib.CreationDate, ib.CreationTime) AS TIMESTAMP) AS EventTime,
ib.CreatorName AS UserName,
ib.BillingDocumentType,
ib.CustomerNumber,
ib.CompanyCode,
ib.TotalInvoiceAmount
FROM InvoiceBase ib
UNION ALL
-- 2. Invoice Parked
SELECT
SUBSTRING(b.AWKEY, 1, 10) AS InvoiceNumber,
'Invoice Parked' AS ActivityName,
CAST(CONCAT(b.CPUDT, b.CPUTM) AS TIMESTAMP) AS EventTime,
b.USNAM AS UserName,
ib.BillingDocumentType,
ib.CustomerNumber,
b.BUKRS AS CompanyCode,
ib.TotalInvoiceAmount
FROM BKPF b
JOIN InvoiceBase ib ON SUBSTRING(b.AWKEY, 1, 10) = ib.InvoiceNumber
WHERE b.AWTYP = 'VBRK' AND b.BSTAT = 'V' AND b.CPUDT BETWEEN '20230101' AND '20231231'
UNION ALL
-- 3. Invoice Posted
SELECT
SUBSTRING(b.AWKEY, 1, 10) AS InvoiceNumber,
'Invoice Posted' AS ActivityName,
CAST(CONCAT(b.CPUDT, b.CPUTM) AS TIMESTAMP) AS EventTime,
b.USNAM AS UserName,
ib.BillingDocumentType,
ib.CustomerNumber,
b.BUKRS AS CompanyCode,
ib.TotalInvoiceAmount
FROM BKPF b
JOIN InvoiceBase ib ON SUBSTRING(b.AWKEY, 1, 10) = ib.InvoiceNumber
WHERE b.AWTYP = 'VBRK' AND b.BSTAT = '' AND b.CPUDT BETWEEN '20230101' AND '20231231'
UNION ALL
-- 4. Invoice Approved (from Parked to Posted)
SELECT
SUBSTRING(h.OBJECTID, 4, 10) AS InvoiceNumber,
'Invoice Approved' as ActivityName,
CAST(CONCAT(h.UDATE, h.UTIME) AS TIMESTAMP) AS EventTime,
h.USERNAME AS UserName,
ib.BillingDocumentType,
ib.CustomerNumber,
ib.CompanyCode,
ib.TotalInvoiceAmount
FROM CDHDR h
JOIN CDPOS p ON h.MANDANT = p.MANDANT AND h.OBJECTCLAS = p.OBJECTCLAS AND h.OBJECTID = p.OBJECTID AND h.CHANGENR = p.CHANGENR
JOIN InvoiceBase ib ON SUBSTRING(h.OBJECTID, 4, 10) = ib.InvoiceNumber
WHERE h.OBJECTCLAS = 'BELEGV'
AND p.TABNAME = 'BKPF'
AND p.FNAME = 'BSTAT'
AND p.VALUE_OLD = 'V'
AND p.VALUE_NEW = ' '
AND h.UDATE BETWEEN '20230101' AND '20231231'
UNION ALL
-- 5. Invoice Sent To Customer
SELECT
n.OBJKY AS InvoiceNumber,
'Invoice Sent To Customer' AS ActivityName,
CAST(CONCAT(n.DATVR, n.UHRVR) AS TIMESTAMP) AS EventTime,
n.VSTAT AS UserName, -- User who processed is not directly available, using processing status as a proxy
ib.BillingDocumentType,
ib.CustomerNumber,
ib.CompanyCode,
ib.TotalInvoiceAmount
FROM NAST n
JOIN InvoiceBase ib ON n.OBJKY = ib.InvoiceNumber
WHERE n.KSCHL = '[Your Invoice Output Type]' -- E.g., 'RD00'
AND n.VSTAT = '1' -- Processed successfully
AND n.DATVR BETWEEN '20230101' AND '20231231'
UNION ALL
-- 6. Invoice Corrected (Reversed)
SELECT
SUBSTRING(orig_doc.AWKEY, 1, 10) AS InvoiceNumber,
'Invoice Corrected' AS ActivityName,
CAST(CONCAT(rev_doc.CPUDT, rev_doc.CPUTM) AS TIMESTAMP) AS EventTime,
rev_doc.USNAM AS UserName,
ib.BillingDocumentType,
ib.CustomerNumber,
rev_doc.BUKRS AS CompanyCode,
ib.TotalInvoiceAmount
FROM BKPF orig_doc
JOIN BKPF rev_doc ON orig_doc.STBLG = rev_doc.BELNR AND orig_doc.BUKRS = rev_doc.BUKRS AND orig_doc.GJAHR = rev_doc.STJAH
JOIN InvoiceBase ib ON SUBSTRING(orig_doc.AWKEY, 1, 10) = ib.InvoiceNumber
WHERE orig_doc.AWTYP = 'VBRK' AND orig_doc.STBLG IS NOT NULL AND rev_doc.CPUDT BETWEEN '20230101' AND '20231231'
UNION ALL
-- 7. Invoice Due Date Reached
SELECT
SUBSTRING(b.AWKEY, 1, 10) AS InvoiceNumber,
'Invoice Due Date Reached' AS ActivityName,
CAST(CONCAT(bs.ZFBDT, '000000') AS TIMESTAMP) AS EventTime,
'System' AS UserName,
ib.BillingDocumentType,
ib.CustomerNumber,
b.BUKRS AS CompanyCode,
ib.TotalInvoiceAmount
FROM BSEG bs
JOIN BKPF b ON bs.MANDT = b.MANDT AND bs.BUKRS = b.BUKRS AND bs.BELNR = b.BELNR AND bs.GJAHR = b.GJAHR
JOIN InvoiceBase ib ON SUBSTRING(b.AWKEY, 1, 10) = ib.InvoiceNumber
WHERE b.AWTYP = 'VBRK' AND bs.KOART = 'D' AND bs.ZFBDT BETWEEN '20230101' AND '20231231'
UNION ALL
-- 8. Payment Reminder Issued
SELECT
SUBSTRING(b.AWKEY, 1, 10) AS InvoiceNumber,
'Payment Reminder Issued' AS ActivityName,
CAST(CONCAT(h.LAUFD, '000000') AS TIMESTAMP) AS EventTime,
h.LAUFI AS UserName, -- Dunning Run ID
ib.BillingDocumentType,
ib.CustomerNumber,
d.BUKRS AS CompanyCode,
ib.TotalInvoiceAmount
FROM MHND d
JOIN MHNK h ON d.MANDT = h.MANDT AND d.LAUFD = h.LAUFD AND d.LAUFI = h.LAUFI
JOIN BKPF b ON d.MANDT = b.MANDT AND d.BUKRS = b.BUKRS AND d.BELNR = b.BELNR AND d.GJAHR = b.GJAHR
JOIN InvoiceBase ib ON SUBSTRING(b.AWKEY, 1, 10) = ib.InvoiceNumber
WHERE h.LAUFD BETWEEN '20230101' AND '20231231'
UNION ALL
-- 9. Dispute Case Created
SELECT
attr.ATTR_VALUE AS InvoiceNumber,
'Dispute Case Created' AS ActivityName,
sc.CREATE_TIME AS EventTime,
sc.CREATED_BY AS UserName,
ib.BillingDocumentType,
ib.CustomerNumber,
ib.CompanyCode,
ib.TotalInvoiceAmount
FROM SCMG_T_CASE_ATTR attr
JOIN SCASE sc ON attr.CASE_GUID = sc.CASE_GUID
JOIN InvoiceBase ib ON attr.ATTR_VALUE = ib.InvoiceNumber
WHERE attr.ATTR_NAME = '[Your Dispute Case Invoice Attribute]' -- e.g., 'INVOICE_ID'
AND CAST(sc.CREATE_TIME AS DATE) BETWEEN '20230101' AND '20231231'
UNION ALL
-- 10, 11, 12. Clearing Events (Payment, Clearing, Write-Off)
SELECT
InvoiceNumber,
ActivityName,
EventTime,
UserName,
BillingDocumentType,
CustomerNumber,
CompanyCode,
TotalInvoiceAmount
FROM (
SELECT
bsad.XBLNR AS InvoiceNumber,
CASE
WHEN clearing_item.HKONT = '[Your Bad Debt G/L Account]' THEN 'Invoice Written Off'
ELSE 'Customer Payment Received'
END AS ActivityName,
CAST(CONCAT(clearing_doc.CPUDT, clearing_doc.CPUTM) AS TIMESTAMP) AS EventTime,
clearing_doc.USNAM AS UserName,
ib.BillingDocumentType,
ib.CustomerNumber,
bsad.BUKRS AS CompanyCode,
ib.TotalInvoiceAmount
FROM BSAD bsad
JOIN InvoiceBase ib ON bsad.XBLNR = ib.InvoiceNumber
JOIN BKPF clearing_doc ON bsad.MANDT = clearing_doc.MANDT AND bsad.BUKRS = clearing_doc.BUKRS AND bsad.AUGBL = clearing_doc.BELNR AND bsad.AUGGJ = clearing_doc.GJAHR
LEFT JOIN BSEG clearing_item ON clearing_doc.MANDT = clearing_item.MANDT AND clearing_doc.BUKRS = clearing_item.BUKRS AND clearing_doc.BELNR = clearing_item.BELNR AND clearing_doc.GJAHR = clearing_item.GJAHR AND clearing_item.HKONT = '[Your Bad Debt G/L Account]' -- e.g. '148000'
WHERE bsad.AUGDT BETWEEN '20230101' AND '20231231' AND bsad.UMSKZ = ''
UNION ALL
SELECT
bsad.XBLNR AS InvoiceNumber,
'Payment Applied To Invoice' AS ActivityName,
CAST(CONCAT(bsad.AUGDT, '000000') AS TIMESTAMP) AS EventTime,
clearing_doc.USNAM AS UserName,
ib.BillingDocumentType,
ib.CustomerNumber,
bsad.BUKRS AS CompanyCode,
ib.TotalInvoiceAmount
FROM BSAD bsad
JOIN InvoiceBase ib ON bsad.XBLNR = ib.InvoiceNumber
JOIN BKPF clearing_doc ON bsad.MANDT = clearing_doc.MANDT AND bsad.BUKRS = clearing_doc.BUKRS AND bsad.AUGBL = clearing_doc.BELNR AND bsad.AUGGJ = clearing_doc.GJAHR
WHERE bsad.AUGDT BETWEEN '20230101' AND '20231231' AND bsad.UMSKZ = ''
UNION ALL
SELECT
bsad.XBLNR AS InvoiceNumber,
'Invoice Cleared' AS ActivityName,
CAST(CONCAT(bsad.AUGDT, '235959') AS TIMESTAMP) AS EventTime, -- Add time to separate from 'Payment Applied'
clearing_doc.USNAM AS UserName,
ib.BillingDocumentType,
ib.CustomerNumber,
bsad.BUKRS AS CompanyCode,
ib.TotalInvoiceAmount
FROM BSAD bsad
JOIN InvoiceBase ib ON bsad.XBLNR = ib.InvoiceNumber
JOIN BKPF clearing_doc ON bsad.MANDT = clearing_doc.MANDT AND bsad.BUKRS = clearing_doc.BUKRS AND bsad.AUGBL = clearing_doc.BELNR AND bsad.AUGGJ = clearing_doc.GJAHR
WHERE bsad.AUGDT BETWEEN '20230101' AND '20231231' AND bsad.UMSKZ = ''
) AS ClearingEventsSteps
- Prerequisites: Ensure you have a licensed ETL tool with a certified SAP connector (e.g., Informatica PowerCenter with SAP Connector, Talend with SAP Connector, etc.). Verify that you have SAP user credentials with the necessary authorizations to read from the required financial, sales, and system tables (BKPF, BSEG, VBRK, NAST, MHNK, UDM_CASE_ATTR00, CDHDR, CDPOS).
- Establish SAP Connection: In your ETL tool, create a new connection to your SAP ECC system. Configure the connection details, including application server, system number, client, user, and password. Test the connection to ensure it is successful.
- Define Data Sources: For each activity to be extracted, define the corresponding SAP table(s) as a data source in your ETL job. For example, add VBRK for invoice generation, BKPF for posting events, and NAST for customer communication.
- Build Extraction Logic for Each Activity: Create a separate data flow or transformation for each of the 13 required activities. In each flow, apply filters to select the relevant records. For instance, filter by Company Code (BUKRS), document type (BLART), and a specific date range (e.g., creation date ERDAT).
- Map Fields and Transform Data: In each data flow, map the source SAP table fields to the target event log structure: InvoiceNumber, ActivityName, EventTime, UserName, and other recommended attributes. Use transformation logic to hardcode the 'ActivityName' for each flow and to correctly format dates and timestamps.
- Handle Complex Activities: For calculated events like 'Invoice Due Date Reached', use the Baseline Payment Date (ZFBDT) and payment terms logic to calculate the due date, or simply pull the Net Due Date (NETDT) from BSEG. For events derived from change logs like 'Invoice Approved', you may need to join tables like BKPF and CDHDR/CDPOS based on the document number and date.
- Combine Activity Data: Use a 'Union' or 'Merge' transformation in your ETL tool to combine the outputs of all 13 individual data flows into a single dataset. Ensure the column names and data types are consistent across all flows before the union.
- Configure Target Destination: Define the final output destination for your event log. This could be a flat file (CSV), a database table, or a direct connection to a staging area.
- Set Extraction Schedule: Configure the date range parameters for the extraction. For the initial load, you might extract 6-12 months of data. For subsequent delta loads, configure the job to extract data from the last run date.
- Execute and Export: Run the ETL job. Once complete, inspect the output file to ensure it meets the required format. The final output should be a single CSV file with each row representing a unique event, ready for upload into ProcessMind.
Configuration
- SAP Connection: An application server connection to the target SAP ECC system is required. The SAP user needs RFC access and authorizations for tables like VBRK, BKPF, BSEG, NAST, and others specified in the query.
- ETL Tool License: A valid license for the commercial ETL tool and its specific SAP connector is mandatory.
- Date Range: It is recommended to extract data for a period of 3 to 6 months to ensure a representative sample for analysis without causing excessive system load. Use a configurable parameter for the start and end dates.
- Key Filters: Always filter by Company Code (BUKRS) to limit the scope of the extraction. It is also critical to filter on the relevant Billing Document Types (VBRK-FKART) and Accounting Document Types (BKPF-BLART) to include only standard invoices and exclude other document types like credit memos or internal documents.
- Performance: Extracting from large tables like BSEG can be slow. Use selective filters, avoid extracting unnecessary fields, and schedule the extraction during off-peak hours to minimize the performance impact on the SAP source system.
a Sample Query config
// ETL Data Extraction Logic for SAP Order-to-Cash Invoicing
// This represents the configuration logic within a graphical ETL tool.
// == Global Parameters ==
// $StartDate: '[Start Date]' (e.g., '2023-01-01')
// $EndDate: '[End Date]' (e.g., '2023-06-30')
// $CompanyCodes: '[Company Code(s)]' (e.g., '1000', '2000')
// $BillingDocTypes: '[Billing Document Type(s)]' (e.g., 'F1', 'F2')
// == Source 1: Invoice Generated ==
// Tables: VBRK
DATA_SOURCE generated_invoices FROM VBRK WHERE
ERDAT >= $StartDate AND ERDAT <= $EndDate
AND BUKRS IN ($CompanyCodes)
AND FKART IN ($BillingDocTypes)
MAP {
InvoiceNumber: VBELN,
ActivityName: 'Invoice Generated',
EventTime: ERDAT + ERZET, // Combine date and time
UserName: ERNAM,
BillingDocumentType: FKART,
CustomerNumber: KUNAG,
CompanyCode: BUKRS,
TotalInvoiceAmount: NETWR
}
// == Source 2: Invoice Posted ==
// Tables: BKPF joined with VBRK
DATA_SOURCE posted_invoices FROM BKPF as A
INNER JOIN VBRK as B ON (A.AWKEY = B.VBELN AND A.AWTYP = 'VBRK')
WHERE A.BUDAT >= $StartDate AND A.BUDAT <= $EndDate
AND A.BUKRS IN ($CompanyCodes)
AND A.BSTAT = ' '
MAP {
InvoiceNumber: B.VBELN,
ActivityName: 'Invoice Posted',
EventTime: A.BUDAT + A.CPUTM, // Posting date and entry time
UserName: A.USNAM,
BillingDocumentType: B.FKART,
CustomerNumber: B.KUNAG,
CompanyCode: A.BUKRS,
TotalInvoiceAmount: B.NETWR
}
// == Source 3: Invoice Parked ==
// Tables: BKPF joined with VBRK
DATA_SOURCE parked_invoices FROM BKPF as A
INNER JOIN VBRK as B ON (A.AWKEY = B.VBELN AND A.AWTYP = 'VBRK')
WHERE A.CPUDT >= $StartDate AND A.CPUDT <= $EndDate
AND A.BUKRS IN ($CompanyCodes)
AND A.BSTAT = 'V'
MAP {
InvoiceNumber: B.VBELN,
ActivityName: 'Invoice Parked',
EventTime: A.CPUDT + A.CPUTM,
UserName: A.USNAM,
BillingDocumentType: B.FKART,
CustomerNumber: B.KUNAG,
CompanyCode: A.BUKRS,
TotalInvoiceAmount: B.NETWR
}
// == Source 4: Invoice Approved (Transition from Parked to Posted) ==
// Tables: BKPF joined with VBRK
DATA_SOURCE approved_invoices FROM BKPF as A
INNER JOIN VBRK as B ON (A.AWKEY = B.VBELN AND A.AWTYP = 'VBRK')
WHERE A.BUDAT >= $StartDate AND A.BUDAT <= $EndDate
AND A.BUKRS IN ($CompanyCodes)
AND A.BSTAT = ' '
AND EXISTS (SELECT 1 FROM VBELEGV C WHERE C.BELNR = A.BELNR) // Check if it was ever parked
MAP {
InvoiceNumber: B.VBELN,
ActivityName: 'Invoice Approved',
EventTime: A.BUDAT + A.CPUTM, // Use posting date as approval date
UserName: A.USNAM,
BillingDocumentType: B.FKART,
CustomerNumber: B.KUNAG,
CompanyCode: A.BUKRS,
TotalInvoiceAmount: B.NETWR
}
// == Source 5: Invoice Sent To Customer ==
// Tables: NAST joined with VBRK
DATA_SOURCE sent_invoices FROM NAST as A
INNER JOIN VBRK as B ON (A.OBJKY = B.VBELN)
WHERE A.ERDAT >= $StartDate AND A.ERDAT <= $EndDate
AND B.BUKRS IN ($CompanyCodes)
AND A.VSTAT = '1' // Successfully processed
MAP {
InvoiceNumber: B.VBELN,
ActivityName: 'Invoice Sent To Customer',
EventTime: A.ERDAT + A.ERUHR,
UserName: A.USNAM,
BillingDocumentType: B.FKART,
CustomerNumber: B.KUNAG,
CompanyCode: B.BUKRS,
TotalInvoiceAmount: B.NETWR
}
// == Source 6: Invoice Corrected (Reversed) ==
// Tables: VBRK (for the reversal document)
DATA_SOURCE corrected_invoices FROM VBRK as A
WHERE A.ERDAT >= $StartDate AND A.ERDAT <= $EndDate
AND A.BUKRS IN ($CompanyCodes)
AND A.SFAKN <> '' // SFAKN is the original cancelled invoice
MAP {
InvoiceNumber: A.SFAKN, // Case ID is the original invoice
ActivityName: 'Invoice Corrected',
EventTime: A.ERDAT + A.ERZET,
UserName: A.ERNAM,
BillingDocumentType: A.FKART,
CustomerNumber: A.KUNAG,
CompanyCode: A.BUKRS,
TotalInvoiceAmount: NULL // Amount belongs to the reversal doc, not original
}
// == Source 7: Invoice Due Date Reached ==
// Tables: BSEG joined with VBRK
DATA_SOURCE due_invoices FROM BSEG as A
INNER JOIN BKPF as H ON (A.BUKRS = H.BUKRS AND A.BELNR = H.BELNR AND A.GJAHR = H.GJAHR)
INNER JOIN VBRK as B ON (H.AWKEY = B.VBELN AND H.AWTYP = 'VBRK')
WHERE A.NETDT >= $StartDate AND A.NETDT <= $EndDate
AND A.BUKRS IN ($CompanyCodes)
AND A.KOART = 'D' // Customer line item
MAP {
InvoiceNumber: B.VBELN,
ActivityName: 'Invoice Due Date Reached',
EventTime: A.NETDT, // Net due date
UserName: 'System',
BillingDocumentType: B.FKART,
CustomerNumber: B.KUNAG,
CompanyCode: A.BUKRS,
TotalInvoiceAmount: B.NETWR
}
// == Source 8: Payment Reminder Issued ==
// Tables: MHNK, MHND, VBRK
DATA_SOURCE reminders FROM MHNK as A
INNER JOIN MHND as D ON (A.LAUFD = D.LAUFD AND A.LAUFI = D.LAUFI)
INNER JOIN VBRK as B ON (SUBSTRING(D.XBLNR, 1, 10) = B.VBELN) // XBLNR may need parsing
WHERE A.LAUFD >= $StartDate AND A.LAUFD <= $EndDate
AND D.BUKRS IN ($CompanyCodes)
MAP {
InvoiceNumber: B.VBELN,
ActivityName: 'Payment Reminder Issued',
EventTime: A.LAUFD, // Dunning date
UserName: A.IDAPS,
BillingDocumentType: B.FKART,
CustomerNumber: B.KUNAG,
CompanyCode: D.BUKRS,
TotalInvoiceAmount: B.NETWR
}
// == Source 9: Dispute Case Created ==
// Tables: UDM_CASE_ATTR00
DATA_SOURCE disputes FROM UDM_CASE_ATTR00 as A
WHERE A.CREATE_TIMESTAMP >= $StartDate // Timestamp format may vary
AND A.FIN_COMP_CODE IN ($CompanyCodes)
AND A.PROCESS = 'FIN_FSCM_DIS'
MAP {
InvoiceNumber: A.BILL_DOC_ID,
ActivityName: 'Dispute Case Created',
EventTime: A.CREATE_TIMESTAMP,
UserName: A.CREATE_USER,
BillingDocumentType: NULL,
CustomerNumber: A.BP_NUMBER,
CompanyCode: A.FIN_COMP_CODE,
TotalInvoiceAmount: A.DISPUTED_AMOUNT
}
// == Source 10: Customer Payment Received ==
// Tables: BKPF
DATA_SOURCE payments FROM BKPF
WHERE BUDAT >= $StartDate AND BUDAT <= $EndDate
AND BUKRS IN ($CompanyCodes)
AND BLART = 'DZ' // Example for Customer Payment
MAP {
InvoiceNumber: NULL, // Invoice not yet known
ActivityName: 'Customer Payment Received',
EventTime: BUDAT + CPUTM,
UserName: USNAM,
BillingDocumentType: NULL,
CustomerNumber: NULL, // Requires join to BSEG to get customer
CompanyCode: BUKRS,
TotalInvoiceAmount: NULL
}
// == Source 11 & 12: Payment Applied To Invoice & Invoice Cleared ==
// Tables: BSEG joined with VBRK
DATA_SOURCE cleared_items FROM BSEG as A
INNER JOIN BKPF as H ON (A.BUKRS = H.BUKRS AND A.BELNR = H.BELNR AND A.GJAHR = H.GJAHR)
INNER JOIN VBRK as B ON (H.AWKEY = B.VBELN AND H.AWTYP = 'VBRK')
WHERE A.AUGDT >= $StartDate AND A.AUGDT <= $EndDate
AND A.BUKRS IN ($CompanyCodes)
AND A.AUGBL <> ''
// Generate two records from this source
MAP {
InvoiceNumber: B.VBELN,
ActivityName: 'Payment Applied To Invoice',
EventTime: A.AUGDT, // Clearing Date
UserName: H.USNAM, // User from header of original invoice doc
BillingDocumentType: B.FKART,
CustomerNumber: B.KUNAG,
CompanyCode: A.BUKRS,
TotalInvoiceAmount: B.NETWR
}
UNION WITH {
InvoiceNumber: B.VBELN,
ActivityName: 'Invoice Cleared',
EventTime: A.AUGDT, // Clearing Date
UserName: H.USNAM,
BillingDocumentType: B.FKART,
CustomerNumber: B.KUNAG,
CompanyCode: A.BUKRS,
TotalInvoiceAmount: B.NETWR
}
// == Source 13: Invoice Written Off ==
// Tables: BSEG (for the invoice line) and BKPF (for clearing doc type)
DATA_SOURCE written_off FROM BSEG as A
INNER JOIN BKPF as H ON (A.BUKRS = H.BUKRS AND A.BELNR = H.BELNR AND A.GJAHR = H.GJAHR)
INNER JOIN VBRK as B ON (H.AWKEY = B.VBELN AND H.AWTYP = 'VBRK')
INNER JOIN BKPF as C ON (A.AUGBL = C.BELNR AND A.BUKRS = C.BUKRS AND A.AUGGJ = C.GJAHR)
WHERE A.AUGDT >= $StartDate AND A.AUGDT <= $EndDate
AND A.BUKRS IN ($CompanyCodes)
AND C.BLART = '[Your Write-Off Document Type]' // e.g., 'AB'
MAP {
InvoiceNumber: B.VBELN,
ActivityName: 'Invoice Written Off',
EventTime: A.AUGDT,
UserName: C.USNAM, // User who posted the write-off
BillingDocumentType: B.FKART,
CustomerNumber: B.KUNAG,
CompanyCode: A.BUKRS,
TotalInvoiceAmount: B.NETWR
}
// == Final Union of all sources ==
OUTPUT generated_invoices
UNION ALL posted_invoices
UNION ALL parked_invoices
UNION ALL approved_invoices
UNION ALL sent_invoices
UNION ALL corrected_invoices
UNION ALL due_invoices
UNION ALL reminders
UNION ALL disputes
UNION ALL payments
UNION ALL cleared_items
UNION ALL written_off