Mẫu dữ liệu quy trình mua hàng đến thanh toán, đơn đặt hàng

WITH PO_base AS (
  SELECT
    poh.PurchaseOrder AS PurchaseOrderNumber,
    poi.PurchaseOrderItem AS PurchaseOrderItem,
    poh.CompanyCode,
    poh.PurchaseOrderType AS DocumentType,
    poh.Supplier AS VendorId,
    poh.PurchaseOrderDate,
    poi.PurchaseRequisition AS PurchaseRequisitionNumber,
    poi.NetPriceAmount * poi.OrderQuantity AS TotalNetAmount, -- Note: This is item-level net amount
    poh.CreationDate AS POCreationDate,
    poh.CreationTime AS POCreationTime,
    poh.LastChangeDateTime AS POLastChangeDateTime,
    poi.IsDeleted,
    poi.DeliveryIsCompleted,
    poi.FinalInvoiceIsExpected,
    poi.GoodsReceiptIsExpected,
    poi.LastGoodsReceiptDate,
    poi.LastInvoiceReceiptDate
  FROM I_PurchaseOrderAPI01 poh
  JOIN I_PurchaseOrderItemAPI01 poi
    ON poh.PurchaseOrder = poi.PurchaseOrder
  WHERE
    poh.PurchaseOrderDate BETWEEN '_start_date' AND '_end_date' -- Placeholder: e.g., '20230101' and '20230630'
    AND poh.CompanyCode IN ('[YourCompanyCode]') -- Placeholder: e.g., '1010'
)
-- 1. Purchase Requisition Created
SELECT
  po.PurchaseOrderNumber,
  'Purchase Requisition Created' AS ActivityName,
  CAST(CONCAT(pr.CreationDate, 'T', pr.CreationTime) AS TIMESTAMP) AS EventTime,
  '[Your S/4HANA System ID]' AS SourceSystem, -- Placeholder
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  pr.CreatedByUser AS UserName,
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  NULL AS RequestedDeliveryDate, -- Available in PR, add if needed
  po.DocumentType
FROM I_PurchaseRequisitionItemAPI01 pr
JOIN PO_base po
  ON pr.PurchaseRequisition = po.PurchaseRequisitionNumber AND pr.PurchaseRequisitionItem = po.PurchaseOrderItem

UNION ALL

-- 2. Purchase Requisition Approved
SELECT
  po.PurchaseOrderNumber,
  'Purchase Requisition Approved' AS ActivityName,
  CAST(CONCAT(pr.PurReqnReleaseDate, 'T', '000000') AS TIMESTAMP) AS EventTime, -- Time is not available in this view
  '[Your S/4HANA System ID]' AS SourceSystem,
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  NULL AS UserName, -- Approver info requires complex joins
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  NULL AS RequestedDeliveryDate,
  po.DocumentType
FROM I_PurchaseRequisitionItemAPI01 pr
JOIN PO_base po
  ON pr.PurchaseRequisition = po.PurchaseRequisitionNumber AND pr.PurchaseRequisitionItem = po.PurchaseOrderItem
WHERE
  pr.PurReqnReleaseDate IS NOT NULL

UNION ALL

-- 3. Purchase Order Created
SELECT
  po.PurchaseOrderNumber,
  'Purchase Order Created' AS ActivityName,
  CAST(CONCAT(po.POCreationDate, 'T', po.POCreationTime) AS TIMESTAMP) AS EventTime,
  '[Your S/4HANA System ID]' AS SourceSystem,
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  poh.CreatedByUser AS UserName,
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  poi.RequestedDeliveryDate,
  po.DocumentType
FROM PO_base po
JOIN I_PurchaseOrderAPI01 poh ON po.PurchaseOrderNumber = poh.PurchaseOrder
JOIN I_PurchaseOrderItemAPI01 poi ON po.PurchaseOrderNumber = poi.PurchaseOrder AND po.PurchaseOrderItem = poi.PurchaseOrderItem

UNION ALL

-- 4. Purchase Order Approved
SELECT DISTINCT
  po.PurchaseOrderNumber,
  'Purchase Order Approved' AS ActivityName,
  CAST(poh.ReleaseDate AS TIMESTAMP) AS EventTime, -- Assuming ReleaseDate reflects final approval
  '[Your S/4HANA System ID]' AS SourceSystem,
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  NULL AS UserName, -- Approver info requires complex joins
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  poi.RequestedDeliveryDate,
  po.DocumentType
FROM PO_base po
JOIN I_PurchaseOrderAPI01 poh ON po.PurchaseOrderNumber = poh.PurchaseOrder
JOIN I_PurchaseOrderItemAPI01 poi ON po.PurchaseOrderNumber = poi.PurchaseOrder AND po.PurchaseOrderItem = poi.PurchaseOrderItem
WHERE poh.ReleaseDate IS NOT NULL

UNION ALL

