> Continuous Process Improvement > Accounts Receivable > SAP S/4HANA > Data Template

Your Accounts Receivable Data Template

SAP S/4HANA

Accounts Receivable Attributes (20) Accounts Receivable Activities (13) Extraction Guides (4)

Your Accounts Receivable Data Template

This template provides a comprehensive framework for mapping your collections and payment processes within SAP S/4HANA. It includes a curated list of essential attributes to collect, a series of process activities to track, and technical guidance for extracting data from your financial tables. By following this structure, you can gain a clear view of your cash flow cycles and identify areas where manual intervention or payment delays occur.

Recommended attributes to collect
Key activities to track
Extraction guidance

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

Download Template

Accounts Receivable Attributes

These are the recommended data fields and dimensional attributes to include in your event log for a comprehensive analysis of your accounts receivable process.

5 Required 10 Recommended 5 Optional

	Name	Description
	Activity Activity	The specific event or action performed in the process.
Description This attribute describes the step performed in the AR process, such as Create Invoice, Post Partial Payment, or Clear Invoice. It is usually derived from the transaction code (TCODE) or specific changes in the document status found in change log tables. Why it matters It is essential for mapping the process flow and identifying the sequence of events. Where to get Derived from SAP TSTCT (Transaction Codes) or CDHDR/CDPOS (Change Documents) Examples Create AR InvoicePost Partial PaymentClear InvoiceCreate Dispute Case
	Event Time EventTime	The timestamp when the activity occurred.
Description This attribute records the exact date and time an activity took place. For transactional data, this is often a combination of Entry Date (CPUDT) and Entry Time (CPUTM) from the header tables. It enables the calculation of cycle times and throughput times. Why it matters Accurate timestamps are critical for calculating DSO and identifying bottlenecks. Where to get SAP Table BKPF-CPUDT and BKPF-CPUTM Examples 2023-10-12T08:30:00Z2023-10-15T14:45:12Z2023-11-01T09:15:00Z
	Invoice Number InvoiceNumber	The unique identifier for the invoice or accounting document.
Description This attribute serves as the unique Case ID for the Accounts Receivable process. It usually corresponds to the Accounting Document Number (BELNR) in the SAP FI module or the Billing Document (VBELN) in SD, depending on the extraction logic. It links all subsequent activities such as payments, disputes, and clearing to the original financial obligation. Why it matters It is the central key required to reconstruct the end-to-end process flow for every transaction. Where to get SAP Table BKPF-BELNR or VBRK-VBELN Examples 1400000234900004321014000002351800000099
	Last Data Update LastDataUpdate	The timestamp when the data was last extracted or refreshed.
Description This attribute indicates when the data was loaded into the process mining tool. It helps users understand the freshness of the data presented in dashboards like Period End Reconciliation Status. Why it matters Ensures analysts know if they are looking at real-time or historical data. Where to get ETL metadata Examples 2023-11-05T00:00:00Z2023-11-05T06:00:00Z
	Source System SourceSystem	The system where the data originated.
Description This attribute identifies the specific instance of SAP S/4HANA or external system where the record was created. It is useful in landscape environments with multiple ERP instances to trace data lineage. Why it matters Allows for segregation of analysis in multi-system environments. Where to get System ID (SY-SYSID) or hardcoded during extraction Examples SAP_S4H_PRODSAP_S4H_01LEGACY_ERP
	Amount in Local Currency AmountInLocalCurrency	The value of the invoice or payment in the company code currency.
Description This attribute (DMBTR) contains the financial amount of the transaction. It is used to calculate the total value of open invoices, credit memos, and payments. It supports the Credit Memo and Correction Analysis dashboard by quantifying value. Why it matters Required for financial impact analysis and prioritizing high-value collections. Where to get SAP Table BSEG-DMBTR or ACDOCA-DMBTR Examples 1500.00250.5010000.00
	Clearing Date ClearingDate	The date the invoice was cleared or settled.
Description This attribute (AUGDT) records when the open item was cleared, usually by a payment. It is the end timestamp for the core AR process cycle and is the primary variable in the Days Sales Outstanding calculation. Why it matters Defines the completion of the collection cycle. Where to get SAP Table BSEG-AUGDT or BSAD-AUGDT Examples 2023-11-012023-11-10
	Company Code CompanyCode	The financial entity or business unit the invoice belongs to.
Description The Company Code (BUKRS) is the central organizational unit of external accounting within the SAP System. It is used to segment analysis for dashboards like Customer Payment Behavior Profiling across different business units. Why it matters Essential for filtering and comparing performance across different legal entities. Where to get SAP Table BKPF-BUKRS or T001-BUKRS Examples 1000US01DE012000
	Customer Number CustomerNumber	The unique identifier for the customer account.
Description This attribute represents the Customer Number (KUNNR) associated with the invoice. It allows for detailed analysis of payment behaviors and is critical for the Customer Payment Behavior Profiling dashboard. Why it matters Enables drill-down into specific customer account performance. Where to get SAP Table BSEG-KUNNR or KNA1-KUNNR Examples CUST100230001004500WALMART_US
	Days Sales Outstanding DaysSalesOutstanding	The duration from invoice posting to clearing.
Description This metric is calculated by subtracting the Posting Date from the Clearing Date for cleared items. It is the primary value for the Days Sales Outstanding Overview dashboard and is a critical measure of cash flow efficiency. Why it matters The primary executive KPI for the AR process. Where to get Calculated: ClearingDate - PostingDate Examples 30 days45 days15 days
	Document Type DocumentType	Classifies the accounting document (e.g., Invoice, Payment, Credit Memo).
Description The Document Type (BLART) categorizes the transaction. Examples include 'DR' for Customer Invoice, 'DZ' for Customer Payment, and 'DG' for Credit Memo. This is vital for filtering specific case types for the Credit Memo and Correction Analysis. Why it matters Segments the data into invoices, payments, and adjustments. Where to get SAP Table BKPF-BLART Examples DRDZDGRV
	Due Date DueDate	The calculated date by which the invoice must be paid.
Description This attribute represents the net due date. In SAP, this is often derived by taking the Baseline Date (ZFBDT) and adding the days defined in the Payment Terms (ZTERM). It is the benchmark for the Dunning Compliance and Execution dashboard. Why it matters The reference point for determining if a payment is late. Where to get Derived from BSEG-ZFBDT and BSEG-ZTERM Examples 2023-11-302023-12-15
	Is Automated IsAutomated	Flag indicating if the activity was performed by a system user.
Description This boolean attribute is calculated based on the User attribute. If the User ID matches known system or batch accounts (e.g., 'BNK_BSM'), this flag is set to true. It directly drives the Payment Matching Automation Rate dashboard. Why it matters Key for measuring digital transformation and automation success. Where to get Derived from User attribute Examples truefalse
	Payment Terms PaymentTerms	Key representing the agreed conditions for payment.
Description This attribute (ZTERM) defines the terms under which the customer must pay, including due dates and cash discounts. It is the primary data source for the Early Payment Discount Optimization dashboard. Why it matters Baseline for calculating due dates and analyzing discount eligibility. Where to get SAP Table BSEG-ZTERM or KNB1-ZTERM Examples NT300001Z015
	User Name User	The ID of the user who performed the activity.
Description This attribute (USNAM) identifies the user or system agent responsible for the event. It is analyzed to calculate the Automated Payment Matching Rate by distinguishing between human users and system batch users. Why it matters Critical for determining automation rates and user productivity. Where to get SAP Table BKPF-USNAM or CDHDR-USERNAME Examples JSMITHBATCH_USERSAP_WFALEX_D
	Country Country	The country of the customer.
Description This attribute (LAND1) comes from the customer master data. It allows for geographical segmentation of KPIs, such as analyzing Credit Memo rates by region or country. Why it matters Regional compliance and performance analysis. Where to get SAP Table KNA1-LAND1 Examples USDEFRJP
	Dispute Case ID DisputeCaseId	The identifier for any dispute case linked to the invoice.
Description This attribute holds the ID of a dispute case (UDM_CASE) if one exists. It links the financial document to the Dispute Management module. This is required for the Dispute Resolution Cycle Efficiency dashboard. Why it matters Connects AR documents to the dispute resolution workflow. Where to get SAP Table FDM_DCPROC or UDM_CASE_ATTR Examples 0000056789DISP-2023-001
	Dunning Level DunningLevel	The current dunning level of the invoice.
Description This attribute (MAHNS) indicates how many times a customer has been reminded of payment. It tracks the escalation of collection efforts and is visualized in the Dunning Compliance and Execution dashboard. Why it matters Monitors the severity of late payments and adherence to dunning policy. Where to get SAP Table MHNK-MAHNS or KNB5 (Customer master dunning) Examples 1230
	Is Credit Memo IsCreditMemo	Flag indicating if the document is a credit memo.
