Ticari Borçlar Fatura İşleme Veri Template'inuz

-- Common Table Expression (CTE) to select the base set of AP Invoices
WITH Invoices_Base AS (
  SELECT
    I_JournalEntry.CompanyCode,
    I_JournalEntry.AccountingDocument,
    I_JournalEntry.FiscalYear,
    CONCAT(I_JournalEntry.CompanyCode, CONCAT(I_JournalEntry.AccountingDocument, I_JournalEntry.FiscalYear)) AS InvoiceId,
    I_JournalEntry.CreationDateTime,
    I_JournalEntry.CreatedByUser,
    I_JournalEntry.DocumentStatus,
    I_JournalEntry.JournalEntryType,
    I_JournalEntry.ReversalReferenceJournalEntry,
    I_JournalEntry.IsReversed,
    I_JournalEntry.ReversalDate,
    IJE_ITEM.NetDueDate,
    IJE_ITEM.Supplier,
    SUP.SupplierName AS VendorName,
    IJE_ITEM.AmountInCompanyCodeCurrency AS InvoiceAmount,
    MM.PurchaseOrder AS PurchaseOrderNumber,
    MM.PaymentBlockingReason
  FROM I_JournalEntry
  -- Join to get item details like due date and supplier
  LEFT JOIN I_JournalEntryItem AS IJE_ITEM
    ON I_JournalEntry.CompanyCode = IJE_ITEM.CompanyCode
    AND I_JournalEntry.AccountingDocument = IJE_ITEM.AccountingDocument
    AND I_JournalEntry.FiscalYear = IJE_ITEM.FiscalYear
    AND IJE_ITEM.IsSupplier = 'X'
  -- Join to get vendor name from master data
  LEFT JOIN I_Supplier AS SUP 
    ON IJE_ITEM.Supplier = SUP.Supplier
  -- Join to get MM Invoice specific data like PO Number and Payment Block
  LEFT JOIN I_SupplierInvoiceAPI01 AS MM 
    ON I_JournalEntry.AccountingDocument = MM.AccountingDocument
    AND I_JournalEntry.CompanyCode = MM.CompanyCode
    AND I_JournalEntry.FiscalYear = MM.FiscalYear
  WHERE
    I_JournalEntry.JournalEntryType IN ('KR', 'RE') -- Standard Vendor Invoice Types
    AND I_JournalEntry.CompanyCode IN ('[Your Company Code 1]', '[Your Company Code 2]')
    AND I_JournalEntry.CreationDateTime BETWEEN '[YYYY-MM-DD]T00:00:00Z' AND '[YYYY-MM-DD]T23:59:59Z'
)

-- Event: 1. Invoice Received
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Received' AS "ActivityName",
  B.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B

UNION ALL

-- Event: 2. Invoice Parked
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Parked' AS "ActivityName",
  B.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
WHERE B.DocumentStatus = 'V' -- 'V' stands for Parked

UNION ALL

-- Event: 3. Purchase Order Matched
SELECT
  B.InvoiceId AS "Invoice",
  'Purchase Order Matched' AS "ActivityName",
  B.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
WHERE B.PurchaseOrderNumber IS NOT NULL AND B.PurchaseOrderNumber <> ''

UNION ALL

-- Event: 4. Goods Receipt Matched
SELECT
  B.InvoiceId AS "Invoice",
  'Goods Receipt Matched' AS "ActivityName",
  B.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_SupplierInvoiceItemAPI01 AS MM_ITEM
  ON B.AccountingDocument = MM_ITEM.AccountingDocument
  AND B.FiscalYear = MM_ITEM.FiscalYear
WHERE MM_ITEM.GoodsReceipt IS NOT NULL AND MM_ITEM.GoodsReceipt <> ''

UNION ALL

-- Event: 5. Invoice Blocked For Payment
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Blocked For Payment' AS "ActivityName",
  B.CreationDateTime AS "EventTime", -- Approximates block time as creation time if blocked on entry
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
WHERE B.PaymentBlockingReason IS NOT NULL AND B.PaymentBlockingReason <> ''

UNION ALL

