您的订单到收款 - 开票与发票处理数据模板

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 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;