Mẫu dữ liệu xử lý hóa đơn phải trả

-- Common Table Expression (CTE) to select the base set of AP Invoices
WITH Invoices_Base AS (
  SELECT
    I_JournalEntry.CompanyCode,
    I_JournalEntry.AccountingDocument,
    I_JournalEntry.FiscalYear,
    CONCAT(I_JournalEntry.CompanyCode, CONCAT(I_JournalEntry.AccountingDocument, I_JournalEntry.FiscalYear)) AS InvoiceId,
    I_JournalEntry.CreationDateTime,
    I_JournalEntry.CreatedByUser,
    I_JournalEntry.DocumentStatus,
    I_JournalEntry.JournalEntryType,
    I_JournalEntry.ReversalReferenceJournalEntry,
    I_JournalEntry.IsReversed,
    I_JournalEntry.ReversalDate,
    IJE_ITEM.NetDueDate,
    IJE_ITEM.Supplier,
    SUP.SupplierName AS VendorName,
    IJE_ITEM.AmountInCompanyCodeCurrency AS InvoiceAmount,
    MM.PurchaseOrder AS PurchaseOrderNumber,
    MM.PaymentBlockingReason
  FROM I_JournalEntry
  -- Join to get item details like due date and supplier
  LEFT JOIN I_JournalEntryItem AS IJE_ITEM
    ON I_JournalEntry.CompanyCode = IJE_ITEM.CompanyCode
    AND I_JournalEntry.AccountingDocument = IJE_ITEM.AccountingDocument
    AND I_JournalEntry.FiscalYear = IJE_ITEM.FiscalYear
    AND IJE_ITEM.IsSupplier = 'X'
  -- Join to get vendor name from master data
  LEFT JOIN I_Supplier AS SUP 
    ON IJE_ITEM.Supplier = SUP.Supplier
  -- Join to get MM Invoice specific data like PO Number and Payment Block
  LEFT JOIN I_SupplierInvoiceAPI01 AS MM 
    ON I_JournalEntry.AccountingDocument = MM.AccountingDocument
    AND I_JournalEntry.CompanyCode = MM.CompanyCode
    AND I_JournalEntry.FiscalYear = MM.FiscalYear
  WHERE
    I_JournalEntry.JournalEntryType IN ('KR', 'RE') -- Standard Vendor Invoice Types
    AND I_JournalEntry.CompanyCode IN ('[Your Company Code 1]', '[Your Company Code 2]')
    AND I_JournalEntry.CreationDateTime BETWEEN '[YYYY-MM-DD]T00:00:00Z' AND '[YYYY-MM-DD]T23:59:59Z'
)

-- Event: 1. Invoice Received
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Received' AS "ActivityName",
  B.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B

UNION ALL

-- Event: 2. Invoice Parked
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Parked' AS "ActivityName",
  B.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
WHERE B.DocumentStatus = 'V' -- 'V' stands for Parked

UNION ALL

-- Event: 3. Purchase Order Matched
SELECT
  B.InvoiceId AS "Invoice",
  'Purchase Order Matched' AS "ActivityName",
  B.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
WHERE B.PurchaseOrderNumber IS NOT NULL AND B.PurchaseOrderNumber <> ''

UNION ALL

-- Event: 4. Goods Receipt Matched
SELECT
  B.InvoiceId AS "Invoice",
  'Goods Receipt Matched' AS "ActivityName",
  B.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_SupplierInvoiceItemAPI01 AS MM_ITEM
  ON B.AccountingDocument = MM_ITEM.AccountingDocument
  AND B.FiscalYear = MM_ITEM.FiscalYear
WHERE MM_ITEM.GoodsReceipt IS NOT NULL AND MM_ITEM.GoodsReceipt <> ''

UNION ALL

-- Event: 5. Invoice Blocked For Payment
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Blocked For Payment' AS "ActivityName",
  B.CreationDateTime AS "EventTime", -- Approximates block time as creation time if blocked on entry
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
WHERE B.PaymentBlockingReason IS NOT NULL AND B.PaymentBlockingReason <> ''

UNION ALL

-- Event: 6. Discrepancy Resolved (Payment Block Removed)
SELECT
  B.InvoiceId AS "Invoice",
  'Discrepancy Resolved' AS "ActivityName",
  CD.ChangeTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  CD.UserName AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_ChangeDocument AS CD
  ON CONCAT(B.CompanyCode, B.AccountingDocument, B.FiscalYear) = CD.ObjectValue
WHERE CD.ChangeDocumentObject = 'INVOICE' 
  AND CD.TableName = 'RBKP' 
  AND CD.FieldName = 'ZLSPR' -- Field for Payment Block
  AND CD.NewFieldValue = '' -- Block was removed

UNION ALL

