Purchase to Pay - 発注書データテンプレート

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

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.