Description This boolean attribute is derived from the Document Type (e.g., if Type equals 'DG'). It allows for quick filtering of corrective transactions to support the Corrective Credit Memo Rate KPI. Why it matters Identifies rework and billing errors. Where to get Derived from DocumentType Examples truefalse
	Region Region	The state, province, or region of the customer.
Description This attribute (REGIO) provides a more granular geographical view than Country. It is useful for the Credit Memo and Correction Analysis to identify regional processing issues. Why it matters Granular geographic analysis. Where to get SAP Table KNA1-REGIO Examples CANYTXBY

Required Recommended Optional

Accounts Receivable Activities

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

6 Recommended 7 Optional

	Activity	Description
	Clear Invoice	The technical accounting step where the open item is matched against a payment or credit and the status changes to Cleared.
Why it matters The definitive end of the process instance in the system. Required to calculate the final DSO metric. Where to get Table BSEG or ACDOCA. Field AUGDT (Clearing Date) is populated. Use USNAM to determine if automated or manual. Capture Logged when AUGDT field is populated Event type explicit
	Create AR Invoice	The initial creation of the customer invoice document within the financial system. This is captured from the document header creation timestamp.
Why it matters Marks the start of the accounts receivable process and the beginning of the aging clock. Essential for calculating Days Sales Outstanding (DSO). Where to get Table BKPF (Accounting Document Header), field CPUDT (Entry Date) and CPUTM (Entry Time). Filter by Document Type (BLART) relevant for Customer Invoices (e.g., DR, RV). Capture Logged when transaction is committed to BKPF Event type explicit
	Issue Invoice Output	The explicit action of generating the output message (email, print, EDI) to send the invoice to the customer. This validates that the customer has officially received the billing request.
Why it matters Delays between creation and dispatch impact the customer's effective payment term window. Monitoring this ensures invoices are not stuck in internal processing. Where to get Table NAST (Message Status), using the Object Key (OBJKY) matching the Invoice Document Number. Timestamp from DATVR and UHRVR. Capture Logged in NAST table upon output generation Event type explicit
	Post Partial Payment	A payment is received that does not cover the full invoice amount, leaving a residual item on the account.
Why it matters Impacts the Short Payment Resolution Cycle. Frequent partial payments increase complexity and manual reconciliation effort. Where to get Table BSEG. Inferred when a clearing document (AUGBL) creates a new residual item (Reference Document matches original invoice) with a remaining balance. Capture Derive from clearing doc creation with residual Event type inferred
	Receive Full Payment	The receipt of funds that fully covers the outstanding invoice amount. This is often the effective end of the customer's obligation.
Why it matters Determines the actual payment date vs. the due date. Critical for analyzing Payment Term Deviation and customer behavior. Where to get Table BSEG, derived from the Clearing Document (AUGBL) where the Clearing Date (AUGDT) is set and balance becomes zero. Capture Logged when clearing document is posted Event type explicit
	Send Payment Reminder	A dunning notice or reminder letter is generated for an overdue invoice. This tracks the collection efforts made by the organization.
Why it matters Critical for the Dunning Compliance Rate KPI. It verifies if the collections strategy is being executed according to policy. Where to get Table MHND (Dunning Data), field LAUFD (Date of Dunning Run). Correlate via Company Code and Customer. Capture Logged when Dunning Run (F150) is executed Event type explicit
	Create Dispute Case	A dispute case is opened against the invoice, typically via SAP FSCM Dispute Management. This indicates the customer has contested the bill.
Why it matters Stops the clock on standard collection but starts the Dispute Resolution Cycle time. High volumes here impact cash flow predictability. Where to get Table UDM_CASE_ATTR (Case Attributes) or UDM_DISPUTE (Dispute Case). Link via CASE_GUID to the financial document key. Capture Logged in FSCM Dispute Management tables Event type explicit
	Post Credit Memo	A credit memo is issued and linked to the invoice, reducing the amount due. This usually corrects an error or processes a return.
Why it matters Key for Corrective Credit Memo Rate KPI. High frequency indicates upstream quality issues in sales or billing. Where to get Table BSEG, Document Type 'DG' or 'KG'. Identified by the 'Invoice Reference' (REBZG) field pointing to the original invoice. Capture Logged when credit memo doc references invoice Event type explicit
	Reconcile Bank Statement	The bank statement line item is successfully matched to the payment document in the ledger, finalizing the cash position.
Why it matters Tracks the Bank Statement Reconciliation Lead Time. Delays here affect the speed of the financial close. Where to get Table FEBEP (Electronic Bank Statement Line Items). Matches the payment document to the bank statement transaction. Capture Logged in EBS tables (FEBEP/FEBKO) Event type explicit
	Record Promise To Pay	A collection agent records a formal promise from the customer to pay a specific amount by a specific date.
Why it matters Indicates successful collection agent activity. Used to forecast short-term cash flow and measure Collection Agent Throughput. Where to get Table UDM_P2P (Promise to Pay), field CREATED_AT. Linked to the invoice via the Case GUID or Document Key. Capture Logged in FSCM Collections Management Event type explicit
	Resolve Dispute Case	The dispute case is closed, resulting in either a credit, a write-off, or re-confirmation of the debt. This marks the end of the exception handling.
Why it matters The end timestamp for the Average Dispute Resolution Time KPI. Efficiency here directly recovers stalled working capital. Where to get Table UDM_CASE_ATTR, field ESCALATION_DATE or CLOSED_DATE depending on configuration. Capture Logged when case status set to Closed/Void Event type explicit
	Reverse Invoice	The original invoice is cancelled or reversed in the system due to a fundamental error.
Why it matters Represents a failed process instance. Filtering these out or analyzing them separately is crucial for data quality. Where to get Table BKPF, field STBLG (Reverse Document Number) is populated on the original invoice record. Capture Logged when STBLG field is populated Event type explicit
	Update Dispute Status	The status of an existing dispute case changes, for example, moving from 'New' to 'In Process' or 'Under Review'.
Why it matters Provides granularity into the dispute resolution workflow and helps identify bottlenecks in the approval or investigation phase. Where to get Change documents for the Dispute Case (S/4HANA Case Management logs). Analysis of UDM_CASE_ATTR changes. Capture Compare status field changes in case history Event type inferred

Recommended Optional

Extraction Guides

How to get your data from SAP S/4HANA

Steps

Identify the Data Source: This extraction uses SAP S/4HANA standard Core Data Services (CDS) views. The primary view is I_JournalEntryItem, which contains the granular line items for all financial transactions including invoices, payments, and clearing events.
Prepare the SQL Environment: Ensure you have SQL access to the SAP HANA database. This can be achieved through SAP HANA Studio, the DBACOCKPIT transaction, or an external ETL tool configured with an ODBC/JDBC connection to the S/4HANA instance.
Define Scope and Filters: Determine the Company Codes and Fiscal Years required for the extraction. The query below includes placeholders for these values to optimize performance and relevance.
Extract Financial Activities: The first block of the query targets the I_JournalEntryItem view. It filters for Account Type 'D' (Customer) to isolate Accounts Receivable. It distinguishes between Invoices, Credit Memos, and Payments based on the Financial Account Type and Document Type.
Extract Clearing and Settlement: The query performs a self-join or separate selection on I_JournalEntryItem to identify when an invoice was cleared. This creates the 'Clear Invoice' and 'Receive Full Payment' activities based on the clearing timestamp.
Integrate Operational Events: To capture non-financial activities like 'Send Payment Reminder' or 'Create Dispute Case', the query joins specialized views such as I_DunningHistory, I_DisputeCase, and I_OutputRequestItem. Note that Dispute Management and Output Management availability depends on your specific S/4HANA configuration.
Handle Bank Reconciliation: The 'Reconcile Bank Statement' activity is derived from I_BankStatementLineItem, linking the external bank statement to the internal accounting document.
Standardize Output Columns: The query casts all timestamps to a standard format and ensures all required columns (InvoiceNumber, Activity, EventTime) are populated. Monetary values are converted to a common float or decimal format.
Execute and Verify: Run the query against a small subset of data first (e.g., one Company Code, one month) to verify the logic. Check that the row count matches expected transaction volumes.
Export Data: Save the result set as a CSV or Parquet file. Ensure the encoding is set to UTF-8 to handle special characters in User Names or Text descriptions.
Import to ProcessMind: Upload the extracted file into ProcessMind. Map the 'InvoiceNumber' column to the Case ID field and 'EventTime' to the Timestamp field during the import configuration.

Configuration

