Šablona dat pro zpracování plateb závazků

/* SAP S/4HANA CDS View Extraction for Accounts Payable */
/* Combined Event Log Query */

/* 1. Invoice Parked */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Invoice Parked' AS Activity,
    JE.CreationDateTime AS EventTime,
    JE.CompanyCode,
    JEItem.Supplier AS VendorNumber,
    JEItem.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    NULL AS PaymentBlockReason,
    JE.CreatedByUser AS UserName,
    NULL AS ClearingDate,
    ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) AS NetDueDate,
    CASE WHEN JE.CreationDateTime > ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) THEN 'True' ELSE 'False' END AS IsLatePayment,
    'False' AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem 
    ON JE.CompanyCode = JEItem.CompanyCode 
    AND JE.AccountingDocument = JEItem.AccountingDocument 
    AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K' -- Vendor
  AND JE.AccountingDocumentCategory = 'V' -- Parked Document

UNION ALL

/* 2. Invoice Posted */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Invoice Posted' AS Activity,
    JE.CreationDateTime AS EventTime,
    JE.CompanyCode,
    JEItem.Supplier AS VendorNumber,
    JEItem.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    JEItem.PaymentBlockingReason AS PaymentBlockReason,
    JE.CreatedByUser AS UserName,
    NULL AS ClearingDate,
    ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) AS NetDueDate,
    'False' AS IsLatePayment,
    CASE WHEN JE.CreatedByUser = 'BATCH_USER' THEN 'True' ELSE 'False' END AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem 
    ON JE.CompanyCode = JEItem.CompanyCode 
    AND JE.AccountingDocument = JEItem.AccountingDocument 
    AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
  AND JE.AccountingDocumentCategory <> 'V' -- Exclude Parked

UNION ALL

/* 3. Price Variance Detected (Inferred at Posting) */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Price Variance Detected' AS Activity,
    JE.CreationDateTime AS EventTime,
    JE.CompanyCode,
    JEItem.Supplier AS VendorNumber,
    JEItem.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    JEItem.PaymentBlockingReason AS PaymentBlockReason,
    'System' AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS IsLatePayment,
    NULL AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem 
    ON JE.CompanyCode = JEItem.CompanyCode 
    AND JE.AccountingDocument = JEItem.AccountingDocument 
    AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
  AND JEItem.PaymentBlockingReason = 'R' -- Standard SAP Price Variance Block Key

UNION ALL

/* 4. Quantity Variance Detected (Inferred at Posting) */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Quantity Variance Detected' AS Activity,
    JE.CreationDateTime AS EventTime,
    JE.CompanyCode,
    JEItem.Supplier AS VendorNumber,
    JEItem.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    JEItem.PaymentBlockingReason AS PaymentBlockReason,
    'System' AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS IsLatePayment,
    NULL AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem 
    ON JE.CompanyCode = JEItem.CompanyCode 
    AND JE.AccountingDocument = JEItem.AccountingDocument 
    AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
  AND JEItem.PaymentBlockingReason = 'M' -- Standard SAP Quantity Variance Block Key

UNION ALL

/* 5. Payment Block Applied (via Change Document) */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Payment Block Applied' AS Activity,
    CD.CreationDateTime AS EventTime,
    JE.CompanyCode,
    JEItem.Supplier AS VendorNumber,
    JEItem.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    CD.NewValue AS PaymentBlockReason,
    CD.CreatedByUser AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS IsLatePayment,
    NULL AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_ChangeDocument AS CD
JOIN I_JournalEntry AS JE ON CD.ObjectValue = CONCAT(JE.CompanyCode, JE.AccountingDocument)
JOIN I_OperationalAcctgDocItem AS JEItem ON JE.AccountingDocument = JEItem.AccountingDocument AND JE.CompanyCode = JEItem.CompanyCode
WHERE CD.ObjectClass = 'BELEG'
  AND CD.TableName = 'BSEG'
  AND CD.FieldName = 'ZLSPR'
  AND CD.OldValue IS NULL AND CD.NewValue IS NOT NULL