-- Event: 6. Discrepancy Resolved (Payment Block Removed)
SELECT
  B.InvoiceId AS "Invoice",
  'Discrepancy Resolved' AS "ActivityName",
  CD.ChangeTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  CD.UserName AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_ChangeDocument AS CD
  ON CONCAT(B.CompanyCode, B.AccountingDocument, B.FiscalYear) = CD.ObjectValue
WHERE CD.ChangeDocumentObject = 'INVOICE' 
  AND CD.TableName = 'RBKP' 
  AND CD.FieldName = 'ZLSPR' -- Field for Payment Block
  AND CD.NewFieldValue = '' -- Block was removed

UNION ALL

-- Event: 7, 8, 9. Workflow Events (Routed, Approved, Rejected)
SELECT
  B.InvoiceId AS "Invoice",
  CASE WF.WorkflowStatus
    WHEN 'READY' THEN 'Invoice Routed For Approval'
    WHEN 'APPROVED' THEN 'Invoice Approved'
    WHEN 'REJECTED' THEN 'Invoice Rejected'
  END AS "ActivityName",
  WF.WorkflowStatusChangedDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  WF.WorkflowStatusChangedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_WorkflowStatusDetails AS WF
  ON B.InvoiceId = WF.WorkflowScenarioInstance
WHERE WF.WorkflowStatus IN ('READY', 'APPROVED', 'REJECTED')

UNION ALL

-- Event: 10. Invoice Posted
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Posted' AS "ActivityName",
  JE.PostingDate AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_JournalEntry AS JE
  ON B.AccountingDocument = JE.AccountingDocument
  AND B.CompanyCode = JE.CompanyCode
  AND B.FiscalYear = JE.FiscalYear
WHERE B.DocumentStatus <> 'V' -- Any status other than Parked is considered Posted for AP

UNION ALL

-- Event: 11. Payment Proposal Created
SELECT
  B.InvoiceId AS "Invoice",
  'Payment Proposal Created' AS "ActivityName",
  PPI.PaymentProposalRunDate AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  PPI.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_PaymentProposalItem AS PPI
  ON B.CompanyCode = PPI.CompanyCode
  AND B.AccountingDocument = PPI.AccountingDocument
  AND B.FiscalYear = PPI.FiscalYear

UNION ALL

-- Event: 12. Payment Executed
-- This links the invoice to its clearing document, which is the payment document
SELECT DISTINCT
  B.InvoiceId AS "Invoice",
  'Payment Executed' AS "ActivityName",
  CLEAR_JE.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  CLEAR_JE.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_JournalEntryItem AS IJE_ITEM
  ON B.CompanyCode = IJE_ITEM.CompanyCode
  AND B.AccountingDocument = IJE_ITEM.AccountingDocument
  AND B.FiscalYear = IJE_ITEM.FiscalYear
INNER JOIN I_JournalEntry AS CLEAR_JE
  ON IJE_ITEM.ClearingJournalEntry = CLEAR_JE.AccountingDocument
  AND IJE_ITEM.CompanyCode = CLEAR_JE.CompanyCode
WHERE IJE_ITEM.ClearingJournalEntry IS NOT NULL AND IJE_ITEM.ClearingJournalEntry <> ''
  AND CLEAR_JE.JournalEntryType = 'KZ' -- Vendor Payment Document Type

UNION ALL

-- Event: 13. Invoice Due Date Passed
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Due Date Passed' AS "ActivityName",
  ADD_DAYS(B.NetDueDate, 1) AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  'SYSTEM' AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
LEFT JOIN I_JournalEntryItem AS IJE_ITEM
  ON B.CompanyCode = IJE_ITEM.CompanyCode
  AND B.AccountingDocument = IJE_ITEM.AccountingDocument
  AND B.FiscalYear = IJE_ITEM.FiscalYear
WHERE B.NetDueDate < CURRENT_DATE
  AND IJE_ITEM.ClearingDate IS NULL -- Invoice is not yet cleared

UNION ALL