Source Views: Uses standard S/4HANA CDS views (I_JournalEntryItem, I_DunningHistory, I_DisputeCase, I_OutputRequestItem).
Date Range: Recommended to extract data for the last 12 months to capture the full lifecycle of open invoices.
Filtering: Filter by Company Code (CompanyCode) and Fiscal Year (FiscalYear) to restrict data volume.
Account Type: Strictly filters for AccountType = 'D' to ensure only Customer/AR data is retrieved.
Performance: CDS views are virtual data models. For very large datasets, consider materializing the result or running the extraction during off-peak hours.
Customization: The logic assumes standard SAP Document Types (e.g., RV for Invoices, DZ for Payments). Adjust the CASE statements in the query if your organization uses custom Document Types.

a Sample Query sql

/* ACCOUNTS RECEIVABLE EXTRACTION - SAP S/4HANA CDS VIEWS */
/* METHOD: SQL Union of multiple CDS Views */

/* 1. Create AR Invoice */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Create AR Invoice' AS Activity,
    JE.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(JE.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE.CreatedByUser AS "User",
    JE.AccountingDocumentType AS DocumentType,
    JE.PaymentTerms,
    JE.NetDueDate AS DueDate,
    JE.ClearingDate,
    'False' AS IsAutomated,
    CASE 
        WHEN JE.ClearingDate IS NOT NULL THEN DAYS_BETWEEN(JE.PostingDate, JE.ClearingDate) 
        ELSE NULL 
    END AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE
WHERE JE.FinancialAccountType = 'D' -- Customer
  AND JE.AccountingDocumentType IN ('RV', 'DR') -- Invoice Types
  AND JE.Ledger = '0L' -- Leading Ledger
  AND JE.CompanyCode IN ('[Your Company Code]')
  AND JE.FiscalYear >= '[Your Start Year]'

UNION ALL

/* 2. Issue Invoice Output (Requires S/4 Output Management) */
SELECT 
    CAST(B.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Issue Invoice Output' AS Activity,
    O.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.CompanyCode,
    B.SoldToParty AS CustomerNumber,
    CAST(B.TotalNetAmount AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    O.CreatedByUser AS "User",
    B.BillingDocumentType AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_BillingDocument AS B
JOIN I_OutputRequestItem AS O ON O.BillingDocument = B.BillingDocument
WHERE B.CompanyCode IN ('[Your Company Code]')
  AND B.FiscalYear >= '[Your Start Year]'

UNION ALL

/* 3. Send Payment Reminder */
SELECT 
    CAST(D.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Send Payment Reminder' AS Activity,
    D.DunningDate AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    D.CompanyCode,
    D.Customer AS CustomerNumber,
    CAST(D.AmountInLocalCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    'System' AS "User",
    'Dunning' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_DunningHistory AS D
WHERE D.CompanyCode IN ('[Your Company Code]')
  AND D.DunningDate >= '[Your Start Date]'

UNION ALL

/* 4. Create Dispute Case (Requires FSCM) */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Create Dispute Case' AS Activity,
    DC.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    DC.CompanyCode,
    DC.Customer AS CustomerNumber,
    CAST(DC.DisputedAmount AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    DC.CreatedByUser AS "User",
    'Dispute' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_DisputeCase AS DC
JOIN I_JournalEntryItem AS JE ON DC.CompanyCode = JE.CompanyCode AND DC.Customer = JE.Customer
/* Note: Join logic simplified. Ideally link via Case ID reference in JE if configured */
WHERE DC.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 5. Update Dispute Status */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Update Dispute Status' AS Activity,
    DC.LastChangeDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    DC.CompanyCode,
    DC.Customer AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    DC.LastChangedByUser AS "User",
    'Dispute' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_DisputeCase AS DC
JOIN I_JournalEntryItem AS JE ON DC.CompanyCode = JE.CompanyCode AND DC.Customer = JE.Customer
WHERE DC.LastChangeDateTime IS NOT NULL
  AND DC.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 6. Resolve Dispute Case */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Resolve Dispute Case' AS Activity,
    DC.ClosingDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    DC.CompanyCode,
    DC.Customer AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    DC.LastChangedByUser AS "User",
    'Dispute' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_DisputeCase AS DC
JOIN I_JournalEntryItem AS JE ON DC.CompanyCode = JE.CompanyCode AND DC.Customer = JE.Customer
WHERE DC.ClosingDateTime IS NOT NULL
  AND DC.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 7. Record Promise To Pay */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Record Promise To Pay' AS Activity,
    PP.PromiseToPayDate AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    PP.CompanyCode,
    PP.Customer AS CustomerNumber,
    CAST(PP.PromiseAmount AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    PP.CreatedByUser AS "User",
    'Promise' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_PromiseToPay AS PP
JOIN I_JournalEntryItem AS JE ON PP.CompanyCode = JE.CompanyCode AND PP.Customer = JE.Customer
WHERE PP.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 8. Post Partial Payment (Logic: Payment Doc exists but invoice not fully cleared immediately) */
SELECT 
    CAST(JE_INV.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Post Partial Payment' AS Activity,
    JE_PAY.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE_PAY.CompanyCode,
    JE_PAY.Customer AS CustomerNumber,
    CAST(JE_PAY.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE_PAY.CreatedByUser AS "User",
    JE_PAY.AccountingDocumentType AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    JE_INV.ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE_PAY
JOIN I_JournalEntryItem AS JE_INV ON JE_PAY.ClearingJournalEntry = JE_INV.AccountingDocument 
     AND JE_PAY.CompanyCode = JE_INV.CompanyCode
     AND JE_PAY.FiscalYear = JE_INV.FiscalYear
WHERE JE_PAY.AccountingDocumentType IN ('DZ', 'ZP') -- Payment Types
  AND JE_INV.ClearingDate IS NULL -- Invoice still open
  AND JE_PAY.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 9. Post Credit Memo */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Post Credit Memo' AS Activity,
    JE.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(JE.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE.CreatedByUser AS "User",
    JE.AccountingDocumentType AS DocumentType,
    JE.PaymentTerms,
    JE.NetDueDate AS DueDate,
    JE.ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE
WHERE JE.FinancialAccountType = 'D'
  AND JE.AccountingDocumentType IN ('DG') -- Credit Memo
  AND JE.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 10. Receive Full Payment */
SELECT 
    CAST(JE_INV.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Receive Full Payment' AS Activity,
    JE_PAY.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE_PAY.CompanyCode,
    JE_PAY.Customer AS CustomerNumber,
    CAST(JE_PAY.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE_PAY.CreatedByUser AS "User",
    JE_PAY.AccountingDocumentType AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    JE_INV.ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE_PAY
JOIN I_JournalEntryItem AS JE_INV ON JE_PAY.ClearingJournalEntry = JE_INV.AccountingDocument 
     AND JE_PAY.CompanyCode = JE_INV.CompanyCode
     AND JE_PAY.FiscalYear = JE_INV.FiscalYear
WHERE JE_PAY.AccountingDocumentType IN ('DZ', 'ZP')
  AND JE_INV.ClearingDate IS NOT NULL -- Fully cleared
  AND JE_PAY.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 11. Clear Invoice */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Clear Invoice' AS Activity,
    CAST(JE.ClearingDate AS TIMESTAMP) AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(JE.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE.CreatedByUser AS "User",
    'Clearing' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    JE.ClearingDate,
    'True' AS IsAutomated,
    DAYS_BETWEEN(JE.PostingDate, JE.ClearingDate) AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE
WHERE JE.FinancialAccountType = 'D'
  AND JE.ClearingDate IS NOT NULL
  AND JE.AccountingDocumentType IN ('RV', 'DR')
  AND JE.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 12. Reconcile Bank Statement */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Reconcile Bank Statement' AS Activity,
    BS.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(BS.AmountInTransactionCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    BS.CreatedByUser AS "User",
    'BankStmt' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    JE.ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_BankStatementLineItem AS BS
JOIN I_JournalEntryItem AS JE ON BS.BankStatementShortID = JE.Reference1IDByBusinessPartner -- Check mapping in your system
WHERE JE.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 13. Reverse Invoice */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Reverse Invoice' AS Activity,
    JE.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(JE.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE.CreatedByUser AS "User",
    JE.AccountingDocumentType AS DocumentType,
    JE.PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE
WHERE JE.IsReversal = 'X' 
  OR JE.IsReversed = 'X'
  AND JE.CompanyCode IN ('[Your Company Code]')

Steps

Identify the Data Source: This extraction uses SAP S/4HANA standard Core Data Services (CDS) views. The primary view is I_JournalEntryItem, which contains the granular line items for all financial transactions including invoices, payments, and clearing events.
Prepare the SQL Environment: Ensure you have SQL access to the SAP HANA database. This can be achieved through SAP HANA Studio, the DBACOCKPIT transaction, or an external ETL tool configured with an ODBC/JDBC connection to the S/4HANA instance.
Define Scope and Filters: Determine the Company Codes and Fiscal Years required for the extraction. The query below includes placeholders for these values to optimize performance and relevance.
Extract Financial Activities: The first block of the query targets the I_JournalEntryItem view. It filters for Account Type 'D' (Customer) to isolate Accounts Receivable. It distinguishes between Invoices, Credit Memos, and Payments based on the Financial Account Type and Document Type.
Extract Clearing and Settlement: The query performs a self-join or separate selection on I_JournalEntryItem to identify when an invoice was cleared. This creates the 'Clear Invoice' and 'Receive Full Payment' activities based on the clearing timestamp.
Integrate Operational Events: To capture non-financial activities like 'Send Payment Reminder' or 'Create Dispute Case', the query joins specialized views such as I_DunningHistory, I_DisputeCase, and I_OutputRequestItem. Note that Dispute Management and Output Management availability depends on your specific S/4HANA configuration.
Handle Bank Reconciliation: The 'Reconcile Bank Statement' activity is derived from I_BankStatementLineItem, linking the external bank statement to the internal accounting document.
Standardize Output Columns: The query casts all timestamps to a standard format and ensures all required columns (InvoiceNumber, Activity, EventTime) are populated. Monetary values are converted to a common float or decimal format.
Execute and Verify: Run the query against a small subset of data first (e.g., one Company Code, one month) to verify the logic. Check that the row count matches expected transaction volumes.
Export Data: Save the result set as a CSV or Parquet file. Ensure the encoding is set to UTF-8 to handle special characters in User Names or Text descriptions.
Import to ProcessMind: Upload the extracted file into ProcessMind. Map the 'InvoiceNumber' column to the Case ID field and 'EventTime' to the Timestamp field during the import configuration.

Configuration

Source Views: Uses standard S/4HANA CDS views (I_JournalEntryItem, I_DunningHistory, I_DisputeCase, I_OutputRequestItem).
Date Range: Recommended to extract data for the last 12 months to capture the full lifecycle of open invoices.
Filtering: Filter by Company Code (CompanyCode) and Fiscal Year (FiscalYear) to restrict data volume.
Account Type: Strictly filters for AccountType = 'D' to ensure only Customer/AR data is retrieved.
Performance: CDS views are virtual data models. For very large datasets, consider materializing the result or running the extraction during off-peak hours.
Customization: The logic assumes standard SAP Document Types (e.g., RV for Invoices, DZ for Payments). Adjust the CASE statements in the query if your organization uses custom Document Types.

a Sample Query sql

/* ACCOUNTS RECEIVABLE EXTRACTION - SAP S/4HANA CDS VIEWS */
/* METHOD: SQL Union of multiple CDS Views */

/* 1. Create AR Invoice */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Create AR Invoice' AS Activity,
    JE.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(JE.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE.CreatedByUser AS "User",
    JE.AccountingDocumentType AS DocumentType,
    JE.PaymentTerms,
    JE.NetDueDate AS DueDate,
    JE.ClearingDate,
    'False' AS IsAutomated,
    CASE 
        WHEN JE.ClearingDate IS NOT NULL THEN DAYS_BETWEEN(JE.PostingDate, JE.ClearingDate) 
        ELSE NULL 
    END AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE
WHERE JE.FinancialAccountType = 'D' -- Customer
  AND JE.AccountingDocumentType IN ('RV', 'DR') -- Invoice Types
  AND JE.Ledger = '0L' -- Leading Ledger
  AND JE.CompanyCode IN ('[Your Company Code]')
  AND JE.FiscalYear >= '[Your Start Year]'

UNION ALL

/* 2. Issue Invoice Output (Requires S/4 Output Management) */
SELECT 
    CAST(B.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Issue Invoice Output' AS Activity,
    O.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    B.CompanyCode,
    B.SoldToParty AS CustomerNumber,
    CAST(B.TotalNetAmount AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    O.CreatedByUser AS "User",
    B.BillingDocumentType AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_BillingDocument AS B
JOIN I_OutputRequestItem AS O ON O.BillingDocument = B.BillingDocument
WHERE B.CompanyCode IN ('[Your Company Code]')
  AND B.FiscalYear >= '[Your Start Year]'

UNION ALL

/* 3. Send Payment Reminder */
SELECT 
    CAST(D.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Send Payment Reminder' AS Activity,
    D.DunningDate AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    D.CompanyCode,
    D.Customer AS CustomerNumber,
    CAST(D.AmountInLocalCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    'System' AS "User",
    'Dunning' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_DunningHistory AS D
WHERE D.CompanyCode IN ('[Your Company Code]')
  AND D.DunningDate >= '[Your Start Date]'

UNION ALL

/* 4. Create Dispute Case (Requires FSCM) */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Create Dispute Case' AS Activity,
    DC.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    DC.CompanyCode,
    DC.Customer AS CustomerNumber,
    CAST(DC.DisputedAmount AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    DC.CreatedByUser AS "User",
    'Dispute' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_DisputeCase AS DC
JOIN I_JournalEntryItem AS JE ON DC.CompanyCode = JE.CompanyCode AND DC.Customer = JE.Customer
/* Note: Join logic simplified. Ideally link via Case ID reference in JE if configured */
WHERE DC.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 5. Update Dispute Status */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Update Dispute Status' AS Activity,
    DC.LastChangeDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    DC.CompanyCode,
    DC.Customer AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    DC.LastChangedByUser AS "User",
    'Dispute' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_DisputeCase AS DC
JOIN I_JournalEntryItem AS JE ON DC.CompanyCode = JE.CompanyCode AND DC.Customer = JE.Customer
WHERE DC.LastChangeDateTime IS NOT NULL
  AND DC.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 6. Resolve Dispute Case */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Resolve Dispute Case' AS Activity,
    DC.ClosingDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    DC.CompanyCode,
    DC.Customer AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    DC.LastChangedByUser AS "User",
    'Dispute' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_DisputeCase AS DC
JOIN I_JournalEntryItem AS JE ON DC.CompanyCode = JE.CompanyCode AND DC.Customer = JE.Customer
WHERE DC.ClosingDateTime IS NOT NULL
  AND DC.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 7. Record Promise To Pay */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Record Promise To Pay' AS Activity,
    PP.PromiseToPayDate AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    PP.CompanyCode,
    PP.Customer AS CustomerNumber,
    CAST(PP.PromiseAmount AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    PP.CreatedByUser AS "User",
    'Promise' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_PromiseToPay AS PP
JOIN I_JournalEntryItem AS JE ON PP.CompanyCode = JE.CompanyCode AND PP.Customer = JE.Customer
WHERE PP.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 8. Post Partial Payment (Logic: Payment Doc exists but invoice not fully cleared immediately) */
SELECT 
    CAST(JE_INV.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Post Partial Payment' AS Activity,
    JE_PAY.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE_PAY.CompanyCode,
    JE_PAY.Customer AS CustomerNumber,
    CAST(JE_PAY.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE_PAY.CreatedByUser AS "User",
    JE_PAY.AccountingDocumentType AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    JE_INV.ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE_PAY
JOIN I_JournalEntryItem AS JE_INV ON JE_PAY.ClearingJournalEntry = JE_INV.AccountingDocument 
     AND JE_PAY.CompanyCode = JE_INV.CompanyCode
     AND JE_PAY.FiscalYear = JE_INV.FiscalYear
WHERE JE_PAY.AccountingDocumentType IN ('DZ', 'ZP') -- Payment Types
  AND JE_INV.ClearingDate IS NULL -- Invoice still open
  AND JE_PAY.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 9. Post Credit Memo */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Post Credit Memo' AS Activity,
    JE.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(JE.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE.CreatedByUser AS "User",
    JE.AccountingDocumentType AS DocumentType,
    JE.PaymentTerms,
    JE.NetDueDate AS DueDate,
    JE.ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE
WHERE JE.FinancialAccountType = 'D'
  AND JE.AccountingDocumentType IN ('DG') -- Credit Memo
  AND JE.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 10. Receive Full Payment */
SELECT 
    CAST(JE_INV.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Receive Full Payment' AS Activity,
    JE_PAY.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE_PAY.CompanyCode,
    JE_PAY.Customer AS CustomerNumber,
    CAST(JE_PAY.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE_PAY.CreatedByUser AS "User",
    JE_PAY.AccountingDocumentType AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    JE_INV.ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE_PAY
JOIN I_JournalEntryItem AS JE_INV ON JE_PAY.ClearingJournalEntry = JE_INV.AccountingDocument 
     AND JE_PAY.CompanyCode = JE_INV.CompanyCode
     AND JE_PAY.FiscalYear = JE_INV.FiscalYear
WHERE JE_PAY.AccountingDocumentType IN ('DZ', 'ZP')
  AND JE_INV.ClearingDate IS NOT NULL -- Fully cleared
  AND JE_PAY.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 11. Clear Invoice */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Clear Invoice' AS Activity,
    CAST(JE.ClearingDate AS TIMESTAMP) AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(JE.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE.CreatedByUser AS "User",
    'Clearing' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    JE.ClearingDate,
    'True' AS IsAutomated,
    DAYS_BETWEEN(JE.PostingDate, JE.ClearingDate) AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE
WHERE JE.FinancialAccountType = 'D'
  AND JE.ClearingDate IS NOT NULL
  AND JE.AccountingDocumentType IN ('RV', 'DR')
  AND JE.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 12. Reconcile Bank Statement */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Reconcile Bank Statement' AS Activity,
    BS.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(BS.AmountInTransactionCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    BS.CreatedByUser AS "User",
    'BankStmt' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    JE.ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_BankStatementLineItem AS BS
JOIN I_JournalEntryItem AS JE ON BS.BankStatementShortID = JE.Reference1IDByBusinessPartner -- Check mapping in your system
WHERE JE.CompanyCode IN ('[Your Company Code]')

UNION ALL

/* 13. Reverse Invoice */
SELECT 
    CAST(JE.AccountingDocument AS VARCHAR(20)) AS InvoiceNumber,
    'Reverse Invoice' AS Activity,
    JE.CreationDateTime AS EventTime,
    'SAP S/4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    JE.CompanyCode,
    JE.Customer AS CustomerNumber,
    CAST(JE.AmountInCompanyCodeCurrency AS DECIMAL(19,2)) AS AmountInLocalCurrency,
    JE.CreatedByUser AS "User",
    JE.AccountingDocumentType AS DocumentType,
    JE.PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM I_JournalEntryItem AS JE
WHERE JE.IsReversal = 'X' 
  OR JE.IsReversed = 'X'
  AND JE.CompanyCode IN ('[Your Company Code]')

Steps

Prerequisites Setup: Ensure Python 3.x is installed on the extraction machine. Install the SAP NetWeaver RFC SDK (required for PyRFC) and the Python libraries pyrfc and pandas using pip.
SAP User Configuration: Verify the SAP user account has S_RFC (for remote function calls) and S_TABU_DIS (for table reading) authorizations. The user requires access to function module RFC_READ_TABLE.
Network Configuration: Ensure the machine running the script can reach the SAP application server ports (typically 33xx) and that firewalls permit this traffic.
Script Configuration: Open the Python script provided in the Query section. Update the sap_config dictionary with your specific Application Server Host (ASHOST), System Number (SYSNR), Client (CLIENT), User, and Password.
Filter Definition: Modify the TARGET_BUKRS (Company Code) and TARGET_GJAHR (Fiscal Year) variables in the script to limit the data scope. This is critical to prevent timeouts when reading large tables like BSEG.
Execution: Run the script via the command line (e.g., python extract_ar.py). The script will verify the connection and begin downloading table chunks sequentially.
Data Transformation: The script uses Pandas to merge the raw SAP tables (BKPF, BSEG, NAST, MHND, UDM tables, FEBEP) into a unified event log. It handles date conversions and logic to derive activities like 'Post Partial Payment' versus 'Receive Full Payment' based on clearing logic.
CSV Output: Upon completion, the script generates a file named AR_Event_Log.csv in the current directory.
Validation: Open the CSV file and verify that the EventTime and Activity columns are populated correctly. Check that specific Invoice Numbers have multiple rows corresponding to their lifecycle (Creation -> Output -> Payment).
Import to ProcessMind: Upload the AR_Event_Log.csv file to ProcessMind. Map InvoiceNumber to Case ID, Activity to Activity, and EventTime to Timestamp.

Configuration

RFC_READ_TABLE Limits: The standard SAP function module has a row width limit (usually 512 bytes). The script explicitly selects only necessary columns to stay within this limit.
Fiscal Year Filter: Always filter by GJAHR (Fiscal Year) when querying financial tables (BKPF, BSEG) to maintain performance.
Company Code: restricting by BUKRS is mandatory for multi-entity SAP instances.
Chunking: For very large datasets, the script might require implementation of ROWCOUNT and SKIP options in the read_table function to paginate results (omitted here for brevity but recommended for production).
FSCM Availability: The script attempts to read UDM_ tables for Dispute Management. If these modules are not active in your S/4HANA system, these sections of the script will return empty frames or should be commented out.
Date Handling: SAP dates are stored as YYYYMMDD strings. The script converts these to Python datetime objects automatically.

a Sample Query python

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

class=class="hl-string">"hl-comment"># === CONFIGURATION ===
sap_config = {
    class="hl-string">'user': class="hl-string">'YOUR_USER',
    class="hl-string">'passwd': class="hl-string">'YOUR_PASSWORD',
    class="hl-string">'ashost': class="hl-string">'192.168.0.1',  class=class="hl-string">"hl-comment"># SAP App Server IP
    class="hl-string">'sysnr': class="hl-string">'00',            class=class="hl-string">"hl-comment"># Instance Number
    class="hl-string">'client': class="hl-string">'100',          class=class="hl-string">"hl-comment"># Client
    class="hl-string">'lang': class="hl-string">'EN'
}

TARGET_BUKRS = class="hl-string">'1000'  class=class="hl-string">"hl-comment"># Company Code
TARGET_GJAHR = class="hl-string">'2023'  class=class="hl-string">"hl-comment"># Fiscal Year

class=class="hl-string">"hl-comment"># === HELPER FUNCTIONS ===
def read_table(conn, table_name, fields, options=[]):
    class="hl-string">"""Reads an SAP table via RFC_READ_TABLE."""
    print(fclass="hl-string">"Reading {table_name}...")
    try:
        result = conn.call(class="hl-string">'RFC_READ_TABLE',
                           QUERY_TABLE=table_name,
                           DELIMITER=class="hl-string">';',
                           FIELDS=[{class="hl-string">'FIELDNAME': f} for f in fields],
                           OPTIONS=[{class="hl-string">'TEXT': o} for o in options])
        data_str = result[class="hl-string">'DATA']
        headers = [f[class="hl-string">'FIELDNAME'] for f in result[class="hl-string">'FIELDS']]
        
        class=class="hl-string">"hl-comment"># Parse delimiter separated values
        rows = [row[class="hl-string">'WA'].split(class="hl-string">';') for row in data_str]
        df = pd.DataFrame(rows, columns=headers)
        return df
    except Exception as e:
        print(fclass="hl-string">"Error reading {table_name}: {e}")
        return pd.DataFrame(columns=fields)

def sap_date_to_datetime(date_str, time_str=class="hl-string">'000000'):
    class="hl-string">"""Converts SAP YYYYMMDD and HHMMSS strings to datetime."""
    try:
        return pd.to_datetime(date_str + time_str, format=class="hl-string">'%Y%m%d%H%M%S')
    except:
        return pd.NaT

class=class="hl-string">"hl-comment"># === MAIN EXTRACTION ===
def main():
    conn = Connection(**sap_config)
    
    class=class="hl-string">"hl-comment"># 1. Fetch Header Data (BKPF) - Invoices and Payments
    class=class="hl-string">"hl-comment"># Document Types for Invoices: DR (Customer Invoice), RV (Billing Doc)
    fields_bkpf = [class="hl-string">'BUKRS', class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BLART', class="hl-string">'CPUDT', class="hl-string">'CPUTM', class="hl-string">'USNAM', class="hl-string">'WAERS', class="hl-string">'STBLG']
    options_bkpf = [fclass="hl-string">"BUKRS = '{TARGET_BUKRS}' AND GJAHR = '{TARGET_GJAHR}'"]
    df_bkpf = read_table(conn, class="hl-string">'BKPF', fields_bkpf, options_bkpf)

    class=class="hl-string">"hl-comment"># 2. Fetch Segment Data (BSEG) - Customer Line Items (KOART = D)
    fields_bseg = [class="hl-string">'BUKRS', class="hl-string">'BELNR', class="hl-string">'GJAHR', class="hl-string">'BUZEI', class="hl-string">'KOART', class="hl-string">'KUNNR', class="hl-string">'WRBTR', class="hl-string">'AUGBL', class="hl-string">'AUGDT', class="hl-string">'ZLSPR', class="hl-string">'ZTERM', class="hl-string">'SKFBT']
    class=class="hl-string">"hl-comment"># Note: Querying BSEG directly can be slow. Ensure filters are strict.
    options_bseg = [fclass="hl-string">"BUKRS = '{TARGET_BUKRS}' AND GJAHR = '{TARGET_GJAHR}' AND KOART = 'D'"]
    df_bseg = read_table(conn, class="hl-string">'BSEG', fields_bseg, options_bseg)

    class=class="hl-string">"hl-comment"># Merge Header and Item to form base document list
    df_docs = pd.merge(df_bseg, df_bkpf, on=[class="hl-string">'BUKRS', class="hl-string">'BELNR', class="hl-string">'GJAHR'])
    
    class=class="hl-string">"hl-comment"># Filter strictly for AR Invoices (Simplification: Doc Types DR, RV)
    df_invoices = df_docs[df_docs[class="hl-string">'BLART'].isin([class="hl-string">'DR', class="hl-string">'RV', class="hl-string">'DG'])].copy()

    class=class="hl-string">"hl-comment"># === ACTIVITY GENERATION ===
    events = []

    class=class="hl-string">"hl-comment"># Activity 1: Create AR Invoice
    for _, row in df_invoices.iterrows():
        timestamp = sap_date_to_datetime(row[class="hl-string">'CPUDT'], row[class="hl-string">'CPUTM'])
        events.append({
            class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
            class="hl-string">'Activity': class="hl-string">'Create AR Invoice',
            class="hl-string">'EventTime': timestamp,
            class="hl-string">'SourceSystem': class="hl-string">'SAP_S4',
            class="hl-string">'CompanyCode': row[class="hl-string">'BUKRS'],
            class="hl-string">'CustomerNumber': row[class="hl-string">'KUNNR'],
            class="hl-string">'AmountInLocalCurrency': row[class="hl-string">'WRBTR'],
            class="hl-string">'User': row[class="hl-string">'USNAM'],
            class="hl-string">'DocumentType': row[class="hl-string">'BLART']
        })

    class=class="hl-string">"hl-comment"># Activity 2: Issue Invoice Output (NAST)
    class=class="hl-string">"hl-comment"># Get unique Invoice numbers to filter NAST
    invoice_nums = df_invoices[class="hl-string">'BELNR'].unique().tolist()
    if invoice_nums:
        class=class="hl-string">"hl-comment"># NAST keys: KAPPL=class="hl-string">'V3' (Billing), OBJKY = Invoice Number
        class=class="hl-string">"hl-comment"># Note: In production, batch this query if list is long
        fields_nast = [class="hl-string">'OBJKY', class="hl-string">'KSCHL', class="hl-string">'DATVR', class="hl-string">'UHRVR', class="hl-string">'USNAM']
        options_nast = [class="hl-string">"KAPPL = 'V3'"] class=class="hl-string">"hl-comment"># Add OBJKY IN (...) logic for optimization
        df_nast = read_table(conn, class="hl-string">'NAST', fields_nast, options_nast)
        
        for _, row in df_nast.iterrows():
            if row[class="hl-string">'OBJKY'] in invoice_nums:
                events.append({
                    class="hl-string">'InvoiceNumber': row[class="hl-string">'OBJKY'],
                    class="hl-string">'Activity': class="hl-string">'Issue Invoice Output',
                    class="hl-string">'EventTime': sap_date_to_datetime(row[class="hl-string">'DATVR'], row[class="hl-string">'UHRVR']),
                    class="hl-string">'SourceSystem': class="hl-string">'SAP_S4',
                    class="hl-string">'User': row[class="hl-string">'USNAM']
                })

    class=class="hl-string">"hl-comment"># Activity 3: Send Payment Reminder (MHND/MHNK)
    fields_mhnd = [class="hl-string">'LAUFD', class="hl-string">'LAUFI', class="hl-string">'KUNNR', class="hl-string">'BUKRS', class="hl-string">'KOART']
    class=class="hl-string">"hl-comment"># MHND links dunning run to customer, difficult to link exact invoice item in RFC without join
    class=class="hl-string">"hl-comment"># We will approximate by reading MHND (Dunning Data) where KOART=D
    class=class="hl-string">"hl-comment"># To get Invoice link, we need to know if item is in dunning. 
    class=class="hl-string">"hl-comment"># Simplified: Assuming we can fetch MHND for our customers.
    options_mhnd = [fclass="hl-string">"BUKRS = '{TARGET_BUKRS}' AND KOART = 'D'"]
    df_mhnd = read_table(conn, class="hl-string">'MHND', fields_mhnd + [class="hl-string">'BELNR'], options_mhnd)
    
    for _, row in df_mhnd.iterrows():
        if row[class="hl-string">'BELNR'] in invoice_nums:
            events.append({
                class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
                class="hl-string">'Activity': class="hl-string">'Send Payment Reminder',
                class="hl-string">'EventTime': sap_date_to_datetime(row[class="hl-string">'LAUFD']), class=class="hl-string">"hl-comment"># Dunning date
                class="hl-string">'SourceSystem': class="hl-string">'SAP_S4',
                class="hl-string">'User': class="hl-string">'SYSTEM'
            })

    class=class="hl-string">"hl-comment"># Activity 4, 5, 6: Dispute Management (UDM_CASE_ATTR)
    class=class="hl-string">"hl-comment"># Requires FSCM. Linking via UDM_RELATION or knowing CASE_GUID.
    class=class="hl-string">"hl-comment"># We assume class="hl-string">'FIN_DISPUTE' case type.
    fields_udm = [class="hl-string">'CASE_GUID', class="hl-string">'CASE_ID', class="hl-string">'FIN_KUNNR', class="hl-string">'CREATE_TIME', class="hl-string">'CLOSE_TIME', class="hl-string">'ESCAL_REASON', class="hl-string">'PROCESSOR']
    df_udm = read_table(conn, class="hl-string">'UDM_CASE_ATTR', fields_udm, options=[])
    
    class=class="hl-string">"hl-comment"># UDM_RELATION links Case to Object (Invoice)
    fields_rel = [class="hl-string">'CASE_GUID', class="hl-string">'OBJ_KEY']
    df_rel = read_table(conn, class="hl-string">'UDM_RELATION', fields_rel, options=[])
    
    class=class="hl-string">"hl-comment"># Merge Dispute info
    df_disputes = pd.merge(df_udm, df_rel, on=class="hl-string">'CASE_GUID')
    
    for _, row in df_disputes.iterrows():
        inv_num = row[class="hl-string">'OBJ_KEY'] class=class="hl-string">"hl-comment"># Needs parsing usually (RefKey), assuming raw match for simplicity
        if inv_num in invoice_nums:
            class=class="hl-string">"hl-comment"># 4. Create Dispute Case
            events.append({
                class="hl-string">'InvoiceNumber': inv_num,
                class="hl-string">'Activity': class="hl-string">'Create Dispute Case',
                class="hl-string">'EventTime': pd.to_datetime(row[class="hl-string">'CREATE_TIME']),
                class="hl-string">'SourceSystem': class="hl-string">'SAP_S4',
                class="hl-string">'User': row[class="hl-string">'PROCESSOR']
            })
            class=class="hl-string">"hl-comment"># 5. Update Dispute Status (Approximation via Change Time or attribute check)
            class=class="hl-string">"hl-comment"># Omitted for brevity: requires CDHDR/CDPOS lookup
            
            class=class="hl-string">"hl-comment"># 6. Resolve Dispute Case
            if pd.notna(row[class="hl-string">'CLOSE_TIME']) and row[class="hl-string">'CLOSE_TIME'] != class="hl-string">'0.0000000E+00':
                 events.append({
                    class="hl-string">'InvoiceNumber': inv_num,
                    class="hl-string">'Activity': class="hl-string">'Resolve Dispute Case',
                    class="hl-string">'EventTime': pd.to_datetime(row[class="hl-string">'CLOSE_TIME']),
                    class="hl-string">'SourceSystem': class="hl-string">'SAP_S4'
                })

    class=class="hl-string">"hl-comment"># Activities 8-11: Payments and Clearing
    class=class="hl-string">"hl-comment"># We look at the class="hl-string">'AUGBL' (Clearing Doc) in the Invoice BSEG row.
    for _, row in df_invoices.iterrows():
        if row[class="hl-string">'AUGBL'] and row[class="hl-string">'AUGBL'] != class="hl-string">'':
            clearing_doc = row[class="hl-string">'AUGBL']
            clearing_date = row[class="hl-string">'AUGDT']
            
            class=class="hl-string">"hl-comment"># 11. Clear Invoice
            events.append({
                class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
                class="hl-string">'Activity': class="hl-string">'Clear Invoice',
                class="hl-string">'EventTime': sap_date_to_datetime(clearing_date),
                class="hl-string">'SourceSystem': class="hl-string">'SAP_S4',
                class="hl-string">'ClearingDate': sap_date_to_datetime(clearing_date)
            })
            
            class=class="hl-string">"hl-comment"># Analyze Clearing Document to determine if it was Payment or Credit Memo
            class=class="hl-string">"hl-comment"># Fetch the header of the clearing document
            class=class="hl-string">"hl-comment"># In a real run, batch these fetches. Here we simulate availability.
            class=class="hl-string">"hl-comment"># We check if we already fetched it in df_bkpf (if within same year)
            clear_header = df_bkpf[(df_bkpf[class="hl-string">'BELNR'] == clearing_doc) & (df_bkpf[class="hl-string">'BUKRS'] == row[class="hl-string">'BUKRS'])]
            
            if not clear_header.empty:
                doc_type = clear_header.iloc[0][class="hl-string">'BLART']
                class=class="hl-string">"hl-comment"># 8. Post Partial Payment / 10. Receive Full Payment
                if doc_type in [class="hl-string">'DZ', class="hl-string">'ZP']: class=class="hl-string">"hl-comment"># Payment Types
                    class=class="hl-string">"hl-comment"># Logic: If residual items exist, it's Partial. 
                    class=class="hl-string">"hl-comment"># Check if clearing doc created new open items. Complex in basic script.
                    class=class="hl-string">"hl-comment"># Simplified: Assume Full Payment for standard clearing.
                    events.append({
                        class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
                        class="hl-string">'Activity': class="hl-string">'Receive Full Payment',
                        class="hl-string">'EventTime': sap_date_to_datetime(clearing_date),
                        class="hl-string">'SourceSystem': class="hl-string">'SAP_S4',
                        class="hl-string">'DocumentType': doc_type
                    })
                class=class="hl-string">"hl-comment"># 9. Post Credit Memo
                elif doc_type == class="hl-string">'DG':
                     events.append({
                        class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
                        class="hl-string">'Activity': class="hl-string">'Post Credit Memo',
                        class="hl-string">'EventTime': sap_date_to_datetime(clearing_date),
                        class="hl-string">'SourceSystem': class="hl-string">'SAP_S4'
                    })

    class=class="hl-string">"hl-comment"># Activity 12: Reconcile Bank Statement (FEBEP)
    class=class="hl-string">"hl-comment"># Check for Electronic Bank Statement items matching the Payment Doc
    class=class="hl-string">"hl-comment"># Requires knowing the Payment Doc number (AUGBL from invoice)
    fields_febep = [class="hl-string">'KUKEY', class="hl-string">'ESNUM', class="hl-string">'NBBLN']
    options_febep = [fclass="hl-string">"BUKRS = '{TARGET_BUKRS}'"] 
    df_febep = read_table(conn, class="hl-string">'FEBEP', fields_febep, options_febep)
    
    for _, row in df_invoices.iterrows():
        payment_doc = row[class="hl-string">'AUGBL']
        if payment_doc:
            match = df_febep[df_febep[class="hl-string">'NBBLN'] == payment_doc]
            if not match.empty:
                events.append({
                    class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
                    class="hl-string">'Activity': class="hl-string">'Reconcile Bank Statement',
                    class="hl-string">'EventTime': sap_date_to_datetime(row[class="hl-string">'AUGDT']), class=class="hl-string">"hl-comment"># Proxy time
                    class="hl-string">'SourceSystem': class="hl-string">'SAP_S4'
                })

    class=class="hl-string">"hl-comment"># Activity 13: Reverse Invoice
    class=class="hl-string">"hl-comment"># Check if STBLG (Reversal Doc) is filled in header
    for _, row in df_invoices.iterrows():
        if row[class="hl-string">'STBLG'] and row[class="hl-string">'STBLG'] != class="hl-string">'':
             events.append({
                class="hl-string">'InvoiceNumber': row[class="hl-string">'BELNR'],
                class="hl-string">'Activity': class="hl-string">'Reverse Invoice',
                class="hl-string">'EventTime': sap_date_to_datetime(row[class="hl-string">'CPUDT']), class=class="hl-string">"hl-comment"># Using updated time
                class="hl-string">'SourceSystem': class="hl-string">'SAP_S4'
            })

    class=class="hl-string">"hl-comment"># 7. Record Promise To Pay (UDM_P2P_ATTR)
    class=class="hl-string">"hl-comment"># Similar to Dispute, requires P2P table fetch
    class=class="hl-string">"hl-comment"># Implementation logic similar to UDM_CASE_ATTR

    class=class="hl-string">"hl-comment"># === EXPORT ===
    df_events = pd.DataFrame(events)
    df_events[class="hl-string">'LastDataUpdate'] = datetime.now()
    
    print(fclass="hl-string">"Extracted {len(df_events)} events.")
    df_events.to_csv(class="hl-string">'AR_Event_Log.csv', index=False)

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

Steps

Prerequisites Verification: Ensure you have the Theobald Xtract Universal Designer installed and a valid connection to your SAP S/4HANA source system configured. Verify that the SAP user account used for the connection has read authorizations (S_TABU_DIS or S_TABU_NAM) for tables BKPF, BSEG, BSAD, NAST, MHND, SCMG_T_CASE_ATTR, UDM_P2P_ATTR, and FEBEP.
Create New Extraction: Open Xtract Universal Designer. Click New to create a new extraction. Name it AR_Process_Mining_EventLog. Select your SAP Source system.
Select Component Type: Choose the Custom SQL component type. This allows us to execute a UNION ALL query to fetch various activities from different SAP tables in a single pass.
Define the Query: Copy the code provided in the Query section of this documentation. Paste it into the SQL editor window within the Xtract Universal Designer.
Configure Parameters: In the 'Parameters' tab of the extraction, define a parameter named YEAR_START (e.g., '20230101') to filter data and optimize performance. Update the WHERE clauses in the SQL script to reference this parameter if you wish to make the script dynamic, or manually replace the placeholder dates in the script.
Set Output Destination: Configure your desired destination (e.g., Local CSV, Azure Blob, AWS S3, or SQL Server). Map the output columns defined in the SQL (InvoiceNumber, Activity, etc.) to the destination fields. Ensure date fields are formatted as YYYY-MM-DD and timestamps as YYYY-MM-DD HH:MM:SS if the destination requires specific typing.
Handling Large Data: For the initial load, it is recommended to run the extraction in chunks (e.g., by Fiscal Year). For daily deltas, modify the WHERE clause to filter on CPUDT (Entry Date) or AEDAT (Change Date) relative to the system date (e.g., BKPF.CPUDT >= sy-datum - 1).
Preview and Validate: Click the Run or Preview button in Xtract Universal to fetch the first 100 rows. Verify that the Activity column contains values like 'Create AR Invoice' and 'Clear Invoice'. Check that InvoiceNumber is populated consistently.
Run Extraction: Execute the full extraction. Monitor the Xtract Universal log for any timeouts or memory errors.
Post-Processing: Import the resulting file into ProcessMind. During the import mapping, set InvoiceNumber as the Case ID, Activity as the Activity Name, and EventTime as the Timestamp.

Configuration

Extraction Type: Custom SQL (Legacy) or Table Join. Custom SQL is preferred for shaping the event log directly in the source.
Package Size: Set the package size to 20,000 to 50,000 rows. SAP S/4HANA can handle larger fetches, but network stability is often the bottleneck.
Timeout: Increase the standard timeout to 600 seconds or more if extracting large historical volumes.
Where Clause Filters:
- Filter by BUKRS (Company Code) to restrict data to relevant entities.
- Filter by GJAHR (Fiscal Year) to limit historical scope.
Column Type Conversion: Ensure the AmountInLocalCurrency is treated as a Decimal/Double in the destination settings to preserve cents.
Delta Logic: For incremental loads, use the SAP server variable @sy-datum in the Custom SQL logic if supported, or pass a dynamic parameter from the calling workflow tool.

a Sample Query config

SELECT 
    BKPF.BELNR AS InvoiceNumber,
    'Create AR Invoice' AS Activity,
    CONCAT(BKPF.CPUDT, BKPF.CPUTM) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BKPF.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    BSEG.DMBTR AS AmountInLocalCurrency,
    BKPF.USNAM AS "User",
    BKPF.BLART AS DocumentType,
    BSEG.ZTERM AS PaymentTerms,
    BSEG.ZFBDT AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    0 AS DaysSalesOutstanding
FROM BKPF
INNER JOIN BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE BKPF.BLART IN ('DR', 'RV', 'RE') 
  AND BSEG.KOART = 'D' 
  AND BKPF.GJAHR >= '2023' -- [Configure based on your system]

UNION ALL

SELECT 
    LEFT(NAST.OBJKY, 10) AS InvoiceNumber,
    'Issue Invoice Output' AS Activity,
    CONCAT(NAST.DATVR, NAST.UHRVR) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NAST.KAPPL AS CompanyCode,
    NAST.PARNR AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    NAST.USNAM AS "User",
    NAST.KSCHL AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM NAST
WHERE NAST.KAPPL = 'V3' 
  AND NAST.OBJKY IS NOT NULL
  AND NAST.DATVR >= '20230101' -- [Configure based on your system]

UNION ALL

SELECT 
    MHND.BELNR AS InvoiceNumber,
    'Send Payment Reminder' AS Activity,
    CONCAT(MHND.LAUFD, '120000') AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    MHND.BUKRS AS CompanyCode,
    MHND.KUNNR AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    NULL AS "User",
    MHND.MANSP AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM MHND
WHERE MHND.KOART = 'D'
  AND MHND.LAUFD >= '20230101' -- [Configure based on your system]

UNION ALL

SELECT 
    SCMG.EXTERNAL_KEY AS InvoiceNumber,
    'Create Dispute Case' AS Activity,
    CONCAT(SUBSTRING(SCMG.CREATE_TIME, 1, 8), SUBSTRING(SCMG.CREATE_TIME, 9, 6)) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    NULL AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    SCMG.CREATE_USER AS "User",
    SCMG.CASE_TYPE AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM SCMG_T_CASE_ATTR AS SCMG
WHERE SCMG.CASE_TYPE = 'F_01'

UNION ALL

SELECT 
    SCMG.EXTERNAL_KEY AS InvoiceNumber,
    'Update Dispute Status' AS Activity,
    CONCAT(SUBSTRING(SCMG.CHANGE_TIME, 1, 8), SUBSTRING(SCMG.CHANGE_TIME, 9, 6)) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    NULL AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    SCMG.CHANGE_USER AS "User",
    SCMG.STAT_ORDERNO AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM SCMG_T_CASE_ATTR AS SCMG
WHERE SCMG.CASE_TYPE = 'F_01' AND SCMG.CHANGE_TIME > SCMG.CREATE_TIME

UNION ALL

SELECT 
    SCMG.EXTERNAL_KEY AS InvoiceNumber,
    'Resolve Dispute Case' AS Activity,
    CONCAT(SUBSTRING(SCMG.CHANGE_TIME, 1, 8), SUBSTRING(SCMG.CHANGE_TIME, 9, 6)) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    NULL AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    SCMG.CHANGE_USER AS "User",
    SCMG.STAT_ORDERNO AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM SCMG_T_CASE_ATTR AS SCMG
WHERE SCMG.CASE_TYPE = 'F_01' AND SCMG.STAT_ORDERNO >= '30' -- [Adjust status code for Closed]

UNION ALL

SELECT 
    P2P.INVOICE_REF AS InvoiceNumber,
    'Record Promise To Pay' AS Activity,
    CONCAT(SUBSTRING(P2P.PROMISE_TIME, 1, 8), SUBSTRING(P2P.PROMISE_TIME, 9, 6)) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    NULL AS CompanyCode,
    P2P.CUSTOMER AS CustomerNumber,
    P2P.AMOUNT_PROMISED AS AmountInLocalCurrency,
    P2P.USER_CREATED AS "User",
    P2P.STATE AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM UDM_P2P_ATTR AS P2P

UNION ALL

SELECT 
    BKPF.BELNR AS InvoiceNumber,
    'Post Partial Payment' AS Activity,
    CONCAT(BKPF.CPUDT, BKPF.CPUTM) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BKPF.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    BSEG.DMBTR AS AmountInLocalCurrency,
    BKPF.USNAM AS "User",
    BKPF.BLART AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BKPF
INNER JOIN BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE BKPF.BLART = 'DZ' AND BSEG.XREF3 IS NOT NULL -- [Assumption: XREF3 implies partial link, or customize logic]

UNION ALL

SELECT 
    BSEG.REBZG AS InvoiceNumber, -- Reference to original invoice
    'Post Credit Memo' AS Activity,
    CONCAT(BKPF.CPUDT, BKPF.CPUTM) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BKPF.BUKRS AS CompanyCode,
    BSEG.KUNNR AS CustomerNumber,
    BSEG.DMBTR AS AmountInLocalCurrency,
    BKPF.USNAM AS "User",
    BKPF.BLART AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BKPF
INNER JOIN BSEG ON BKPF.BUKRS = BSEG.BUKRS AND BKPF.BELNR = BSEG.BELNR AND BKPF.GJAHR = BSEG.GJAHR
WHERE BKPF.BLART = 'DG' AND BSEG.REBZG IS NOT NULL

UNION ALL

SELECT 
    BSAD.BELNR AS InvoiceNumber,
    'Receive Full Payment' AS Activity,
    CONCAT(BKPF.CPUDT, BKPF.CPUTM) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BKPF.BUKRS AS CompanyCode,
    BSAD.KUNNR AS CustomerNumber,
    BSAD.DMBTR AS AmountInLocalCurrency,
    BKPF.USNAM AS "User",
    BKPF.BLART AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    BSAD.AUGDT AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BKPF
INNER JOIN BSAD ON BKPF.BUKRS = BSAD.BUKRS AND BKPF.BELNR = BSAD.AUGBL AND BKPF.GJAHR = BSAD.AUGGJ
WHERE BKPF.BLART = 'DZ' -- Customer Payment

UNION ALL

SELECT 
    BSAD.BELNR AS InvoiceNumber,
    'Clear Invoice' AS Activity,
    CONCAT(BSAD.AUGDT, '235959') AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BSAD.BUKRS AS CompanyCode,
    BSAD.KUNNR AS CustomerNumber,
    BSAD.DMBTR AS AmountInLocalCurrency,
    NULL AS "User",
    BSAD.BLART AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    BSAD.AUGDT AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BSAD
WHERE BSAD.AUGDT >= '20230101' -- [Configure based on your system]

UNION ALL

SELECT 
    BSEG.BELNR AS InvoiceNumber,
    'Reconcile Bank Statement' AS Activity,
    CONCAT(FEB.BUDAT, '120000') AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    FEB.BUKRS AS CompanyCode,
    NULL AS CustomerNumber,
    FEB.KWERT AS AmountInLocalCurrency,
    NULL AS "User",
    'EBS' AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'True' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM FEBEP AS FEB
INNER JOIN BSEG ON FEB.BELNR = BSEG.BELNR AND FEB.BUKRS = BSEG.BUKRS
WHERE FEB.BUDAT >= '20230101' -- [Configure based on your system]

UNION ALL

SELECT 
    BKPF.BELNR AS InvoiceNumber,
    'Reverse Invoice' AS Activity,
    CONCAT(BKPF.CPUDT, BKPF.CPUTM) AS EventTime,
    'SAP_S4' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate,
    BKPF.BUKRS AS CompanyCode,
    NULL AS CustomerNumber,
    NULL AS AmountInLocalCurrency,
    BKPF.USNAM AS "User",
    BKPF.BLART AS DocumentType,
    NULL AS PaymentTerms,
    NULL AS DueDate,
    NULL AS ClearingDate,
    'False' AS IsAutomated,
    NULL AS DaysSalesOutstanding
FROM BKPF
WHERE BKPF.STBLG IS NOT NULL AND BKPF.STBLG <> ''

Your Accounts Receivable Data Template

Your Accounts Receivable Data Template

Accounts Receivable Attributes

Accounts Receivable Activities

Extraction Guides

Standard SAP S/4HANA CDS View Data Extraction

Steps

Configuration

a Sample Query sql

Direct SAP HANA SQL Query on ACDOCA Table

Steps

Configuration

a Sample Query sql

Python Extraction Script using PyRFC Connector

Steps

Configuration

a Sample Query python

Theobald Xtract Universal SAP Connector Usage

Steps

Configuration

a Sample Query config

Ready to Get Started?

Fix SAP S/4HANA Accounts Receivable and Boost Cash Flow

Your Accounts Receivable Data Template

Your Accounts Receivable Data Template

Accounts Receivable Attributes

Accounts Receivable Activities

Extraction Guides

Standard SAP S/4HANA CDS View Data Extraction

Steps

Configuration

a Sample Query sql

Direct SAP HANA SQL Query on ACDOCA Table

Steps

Configuration

a Sample Query sql

Python Extraction Script using PyRFC Connector

Steps

Configuration

a Sample Query python

Theobald Xtract Universal SAP Connector Usage

Steps

Configuration

a Sample Query config

We value your privacy

Ready to Get Started?

Fix SAP S/4HANA Accounts Receivable and Boost Cash Flow