-- Event: 7, 8, 9. Workflow Events (Routed, Approved, Rejected)
SELECT
  B.InvoiceId AS "Invoice",
  CASE WF.WorkflowStatus
    WHEN 'READY' THEN 'Invoice Routed For Approval'
    WHEN 'APPROVED' THEN 'Invoice Approved'
    WHEN 'REJECTED' THEN 'Invoice Rejected'
  END AS "ActivityName",
  WF.WorkflowStatusChangedDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  WF.WorkflowStatusChangedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_WorkflowStatusDetails AS WF
  ON B.InvoiceId = WF.WorkflowScenarioInstance
WHERE WF.WorkflowStatus IN ('READY', 'APPROVED', 'REJECTED')

UNION ALL

-- Event: 10. Invoice Posted
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Posted' AS "ActivityName",
  JE.PostingDate AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_JournalEntry AS JE
  ON B.AccountingDocument = JE.AccountingDocument
  AND B.CompanyCode = JE.CompanyCode
  AND B.FiscalYear = JE.FiscalYear
WHERE B.DocumentStatus <> 'V' -- Any status other than Parked is considered Posted for AP

UNION ALL

-- Event: 11. Payment Proposal Created
SELECT
  B.InvoiceId AS "Invoice",
  'Payment Proposal Created' AS "ActivityName",
  PPI.PaymentProposalRunDate AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  PPI.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_PaymentProposalItem AS PPI
  ON B.CompanyCode = PPI.CompanyCode
  AND B.AccountingDocument = PPI.AccountingDocument
  AND B.FiscalYear = PPI.FiscalYear

UNION ALL

-- Event: 12. Payment Executed
-- This links the invoice to its clearing document, which is the payment document
SELECT DISTINCT
  B.InvoiceId AS "Invoice",
  'Payment Executed' AS "ActivityName",
  CLEAR_JE.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  CLEAR_JE.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_JournalEntryItem AS IJE_ITEM
  ON B.CompanyCode = IJE_ITEM.CompanyCode
  AND B.AccountingDocument = IJE_ITEM.AccountingDocument
  AND B.FiscalYear = IJE_ITEM.FiscalYear
INNER JOIN I_JournalEntry AS CLEAR_JE
  ON IJE_ITEM.ClearingJournalEntry = CLEAR_JE.AccountingDocument
  AND IJE_ITEM.CompanyCode = CLEAR_JE.CompanyCode
WHERE IJE_ITEM.ClearingJournalEntry IS NOT NULL AND IJE_ITEM.ClearingJournalEntry <> ''
  AND CLEAR_JE.JournalEntryType = 'KZ' -- Vendor Payment Document Type

UNION ALL

-- Event: 13. Invoice Due Date Passed
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Due Date Passed' AS "ActivityName",
  ADD_DAYS(B.NetDueDate, 1) AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  'SYSTEM' AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
LEFT JOIN I_JournalEntryItem AS IJE_ITEM
  ON B.CompanyCode = IJE_ITEM.CompanyCode
  AND B.AccountingDocument = IJE_ITEM.AccountingDocument
  AND B.FiscalYear = IJE_ITEM.FiscalYear
WHERE B.NetDueDate < CURRENT_DATE
  AND IJE_ITEM.ClearingDate IS NULL -- Invoice is not yet cleared

UNION ALL

-- Event: 14. Payment Cleared
SELECT DISTINCT
  B.InvoiceId AS "Invoice",
  'Payment Cleared' AS "ActivityName",
  IJE_ITEM.ClearingDate AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  IJE_ITEM.ChangedByUser AS "UserName", -- User who cleared it
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_JournalEntryItem AS IJE_ITEM
  ON B.CompanyCode = IJE_ITEM.CompanyCode
  AND B.AccountingDocument = IJE_ITEM.AccountingDocument
  AND B.FiscalYear = IJE_ITEM.FiscalYear
WHERE IJE_ITEM.ClearingDate IS NOT NULL

UNION ALL

-- Event: 15. Invoice Cancelled
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Cancelled' AS "ActivityName",
  B.ReversalDate AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName", -- User who created the original document
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
WHERE B.IsReversed = 'X';

