Mẫu dữ liệu Order-to-Cash: thanh toán và lập hóa đơn

WITH BaseInvoices AS (
  SELECT
    bd.BillingDocument AS InvoiceNumber,
    bd.CreationDateTime,
    bd.BillingDocumentDate AS InvoiceDate,
    bd.NetDueDate AS PaymentDueDate,
    bd.TotalNetAmount AS InvoiceAmount,
    bd.CreatedByUser AS UserName,
    bd.SDDocumentPostingStatus,
    bd.AccountingDocument,
    bd.IsCancelled,
    bd.CancelledBillingDocument,
    bd.PrecedingSDDocument,
    bd.CompanyCode,
    bd.BillingDocumentType,
    cust.CustomerName,
    reg.RegionName AS Region
  FROM I_BillingDocument AS bd
  LEFT JOIN I_Customer AS cust ON bd.SoldToParty = cust.Customer
  LEFT JOIN I_Region AS reg ON cust.Region = reg.Region
  WHERE
    bd.BillingDocumentDate BETWEEN '2023-01-01' AND '2023-12-31' -- Placeholder: Set your date range
    AND bd.CompanyCode = 'XXXX' -- Placeholder: Set your Company Code
    AND bd.BillingCategory IN ('M', 'N', 'O', 'P', 'U', 'V', '5', '6') -- Filters for customer invoices/credit memos
)
-- 1. Invoice Generated
SELECT
  bi.InvoiceNumber,
  'Invoice Generated' AS ActivityName,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(bi.CreationDateTime) AS EventTime,
  bi.UserName,
  bi.InvoiceDate,
  bi.PaymentDueDate,
  bi.InvoiceAmount,
  bi.CustomerName,
  bi.Region,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(bi.CreationDateTime) AS EndTime
FROM BaseInvoices AS bi

UNION ALL

-- 2. Invoice Posting Blocked
SELECT
  bi.InvoiceNumber,
  'Invoice Posting Blocked' AS ActivityName,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(bi.CreationDateTime) AS EventTime,
  bi.UserName,
  bi.InvoiceDate,
  bi.PaymentDueDate,
  bi.InvoiceAmount,
  bi.CustomerName,
  bi.Region,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(bi.CreationDateTime) AS EndTime
FROM BaseInvoices AS bi
WHERE bi.SDDocumentPostingStatus = 'A' -- A = Billing document blocked for posting

UNION ALL

-- 3. Invoice Posted To Accounting
SELECT DISTINCT
  bi.InvoiceNumber,
  'Invoice Posted To Accounting' AS ActivityName,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(je.CreationDateTime) AS EventTime,
  je.CreatedByUser AS UserName,
  bi.InvoiceDate,
  bi.PaymentDueDate,
  bi.InvoiceAmount,
  bi.CustomerName,
  bi.Region,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(je.CreationDateTime) AS EndTime
FROM BaseInvoices AS bi
JOIN I_JournalEntry AS je ON bi.AccountingDocument = je.AccountingDocument
WHERE bi.AccountingDocument IS NOT NULL AND bi.AccountingDocument <> ''

UNION ALL

-- 4. Invoice Sent To Customer
SELECT DISTINCT
  bi.InvoiceNumber,
  'Invoice Sent To Customer' AS ActivityName,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(om.OutputRequestLastChgDateTime) AS EventTime,
  om.CreatedByUser AS UserName,
  bi.InvoiceDate,
  bi.PaymentDueDate,
  bi.InvoiceAmount,
  bi.CustomerName,
  bi.Region,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(om.OutputRequestLastChgDateTime) AS EndTime
FROM BaseInvoices AS bi
JOIN I_Outgmgmtdocumentoutputreq AS om ON bi.InvoiceNumber = om.SenderBusinessObject
WHERE om.OutputRequestStatus = 'S' -- Status 'S' for 'Successfully Processed'

UNION ALL