UNION ALL

/* 6. Payment Block Removed (via Change Document) */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Payment Block Removed' AS Activity,
    CD.CreationDateTime AS EventTime,
    JE.CompanyCode,
    JEItem.Supplier AS VendorNumber,
    JEItem.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    NULL AS PaymentBlockReason,
    CD.CreatedByUser AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS IsLatePayment,
    NULL AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_ChangeDocument AS CD
JOIN I_JournalEntry AS JE ON CD.ObjectValue = CONCAT(JE.CompanyCode, JE.AccountingDocument)
JOIN I_OperationalAcctgDocItem AS JEItem ON JE.AccountingDocument = JEItem.AccountingDocument AND JE.CompanyCode = JEItem.CompanyCode
WHERE CD.ObjectClass = 'BELEG'
  AND CD.TableName = 'BSEG'
  AND CD.FieldName = 'ZLSPR'
  AND CD.OldValue IS NOT NULL AND (CD.NewValue IS NULL OR CD.NewValue = '')

UNION ALL

/* 7. Payment Terms Changed (via Change Document) */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Payment Terms Changed' AS Activity,
    CD.CreationDateTime AS EventTime,
    JE.CompanyCode,
    JEItem.Supplier AS VendorNumber,
    JEItem.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    CD.NewValue AS PaymentTerms,
    NULL AS PaymentBlockReason,
    CD.CreatedByUser AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS IsLatePayment,
    NULL AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_ChangeDocument AS CD
JOIN I_JournalEntry AS JE ON CD.ObjectValue = CONCAT(JE.CompanyCode, JE.AccountingDocument)
JOIN I_OperationalAcctgDocItem AS JEItem ON JE.AccountingDocument = JEItem.AccountingDocument AND JE.CompanyCode = JEItem.CompanyCode
WHERE CD.ObjectClass = 'BELEG'
  AND CD.TableName = 'BSEG'
  AND CD.FieldName = 'ZTERM'

UNION ALL

/* 8. Invoice Due (Calculated) */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Invoice Due' AS Activity,
    TO_TIMESTAMP(ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays))) AS EventTime,
    JE.CompanyCode,
    JEItem.Supplier AS VendorNumber,
    JEItem.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    NULL AS PaymentBlockReason,
    'System' AS UserName,
    NULL AS ClearingDate,
    ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) AS NetDueDate,
    NULL AS IsLatePayment,
    NULL AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem 
    ON JE.CompanyCode = JEItem.CompanyCode 
    AND JE.AccountingDocument = JEItem.AccountingDocument 
    AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
  AND ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) < CURRENT_DATE

UNION ALL

/* 9. Cash Discount Lost (Calculated) */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Cash Discount Lost' AS Activity,
    TO_TIMESTAMP(ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.CashDiscount1Days))) AS EventTime,
    JE.CompanyCode,
    JEItem.Supplier AS VendorNumber,
    JEItem.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    NULL AS PaymentBlockReason,
    'System' AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS IsLatePayment,
    NULL AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem 
    ON JE.CompanyCode = JEItem.CompanyCode 
    AND JE.AccountingDocument = JEItem.AccountingDocument 
    AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
  AND JEItem.CashDiscount1Days > 0
  AND (JEItem.ClearingDate IS NULL OR JEItem.ClearingDate > ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.CashDiscount1Days)))

UNION ALL

/* 10. Payment Proposal Created */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Payment Proposal Created' AS Activity,
    PPI.ProposalRunDate AS EventTime, -- Often just a date, cast to timestamp if needed
    JE.CompanyCode,
    PPI.Supplier AS VendorNumber,
    PPI.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    NULL AS PaymentBlockReason,
    PPI.CreatedByUser AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS IsLatePayment,
    NULL AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_PaymentProposalItem AS PPI