-- 5. Purchase Order Sent to Vendor
SELECT DISTINCT
  po.PurchaseOrderNumber,
  'Purchase Order Sent to Vendor' AS ActivityName,
  CAST(poh.ReleaseDate AS TIMESTAMP) AS EventTime, -- Using ReleaseDate as a proxy for sending time
  '[Your S/4HANA System ID]' AS SourceSystem,
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  NULL AS UserName,
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  poi.RequestedDeliveryDate,
  po.DocumentType
FROM PO_base po
JOIN I_PurchaseOrderAPI01 poh ON po.PurchaseOrderNumber = poh.PurchaseOrder
JOIN I_PurchaseOrderItemAPI01 poi ON po.PurchaseOrderNumber = poi.PurchaseOrder AND po.PurchaseOrderItem = poi.PurchaseOrderItem
WHERE poh.ReleaseDate IS NOT NULL

UNION ALL

-- 6. Purchase Order Changed
SELECT DISTINCT
  ch.OBJECTID AS PurchaseOrderNumber,
  'Purchase Order Changed' AS ActivityName,
  CAST(CONCAT(ch.ChangeDocumentDate, 'T', ch.ChangeDocumentTime) AS TIMESTAMP) AS EventTime,
  '[Your S/4HANA System ID]' AS SourceSystem,
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  ch.UserName,
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  NULL AS RequestedDeliveryDate,
  po.DocumentType
FROM I_ChangeDocument ch
JOIN PO_base po ON ch.OBJECTID = po.PurchaseOrderNumber
WHERE
  ch.ObjectClassName = 'EINKBELEG' -- Object Class for Purchase Documents
  AND CAST(CONCAT(ch.ChangeDocumentDate, 'T', ch.ChangeDocumentTime) AS TIMESTAMP) > CAST(CONCAT(po.POCreationDate, 'T', po.POCreationTime) AS TIMESTAMP)

UNION ALL

-- 7. Goods Receipt Posted
SELECT
  po.PurchaseOrderNumber,
  'Goods Receipt Posted' AS ActivityName,
  CAST(CONCAT(md.PostingDate, 'T', md.CreationTime) AS TIMESTAMP) AS EventTime,
  '[Your S/4HANA System ID]' AS SourceSystem,
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  md.CreatedByUser AS UserName,
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  NULL AS RequestedDeliveryDate,
  po.DocumentType
FROM I_MaterialDocumentItem md
JOIN PO_base po
  ON md.PurchaseOrder = po.PurchaseOrderNumber AND md.PurchaseOrderItem = po.PurchaseOrderItem
WHERE
  md.GoodsMovementType = '101'

UNION ALL

-- 8. Services Confirmation Entered
SELECT
  po.PurchaseOrderNumber,
  'Services Confirmation Entered' AS ActivityName,
  CAST(se.PostingDate AS TIMESTAMP) AS EventTime,
  '[Your S/4HANA System ID]' AS SourceSystem,
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  se.CreatedByUser AS UserName,
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  NULL AS RequestedDeliveryDate,
  po.DocumentType
FROM I_ServiceEntrySheetAPI01 se
JOIN PO_base po
  ON se.PurchaseOrder = po.PurchaseOrderNumber AND se.PurchaseOrderItem = po.PurchaseOrderItem

UNION ALL

-- 9. Goods Returned
SELECT
  po.PurchaseOrderNumber,
  'Goods Returned' AS ActivityName,
  CAST(CONCAT(md.PostingDate, 'T', md.CreationTime) AS TIMESTAMP) AS EventTime,
  '[Your S/4HANA System ID]' AS SourceSystem,
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  md.CreatedByUser AS UserName,
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  NULL AS RequestedDeliveryDate,
  po.DocumentType
FROM I_MaterialDocumentItem md
JOIN PO_base po
  ON md.PurchaseOrder = po.PurchaseOrderNumber AND md.PurchaseOrderItem = po.PurchaseOrderItem
WHERE
  md.GoodsMovementType = '122'

UNION ALL

-- 10. Invoice Received
SELECT
  po.PurchaseOrderNumber,
  'Invoice Received' AS ActivityName,
  CAST(inv.PostingDate AS TIMESTAMP) AS EventTime,
  '[Your S/4HANA System ID]' AS SourceSystem,
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  inv.CreatedByUser AS UserName,
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  NULL AS RequestedDeliveryDate,
  po.DocumentType
FROM I_SupplierInvoiceAPI01 inv
JOIN PO_base po
  ON inv.PurchaseOrderReference = po.PurchaseOrderNumber
WHERE
  inv.DebitCreditCode = 'H' -- 'H' for Credit (Supplier Invoice)

UNION ALL

