Datatemplate voor Purchase to Pay - Factuurverwerking

WITH InvoiceBase AS (
  SELECT
    inv.InvoiceDocument,
    inv.FiscalYear,
    inv.CompanyCode,
    inv.Supplier AS VendorNumber,
    inv.DocumentType,
    inv.GrossInvoiceAmountInCoCoCrcy AS AmountInCompanyCodeCurrency,
    inv.NetDueDate AS PaymentDueDate,
    inv.PurchasingDocument,
    inv.CreationDateTime,
    inv.CreatedByUser,
    accdoc.AccountingDocument,
    accdoc.ClearingDate,
    accdoc.ClearingJournalEntry,
    accdoc.PaymentBlockReason,
    accdoc.IsReversed
  FROM I_InvoiceDocument AS inv
  LEFT JOIN I_OperationalAcctgDocItem AS accdoc
    ON inv.AccountingDocument = accdoc.AccountingDocument
    AND inv.FiscalYear = accdoc.FiscalYear
    AND inv.CompanyCode = accdoc.CompanyCode
  WHERE
    inv.CreationDate BETWEEN '{StartDate}' AND '{EndDate}'
    AND inv.CompanyCode IN ('{CompanyCode1}', '{CompanyCode2}')
)
-- 1. Invoice Document Created
SELECT
  InvoiceDocument AS "InvoiceNumber",
  'Invoice Document Created' AS "ActivityName",
  CreationDateTime AS "EventTime",
  CreatedByUser AS "UserName",
  CompanyCode AS "CompanyCode",
  VendorNumber AS "VendorNumber",
  AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
  PaymentDueDate AS "PaymentDueDate",
  DocumentType AS "DocumentType",
  CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
  PurchasingDocument AS "PurchasingDocument"
FROM InvoiceBase

UNION ALL

-- 2. Invoice Parked
SELECT
  i.InvoiceDocument AS "InvoiceNumber",
  'Invoice Parked' AS "ActivityName",
  i.CreationDateTime AS "EventTime",
  i.CreatedByUser AS "UserName",
  i.CompanyCode AS "CompanyCode",
  i.Supplier AS "VendorNumber",
  i.GrossInvoiceAmountInCoCoCrcy AS "AmountInCompanyCodeCurrency",
  i.NetDueDate AS "PaymentDueDate",
  i.DocumentType AS "DocumentType",
  CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
  i.PurchasingDocument AS "PurchasingDocument"
FROM I_InvoiceDocument AS i
WHERE
  i.InvoiceDocumentIsParked = 'X'
  AND i.CreationDate BETWEEN '{StartDate}' AND '{EndDate}'
  AND i.CompanyCode IN ('{CompanyCode1}', '{CompanyCode2}')

UNION ALL

-- 3, 4, 5. Workflow activities (Sent for Approval, Approved, Rejected) from Change Docs
SELECT
  cdpos.ObjectValue AS "InvoiceNumber",
  CASE
    WHEN cdpos.ValueNew = '[StatusSentForApproval]' THEN 'Invoice Sent For Approval'
    WHEN cdpos.ValueNew = '[StatusApproved]' THEN 'Invoice Approved'
    WHEN cdpos.ValueNew = '[StatusRejected]' THEN 'Invoice Rejected'
  END AS "ActivityName",
  CAST(cdhdr.ChangeDate AS TIMESTAMP) + CAST(cdhdr.ChangeTime AS TIME) AS "EventTime",
  cdhdr.UserName AS "UserName",
  inv.CompanyCode AS "CompanyCode",
  inv.VendorNumber AS "VendorNumber",
  inv.AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
  inv.PaymentDueDate AS "PaymentDueDate",
  inv.DocumentType AS "DocumentType",
  CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
  inv.PurchasingDocument AS "PurchasingDocument"
FROM I_ChangeDocument AS cdhdr
JOIN I_ChangeDocumentItem AS cdpos ON cdhdr.ChangeDocument = cdpos.ChangeDocument
JOIN InvoiceBase AS inv ON cdpos.ObjectValue = inv.InvoiceDocument
WHERE
  cdhdr.ObjectClassName = 'INCOMINGINVOICE'
  AND cdpos.FieldName = '[WorkflowStatusFieldName]'
  AND cdpos.ValueNew IN ('[StatusSentForApproval]', '[StatusApproved]', '[StatusRejected]')

UNION ALL

-- 6. Invoice Data Updated
SELECT
  cdpos.ObjectValue AS "InvoiceNumber",
  'Invoice Data Updated' AS "ActivityName",
  CAST(cdhdr.ChangeDate AS TIMESTAMP) + CAST(cdhdr.ChangeTime AS TIME) AS "EventTime",
  cdhdr.UserName AS "UserName",
  inv.CompanyCode AS "CompanyCode",
  inv.VendorNumber AS "VendorNumber",
  inv.AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
  inv.PaymentDueDate AS "PaymentDueDate",
  inv.DocumentType AS "DocumentType",
  CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
  inv.PurchasingDocument AS "PurchasingDocument"
