> Continuous Process Improvement > Credit Management & Collections > SAP ECC > Data Template

Your Credit Management & Collections Data Template

SAP ECC

Credit Management & Collections Attributes (21) Credit Management & Collections Activities (14) Extraction Guides (4)

Your Credit Management & Collections Data Template

This template provides a clear overview of the essential data attributes and activities required to effectively analyze your Credit Management & Collections process. It also includes practical guidance to help you extract this critical information from your SAP ECC system. Leverage this resource to ensure a smooth and accurate data collection phase.

Recommended attributes to collect
Key activities to track
Extraction guidance for SAP ECC

New to event logs? Learn how to create a process mining event log.

Download Template

Credit Management & Collections Attributes

These are the recommended data fields to include in your event log for comprehensive credit management and collections analysis in SAP ECC.

3 Required 8 Recommended 10 Optional

	Name	Description
	Activity Name ActivityName	The name of the business activity or event that occurred at a specific point in the invoice lifecycle.
Description This attribute describes a specific step or event within the credit management and collections process, such as 'Invoice Posted', 'Dunning Run Executed', or 'Incoming Payment Document Posted'. These activities form the nodes of the process map. By analyzing the sequence, frequency, and duration between these activities, businesses can visualize their actual process flow, identify deviations from standard procedures, and pinpoint bottlenecks. For instance, analyzing the path after 'Dunning Run Executed' can reveal the effectiveness of the dunning strategy. Why it matters Activities are the building blocks of the process map, enabling the visualization and analysis of the process flow, variants, and exceptions. Where to get This is a derived attribute, typically constructed by mapping transaction codes (TCODE), document types (BLART), or specific field changes from various SAP tables (e.g., BKPF, BSID, MHNK, UDM_CASE) to user-friendly activity names. Examples Invoice PostedDunning Run ExecutedPromise To Pay CreatedIncoming Payment Document PostedInvoice Cleared
	Event Time EventTime	The timestamp indicating when a specific activity or event occurred.
Description The Event Time records the precise date and time of each activity in the process. It is the chronological backbone of the event log, allowing for the ordering of activities and the calculation of durations between them. In analysis, this timestamp is fundamental for calculating all time-based KPIs, such as Dispute Resolution Cycle Time, Payment Posting Latency, and Average Days from Due Date to Payment. It enables the discovery of bottlenecks by highlighting long waiting times between consecutive steps. Why it matters This attribute is essential for sequencing events correctly and calculating all performance metrics related to time, such as cycle times and durations. Where to get Sourced from various date and time fields in SAP tables, such as BUDAT (Posting Date) or CPUDT/CPUTM (Document Entry Date/Time) in BKPF, or event-specific tables. Examples 2023-01-15T09:30:00Z2023-02-10T14:00:00Z2023-02-28T11:25:10Z
	Invoice Number InvoiceNumber	The unique identifier for the customer invoice, which serves as the primary case identifier for the credit management process.
Description The Invoice Number, known as Belegnummer (BELNR) in SAP, uniquely identifies each accounts receivable document. In process mining, this number is crucial as it links all related activities, from posting and dunning to eventual payment or write-off, into a single, cohesive case. Analyzing the process with the invoice number as the case identifier allows for a complete view of the invoice lifecycle. This helps in tracking key metrics like Days Sales Outstanding (DSO), identifying bottlenecks in the collection process, and understanding the effectiveness of different collection strategies for specific invoices. Why it matters This is the essential key that connects every event in an invoice's journey, making it possible to trace and analyze the end-to-end credit-to-cash process. Where to get Found in various financial accounting tables in SAP ECC, primarily in BKPF (Accounting Document Header) and BSEG (Accounting Document Segment) as field BELNR. Examples 190000000119000000451900000102
	Collector Sachp	The accounting clerk or collection agent assigned to the customer account.
Description This attribute identifies the person or group responsible for managing collections for a specific customer account. In SAP, this is often the Accounting Clerk (SACHP) defined in the customer master record. This dimension is crucial for performance management within the collections team. It allows for creating dashboards like 'Invoice Aging Overview' filtered by collector, helping to manage workloads and compare the effectiveness of different collectors or teams. It answers questions about which collectors are most successful at resolving overdue invoices. Why it matters Enables performance analysis and workload management of the collections team by attributing cases and outcomes to specific individuals or groups. Where to get Typically found in the customer master company code data (table KNB1, field SACHP). Examples J. SmithTeam AINTL-COLL
	Customer Number Kunnr	The unique identifier for the customer.
Description The Customer Number (KUNNR in SAP) is the unique key for a customer account. It links a transaction to a specific customer's master data, including their payment history, credit information, and contact details. In process mining, this attribute is essential for segmenting analysis. It allows you to compare process performance across different customers, identify chronically late payers, and analyze how collection strategies differ for strategic versus non-strategic accounts. It is fundamental for dashboards like Invoice Aging and Payment Terms Compliance. Why it matters Enables customer-centric analysis, helping to identify behavioral patterns for specific customers or customer groups and tailor collection strategies. Where to get Standard field KUNNR in customer line item tables (BSID, BSAD) and document header tables (BKPF, if populated). Examples 10002050CUST-7890
	Customer Segment CustomerSegment	The classification of the customer, for example, based on size, industry, or strategic importance.
Description Customer Segment is a classification used to group customers with similar characteristics. This is often derived from the Customer Account Group (KTOKD) in SAP or other custom fields in the customer master data. Segmenting the process analysis by this attribute provides powerful insights. It can reveal whether certain segments have longer payment cycles, higher dispute rates, or respond better to specific collection activities. This information is vital for optimizing collection strategies and tailoring customer interactions. Why it matters Allows for targeted analysis to see how the process performs for different types of customers, enabling tailored strategies and resource allocation. Where to get Often derived from the Customer Account Group (KTOKD) field in customer master table KNA1, or other custom fields. Examples Key AccountsSmall & Medium BusinessGovernmentInternal
	Days Overdue DaysOverdue	The calculated number of days an invoice is past its due date.
Description This metric calculates the number of days between an invoice's payment due date and the date it was cleared. For open invoices, it is calculated based on the current date. Days Overdue is a fundamental metric for all accounts receivable analysis. It is the primary measure for the 'Invoice Aging Overview' dashboard and is used to prioritize collection activities. Trend analysis of this metric at an aggregate level can indicate the overall health of a company's receivables. Why it matters This is a core performance metric that directly quantifies payment delays and is used to prioritize collection efforts and measure process health. Where to get Calculated attribute. The logic is: (Clearing Date or Current Date) - Net Due Date. Examples 0153295
	Due Date NetDueDate	The date on which the invoice payment is contractually due.
Description The Due Date is the deadline by which a customer is expected to pay an invoice. It is calculated based on the invoice baseline date and the payment terms. In SAP, the net due date is often available in field FAEDT. This date is fundamental for credit management. It is the baseline against which timeliness is measured. It is used to calculate KPIs like 'Average Days from Due Date to Payment' and to trigger collection activities when passed. Analyzing delays relative to this date is a core activity in collections analysis. Why it matters This is the primary benchmark for measuring payment timeliness and is essential for calculating overdue days and related KPIs. Where to get This date is often directly available in customer line item tables like BSID as FAEDT (Net Due Date). It can also be calculated from the baseline date (ZFBDT) and payment terms (ZTERM). Examples 2023-02-142023-03-312023-04-15
	Dunning Level Mahns	The highest dunning (reminder) level that has been reached for the invoice.
Description The Dunning Level (MAHNS in SAP) indicates how many reminder notices have been sent for an overdue invoice, corresponding to the intensity of the dunning procedure. This attribute is key to evaluating the dunning process. It is used directly in the 'Dunning Effectiveness by Level' dashboard to measure what percentage of invoices are paid after each dunning notice is sent. This analysis helps to refine the dunning strategy, for example, by changing the timing or content of notices at ineffective levels. Why it matters Directly measures the intensity of collection efforts, which is crucial for analyzing the effectiveness of the dunning strategy. Where to get Found on the customer line item in table BSID as field MAHNS. Dunning run history is in tables MHNK (header) and MHND (data). Examples 1234
	Invoice Amount Dmbtr	The total value of the invoice in the local currency.
Description This attribute represents the total monetary value of the invoice. In SAP, this is often stored in the DMBTR field for amount in local currency. Invoice Amount is a critical dimension for analysis. It allows for prioritizing collection efforts on high-value invoices and understanding if there is a correlation between invoice value and payment delays or dispute occurrences. It can be used to filter the process map to focus only on invoices above a certain threshold. Why it matters Provides financial context to the process, enabling value-based analysis and prioritization of collection efforts on high-value items. Where to get Standard field DMBTR (Amount in Local Currency) in tables like BSEG, BSID, and BSAD. Examples 1500.0012500.50750.25
	User Name UserName	The user ID of the person who executed the activity.
Description This attribute identifies the specific user responsible for an event, such as posting an invoice or creating a dispute case. In SAP, this is often stored in fields like ERNAM (Created by) or USNAM. Analyzing by user helps to understand workload distribution, identify training needs, and detect potential compliance issues. For example, it can reveal if certain users are consistently associated with process deviations or delays, or if top performers follow a more efficient process variant. Why it matters It enables analysis of human performance and behavior within the process, helping to identify top performers, training opportunities, and workload imbalances. Where to get Typically found in header tables like BKPF as USNAM (User name) or ERNAM (Name of Person who Created the Object). Examples SMITHJRDOECFO-ADMIN
	Clearing Document Augbl	The document number that cleared the invoice, typically a payment or credit memo.
Description The Clearing Document number (AUGBL in SAP) links an open item, like an invoice, to the document that settled it. When an invoice is paid, the payment document number is stored as the clearing document for that invoice. This field is technically crucial for confirming that an invoice has been cleared and for linking it to the specific payment or credit memo event. It is the basis for identifying the 'Invoice Cleared' activity and for ensuring the end-to-end process is correctly captured. Why it matters Provides the explicit link between an invoice and its settlement document, which is critical for accurately modeling clearing events. Where to get Standard field AUGBL in customer line item tables BSID (while open, it's blank) and BSAD (after clearing, it's populated). Examples 140000000114000000551400000120
	Company Code Bukrs	The identifier for the legal entity (company code) to which the invoice belongs.
Description The Company Code (BUKRS in SAP) represents an independent legal entity for which financial statements are created. It is a fundamental organizational unit in SAP Financials. This attribute is essential for filtering and comparing process performance across different legal entities within a corporation. It allows for analysis to see if collection processes are standardized or if there are significant variations in performance, for example, in DSO or dispute rates, between different company codes. Why it matters Allows for process analysis to be segmented by legal entity, which is critical for large, multinational organizations to compare performance. Where to get Standard field BUKRS in nearly all financial tables, including BKPF, BSEG, BSID, and BSAD. Examples 10002000US01
	Credit Limit Klimk	The total credit limit amount assigned to the customer.
Description The Credit Limit (KLIMK in SAP) is the maximum amount of credit extended to a customer account. It is a key element of credit risk management. This attribute is essential for the 'Credit Limit Accuracy vs. Bad Debt' dashboard. By analyzing the relationship between the assigned credit limit and the occurrence of write-offs, a company can assess the effectiveness of its credit policies. It also supports KPIs like 'Credit Limit Revision Rate' to see if initial assessments are accurate. Why it matters Provides context on credit risk, allowing for analysis of whether credit policies are effective at preventing bad debt without stifling sales. Where to get Found in the central credit management data for a customer in table KNKK, field KLIMK. Examples 10000.0050000.00250000.00
	Dispute Case ID DisputeCaseId	The unique identifier for a dispute case linked to the invoice.