WITH
params AS (
    SELECT
        TO_DATE('[Start date]') AS start_date,
        TO_DATE('[End date]') AS end_date,
        TO_TIMESTAMP('[Evaluation timestamp]') AS evaluation_ts,
        TO_TIMESTAMP('[Extraction timestamp]') AS extraction_ts
    FROM DUMMY
),
base_invoice AS (
    SELECT
        b.mandt,
        b.bukrs,
        b.belnr,
        b.gjahr,
        b.bldat,
        b.budat,
        b.cpudt,
        b.cputm,
        b.usnam,
        b.blart,
        b.xblnr,
        b.stblg,
        b.stjah,
        a.lifnr,
        a.wrbtr,
        a.waers,
        a.zfbdt,
        a.zbd1t,
        a.zbd2t,
        a.zbd3t,
        a.ebeln,
        a.ebelp,
        a.augbl,
        a.augdt,
        a.buzei,
        v.name1 AS vendor_name,
        CASE
            WHEN a.zfbdt IS NOT NULL THEN ADD_DAYS(a.zfbdt, COALESCE(a.zbd1t, 0))
            ELSE NULL
        END AS invoice_due_date
    FROM [Schema name].BKPF b
    INNER JOIN [Schema name].ACDOCA a
        ON a.mandt = b.mandt
       AND a.rbukrs = b.bukrs
       AND a.belnr = b.belnr
       AND a.gjahr = b.gjahr
    LEFT JOIN [Schema name].[Vendor master table] v
        ON v.[Vendor key field] = a.lifnr
    CROSS JOIN params p
    WHERE b.bukrs IN ([Company code filter])
      AND b.blart IN ([Document type filter])
      AND b.cpudt BETWEEN p.start_date AND p.end_date
),
invoice_received AS (
    SELECT DISTINCT
        CAST(belnr AS NVARCHAR(40)) AS invoice,
        'Invoice Received' AS activity_name,
        TO_TIMESTAMP(TO_VARCHAR(cpudt, 'YYYY-MM-DD') || ' ' || COALESCE(TO_VARCHAR(cputm), '00:00:00')) AS event_time,
        CAST(usnam AS NVARCHAR(80)) AS user_name,
        bukrs AS company_code,
        vendor_name,
        wrbtr AS invoice_amount,
        waers AS document_currency,
        CAST(ebeln AS NVARCHAR(40)) AS purchase_order_number,
        invoice_due_date
    FROM base_invoice
),
invoice_parked AS (
    SELECT
        CAST([Parked invoice key field] AS NVARCHAR(40)) AS invoice,
        'Invoice Parked' AS activity_name,
        [Parked event timestamp field] AS event_time,
        CAST([Parked user field] AS NVARCHAR(80)) AS user_name,
        [Parked company code field] AS company_code,
        [Parked vendor name field] AS vendor_name,
        [Parked amount field] AS invoice_amount,
        [Parked currency field] AS document_currency,
        CAST([Parked purchase order field] AS NVARCHAR(40)) AS purchase_order_number,
        [Parked due date field] AS invoice_due_date
    FROM [Schema name].[Parked document source]
    WHERE [Parked event date field] BETWEEN (SELECT start_date FROM params) AND (SELECT end_date FROM params)
      AND [Parked company code field] IN ([Company code filter])
),
purchase_order_matched AS (
    SELECT DISTINCT
        CAST(i.belnr AS NVARCHAR(40)) AS invoice,
        'Purchase Order Matched' AS activity_name,
        COALESCE([Purchase order match timestamp field], TO_TIMESTAMP(TO_VARCHAR(i.cpudt, 'YYYY-MM-DD') || ' ' || COALESCE(TO_VARCHAR(i.cputm), '00:00:00'))) AS event_time,
        CAST(COALESCE([Purchase order match user field], i.usnam) AS NVARCHAR(80)) AS user_name,
        i.bukrs AS company_code,
        i.vendor_name,
        i.wrBtr AS invoice_amount,
        i.waers AS document_currency,
        CAST(i.ebeln AS NVARCHAR(40)) AS purchase_order_number,
        i.invoice_due_date
    FROM base_invoice i
    INNER JOIN [Schema name].[Purchase order match source] m
        ON m.[Invoice document key field] = i.belnr
       AND m.[Invoice company code field] = i.bukrs
       AND m.[Invoice fiscal year field] = i.gjahr
    WHERE i.ebeln IS NOT NULL
),
goods_receipt_matched AS (
    SELECT DISTINCT
        CAST(i.belnr AS NVARCHAR(40)) AS invoice,
        'Goods Receipt Matched' AS activity_name,
        [Goods receipt match timestamp field] AS event_time,
        CAST(COALESCE([Goods receipt match user field], i.usnam) AS NVARCHAR(80)) AS user_name,
        i.bukrs AS company_code,
        i.vendor_name,
        i.wrbtr AS invoice_amount,
        i.waers AS document_currency,
        CAST(i.ebeln AS NVARCHAR(40)) AS purchase_order_number,
        i.invoice_due_date
    FROM base_invoice i
    INNER JOIN [Schema name].[Goods receipt match source] g
        ON g.[Invoice document key field] = i.belnr
       AND g.[Invoice company code field] = i.bukrs
       AND g.[Invoice fiscal year field] = i.gjahr
    WHERE i.ebeln IS NOT NULL
),
invoice_blocked AS (
    SELECT DISTINCT
        CAST(i.belnr AS NVARCHAR(40)) AS invoice,
        'Invoice Blocked For Payment' AS activity_name,
        [Payment block timestamp field] AS event_time,
        CAST(COALESCE([Payment block user field], i.usnam) AS NVARCHAR(80)) AS user_name,
        i.bukrs AS company_code,
        i.vendor_name,
        i.wrbtr AS invoice_amount,
        i.waers AS document_currency,
        CAST(i.ebeln AS NVARCHAR(40)) AS purchase_order_number,
        i.invoice_due_date
    FROM base_invoice i
    INNER JOIN [Schema name].[Payment block source] b
        ON b.[Invoice document key field] = i.belnr
       AND b.[Invoice company code field] = i.bukrs
       AND b.[Invoice fiscal year field] = i.gjahr
    WHERE [Payment block value field] IS NOT NULL
),
discrepancy_resolved AS (
    SELECT DISTINCT
        CAST(i.belnr AS NVARCHAR(40)) AS invoice,
        'Discrepancy Resolved' AS activity_name,
        [Payment block removal timestamp field] AS event_time,
        CAST(COALESCE([Payment block removal user field], i.usnam) AS NVARCHAR(80)) AS user_name,
        i.bukrs AS company_code,
        i.vendor_name,
        i.wrbtr AS invoice_amount,
        i.waers AS document_currency,
        CAST(i.ebeln AS NVARCHAR(40)) AS purchase_order_number,
        i.invoice_due_date
    FROM base_invoice i
    INNER JOIN [Schema name].[Payment block history source] h
        ON h.[Invoice document key field] = i.belnr
       AND h.[Invoice company code field] = i.bukrs
       AND h.[Invoice fiscal year field] = i.gjahr
    WHERE [Previous payment block value field] IS NOT NULL
      AND [New payment block value field] IS NULL
),
invoice_routed_for_approval AS (
    SELECT DISTINCT
        CAST(i.belnr AS NVARCHAR(40)) AS invoice,
        'Invoice Routed For Approval' AS activity_name,
        [Workflow routed timestamp field] AS event_time,
        CAST([Workflow initiator field] AS NVARCHAR(80)) AS user_name,
        i.bukrs AS company_code,
        i.vendor_name,
        i.wrbtr AS invoice_amount,
        i.waers AS document_currency,
        CAST(i.ebeln AS NVARCHAR(40)) AS purchase_order_number,
        i.invoice_due_date
    FROM base_invoice i
    INNER JOIN [Schema name].[Workflow event source] w
        ON w.[Invoice document key field] = i.belnr
       AND w.[Invoice company code field] = i.bukrs
       AND w.[Invoice fiscal year field] = i.gjahr
    WHERE [Workflow event type field] = '[Workflow routed event value]'
),
invoice_approved AS (
    SELECT DISTINCT
        CAST(i.belnr AS NVARCHAR(40)) AS invoice,
        'Invoice Approved' AS activity_name,
        [Workflow approval timestamp field] AS event_time,
        CAST([Workflow approver field] AS NVARCHAR(80)) AS user_name,
        i.bukrs AS company_code,
        i.vendor_name,
        i.wrbtr AS invoice_amount,
        i.waers AS document_currency,
        CAST(i.ebeln AS NVARCHAR(40)) AS purchase_order_number,
        i.invoice_due_date
    FROM base_invoice i
    INNER JOIN [Schema name].[Workflow event source] w
        ON w.[Invoice document key field] = i.belnr
       AND w.[Invoice company code field] = i.bukrs
       AND w.[Invoice fiscal year field] = i.gjahr
    WHERE [Workflow event type field] = '[Workflow approved event value]'
),
invoice_rejected AS (
    SELECT DISTINCT
        CAST(i.belnr AS NVARCHAR(40)) AS invoice,
        'Invoice Rejected' AS activity_name,
        [Workflow rejection timestamp field] AS event_time,
        CAST([Workflow rejector field] AS NVARCHAR(80)) AS user_name,
        i.bukrs AS company_code,
        i.vendor_name,
        i.wrbtr AS invoice_amount,
        i.waers AS document_currency,
        CAST(i.ebeln AS NVARCHAR(40)) AS purchase_order_number,
        i.invoice_due_date
    FROM base_invoice i
    INNER JOIN [Schema name].[Workflow event source] w
        ON w.[Invoice document key field] = i.belnr
       AND w.[Invoice company code field] = i.bukrs
       AND w.[Invoice fiscal year field] = i.gjahr
    WHERE [Workflow event type field] = '[Workflow rejected event value]'
),
invoice_posted AS (
    SELECT DISTINCT
        CAST(belnr AS NVARCHAR(40)) AS invoice,
        'Invoice Posted' AS activity_name,
        TO_TIMESTAMP(TO_VARCHAR(budat, 'YYYY-MM-DD') || ' 00:00:00') AS event_time,
        CAST(usnam AS NVARCHAR(80)) AS user_name,
        bukrs AS company_code,
        vendor_name,
        wrbtr AS invoice_amount,
        waers AS document_currency,
        CAST(ebeln AS NVARCHAR(40)) AS purchase_order_number,
        invoice_due_date
    FROM base_invoice
    WHERE stblg IS NULL
),
payment_proposal_created AS (
    SELECT DISTINCT
        CAST(i.belnr AS NVARCHAR(40)) AS invoice,
        'Payment Proposal Created' AS activity_name,
        [Payment proposal timestamp field] AS event_time,
        CAST([Payment proposal user field] AS NVARCHAR(80)) AS user_name,
        i.bukrs AS company_code,
        i.vendor_name,
        i.wrbtr AS invoice_amount,
        i.waers AS document_currency,
        CAST(i.ebeln AS NVARCHAR(40)) AS purchase_order_number,
        i.invoice_due_date
    FROM base_invoice i
    INNER JOIN [Schema name].[Payment proposal source] p
        ON p.[Invoice document key field] = i.belnr
       AND p.[Invoice company code field] = i.bukrs
       AND p.[Invoice fiscal year field] = i.gjahr
),
payment_executed AS (
    SELECT DISTINCT
        CAST(i.belnr AS NVARCHAR(40)) AS invoice,
        'Payment Executed' AS activity_name,
        [Payment execution timestamp field] AS event_time,
        CAST([Payment execution user field] AS NVARCHAR(80)) AS user_name,
        i.bukrs AS company_code,
        i.vendor_name,
        i.wrbtr AS invoice_amount,
        i.waers AS document_currency,
        CAST(i.ebeln AS NVARCHAR(40)) AS purchase_order_number,
        i.invoice_due_date
    FROM base_invoice i
    INNER JOIN [Schema name].[Payment execution source] p
        ON p.[Invoice document key field] = i.belnr
       AND p.[Invoice company code field] = i.bukrs
       AND p.[Invoice fiscal year field] = i.gjahr
),
invoice_due_date_passed AS (
    SELECT DISTINCT
        CAST(i.belnr AS NVARCHAR(40)) AS invoice,
        'Invoice Due Date Passed' AS activity_name,
        TO_TIMESTAMP(TO_VARCHAR(i.invoice_due_date, 'YYYY-MM-DD') || ' 23:59:59') AS event_time,
        CAST(i.usnam AS NVARCHAR(80)) AS user_name,
        i.bukrs AS company_code,
        i.vendor_name,
        i.wrbtr AS invoice_amount,
        i.waers AS document_currency,
        CAST(i.ebeln AS NVARCHAR(40)) AS purchase_order_number,
        i.invoice_due_date
    FROM base_invoice i
    WHERE i.invoice_due_date IS NOT NULL
      AND TO_TIMESTAMP(TO_VARCHAR(i.invoice_due_date, 'YYYY-MM-DD') || ' 23:59:59') < (SELECT evaluation_ts FROM params)
      AND i.augbl IS NULL
),
payment_cleared AS (
    SELECT DISTINCT
        CAST(i.belnr AS NVARCHAR(40)) AS invoice,
        'Payment Cleared' AS activity_name,
        TO_TIMESTAMP(TO_VARCHAR(i.augdt, 'YYYY-MM-DD') || ' 00:00:00') AS event_time,
        CAST(i.usnam AS NVARCHAR(80)) AS user_name,
        i.bukrs AS company_code,
        i.vendor_name,
        i.wrbtr AS invoice_amount,
        i.waers AS document_currency,
        CAST(i.ebeln AS NVARCHAR(40)) AS purchase_order_number,
        i.invoice_due_date
    FROM base_invoice i
    WHERE i.augbl IS NOT NULL
      AND i.augdt IS NOT NULL
),
invoice_cancelled AS (
    SELECT DISTINCT
        CAST(belnr AS NVARCHAR(40)) AS invoice,
        'Invoice Cancelled' AS activity_name,
        TO_TIMESTAMP(TO_VARCHAR([Reversal posting date field], 'YYYY-MM-DD') || ' 00:00:00') AS event_time,
        CAST(COALESCE([Reversal user field], usnam) AS NVARCHAR(80)) AS user_name,
        bukrs AS company_code,
        vendor_name,
        wrbtr AS invoice_amount,
        waers AS document_currency,
        CAST(ebeln AS NVARCHAR(40)) AS purchase_order_number,
        invoice_due_date
    FROM base_invoice
    WHERE stblg IS NOT NULL
),
all_events AS (
    SELECT * FROM invoice_received
    UNION ALL SELECT * FROM invoice_parked
    UNION ALL SELECT * FROM purchase_order_matched
    UNION ALL SELECT * FROM goods_receipt_matched
    UNION ALL SELECT * FROM invoice_blocked
    UNION ALL SELECT * FROM discrepancy_resolved
    UNION ALL SELECT * FROM invoice_routed_for_approval
    UNION ALL SELECT * FROM invoice_approved
    UNION ALL SELECT * FROM invoice_rejected
    UNION ALL SELECT * FROM invoice_posted
    UNION ALL SELECT * FROM payment_proposal_created
    UNION ALL SELECT * FROM payment_executed
    UNION ALL SELECT * FROM invoice_due_date_passed
    UNION ALL SELECT * FROM payment_cleared
    UNION ALL SELECT * FROM invoice_cancelled
)
SELECT
    invoice AS "Invoice",
    activity_name AS "ActivityName",
    event_time AS "EventTime",
    'SAP S/4HANA' AS "SourceSystem",
    (SELECT extraction_ts FROM params) AS "LastDataUpdate",
    user_name AS "UserName",
    company_code AS "CompanyCode",
    vendor_name AS "VendorName",
    invoice_amount AS "InvoiceAmount",
    purchase_order_number AS "PurchaseOrderNumber",
    invoice_due_date AS "InvoiceDueDate"