-- Event: 14. Payment Cleared
SELECT DISTINCT
  B.InvoiceId AS "Invoice",
  'Payment Cleared' AS "ActivityName",
  IJE_ITEM.ClearingDate AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  IJE_ITEM.ChangedByUser AS "UserName", -- User who cleared it
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_JournalEntryItem AS IJE_ITEM
  ON B.CompanyCode = IJE_ITEM.CompanyCode
  AND B.AccountingDocument = IJE_ITEM.AccountingDocument
  AND B.FiscalYear = IJE_ITEM.FiscalYear
WHERE IJE_ITEM.ClearingDate IS NOT NULL

UNION ALL

-- Event: 15. Invoice Cancelled
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Cancelled' AS "ActivityName",
  B.ReversalDate AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName", -- User who created the original document
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
WHERE B.IsReversed = 'X';

-- Common Table Expression (CTE) to select the base set of AP Invoices
WITH Invoices_Base AS (
  SELECT
    I_JournalEntry.CompanyCode,
    I_JournalEntry.AccountingDocument,
    I_JournalEntry.FiscalYear,
    CONCAT(I_JournalEntry.CompanyCode, CONCAT(I_JournalEntry.AccountingDocument, I_JournalEntry.FiscalYear)) AS InvoiceId,
    I_JournalEntry.CreationDateTime,
    I_JournalEntry.CreatedByUser,
    I_JournalEntry.DocumentStatus,
    I_JournalEntry.JournalEntryType,
    I_JournalEntry.ReversalReferenceJournalEntry,
    I_JournalEntry.IsReversed,
    I_JournalEntry.ReversalDate,
    IJE_ITEM.NetDueDate,
    IJE_ITEM.Supplier,
    SUP.SupplierName AS VendorName,
    IJE_ITEM.AmountInCompanyCodeCurrency AS InvoiceAmount,
    MM.PurchaseOrder AS PurchaseOrderNumber,
    MM.PaymentBlockingReason
  FROM I_JournalEntry
  -- Join to get item details like due date and supplier
  LEFT JOIN I_JournalEntryItem AS IJE_ITEM
    ON I_JournalEntry.CompanyCode = IJE_ITEM.CompanyCode
    AND I_JournalEntry.AccountingDocument = IJE_ITEM.AccountingDocument
    AND I_JournalEntry.FiscalYear = IJE_ITEM.FiscalYear
    AND IJE_ITEM.IsSupplier = 'X'
  -- Join to get vendor name from master data
  LEFT JOIN I_Supplier AS SUP 
    ON IJE_ITEM.Supplier = SUP.Supplier
  -- Join to get MM Invoice specific data like PO Number and Payment Block
  LEFT JOIN I_SupplierInvoiceAPI01 AS MM 
    ON I_JournalEntry.AccountingDocument = MM.AccountingDocument
    AND I_JournalEntry.CompanyCode = MM.CompanyCode
    AND I_JournalEntry.FiscalYear = MM.FiscalYear
  WHERE
    I_JournalEntry.JournalEntryType IN ('KR', 'RE') -- Standard Vendor Invoice Types
    AND I_JournalEntry.CompanyCode IN ('[Your Company Code 1]', '[Your Company Code 2]')
    AND I_JournalEntry.CreationDateTime BETWEEN '[YYYY-MM-DD]T00:00:00Z' AND '[YYYY-MM-DD]T23:59:59Z'
)

-- Event: 1. Invoice Received
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Received' AS "ActivityName",
  B.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B

UNION ALL

-- Event: 2. Invoice Parked
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Parked' AS "ActivityName",
  B.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
WHERE B.DocumentStatus = 'V' -- 'V' stands for Parked

UNION ALL

-- Event: 3. Purchase Order Matched
SELECT
  B.InvoiceId AS "Invoice",
  'Purchase Order Matched' AS "ActivityName",
  B.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
WHERE B.PurchaseOrderNumber IS NOT NULL AND B.PurchaseOrderNumber <> ''

UNION ALL

-- Event: 4. Goods Receipt Matched
SELECT
  B.InvoiceId AS "Invoice",
  'Goods Receipt Matched' AS "ActivityName",
  B.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_SupplierInvoiceItemAPI01 AS MM_ITEM
  ON B.AccountingDocument = MM_ITEM.AccountingDocument
  AND B.FiscalYear = MM_ITEM.FiscalYear