Description When a customer disputes an invoice, a formal dispute case may be created in SAP's Dispute Management module. This ID uniquely identifies that case. Having this identifier allows for a detailed analysis of the dispute resolution process. It is essential for calculating the 'Dispute Resolution Cycle Time' KPI and for understanding why disputes are created, how they are handled, and what the common outcomes are. It helps isolate the sub-process for disputes from the standard collection flow. Why it matters Links collection activities to formal dispute cases, enabling a focused analysis of the dispute resolution process efficiency and root causes. Where to get Found in SAP Dispute Management tables, such as UDM_CASE_ATTR00. Requires the use of the SAP Dispute Management module. Examples 400000000021400000000157400000000305
	Document Type Blart	The type of financial document, such as an invoice, credit memo, or payment.
Description The Document Type (BLART in SAP) classifies accounting documents. For example, 'RV' might be a customer invoice, 'DZ' a customer payment, and 'DG' a credit memo. While activities are derived for process mining, the original document type provides important context and can be used for verification or for more detailed financial analysis. It helps in understanding the nature of the transactions being processed and can be used to filter the analysis to focus only on specific types of documents, like customer invoices. Why it matters Provides financial context by classifying transactions, which can be used to filter analysis or to validate the derived activity names. Where to get Standard field BLART in the document header table BKPF. Examples RVDZDGAB
	Is Written Off IsWrittenOff	A boolean flag that indicates if the invoice was ultimately written off as bad debt.
Description This is a derived flag, typically set to true if an invoice is cleared using a specific reason code or document type that signifies a write-off. It identifies cases that represent a financial loss. This attribute is essential for calculating the 'Bad Debt Write-Off Rate' KPI and for related dashboards. It allows for root cause analysis to understand the characteristics of invoices and customers that are frequently written off, which can help improve credit policies and collection effectiveness. Why it matters Identifies process outcomes that result in financial loss, enabling analysis to find root causes of bad debt and improve credit policy. Where to get This is a derived attribute. The logic is typically based on identifying specific clearing reason codes (BSEG-RSTGR) or document types (BKPF-BLART) used for write-offs. Examples truefalse
	Last Data Refresh LastDataRefreshTimestamp	The timestamp indicating when the data was last extracted or updated in the process mining tool.
Description This attribute records the date and time of the most recent data load. It provides transparency to business users about the freshness of the data they are analyzing. When reviewing dashboards and analyses, this timestamp helps users understand if the data includes the very latest transactions or if there is a known lag. It is a critical piece of metadata for building trust in the analytical results. Why it matters Informs users about the timeliness of the data, which is crucial for making decisions based on the most current process information available. Where to get This value is generated and stored by the data extraction and loading (ETL) pipeline when data is refreshed. Examples 2023-03-01T02:00:00Z2023-03-02T02:00:00Z2023-03-03T02:00:00Z
	Payment Terms Zterm	The code for the payment terms agreed upon with the customer.
Description The Payment Terms code (ZTERM in SAP) defines the conditions for payment, such as the due date and any available discounts for early payment. This is typically set in the customer master data and copied to invoices. Analyzing by payment terms helps to understand how different terms impact payment behavior. The 'Payment Terms Compliance & Impact' dashboard, for example, visualizes how adherence to terms varies and its effect on overdue days. This can inform decisions on which payment terms to offer to different customer segments. Why it matters Explains the contractual agreement for payment and helps analyze if certain terms lead to better payment performance or more disputes. Where to get Standard field ZTERM, found in customer master data (KNB1) and financial document tables (BSEG). Examples 0001NT30ZD60
	Risk Category Ctlpc	A classification of the customer's credit risk.
Description The Risk Category (CTLPC in SAP) groups customers based on their creditworthiness and payment history. This classification is used to drive automated credit checks and to guide collection strategies. Analyzing the process by Risk Category can provide deep insights. It can show if high-risk customers follow different process paths or have significantly longer payment cycles. This information is valuable for validating the accuracy of the risk classification and for tailoring collection intensity based on risk. Why it matters Allows for risk-based analysis of the collections process, helping to validate risk models and tailor collection strategies appropriately. Where to get Found in the central credit management data for a customer in table KNKK, field CTLPC. Examples 001002HIGH-RISK
	Source System ID SourceSystemId	The identifier of the source system from which the data was extracted.
Description This attribute specifies the originating system, for example, the specific SAP ECC instance like 'ECCPRD100'. This is important in environments with multiple ERP systems or when integrating data from different sources. It allows for filtering and comparing processes across different systems or regions. This ensures data lineage is clear and helps in troubleshooting data extraction issues. Why it matters Provides crucial context about data origin, especially in complex IT landscapes, ensuring data traceability and enabling system-specific analysis. Where to get Typically added during the data extraction process. In SAP, the logical system name (LOGSYS) can be used. Examples SAPECC_PROD_100ECC_EU_200US_FIN_ERP

Required Recommended Optional

Credit Management & Collections Activities

These are the key process steps and milestones to capture in your event log for accurate process discovery and bottleneck identification.

5 Recommended 9 Optional

	Activity	Description
	Dunning Run Executed	This activity represents the execution of the automatic dunning program for an overdue invoice. The system logs the dunning level, date, and other details for each invoice included in a dunning run.
Why it matters Tracking dunning activities is essential for evaluating the effectiveness of the collections strategy. It helps determine which dunning levels are most successful at prompting payment and identifies non-responsive customers. Where to get This is an explicit event. Details of dunning runs are stored in the dunning data tables, primarily MHNK (Dunning data) which contains the run date (LAUFD) and dunning level (MAHNS) for each dunned invoice. Capture Extract records from the MHNK table, linking the company code, account, and dunning date. Event type explicit
	Invoice Cleared	This event marks the successful closure of an invoice, typically after full payment has been received and applied. It is inferred when the invoice line item moves from the open items table (BSID) to the cleared items table (BSAD).
Why it matters This is the primary 'good' end event for the process. The time to reach this activity is a core component of DSO. Analyzing paths that lead here helps identify best practices. Where to get This is an inferred event. The clearing is identified by the presence of a Clearing Document (AUGBL) and Clearing Date (AUGDT) for the invoice line item, which is found in the cleared items table BSAD. Capture Use the clearing date (BSAD-AUGDT) for the specific invoice line item as the event timestamp. Event type inferred
	Invoice Posted	Represents the creation of an accounts receivable invoice document in the financial accounting module. This event is explicitly captured when a billing document from Sales and Distribution (SD) is released to accounting or an FI invoice is entered directly, creating entries in tables BKPF and BSEG.
Why it matters This is the primary start event for the invoice lifecycle. Analyzing the time from this point to payment is crucial for measuring Days Sales Outstanding (DSO) and overall process efficiency. Where to get This is an explicit event recorded upon the creation of a financial document. The event timestamp can be retrieved from the document header table BKPF (field CPUDT or BKTXT) for the corresponding invoice document number (BELNR). Capture Identify creation events for FI documents with relevant document types (e.g., 'RV', 'DR') in table BKPF. Event type explicit
	Invoice Written Off	Represents the decision to absorb an unpaid invoice as a loss, classifying it as bad debt. This is captured through a specific financial posting that clears the original invoice and moves the amount to a bad debt account.
Why it matters This is the primary 'bad' end event, signifying a failure in the credit or collections process and a direct financial loss. Analyzing these cases is critical for improving credit policies and collection strategies. Where to get This is typically an explicit posting or an inferred event based on the clearing transaction. The clearing is done using a specific transaction code and reason code that indicates a write-off. The clearing document can be analyzed to confirm the write-off. Capture Identify clearing documents where a specific reason code (BSEG-RSTGR) for write-offs is used, or where the offsetting entry is posted to a bad debt expense account. Event type inferred
	Payment Due Date Passed	A calculated event that signifies the invoice has officially become overdue. This is not an explicit system event but is derived by comparing the invoice's net due date with the current date or the timestamp of a subsequent activity.
Why it matters This event marks the transition from standard invoicing to the collections process. It is the trigger for calculating overdue days and initiating dunning procedures, forming the basis for aging reports. Where to get This is a calculated event. It is derived by comparing the Net Due Date (BSID-NETDT) of the open invoice line item with the system date or another event's timestamp. Capture Calculated by comparing the invoice net due date (BSID-NETDT) against a timeline. Event type calculated
	Collection Contact Logged	A collections agent has made contact with the customer regarding an overdue invoice, for example, by phone or email. This activity is typically logged manually by the collector in the system.
Why it matters This activity measures the manual effort of the collections team. Analyzing the frequency and timing of contacts against subsequent payments helps determine the effectiveness of collector actions. Where to get If using SAP FSCM Collections Management, this is logged as a 'Customer Contact'. The details are stored in tables related to the collections worklist and contact history, often linked to UDM_CASE. Capture Extract customer contact logs from the relevant FSCM Collections Management tables. Event type explicit
	Credit Memo Posted	Represents the creation of a credit memo document against a customer account, often to correct a billing error or resolve a dispute. This document is explicitly created in the FI module.
Why it matters Credit memos are a direct result of process failures upstream, such as incorrect pricing or shipping. Analyzing their frequency and root causes is vital for process improvement and reducing revenue leakage. Where to get This is an explicit event captured upon the creation of a financial document with a credit memo document type (e.g., 'DG'). The event timestamp can be retrieved from the document header table BKPF. Capture Identify creation events for FI documents with credit memo document types (e.g., 'DG', 'G2') in table BKPF. Event type explicit
	Dispute Case Created	Represents the formal registration of a customer dispute related to an invoice, such as a price or quantity discrepancy. This is an explicit event logged within the SAP FSCM Dispute Management module.
Why it matters Disputes freeze the payment process and require internal resources to resolve. Tracking dispute creation is the first step in analyzing resolution time, root causes, and their impact on DSO. Where to get If using SAP FSCM Dispute Management, this is an explicit event. The creation of a dispute case is logged in tables such as UDM_CASE or SCMG_T_CASE_ATTR, with a creation timestamp. Capture Extract the creation date and time from the case management tables (e.g., UDM_CASE) for cases linked to the invoice. Event type explicit
	Dispute Case Resolved	The dispute associated with the invoice has been investigated and a resolution has been reached. This is typically captured by a status change on the dispute case in SAP FSCM Dispute Management.
Why it matters The resolution of a dispute unblocks the payment process. Measuring the time between dispute creation and resolution is a key KPI for identifying inefficiencies in the dispute handling process. Where to get This is an inferred event from a status change in the dispute case. The change logs (CDHDR/CDPOS) for the status field of the dispute case in tables like UDM_CASE provide the timestamp. Capture Identify the timestamp when the dispute case status is changed to 'Closed' or 'Resolved' by analyzing change logs. Event type inferred
	Incoming Payment Document Posted	Represents the initial recording of a payment from a customer into the system, often before it's applied to specific invoices. This is an explicit event creating a payment document, for instance a DZ document type.
Why it matters This marks the receipt of cash. The time lag between this event and the final 'Invoice Cleared' event represents the cash application process, which can be a significant bottleneck. Where to get This is an explicit event. It is captured from the creation of a payment document in table BKPF, identifiable by specific document types (e.g., 'DZ'). The posting date (BKPF-BUDAT) serves as the timestamp. Capture Identify creation of documents with payment-related document types (e.g., 'DZ') in table BKPF. Event type explicit
	Invoice Blocked For Payment	Indicates that a manual or automatic block has been placed on the invoice, preventing it from being paid. This is captured by a specific payment block indicator set on the invoice line item in the BSEG table.