FROM all_events
WHERE invoice IS NOT NULL
  AND event_time IS NOT NULL
ORDER BY "Invoice", "EventTime", "ActivityName";

*&---------------------------------------------------------------------*
*& Report Z_PM_AP_INVOICE_EXTRACT
*&---------------------------------------------------------------------*
*& This report extracts Accounts Payable invoice lifecycle events for
*& process mining analysis.
*&---------------------------------------------------------------------*
REPORT z_pm_ap_invoice_extract.

*&---------------------------------------------------------------------*
*& Data Structures
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_event_log,
         invoice             TYPE belnr_d,
         activityname        TYPE string,
         eventtime           TYPE string,
         sourcesystem        TYPE logsys,
         lastdataupdate      TYPE string,
         username            TYPE uname,
         companycode         TYPE bukrs,
         vendorname          TYPE name1_gp,
         invoiceamount       TYPE wrbtr,
         purchaseordernumber TYPE ebeln,
         invoiceduedate      TYPE d,
       END OF ty_event_log.

DATA: gt_event_log TYPE TABLE OF ty_event_log.
DATA: gv_system_id TYPE logsys.
DATA: gv_last_update TYPE string.

*&---------------------------------------------------------------------*
*& Selection Screen
*&---------------------------------------------------------------------*
SELECT-OPTIONS: s_bukrs FOR bkpf-bukrs OBLIGATORY,
                s_cpudt FOR bkpf-cpudt OBLIGATORY DEFAULT sy-datum,
                s_blart FOR bkpf-blart.