WHERE MM_ITEM.GoodsReceipt IS NOT NULL AND MM_ITEM.GoodsReceipt <> ''

UNION ALL

-- Event: 5. Invoice Blocked For Payment
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Blocked For Payment' AS "ActivityName",
  B.CreationDateTime AS "EventTime", -- Approximates block time as creation time if blocked on entry
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
WHERE B.PaymentBlockingReason IS NOT NULL AND B.PaymentBlockingReason <> ''

UNION ALL

-- Event: 6. Discrepancy Resolved (Payment Block Removed)
SELECT
  B.InvoiceId AS "Invoice",
  'Discrepancy Resolved' AS "ActivityName",
  CD.ChangeTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  CD.UserName AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_ChangeDocument AS CD
  ON CONCAT(B.CompanyCode, B.AccountingDocument, B.FiscalYear) = CD.ObjectValue
WHERE CD.ChangeDocumentObject = 'INVOICE' 
  AND CD.TableName = 'RBKP' 
  AND CD.FieldName = 'ZLSPR' -- Field for Payment Block
  AND CD.NewFieldValue = '' -- Block was removed

UNION ALL

-- Event: 7, 8, 9. Workflow Events (Routed, Approved, Rejected)
SELECT
  B.InvoiceId AS "Invoice",
  CASE WF.WorkflowStatus
    WHEN 'READY' THEN 'Invoice Routed For Approval'
    WHEN 'APPROVED' THEN 'Invoice Approved'
    WHEN 'REJECTED' THEN 'Invoice Rejected'
  END AS "ActivityName",
  WF.WorkflowStatusChangedDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  WF.WorkflowStatusChangedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_WorkflowStatusDetails AS WF
  ON B.InvoiceId = WF.WorkflowScenarioInstance
WHERE WF.WorkflowStatus IN ('READY', 'APPROVED', 'REJECTED')

UNION ALL

-- Event: 10. Invoice Posted
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Posted' AS "ActivityName",
  JE.PostingDate AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_JournalEntry AS JE
  ON B.AccountingDocument = JE.AccountingDocument
  AND B.CompanyCode = JE.CompanyCode
  AND B.FiscalYear = JE.FiscalYear
WHERE B.DocumentStatus <> 'V' -- Any status other than Parked is considered Posted for AP

UNION ALL

-- Event: 11. Payment Proposal Created
SELECT
  B.InvoiceId AS "Invoice",
  'Payment Proposal Created' AS "ActivityName",
  PPI.PaymentProposalRunDate AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  PPI.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_PaymentProposalItem AS PPI
  ON B.CompanyCode = PPI.CompanyCode
  AND B.AccountingDocument = PPI.AccountingDocument
  AND B.FiscalYear = PPI.FiscalYear

UNION ALL

-- Event: 12. Payment Executed
-- This links the invoice to its clearing document, which is the payment document
SELECT DISTINCT
  B.InvoiceId AS "Invoice",
  'Payment Executed' AS "ActivityName",
  CLEAR_JE.CreationDateTime AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  CLEAR_JE.CreatedByUser AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_JournalEntryItem AS IJE_ITEM
  ON B.CompanyCode = IJE_ITEM.CompanyCode
  AND B.AccountingDocument = IJE_ITEM.AccountingDocument
  AND B.FiscalYear = IJE_ITEM.FiscalYear
INNER JOIN I_JournalEntry AS CLEAR_JE
  ON IJE_ITEM.ClearingJournalEntry = CLEAR_JE.AccountingDocument
  AND IJE_ITEM.CompanyCode = CLEAR_JE.CompanyCode
WHERE IJE_ITEM.ClearingJournalEntry IS NOT NULL AND IJE_ITEM.ClearingJournalEntry <> ''
  AND CLEAR_JE.JournalEntryType = 'KZ' -- Vendor Payment Document Type

UNION ALL

-- Event: 13. Invoice Due Date Passed
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Due Date Passed' AS "ActivityName",
  ADD_DAYS(B.NetDueDate, 1) AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  'SYSTEM' AS "UserName",
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
LEFT JOIN I_JournalEntryItem AS IJE_ITEM
  ON B.CompanyCode = IJE_ITEM.CompanyCode
  AND B.AccountingDocument = IJE_ITEM.AccountingDocument
  AND B.FiscalYear = IJE_ITEM.FiscalYear