-- 11. Invoice Paid
SELECT
  po.PurchaseOrderNumber,
  'Invoice Paid' AS ActivityName,
  CAST(doc.ClearingDate AS TIMESTAMP) AS EventTime,
  '[Your S/4HANA System ID]' AS SourceSystem,
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  doc.CreatedByUser AS UserName,
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  NULL AS RequestedDeliveryDate,
  po.DocumentType
FROM I_SupplierInvoiceAPI01 inv
JOIN I_OperationalAcctgDocItem doc
  ON inv.AccountingDocument = doc.AccountingDocument
JOIN PO_base po
  ON inv.PurchaseOrderReference = po.PurchaseOrderNumber
WHERE
  doc.IsCleared = 'X' AND doc.ClearingDate IS NOT NULL

UNION ALL

-- 12. Purchase Order Completed
SELECT
  po.PurchaseOrderNumber,
  'Purchase Order Completed' AS ActivityName,
  CAST(GREATEST(po.LastGoodsReceiptDate, po.LastInvoiceReceiptDate) AS TIMESTAMP) AS EventTime,
  '[Your S/4HANA System ID]' AS SourceSystem,
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  'SYSTEM' AS UserName,
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  NULL AS RequestedDeliveryDate,
  po.DocumentType
FROM PO_base po
WHERE
  po.DeliveryIsCompleted = 'X'
  AND (po.FinalInvoiceIsExpected = 'X' OR po.GoodsReceiptIsExpected = '') -- Logic for completion
  AND GREATEST(po.LastGoodsReceiptDate, po.LastInvoiceReceiptDate) IS NOT NULL

UNION ALL

-- 13. Purchase Order Deleted
SELECT
  po.PurchaseOrderNumber,
  'Purchase Order Deleted' AS ActivityName,
  CAST(po.POLastChangeDateTime AS TIMESTAMP) AS EventTime,
  '[Your S/4HANA System ID]' AS SourceSystem,
  CURRENT_UTCTIMESTAMP AS LastDataUpdate,
  po.VendorId,
  NULL AS UserName, -- User who set the flag is in change docs
  po.TotalNetAmount,
  po.PurchaseRequisitionNumber,
  NULL AS RequestedDeliveryDate,
  po.DocumentType
FROM PO_base po
WHERE
  po.IsDeleted = 'X'