PARAMETERS: p_fpath TYPE string OBLIGATORY DEFAULT '/tmp/ap_extract.csv'.

*&---------------------------------------------------------------------*
*& Main Processing Block
*&---------------------------------------------------------------------*
START-OF-SELECTION.

  " Get System ID and Update Timestamp
  CALL FUNCTION 'OWN_LOGICAL_SYSTEM_GET'
    IMPORTING
      own_logical_system             = gv_system_id
    EXCEPTIONS
      own_logical_system_not_defined = 1
      OTHERS                         = 2.
  CONCATENATE sy-datum sy-uzeit INTO gv_last_update.

  " Internal tables for SAP data
  DATA: lt_bkpf TYPE TABLE OF bkpf,
        lt_rbkp TYPE TABLE OF rbkp.

  " Select base documents
  SELECT * FROM bkpf INTO TABLE lt_bkpf
    WHERE bukrs IN s_bukrs
      AND cpudt IN s_cpudt
      AND blart IN s_blart
      AND ( blart = 'KR' OR blart = 'KG' ). " Example FI Invoice Types

  SELECT * FROM rbkp INTO TABLE lt_rbkp
    WHERE bukrs IN s_bukrs
      AND cpudt IN s_cpudt
      AND blart IN s_blart
      AND blart = 'RE'. " Example MM Invoice Type

  " --- Process each invoice document ---
  LOOP AT lt_bkpf ASSIGNING FIELD-SYMBOL(<fs_bkpf>).
    PERFORM process_invoice USING <fs_bkpf>.
  ENDLOOP.

  LOOP AT lt_rbkp ASSIGNING FIELD-SYMBOL(<fs_rbkp>).
    PERFORM process_mm_invoice USING <fs_rbkp>.
  ENDLOOP.

  " Write output to file
  PERFORM write_output_file.