FROM I_ChangeDocument AS cdhdr
JOIN I_ChangeDocumentItem AS cdpos ON cdhdr.ChangeDocument = cdpos.ChangeDocument
JOIN InvoiceBase AS inv ON cdpos.ObjectValue = inv.InvoiceDocument
WHERE
  cdhdr.ObjectClassName = 'INCOMINGINVOICE'
  AND cdpos.FieldName IN ('GrossInvoiceAmount', 'DocumentDate', 'PaymentTerms')
  AND cdhdr.ChangeDate BETWEEN '{StartDate}' AND '{EndDate}'

UNION ALL

-- 7 & 8. Payment Block Set/Removed
SELECT
  inv.InvoiceDocument AS "InvoiceNumber",
  CASE
    WHEN cdpos.ValueNew <> '' AND cdpos.ValueOld = '' THEN 'Payment Block Set'
    WHEN cdpos.ValueNew = '' AND cdpos.ValueOld <> '' THEN 'Payment Block Removed'
  END AS "ActivityName",
  CAST(cdhdr.ChangeDate AS TIMESTAMP) + CAST(cdhdr.ChangeTime AS TIME) AS "EventTime",
  cdhdr.UserName AS "UserName",
  inv.CompanyCode AS "CompanyCode",
  inv.VendorNumber AS "VendorNumber",
  inv.AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
  inv.PaymentDueDate AS "PaymentDueDate",
  inv.DocumentType AS "DocumentType",
  cdpos.ValueNew AS "PaymentBlockReason",
  inv.PurchasingDocument AS "PurchasingDocument"
FROM I_ChangeDocument AS cdhdr
JOIN I_ChangeDocumentItem AS cdpos ON cdhdr.ChangeDocument = cdpos.ChangeDocument
JOIN InvoiceBase AS inv ON cdpos.ObjectValue = inv.AccountingDocument
WHERE 
  cdhdr.ObjectClassName = 'BELEG' 
  AND cdpos.TableName = 'BSEG' 
  AND cdpos.FieldName = 'ZLSPR'
  AND ( (cdpos.ValueNew <> '' AND cdpos.ValueOld = '') OR (cdpos.ValueNew = '' AND cdpos.ValueOld <> '') )

UNION ALL

-- 9. Invoice Posted
SELECT
  inv.InvoiceDocument AS "InvoiceNumber",
  'Invoice Posted' AS "ActivityName",
  CAST(accdoc.PostingDate AS TIMESTAMP) AS "EventTime",
  accdoc.CreatedByUser AS "UserName",
  inv.CompanyCode AS "CompanyCode",
  inv.VendorNumber AS "VendorNumber",
  inv.AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
  inv.PaymentDueDate AS "PaymentDueDate",
  inv.DocumentType AS "DocumentType",
  accdoc.PaymentBlockReason AS "PaymentBlockReason",
  inv.PurchasingDocument AS "PurchasingDocument"
FROM InvoiceBase AS inv
JOIN I_OperationalAcctgDocItem AS accdoc ON inv.AccountingDocument = accdoc.AccountingDocument
WHERE inv.AccountingDocument IS NOT NULL AND inv.IsReversed = FALSE

UNION ALL

-- 10. Payment Proposal Created
SELECT 
  item.InvoiceReference AS "InvoiceNumber",
  'Payment Proposal Created' AS "ActivityName",
  CAST(prun.PaymentRunDate AS TIMESTAMP) AS "EventTime",
  prun.CreatedByUser AS "UserName",
  item.CompanyCode AS "CompanyCode",
  item.Supplier AS "VendorNumber",
  item.AmountInTransactionCurrency AS "AmountInCompanyCodeCurrency",
  item.NetDueDate AS "PaymentDueDate",
  item.AccountingDocumentType AS "DocumentType",
  item.PaymentBlockReason AS "PaymentBlockReason",
  item.PurchasingDocument AS "PurchasingDocument"
FROM I_PaymentProposalItem as item
JOIN I_PaymentRun as prun ON item.PaymentRunName = prun.PaymentRunName
JOIN InvoiceBase AS inv ON item.InvoiceReference = inv.InvoiceDocument

UNION ALL

-- 11 & 12. Payment Executed / Late Payment Executed
SELECT
  InvoiceDocument AS "InvoiceNumber",
  CASE
    WHEN ClearingDate > PaymentDueDate THEN 'Late Payment Executed'
    ELSE 'Payment Executed'
  END AS "ActivityName",
  CAST(ClearingDate AS TIMESTAMP) AS "EventTime",
  CAST(NULL AS VARCHAR(12)) AS "UserName", -- User for clearing is not always straightforward
  CompanyCode AS "CompanyCode",
  VendorNumber AS "VendorNumber",
  AmountInCompanyCodeCurrency AS "AmountInCompanyCodeCurrency",
  PaymentDueDate AS "PaymentDueDate",
  DocumentType AS "DocumentType",
  '' AS "PaymentBlockReason",
  PurchasingDocument AS "PurchasingDocument"
FROM InvoiceBase
WHERE ClearingDate IS NOT NULL AND IsReversed = FALSE

UNION ALL

-- 13. Invoice Reversed
SELECT
  rev.OriginalInvoiceDocument AS "InvoiceNumber",
  'Invoice Reversed' AS "ActivityName",
  rev.CreationDateTime AS "EventTime",
  rev.CreatedByUser AS "UserName",
  rev.CompanyCode AS "CompanyCode",
  rev.Supplier AS "VendorNumber",
  rev.GrossInvoiceAmountInCoCoCrcy AS "AmountInCompanyCodeCurrency",
  CAST(NULL AS DATE) AS "PaymentDueDate",
  rev.DocumentType AS "DocumentType",
  CAST(NULL AS VARCHAR(1)) AS "PaymentBlockReason",
  rev.PurchasingDocument AS "PurchasingDocument"