JOIN I_JournalEntry AS JE 
    ON PPI.CompanyCode = JE.CompanyCode 
    AND PPI.AccountingDocument = JE.AccountingDocument 
    AND PPI.FiscalYear = JE.FiscalYear
JOIN I_OperationalAcctgDocItem AS JEItem
    ON JE.CompanyCode = JEItem.CompanyCode 
    AND JE.AccountingDocument = JEItem.AccountingDocument 
    AND JEItem.FinancialAccountType = 'K'

UNION ALL

/* 11. Payment Run Executed */
/* Derived from existence in payment tables with a run ID */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Payment Run Executed' AS Activity,
    PPI.PaymentRunDate AS EventTime,
    JE.CompanyCode,
    PPI.Supplier AS VendorNumber,
    PPI.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    NULL AS PaymentBlockReason,
    PPI.CreatedByUser AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS IsLatePayment,
    NULL AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_PaymentProposalItem AS PPI
JOIN I_JournalEntry AS JE 
    ON PPI.CompanyCode = JE.CompanyCode 
    AND PPI.AccountingDocument = JE.AccountingDocument
JOIN I_OperationalAcctgDocItem AS JEItem
    ON JE.CompanyCode = JEItem.CompanyCode 
    AND JE.AccountingDocument = JEItem.AccountingDocument
WHERE PPI.PaymentRunID IS NOT NULL

UNION ALL

/* 12. Payment Document Created */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Payment Document Created' AS Activity,
    PayJE.CreationDateTime AS EventTime,
    JE.CompanyCode,
    JEItem.Supplier AS VendorNumber,
    JEItem.AmountInTransactionCurrency AS InvoiceAmount,
    PayJE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    NULL AS PaymentBlockReason,
    PayJE.CreatedByUser AS UserName,
    PayJE.PostingDate AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS IsLatePayment,
    NULL AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem 
    ON JE.CompanyCode = JEItem.CompanyCode 
    AND JE.AccountingDocument = JEItem.AccountingDocument 
    AND JE.FiscalYear = JEItem.FiscalYear
JOIN I_JournalEntry AS PayJE
    ON JEItem.ClearingJournalEntry = PayJE.AccountingDocument
    AND JEItem.ClearingJournalEntryFiscalYear = PayJE.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
  AND JEItem.ClearingJournalEntry IS NOT NULL

UNION ALL

/* 13. Payment Cleared */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Payment Cleared' AS Activity,
    TO_TIMESTAMP(JEItem.ClearingDate) AS EventTime,
    JE.CompanyCode,
    JEItem.Supplier AS VendorNumber,
    JEItem.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    NULL AS PaymentBlockReason,
    'System' AS UserName,
    JEItem.ClearingDate,
    ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) AS NetDueDate,
    CASE WHEN JEItem.ClearingDate > ADD_DAYS(JEItem.DocumentItemDate, TO_INTEGER(JEItem.NetPaymentDays)) THEN 'True' ELSE 'False' END AS IsLatePayment,
    NULL AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem 
    ON JE.CompanyCode = JEItem.CompanyCode 
    AND JE.AccountingDocument = JEItem.AccountingDocument 
    AND JE.FiscalYear = JEItem.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
  AND JEItem.ClearingDate IS NOT NULL

UNION ALL

/* 14. Invoice Reversed */
SELECT
    JE.OriginalReferenceDocument AS InvoiceNumber,
    'Invoice Reversed' AS Activity,
    RevJE.CreationDateTime AS EventTime,
    JE.CompanyCode,
    JEItem.Supplier AS VendorNumber,
    JEItem.AmountInTransactionCurrency AS InvoiceAmount,
    JE.AccountingDocumentType AS DocumentType,
    JEItem.PaymentTerms,
    NULL AS PaymentBlockReason,
    RevJE.CreatedByUser AS UserName,
    NULL AS ClearingDate,
    NULL AS NetDueDate,
    NULL AS IsLatePayment,
    NULL AS IsTouchless,
    'S4HANA' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM I_JournalEntry AS JE