WITH
params AS (
    SELECT
        CAST('[Start timestamp]' AS TIMESTAMP) AS start_ts,
        CAST('[End timestamp]' AS TIMESTAMP) AS end_ts,
        CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS last_data_update,
        CAST('[Source system]' AS NVARCHAR(100)) AS source_system
    FROM DUMMY
),
po_base AS (
    SELECT
        h.MANDT,
        h.EBELN AS PurchaseOrderNumber,
        h.LIFNR AS VendorId,
        h.BSART AS DocumentType,
        h.BUKRS AS CompanyCode,
        CAST(h.AEDAT AS DATE) AS POChangedDate,
        CAST(h.AEDAT AS TIMESTAMP) AS POChangedTimestamp,
        CAST(h.ERNAM AS NVARCHAR(100)) AS POCreatedBy,
        CAST(h.BEDAT AS DATE) AS PODate,
        CAST(h.EBELN AS NVARCHAR(20)) AS PurchaseOrderKey,
        CAST(SUM(COALESCE(i.NETWR, 0)) AS DECIMAL(23, 2)) AS TotalNetAmount,
        CAST(MIN(i.BEDNR) AS NVARCHAR(20)) AS PurchaseRequisitionNumber,
        CAST(MIN(i.EINDT) AS DATE) AS RequestedDeliveryDate
    FROM EKKO h
    INNER JOIN EKPO i
        ON i.MANDT = h.MANDT
       AND i.EBELN = h.EBELN
    WHERE h.AEDAT >= (SELECT start_ts FROM params)
      AND h.AEDAT < (SELECT end_ts FROM params)
      AND h.BUKRS IN ([Company Code filter])
      AND h.BSART IN ([Document Type filter])
    GROUP BY
        h.MANDT,
        h.EBELN,
        h.LIFNR,
        h.BSART,
        h.BUKRS,
        h.AEDAT,
        h.ERNAM,
        h.BEDAT
),
po_items AS (
    SELECT
        i.MANDT,
        i.EBELN AS PurchaseOrderNumber,
        i.EBELP,
        i.BANFN AS PurchaseRequisitionNumber,
        i.BEDNR,
        i.EINDT AS RequestedDeliveryDate
    FROM EKPO i
),
events AS (
    SELECT
        p.PurchaseOrderNumber,
        'Purchase Requisition Created' AS ActivityName,
        CAST(r.[Purchase requisition creation timestamp] AS TIMESTAMP) AS EventTime,
        s.source_system AS SourceSystem,
        s.last_data_update AS LastDataUpdate,
        p.VendorId,
        CAST(r.[Purchase requisition created by] AS NVARCHAR(100)) AS UserName,
        p.TotalNetAmount,
        CAST(r.[Purchase requisition number] AS NVARCHAR(20)) AS PurchaseRequisitionNumber,
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    INNER JOIN [Your requisition source table or view] r
        ON r.[Client] = p.MANDT
       AND r.[Purchase requisition number] = p.PurchaseRequisitionNumber
    CROSS JOIN params s
    WHERE r.[Purchase requisition creation timestamp] >= s.start_ts
      AND r.[Purchase requisition creation timestamp] < s.end_ts

    UNION ALL

    SELECT
        p.PurchaseOrderNumber,
        'Purchase Requisition Approved' AS ActivityName,
        CAST(r.[Purchase requisition approval timestamp] AS TIMESTAMP) AS EventTime,
        s.source_system,
        s.last_data_update,
        p.VendorId,
        CAST(r.[Purchase requisition approver] AS NVARCHAR(100)),
        p.TotalNetAmount,
        CAST(r.[Purchase requisition number] AS NVARCHAR(20)),
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    INNER JOIN [Your requisition approval history table or view] r
        ON r.[Client] = p.MANDT
       AND r.[Purchase requisition number] = p.PurchaseRequisitionNumber
    CROSS JOIN params s
    WHERE r.[Purchase requisition approval timestamp] >= s.start_ts
      AND r.[Purchase requisition approval timestamp] < s.end_ts
      AND r.[Approval status] = '[Approved status value]'

    UNION ALL

    SELECT
        p.PurchaseOrderNumber,
        'Purchase Order Created',
        CAST(p.PODate AS TIMESTAMP),
        s.source_system,
        s.last_data_update,
        p.VendorId,
        p.POCreatedBy,
        p.TotalNetAmount,
        p.PurchaseRequisitionNumber,
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    CROSS JOIN params s
    WHERE p.PODate >= CAST(s.start_ts AS DATE)
      AND p.PODate < CAST(s.end_ts AS DATE)

    UNION ALL

    SELECT
        p.PurchaseOrderNumber,
        'Purchase Order Approved',
        CAST(a.[Purchase order approval timestamp] AS TIMESTAMP),
        s.source_system,
        s.last_data_update,
        p.VendorId,
        CAST(a.[Approver] AS NVARCHAR(100)),
        p.TotalNetAmount,
        p.PurchaseRequisitionNumber,
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    INNER JOIN [Your purchase order release history table or view] a
        ON a.[Client] = p.MANDT
       AND a.[Purchase order number] = p.PurchaseOrderNumber
    CROSS JOIN params s
    WHERE a.[Purchase order approval timestamp] >= s.start_ts
      AND a.[Purchase order approval timestamp] < s.end_ts
      AND a.[Release status] = '[Approved release status value]'

    UNION ALL

    SELECT
        p.PurchaseOrderNumber,
        'Purchase Order Sent to Vendor',
        CAST(o.[Output timestamp] AS TIMESTAMP),
        s.source_system,
        s.last_data_update,
        p.VendorId,
        CAST(o.[Output user] AS NVARCHAR(100)),
        p.TotalNetAmount,
        p.PurchaseRequisitionNumber,
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    INNER JOIN [Your purchase order output source table or view] o
        ON o.[Client] = p.MANDT
       AND o.[Purchase order number] = p.PurchaseOrderNumber
    CROSS JOIN params s
    WHERE o.[Output timestamp] >= s.start_ts
      AND o.[Output timestamp] < s.end_ts
      AND o.[Output status] = '[Successfully processed output status]'

    UNION ALL

    SELECT
        p.PurchaseOrderNumber,
        'Purchase Order Changed',
        CAST(c.[Change timestamp] AS TIMESTAMP),
        s.source_system,
        s.last_data_update,
        p.VendorId,
        CAST(c.[Changed by] AS NVARCHAR(100)),
        p.TotalNetAmount,
        p.PurchaseRequisitionNumber,
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    INNER JOIN [Your purchase order change history table or view] c
        ON c.[Client] = p.MANDT
       AND c.[Purchase order number] = p.PurchaseOrderNumber
    CROSS JOIN params s
    WHERE c.[Change timestamp] >= s.start_ts
      AND c.[Change timestamp] < s.end_ts
      AND c.[Change indicator] = '[Changed indicator value]'

    UNION ALL

    SELECT
        p.PurchaseOrderNumber,
        'Goods Receipt Posted',
        CAST(g.[Goods movement timestamp] AS TIMESTAMP),
        s.source_system,
        s.last_data_update,
        p.VendorId,
        CAST(g.[Posted by] AS NVARCHAR(100)),
        p.TotalNetAmount,
        p.PurchaseRequisitionNumber,
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    INNER JOIN [Your goods movement source table or view] g
        ON g.[Client] = p.MANDT
       AND g.[Purchase order number] = p.PurchaseOrderNumber
    CROSS JOIN params s
    WHERE g.[Goods movement timestamp] >= s.start_ts
      AND g.[Goods movement timestamp] < s.end_ts
      AND g.[Movement type] IN ([Goods receipt movement types])
      AND g.[Reversal indicator] IS NULL

    UNION ALL

    SELECT
        p.PurchaseOrderNumber,
        'Services Confirmation Entered',
        CAST(v.[Service entry timestamp] AS TIMESTAMP),
        s.source_system,
        s.last_data_update,
        p.VendorId,
        CAST(v.[Entered by] AS NVARCHAR(100)),
        p.TotalNetAmount,
        p.PurchaseRequisitionNumber,
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    INNER JOIN [Your service entry sheet source table or view] v
        ON v.[Client] = p.MANDT
       AND v.[Purchase order number] = p.PurchaseOrderNumber
    CROSS JOIN params s
    WHERE v.[Service entry timestamp] >= s.start_ts
      AND v.[Service entry timestamp] < s.end_ts
      AND v.[Service entry status] = '[Accepted service entry status]'

    UNION ALL

    SELECT
        p.PurchaseOrderNumber,
        'Goods Returned',
        CAST(g.[Goods movement timestamp] AS TIMESTAMP),
        s.source_system,
        s.last_data_update,
        p.VendorId,
        CAST(g.[Posted by] AS NVARCHAR(100)),
        p.TotalNetAmount,
        p.PurchaseRequisitionNumber,
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    INNER JOIN [Your goods movement source table or view] g
        ON g.[Client] = p.MANDT
       AND g.[Purchase order number] = p.PurchaseOrderNumber
    CROSS JOIN params s
    WHERE g.[Goods movement timestamp] >= s.start_ts
      AND g.[Goods movement timestamp] < s.end_ts
      AND g.[Movement type] IN ([Goods return movement types])
      AND g.[Reversal indicator] IS NULL

    UNION ALL

    SELECT
        p.PurchaseOrderNumber,
        'Invoice Received',
        CAST(i.[Invoice posting timestamp] AS TIMESTAMP),
        s.source_system,
        s.last_data_update,
        p.VendorId,
        CAST(i.[Posted by] AS NVARCHAR(100)),
        p.TotalNetAmount,
        p.PurchaseRequisitionNumber,
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    INNER JOIN [Your supplier invoice source table or view] i
        ON i.[Client] = p.MANDT
       AND i.[Purchase order number] = p.PurchaseOrderNumber
    CROSS JOIN params s
    WHERE i.[Invoice posting timestamp] >= s.start_ts
      AND i.[Invoice posting timestamp] < s.end_ts
      AND i.[Invoice status] = '[Posted invoice status]'

    UNION ALL

    SELECT
        p.PurchaseOrderNumber,
        'Invoice Paid',
        CAST(i.[Clearing timestamp] AS TIMESTAMP),
        s.source_system,
        s.last_data_update,
        p.VendorId,
        CAST(i.[Cleared by] AS NVARCHAR(100)),
        p.TotalNetAmount,
        p.PurchaseRequisitionNumber,
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    INNER JOIN [Your supplier invoice clearing source table or view] i
        ON i.[Client] = p.MANDT
       AND i.[Purchase order number] = p.PurchaseOrderNumber
    CROSS JOIN params s
    WHERE i.[Clearing timestamp] >= s.start_ts
      AND i.[Clearing timestamp] < s.end_ts
      AND i.[Clearing status] = '[Cleared status value]'

    UNION ALL

    SELECT
        p.PurchaseOrderNumber,
        'Purchase Order Completed',
        CAST(x.[Completion timestamp] AS TIMESTAMP),
        s.source_system,
        s.last_data_update,
        p.VendorId,
        CAST(x.[Completion user] AS NVARCHAR(100)),
        p.TotalNetAmount,
        p.PurchaseRequisitionNumber,
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    INNER JOIN [Your purchase order item status source table or view] x
        ON x.[Client] = p.MANDT
       AND x.[Purchase order number] = p.PurchaseOrderNumber
    CROSS JOIN params s
    WHERE x.[Completion timestamp] >= s.start_ts
      AND x.[Completion timestamp] < s.end_ts
      AND x.[Delivery completed indicator] = '[Set indicator value]'
      AND x.[Final invoice indicator] = '[Set indicator value]'

    UNION ALL

    SELECT
        p.PurchaseOrderNumber,
        'Purchase Order Deleted',
        CAST(d.[Deletion timestamp] AS TIMESTAMP),
        s.source_system,
        s.last_data_update,
        p.VendorId,
        CAST(d.[Deleted by] AS NVARCHAR(100)),
        p.TotalNetAmount,
        p.PurchaseRequisitionNumber,
        p.RequestedDeliveryDate,
        p.DocumentType
    FROM po_base p
    INNER JOIN [Your purchase order deletion history table or view] d
        ON d.[Client] = p.MANDT
       AND d.[Purchase order number] = p.PurchaseOrderNumber
    CROSS JOIN params s
    WHERE d.[Deletion timestamp] >= s.start_ts
      AND d.[Deletion timestamp] < s.end_ts
      AND d.[Deletion indicator] = '[Set deletion indicator value]'
)
SELECT
    PurchaseOrderNumber,
    ActivityName,
    EventTime,
    SourceSystem,
    LastDataUpdate,
    VendorId,
    UserName,
    TotalNetAmount,
    PurchaseRequisitionNumber,
    RequestedDeliveryDate,
    DocumentType