-- 5. Payment Due Date Reached
SELECT
  bi.InvoiceNumber,
  'Payment Due Date Reached' AS ActivityName,
  CAST(bi.PaymentDueDate AS TIMESTAMP) AS EventTime,
  'System' AS UserName,
  bi.InvoiceDate,
  bi.PaymentDueDate,
  bi.InvoiceAmount,
  bi.CustomerName,
  bi.Region,
  CAST(bi.PaymentDueDate AS TIMESTAMP) AS EndTime
FROM BaseInvoices AS bi
WHERE bi.PaymentDueDate IS NOT NULL AND bi.PaymentDueDate <= CURRENT_DATE

UNION ALL

-- 6. Customer Dispute Opened
SELECT DISTINCT
  bi.InvoiceNumber,
  'Customer Dispute Opened' AS ActivityName,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(dc.CreationDateTime) AS EventTime,
  dc.CreatedByUser AS UserName,
  bi.InvoiceDate,
  bi.PaymentDueDate,
  bi.InvoiceAmount,
  bi.CustomerName,
  bi.Region,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(dc.CreationDateTime) AS EndTime
FROM BaseInvoices AS bi
JOIN I_DisputedItem AS di ON bi.InvoiceNumber = di.BillingDocument
JOIN I_DisputeCase AS dc ON di.DisputeCase = dc.DisputeCase

UNION ALL

-- 7. Payment Reminder Issued
SELECT DISTINCT
  bi.InvoiceNumber,
  'Payment Reminder Issued' AS ActivityName,
  CAST(dh.DunningRunDate AS TIMESTAMP) AS EventTime,
  dh.DunningRunUser AS UserName,
  bi.InvoiceDate,
  bi.PaymentDueDate,
  bi.InvoiceAmount,
  bi.CustomerName,
  bi.Region,
  CAST(dh.DunningRunDate AS TIMESTAMP) AS EndTime
FROM BaseInvoices AS bi
JOIN I_JournalEntryItem AS jei ON bi.AccountingDocument = jei.AccountingDocument AND bi.CompanyCode = jei.CompanyCode
JOIN I_DunningHistory AS dh ON jei.CompanyCode = dh.CompanyCode AND jei.Customer = dh.Customer AND jei.AccountingDocument = dh.AccountingDocument

UNION ALL

-- 8, 9, 10. Payment Received, Cash Applied/Reconciled, Invoice Closed
SELECT
  bi.InvoiceNumber,
  ActivityName,
  EventTime,
  clearing_je.CreatedByUser AS UserName,
  bi.InvoiceDate,
  bi.PaymentDueDate,
  bi.InvoiceAmount,
  bi.CustomerName,
  bi.Region,
  EventTime AS EndTime
FROM BaseInvoices AS bi
JOIN I_JournalEntryItem AS jei ON bi.AccountingDocument = jei.AccountingDocument AND bi.Customer IS NOT NULL
JOIN I_JournalEntry AS clearing_je ON jei.ClearingJournalEntry = clearing_je.AccountingDocument
CROSS JOIN (
  VALUES ('Customer Payment Received'), ('Cash Applied/Reconciled'), ('Invoice Closed')
) AS Activities(ActivityName)
WHERE jei.ClearingDate IS NOT NULL AND jei.ClearingJournalEntry IS NOT NULL AND jei.ClearingJournalEntry <> ''

UNION ALL

-- 11. Invoice Cancelled
SELECT
  bi.InvoiceNumber,
  'Invoice Cancelled' AS ActivityName,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(cancellation_doc.CreationDateTime) AS EventTime,
  cancellation_doc.CreatedByUser AS UserName,
  bi.InvoiceDate,
  bi.PaymentDueDate,
  bi.InvoiceAmount,
  bi.CustomerName,
  bi.Region,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(cancellation_doc.CreationDateTime) AS EndTime