WHERE B.NetDueDate < CURRENT_DATE
  AND IJE_ITEM.ClearingDate IS NULL -- Invoice is not yet cleared

UNION ALL

-- Event: 14. Payment Cleared
SELECT DISTINCT
  B.InvoiceId AS "Invoice",
  'Payment Cleared' AS "ActivityName",
  IJE_ITEM.ClearingDate AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  IJE_ITEM.ChangedByUser AS "UserName", -- User who cleared it
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
INNER JOIN I_JournalEntryItem AS IJE_ITEM
  ON B.CompanyCode = IJE_ITEM.CompanyCode
  AND B.AccountingDocument = IJE_ITEM.AccountingDocument
  AND B.FiscalYear = IJE_ITEM.FiscalYear
WHERE IJE_ITEM.ClearingDate IS NOT NULL

UNION ALL

-- Event: 15. Invoice Cancelled
SELECT
  B.InvoiceId AS "Invoice",
  'Invoice Cancelled' AS "ActivityName",
  B.ReversalDate AS "EventTime",
  'SAP_S4HANA' AS "SourceSystem",
  CURRENT_UTCTIMESTAMP AS "LastDataUpdate",
  B.CreatedByUser AS "UserName", -- User who created the original document
  B.CompanyCode AS "CompanyCode",
  B.VendorName AS "VendorName",
  B.InvoiceAmount AS "InvoiceAmount",
  B.PurchaseOrderNumber AS "PurchaseOrderNumber",
  B.NetDueDate AS "InvoiceDueDate"
FROM Invoices_Base B
WHERE B.IsReversed = 'X';

*&---------------------------------------------------------------------*
*& Report Z_PM_AP_INVOICE_EXTRACT
*&---------------------------------------------------------------------*
*& This report extracts Accounts Payable invoice lifecycle events for
*& process mining analysis.
*&---------------------------------------------------------------------*
REPORT z_pm_ap_invoice_extract.

*&---------------------------------------------------------------------*
*& Data Structures
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_event_log,
         invoice             TYPE belnr_d,
         activityname        TYPE string,
         eventtime           TYPE string,
         sourcesystem        TYPE logsys,
         lastdataupdate      TYPE string,
         username            TYPE uname,
         companycode         TYPE bukrs,
         vendorname          TYPE name1_gp,
         invoiceamount       TYPE wrbtr,
         purchaseordernumber TYPE ebeln,
         invoiceduedate      TYPE d,
       END OF ty_event_log.

DATA: gt_event_log TYPE TABLE OF ty_event_log.
DATA: gv_system_id TYPE logsys.
DATA: gv_last_update TYPE string.

*&---------------------------------------------------------------------*
*& Selection Screen
*&---------------------------------------------------------------------*
SELECT-OPTIONS: s_bukrs FOR bkpf-bukrs OBLIGATORY,
                s_cpudt FOR bkpf-cpudt OBLIGATORY DEFAULT sy-datum,
                s_blart FOR bkpf-blart.
PARAMETERS: p_fpath TYPE string OBLIGATORY DEFAULT '/tmp/ap_extract.csv'.