FROM I_InvoiceDocument AS rev
WHERE rev.OriginalInvoiceDocument IN (SELECT InvoiceDocument FROM InvoiceBase) AND rev.IsReversal = 'X'

WITH
invoice_base AS (
    SELECT
        r.INV_DOC_NO AS InvoiceNumber,
        r.COMPANY_CODE AS CompanyCode,
        r.FISCAL_YEAR AS FiscalYear,
        r.DOCUMENT_TYPE AS DocumentType,
        r.VENDOR_NO AS VendorNumber,
        r.GROSS_AMOUNT_CC AS AmountInCompanyCodeCurrency,
        r.PAYMENT_DUE_DATE AS PaymentDueDate,
        r.PURCHASING_DOCUMENT AS PurchasingDocument,
        r.CREATED_AT AS InvoiceCreatedAt,
        r.CREATED_BY AS InvoiceCreatedBy
    FROM [Your RBKP invoice header source] r
    WHERE r.CREATED_AT >= '[Start timestamp]'
      AND r.CREATED_AT < '[End timestamp]'
      AND r.COMPANY_CODE IN ([Company code filter])
      AND r.DOCUMENT_TYPE IN ([Document type filter])
),
posted_accounting AS (
    SELECT
        b.INV_DOC_NO AS InvoiceNumber,
        b.COMPANY_CODE AS CompanyCode,
        b.FISCAL_YEAR AS FiscalYear,
        b.ACCOUNTING_DOCUMENT AS AccountingDocument,
        b.POSTING_DATE AS PostingDate,
        b.CREATED_AT AS PostedAt,
        b.CREATED_BY AS PostedBy,
        b.REVERSAL_DOCUMENT AS ReversalDocument,
        b.REVERSED_DOCUMENT AS ReversedDocument
    FROM [Your BKPF accounting document source] b
    WHERE b.CREATED_AT >= '[Start timestamp]'
      AND b.CREATED_AT < '[End timestamp]'
      AND b.COMPANY_CODE IN ([Company code filter])
),
journal_attributes AS (
    SELECT
        a.COMPANY_CODE AS CompanyCode,
        a.FISCAL_YEAR AS FiscalYear,
        a.ACCOUNTING_DOCUMENT AS AccountingDocument,
        MAX(a.VENDOR_NO) AS VendorNumber,
        SUM(a.AMOUNT_IN_COMPANY_CODE_CURRENCY) AS AmountInCompanyCodeCurrency,
        MAX(a.PURCHASING_DOCUMENT) AS PurchasingDocument,
        MAX(a.CLEARING_DATE) AS ClearingDate,
        MAX(a.CLEARING_DOCUMENT) AS ClearingDocument
    FROM [Your ACDOCA universal journal source] a
    WHERE a.COMPANY_CODE IN ([Company code filter])
      AND a.POSTING_DATE >= '[Start date]'
      AND a.POSTING_DATE < '[End date]'
    GROUP BY
        a.COMPANY_CODE,
        a.FISCAL_YEAR,
        a.ACCOUNTING_DOCUMENT
),
source_events AS (
    SELECT
        e.INV_DOC_NO AS InvoiceNumber,
        e.COMPANY_CODE AS CompanyCode,
        e.FISCAL_YEAR AS FiscalYear,
        e.EVENT_TIMESTAMP AS EventTime,
        e.EVENT_USER AS UserName,
        e.PAYMENT_BLOCK_REASON AS PaymentBlockReason,
        e.PURCHASING_DOCUMENT AS PurchasingDocument,
        e.VENDOR_NO AS VendorNumber,
        e.AMOUNT_IN_COMPANY_CODE_CURRENCY AS AmountInCompanyCodeCurrency,
        e.PAYMENT_DUE_DATE AS PaymentDueDate,
        e.DOCUMENT_TYPE AS DocumentType,
        e.EVENT_TYPE AS SourceEventType
    FROM [Your verified invoice event and workflow source] e
    WHERE e.EVENT_TIMESTAMP >= '[Start timestamp]'
      AND e.EVENT_TIMESTAMP < '[End timestamp]'
      AND e.COMPANY_CODE IN ([Company code filter])
),
base_events AS (
    SELECT
        i.InvoiceNumber,
        'Invoice Document Created' AS ActivityName,
        i.InvoiceCreatedAt AS EventTime,
        i.InvoiceCreatedBy AS UserName,
        i.CompanyCode,
        COALESCE(i.VendorNumber, j.VendorNumber) AS VendorNumber,
        COALESCE(i.AmountInCompanyCodeCurrency, j.AmountInCompanyCodeCurrency) AS AmountInCompanyCodeCurrency,
        i.PaymentDueDate,
        i.DocumentType,
        CAST(NULL AS NVARCHAR(20)) AS PaymentBlockReason,
        COALESCE(i.PurchasingDocument, j.PurchasingDocument) AS PurchasingDocument
    FROM invoice_base i
    LEFT JOIN posted_accounting p
      ON p.InvoiceNumber = i.InvoiceNumber
     AND p.CompanyCode = i.CompanyCode
     AND p.FiscalYear = i.FiscalYear
    LEFT JOIN journal_attributes j
      ON j.CompanyCode = p.CompanyCode
     AND j.FiscalYear = p.FiscalYear
     AND j.AccountingDocument = p.AccountingDocument
    WHERE i.InvoiceCreatedAt IS NOT NULL

    UNION ALL

    SELECT
        s.InvoiceNumber,
        'Invoice Parked' AS ActivityName,
        s.EventTime,
        s.UserName,
        s.CompanyCode,
        COALESCE(s.VendorNumber, i.VendorNumber) AS VendorNumber,
        COALESCE(s.AmountInCompanyCodeCurrency, i.AmountInCompanyCodeCurrency) AS AmountInCompanyCodeCurrency,
        COALESCE(s.PaymentDueDate, i.PaymentDueDate) AS PaymentDueDate,
        COALESCE(s.DocumentType, i.DocumentType) AS DocumentType,
        s.PaymentBlockReason,
        COALESCE(s.PurchasingDocument, i.PurchasingDocument) AS PurchasingDocument
    FROM source_events s
    LEFT JOIN invoice_base i ON i.InvoiceNumber = s.InvoiceNumber AND i.CompanyCode = s.CompanyCode AND i.FiscalYear = s.FiscalYear
    WHERE s.SourceEventType = 'PARKED'

    UNION ALL

    SELECT s.InvoiceNumber, 'Invoice Sent For Approval', s.EventTime, s.UserName, s.CompanyCode, COALESCE(s.VendorNumber, i.VendorNumber), COALESCE(s.AmountInCompanyCodeCurrency, i.AmountInCompanyCodeCurrency), COALESCE(s.PaymentDueDate, i.PaymentDueDate), COALESCE(s.DocumentType, i.DocumentType), s.PaymentBlockReason, COALESCE(s.PurchasingDocument, i.PurchasingDocument)
    FROM source_events s LEFT JOIN invoice_base i ON i.InvoiceNumber = s.InvoiceNumber AND i.CompanyCode = s.CompanyCode AND i.FiscalYear = s.FiscalYear
    WHERE s.SourceEventType = 'SENT_FOR_APPROVAL'

    UNION ALL

    SELECT s.InvoiceNumber, 'Invoice Approved', s.EventTime, s.UserName, s.CompanyCode, COALESCE(s.VendorNumber, i.VendorNumber), COALESCE(s.AmountInCompanyCodeCurrency, i.AmountInCompanyCodeCurrency), COALESCE(s.PaymentDueDate, i.PaymentDueDate), COALESCE(s.DocumentType, i.DocumentType), s.PaymentBlockReason, COALESCE(s.PurchasingDocument, i.PurchasingDocument)
    FROM source_events s LEFT JOIN invoice_base i ON i.InvoiceNumber = s.InvoiceNumber AND i.CompanyCode = s.CompanyCode AND i.FiscalYear = s.FiscalYear
    WHERE s.SourceEventType = 'APPROVED'

    UNION ALL

    SELECT s.InvoiceNumber, 'Invoice Rejected', s.EventTime, s.UserName, s.CompanyCode, COALESCE(s.VendorNumber, i.VendorNumber), COALESCE(s.AmountInCompanyCodeCurrency, i.AmountInCompanyCodeCurrency), COALESCE(s.PaymentDueDate, i.PaymentDueDate), COALESCE(s.DocumentType, i.DocumentType), s.PaymentBlockReason, COALESCE(s.PurchasingDocument, i.PurchasingDocument)
    FROM source_events s LEFT JOIN invoice_base i ON i.InvoiceNumber = s.InvoiceNumber AND i.CompanyCode = s.CompanyCode AND i.FiscalYear = s.FiscalYear
    WHERE s.SourceEventType = 'REJECTED'

    UNION ALL

    SELECT s.InvoiceNumber, 'Invoice Data Updated', s.EventTime, s.UserName, s.CompanyCode, COALESCE(s.VendorNumber, i.VendorNumber), COALESCE(s.AmountInCompanyCodeCurrency, i.AmountInCompanyCodeCurrency), COALESCE(s.PaymentDueDate, i.PaymentDueDate), COALESCE(s.DocumentType, i.DocumentType), s.PaymentBlockReason, COALESCE(s.PurchasingDocument, i.PurchasingDocument)
    FROM source_events s LEFT JOIN invoice_base i ON i.InvoiceNumber = s.InvoiceNumber AND i.CompanyCode = s.CompanyCode AND i.FiscalYear = s.FiscalYear
    WHERE s.SourceEventType = 'DATA_UPDATED'

    UNION ALL

    SELECT s.InvoiceNumber, 'Payment Block Set', s.EventTime, s.UserName, s.CompanyCode, COALESCE(s.VendorNumber, i.VendorNumber), COALESCE(s.AmountInCompanyCodeCurrency, i.AmountInCompanyCodeCurrency), COALESCE(s.PaymentDueDate, i.PaymentDueDate), COALESCE(s.DocumentType, i.DocumentType), s.PaymentBlockReason, COALESCE(s.PurchasingDocument, i.PurchasingDocument)
    FROM source_events s LEFT JOIN invoice_base i ON i.InvoiceNumber = s.InvoiceNumber AND i.CompanyCode = s.CompanyCode AND i.FiscalYear = s.FiscalYear
    WHERE s.SourceEventType = 'PAYMENT_BLOCK_SET'

    UNION ALL

    SELECT s.InvoiceNumber, 'Payment Block Removed', s.EventTime, s.UserName, s.CompanyCode, COALESCE(s.VendorNumber, i.VendorNumber), COALESCE(s.AmountInCompanyCodeCurrency, i.AmountInCompanyCodeCurrency), COALESCE(s.PaymentDueDate, i.PaymentDueDate), COALESCE(s.DocumentType, i.DocumentType), s.PaymentBlockReason, COALESCE(s.PurchasingDocument, i.PurchasingDocument)
    FROM source_events s LEFT JOIN invoice_base i ON i.InvoiceNumber = s.InvoiceNumber AND i.CompanyCode = s.CompanyCode AND i.FiscalYear = s.FiscalYear
    WHERE s.SourceEventType = 'PAYMENT_BLOCK_REMOVED'

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Invoice Posted',
        p.PostedAt,
        p.PostedBy,
        i.CompanyCode,
        COALESCE(i.VendorNumber, j.VendorNumber),
        COALESCE(i.AmountInCompanyCodeCurrency, j.AmountInCompanyCodeCurrency),
        i.PaymentDueDate,
        i.DocumentType,
        CAST(NULL AS NVARCHAR(20)),
        COALESCE(i.PurchasingDocument, j.PurchasingDocument)
    FROM invoice_base i
    INNER JOIN posted_accounting p ON p.InvoiceNumber = i.InvoiceNumber AND p.CompanyCode = i.CompanyCode AND p.FiscalYear = i.FiscalYear
    LEFT JOIN journal_attributes j ON j.CompanyCode = p.CompanyCode AND j.FiscalYear = p.FiscalYear AND j.AccountingDocument = p.AccountingDocument
    WHERE p.PostedAt IS NOT NULL

    UNION ALL

    SELECT s.InvoiceNumber, 'Payment Proposal Created', s.EventTime, s.UserName, s.CompanyCode, COALESCE(s.VendorNumber, i.VendorNumber), COALESCE(s.AmountInCompanyCodeCurrency, i.AmountInCompanyCodeCurrency), COALESCE(s.PaymentDueDate, i.PaymentDueDate), COALESCE(s.DocumentType, i.DocumentType), s.PaymentBlockReason, COALESCE(s.PurchasingDocument, i.PurchasingDocument)
    FROM source_events s LEFT JOIN invoice_base i ON i.InvoiceNumber = s.InvoiceNumber AND i.CompanyCode = s.CompanyCode AND i.FiscalYear = s.FiscalYear
    WHERE s.SourceEventType = 'PAYMENT_PROPOSAL_CREATED'

    UNION ALL

    SELECT s.InvoiceNumber, 'Payment Executed', s.EventTime, s.UserName, s.CompanyCode, COALESCE(s.VendorNumber, i.VendorNumber), COALESCE(s.AmountInCompanyCodeCurrency, i.AmountInCompanyCodeCurrency), COALESCE(s.PaymentDueDate, i.PaymentDueDate), COALESCE(s.DocumentType, i.DocumentType), s.PaymentBlockReason, COALESCE(s.PurchasingDocument, i.PurchasingDocument)
    FROM source_events s LEFT JOIN invoice_base i ON i.InvoiceNumber = s.InvoiceNumber AND i.CompanyCode = s.CompanyCode AND i.FiscalYear = s.FiscalYear
    WHERE s.SourceEventType = 'PAYMENT_EXECUTED'

    UNION ALL

    SELECT s.InvoiceNumber, 'Late Payment Executed', s.EventTime, s.UserName, s.CompanyCode, COALESCE(s.VendorNumber, i.VendorNumber), COALESCE(s.AmountInCompanyCodeCurrency, i.AmountInCompanyCodeCurrency), COALESCE(s.PaymentDueDate, i.PaymentDueDate), COALESCE(s.DocumentType, i.DocumentType), s.PaymentBlockReason, COALESCE(s.PurchasingDocument, i.PurchasingDocument)
    FROM source_events s LEFT JOIN invoice_base i ON i.InvoiceNumber = s.InvoiceNumber AND i.CompanyCode = s.CompanyCode AND i.FiscalYear = s.FiscalYear
    WHERE s.SourceEventType = 'PAYMENT_EXECUTED'
      AND s.EventTime > CAST(COALESCE(s.PaymentDueDate, i.PaymentDueDate) AS TIMESTAMP)

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Invoice Reversed',
        p.ReversalEventAt,
        p.ReversalUser,
        i.CompanyCode,
        COALESCE(i.VendorNumber, j.VendorNumber),
        COALESCE(i.AmountInCompanyCodeCurrency, j.AmountInCompanyCodeCurrency),
        i.PaymentDueDate,
        i.DocumentType,
        CAST(NULL AS NVARCHAR(20)),
        COALESCE(i.PurchasingDocument, j.PurchasingDocument)
    FROM invoice_base i
    INNER JOIN [Your verified reversal event source] p ON p.INV_DOC_NO = i.InvoiceNumber AND p.COMPANY_CODE = i.CompanyCode AND p.FISCAL_YEAR = i.FiscalYear
    LEFT JOIN journal_attributes j ON j.CompanyCode = p.COMPANY_CODE AND j.FiscalYear = p.FISCAL_YEAR AND j.AccountingDocument = p.ACCOUNTING_DOCUMENT
    WHERE p.ReversalEventAt IS NOT NULL
)
SELECT
    InvoiceNumber,
    ActivityName,
    EventTime,
    UserName,
    CompanyCode,
    VendorNumber,
    AmountInCompanyCodeCurrency,
    PaymentDueDate,
    DocumentType,
    PaymentBlockReason,
    PurchasingDocument