JOIN I_OperationalAcctgDocItem AS JEItem 
    ON JE.CompanyCode = JEItem.CompanyCode 
    AND JE.AccountingDocument = JEItem.AccountingDocument 
    AND JE.FiscalYear = JEItem.FiscalYear
JOIN I_JournalEntry AS RevJE
    ON JE.ReverseDocument = RevJE.AccountingDocument
    AND JE.ReverseDocumentFiscalYear = RevJE.FiscalYear
WHERE JEItem.FinancialAccountType = 'K'
  AND JE.ReverseDocument IS NOT NULL

WITH
params AS (
    SELECT
        TO_DATE('[Start date]', 'YYYYMMDD') AS start_date,
        TO_DATE('[End date]', 'YYYYMMDD') AS end_date,
        '[Source system identifier]' AS source_system
    FROM DUMMY
),
base_bkpf AS (
    SELECT
        b.MANDT,
        b.BUKRS,
        b.BELNR,
        b.GJAHR,
        b.BLART,
        b.BLDAT,
        b.BUDAT,
        b.CPUDT,
        b.CPUTM,
        b.USNAM,
        b.STBLG,
        b.STJAH,
        b.XBLNR,
        b.WAERS,
        b.BKTXT,
        p.source_system,
        p.start_date,
        p.end_date
    FROM [Your SAP HANA schema].BKPF b
    CROSS JOIN params p
    WHERE b.BUDAT BETWEEN TO_VARCHAR(p.start_date, 'YYYYMMDD') AND TO_VARCHAR(p.end_date, 'YYYYMMDD')
      AND [Company code filter]
),
invoice_bseg AS (
    SELECT
        k.MANDT,
        k.BUKRS,
        k.BELNR,
        k.GJAHR,
        k.BLART,
        k.BLDAT,
        k.BUDAT,
        k.CPUDT,
        k.CPUTM,
        k.USNAM,
        k.STBLG,
        k.STJAH,
        k.XBLNR,
        k.WAERS,
        k.BKTXT,
        k.source_system,
        s.BUZEI,
        s.LIFNR,
        s.WRBTR,
        s.DMBTR,
        s.ZTERM,
        s.ZLSPR,
        s.ZFBDT,
        s.ZBD1T,
        s.ZBD2T,
        s.ZBD3T,
        s.AUGDT,
        s.AUGBL,
        s.SHKZG,
        s.SGTXT,
        s.XREF1,
        s.XREF2,
        s.XREF3
    FROM base_bkpf k
    INNER JOIN [Your SAP HANA schema].BSEG s
        ON s.MANDT = k.MANDT
       AND s.BUKRS = k.BUKRS
       AND s.BELNR = k.BELNR
       AND s.GJAHR = k.GJAHR
    WHERE s.LIFNR IS NOT NULL
      AND s.LIFNR <> ''
),
acdoca_invoice AS (
    SELECT
        a.RCLNT AS MANDT,
        a.RBUKRS AS BUKRS,
        a.BELNR,
        a.GJAHR,
        a.BUZEI,
        a.RACCT,
        a.HSL,
        a.WSL,
        a.RWCUR,
        a.BUDAT,
        a.BLDAT,
        a.AUGDT,
        a.AUGBL
    FROM [Your SAP HANA schema].ACDOCA a
    WHERE a.BUDAT BETWEEN TO_VARCHAR((SELECT start_date FROM params), 'YYYYMMDD')
                      AND TO_VARCHAR((SELECT end_date FROM params), 'YYYYMMDD')
      AND [ACDOCA company code filter]
),
invoice_cases AS (
    SELECT DISTINCT
        i.MANDT,
        i.BUKRS,
        i.BELNR,
        i.GJAHR,
        i.BUZEI,
        i.LIFNR,
        i.WRBTR,
        i.ZTERM,
        i.ZLSPR,
        i.ZFBDT,
        i.ZBD1T,
        i.ZBD2T,
        i.ZBD3T,
        i.AUGDT,
        i.AUGBL,
        i.BLART,
        i.BLDAT,
        i.BUDAT,
        i.CPUDT,
        i.CPUTM,
        i.USNAM,
        i.STBLG,
        i.STJAH,
        i.XBLNR,
        i.WAERS,
        i.source_system,
        COALESCE(NULLIF(i.XBLNR, ''), i.BELNR) AS InvoiceNumber,
        ADD_DAYS(TO_DATE(COALESCE(NULLIF(i.ZFBDT, ''), i.BLDAT), 'YYYYMMDD'), COALESCE(TO_INTEGER(NULLIF(i.ZBD3T, '')), 0)) AS NetDueDate,
        ADD_DAYS(TO_DATE(COALESCE(NULLIF(i.ZFBDT, ''), i.BLDAT), 'YYYYMMDD'), COALESCE(TO_INTEGER(NULLIF(i.ZBD1T, '')), 0)) AS DiscountDueDate1,
        ADD_DAYS(TO_DATE(COALESCE(NULLIF(i.ZFBDT, ''), i.BLDAT), 'YYYYMMDD'), COALESCE(TO_INTEGER(NULLIF(i.ZBD2T, '')), 0)) AS DiscountDueDate2
    FROM invoice_bseg i
    LEFT JOIN acdoca_invoice a
        ON a.MANDT = i.MANDT
       AND a.BUKRS = i.BUKRS
       AND a.BELNR = i.BELNR
       AND a.GJAHR = i.GJAHR
       AND a.BUZEI = i.BUZEI
),
change_events AS (
    SELECT
        c.MANDT,
        c.OBJECTID,
        c.TABKEY,
        c.FNAME,
        c.VALUE_OLD,
        c.VALUE_NEW,
        c.UDATE,
        c.UTIME,
        c.USERNAME
    FROM [Your SAP HANA schema].[Your change document item table name] c
    WHERE c.TABNAME = 'BSEG'
      AND c.FNAME IN ('ZLSPR', 'ZTERM')
      AND c.UDATE BETWEEN TO_VARCHAR((SELECT start_date FROM params), 'YYYYMMDD')
                      AND TO_VARCHAR((SELECT end_date FROM params), 'YYYYMMDD')
      AND [Change document object class filter]
),
reguh_events AS (
    SELECT
        r.MANDT,
        r.LAUFD,
        r.LAUFI,
        r.XVORL,
        r.ZBUKR,
        r.LIFNR,
        r.VBLNR,
        r.AUSFD,
        r.AUSDT,
        r.LAUFD AS RunDate,
        r.ZALDT,
        r.RZAWE,
        r.XEINZ,
        r.XPGRO,
        r.XAVIS,
        r.XVORL AS ProposalIndicator
    FROM [Your SAP HANA schema].REGUH r
    WHERE r.LAUFD BETWEEN TO_VARCHAR((SELECT start_date FROM params), 'YYYYMMDD')
                      AND TO_VARCHAR((SELECT end_date FROM params), 'YYYYMMDD')
),
regup_events AS (
    SELECT
        u.MANDT,
        u.LAUFD,
        u.LAUFI,
        u.ZBUKR,
        u.LIFNR,
        u.VBLNR,
        u.BUKRS,
        u.BELNR,
        u.GJAHR,
        u.BUZEI,
        u.XVORL,
        u.AUGBL,
        u.AUGDT
    FROM [Your SAP HANA schema].REGUP u
    WHERE u.LAUFD BETWEEN TO_VARCHAR((SELECT start_date FROM params), 'YYYYMMDD')
                      AND TO_VARCHAR((SELECT end_date FROM params), 'YYYYMMDD')
),
events AS (
    SELECT
        i.InvoiceNumber,
        'Invoice Parked' AS Activity,
        TO_TIMESTAMP(i.CPUDT || LPAD(COALESCE(i.CPUTM, '000000'), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        i.source_system AS SourceSystem,
        CURRENT_TIMESTAMP AS LastDataUpdate,
        i.LIFNR AS VendorNumber,
        i.BUKRS AS CompanyCode,
        i.WRBTR AS InvoiceAmount,
        i.BLART AS DocumentType,
        i.ZTERM AS PaymentTerms,
        i.ZLSPR AS PaymentBlockReason,
        i.USNAM AS UserName,
        i.AUGDT AS ClearingDate,
        i.NetDueDate,
        CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END AS IsLatePayment,
        FALSE AS IsTouchless
    FROM invoice_cases i
    INNER JOIN [Your SAP HANA schema].VBKPF v
        ON v.MANDT = i.MANDT
       AND v.BUKRS = i.BUKRS
       AND v.BELNR = i.BELNR
       AND v.GJAHR = i.GJAHR
    WHERE [VBKPF parked status predicate]

    UNION ALL
    SELECT i.InvoiceNumber, 'Invoice Posted', TO_TIMESTAMP(i.CPUDT || LPAD(COALESCE(i.CPUTM, '000000'), 6, '0'), 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, i.BLART, i.ZTERM, i.ZLSPR, i.USNAM, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i

    UNION ALL
    SELECT i.InvoiceNumber, 'Payment Block Applied', TO_TIMESTAMP(c.UDATE || LPAD(COALESCE(c.UTIME, '000000'), 6, '0'), 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, i.BLART, i.ZTERM, c.VALUE_NEW, c.USERNAME, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i INNER JOIN change_events c ON c.OBJECTID = i.BELNR AND c.FNAME = 'ZLSPR' AND COALESCE(c.VALUE_OLD, '') = '' AND COALESCE(c.VALUE_NEW, '') <> ''

    UNION ALL
    SELECT i.InvoiceNumber, 'Price Variance Detected', TO_TIMESTAMP(i.CPUDT || LPAD(COALESCE(i.CPUTM, '000000'), 6, '0'), 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, i.BLART, i.ZTERM, i.ZLSPR, i.USNAM, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i WHERE i.ZLSPR = 'R'

    UNION ALL
    SELECT i.InvoiceNumber, 'Quantity Variance Detected', TO_TIMESTAMP(i.CPUDT || LPAD(COALESCE(i.CPUTM, '000000'), 6, '0'), 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, i.BLART, i.ZTERM, i.ZLSPR, i.USNAM, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i WHERE i.ZLSPR = 'M'

    UNION ALL
    SELECT i.InvoiceNumber, 'Payment Terms Changed', TO_TIMESTAMP(c.UDATE || LPAD(COALESCE(c.UTIME, '000000'), 6, '0'), 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, i.BLART, c.VALUE_NEW, i.ZLSPR, c.USERNAME, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i INNER JOIN change_events c ON c.OBJECTID = i.BELNR AND c.FNAME = 'ZTERM'

    UNION ALL
    SELECT i.InvoiceNumber, 'Payment Block Removed', TO_TIMESTAMP(c.UDATE || LPAD(COALESCE(c.UTIME, '000000'), 6, '0'), 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, i.BLART, i.ZTERM, c.VALUE_OLD, c.USERNAME, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i INNER JOIN change_events c ON c.OBJECTID = i.BELNR AND c.FNAME = 'ZLSPR' AND COALESCE(c.VALUE_OLD, '') <> '' AND COALESCE(c.VALUE_NEW, '') = ''

    UNION ALL
    SELECT i.InvoiceNumber, 'Invoice Due', TO_TIMESTAMP(TO_VARCHAR(i.NetDueDate, 'YYYYMMDD') || '000000', 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, i.BLART, i.ZTERM, i.ZLSPR, i.USNAM, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i WHERE i.NetDueDate IS NOT NULL

    UNION ALL
    SELECT i.InvoiceNumber, 'Cash Discount Lost', TO_TIMESTAMP(TO_VARCHAR(CASE WHEN i.DiscountDueDate1 <= i.DiscountDueDate2 THEN i.DiscountDueDate1 ELSE i.DiscountDueDate2 END, 'YYYYMMDD') || '000000', 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, i.BLART, i.ZTERM, i.ZLSPR, i.USNAM, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i WHERE i.AUGDT IS NULL OR i.AUGDT > CASE WHEN i.DiscountDueDate1 <= i.DiscountDueDate2 THEN i.DiscountDueDate1 ELSE i.DiscountDueDate2 END

    UNION ALL
    SELECT i.InvoiceNumber, 'Payment Proposal Created', TO_TIMESTAMP(r.RunDate || '000000', 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, i.BLART, i.ZTERM, i.ZLSPR, i.USNAM, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i INNER JOIN regup_events u ON u.BUKRS = i.BUKRS AND u.BELNR = i.BELNR AND u.GJAHR = i.GJAHR AND u.BUZEI = i.BUZEI INNER JOIN reguh_events r ON r.MANDT = u.MANDT AND r.LAUFD = u.LAUFD AND r.LAUFI = u.LAUFI AND r.LIFNR = u.LIFNR WHERE [Payment proposal status predicate]

    UNION ALL
    SELECT i.InvoiceNumber, 'Payment Run Executed', TO_TIMESTAMP(r.RunDate || '000000', 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, i.BLART, i.ZTERM, i.ZLSPR, i.USNAM, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i INNER JOIN regup_events u ON u.BUKRS = i.BUKRS AND u.BELNR = i.BELNR AND u.GJAHR = i.GJAHR AND u.BUZEI = i.BUZEI INNER JOIN reguh_events r ON r.MANDT = u.MANDT AND r.LAUFD = u.LAUFD AND r.LAUFI = u.LAUFI AND r.LIFNR = u.LIFNR WHERE [Payment run executed status predicate]

    UNION ALL
    SELECT i.InvoiceNumber, 'Payment Document Created', TO_TIMESTAMP(p.CPUDT || LPAD(COALESCE(p.CPUTM, '000000'), 6, '0'), 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, p.BLART, i.ZTERM, i.ZLSPR, p.USNAM, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i INNER JOIN [Your SAP HANA schema].BKPF p ON p.MANDT = i.MANDT AND p.BUKRS = i.BUKRS AND p.BELNR = i.AUGBL AND p.GJAHR = i.GJAHR WHERE p.BLART IN ('ZP', 'KZ')

    UNION ALL
    SELECT i.InvoiceNumber, 'Payment Cleared', TO_TIMESTAMP(i.AUGDT || '000000', 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, i.BLART, i.ZTERM, i.ZLSPR, i.USNAM, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i WHERE i.AUGDT IS NOT NULL

    UNION ALL
    SELECT i.InvoiceNumber, 'Invoice Reversed', TO_TIMESTAMP(i.CPUDT || LPAD(COALESCE(i.CPUTM, '000000'), 6, '0'), 'YYYYMMDDHH24MISS'), i.source_system, CURRENT_TIMESTAMP, i.LIFNR, i.BUKRS, i.WRBTR, i.BLART, i.ZTERM, i.ZLSPR, i.USNAM, i.AUGDT, i.NetDueDate, CASE WHEN i.AUGDT IS NOT NULL AND i.AUGDT > i.NetDueDate THEN TRUE ELSE FALSE END, FALSE
    FROM invoice_cases i WHERE i.STBLG IS NOT NULL AND i.STBLG <> ''
)
SELECT
    InvoiceNumber,
    Activity,
    EventTime,
    SourceSystem,
    LastDataUpdate,
    VendorNumber,
    CompanyCode,
    InvoiceAmount,
    DocumentType,
    PaymentTerms,
    PaymentBlockReason,
    UserName,
    ClearingDate,
    NetDueDate,
    IsLatePayment,
    IsTouchless
FROM events
WHERE EventTime IS NOT NULL
ORDER BY InvoiceNumber, EventTime, Activity;