FROM BaseInvoices AS bi
JOIN I_BillingDocument AS cancellation_doc ON bi.CancelledBillingDocument = cancellation_doc.BillingDocument
WHERE bi.IsCancelled = 'X'

UNION ALL

-- 12. Credit Memo Created
SELECT
  bi.InvoiceNumber,
  'Credit Memo Created' AS ActivityName,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(bi.CreationDateTime) AS EventTime,
  bi.UserName,
  bi.InvoiceDate,
  bi.PaymentDueDate,
  bi.InvoiceAmount,
  bi.CustomerName,
  bi.Region,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(bi.CreationDateTime) AS EndTime
FROM BaseInvoices AS bi
WHERE bi.BillingDocumentType IN ('G2') -- Placeholder: Adjust with your credit memo document types

UNION ALL

-- 13. Billing Rework Identified
WITH CancelledInvoices AS (
    SELECT
        bi.PrecedingSDDocument,
        bi.CompanyCode,
        cancellation_doc.CreationDateTime AS CancellationTime
    FROM BaseInvoices bi
    JOIN I_BillingDocument AS cancellation_doc ON bi.CancelledBillingDocument = cancellation_doc.BillingDocument
    WHERE bi.IsCancelled = 'X' AND bi.PrecedingSDDocument IS NOT NULL AND bi.PrecedingSDDocument <> ''
)
SELECT
  rework.InvoiceNumber,
  'Billing Rework Identified' AS ActivityName,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(rework.CreationDateTime) AS EventTime,
  rework.UserName,
  rework.InvoiceDate,
  rework.PaymentDueDate,
  rework.InvoiceAmount,
  rework.CustomerName,
  rework.Region,
  ABAP_SYSTEM_UTCL_TO_TIMESTAMP(rework.CreationDateTime) AS EndTime
FROM BaseInvoices AS rework
JOIN CancelledInvoices AS cancelled ON rework.PrecedingSDDocument = cancelled.PrecedingSDDocument
  AND rework.CompanyCode = cancelled.CompanyCode
WHERE rework.CreationDateTime > cancelled.CancellationTime AND rework.IsCancelled = ''
ORDER BY InvoiceNumber, EventTime;