FROM base_events
WHERE InvoiceNumber IS NOT NULL
  AND EventTime IS NOT NULL
ORDER BY InvoiceNumber, EventTime, ActivityName;

REPORT Z_PM_INVOICE_EXTRACT.

* --- Internal table structure for the final event log
TYPES: BEGIN OF ty_s_event_log,
         invoicenumber                 TYPE char25,
         activityname                  TYPE char50,
         eventtime                     TYPE char19, "YYYY-MM-DD HH:MM:SS
         username                      TYPE sy-uname,
         companycode                   TYPE bukrs,
         vendornumber                  TYPE lifnr,
         amountincompanycodecurrency   TYPE wrbtr,
         paymentduedate                TYPE char10, "YYYY-MM-DD
         documenttype                  TYPE blart,
         paymentblockreason            TYPE char1,
         purchasingdocument            TYPE ebeln,
       END OF ty_s_event_log.

DATA: lt_event_log TYPE STANDARD TABLE OF ty_s_event_log.
DATA: ls_event_log TYPE ty_s_event_log.

* --- Selection Screen for user inputs
PARAMETERS: p_path TYPE string DEFAULT '/usr/sap/tmp/invoice_events.csv'.

SELECT-OPTIONS: s_erdat FOR sy-datum OBLIGATORY,  " Entry Date
                  s_bukrs FOR bkpf-bukrs OBLIGATORY, " Company Code
                  s_blart FOR bkpf-blart.             " Document Type

