Datatemplate voor debiteurenbeheer

/* 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]')

WITH
params AS (
    SELECT
        CAST('[Start date and time]' AS TIMESTAMP) AS start_ts,
        CAST('[End date and time]' AS TIMESTAMP) AS end_ts,
        CAST('[Last data update timestamp]' AS TIMESTAMP) AS last_data_update
    FROM DUMMY
),
base_acdoca AS (
    SELECT
        a.RBUKRS AS CompanyCode,
        a.BELNR AS AccountingDocumentNumber,
        a.GJAHR AS FiscalYear,
        a.BUZEI AS LineItem,
        a.KUNNR AS CustomerNumber,
        a.HSL AS AmountInLocalCurrency,
        a.BLART AS DocumentType,
        a.ZTERM AS PaymentTerms,
        a.BUDAT AS PostingDate,
        a.AUGDT AS ClearingDate,
        a.AUGBL AS ClearingDocumentNumber,
        a.USNAM AS UserName,
        a.BELNR AS InvoiceNumber,
        CAST(a.BUDAT AS TIMESTAMP) AS PostingTimestamp
    FROM ACDOCA a
    CROSS JOIN params p
    WHERE CAST(a.BUDAT AS TIMESTAMP) >= p.start_ts
      AND CAST(a.BUDAT AS TIMESTAMP) < p.end_ts
      AND a.RBUKRS = '[Company Code filter]'
),
base_bkpf AS (
    SELECT
        b.BUKRS AS CompanyCode,
        b.BELNR AS AccountingDocumentNumber,
        b.GJAHR AS FiscalYear,
        b.CPUDT AS CreationDate,
        b.CPUTM AS CreationTime,
        b.USNAM AS UserName,
        b.BLART AS DocumentType
    FROM BKPF b
),
invoice_events AS (
    SELECT
        x.InvoiceNumber,
        'Create AR Invoice' AS Activity,
        CAST(TO_TIMESTAMP(TO_VARCHAR(h.CreationDate) || TO_VARCHAR(h.CreationTime), 'YYYYMMDDHH24MISS') AS TIMESTAMP) AS EventTime,
        'SAP S/4HANA' AS SourceSystem,
        p.last_data_update AS LastDataUpdate,
        x.CompanyCode,
        x.CustomerNumber,
        x.AmountInLocalCurrency,
        COALESCE(h.UserName, x.UserName) AS UserName,
        x.DocumentType,
        x.PaymentTerms,
        CAST(NULL AS DATE) AS DueDate,
        x.ClearingDate,
        CAST(NULL AS NVARCHAR(1)) AS IsAutomated,
        CAST(NULL AS DECIMAL(18,2)) AS DaysSalesOutstanding
    FROM base_acdoca x
    INNER JOIN base_bkpf h ON h.CompanyCode = x.CompanyCode AND h.AccountingDocumentNumber = x.AccountingDocumentNumber AND h.FiscalYear = x.FiscalYear
    CROSS JOIN params p
    WHERE x.CustomerNumber IS NOT NULL
),
output_events AS (
    SELECT InvoiceNumber, 'Issue Invoice Output' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved invoice output source object]
    WHERE EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
dunning_events AS (
    SELECT InvoiceNumber, 'Send Payment Reminder' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved dunning source object]
    WHERE EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
dispute_create_events AS (
    SELECT InvoiceNumber, 'Create Dispute Case' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved dispute case source object]
    WHERE ActivityCode = '[Dispute creation activity code]' AND EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
dispute_update_events AS (
    SELECT InvoiceNumber, 'Update Dispute Status' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved dispute status history source object]
    WHERE ActivityCode = '[Dispute status update activity code]' AND EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
dispute_resolve_events AS (
    SELECT InvoiceNumber, 'Resolve Dispute Case' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved dispute case source object]
    WHERE ActivityCode = '[Dispute resolution activity code]' AND EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
promise_events AS (
    SELECT InvoiceNumber, 'Record Promise To Pay' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved promise to pay source object]
    WHERE EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
partial_payment_events AS (
    SELECT InvoiceNumber, 'Post Partial Payment' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved payment relationship source object]
    WHERE PaymentClassification = '[Partial payment classification]' AND EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
credit_memo_events AS (
    SELECT InvoiceNumber, 'Post Credit Memo' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved credit memo relationship source object]
    WHERE EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
full_payment_events AS (
    SELECT InvoiceNumber, 'Receive Full Payment' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved payment relationship source object]
    WHERE PaymentClassification = '[Full payment classification]' AND EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
clear_events AS (
    SELECT InvoiceNumber, 'Clear Invoice' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved clearing relationship source object]
    WHERE EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
reconcile_events AS (
    SELECT InvoiceNumber, 'Reconcile Bank Statement' AS Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName, DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding
    FROM [Approved bank statement reconciliation source object]
    WHERE EventTime >= (SELECT start_ts FROM params) AND EventTime < (SELECT end_ts FROM params)
),
reverse_events AS (
    SELECT
        x.InvoiceNumber,
        'Reverse Invoice' AS Activity,
        CAST(TO_TIMESTAMP(TO_VARCHAR(h.CreationDate) || TO_VARCHAR(h.CreationTime), 'YYYYMMDDHH24MISS') AS TIMESTAMP) AS EventTime,
        'SAP S/4HANA' AS SourceSystem,
        p.last_data_update AS LastDataUpdate,
        x.CompanyCode,
        x.CustomerNumber,
        x.AmountInLocalCurrency,
        COALESCE(h.UserName, x.UserName) AS UserName,
        x.DocumentType,
        x.PaymentTerms,
        CAST(NULL AS DATE) AS DueDate,
        x.ClearingDate,
        CAST(NULL AS NVARCHAR(1)) AS IsAutomated,
        CAST(NULL AS DECIMAL(18,2)) AS DaysSalesOutstanding
    FROM base_acdoca x
    INNER JOIN base_bkpf h ON h.CompanyCode = x.CompanyCode AND h.AccountingDocumentNumber = x.AccountingDocumentNumber AND h.FiscalYear = x.FiscalYear
    CROSS JOIN params p
    WHERE x.DocumentType = '[Reversal document type]'
)
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM invoice_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM output_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM dunning_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM dispute_create_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM dispute_update_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM dispute_resolve_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM promise_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM partial_payment_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM credit_memo_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM full_payment_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM clear_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM reconcile_events
UNION ALL
SELECT InvoiceNumber, Activity, EventTime, SourceSystem, LastDataUpdate, CompanyCode, CustomerNumber, AmountInLocalCurrency, UserName AS "User", DocumentType, PaymentTerms, DueDate, ClearingDate, IsAutomated, DaysSalesOutstanding FROM reverse_events
ORDER BY InvoiceNumber, EventTime, Activity;

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()

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 <> ''