WITH
billing_headers AS (
    SELECT
        h.VBELN AS InvoiceNumber,
        h.FKDAT AS InvoiceDate,
        h.NETWR AS InvoiceAmount,
        h.KUNAG AS CustomerNumber,
        h.ERDAT AS BillingCreatedDate,
        h.ERZET AS BillingCreatedTime,
        h.ERNAM AS BillingCreatedBy,
        h.BUKRS AS CompanyCode,
        h.FKART AS BillingType,
        h.FKSTO AS CancellationIndicator,
        h.RFBSK AS AccountingPostingStatus,
        h.ZTERM AS PaymentTerms,
        h.ZFBDT AS BaselineDate,
        h.NETDT AS PaymentDueDate,
        h.VBELV AS PrecedingDocument
    FROM [Your HANA schema].VBRK h
    WHERE h.ERDAT >= '[Start date, YYYY-MM-DD]'
      AND h.ERDAT < '[End date, YYYY-MM-DD]'
      AND h.BUKRS IN ([Company code filter])
      AND h.FKART IN ([Billing document type filter])
),
customer_data AS (
    SELECT
        c.KUNNR AS CustomerNumber,
        c.NAME1 AS CustomerName,
        c.REGION AS Region
    FROM [Your customer master source] c
),
invoice_base AS (
    SELECT
        b.InvoiceNumber,
        b.InvoiceDate,
        b.InvoiceAmount,
        b.CustomerNumber,
        c.CustomerName,
        c.Region,
        b.BillingCreatedDate,
        b.BillingCreatedTime,
        b.BillingCreatedBy,
        b.CompanyCode,
        b.BillingType,
        b.CancellationIndicator,
        b.AccountingPostingStatus,
        b.PaymentTerms,
        b.BaselineDate,
        b.PaymentDueDate,
        b.PrecedingDocument
    FROM billing_headers b
    LEFT JOIN customer_data c
      ON c.CustomerNumber = b.CustomerNumber
),
events AS (
    SELECT
        i.InvoiceNumber,
        'Invoice Generated' AS ActivityName,
        TO_TIMESTAMP(TO_VARCHAR(i.BillingCreatedDate) || ' ' || TO_VARCHAR(i.BillingCreatedTime)) AS EventTime,
        i.BillingCreatedBy AS UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        i.InvoiceAmount,
        i.CustomerName,
        i.Region,
        TO_TIMESTAMP(TO_VARCHAR(i.BillingCreatedDate) || ' ' || TO_VARCHAR(i.BillingCreatedTime)) AS EndTime
    FROM invoice_base i

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Invoice Posting Blocked' AS ActivityName,
        COALESCE(a.StatusTimestamp, TO_TIMESTAMP(TO_VARCHAR(i.BillingCreatedDate) || ' ' || TO_VARCHAR(i.BillingCreatedTime))) AS EventTime,
        a.UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        i.InvoiceAmount,
        i.CustomerName,
        i.Region,
        COALESCE(a.StatusTimestamp, TO_TIMESTAMP(TO_VARCHAR(i.BillingCreatedDate) || ' ' || TO_VARCHAR(i.BillingCreatedTime))) AS EndTime
    FROM invoice_base i
    INNER JOIN [Your accounting status source] a
      ON a.InvoiceNumber = i.InvoiceNumber
     AND a.PostingStatus = '[Posting blocked status value]'

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Invoice Posted To Accounting' AS ActivityName,
        a.StatusTimestamp AS EventTime,
        a.UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        i.InvoiceAmount,
        i.CustomerName,
        i.Region,
        a.StatusTimestamp AS EndTime
    FROM invoice_base i
    INNER JOIN [Your accounting status source] a
      ON a.InvoiceNumber = i.InvoiceNumber
     AND a.PostingStatus = '[Posted status value]'

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Invoice Sent To Customer' AS ActivityName,
        o.SentTimestamp AS EventTime,
        o.UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        i.InvoiceAmount,
        i.CustomerName,
        i.Region,
        o.SentTimestamp AS EndTime
    FROM invoice_base i
    INNER JOIN [Your output management source] o
      ON o.InvoiceNumber = i.InvoiceNumber
     AND o.OutputStatus = '[Successfully sent status value]'

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Payment Due Date Reached' AS ActivityName,
        CAST(i.PaymentDueDate AS TIMESTAMP) AS EventTime,
        CAST(NULL AS NVARCHAR(80)) AS UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        i.InvoiceAmount,
        i.CustomerName,
        i.Region,
        CAST(i.PaymentDueDate AS TIMESTAMP) AS EndTime
    FROM invoice_base i
    WHERE i.PaymentDueDate IS NOT NULL

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Customer Dispute Opened' AS ActivityName,
        d.OpenedTimestamp AS EventTime,
        d.UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        i.InvoiceAmount,
        i.CustomerName,
        i.Region,
        d.OpenedTimestamp AS EndTime
    FROM invoice_base i
    INNER JOIN [Your dispute management source] d
      ON d.InvoiceNumber = i.InvoiceNumber

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Payment Reminder Issued' AS ActivityName,
        r.IssuedTimestamp AS EventTime,
        r.UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        i.InvoiceAmount,
        i.CustomerName,
        i.Region,
        r.IssuedTimestamp AS EndTime
    FROM invoice_base i
    INNER JOIN [Your dunning or payment reminder source] r
      ON r.InvoiceNumber = i.InvoiceNumber

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Customer Payment Received' AS ActivityName,
        p.ReceivedTimestamp AS EventTime,
        p.UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        i.InvoiceAmount,
        i.CustomerName,
        i.Region,
        p.ReceivedTimestamp AS EndTime
    FROM invoice_base i
    INNER JOIN [Your incoming payment source] p
      ON p.CustomerNumber = i.CustomerNumber
     AND p.CompanyCode = i.CompanyCode
     AND p.ReceivedTimestamp >= TO_TIMESTAMP(TO_VARCHAR(i.InvoiceDate))

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Cash Applied/Reconciled' AS ActivityName,
        cl.ClearedTimestamp AS EventTime,
        cl.UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        i.InvoiceAmount,
        i.CustomerName,
        i.Region,
        cl.ClearedTimestamp AS EndTime
    FROM invoice_base i
    INNER JOIN [Your accounts receivable clearing source] cl
      ON cl.InvoiceNumber = i.InvoiceNumber

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Invoice Closed' AS ActivityName,
        cl.ClearedTimestamp AS EventTime,
        cl.UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        i.InvoiceAmount,
        i.CustomerName,
        i.Region,
        cl.ClearedTimestamp AS EndTime
    FROM invoice_base i
    INNER JOIN [Your accounts receivable clearing source] cl
      ON cl.InvoiceNumber = i.InvoiceNumber

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Invoice Cancelled' AS ActivityName,
        COALESCE(x.CancellationTimestamp, TO_TIMESTAMP(TO_VARCHAR(i.BillingCreatedDate) || ' ' || TO_VARCHAR(i.BillingCreatedTime))) AS EventTime,
        x.UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        i.InvoiceAmount,
        i.CustomerName,
        i.Region,
        COALESCE(x.CancellationTimestamp, TO_TIMESTAMP(TO_VARCHAR(i.BillingCreatedDate) || ' ' || TO_VARCHAR(i.BillingCreatedTime))) AS EndTime
    FROM invoice_base i
    LEFT JOIN [Your billing cancellation or document flow source] x
      ON x.InvoiceNumber = i.InvoiceNumber
    WHERE i.CancellationIndicator = '[Cancellation indicator value]'
       OR x.InvoiceNumber IS NOT NULL

    UNION ALL

    SELECT
        cm.ReferenceInvoiceNumber AS InvoiceNumber,
        'Credit Memo Created' AS ActivityName,
        cm.CreatedTimestamp AS EventTime,
        cm.UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        cm.Amount AS InvoiceAmount,
        i.CustomerName,
        i.Region,
        cm.CreatedTimestamp AS EndTime
    FROM [Your credit memo source] cm
    INNER JOIN invoice_base i
      ON i.InvoiceNumber = cm.ReferenceInvoiceNumber

    UNION ALL

    SELECT
        i.InvoiceNumber,
        'Billing Rework Identified' AS ActivityName,
        r.ReworkTimestamp AS EventTime,
        r.UserName,
        i.InvoiceDate,
        i.PaymentDueDate,
        i.InvoiceAmount,
        i.CustomerName,
        i.Region,
        r.ReworkTimestamp AS EndTime
    FROM invoice_base i
    INNER JOIN (
        SELECT
            old_invoice.InvoiceNumber,
            new_invoice.InvoiceNumber AS ReplacementInvoiceNumber,
            new_invoice.BillingCreatedDate AS ReworkTimestamp,
            new_invoice.BillingCreatedBy AS UserName
        FROM invoice_base old_invoice
        INNER JOIN invoice_base new_invoice
          ON new_invoice.PrecedingDocument = old_invoice.InvoiceNumber
         AND new_invoice.BillingCreatedDate > old_invoice.BillingCreatedDate
        WHERE old_invoice.CancellationIndicator = '[Cancellation indicator value]'
    ) r
      ON r.InvoiceNumber = i.InvoiceNumber
)
SELECT
    InvoiceNumber,
    ActivityName,
    EventTime,
    UserName,
    InvoiceDate,
    PaymentDueDate,
    InvoiceAmount,
    CustomerName,
    Region,
    EndTime
FROM events
WHERE EventTime IS NOT NULL
ORDER BY InvoiceNumber, EventTime, ActivityName;