START-OF-SELECTION.

* --- 1. Invoice Document Created (from Logistics Invoice Verification)
  SELECT CONCAT( rbkp~belnr, rbkp~gjahr ) AS invoicenumber,
         'Invoice Document Created'     AS activityname,
         CONCAT( rbkp~cpudt, rbkp~cputm ) AS eventtime,
         rbkp~usnam                     AS username,
         rbkp~bukrs                     AS companycode,
         rbkp~lifnr                     AS vendornumber,
         rbkp~rmwwr                     AS amountincompanycodecurrency,
         ''                             AS paymentduedate,
         rbkp~blart                     AS documenttype,
         rbkp~zuonr                     AS paymentblockreason,
         ''                             AS purchasingdocument
    FROM rbkp
    INTO TABLE @DATA(lt_created)
    WHERE rbkp~cpudt IN @s_erdat
      AND rbkp~bukrs IN @s_bukrs
      AND rbkp~blart IN @s_blart.

  LOOP AT lt_created INTO DATA(ls_created).
    ls_event_log-invoicenumber = ls_created-invoicenumber.
    ls_event_log-activityname = ls_created-activityname.
    ls_event_log-eventtime = |{ ls_created-eventtime(8) } { ls_created-eventtime+8(2) }:{ ls_created-eventtime+10(2) }:{ ls_created-eventtime+12(2) }|.
    ls_event_log-username = ls_created-username.
    ls_event_log-companycode = ls_created-companycode.
    ls_event_log-vendornumber = ls_created-vendornumber.
    ls_event_log-amountincompanycodecurrency = ls_created-amountincompanycodecurrency.
    ls_event_log-paymentduedate = ''.
    ls_event_log-documenttype = ls_created-documenttype.
    ls_event_log-paymentblockreason = ''.
    ls_event_log-purchasingdocument = ls_created-purchasingdocument.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