Why it matters Payment blocks are a major source of delays and process exceptions. Identifying when and why invoices are blocked is key to uncovering root causes for late payments and improving cash flow. Where to get This status is typically inferred from a change in the Payment Block field (BSEG-ZLSPR) on the invoice line item. Change logs for this field (tables CDHDR and CDPOS) can provide explicit timestamps. Capture Detect changes to the BSEG-ZLSPR field for the invoice document line item, using table CDHDR for timestamps. Event type inferred
	Promise To Pay Broken	A calculated event indicating a customer failed to make a payment by the agreed-upon date in a 'Promise to Pay'. This is inferred by the absence of a corresponding payment by the promise date.
Why it matters Identifying broken promises is critical for escalating collection efforts. A high rate of broken promises may signal issues with the collection strategy or customer financial health. Where to get This is an inferred or calculated event. It is determined by comparing the promise date (from UDM_P2P_ATTR) with the actual payment clearing date for the invoice. If no payment is received by the promise date, the promise is considered broken. Capture Compare the promise date in UDM_P2P_ATTR with the clearing date of the invoice. If clearing date is after promise date, the promise was broken. Event type calculated
	Promise To Pay Created	A customer has contacted the collections department and promised to make a payment by a specific date. This event is explicitly logged if SAP FSCM Collections Management is in use.
Why it matters Promises to pay are a key outcome of collection activities. Analyzing their creation, fulfillment, and breakage rates helps measure the effectiveness of collector actions and forecast cash inflows. Where to get If using SAP FSCM Collections Management, this is an explicit event. Promise to pay details are stored in tables like UDM_P2P_ATTR, linked to the business partner and invoice. Capture Extract creation timestamp from promise to pay tables like UDM_P2P_ATTR in SAP FSCM. Event type explicit
	Residual Item Created	Occurs during payment application when a customer underpays an invoice, and the remaining small balance is posted as a new open item. This is inferred from the clearing transaction details.
Why it matters Residual items indicate payment discrepancies and create additional work. Tracking them helps identify customers who frequently short-pay and highlights issues in pricing or billing that lead to disputes. Where to get This is an inferred event. When a clearing transaction (e.g., via F-28) clears an original invoice but also posts a new open item document for the remaining amount, a residual item is created. This can be identified by checking the clearing document's line items. Capture Analyze clearing documents (BKPF-AUGBL) to find instances where a new open item is created with a reference to the original invoice. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from SAP ECC

Steps

Understand the Multi-Query Approach: The SAP Query tool (SQVI/SQ01) does not natively support the UNION operation required to stack different activities into a single event log. Therefore, this process involves creating a separate query for each of the 14 activities, exporting the results from each, and then combining them into one file outside of SAP.
Access SAP QuickViewer: Open the transaction code SQVI. Enter a name for your first query, for example, ZCREDIT_01_POSTED, and click 'Create'.
Define Data Source: In the 'Data source' selection, choose 'Table join'. On the next screen, add the primary tables required for invoice posting: BKPF, BSEG, and KNA1.
Define Table Joins: SAP will often propose the correct links. Verify that the tables are joined correctly: BKPF.BUKRS to BSEG.BUKRS, BKPF.BELNR to BSEG.BELNR, BKPF.GJAHR to BSEG.GJAHR, and BSEG.KUNNR to KNA1.KUNNR. Adjust the join conditions if necessary.
Select Fields and Define Constants: Navigate to the 'List fld. select.' and 'Sort sequence' tabs. Select the necessary fields from the tables, such as BELNR from BKPF, BUDAT from BKPF, and NAME1 from KNA1. To create the ActivityName, create a 'Local Field' and assign it a constant value, for example, 'Invoice Posted'. Ensure all queries you create have the exact same column names and order for easy merging later.
Set Selection Criteria: Go to the 'Selection fields' tab. Add fields that will be used for filtering, such as BUKRS (Company Code) and BUDAT (Posting Date) from BKPF. This is critical for managing data volume and performance.
Execute and Save First Query: Save the query. Execute it with a specific Company Code and a limited date range to test the output. Review the results to ensure they are correct. Export the data to a local file, such as a spreadsheet.
Create Queries for Other Activities: Repeat the process for all other required activities. For each activity, create a new query (e.g., ZCREDIT_02_DUNNING, ZCREDIT_03_CLEARED). You will need to use different tables as the primary data source:
- Dunning: MHNK joined with BKPF/BSEG.
- Promise to Pay / Dispute Cases: UDM_P2P_ATTR / SCASE if SAP FSCM is used.
- Cleared Invoices: BSAD (cleared items) instead of BSID (open items).
- Calculated Events: For events like 'Payment Due Date Passed', you will need to add a local field with logic to compare the due date (BSEG-ZFBDT) with the current date or another reference date.
Run All Queries: Execute each of the 14 saved queries using the exact same selection criteria (e.g., date range, company code) to ensure a consistent dataset.
Export All Results: Export the results from each query execution into separate files. Ensure the file format is consistent, for example, CSV or Tab-Delimited Text.
Combine and Format Event Log: Use an external tool (like Microsoft Excel, Python, or a data preparation tool) to open all the exported files. Append the data from each file into a single master file. Verify the column headers are consistent and remove any duplicates.
Final Review and Upload: The combined file is your final event log. Review it for completeness and correctness. Ensure it has the required columns: InvoiceNumber, ActivityName, and EventTime. The log is now ready to be uploaded to the process mining tool.

Configuration

Prerequisites: Access and authorization to transaction SQVI (QuickViewer) or SQ01/SQ02 (SAP Query). Sufficient authorization is required to read from financial accounting tables like BKPF, BSEG, BSAD, KNA1, MHNK, and potentially SAP FSCM tables like SCASE and UDM_P2P_ATTR.
Date Range Filters: It is critical to apply a date range filter to limit the data volume. Use the Posting Date (BKPF-BUDAT) or CPU-Enter Date (BKPF-CPUDT) as the primary filter. A range of 6-12 months is typically recommended for an initial analysis.
Mandatory Filters: Always filter by Company Code (BKPF-BUKRS) to analyze a specific legal entity. It is also highly recommended to filter by Document Type (BKPF-BLART), for instance, 'RV' for standard invoices, to exclude irrelevant documents.
Table Joins: Ensure that table joins are defined on all key fields, including Client (MANDT), Company Code (BUKRS), Document Number (BELNR), and Fiscal Year (GJAHR) to prevent incorrect data combinations.
Performance Considerations: Querying BSEG can be very slow. Always use filters that correspond to indexed fields. For large datasets, execute the queries in the background as scheduled jobs and retrieve the spool file output.

a Sample Query abap

/*
NOTE: SAP Query (SQVI/SQ01) is a visual tool and does not use a single script. 
The following ABAP SQL code is a logical representation of the data that must be extracted
across multiple, separate queries which are then combined manually. 
This script consolidates the logic for all 14 activities into one block for clarity.
*/

SELECT
    bseg~belnr AS InvoiceNumber,
    'Invoice Posted' AS ActivityName,
    bkpf~cpudt AS EventTime,
    bkpf~usnam AS UserName,
    bkpf~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bseg~dmbtr AS Dmbtr,
    bseg~zfbdt AS NetDueDate,
    t001w~sachp AS Collector, -- Note: Collector can be stored in different places
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bkpf
INNER JOIN bseg ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
LEFT JOIN kna1 ON bkpf~kunnr = kna1~kunnr
WHERE bkpf~bukrs = '[Your Company Code]' AND bkpf~budat BETWEEN '[Start Date]' AND '[End Date]' AND bkpf~blart IN ('RV', 'DR')

UNION ALL

SELECT
    bseg~belnr AS InvoiceNumber,
    'Invoice Blocked For Payment' AS ActivityName,
    bkpf~aedat AS EventTime, -- Use change date as best approximation
    bkpf~usnam AS UserName,
    bseg~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bseg~dmbtr AS Dmbtr,
    bseg~zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bseg
INNER JOIN bkpf ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
LEFT JOIN kna1 ON bseg~kunnr = kna1~kunnr
WHERE bseg~bukrs = '[Your Company Code]' AND bseg~zlspr <> '' AND bkpf~budat BETWEEN '[Start Date]' AND '[End Aate]'

UNION ALL

SELECT
    belnr AS InvoiceNumber,
    'Payment Due Date Passed' AS ActivityName,
    zfbdt AS EventTime, -- The due date itself is the event time
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsid
LEFT JOIN kna1 ON bsid~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND zfbdt < CURRENT_DATE() AND budat BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    mhnk~belnr AS InvoiceNumber,
    'Dunning Run Executed' AS ActivityName,
    mhnk~laufd AS EventTime, -- Dunning run date
    laufi~usnam AS UserName,
    mhnk~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bsid~dmbtr AS Dmbtr,
    bsid~zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mhnk~mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM mhnk
INNER JOIN bsid ON mhnk~bukrs = bsid~bukrs AND mhnk~belnr = bsid~belnr AND mhnk~gjahr = bsid~gjahr
INNER JOIN mahn AS laufi ON mhnk~laufd = laufi~laufd AND mhnk~laufi = laufi~laufi
LEFT JOIN kna1 ON mhnk~kunnr = kna1~kunnr
WHERE mhnk~bukrs = '[Your Company Code]' AND mhnk~laufd BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- Activities from SAP FSCM (Promise to Pay, Dispute Case). Tables/fields might vary.
-- This logic assumes SAP FSCM Collections and Dispute Management is in use.

SELECT
    p2p~invoice_ref AS InvoiceNumber,
    'Promise To Pay Created' AS ActivityName,
    p2p~creation_date AS EventTime,
    p2p~created_by AS UserName,
    p2p~customer AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    p2p~promise_date AS NetDueDate,
    p2p~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM udm_p2p_attr AS p2p
LEFT JOIN kna1 ON p2p~customer = kna1~kunnr
WHERE p2p~company_code = '[Your Company Code]' AND p2p~creation_date BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    p2p~invoice_ref AS InvoiceNumber,
    'Promise To Pay Broken' AS ActivityName,
    p2p~promise_date AS EventTime, -- Event happens on the day the promise was broken
    p2p~created_by AS UserName,
    p2p~customer AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    p2p~promise_date AS NetDueDate,
    p2p~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM udm_p2p_attr AS p2p
LEFT JOIN kna1 ON p2p~customer = kna1~kunnr
WHERE p2p~company_code = '[Your Company Code]' AND p2p~state = 'BROKEN' AND p2p~promise_date BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    obj~value AS InvoiceNumber,
    'Dispute Case Created' AS ActivityName,
    scase~creation_time AS EventTime,
    scase~created_by AS UserName,
    scase~partner AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    CAST( NULL AS DATS) AS NetDueDate,
    scase~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM scase
INNER JOIN scmg_t_case_attr ON scase~guid = scmg_t_case_attr~case_guid
INNER JOIN scmg_t_obj_attr AS obj ON scase~guid = obj~case_guid
LEFT JOIN kna1 ON scase~partner = kna1~kunnr
WHERE scase~case_type = '[Your Dispute Case Type]' AND obj~obj_type = 'BSEG' AND scase~creation_time BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    obj~value AS InvoiceNumber,
    'Dispute Case Resolved' AS ActivityName,
    scase~closing_time AS EventTime,
    scase~closed_by AS UserName,
    scase~partner AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    CAST( NULL AS DATS) AS NetDueDate,
    scase~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM scase
