Borç Hesapları Ödeme İşleme Veri Template'iniz

/* 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

/* 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