*&---------------------------------------------------------------------*
*& Main Processing Block
*&---------------------------------------------------------------------*
START-OF-SELECTION.

  " Get System ID and Update Timestamp
  CALL FUNCTION 'OWN_LOGICAL_SYSTEM_GET'
    IMPORTING
      own_logical_system             = gv_system_id
    EXCEPTIONS
      own_logical_system_not_defined = 1
      OTHERS                         = 2.
  CONCATENATE sy-datum sy-uzeit INTO gv_last_update.

  " Internal tables for SAP data
  DATA: lt_bkpf TYPE TABLE OF bkpf,
        lt_rbkp TYPE TABLE OF rbkp.

  " Select base documents
  SELECT * FROM bkpf INTO TABLE lt_bkpf
    WHERE bukrs IN s_bukrs
      AND cpudt IN s_cpudt
      AND blart IN s_blart
      AND ( blart = 'KR' OR blart = 'KG' ). " Example FI Invoice Types

  SELECT * FROM rbkp INTO TABLE lt_rbkp
    WHERE bukrs IN s_bukrs
      AND cpudt IN s_cpudt
      AND blart IN s_blart
      AND blart = 'RE'. " Example MM Invoice Type

  " --- Process each invoice document ---
  LOOP AT lt_bkpf ASSIGNING FIELD-SYMBOL(<fs_bkpf>).
    PERFORM process_invoice USING <fs_bkpf>.
  ENDLOOP.

  LOOP AT lt_rbkp ASSIGNING FIELD-SYMBOL(<fs_rbkp>).
    PERFORM process_mm_invoice USING <fs_rbkp>.
  ENDLOOP.

  " Write output to file
  PERFORM write_output_file.