INNER JOIN scmg_t_case_attr ON scase~guid = scmg_t_case_attr~case_guid
INNER JOIN scmg_t_obj_attr AS obj ON scase~guid = obj~case_guid
LEFT JOIN kna1 ON scase~partner = kna1~kunnr
WHERE scase~case_type = '[Your Dispute Case Type]' AND obj~obj_type = 'BSEG' AND scase~status = '[Your Resolved Status]' AND scase~closing_time BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- For Collection Contact Logged, this is highly dependent on implementation. Assuming notes in FSCM.
SELECT
    obj~value AS InvoiceNumber,
    'Collection Contact Logged' AS ActivityName,
    note~creation_tstmp AS EventTime,
    note~created_by AS UserName,
    scase~partner AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    CAST( NULL AS DATS) AS NetDueDate,
    scase~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM scmg_t_note_attr AS note
INNER JOIN scase ON note~case_guid = scase~guid
INNER JOIN scmg_t_obj_attr AS obj ON scase~guid = obj~case_guid
LEFT JOIN kna1 ON scase~partner = kna1~kunnr
WHERE note~creation_tstmp BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT 
    augbl AS InvoiceNumber,
    'Incoming Payment Document Posted' AS ActivityName,
    budat AS EventTime,
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsad 
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND blart = 'DZ' AND budat BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    bsad~belnr AS InvoiceNumber,
    'Residual Item Created' AS ActivityName,
    bsad~augdt AS EventTime,
    bkpf~usnam AS UserName,
    bsad~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bsad~dmbtr AS Dmbtr,
    bsad~zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    bsad~mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsad
INNER JOIN bkpf ON bsad~augbl = bkpf~belnr AND bsad~bukrs = bkpf~bukrs AND bsad~augjg = bkpf~gjahr
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bsad~bukrs = '[Your Company Code]' AND bsad~augdt BETWEEN '[Start Date]' AND '[End Date]' AND EXISTS (SELECT 1 FROM bseg WHERE bseg~augbl = bsad~augbl AND bseg~rebzg IS NOT NULL)

UNION ALL

SELECT
    bseg~belnr AS InvoiceNumber,
    'Credit Memo Posted' AS ActivityName,
    bkpf~cpudt AS EventTime,
    bkpf~usnam AS UserName,
    bkpf~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bseg~dmbtr * -1 AS Dmbtr,
    bseg~zfbdt AS NetDueDate,
    t001w~sachp AS Collector, 
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bkpf
INNER JOIN bseg ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
LEFT JOIN kna1 ON bkpf~kunnr = kna1~kunnr
WHERE bkpf~bukrs = '[Your Company Code]' AND bkpf~budat BETWEEN '[Start Date]' AND '[End Date]' AND bkpf~blart IN ('DG', 'DZ')

UNION ALL

SELECT
    belnr AS InvoiceNumber,
    'Invoice Cleared' AS ActivityName,
    augdt AS EventTime, -- Clearing date
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mahns AS Mahns,
    DATEDIFF(day, zfbdt, augdt) AS DaysOverdue
FROM bsad
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND augdt BETWEEN '[Start Date]' AND '[End Date]' AND blart IN ('RV', 'DR')

UNION ALL

-- Write-off logic can vary. This is a simplified example looking for clearing via a specific reason code.
SELECT
    belnr AS InvoiceNumber,
    'Invoice Written Off' AS ActivityName,
    augdt AS EventTime,
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsad
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND augdt BETWEEN '[Start Date]' AND '[End Date]' AND auggr = '[Your Write-Off Reason Code]'

Steps

Establish Database Connection: Obtain secure, read-only access credentials for the underlying SAP ECC database. Use a standard SQL client, such as DBeaver, SQL Developer, or a dedicated ETL tool, to connect to the database.
Identify System Configuration: Before running the query, confirm with an SAP functional consultant the specific document types used for customer invoices (e.g., 'DR', 'RV'), credit memos (e.g., 'DG'), and payments (e.g., 'DZ'). Also, identify the G/L accounts used for bad debt write-offs.
Define Extraction Parameters: Open the provided SQL query in your client. Locate the placeholder variables within the initial Common Table Expression (CTE). Set the start and end dates for the extraction period and specify the relevant company codes (BUKRS).
Execute the Main Query: Run the complete SQL script. The query is structured with a UNION ALL to gather each of the 14 required activities into a single event log format. It sources data from core FI tables (BKPF, BSEG, BSAD) and, where applicable, FSCM and change log tables.
Handle FSCM Dependencies: Note that activities like 'Promise To Pay Created', 'Dispute Case Created', and 'Collection Contact Logged' depend on the use of SAP Financial Supply Chain Management (FSCM). If your organization does not use these modules, the corresponding parts of the query will return no data. You may comment out these sections to improve performance.
Review Calculated Events: The activities 'Payment Due Date Passed' and 'Promise To Pay Broken' are not direct system events but are derived by the query logic. 'Payment Due Date Passed' uses the net due date as the event time. 'Promise To Pay Broken' uses the promise fulfillment date as the event time if the promise was not kept.
Address Performance: For very large databases, executing the full query against tables like CDHDR and CDPOS can be time-consuming. Consider running the extraction during off-peak hours or applying more restrictive filters, such as specific fiscal years, to limit the data volume.
Export the Event Log: Once the query execution is complete, export the result set to a CSV file. Ensure the file is UTF-8 encoded to prevent character issues.
Prepare for Upload: Verify that the column headers in the exported CSV file match the required attributes for ProcessMind, specifically InvoiceNumber, ActivityName, and EventTime. The other columns like UserName and Kunnr will be mapped as case and event attributes.

Configuration

Prerequisites: Direct read-only access to the SAP ECC database is mandatory. Familiarity with the SAP FI data model is crucial. Access to FSCM tables (UDM_DISPUTE, UDM_P2P_ATTR) is required for dispute and promise-to-pay activities.
Key Filters: The most important filters are applied in the Invoices CTE at the beginning of the query.
- BUKRS (Company Code): Always filter by the company codes you want to analyze.
- BUDAT (Posting Date): Define a clear date range to limit the scope. Start with a smaller range, like 3-6 months, to validate the data before running a larger extraction.
- BLART (Document Type): Filter for customer invoice document types to create the base population of cases.
Performance Considerations: The query joins multiple large tables, including the change log tables (CDHDR, CDPOS). On systems with high data volume, this can lead to long execution times. Indexing on key fields like document number, company code, and fiscal year is critical on the database side. If performance is a major issue, consider extracting data for each activity separately and combining the results outside the database.
FSCM Module Dependency: A significant portion of the advanced collections activities (disputes, promises to pay, collection contacts) relies on the implementation and active use of SAP FSCM. If these modules are not in use, the query will not find data for these steps, and those activities will be missing from the process flow.

a Sample Query sql

WITH Invoices AS (
    SELECT 
        h.MANDT,
        h.BUKRS,
        h.BELNR, 
        h.GJAHR,
        h.BLART,
        h.BUDAT,
        h.CPUDT,
        h.CPUTM,
        h.USNAM, 
        i.KUNNR,
        i.DMBTR,
        i.ZFBDT AS NetDueDate -- ZFBDT is often the baseline date for payment
    FROM BKPF h
    JOIN BSEG i ON h.MANDT = i.MANDT AND h.BUKRS = i.BUKRS AND h.BELNR = i.BELNR AND h.GJAHR = i.GJAHR
    WHERE h.BUKRS IN ('[Your Company Code]') -- Placeholder: e.g., '1000', '2000'
      AND h.BUDAT BETWEEN '[YYYY-MM-DD]' AND '[YYYY-MM-DD]' -- Placeholder: e.g., '2023-01-01' AND '2023-12-31'
      AND h.BLART IN ('DR', 'RV') -- Typical customer invoice document types
      AND i.KOART = 'D' -- Account Type 'D' for Customer
      AND i.BSCHL LIKE '0%'
)
-- 1. Invoice Posted
SELECT
    i.BELNR AS "InvoiceNumber",
    'Invoice Posted' AS "ActivityName",
    CAST(i.CPUDT AS VARCHAR(8)) || ' ' || CAST(i.CPUTM AS VARCHAR(6)) AS "EventTime",
    i.USNAM AS "UserName",
    i.KUNNR AS "Kunnr",
    k.KDGRP AS "CustomerSegment",
    i.DMBTR AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    b.SACHP AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM Invoices i
LEFT JOIN KNA1 k ON i.MANDT = k.MANDT AND i.KUNNR = k.KUNNR
LEFT JOIN KNB1 b ON i.MANDT = b.MANDT AND i.KUNNR = b.KUNNR AND i.BUKRS = b.BUKRS

UNION ALL

-- 2. Invoice Blocked For Payment (from Change Logs)
SELECT
    i.BELNR AS "InvoiceNumber",
    'Invoice Blocked For Payment' AS "ActivityName",
    CAST(ch.UDATE AS VARCHAR(8)) || ' ' || CAST(ch.UTIME AS VARCHAR(6)) AS "EventTime",
    ch.USERNAME AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    i.DMBTR AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM Invoices i
JOIN CDHDR ch ON ch.OBJECTCLAS = 'BELEG' AND ch.OBJECTID = CONCAT(i.BUKRS, i.BELNR, i.GJAHR)
JOIN CDPOS cp ON ch.OBJECTCLAS = cp.OBJECTCLAS AND ch.OBJECTID = cp.OBJECTID AND ch.CHANGENR = cp.CHANGENR
WHERE cp.TABNAME = 'BSEG' AND cp.FNAME = 'ZLSPR' AND cp.VALUE_NEW <> ''

UNION ALL

-- 3. Payment Due Date Passed (Calculated)
SELECT 
    i.BELNR AS "InvoiceNumber",
    'Payment Due Date Passed' AS "ActivityName",
    CAST(i.NetDueDate AS VARCHAR(8)) || ' 00:00:00' AS "EventTime",
    'SYSTEM' AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    i.DMBTR AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    0 AS "DaysOverdue"
FROM Invoices i
WHERE i.NetDueDate < CURRENT_DATE

UNION ALL

-- 4. Dunning Run Executed
SELECT
    m.BELNR AS "InvoiceNumber",
    'Dunning Run Executed' AS "ActivityName",
    CAST(h.LAUFD AS VARCHAR(8)) || ' 00:00:00' AS "EventTime",
    h.USNAM AS "UserName",
    m.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    m.SKBET AS "Dmbtr",
    NULL AS "NetDueDate",
    NULL AS "Collector",
    m.MAHNS AS "Mahns",
    NULL AS "DaysOverdue"
FROM MHND m
JOIN MHNK h ON m.MANDT = h.MANDT AND m.LAUFD = h.LAUFD AND m.LAUFI = h.LAUFI
JOIN Invoices i ON m.MANDT = i.MANDT AND m.BUKRS = i.BUKRS AND m.BELNR = i.BELNR AND m.GJAHR = i.GJAHR

UNION ALL

-- 5. Promise To Pay Created (FSCM)
SELECT 
    p2p.INVOICE_ID AS "InvoiceNumber",
    'Promise To Pay Created' AS "ActivityName",
    p2p.CREATION_DATE || ' ' || p2p.CREATION_TIME AS "EventTime",
    p2p.CREATION_USER AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    p2p.PROMISED_AMOUNT AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM UDM_P2P_ATTR p2p
JOIN Invoices i ON p2p.INVOICE_ID = i.BELNR AND p2p.COMPANY_CODE = i.BUKRS

UNION ALL