*&---------------------------------------------------------------------*
*&      Form  PROCESS_INVOICE (Handles FI Invoices)
*&---------------------------------------------------------------------*
FORM process_invoice USING iv_bkpf TYPE bkpf.
  DATA: ls_bseg TYPE bseg,
        ls_lfa1 TYPE lfa1,
        ld_due_date TYPE d.
  DATA: ls_event TYPE ty_event_log.

  " Get Vendor and other details from first line item
  SELECT SINGLE * FROM bseg INTO ls_bseg
    WHERE bukrs = iv_bkpf-bukrs
      AND belnr = iv_bkpf-belnr
      AND gjahr = iv_bkpf-gjahr
      AND koart = 'K'.
  IF sy-subrc = 0.
    SELECT SINGLE name1 FROM lfa1 INTO ls_lfa1-name1 WHERE lifnr = ls_bseg-lifnr.
    CALL FUNCTION 'DETERMINE_DUE_DATE'
      EXPORTING
        i_zfbdt = ls_bseg-zfbdt
        i_zbd1t = ls_bseg-zbd1t
        i_zbd2t = ls_bseg-zbd2t
        i_zbd3t = ls_bseg-zbd3t
        i_zbd1p = ls_bseg-zbd1p
        i_zbd2p = ls_bseg-zbd2p
        i_zterm = ls_bseg-zterm
      IMPORTING
        e_faedt = ld_due_date.
  ENDIF.

  " Helper function to populate common fields
  MACRO set_common_fields.
    ls_event-invoice = iv_bkpf-belnr.
    ls_event-sourcesystem = gv_system_id.
    ls_event-lastdataupdate = gv_last_update.
    ls_event-companycode = iv_bkpf-bukrs.
    ls_event-vendorname = ls_lfa1-name1.
    ls_event-invoiceduedate = ld_due_date.
    SELECT SINGLE wrbtr FROM bseg INTO ls_event-invoiceamount WHERE belnr = iv_bkpf-belnr AND gjahr = iv_bkpf-gjahr AND koart = 'K'.
  ENDMACRO.

  " 1. Invoice Received
  CLEAR ls_event.
  set_common_fields.
  ls_event-activityname = 'Invoice Received'.
  CONCATENATE iv_bkpf-cpudt iv_bkpf-cputm INTO ls_event-eventtime.
  ls_event-username = iv_bkpf-usnam.
  APPEND ls_event TO gt_event_log.

  " 2. Invoice Parked (if document was created as parked)
  IF iv_bkpf-bstat = 'V'.
    CLEAR ls_event.
    set_common_fields.
    ls_event-activityname = 'Invoice Parked'.
    CONCATENATE iv_bkpf-cpudt iv_bkpf-cputm INTO ls_event-eventtime.
    ls_event-username = iv_bkpf-usnam.
    APPEND ls_event TO gt_event_log.
  ENDIF.

  " 10. Invoice Posted (For non-parked, same as received. For parked, this needs CDHDR/CDPOS logic not shown for brevity)
  IF iv_bkpf-bstat <> 'V'.
    CLEAR ls_event.
    set_common_fields.
    ls_event-activityname = 'Invoice Posted'.
    CONCATENATE iv_bkpf-budat iv_bkpf-cputm INTO ls_event-eventtime. " Using posting date
    ls_event-username = iv_bkpf-usnam.
    APPEND ls_event TO gt_event_log.
  ENDIF.

  " 5. & 7. Invoice Blocked / Discrepancy Resolved from Change Docs
  DATA: lt_cdhdr TYPE TABLE OF cdhdr, lt_cdpos TYPE TABLE OF cdpos.
  DATA(ld_objectkey) = |{ iv_bkpf-bukrs }{ iv_bkpf-belnr }{ iv_bkpf-gjahr }|.
  SELECT * FROM cdhdr INTO TABLE lt_cdhdr WHERE objectclas = 'BELEG' AND objectid = ld_objectkey.
  IF sy-subrc = 0.
    SELECT * FROM cdpos INTO TABLE lt_cdpos FOR ALL ENTRIES IN lt_cdhdr
      WHERE changenr = lt_cdhdr-changenr AND tabname = 'BSEG' AND fname = 'ZLSPR'.
    LOOP AT lt_cdpos ASSIGNING FIELD-SYMBOL(<fs_cdpos>).
      READ TABLE lt_cdhdr ASSIGNING FIELD-SYMBOL(<fs_cdhdr>) WITH KEY changenr = <fs_cdpos>-changenr.
      IF sy-subrc = 0.
        CLEAR ls_event.
        set_common_fields.
        IF <fs_cdpos>-value_new IS NOT INITIAL AND <fs_cdpos>-value_old IS INITIAL.
          ls_event-activityname = 'Invoice Blocked For Payment'.
        ELSEIF <fs_cdpos>-value_new IS INITIAL AND <fs_cdpos>-value_old IS NOT INITIAL.
          ls_event-activityname = 'Discrepancy Resolved'.
        ELSE.
          CONTINUE.
        ENDIF.
        CONCATENATE <fs_cdhdr>-udate <fs_cdhdr>-utime INTO ls_event-eventtime.
        ls_event-username = <fs_cdhdr>-username.
        APPEND ls_event TO gt_event_log.
      ENDIF.
    ENDLOOP.
  ENDIF.

  " 6. 8. 9. Workflow Events (Routed, Approved, Rejected) - Simplified Example
  " This requires knowledge of specific workflow templates. Placeholder logic:
  " SELECT ... FROM SWW_WI2OBJ ... WHERE INSTID = [Invoice Object]
  " SELECT ... FROM SWWWIHEAD ... to get status and times

  " 11. & 12. & 14. Payment Proposal, Executed, Cleared
  IF ls_bseg-augbl IS NOT INITIAL.
      DATA: ls_regup TYPE regup.
      SELECT SINGLE * FROM regup INTO ls_regup WHERE vblnr = ls_bseg-belnr.
      IF sy-subrc = 0.
          DATA(ld_rundate) = ls_regup-laufd.
          CLEAR ls_event.
          set_common_fields.
          ls_event-activityname = 'Payment Proposal Created'.
          CONCATENATE ld_rundate '000000' INTO ls_event-eventtime.
          APPEND ls_event TO gt_event_log.
      ENDIF.

      CLEAR ls_event.
      set_common_fields.
      ls_event-activityname = 'Payment Executed'.
      CONCATENATE ls_bseg-augdt '120000' INTO ls_event-eventtime. " Using clearing date as proxy
      APPEND ls_event TO gt_event_log.

      CLEAR ls_event.
      set_common_fields.
      ls_event-activityname = 'Payment Cleared'.
      CONCATENATE ls_bseg-augdt '120001' INTO ls_event-eventtime.
      APPEND ls_event TO gt_event_log.
  ENDIF.

  " 13. Invoice Due Date Passed (Calculated)
  IF ls_bseg-augdt IS NOT INITIAL AND ld_due_date IS NOT INITIAL.
    IF ls_bseg-augdt > ld_due_date.
      CLEAR ls_event.
      set_common_fields.
      ls_event-activityname = 'Invoice Due Date Passed'.
      CONCATENATE ld_due_date '235959' INTO ls_event-eventtime.
      APPEND ls_event TO gt_event_log.
    ENDIF.
  ENDIF.

  " 15. Invoice Cancelled
  IF iv_bkpf-stblg IS NOT INITIAL.
    DATA: ls_rev_bkpf TYPE bkpf.
    SELECT SINGLE * FROM bkpf INTO ls_rev_bkpf WHERE belnr = iv_bkpf-stblg.
    IF sy-subrc = 0.
      CLEAR ls_event.
      set_common_fields.
      ls_event-activityname = 'Invoice Cancelled'.
      CONCATENATE ls_rev_bkpf-cpudt ls_rev_bkpf-cputm INTO ls_event-eventtime.
      ls_event-username = ls_rev_bkpf-usnam.
      APPEND ls_event TO gt_event_log.
    ENDIF.
  ENDIF.

ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  PROCESS_MM_INVOICE (Handles MM/Logistics Invoices)
*&---------------------------------------------------------------------*
FORM process_mm_invoice USING iv_rbkp TYPE rbkp.
  " This form would be similar to PROCESS_INVOICE, but starts with RBKP.
  " It needs to find the corresponding FI document in BKPF via AWKEY.
  " The logic for PO/GR Matched would be included here.

  " For demonstration, creating placeholder events for MM-specific activities.
  DATA: ls_event TYPE ty_event_log.
  ls_event-invoice = iv_rbkp-belnr.
  ls_event-sourcesystem = gv_system_id.
  ls_event-lastdataupdate = gv_last_update.
  ls_event-companycode = iv_rbkp-bukrs.

  " 1. Invoice Received (MM)
  ls_event-activityname = 'Invoice Received'.
  CONCATENATE iv_rbkp-cpudt iv_rbkp-cputm INTO ls_event-eventtime.
  ls_event-username = iv_rbkp-usnam.
  APPEND ls_event TO gt_event_log.

  " 3. Purchase Order Matched (Implicit)
  ls_event-activityname = 'Purchase Order Matched'.
  CONCATENATE iv_rbkp-cpudt iv_rbkp-cputm INTO ls_event-eventtime.
  ls_event-username = iv_rbkp-usnam.
  APPEND ls_event TO gt_event_log.

  " 4. Goods Receipt Matched (Implicit)
  ls_event-activityname = 'Goods Receipt Matched'.
  CONCATENATE iv_rbkp-cpudt iv_rbkp-cputm INTO ls_event-eventtime.
  ls_event-username = iv_rbkp-usnam.
  APPEND ls_event TO gt_event_log.

  " NOTE: The rest of the events (Block, Pay, etc.) would be found by linking
  " RBKP to BKPF and then reusing the logic from PROCESS_INVOICE.
  " Link: BKPF-AWKEY = CONCATENATE( RBKP-BELNR, RBKP-GJAHR ).
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  WRITE_OUTPUT_FILE
*&---------------------------------------------------------------------*
FORM write_output_file.
  DATA: lv_string TYPE string.

  OPEN DATASET p_fpath FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
  IF sy-subrc <> 0.
    MESSAGE 'Error opening file.' TYPE 'E'.
    RETURN.
  ENDIF.

  " Write Header
  lv_string = 'Invoice,ActivityName,EventTime,SourceSystem,LastDataUpdate,UserName,CompanyCode,VendorName,InvoiceAmount,PurchaseOrderNumber,InvoiceDueDate'.
  TRANSFER lv_string TO p_fpath.

  " Write Data
  LOOP AT gt_event_log ASSIGNING FIELD-SYMBOL(<fs_event>).
    " Create a comma-separated string, handling potential commas in data
    CONCATENATE <fs_event>-invoice
                <fs_event>-activityname
                <fs_event>-eventtime
                <fs_event>-sourcesystem
                <fs_event>-lastdataupdate
                <fs_event>-username
                <fs_event>-companycode
                <fs_event>-vendorname
                <fs_event>-invoiceamount
                <fs_event>-purchaseordernumber
                <fs_event>-invoiceduedate
    INTO lv_string SEPARATED BY ','.
    TRANSFER lv_string TO p_fpath.
  ENDLOOP.

  CLOSE DATASET p_fpath.
  WRITE: / 'Extraction complete. File written to:', p_fpath.
ENDFORM.