FROM events
WHERE PurchaseOrderNumber IS NOT NULL
  AND ActivityName IS NOT NULL
  AND EventTime IS NOT NULL
ORDER BY PurchaseOrderNumber, EventTime, ActivityName;

REPORT z_pm_po_extract.

" ====================================================================
" SELECTION SCREEN
" ====================================================================

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
  SELECT-OPTIONS: s_aedat FOR sy-datum OBLIGATORY.
  SELECT-OPTIONS: s_bukrs FOR ekko-bukrs.
  SELECT-OPTIONS: s_bsart FOR ekko-bsart.
  PARAMETERS: p_sysid TYPE string DEFAULT '[Your SAP System ID]'.
SELECTION-SCREEN END OF BLOCK b1.

" ====================================================================
" DATA DECLARATIONS
" ====================================================================

TYPES: BEGIN OF ty_event_log,
         purchaseordernumber      TYPE ebeln,
         activityname             TYPE string,
         eventtime                TYPE timestamp,
         sourcesystem             TYPE string,
         lastdataupdate           TYPE timestamp,
         vendorid                 TYPE lifnr,
         username                 TYPE ernam,
         totalnetamount           TYPE netwr,
         purchaserequisitionnumber TYPE banfn,
         requesteddeliverydate    TYPE eedat,
         documenttype             TYPE bsart,
       END OF ty_event_log.