-- 6. Promise To Pay Broken (FSCM, Calculated)
SELECT 
    p2p.INVOICE_ID AS "InvoiceNumber",
    'Promise To Pay Broken' AS "ActivityName",
    p2p.FULFILLMENT_DATE || ' 00:00:00' AS "EventTime",
    'SYSTEM' AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    p2p.PROMISED_AMOUNT AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM UDM_P2P_ATTR p2p
JOIN Invoices i ON p2p.INVOICE_ID = i.BELNR AND p2p.COMPANY_CODE = i.BUKRS
WHERE p2p.STATE IN ('BROKEN', 'PARTIALLY_KEPT_BROKEN') AND p2p.FULFILLMENT_DATE < CURRENT_DATE

UNION ALL

-- 7. Dispute Case Created (FSCM)
SELECT 
    COALESCE(inv.REF_VALUE, d.CASE_ID) AS "InvoiceNumber",
    'Dispute Case Created' AS "ActivityName",
    d.CREATION_DATE || ' ' || d.CREATION_TIME AS "EventTime",
    d.CREATED_BY AS "UserName",
    d.PARTNER AS "Kunnr",
    NULL AS "CustomerSegment",
    d.DISPUTED_AMOUNT AS "Dmbtr",
    NULL AS "NetDueDate",
    d.PROCESSOR AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM UDM_DISPUTE d
LEFT JOIN SCMG_T_CASE_ATTR inv ON d.CLIENT = inv.CLIENT AND d.CASE_GUID = inv.CASE_GUID AND inv.ATTR_ID = 'INVOICE'
JOIN Invoices i ON COALESCE(inv.REF_VALUE, d.CASE_ID) = i.BELNR AND d.COMPANY_CODE = i.BUKRS

UNION ALL

-- 8. Collection Contact Logged (FSCM)
SELECT
    i.BELNR AS "InvoiceNumber",
    'Collection Contact Logged' AS "ActivityName",
    c.CONTACT_DATE || ' 00:00:00' AS "EventTime",
    c.CREATED_BY AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    i.DMBTR AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    c.PROCESSOR AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM UDM_COLL_ITEM c
JOIN Invoices i ON c.DOCUMENT_NUMBER = i.BELNR AND c.COMPANY_CODE = i.BUKRS
WHERE c.OBJECT_TYPE = 'CONTACT'

UNION ALL

-- 9. Dispute Case Resolved (FSCM)
SELECT 
    COALESCE(inv.REF_VALUE, d.CASE_ID) AS "InvoiceNumber",
    'Dispute Case Resolved' AS "ActivityName",
    d.CLOSING_DATE || ' ' || d.CLOSING_TIME AS "EventTime",
    d.CLOSED_BY AS "UserName",
    d.PARTNER AS "Kunnr",
    NULL AS "CustomerSegment",
    d.DISPUTED_AMOUNT AS "Dmbtr",
    NULL AS "NetDueDate",
    d.PROCESSOR AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM UDM_DISPUTE d
LEFT JOIN SCMG_T_CASE_ATTR inv ON d.CLIENT = inv.CLIENT AND d.CASE_GUID = inv.CASE_GUID AND inv.ATTR_ID = 'INVOICE'
JOIN Invoices i ON COALESCE(inv.REF_VALUE, d.CASE_ID) = i.BELNR AND d.COMPANY_CODE = i.BUKRS
WHERE d.STATE = 'CLOSED'

UNION ALL

-- 10. Incoming Payment Document Posted
SELECT
    i.BELNR AS "InvoiceNumber",
    'Incoming Payment Document Posted' AS "ActivityName",
    CAST(p.BUDAT AS VARCHAR(8)) || ' ' || CAST(p.CPUTM AS VARCHAR(6)) AS "EventTime",
    p.USNAM AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    NULL AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM Invoices i
JOIN BSAD c ON i.MANDT = c.MANDT AND i.BELNR = c.BELNR AND i.GJAHR = c.GJAHR AND i.BUKRS = c.BUKRS
JOIN BKPF p ON c.MANDT = p.MANDT AND c.AUGBL = p.BELNR AND c.AUGGJ = p.GJAHR AND c.BUKRS = p.BUKRS
WHERE p.BLART IN ('DZ', 'ZP') -- Typical customer payment document types

UNION ALL

-- 11. Residual Item Created (Simplified)
SELECT
    i.BELNR AS "InvoiceNumber",
    'Residual Item Created' AS "ActivityName",
    CAST(c.AUGDT AS VARCHAR(8)) || ' 00:00:00' AS "EventTime",
    c.USNAM_TMP AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    res.DMBTR AS "Dmbtr",
    i.NetDueDate AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM Invoices i
JOIN BSAD c ON i.MANDT = c.MANDT AND i.BELNR = c.BELNR AND i.GJAHR = c.GJAHR AND i.BUKRS = c.BUKRS
JOIN BSEG res ON c.MANDT = res.MANDT AND c.AUGBL = res.AUGBL AND c.AUGGJ = res.GJAHR AND c.BUKRS = res.BUKRS
WHERE res.BSCHL = '06' -- Posting key for residual item

UNION ALL

-- 12. Credit Memo Posted
SELECT 
    h.REBZG AS "InvoiceNumber", -- Reference to original invoice
    'Credit Memo Posted' AS "ActivityName",
    CAST(h.CPUDT AS VARCHAR(8)) || ' ' || CAST(h.CPUTM AS VARCHAR(6)) AS "EventTime",
    h.USNAM AS "UserName",
    i.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    i.DMBTR AS "Dmbtr",
    NULL AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM BKPF h
JOIN BSEG i ON h.MANDT = i.MANDT AND h.BUKRS = i.BUKRS AND h.BELNR = i.BELNR AND h.GJAHR = i.GJAHR
JOIN Invoices inv ON h.REBZG = inv.BELNR AND h.BUKRS = inv.BUKRS
WHERE h.BLART IN ('DG', 'G2') -- Typical credit memo doc types
  AND i.KOART = 'D'

UNION ALL

-- 13. Invoice Cleared
SELECT
    c.BELNR AS "InvoiceNumber",
    'Invoice Cleared' AS "ActivityName",
    CAST(c.AUGDT AS VARCHAR(8)) || ' 00:00:00' AS "EventTime",
    c.USNAM_TMP AS "UserName",
    c.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    c.DMBTR AS "Dmbtr",
    NULL AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    (c.AUGDT - i.NetDueDate) AS "DaysOverdue"
FROM BSAD c
JOIN Invoices i ON c.MANDT = i.MANDT AND c.KUNNR = i.KUNNR AND c.BELNR = i.BELNR AND c.GJAHR = i.GJAHR

UNION ALL

-- 14. Invoice Written Off
SELECT 
    c.BELNR AS "InvoiceNumber",
    'Invoice Written Off' AS "ActivityName",
    CAST(c.AUGDT AS VARCHAR(8)) || ' 00:00:00' AS "EventTime",
    c.USNAM_TMP AS "UserName",
    c.KUNNR AS "Kunnr",
    NULL AS "CustomerSegment",
    c.DMBTR AS "Dmbtr",
    NULL AS "NetDueDate",
    NULL AS "Collector",
    NULL AS "Mahns",
    NULL AS "DaysOverdue"
FROM BSAD c
JOIN BSEG cl_item ON c.MANDT = cl_item.MANDT AND c.AUGBL = cl_item.BELNR AND c.BUKRS = cl_item.BUKRS AND c.AUGGJ = cl_item.GJAHR
JOIN Invoices i ON c.MANDT = i.MANDT AND c.BELNR = i.BELNR AND c.GJAHR = i.GJAHR
WHERE cl_item.HKONT IN ('[Your Bad Debt GL Account]') -- Placeholder: e.g., '800100'
  AND cl_item.KOART = 'S'; -- G/L Account Item

Steps

Prerequisites: Install Python 3.x on your machine. Install the required libraries using pip: pip install pyrfc pandas. Download the SAP NetWeaver RFC SDK from the SAP Support Portal and configure it according to the PyRFC documentation. Ensure you have an SAP user with sufficient authorizations to execute remote function calls and access financial accounting tables like BKPF, BSEG, BSAD, and MHNK.
Gather SAP Connection Details: Obtain the necessary credentials for your target SAP ECC system, including the Application Server Host (ASHOST), System Number (SYSNR), Client (CLIENT), User ID, and Password.
Configure the Python Script: Open the provided Python script. Locate the SAP_CONNECTION_DETAILS dictionary and replace the placeholder values with your actual SAP system credentials.
Define Extraction Parameters: In the main section of the script, set the COMPANY_CODES list and the START_DATE and END_DATE variables. This will control the scope of the data extraction, which is critical for performance.
Create Custom RFCs (if needed): The script uses placeholder functions for extracting data from SAP FSCM modules (get_fscm_promises, get_fscm_disputes, get_fscm_contacts). Work with an ABAP developer to create corresponding remote-enabled function modules in SAP (e.g., Z_PM_GET_PROMISES) that read from tables like UDM_P2P_ATTR and UDM_SPS_DC_ATTR and return the data in a structured format.
Execute the Script: Run the script from your command line using python your_script_name.py. The script will connect to SAP, execute a series of function calls to fetch data for each activity, and log its progress to the console.
Derive Calculated Events: After fetching all transactional data from SAP, the script will process it using the pandas library to derive calculated events like 'Payment Due Date Passed' and 'Promise To Pay Broken' based on the defined business logic.
Consolidate Event Data: The script collects data from all sources into a single, unified pandas DataFrame. It standardizes column names to match the ProcessMind requirements (InvoiceNumber, ActivityName, EventTime, etc.).
Review the Output: The script generates a final CSV file named credit_management_event_log.csv. Open this file to inspect the data for completeness and correctness before uploading.

Configuration

Prerequisites: A functional Python 3 environment with the pyrfc and pandas libraries is required. The SAP NWRFC SDK must be installed and accessible. The SAP user needs RFC execution permissions and read access to FI and, if applicable, FSCM tables.
SAP Connection: The script requires direct RFC connection details. It is highly recommended to use a dedicated service account with limited, read-only permissions for this purpose.
Date Range: The START_DATE and END_DATE parameters are crucial for controlling the data volume. For initial analysis, a period of 3 to 6 months is recommended to ensure manageable extraction times.
Company Code Filter: Filtering by one or more specific Company Codes (COMPANY_CODES) is mandatory. Extracting data for all company codes at once is not recommended and can cause severe performance issues or script timeouts.
FSCM Module Dependency: Activities such as 'Promise To Pay Created', 'Dispute Case Created', and 'Collection Contact Logged' depend entirely on the active implementation and usage of SAP Financial Supply Chain Management (FSCM) modules. If FSCM is not used, these events cannot be extracted.
Performance Considerations: The script performs multiple data requests to SAP. For very large datasets (millions of invoices), the extraction process can be lengthy. Consider running the script in batches for smaller date ranges or during off-peak system hours.

a Sample Query python

import pandas as pd
from pyrfc import Connection
from datetime import datetime, timedelta

class=class="hl-string">"hl-comment"># --- 1. CONFIGURATION ---
class=class="hl-string">"hl-comment"># Replace with your SAP system connection details
SAP_CONNECTION_DETAILS = {
    class="hl-string">"ashost": class="hl-string">"[your_sap_host]",
    class="hl-string">"sysnr": class="hl-string">"[your_system_number]",
    class="hl-string">"client": class="hl-string">"[your_client]",
    class="hl-string">"user": class="hl-string">"[your_username]",
    class="hl-string">"passwd": class="hl-string">"[your_password]"
}