* --- 2. Invoice Parked (assuming status 'A' or 'B' in RBKP)
  SELECT CONCAT( belnr, gjahr )       AS invoicenumber,
         'Invoice Parked'             AS activityname,
         CONCAT( cpudt, cputm )        AS eventtime,
         usnam                        AS username,
         bukrs                        AS companycode,
         lifnr                        AS vendornumber,
         rmwwr                        AS amountincompanycodecurrency,
         ''                           AS paymentduedate,
         blart                        AS documenttype,
         ''                           AS paymentblockreason,
         ''                           AS purchasingdocument
    FROM rbkp
    INTO TABLE @DATA(lt_parked)
    WHERE rbstat IN ('A', 'B')
      AND cpudt IN @s_erdat
      AND bukrs IN @s_bukrs
      AND blart IN @s_blart.

  LOOP AT lt_parked INTO DATA(ls_parked).
    ls_event_log-invoicenumber = ls_parked-invoicenumber.
    ls_event_log-activityname = ls_parked-activityname.
    ls_event_log-eventtime = |{ ls_parked-eventtime(8) } { ls_parked-eventtime+8(2) }:{ ls_parked-eventtime+10(2) }:{ ls_parked-eventtime+12(2) }|.
    ls_event_log-username = ls_parked-username.
    ls_event_log-companycode = ls_parked-companycode.
    ls_event_log-vendornumber = ls_parked-vendornumber.
    ls_event_log-amountincompanycodecurrency = ls_parked-amountincompanycodecurrency.
    ls_event_log-paymentduedate = ''.
    ls_event_log-documenttype = ls_parked-documenttype.
    ls_event_log-paymentblockreason = ''.
    ls_event_log-purchasingdocument = ''.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