*&---------------------------------------------------------------------*
*&      Form  PROCESS_INVOICE (Handles FI Invoices)
*&---------------------------------------------------------------------*
FORM process_invoice USING iv_bkpf TYPE bkpf.
  DATA: ls_bseg TYPE bseg,
        ls_lfa1 TYPE lfa1,
        ld_due_date TYPE d.
  DATA: ls_event TYPE ty_event_log.

  " Get Vendor and other details from first line item
  SELECT SINGLE * FROM bseg INTO ls_bseg
    WHERE bukrs = iv_bkpf-bukrs
      AND belnr = iv_bkpf-belnr
      AND gjahr = iv_bkpf-gjahr
      AND koart = 'K'.
  IF sy-subrc = 0.
    SELECT SINGLE name1 FROM lfa1 INTO ls_lfa1-name1 WHERE lifnr = ls_bseg-lifnr.
    CALL FUNCTION 'DETERMINE_DUE_DATE'
      EXPORTING
        i_zfbdt = ls_bseg-zfbdt
        i_zbd1t = ls_bseg-zbd1t
        i_zbd2t = ls_bseg-zbd2t
        i_zbd3t = ls_bseg-zbd3t
        i_zbd1p = ls_bseg-zbd1p
        i_zbd2p = ls_bseg-zbd2p
        i_zterm = ls_bseg-zterm
      IMPORTING
        e_faedt = ld_due_date.
  ENDIF.

  " Helper function to populate common fields
  MACRO set_common_fields.
    ls_event-invoice = iv_bkpf-belnr.
    ls_event-sourcesystem = gv_system_id.
    ls_event-lastdataupdate = gv_last_update.
    ls_event-companycode = iv_bkpf-bukrs.
    ls_event-vendorname = ls_lfa1-name1.
    ls_event-invoiceduedate = ld_due_date.
    SELECT SINGLE wrbtr FROM bseg INTO ls_event-invoiceamount WHERE belnr = iv_bkpf-belnr AND gjahr = iv_bkpf-gjahr AND koart = 'K'.
  ENDMACRO.

  " 1. Invoice Received
  CLEAR ls_event.
  set_common_fields.
  ls_event-activityname = 'Invoice Received'.
  CONCATENATE iv_bkpf-cpudt iv_bkpf-cputm INTO ls_event-eventtime.
  ls_event-username = iv_bkpf-usnam.
  APPEND ls_event TO gt_event_log.

  " 2. Invoice Parked (if document was created as parked)
  IF iv_bkpf-bstat = 'V'.
    CLEAR ls_event.
    set_common_fields.
    ls_event-activityname = 'Invoice Parked'.
    CONCATENATE iv_bkpf-cpudt iv_bkpf-cputm INTO ls_event-eventtime.
    ls_event-username = iv_bkpf-usnam.
    APPEND ls_event TO gt_event_log.
  ENDIF.

  " 10. Invoice Posted (For non-parked, same as received. For parked, this needs CDHDR/CDPOS logic not shown for brevity)
  IF iv_bkpf-bstat <> 'V'.
    CLEAR ls_event.
    set_common_fields.
    ls_event-activityname = 'Invoice Posted'.
    CONCATENATE iv_bkpf-budat iv_bkpf-cputm INTO ls_event-eventtime. " Using posting date
    ls_event-username = iv_bkpf-usnam.
    APPEND ls_event TO gt_event_log.
  ENDIF.

  " 5. & 7. Invoice Blocked / Discrepancy Resolved from Change Docs
  DATA: lt_cdhdr TYPE TABLE OF cdhdr, lt_cdpos TYPE TABLE OF cdpos.
  DATA(ld_objectkey) = |{ iv_bkpf-bukrs }{ iv_bkpf-belnr }{ iv_bkpf-gjahr }|.
  SELECT * FROM cdhdr INTO TABLE lt_cdhdr WHERE objectclas = 'BELEG' AND objectid = ld_objectkey.
  IF sy-subrc = 0.
    SELECT * FROM cdpos INTO TABLE lt_cdpos FOR ALL ENTRIES IN lt_cdhdr
      WHERE changenr = lt_cdhdr-changenr AND tabname = 'BSEG' AND fname = 'ZLSPR'.
    LOOP AT lt_cdpos ASSIGNING FIELD-SYMBOL(<fs_cdpos>).
      READ TABLE lt_cdhdr ASSIGNING FIELD-SYMBOL(<fs_cdhdr>) WITH KEY changenr = <fs_cdpos>-changenr.
      IF sy-subrc = 0.
        CLEAR ls_event.
        set_common_fields.
        IF <fs_cdpos>-value_new IS NOT INITIAL AND <fs_cdpos>-value_old IS INITIAL.
          ls_event-activityname = 'Invoice Blocked For Payment'.
        ELSEIF <fs_cdpos>-value_new IS INITIAL AND <fs_cdpos>-value_old IS NOT INITIAL.
          ls_event-activityname = 'Discrepancy Resolved'.
        ELSE.
          CONTINUE.
        ENDIF.
        CONCATENATE <fs_cdhdr>-udate <fs_cdhdr>-utime INTO ls_event-eventtime.
        ls_event-username = <fs_cdhdr>-username.
        APPEND ls_event TO gt_event_log.
      ENDIF.
    ENDLOOP.
  ENDIF.

  " 6. 8. 9. Workflow Events (Routed, Approved, Rejected) - Simplified Example
  " This requires knowledge of specific workflow templates. Placeholder logic:
  " SELECT ... FROM SWW_WI2OBJ ... WHERE INSTID = [Invoice Object]
  " SELECT ... FROM SWWWIHEAD ... to get status and times

  " 11. & 12. & 14. Payment Proposal, Executed, Cleared
  IF ls_bseg-augbl IS NOT INITIAL.
      DATA: ls_regup TYPE regup.
      SELECT SINGLE * FROM regup INTO ls_regup WHERE vblnr = ls_bseg-belnr.
      IF sy-subrc = 0.
          DATA(ld_rundate) = ls_regup-laufd.
          CLEAR ls_event.
          set_common_fields.
          ls_event-activityname = 'Payment Proposal Created'.
          CONCATENATE ld_rundate '000000' INTO ls_event-eventtime.
          APPEND ls_event TO gt_event_log.
      ENDIF.

      CLEAR ls_event.
      set_common_fields.
      ls_event-activityname = 'Payment Executed'.
      CONCATENATE ls_bseg-augdt '120000' INTO ls_event-eventtime. " Using clearing date as proxy
      APPEND ls_event TO gt_event_log.

      CLEAR ls_event.
      set_common_fields.
      ls_event-activityname = 'Payment Cleared'.
      CONCATENATE ls_bseg-augdt '120001' INTO ls_event-eventtime.
      APPEND ls_event TO gt_event_log.
  ENDIF.

  " 13. Invoice Due Date Passed (Calculated)
  IF ls_bseg-augdt IS NOT INITIAL AND ld_due_date IS NOT INITIAL.
    IF ls_bseg-augdt > ld_due_date.
      CLEAR ls_event.
      set_common_fields.
      ls_event-activityname = 'Invoice Due Date Passed'.
      CONCATENATE ld_due_date '235959' INTO ls_event-eventtime.
      APPEND ls_event TO gt_event_log.
    ENDIF.
  ENDIF.

  " 15. Invoice Cancelled
  IF iv_bkpf-stblg IS NOT INITIAL.
    DATA: ls_rev_bkpf TYPE bkpf.
    SELECT SINGLE * FROM bkpf INTO ls_rev_bkpf WHERE belnr = iv_bkpf-stblg.
    IF sy-subrc = 0.
      CLEAR ls_event.
      set_common_fields.
      ls_event-activityname = 'Invoice Cancelled'.
      CONCATENATE ls_rev_bkpf-cpudt ls_rev_bkpf-cputm INTO ls_event-eventtime.
      ls_event-username = ls_rev_bkpf-usnam.
      APPEND ls_event TO gt_event_log.
    ENDIF.
  ENDIF.

ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  PROCESS_MM_INVOICE (Handles MM/Logistics Invoices)
*&---------------------------------------------------------------------*
FORM process_mm_invoice USING iv_rbkp TYPE rbkp.
  " This form would be similar to PROCESS_INVOICE, but starts with RBKP.
  " It needs to find the corresponding FI document in BKPF via AWKEY.
  " The logic for PO/GR Matched would be included here.

  " For demonstration, creating placeholder events for MM-specific activities.
  DATA: ls_event TYPE ty_event_log.
  ls_event-invoice = iv_rbkp-belnr.
  ls_event-sourcesystem = gv_system_id.
  ls_event-lastdataupdate = gv_last_update.
  ls_event-companycode = iv_rbkp-bukrs.

  " 1. Invoice Received (MM)
  ls_event-activityname = 'Invoice Received'.
  CONCATENATE iv_rbkp-cpudt iv_rbkp-cputm INTO ls_event-eventtime.
  ls_event-username = iv_rbkp-usnam.
  APPEND ls_event TO gt_event_log.

  " 3. Purchase Order Matched (Implicit)
  ls_event-activityname = 'Purchase Order Matched'.
  CONCATENATE iv_rbkp-cpudt iv_rbkp-cputm INTO ls_event-eventtime.
  ls_event-username = iv_rbkp-usnam.
  APPEND ls_event TO gt_event_log.

  " 4. Goods Receipt Matched (Implicit)
  ls_event-activityname = 'Goods Receipt Matched'.
  CONCATENATE iv_rbkp-cpudt iv_rbkp-cputm INTO ls_event-eventtime.
  ls_event-username = iv_rbkp-usnam.
  APPEND ls_event TO gt_event_log.

  " NOTE: The rest of the events (Block, Pay, etc.) would be found by linking
  " RBKP to BKPF and then reusing the logic from PROCESS_INVOICE.
  " Link: BKPF-AWKEY = CONCATENATE( RBKP-BELNR, RBKP-GJAHR ).
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  WRITE_OUTPUT_FILE
*&---------------------------------------------------------------------*
FORM write_output_file.
  DATA: lv_string TYPE string.

  OPEN DATASET p_fpath FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
  IF sy-subrc <> 0.
    MESSAGE 'Error opening file.' TYPE 'E'.
    RETURN.
  ENDIF.

  " Write Header
  lv_string = 'Invoice,ActivityName,EventTime,SourceSystem,LastDataUpdate,UserName,CompanyCode,VendorName,InvoiceAmount,PurchaseOrderNumber,InvoiceDueDate'.
  TRANSFER lv_string TO p_fpath.

  " Write Data
  LOOP AT gt_event_log ASSIGNING FIELD-SYMBOL(<fs_event>).
    " Create a comma-separated string, handling potential commas in data
    CONCATENATE <fs_event>-invoice
                <fs_event>-activityname
                <fs_event>-eventtime
                <fs_event>-sourcesystem
                <fs_event>-lastdataupdate
                <fs_event>-username
                <fs_event>-companycode
                <fs_event>-vendorname
                <fs_event>-invoiceamount
                <fs_event>-purchaseordernumber
                <fs_event>-invoiceduedate
    INTO lv_string SEPARATED BY ','.
    TRANSFER lv_string TO p_fpath.
  ENDLOOP.

  CLOSE DATASET p_fpath.
  WRITE: / 'Extraction complete. File written to:', p_fpath.
ENDFORM.