class=class="hl-string">"hl-comment"># Define extraction scope
COMPANY_CODES = [class="hl-string">'1000'] class=class="hl-string">"hl-comment"># Example: [class="hl-string">'1000', class="hl-string">'2000']
START_DATE = class="hl-string">"20230101"
END_DATE = class="hl-string">"20231231"

class=class="hl-string">"hl-comment"># --- Helper function to call RFC_READ_TABLE and return a pandas DataFrame ---
def read_sap_table(conn, table_name, fields, options):
    print(fclass="hl-string">"Reading table: {table_name}...")
    try:
        result = conn.call(class="hl-string">'RFC_READ_TABLE',
                             QUERY_TABLE=table_name,
                             DELIMITER=class="hl-string">'|',
                             FIELDS=fields,
                             OPTIONS=options)
        
        data = [row[class="hl-string">'WA'].split(class="hl-string">'|') for row in result[class="hl-string">'DATA']]
        df = pd.DataFrame(data, columns=fields)
        print(fclass="hl-string">"Successfully read {len(df)} rows from {table_name}.")
        return df
    except Exception as e:
        print(fclass="hl-string">"Error reading table {table_name}: {e}")
        return pd.DataFrame(columns=fields)

class=class="hl-string">"hl-comment"># --- Placeholder functions for custom FSCM RFCs ---
class=class="hl-string">"hl-comment"># These must be implemented by an ABAP developer in SAP
def get_fscm_promises(conn, company_codes, start_date, end_date):
    print(class="hl-string">"INFO: 'get_fscm_promises' is a placeholder. Implement a custom RFC to read promise-to-pay data.")
    class=class="hl-string">"hl-comment"># Example custom RFC: Z_PM_GET_PROMISES
    class=class="hl-string">"hl-comment"># This RFC should read from UDM_P2P_ATTR and related tables.
    class=class="hl-string">"hl-comment"># It should return fields like: BUKRS, BELNR, GJAHR, P2P_CREATE_DATE, P2P_DATE, P2P_USER
    return pd.DataFrame(columns=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'P2P_CREATE_DATE', class="hl-string">'P2P_DATE', class="hl-string">'P2P_USER'])

def get_fscm_disputes(conn, company_codes, start_date, end_date):
    print(class="hl-string">"INFO: 'get_fscm_disputes' is a placeholder. Implement a custom RFC to read dispute case data.")
    class=class="hl-string">"hl-comment"># Example custom RFC: Z_PM_GET_DISPUTES
    class=class="hl-string">"hl-comment"># This RFC should read from UDM_SPS_DC_ATTR and related tables.
    class=class="hl-string">"hl-comment"># It should return fields like: BUKRS, BELNR, GJAHR, CREATE_DATE, CREATE_USER, RESOLVE_DATE
    return pd.DataFrame(columns=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'CREATE_DATE', class="hl-string">'RESOLVE_DATE', class="hl-string">'CREATE_USER'])