DATA: lt_event_log TYPE TABLE OF ty_event_log,
      ls_event_log TYPE ty_event_log.

DATA: lt_ekko TYPE TABLE OF ekko,
      lt_ekpo TYPE TABLE OF ekpo.

" ====================================================================
" START OF SELECTION
" ====================================================================

START-OF-SELECTION.

  " Get current timestamp for LastDataUpdate
  GET TIME STAMP FIELD ls_event_log-lastdataupdate.
  ls_event_log-sourcesystem = p_sysid.

  " --- Initial Data Selection: Purchase Orders in Scope ---
  SELECT * FROM ekko INTO TABLE lt_ekko
    WHERE aedat IN s_aedat
      AND bukrs IN s_bukrs
      AND bsart IN s_bsart.

  IF lt_ekko IS INITIAL.
    MESSAGE 'No Purchase Orders found for the given criteria.' TYPE 'S' DISPLAY LIKE 'E'.
    RETURN.
  ENDIF.

  SELECT * FROM ekpo INTO TABLE lt_ekpo
    FOR ALL ENTRIES IN lt_ekko
    WHERE ebeln = lt_ekko-ebeln.

  " --- 1. Purchase Requisition Created ---
  SELECT ban.banfn, ban.erdat, ban.erzet, ban.ernam,
         ekpo.ebeln, ekpo.netwr, ekpo.eindt, ekpo.bsart, ekpo.lifnr, ekko.bukrs
    FROM eban AS ban
    INNER JOIN ekpo AS ekpo ON ban.banfn = ekpo.banfn AND ban.bnfpo = ekpo.bnfpo
    INNER JOIN ekko AS ekko ON ekpo.ebeln = ekko.ebeln
    WHERE ekko.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
    INTO TABLE @DATA(lt_pr_created).

  LOOP AT lt_pr_created INTO DATA(ls_pr_created).
    ls_event_log-purchaseordernumber = ls_pr_created-ebeln.
    ls_event_log-activityname = 'Purchase Requisition Created'.
    CONVERT DATE ls_pr_created-erdat TIME ls_pr_created-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
    ls_event_log-vendorid = ls_pr_created-lifnr.
    ls_event_log-username = ls_pr_created-ernam.
    ls_event_log-totalnetamount = ls_pr_created-netwr.
    ls_event_log-purchaserequisitionnumber = ls_pr_created-banfn.
    ls_event_log-requesteddeliverydate = ls_pr_created-eindt.
    ls_event_log-documenttype = ls_pr_created-bsart.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

  " --- 2. Purchase Requisition Approved (via Change Docs on Release Indicator) ---
  SELECT h.objectid, h.udate, h.utime, h.username
    FROM cdhdr AS h
    INNER JOIN cdpos AS p ON h.objectclas = p.objectclas AND h.objectid = p.objectid AND h.changenr = p.changenr
    INNER JOIN ekpo AS ekpo ON h.objectid = ekpo.banfn
    INNER JOIN ekko AS ekko ON ekpo.ebeln = ekko.ebeln
    WHERE h.objectclas = 'BANF'
      AND p.tabname = 'EBAN'
      AND p.fname = 'FRGZU'
      AND p.value_new = 'X' "Configure based on your system release indicator for 'Approved'
      AND ekko.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
    INTO TABLE @DATA(lt_pr_approved).

  LOOP AT lt_pr_approved INTO DATA(ls_pr_approved).
    SELECT SINGLE ebeln FROM ekpo INTO ls_event_log-purchaseordernumber WHERE banfn = ls_pr_approved-objectid.
    ls_event_log-activityname = 'Purchase Requisition Approved'.
    CONVERT DATE ls_pr_approved-udate TIME ls_pr_approved-utime INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
    ls_event_log-username = ls_pr_approved-username.
    " Other attributes can be populated with another SELECT if needed.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

  " --- 3. Purchase Order Created ---
  LOOP AT lt_ekko INTO DATA(ls_ekko_created).
    ls_event_log-purchaseordernumber = ls_ekko_created-ebeln.
    ls_event_log-activityname = 'Purchase Order Created'.
    CONVERT DATE ls_ekko_created-aedat TIME ls_ekko_created-erzet INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
    ls_event_log-vendorid = ls_ekko_created-lifnr.
    ls_event_log-username = ls_ekko_created-ernam.
    ls_event_log-totalnetamount = ls_ekko_created-rlwrt.
    ls_event_log-purchaserequisitionnumber = ''. "Can be enriched later if needed
    ls_event_log-requesteddeliverydate = ''. "Can be enriched from EKPO
    ls_event_log-documenttype = ls_ekko_created-bsart.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

  " --- 4. Purchase Order Approved (via Change Docs on Release Indicator) ---
  SELECT h.objectid, h.udate, h.utime, h.username
    FROM cdhdr AS h
    INNER JOIN cdpos AS p ON h.objectclas = p.objectclas AND h.objectid = p.objectid AND h.changenr = p.changenr
    WHERE h.objectclas = 'EINKBELEG'
      AND p.tabname = 'EKKO'
      AND p.fname = 'FRGKE'
      AND p.value_new = 'R' "R for Released
      AND h.objectid IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
    INTO TABLE @DATA(lt_po_approved).

  LOOP AT lt_po_approved INTO DATA(ls_po_approved).
    ls_event_log-purchaseordernumber = ls_po_approved-objectid.
    ls_event_log-activityname = 'Purchase Order Approved'.
    CONVERT DATE ls_po_approved-udate TIME ls_po_approved-utime INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
    ls_event_log-username = ls_po_approved-username.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

  " --- 5. Purchase Order Sent to Vendor ---
  SELECT n.objky, n.vstat, n.datvr, n.uhrvr, e.ernam
    FROM nast AS n
    INNER JOIN ekko AS e ON n.objky = e.ebeln
    WHERE n.kappl = 'EF' "Application for Purchasing
      AND n.kschl = '[Your PO Output Type]' "e.g. NEU
      AND n.vstat = '1' "Successfully processed
      AND n.objky IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
    INTO TABLE @DATA(lt_po_sent).

  LOOP AT lt_po_sent INTO DATA(ls_po_sent).
    ls_event_log-purchaseordernumber = ls_po_sent-objky.
    ls_event_log-activityname = 'Purchase Order Sent to Vendor'.
    CONVERT DATE ls_po_sent-datvr TIME ls_po_sent-uhrvr INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
    ls_event_log-username = ls_po_sent-ernam.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

  " --- 6. Purchase Order Changed ---
  SELECT objectid, udate, utime, username FROM cdhdr
    WHERE objectclas = 'EINKBELEG'
      AND tcode IN ('ME22', 'ME22N')
      AND objectid IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
    INTO TABLE @DATA(lt_po_changed).

  LOOP AT lt_po_changed INTO DATA(ls_po_changed).
    ls_event_log-purchaseordernumber = ls_po_changed-objectid.
    ls_event_log-activityname = 'Purchase Order Changed'.
    CONVERT DATE ls_po_changed-udate TIME ls_po_changed-utime INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
    ls_event_log-username = ls_po_changed-username.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

  " --- 7. Goods Receipt Posted & 9. Goods Returned ---
  SELECT e.ebeln, m.budat, m.cpudt, m.cputm, m.usnam, b.shkzg, b.bwart
    FROM mkpf AS m
    INNER JOIN mseg AS s ON m.mblnr = s.mblnr AND m.mjahr = s.mjahr
    INNER JOIN t156 AS t ON s.bwart = t.bwart
    INNER JOIN ekbe AS e ON s.ebeln = e.ebeln AND s.ebelp = e.ebelp AND s.mblnr = e.belnr AND s.mjahr = e.gjahr
    WHERE e.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
    AND e.bwart IN ('101', '102', '122', '123') "GR, GR Reversal, Return
    INTO TABLE @DATA(lt_goods_mvmt).

  LOOP AT lt_goods_mvmt INTO DATA(ls_goods_mvmt).
    ls_event_log-purchaseordernumber = ls_goods_mvmt-ebeln.
    IF ls_goods_mvmt-bwart = '101'.
        ls_event_log-activityname = 'Goods Receipt Posted'.
    ELSE.
        ls_event_log-activityname = 'Goods Returned'.
    ENDIF.
    CONVERT DATE ls_goods_mvmt-cpudt TIME ls_goods_mvmt-cputm INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
    ls_event_log-username = ls_goods_mvmt-usnam.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

  " --- 8. Services Confirmation Entered ---
  SELECT h.erdat, h.erzeit, h.ernam, l.ebeln
    FROM essr AS h
    INNER JOIN esll AS l ON h.lblni = l.lblni
    WHERE l.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
    INTO TABLE @DATA(lt_services).

  LOOP AT lt_services INTO DATA(ls_services).
    ls_event_log-purchaseordernumber = ls_services-ebeln.
    ls_event_log-activityname = 'Services Confirmation Entered'.
    CONVERT DATE ls_services-erdat TIME ls_services-erzeit INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
    ls_event_log-username = ls_services-ernam.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

  " --- 10. Invoice Received ---
  SELECT r.ebeln, r.cpudt, r.cputm, r.usnam
    FROM rbkp AS r
    WHERE r.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
    INTO TABLE @DATA(lt_invoice_rcvd).

  LOOP AT lt_invoice_rcvd INTO DATA(ls_invoice_rcvd).
    ls_event_log-purchaseordernumber = ls_invoice_rcvd-ebeln.
    ls_event_log-activityname = 'Invoice Received'.
    CONVERT DATE ls_invoice_rcvd-cpudt TIME ls_invoice_rcvd-cputm INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
    ls_event_log-username = ls_invoice_rcvd-usnam.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

  " --- 11. Invoice Paid ---
  SELECT b.ebeln, s.augdt, s.augbl, b.usnam
    FROM rbkp AS b
    INNER JOIN bseg AS e ON b.belnr = e.belnr AND b.gjahr = e.gjahr
    INNER JOIN bsak AS s ON e.bukrs = s.bukrs AND e.belnr = s.belnr AND e.gjahr = s.gjahr AND e.buzei = s.buzei
    WHERE b.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
      AND s.augdt IS NOT NULL
    INTO TABLE @DATA(lt_invoice_paid).

  LOOP AT lt_invoice_paid INTO DATA(ls_invoice_paid).
    ls_event_log-purchaseordernumber = ls_invoice_paid-ebeln.
    ls_event_log-activityname = 'Invoice Paid'.
    CONVERT DATE ls_invoice_paid-augdt INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
    ls_event_log-username = ls_invoice_paid-usnam.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

  " --- 12. Purchase Order Completed & 13. Purchase Order Deleted (via Change Docs) ---
  SELECT h.objectid, h.udate, h.utime, h.username, p.fname
    FROM cdhdr AS h
    INNER JOIN cdpos AS p ON h.changenr = p.changenr
    INNER JOIN ekpo AS ekpo ON h.objectid = |{ ekpo.ebeln }{ ekpo.ebelp }|
    WHERE h.objectclas = 'EINKBELEG'
      AND p.tabname = 'EKPO'
      AND p.fname IN ('ELIKZ', 'EREKZ', 'LOEKZ')
      AND p.value_new = 'X'
      AND ekpo.ebeln IN @( VALUE #( FOR ls_ekko IN lt_ekko ( ls_ekko-ebeln ) ) )
    INTO TABLE @DATA(lt_po_status_change).

  LOOP AT lt_po_status_change INTO DATA(ls_po_status_change).
    ls_event_log-purchaseordernumber = substring( val = ls_po_status_change-objectid, off = 0, len = 10 ).
    CASE ls_po_status_change-fname.
      WHEN 'LOEKZ'.
        ls_event_log-activityname = 'Purchase Order Deleted'.
      WHEN 'ELIKZ' OR 'EREKZ'.
        "This logic may need refinement to check if both are now set.
        ls_event_log-activityname = 'Purchase Order Completed'.
    ENDCASE.
    CONVERT DATE ls_po_status_change-udate TIME ls_po_status_change-utime INTO TIME STAMP ls_event_log-eventtime TIME ZONE sy-zonlo.
    ls_event_log-username = ls_po_status_change-username.
    APPEND ls_event_log TO lt_event_log.
  ENDLOOP.

  " --- Final Output to CSV ---
  CALL METHOD cl_gui_frontend_services=>gui_download
    EXPORTING
      filename = 'C:\temp\po_event_log.csv'
      filetype = 'ASC'
    CHANGING
      data_tab = lt_event_log.