* --- 3, 4, 5. Sent For Approval, Approved, Rejected (Placeholder logic, needs adaptation)
* --- This logic is a generic template for SAP Business Workflow.
* --- Your implementation will vary. You must identify the correct workflow tasks.
  SELECT obj.instid, wi.wi_cd, wi.wi_ct, wi.wi_stat, wi.wi_aagent
    FROM sww_wi2obj AS obj
    JOIN swwlog     AS wi ON obj~instid = wi~wi_id
    INTO TABLE @DATA(lt_workflow)
    WHERE obj~typeid = 'BUS2081' " Business Object for Incoming Invoice
      AND obj~catid  = 'BO'
      AND wi~wi_cd IN s_erdat.

  LOOP AT lt_workflow INTO DATA(ls_workflow).
* --- This is a placeholder, adapt task IDs and logic
    CASE ls_workflow-wi_stat.
      WHEN 'STARTED'.
        ls_event_log-activityname = 'Invoice Sent For Approval'.
      WHEN 'COMPLETED'.
        ls_event_log-activityname = 'Invoice Approved'.
      WHEN 'CANCELLED'.
        ls_event_log-activityname = 'Invoice Rejected'.
      WHEN OTHERS.
        CONTINUE.
    ENDCASE.
* --- Code to get invoice details based on ls_workflow-instid needed here
* --- ... appending to lt_event_log ...
  ENDLOOP.

* --- 6, 7, 8. Payment Block Set/Removed, Data Updated (from Change Docs)
  SELECT h~objectid, h~username, h~udate, h~utime, p~fname, p~value_new, p~value_old
    FROM cdhdr AS h
    JOIN cdpos AS p ON h~objectclas = p~objectclas AND h~objectid = p~objectid AND h~changenr = p~changenr
    INTO TABLE @DATA(lt_changes)
    WHERE h~objectclas = 'BELEGV'
      AND h~udate IN s_erdat.

  LOOP AT lt_changes INTO DATA(ls_change).
    ls_event_log-invoicenumber = |{ ls_change-objectid+10(10) }{ ls_change-objectid(4) }|.
    ls_event_log-username = ls_change-username.
    ls_event_log-eventtime = |{ ls_change-udate } { ls_change-utime(2) }:{ ls_change-utime+2(2) }:{ ls_change-utime+4(2) }|.

    IF ls_change-fname = 'ZLSPR'. " Payment Block
      IF ls_change-value_old IS INITIAL AND ls_change-value_new IS NOT INITIAL.
        ls_event_log-activityname = 'Payment Block Set'.
        ls_event_log-paymentblockreason = ls_change-value_new.
      ELSEIF ls_change-value_old IS NOT INITIAL AND ls_change-value_new IS INITIAL.
        ls_event_log-activityname = 'Payment Block Removed'.
        ls_event_log-paymentblockreason = ''.
      ELSE.
        CONTINUE.
      ENDIF.
    ELSE.
      ls_event_log-activityname = 'Invoice Data Updated'.
    ENDIF.
* --- Need to select other attributes based on invoice number
* --- ... appending to lt_event_log ...
  ENDLOOP.

* --- 9. Invoice Posted
  SELECT CONCAT( bkpf~belnr, bkpf~gjahr ) AS invoicenumber,
         'Invoice Posted'               AS activityname,
         CONCAT( bkpf~cpudt, bkpf~cputm ) AS eventtime,
         bkpf~usnam                     AS username,
         bkpf~bukrs                     AS companycode,
         bseg~lifnr                     AS vendornumber,
         bseg~wrbtr                     AS amountincompanycodecurrency,
         bseg~zfBDT                     AS paymentduedate,
         bkpf~blart                     AS documenttype,
         bseg~zlspr                     AS paymentblockreason,
         bseg~ebeln                     AS purchasingdocument
    FROM bkpf
    JOIN bseg ON bkpf~bukrs = bseg~bukrs AND bkpf~belnr = bseg~belnr AND bkpf~gjahr = bseg~gjahr
    INTO TABLE @DATA(lt_posted)
    WHERE bkpf~cpudt IN @s_erdat
      AND bkpf~bukrs IN @s_bukrs
      AND bkpf~blart IN @s_blart
      AND bseg~koart = 'K'. " Vendor line

  LOOP AT lt_posted INTO DATA(ls_posted).
    ls_event_log-invoicenumber = ls_posted-invoicenumber.
    ls_event_log-activityname = ls_posted-activityname.
    ls_event_log-eventtime = |{ ls_posted-eventtime(8) } { ls_posted-eventtime+8(2) }:{ ls_posted-eventtime+10(2) }:{ ls_posted-eventtime+12(2) }|.
    ls_event_log-username = ls_posted-username.
    ls_event_log-companycode = ls_posted-companycode.
    ls_event_log-vendornumber = ls_posted-vendornumber.
    ls_event_log-amountincompanycodecurrency = ls_posted-amountincompanycodecurrency.
    ls_event_log-paymentduedate = ls_posted-paymentduedate.
    ls_event_log-documenttype = ls_posted-documenttype.
    ls_event_log-paymentblockreason = ls_posted-paymentblockreason.
    ls_event_log-purchasingdocument = ls_posted-purchasingdocument.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