def get_fscm_contacts(conn, company_codes, start_date, end_date):
    print(class="hl-string">"INFO: 'get_fscm_contacts' is a placeholder. Implement a custom RFC to read collection contact data.")
    class=class="hl-string">"hl-comment"># Example custom RFC: Z_PM_GET_COLLECTION_CONTACTS
    class=class="hl-string">"hl-comment"># It should read from relevant collection contact log tables.
    class=class="hl-string">"hl-comment"># It should return fields like: BUKRS, BELNR, GJAHR, CONTACT_DATE, CONTACT_USER
    return pd.DataFrame(columns=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'CONTACT_DATE', class="hl-string">'CONTACT_USER'])

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    all_events = []

    try:
        conn = Connection(**SAP_CONNECTION_DETAILS)
        print(class="hl-string">"Successfully connected to SAP.")

        company_code_filter = class="hl-string">" OR ".join([fclass="hl-string">"BUKRS = '{cc}'" for cc in COMPANY_CODES])
        date_filter = fclass="hl-string">"BUDAT >= '{START_DATE}' AND BUDAT <= '{END_DATE}'"
        doc_type_filter = class="hl-string">"(BLART = 'RV' OR BLART = 'DR')" class=class="hl-string">"hl-comment"># Standard Invoice Types
        
        class=class="hl-string">"hl-comment"># 1. Invoice Posted
        bkpf_fields = [class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'BUDAT', class="hl-string">'CPUDT', class="hl-string">'CPUTM', class="hl-string">'USNAM', class="hl-string">'BLART']
        bkpf_options = [fclass="hl-string">"({company_code_filter}) AND ({date_filter}) AND {doc_type_filter}"]
        df_bkpf = read_sap_table(conn, class="hl-string">'BKPF', bkpf_fields, bkpf_options)

        if not df_bkpf.empty:
            bseg_fields = [class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'BUZEI', class="hl-string">'KOART', class="hl-string">'KUNNR', class="hl-string">'DMBTR', class="hl-string">'ZFBDT', class="hl-string">'SACHP', class="hl-string">'ZLSPR']
            class=class="hl-string">"hl-comment"># Create batches for BSEG query to avoid hitting RFC limits
            df_bseg_list = []
            for i in range(0, len(df_bkpf), 500):
                batch = df_bkpf.iloc[i:i+500]
                bseg_options = [class="hl-string">" OR ".join([fclass="hl-string">"(BELNR = '{row.BELNR}' AND GJAHR = '{row.GJAHR}' AND BUKRS = '{row.BUKRS}')" for index, row in batch.iterrows()])]
                df_bseg_list.append(read_sap_table(conn, class="hl-string">'BSEG', bseg_fields, bseg_options))
            df_bseg = pd.concat(df_bseg_list)
            df_bseg_customer = df_bseg[df_bseg[class="hl-string">'KOART'] == class="hl-string">'D'].copy()
            df_base = pd.merge(df_bkpf, df_bseg_customer, on=[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS'])

            class=class="hl-string">"hl-comment"># Activity: Invoice Posted
            evt_posted = df_base[[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'CPUDT', class="hl-string">'CPUTM', class="hl-string">'USNAM', class="hl-string">'KUNNR', class="hl-string">'DMBTR', class="hl-string">'ZFBDT', class="hl-string">'SACHP']].copy()
            evt_posted[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Posted'
            evt_posted[class="hl-string">'EventTime'] = pd.to_datetime(evt_posted[class="hl-string">'CPUDT'] + evt_posted[class="hl-string">'CPUTM'], format=class="hl-string">'%Y%m%d%H%M%S')
            all_events.append(evt_posted)

            class=class="hl-string">"hl-comment"># Activity: Invoice Blocked For Payment (based on current status)
            evt_blocked = df_base[df_base[class="hl-string">'ZLSPR'] != class="hl-string">''].copy()
            if not evt_blocked.empty:
                evt_blocked[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Blocked For Payment'
                evt_blocked[class="hl-string">'EventTime'] = pd.to_datetime(evt_blocked[class="hl-string">'CPUDT'] + evt_blocked[class="hl-string">'CPUTM'], format=class="hl-string">'%Y%m%d%H%M%S') class=class="hl-string">"hl-comment"># Note: This is posting time, not block time.
                all_events.append(evt_blocked)

            class=class="hl-string">"hl-comment"># 2. Invoice Cleared & Residuals & Write-Offs
            bsad_fields = [class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'KUNNR', class="hl-string">'AUGDT', class="hl-string">'AUGBL', class="hl-string">'DMBTR', class="hl-string">'ZFBDT', class="hl-string">'SACHP', class="hl-string">'RSTGR']
            bsad_options = [fclass="hl-string">"({company_code_filter}) AND AUGDT >= '{START_DATE}' AND AUGDT <= '{END_DATE}' AND (BLART = 'RV' OR BLART = 'DR')"]
            df_bsad = read_sap_table(conn, class="hl-string">'BSAD', bsad_fields, bsad_options)

            if not df_bsad.empty:
                evt_cleared = df_bsad.copy()
                evt_cleared[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Cleared'
                evt_cleared[class="hl-string">'EventTime'] = pd.to_datetime(evt_cleared[class="hl-string">'AUGDT'], format=class="hl-string">'%Y%m%d')
                evt_cleared[class="hl-string">'USNAM'] = class="hl-string">'' class=class="hl-string">"hl-comment"># Clearing user is in the clearing document header
                all_events.append(evt_cleared)

                class=class="hl-string">"hl-comment"># Activity: Residual Item Created (simplified logic)
                evt_residual = df_bsad[df_bsad[class="hl-string">'RSTGR'] != class="hl-string">''].copy()
                if not evt_residual.empty:
                    evt_residual[class="hl-string">'ActivityName'] = class="hl-string">'Residual Item Created'
                    evt_residual[class="hl-string">'EventTime'] = pd.to_datetime(evt_residual[class="hl-string">'AUGDT'], format=class="hl-string">'%Y%m%d')
                    evt_residual[class="hl-string">'USNAM'] = class="hl-string">''
                    all_events.append(evt_residual)

            class=class="hl-string">"hl-comment"># 3. Dunning Run Executed
            mhnk_fields = [class="hl-string">'MABER', class="hl-string">'LAUFD']
            mhnk_options = [fclass="hl-string">"LAUFD >= '{START_DATE}' AND LAUFD <= '{END_DATE}'"]
            df_mhnk = read_sap_table(conn, class="hl-string">'MHNK', mhnk_fields, mhnk_options)
            if not df_mhnk.empty:
                mhnd_fields = [class="hl-string">'MABER', class="hl-string">'LAUFD', class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUKRS', class="hl-string">'MAHNS', class="hl-string">'KUNNR']
                mhnd_options = [class="hl-string">" OR ".join([fclass="hl-string">"(MABER = '{row.MABER}' AND LAUFD = '{row.LAUFD}')" for index, row in df_mhnk.iterrows()])]
                df_mhnd = read_sap_table(conn, class="hl-string">'MHND', mhnd_fields, mhnd_options)
                if not df_mhnd.empty:
                    evt_dunning = df_mhnd.copy()
                    evt_dunning[class="hl-string">'ActivityName'] = class="hl-string">'Dunning Run Executed'
                    evt_dunning[class="hl-string">'EventTime'] = pd.to_datetime(evt_dunning[class="hl-string">'LAUFD'], format=class="hl-string">'%Y%m%d')
                    evt_dunning[class="hl-string">'USNAM'] = class="hl-string">'SAP_BATCH' class=class="hl-string">"hl-comment"># Dunning is usually a batch job
                    all_events.append(evt_dunning)

            class=class="hl-string">"hl-comment"># 4. Other Document Types (Payments, Credit Memos)
            other_docs_filter = fclass="hl-string">"(BLART = 'DZ' OR BLART = 'DG')" class=class="hl-string">"hl-comment"># DZ=Payment, DG=Credit Memo
            other_bkpf_options = [fclass="hl-string">"({company_code_filter}) AND ({date_filter}) AND {other_docs_filter}"]
            df_other_bkpf = read_sap_table(conn, class="hl-string">'BKPF', bkpf_fields, other_bkpf_options)
            if not df_other_bkpf.empty:
                class=class="hl-string">"hl-comment"># Simplified: Link via customer and date. Proper linking uses clearing doc.
                class=class="hl-string">"hl-comment"># Here we just log the event. You would need to refine logic to link it to a specific invoice.
                class=class="hl-string">"hl-comment"># The presence of these events on the customer timeline is still valuable.
                df_other_bkpf[class="hl-string">'ActivityName'] = df_other_bkpf[class="hl-string">'BLART'].apply(lambda x: class="hl-string">'Incoming Payment Document Posted' if x == class="hl-string">'DZ' else class="hl-string">'Credit Memo Posted')
                df_other_bkpf[class="hl-string">'EventTime'] = pd.to_datetime(df_other_bkpf[class="hl-string">'CPUDT'] + df_other_bkpf[class="hl-string">'CPUTM'], format=class="hl-string">'%Y%m%d%H%M%S')
                class=class="hl-string">"hl-comment"># This part lacks invoice-level detail but is included for completeness.
                print(class="hl-string">"WARNING: Payment/Credit Memo events are logged but not directly linked to a specific invoice in this simplified script.")

            class=class="hl-string">"hl-comment"># 5. FSCM Activities (Requires custom RFCs)
            df_promises = get_fscm_promises(conn, COMPANY_CODES, START_DATE, END_DATE)
            if not df_promises.empty:
                evt_promise_created = df_promises.copy()
                evt_promise_created[class="hl-string">'ActivityName'] = class="hl-string">'Promise To Pay Created'
                evt_promise_created[class="hl-string">'EventTime'] = pd.to_datetime(evt_promise_created[class="hl-string">'P2P_CREATE_DATE'], format=class="hl-string">'%Y%m%d')
                evt_promise_created.rename(columns={class="hl-string">'P2P_USER': class="hl-string">'USNAM'}, inplace=True)
                all_events.append(evt_promise_created)

            df_disputes = get_fscm_disputes(conn, COMPANY_CODES, START_DATE, END_DATE)
            if not df_disputes.empty:
                evt_dispute_created = df_disputes.copy()
                evt_dispute_created[class="hl-string">'ActivityName'] = class="hl-string">'Dispute Case Created'
                evt_dispute_created[class="hl-string">'EventTime'] = pd.to_datetime(evt_dispute_created[class="hl-string">'CREATE_DATE'], format=class="hl-string">'%Y%m%d')
                evt_dispute_created.rename(columns={class="hl-string">'CREATE_USER': class="hl-string">'USNAM'}, inplace=True)
                all_events.append(evt_dispute_created)

                evt_dispute_resolved = df_disputes[df_disputes[class="hl-string">'RESOLVE_DATE'].notna()].copy()
                evt_dispute_resolved[class="hl-string">'ActivityName'] = class="hl-string">'Dispute Case Resolved'
                evt_dispute_resolved[class="hl-string">'EventTime'] = pd.to_datetime(evt_dispute_resolved[class="hl-string">'RESOLVE_DATE'], format=class="hl-string">'%Y%m%d')
                evt_dispute_resolved[class="hl-string">'USNAM'] = class="hl-string">'' class=class="hl-string">"hl-comment"># Resolve user might be different, needs custom RFC logic
                all_events.append(evt_dispute_resolved)

            df_contacts = get_fscm_contacts(conn, COMPANY_CODES, START_DATE, END_DATE)
            if not df_contacts.empty:
                evt_contacts = df_contacts.copy()
                evt_contacts[class="hl-string">'ActivityName'] = class="hl-string">'Collection Contact Logged'
                evt_contacts[class="hl-string">'EventTime'] = pd.to_datetime(evt_contacts[class="hl-string">'CONTACT_DATE'], format=class="hl-string">'%Y%m%d')
                evt_contacts.rename(columns={class="hl-string">'CONTACT_USER': class="hl-string">'USNAM'}, inplace=True)
                all_events.append(evt_contacts)
            
            class=class="hl-string">"hl-comment"># --- 6. Consolidate and Process --- 
            final_df = pd.concat(all_events, ignore_index=True)

            class=class="hl-string">"hl-comment"># Combine document number and year for a unique ID
            final_df[class="hl-string">'InvoiceNumber'] = final_df[class="hl-string">'BELNR'].astype(str) + class="hl-string">'-' + final_df[class="hl-string">'GJAHR'].astype(str)

            class=class="hl-string">"hl-comment"># Rename columns to match target schema
            final_df.rename(columns={
                class="hl-string">'KUNNR': class="hl-string">'Kunnr',
                class="hl-string">'DMBTR': class="hl-string">'Dmbtr',
                class="hl-string">'ZFBDT': class="hl-string">'NetDueDate',
                class="hl-string">'USNAM': class="hl-string">'UserName',
                class="hl-string">'SACHP': class="hl-string">'Sachp',
                class="hl-string">'MAHNS': class="hl-string">'DunningLevel'
            }, inplace=True)
            
            class=class="hl-string">"hl-comment"># --- 7. Derive Calculated Events --- 
            class=class="hl-string">"hl-comment"># Payment Due Date Passed
            df_due_date = df_base[[class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'ZFBDT']].copy()
            df_due_date.dropna(subset=[class="hl-string">'ZFBDT'], inplace=True)
            df_due_date = df_due_date[df_due_date[class="hl-string">'ZFBDT'] != class="hl-string">'00000000']
            df_due_date[class="hl-string">'EventTime'] = pd.to_datetime(df_due_date[class="hl-string">'ZFBDT'], format=class="hl-string">'%Y%m%d')
            df_due_date = df_due_date[df_due_date[class="hl-string">'EventTime'] < datetime.now()]
            df_due_date[class="hl-string">'ActivityName'] = class="hl-string">'Payment Due Date Passed'
            df_due_date[class="hl-string">'InvoiceNumber'] = df_due_date[class="hl-string">'BELNR'].astype(str) + class="hl-string">'-' + df_due_date[class="hl-string">'GJAHR'].astype(str)
            final_df = pd.concat([final_df, df_due_date], ignore_index=True)

            class=class="hl-string">"hl-comment"># Promise to Pay Broken (example logic)
            if not df_promises.empty and not df_bsad.empty:
                promises_with_due_date = df_promises.copy()
                promises_with_due_date[class="hl-string">'P2P_DATE_DT'] = pd.to_datetime(promises_with_due_date[class="hl-string">'P2P_DATE'], format=class="hl-string">'%Y%m%d')
                cleared_docs = df_bsad[[class="hl-string">'BELNR', class="hl-string">'GJAHR']].copy()
                cleared_docs[class="hl-string">'cleared'] = True
                promises_with_due_date = pd.merge(promises_with_due_date, cleared_docs, on=[class="hl-string">'BELNR', class="hl-string">'GJAHR'], how=class="hl-string">'left')
                broken_promises = promises_with_due_date[(promises_with_due_date[class="hl-string">'cleared'].isna()) & (promises_with_due_date[class="hl-string">'P2P_DATE_DT'] < datetime.now())]
                if not broken_promises.empty:
                    evt_broken = broken_promises.copy()
                    evt_broken[class="hl-string">'ActivityName'] = class="hl-string">'Promise To Pay Broken'
                    evt_broken[class="hl-string">'EventTime'] = evt_broken[class="hl-string">'P2P_DATE_DT']
                    evt_broken[class="hl-string">'InvoiceNumber'] = evt_broken[class="hl-string">'BELNR'].astype(str) + class="hl-string">'-' + evt_broken[class="hl-string">'GJAHR'].astype(str)
                    final_df = pd.concat([final_df, evt_broken], ignore_index=True)

            class=class="hl-string">"hl-comment"># --- 8. Final Formatting & Export --- 
            class=class="hl-string">"hl-comment"># Standardize column set
            final_columns = [
                class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName',
                class="hl-string">'Kunnr', class="hl-string">'Dmbtr', class="hl-string">'NetDueDate', class="hl-string">'Sachp', class="hl-string">'DunningLevel'
            ]
            class=class="hl-string">"hl-comment"># Add missing columns with None
            for col in final_columns:
                if col not in final_df.columns:
                    final_df[col] = None

            class=class="hl-string">"hl-comment"># Select and order columns
            final_df = final_df[final_columns]

            class=class="hl-string">"hl-comment"># Sort by case and time
            final_df.sort_values(by=[class="hl-string">'InvoiceNumber', class="hl-string">'EventTime'], inplace=True)
            
            class=class="hl-string">"hl-comment"># Export to CSV
            output_filename = class="hl-string">'credit_management_event_log.csv'
            final_df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%d %H:%M:%S')
            print(fclass="hl-string">"\nExtraction complete. Event log saved to {output_filename}")

    except Exception as e:
        print(fclass="hl-string">"An error occurred: {e}")
    finally:
        if class="hl-string">'conn' in locals() and conn.alive:
            conn.close()
            print(class="hl-string">"SAP connection closed.")

if __name__ == class="hl-string">"__main__":
    main()

Steps

Understand the Multi-Query Approach: The SAP Query tool (SQVI/SQ01) does not natively support the UNION operation required to stack different activities into a single event log. Therefore, this process involves creating a separate query for each of the 14 activities, exporting the results from each, and then combining them into one file outside of SAP.
Access SAP QuickViewer: Open the transaction code SQVI. Enter a name for your first query, for example, ZCREDIT_01_POSTED, and click 'Create'.
Define Data Source: In the 'Data source' selection, choose 'Table join'. On the next screen, add the primary tables required for invoice posting: BKPF, BSEG, and KNA1.
Define Table Joins: SAP will often propose the correct links. Verify that the tables are joined correctly: BKPF.BUKRS to BSEG.BUKRS, BKPF.BELNR to BSEG.BELNR, BKPF.GJAHR to BSEG.GJAHR, and BSEG.KUNNR to KNA1.KUNNR. Adjust the join conditions if necessary.
Select Fields and Define Constants: Navigate to the 'List fld. select.' and 'Sort sequence' tabs. Select the necessary fields from the tables, such as BELNR from BKPF, BUDAT from BKPF, and NAME1 from KNA1. To create the ActivityName, create a 'Local Field' and assign it a constant value, for example, 'Invoice Posted'. Ensure all queries you create have the exact same column names and order for easy merging later.
Set Selection Criteria: Go to the 'Selection fields' tab. Add fields that will be used for filtering, such as BUKRS (Company Code) and BUDAT (Posting Date) from BKPF. This is critical for managing data volume and performance.
Execute and Save First Query: Save the query. Execute it with a specific Company Code and a limited date range to test the output. Review the results to ensure they are correct. Export the data to a local file, such as a spreadsheet.
Create Queries for Other Activities: Repeat the process for all other required activities. For each activity, create a new query (e.g., ZCREDIT_02_DUNNING, ZCREDIT_03_CLEARED). You will need to use different tables as the primary data source:
- Dunning: MHNK joined with BKPF/BSEG.
- Promise to Pay / Dispute Cases: UDM_P2P_ATTR / SCASE if SAP FSCM is used.
- Cleared Invoices: BSAD (cleared items) instead of BSID (open items).
- Calculated Events: For events like 'Payment Due Date Passed', you will need to add a local field with logic to compare the due date (BSEG-ZFBDT) with the current date or another reference date.
Run All Queries: Execute each of the 14 saved queries using the exact same selection criteria (e.g., date range, company code) to ensure a consistent dataset.
Export All Results: Export the results from each query execution into separate files. Ensure the file format is consistent, for example, CSV or Tab-Delimited Text.
Combine and Format Event Log: Use an external tool (like Microsoft Excel, Python, or a data preparation tool) to open all the exported files. Append the data from each file into a single master file. Verify the column headers are consistent and remove any duplicates.
Final Review and Upload: The combined file is your final event log. Review it for completeness and correctness. Ensure it has the required columns: InvoiceNumber, ActivityName, and EventTime. The log is now ready to be uploaded to the process mining tool.

Configuration

Prerequisites: Access and authorization to transaction SQVI (QuickViewer) or SQ01/SQ02 (SAP Query). Sufficient authorization is required to read from financial accounting tables like BKPF, BSEG, BSAD, KNA1, MHNK, and potentially SAP FSCM tables like SCASE and UDM_P2P_ATTR.
Date Range Filters: It is critical to apply a date range filter to limit the data volume. Use the Posting Date (BKPF-BUDAT) or CPU-Enter Date (BKPF-CPUDT) as the primary filter. A range of 6-12 months is typically recommended for an initial analysis.
Mandatory Filters: Always filter by Company Code (BKPF-BUKRS) to analyze a specific legal entity. It is also highly recommended to filter by Document Type (BKPF-BLART), for instance, 'RV' for standard invoices, to exclude irrelevant documents.
Table Joins: Ensure that table joins are defined on all key fields, including Client (MANDT), Company Code (BUKRS), Document Number (BELNR), and Fiscal Year (GJAHR) to prevent incorrect data combinations.
Performance Considerations: Querying BSEG can be very slow. Always use filters that correspond to indexed fields. For large datasets, execute the queries in the background as scheduled jobs and retrieve the spool file output.

a Sample Query abap

/*
NOTE: SAP Query (SQVI/SQ01) is a visual tool and does not use a single script. 
The following ABAP SQL code is a logical representation of the data that must be extracted
across multiple, separate queries which are then combined manually. 
This script consolidates the logic for all 14 activities into one block for clarity.
*/

SELECT
    bseg~belnr AS InvoiceNumber,
    'Invoice Posted' AS ActivityName,
    bkpf~cpudt AS EventTime,
    bkpf~usnam AS UserName,
    bkpf~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bseg~dmbtr AS Dmbtr,
    bseg~zfbdt AS NetDueDate,
    t001w~sachp AS Collector, -- Note: Collector can be stored in different places
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bkpf
INNER JOIN bseg ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
LEFT JOIN kna1 ON bkpf~kunnr = kna1~kunnr
WHERE bkpf~bukrs = '[Your Company Code]' AND bkpf~budat BETWEEN '[Start Date]' AND '[End Date]' AND bkpf~blart IN ('RV', 'DR')

UNION ALL

SELECT
    bseg~belnr AS InvoiceNumber,
    'Invoice Blocked For Payment' AS ActivityName,
    bkpf~aedat AS EventTime, -- Use change date as best approximation
    bkpf~usnam AS UserName,
    bseg~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bseg~dmbtr AS Dmbtr,
    bseg~zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bseg
INNER JOIN bkpf ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
LEFT JOIN kna1 ON bseg~kunnr = kna1~kunnr
WHERE bseg~bukrs = '[Your Company Code]' AND bseg~zlspr <> '' AND bkpf~budat BETWEEN '[Start Date]' AND '[End Aate]'

UNION ALL

SELECT
    belnr AS InvoiceNumber,
    'Payment Due Date Passed' AS ActivityName,
    zfbdt AS EventTime, -- The due date itself is the event time
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsid
LEFT JOIN kna1 ON bsid~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND zfbdt < CURRENT_DATE() AND budat BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    mhnk~belnr AS InvoiceNumber,
    'Dunning Run Executed' AS ActivityName,
    mhnk~laufd AS EventTime, -- Dunning run date
    laufi~usnam AS UserName,
    mhnk~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bsid~dmbtr AS Dmbtr,
    bsid~zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mhnk~mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM mhnk
INNER JOIN bsid ON mhnk~bukrs = bsid~bukrs AND mhnk~belnr = bsid~belnr AND mhnk~gjahr = bsid~gjahr
INNER JOIN mahn AS laufi ON mhnk~laufd = laufi~laufd AND mhnk~laufi = laufi~laufi
LEFT JOIN kna1 ON mhnk~kunnr = kna1~kunnr
WHERE mhnk~bukrs = '[Your Company Code]' AND mhnk~laufd BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- Activities from SAP FSCM (Promise to Pay, Dispute Case). Tables/fields might vary.
-- This logic assumes SAP FSCM Collections and Dispute Management is in use.

SELECT
    p2p~invoice_ref AS InvoiceNumber,
    'Promise To Pay Created' AS ActivityName,
    p2p~creation_date AS EventTime,
    p2p~created_by AS UserName,
    p2p~customer AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    p2p~promise_date AS NetDueDate,
    p2p~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM udm_p2p_attr AS p2p
LEFT JOIN kna1 ON p2p~customer = kna1~kunnr
WHERE p2p~company_code = '[Your Company Code]' AND p2p~creation_date BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    p2p~invoice_ref AS InvoiceNumber,
    'Promise To Pay Broken' AS ActivityName,
    p2p~promise_date AS EventTime, -- Event happens on the day the promise was broken
    p2p~created_by AS UserName,
    p2p~customer AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    p2p~promise_date AS NetDueDate,
    p2p~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM udm_p2p_attr AS p2p
LEFT JOIN kna1 ON p2p~customer = kna1~kunnr
WHERE p2p~company_code = '[Your Company Code]' AND p2p~state = 'BROKEN' AND p2p~promise_date BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    obj~value AS InvoiceNumber,
    'Dispute Case Created' AS ActivityName,
    scase~creation_time AS EventTime,
    scase~created_by AS UserName,
    scase~partner AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    CAST( NULL AS DATS) AS NetDueDate,
    scase~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM scase
INNER JOIN scmg_t_case_attr ON scase~guid = scmg_t_case_attr~case_guid
INNER JOIN scmg_t_obj_attr AS obj ON scase~guid = obj~case_guid
LEFT JOIN kna1 ON scase~partner = kna1~kunnr
WHERE scase~case_type = '[Your Dispute Case Type]' AND obj~obj_type = 'BSEG' AND scase~creation_time BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    obj~value AS InvoiceNumber,
    'Dispute Case Resolved' AS ActivityName,
    scase~closing_time AS EventTime,
    scase~closed_by AS UserName,
    scase~partner AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    CAST( NULL AS DATS) AS NetDueDate,
    scase~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM scase
INNER JOIN scmg_t_case_attr ON scase~guid = scmg_t_case_attr~case_guid
INNER JOIN scmg_t_obj_attr AS obj ON scase~guid = obj~case_guid
LEFT JOIN kna1 ON scase~partner = kna1~kunnr
WHERE scase~case_type = '[Your Dispute Case Type]' AND obj~obj_type = 'BSEG' AND scase~status = '[Your Resolved Status]' AND scase~closing_time BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

-- For Collection Contact Logged, this is highly dependent on implementation. Assuming notes in FSCM.
SELECT
    obj~value AS InvoiceNumber,
    'Collection Contact Logged' AS ActivityName,
    note~creation_tstmp AS EventTime,
    note~created_by AS UserName,
    scase~partner AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    CAST( NULL AS DEC(13,2)) AS Dmbtr,
    CAST( NULL AS DATS) AS NetDueDate,
    scase~processor AS Collector,
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM scmg_t_note_attr AS note
INNER JOIN scase ON note~case_guid = scase~guid
INNER JOIN scmg_t_obj_attr AS obj ON scase~guid = obj~case_guid
LEFT JOIN kna1 ON scase~partner = kna1~kunnr
WHERE note~creation_tstmp BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT 
    augbl AS InvoiceNumber,
    'Incoming Payment Document Posted' AS ActivityName,
    budat AS EventTime,
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsad 
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND blart = 'DZ' AND budat BETWEEN '[Start Date]' AND '[End Date]'

UNION ALL

SELECT
    bsad~belnr AS InvoiceNumber,
    'Residual Item Created' AS ActivityName,
    bsad~augdt AS EventTime,
    bkpf~usnam AS UserName,
    bsad~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bsad~dmbtr AS Dmbtr,
    bsad~zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    bsad~mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsad
INNER JOIN bkpf ON bsad~augbl = bkpf~belnr AND bsad~bukrs = bkpf~bukrs AND bsad~augjg = bkpf~gjahr
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bsad~bukrs = '[Your Company Code]' AND bsad~augdt BETWEEN '[Start Date]' AND '[End Date]' AND EXISTS (SELECT 1 FROM bseg WHERE bseg~augbl = bsad~augbl AND bseg~rebzg IS NOT NULL)

UNION ALL

SELECT
    bseg~belnr AS InvoiceNumber,
    'Credit Memo Posted' AS ActivityName,
    bkpf~cpudt AS EventTime,
    bkpf~usnam AS UserName,
    bkpf~kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    bseg~dmbtr * -1 AS Dmbtr,
    bseg~zfbdt AS NetDueDate,
    t001w~sachp AS Collector, 
    CAST( NULL AS CHAR(1)) AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bkpf
INNER JOIN bseg ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
LEFT JOIN kna1 ON bkpf~kunnr = kna1~kunnr
WHERE bkpf~bukrs = '[Your Company Code]' AND bkpf~budat BETWEEN '[Start Date]' AND '[End Date]' AND bkpf~blart IN ('DG', 'DZ')

UNION ALL

SELECT
    belnr AS InvoiceNumber,
    'Invoice Cleared' AS ActivityName,
    augdt AS EventTime, -- Clearing date
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mahns AS Mahns,
    DATEDIFF(day, zfbdt, augdt) AS DaysOverdue
FROM bsad
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND augdt BETWEEN '[Start Date]' AND '[End Date]' AND blart IN ('RV', 'DR')

UNION ALL

-- Write-off logic can vary. This is a simplified example looking for clearing via a specific reason code.
SELECT
    belnr AS InvoiceNumber,
    'Invoice Written Off' AS ActivityName,
    augdt AS EventTime,
    usnam AS UserName,
    kunnr AS Kunnr,
    kna1~ktokd AS CustomerSegment,
    dmbtr AS Dmbtr,
    zfbdt AS NetDueDate,
    t001w~sachp AS Collector,
    mahns AS Mahns,
    CAST( NULL AS INT4) AS DaysOverdue
FROM bsad
LEFT JOIN kna1 ON bsad~kunnr = kna1~kunnr
WHERE bukrs = '[Your Company Code]' AND augdt BETWEEN '[Start Date]' AND '[End Date]' AND auggr = '[Your Write-Off Reason Code]'

Your Credit Management & Collections Data Template

Your Credit Management & Collections Data Template

Credit Management & Collections Attributes

Credit Management & Collections Activities

Extraction Guides

SAP ECC Data Extraction using ABAP Query (SQ01/SQVI)

Steps

Configuration

a Sample Query abap

Direct SQL Query on SAP ECC Database Tables

Steps

Configuration

a Sample Query sql

Python Scripting with SAP Remote Function Call (RFC)

Steps

Configuration

a Sample Query python

Custom ABAP Extraction Program for Flat File Output

Steps

Configuration

a Sample Query abap

Ready to Get Started?

Unlock Faster Cash Flow: Optimize Credit & Collections Now

Your Credit Management & Collections Data Template

Your Credit Management & Collections Data Template

Credit Management & Collections Attributes

Credit Management & Collections Activities

Extraction Guides

SAP ECC Data Extraction using ABAP Query (SQ01/SQVI)

Steps

Configuration

a Sample Query abap

Direct SQL Query on SAP ECC Database Tables

Steps

Configuration

a Sample Query sql

Python Scripting with SAP Remote Function Call (RFC)

Steps

Configuration

a Sample Query python

Custom ABAP Extraction Program for Flat File Output

Steps

Configuration

a Sample Query abap

We value your privacy

Ready to Get Started?

Unlock Faster Cash Flow: Optimize Credit & Collections Now