* --- 10. Payment Proposal Created
  SELECT CONCAT( regup~belnr, regup~gjahr ) AS invoicenumber,
         'Payment Proposal Created'       AS activityname,
         CONCAT( reguh~erfdt, reguh~erfzt ) AS eventtime,
         reguh~erfbu                      AS username,
         regup~bukrs                      AS companycode,
         regup~lifnr                      AS vendornumber,
         regup~wrbtr                      AS amountincompanycodecurrency,
         ''                               AS paymentduedate,
         regup~blart                      AS documenttype,
         ''                               AS paymentblockreason,
         ''                               AS purchasingdocument
    FROM regup
    JOIN reguh ON regup~laufd = reguh~laufd AND regup~laufi = reguh~laufi
    INTO TABLE @DATA(lt_proposal)
    WHERE reguh~erfdt IN @s_erdat
      AND regup~bukrs IN @s_bukrs.

  LOOP AT lt_proposal INTO DATA(ls_proposal).
    ls_event_log-invoicenumber = ls_proposal-invoicenumber.
    ls_event_log-activityname = ls_proposal-activityname.
    ls_event_log-eventtime = |{ ls_proposal-eventtime(8) } { ls_proposal-eventtime+8(2) }:{ ls_proposal-eventtime+10(2) }:{ ls_proposal-eventtime+12(2) }|.
    ls_event_log-username = ls_proposal-username.
    ls_event_log-companycode = ls_proposal-companycode.
    ls_event_log-vendornumber = ls_proposal-vendornumber.
    ls_event_log-amountincompanycodecurrency = ls_proposal-amountincompanycodecurrency.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

* --- 11, 12. Payment Executed / Late Payment Executed
  SELECT CONCAT( belnr, gjahr ) AS invoicenumber,
         augdt,
         zfBDT
    FROM bsak
    INTO TABLE @DATA(lt_cleared)
    WHERE augdt IN @s_erdat
      AND bukrs IN @s_bukrs.

  LOOP AT lt_cleared INTO DATA(ls_cleared).
    IF ls_cleared-augdt > ls_cleared-zfbdt.
      ls_event_log-activityname = 'Late Payment Executed'.
    ELSE.
      ls_event_log-activityname = 'Payment Executed'.
    ENDIF.
    ls_event_log-invoicenumber = ls_cleared-invoicenumber.
    ls_event_log-eventtime = |{ ls_cleared-augdt } 00:00:00|.
*   --- Need to select other attributes based on invoice number
*   --- ... appending to lt_event_log ...
  ENDLOOP.

* --- 13. Invoice Reversed
  SELECT CONCAT( stblg, stjah )         AS invoicenumber,
         'Invoice Reversed'             AS activityname,
         CONCAT( cpudt, cputm )          AS eventtime,
         usnam                          AS username,
         bukrs                          AS companycode,
         ''                             AS vendornumber,
         ''                             AS amountincompanycodecurrency,
         ''                             AS paymentduedate,
         blart                          AS documenttype,
         ''                             AS paymentblockreason,
         ''                             AS purchasingdocument
    FROM bkpf
    INTO TABLE @DATA(lt_reversed)
    WHERE stblg IS NOT NULL
      AND cpudt IN @s_erdat
      AND bukrs IN @s_bukrs.

  LOOP AT lt_reversed INTO DATA(ls_reversed).
    ls_event_log-invoicenumber = ls_reversed-invoicenumber.
    ls_event_log-activityname = ls_reversed-activityname.
    ls_event_log-eventtime = |{ ls_reversed-eventtime(8) } { ls_reversed-eventtime+8(2) }:{ ls_reversed-eventtime+10(2) }:{ ls_reversed-eventtime+12(2) }|.
    ls_event_log-username = ls_reversed-username.
    ls_event_log-companycode = ls_reversed-companycode.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

* --- Write internal table to CSV file
  OPEN DATASET p_path FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
  IF sy-subrc <> 0.
    MESSAGE 'Error opening file.' TYPE 'E'.
  ENDIF.

  DATA: lv_line TYPE string.
  FIELD-SYMBOLS: <fs_any> TYPE any.

* --- Header row
  lv_line = 'InvoiceNumber,ActivityName,EventTime,UserName,CompanyCode,VendorNumber,AmountInCompanyCodeCurrency,PaymentDueDate,DocumentType,PaymentBlockReason,PurchasingDocument'.
  TRANSFER lv_line TO p_path.

  LOOP AT lt_event_log INTO ls_event_log.
    CLEAR lv_line.
    DO.
      ASSIGN COMPONENT sy-index OF STRUCTURE ls_event_log TO <fs_any>.
      IF sy-subrc <> 0.
        EXIT.
      ENDIF.
      IF sy-index = 1.
        lv_line = <fs_any>.
      ELSE.
        CONCATENATE lv_line <fs_any> INTO lv_line SEPARATED BY ','.
      ENDIF.
    ENDDO.
    TRANSFER lv_line TO p_path.
  ENDLOOP.

  CLOSE DATASET p_path.
  WRITE: / 'Extraction complete. File saved to:', p_path.