Datatemplate voor Purchase to Pay - Purchase Order

/* This is a conceptual representation of the logic to be built in the OTBI Analysis using the 'Union All' feature. 
Each SELECT statement corresponds to a separate criteria block in the OTBI analysis. */

-- 1. Purchase Requisition Created
SELECT
  "PO Requisition Lines All"."PO Header Number" AS PurchaseOrder,
  'Purchase Requisition Created' AS ActivityName,
  "Requisition Headers"."Creation Date" AS EventTime,
  "Requisition Headers"."Creation Date" AS EndTime,
  "Requisition Headers"."Created By" AS UserName,
  "Requisition Headers"."Department" AS DepartmentName,
  NULL AS VendorName,
  NULL AS PurchaseOrderTotalAmount,
  "Requisition Lines"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Requisition Headers"."Creation Date" IS NOT NULL

UNION ALL

-- 2. Purchase Requisition Approved
SELECT
  "PO Requisition Lines All"."PO Header Number" AS PurchaseOrder,
  'Purchase Requisition Approved' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisition Headers"."Department" AS DepartmentName,
  NULL AS VendorName,
  NULL AS PurchaseOrderTotalAmount,
  "Requisition Lines"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'REQUISITION' AND "Document History"."Action" = 'APPROVE'

UNION ALL

-- 3. Purchase Order Created
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Created' AS ActivityName,
  "PO Headers All"."Creation Date" AS EventTime,
  "PO Headers All"."Creation Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Creation Date" IS NOT NULL

UNION ALL

-- 4. Purchase Order Submitted
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Submitted' AS ActivityName,
  "PO Headers All"."Submitted Date" AS EventTime,
  "PO Headers All"."Submitted Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName, -- Submitter is often the creator
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Submitted Date" IS NOT NULL

UNION ALL

-- 5. Purchase Order Approved
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Approved' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'PO' AND "Document History"."Action" = 'APPROVE'

UNION ALL

-- 6. Purchase Order Rejected
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Rejected' AS ActivityName,
  "Document History"."Action Date" AS EventTime,
  "Document History"."Action Date" AS EndTime,
  "Document History"."Performer" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  "Document History"."Performer" AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Document History"."Document Type" = 'PO' AND "Document History"."Action" = 'REJECT'

UNION ALL

-- 7. Purchase Order Sent to Vendor (Communicated)
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Sent to Vendor' AS ActivityName,
  "PO Headers All"."PO Communication Date" AS EventTime,
  "PO Headers All"."PO Communication Date" AS EndTime,
  "PO Headers All"."Created By" AS UserName, -- User who took action
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."PO Communication Date" IS NOT NULL AND "PO Headers All"."Document Status" <> 'INCOMPLETE'

UNION ALL

-- 8. Purchase Order Acknowledged
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Acknowledged' AS ActivityName,
  "PO Headers All"."Acknowledged Date" AS EventTime,
  "PO Headers All"."Acknowledged Date" AS EndTime,
  NULL AS UserName, -- Often an automated or system action
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Acknowledged Date" IS NOT NULL

UNION ALL

-- 9. Purchase Order Changed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Changed' AS ActivityName,
  "PO Headers All"."Creation Date" AS EventTime, -- The creation date of the new revision
  "PO Headers All"."Creation Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Revision Number" > 0

UNION ALL

-- 10. Goods Receipt Created
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Receipt Created' AS ActivityName,
  "Receipt"."Creation Date" AS EventTime,
  "Receipt"."Creation Date" AS EndTime,
  "Receipt"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt"."Creation Date" IS NOT NULL

UNION ALL

-- 11. Goods Received
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Received' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RECEIVE'

UNION ALL

-- 12. Quality Inspection Performed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Quality Inspection Performed' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" IN ('ACCEPT', 'REJECT')

UNION ALL

-- 13. Goods Returned to Vendor
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Goods Returned to Vendor' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RETURN TO VENDOR'

UNION ALL

-- 14. Services Delivered Confirmed (Modeled as a receipt against a Service line)
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Services Delivered Confirmed' AS ActivityName,
  "Receipt Line Transaction"."Transaction Date" AS EventTime,
  "Receipt Line Transaction"."Transaction Date" AS EndTime,
  "Receipt Line Transaction"."Created By" AS UserName,
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "Receipt Line Transaction"."Transaction Type" = 'RECEIVE' AND "PO Lines All"."Line Type" = 'Fixed Price Services'

UNION ALL

-- 15. Purchase Order Canceled
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Canceled' AS ActivityName,
  "PO Headers All"."Cancel Date" AS EventTime,
  "PO Headers All"."Cancel Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName, -- User who cancelled
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Cancel Flag" = 'Y' AND "PO Headers All"."Cancel Date" IS NOT NULL

UNION ALL

-- 16. Purchase Order Finally Closed
SELECT
  "PO Headers All"."PO Number" AS PurchaseOrder,
  'Purchase Order Finally Closed' AS ActivityName,
  "PO Headers All"."Closed Date" AS EventTime,
  "PO Headers All"."Closed Date" AS EndTime,
  "PO Headers All"."Changed By" AS UserName, -- User who closed
  "Requisitioning BU"."Department Name" AS DepartmentName,
  "Supplier"."Supplier" AS VendorName,
  "Base Measures"."Ordered Amount" AS PurchaseOrderTotalAmount,
  "PO Schedules All"."Requested Delivery Date" AS RequestedDeliveryDate,
  NULL AS ApproverName
FROM "Procurement - Purchasing Real Time"
WHERE "PO Headers All"."Closed Code" = 'FINALLY CLOSED' AND "PO Headers All"."Closed Date" IS NOT NULL

WITH
requisition_created AS (
    SELECT
        CAST([Purchase order identifier from requisition linkage] AS VARCHAR(200)) AS PurchaseOrder,
        'Purchase Requisition Created' AS ActivityName,
        [Requisition creation timestamp] AS EventTime,
        [Requisition creation timestamp] AS EndTime,
        [Requisition creator user] AS UserName,
        [Requisition department] AS DepartmentName,
        [Supplier name linked to requisition] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Requisition identifier] AS SourceEventIdentifier,
        'RequisitionCreated' AS SourceEventType
    FROM [Your requisition header Public View Object]
    WHERE [Requisition creation timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Requisition creation timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
requisition_approved AS (
    SELECT
        CAST([Purchase order identifier from requisition linkage] AS VARCHAR(200)) AS PurchaseOrder,
        'Purchase Requisition Approved' AS ActivityName,
        [Requisition approval timestamp] AS EventTime,
        [Requisition approval timestamp] AS EndTime,
        [Requisition approval user] AS UserName,
        [Requisition department] AS DepartmentName,
        [Supplier name linked to requisition] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        [Requisition approver name] AS ApproverName,
        [Requisition approval action identifier] AS SourceEventIdentifier,
        'RequisitionApproved' AS SourceEventType
    FROM [Your requisition approval action history Public View Object]
    WHERE [Requisition approval timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Requisition approval timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
purchase_order_created AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Purchase Order Created' AS ActivityName,
        [Purchase order creation timestamp] AS EventTime,
        [Purchase order creation timestamp] AS EndTime,
        [Purchase order creator user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Purchase order identifier] AS SourceEventIdentifier,
        'PurchaseOrderCreated' AS SourceEventType
    FROM PoHeaderPVO
    WHERE [Purchase order creation timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Purchase order creation timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
purchase_order_submitted AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Purchase Order Submitted' AS ActivityName,
        [Submission timestamp] AS EventTime,
        [Submission timestamp] AS EndTime,
        [Submission user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Purchase order action identifier] AS SourceEventIdentifier,
        'PurchaseOrderSubmitted' AS SourceEventType
    FROM [Your purchase order action history Public View Object]
    WHERE [Action name] = 'Submitted'
      AND [Submission timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Submission timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
purchase_order_approved AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Purchase Order Approved' AS ActivityName,
        [Approval timestamp] AS EventTime,
        [Approval timestamp] AS EndTime,
        [Approval user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        [Approver name] AS ApproverName,
        [Purchase order action identifier] AS SourceEventIdentifier,
        'PurchaseOrderApproved' AS SourceEventType
    FROM [Your purchase order action history Public View Object]
    WHERE [Action name] = 'Approved'
      AND [Approval timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Approval timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
purchase_order_rejected AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Purchase Order Rejected' AS ActivityName,
        [Rejection timestamp] AS EventTime,
        [Rejection timestamp] AS EndTime,
        [Rejection user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        [Approver name] AS ApproverName,
        [Purchase order action identifier] AS SourceEventIdentifier,
        'PurchaseOrderRejected' AS SourceEventType
    FROM [Your purchase order action history Public View Object]
    WHERE [Action name] = 'Rejected'
      AND [Rejection timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Rejection timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
purchase_order_sent AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Purchase Order Sent to Vendor' AS ActivityName,
        [Vendor communication timestamp] AS EventTime,
        [Vendor communication timestamp] AS EndTime,
        [Communication user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Communication identifier] AS SourceEventIdentifier,
        'PurchaseOrderSentToVendor' AS SourceEventType
    FROM [Your purchase order vendor communication Public View Object]
    WHERE [Communication status] = 'Sent'
      AND [Vendor communication timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Vendor communication timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
purchase_order_acknowledged AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Purchase Order Acknowledged' AS ActivityName,
        [Vendor acknowledgement timestamp] AS EventTime,
        [Vendor acknowledgement timestamp] AS EndTime,
        [Acknowledgement recorded user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Vendor acknowledgement identifier] AS SourceEventIdentifier,
        'PurchaseOrderAcknowledged' AS SourceEventType
    FROM [Your purchase order acknowledgement Public View Object]
    WHERE [Vendor acknowledgement timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Vendor acknowledgement timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
purchase_order_changed AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Purchase Order Changed' AS ActivityName,
        [Revision creation timestamp] AS EventTime,
        [Revision creation timestamp] AS EndTime,
        [Revision creator user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Purchase order revision identifier] AS SourceEventIdentifier,
        'PurchaseOrderChanged' AS SourceEventType
    FROM [Your purchase order revision Public View Object]
    WHERE [Revision creation timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Revision creation timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
goods_receipt_created AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Goods Receipt Created' AS ActivityName,
        [Receiving document creation timestamp] AS EventTime,
        [Receiving document creation timestamp] AS EndTime,
        [Receiving document creator user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Receiving document identifier] AS SourceEventIdentifier,
        'GoodsReceiptCreated' AS SourceEventType
    FROM [Your receiving document Public View Object]
    WHERE [Receiving document creation timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Receiving document creation timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
goods_received AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Goods Received' AS ActivityName,
        [Goods receipt transaction timestamp] AS EventTime,
        [Goods receipt transaction timestamp] AS EndTime,
        [Goods receipt user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Goods receipt transaction identifier] AS SourceEventIdentifier,
        'GoodsReceived' AS SourceEventType
    FROM [Your receiving transaction Public View Object]
    WHERE [Receiving transaction type] = 'Receipt'
      AND [Goods receipt transaction timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Goods receipt transaction timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
quality_inspection_performed AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Quality Inspection Performed' AS ActivityName,
        [Inspection completion timestamp] AS EventTime,
        [Inspection completion timestamp] AS EndTime,
        [Inspector user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Inspection identifier] AS SourceEventIdentifier,
        'QualityInspectionPerformed' AS SourceEventType
    FROM [Your receiving quality inspection Public View Object]
    WHERE [Inspection completion timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Inspection completion timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
goods_returned_to_vendor AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Goods Returned to Vendor' AS ActivityName,
        [Return transaction timestamp] AS EventTime,
        [Return transaction timestamp] AS EndTime,
        [Return transaction user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Return transaction identifier] AS SourceEventIdentifier,
        'GoodsReturnedToVendor' AS SourceEventType
    FROM [Your receiving return transaction Public View Object]
    WHERE [Return transaction timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Return transaction timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
services_delivered_confirmed AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Services Delivered Confirmed' AS ActivityName,
        [Service confirmation timestamp] AS EventTime,
        [Service confirmation timestamp] AS EndTime,
        [Service confirmation user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Service confirmation identifier] AS SourceEventIdentifier,
        'ServicesDeliveredConfirmed' AS SourceEventType
    FROM [Your service confirmation Public View Object]
    WHERE [Service confirmation timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Service confirmation timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
purchase_order_canceled AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Purchase Order Canceled' AS ActivityName,
        [Cancellation timestamp] AS EventTime,
        [Cancellation timestamp] AS EndTime,
        [Cancellation user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Purchase order action identifier] AS SourceEventIdentifier,
        'PurchaseOrderCanceled' AS SourceEventType
    FROM [Your purchase order action history Public View Object]
    WHERE [Action name] = 'Canceled'
      AND [Cancellation timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Cancellation timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
),
purchase_order_finally_closed AS (
    SELECT
        CAST([Purchase order number] AS VARCHAR(200)) AS PurchaseOrder,
        'Purchase Order Finally Closed' AS ActivityName,
        [Final closure timestamp] AS EventTime,
        [Final closure timestamp] AS EndTime,
        [Final closure user] AS UserName,
        [Purchase order department] AS DepartmentName,
        [Supplier name] AS VendorName,
        [Purchase order total amount] AS PurchaseOrderTotalAmount,
        [Requested delivery date] AS RequestedDeliveryDate,
        CAST(NULL AS VARCHAR(200)) AS ApproverName,
        [Purchase order action identifier] AS SourceEventIdentifier,
        'PurchaseOrderFinallyClosed' AS SourceEventType
    FROM [Your purchase order action history Public View Object]
    WHERE [Action name] = 'Finally Closed'
      AND [Final closure timestamp] >= CAST('[Start date parameter]' AS TIMESTAMP)
      AND [Final closure timestamp] < CAST('[End date parameter]' AS TIMESTAMP)
)
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM requisition_created
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM requisition_approved
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM purchase_order_created
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM purchase_order_submitted
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM purchase_order_approved
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM purchase_order_rejected
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM purchase_order_sent
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM purchase_order_acknowledged
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM purchase_order_changed
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM goods_receipt_created
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM goods_received
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM quality_inspection_performed
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM goods_returned_to_vendor
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM services_delivered_confirmed
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM purchase_order_canceled
UNION ALL
SELECT PurchaseOrder, ActivityName, EventTime, EndTime, UserName, DepartmentName, VendorName, PurchaseOrderTotalAmount, RequestedDeliveryDate, ApproverName, SourceEventIdentifier, SourceEventType FROM purchase_order_finally_closed
ORDER BY PurchaseOrder, EventTime, ActivityName;

SELECT
    PRH.REQUISITION_NUMBER AS "PurchaseOrder",
    'Purchase Requisition Created' AS "ActivityName",
    PRH.CREATION_DATE AS "EventTime",
    PRH.CREATION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    HOU.NAME AS "DepartmentName",
    NULL AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    PRL.NEED_BY_DATE AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    POR_REQUISITION_HEADERS_ALL PRH
JOIN
    POR_REQUISITION_LINES_ALL PRL ON PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
LEFT JOIN
    PER_USERS PU ON PRH.CREATED_BY = PU.USERNAME
LEFT JOIN
    HR_ORGANIZATION_UNITS HOU ON PRH.DELIVER_TO_ORGANIZATION_ID = HOU.ORGANIZATION_ID
WHERE
    PRH.CREATION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    PRH.REQUISITION_NUMBER AS "PurchaseOrder",
    'Purchase Requisition Approved' AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    PAH.ACTION_DATE AS "EndTime",
    PU_PERFORMER.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    NULL AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    PU_PERFORMER.USERNAME AS "ApproverName"
FROM
    PO_ACTION_HISTORY PAH
JOIN
    POR_REQUISITION_HEADERS_ALL PRH ON PAH.OBJECT_ID = PRH.REQUISITION_HEADER_ID
JOIN
    PER_USERS PU_PERFORMER ON PAH.PERFORMER_ID = PU_PERFORMER.USER_ID
WHERE
    PAH.OBJECT_TYPE_CODE = 'REQUISITION'
    AND PAH.ACTION_CODE = 'APPROVE'
    AND PAH.ACTION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    'Purchase Order Created' AS "ActivityName",
    POH.CREATION_DATE AS "EventTime",
    POH.CREATION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    HOU.NAME AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    (SELECT SUM(POL.AMOUNT) FROM PO_LINES_ALL POL WHERE POL.PO_HEADER_ID = POH.PO_HEADER_ID) AS "PurchaseOrderTotalAmount",
    (SELECT MIN(PLL.NEED_BY_DATE) FROM PO_LINE_LOCATIONS_ALL PLL WHERE PLL.PO_HEADER_ID = POH.PO_HEADER_ID) AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    PO_HEADERS_ALL POH
LEFT JOIN
    PER_USERS PU ON POH.CREATED_BY = PU.USERNAME
LEFT JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
LEFT JOIN
    PO_DISTRIBUTIONS_ALL PDA ON POH.PO_HEADER_ID = PDA.PO_HEADER_ID
LEFT JOIN
    HR_ORGANIZATION_UNITS HOU ON PDA.PJC_EXPENDITURE_ORGANIZATION_ID = HOU.ORGANIZATION_ID
WHERE
    POH.CREATION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')
    AND ROWNUM = 1

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    CASE PAH.ACTION_CODE
        WHEN 'SUBMIT' THEN 'Purchase Order Submitted'
        WHEN 'APPROVE' THEN 'Purchase Order Approved'
        WHEN 'REJECT' THEN 'Purchase Order Rejected'
        WHEN 'ACCEPT' THEN 'Purchase Order Acknowledged'
        WHEN 'CANCEL' THEN 'Purchase Order Canceled'
        WHEN 'FINALLY CLOSE' THEN 'Purchase Order Finally Closed'
    END AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    PAH.ACTION_DATE AS "EndTime",
    PU_PERFORMER.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    CASE WHEN PAH.ACTION_CODE IN ('APPROVE', 'REJECT') THEN PU_PERFORMER.USERNAME ELSE NULL END AS "ApproverName"
FROM
    PO_ACTION_HISTORY PAH
JOIN
    PO_HEADERS_ALL POH ON PAH.OBJECT_ID = POH.PO_HEADER_ID
LEFT JOIN
    PER_USERS PU_PERFORMER ON PAH.PERFORMER_ID = PU_PERFORMER.USER_ID
LEFT JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
WHERE
    PAH.OBJECT_TYPE_CODE = 'PO'
    AND PAH.ACTION_CODE IN ('SUBMIT', 'APPROVE', 'REJECT', 'ACCEPT', 'CANCEL', 'FINALLY CLOSE')
    AND PAH.ACTION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    'Purchase Order Sent to Vendor' AS "ActivityName",
    PAH.ACTION_DATE AS "EventTime",
    PAH.ACTION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    PO_HEADERS_ALL POH
JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
LEFT JOIN
    PER_USERS PU ON POH.SOLDTO_CONTACT_ID = PU.PERSON_ID
JOIN
    (SELECT OBJECT_ID, MIN(ACTION_DATE) AS ACTION_DATE FROM PO_ACTION_HISTORY WHERE OBJECT_TYPE_CODE = 'PO' AND ACTION_CODE = 'APPROVE' GROUP BY OBJECT_ID) PAH ON POH.PO_HEADER_ID = PAH.OBJECT_ID
WHERE
    POH.DOCUMENT_STATUS = 'OPEN'
    AND PAH.ACTION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    'Purchase Order Changed' AS "ActivityName",
    POH.LAST_UPDATE_DATE AS "EventTime",
    POH.LAST_UPDATE_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    PO_HEADERS_ALL POH
LEFT JOIN
    PER_USERS PU ON POH.LAST_UPDATED_BY = PU.USERNAME
LEFT JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
WHERE
    POH.REVISION_NUM > 0
    AND POH.LAST_UPDATE_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    'Goods Receipt Created' AS "ActivityName",
    RSH.CREATION_DATE AS "EventTime",
    RSH.CREATION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    RCV_SHIPMENT_HEADERS RSH
JOIN
    PO_HEADERS_ALL POH ON RSH.PO_HEADER_ID = POH.PO_HEADER_ID
LEFT JOIN
    PER_USERS PU ON RSH.CREATED_BY = PU.USERNAME
LEFT JOIN
    POZ_SUPPLIERS PS ON RSH.VENDOR_ID = PS.VENDOR_ID
WHERE
    RSH.CREATION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

UNION ALL

SELECT
    POH.SEGMENT1 AS "PurchaseOrder",
    CASE RT.TRANSACTION_TYPE
        WHEN 'RECEIVE' THEN 'Goods Received'
        WHEN 'ACCEPT' THEN 'Quality Inspection Performed'
        WHEN 'RETURN TO VENDOR' THEN 'Goods Returned to Vendor'
        WHEN 'DELIVER' THEN 'Services Delivered Confirmed' -- Using DELIVER for services as a proxy
    END AS "ActivityName",
    RT.TRANSACTION_DATE AS "EventTime",
    RT.TRANSACTION_DATE AS "EndTime",
    PU.USERNAME AS "UserName",
    NULL AS "DepartmentName",
    PS.VENDOR_NAME AS "VendorName",
    NULL AS "PurchaseOrderTotalAmount",
    NULL AS "RequestedDeliveryDate",
    NULL AS "ApproverName"
FROM
    RCV_TRANSACTIONS RT
JOIN
    PO_HEADERS_ALL POH ON RT.PO_HEADER_ID = POH.PO_HEADER_ID
LEFT JOIN
    PER_USERS PU ON RT.CREATED_BY = PU.USERNAME
LEFT JOIN
    POZ_SUPPLIERS PS ON POH.VENDOR_ID = PS.VENDOR_ID
WHERE
    RT.TRANSACTION_TYPE IN ('RECEIVE', 'ACCEPT', 'RETURN TO VENDOR', 'DELIVER')
    AND RT.TRANSACTION_DATE BETWEEN TO_DATE(:P_START_DATE, 'YYYY-MM-DD') AND TO_DATE(:P_END_DATE, 'YYYY-MM-DD')

import requests
import json
import csv
import os
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Oracle Fusion instance details
FUSION_URL = class="hl-string">"https://[your_instance_url].fa.em2.oraclecloud.com"
USERNAME = os.environ.get(class="hl-string">"FUSION_USERNAME", class="hl-string">"your_username")
PASSWORD = os.environ.get(class="hl-string">"FUSION_PASSWORD", class="hl-string">"your_password")

class=class="hl-string">"hl-comment"># Date range for the extraction (ISO 8601 format)
START_DATE = class="hl-string">"2023-01-01T00:00:00Z"
END_DATE = class="hl-string">"2023-03-31T23:59:59Z"

class=class="hl-string">"hl-comment"># API settings
PAGE_SIZE = 500
OUTPUT_FILE = class="hl-string">"p2p_event_log.csv"

class=class="hl-string">"hl-comment"># --- Helper Functions ---
def get_api_data(endpoint, params=None):
    class="hl-string">"""Generic function to handle pagination and data retrieval from Fusion REST API."""
    all_items = []
    offset = 0
    while True:
        if params is None:
            params = {}
        params[class="hl-string">'limit'] = PAGE_SIZE
        params[class="hl-string">'offset'] = offset

        try:
            response = requests.get(
                FUSION_URL + endpoint,
                auth=(USERNAME, PASSWORD),
                params=params,
                headers={class="hl-string">'Content-Type': class="hl-string">'application/vnd.oracle.adf.resourceitem+json'}
            )
            response.raise_for_status()  class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            data = response.json()
            items = data.get(class="hl-string">'items', [])
            all_items.extend(items)

            if not data.get(class="hl-string">'hasMore', False):
                break
            offset += PAGE_SIZE
        except requests.exceptions.HTTPError as e:
            print(fclass="hl-string">"HTTP Error fetching {endpoint}: {e}")
            print(fclass="hl-string">"Response content: {e.response.text}")
            break
        except Exception as e:
            print(fclass="hl-string">"An error occurred fetching {endpoint}: {e}")
            break
    return all_items

def add_event(event_log, po_data, activity, timestamp, user, approver=None):
    class="hl-string">"""Helper to format and add an event to the log."""
    if not timestamp:
        return class=class="hl-string">"hl-comment"># Skip events without a timestamp
    
    event = {
        class="hl-string">'PurchaseOrder': po_data.get(class="hl-string">'OrderNumber'),
        class="hl-string">'ActivityName': activity,
        class="hl-string">'EventTime': timestamp,
        class="hl-string">'EndTime': timestamp,
        class="hl-string">'UserName': user,
        class="hl-string">'DepartmentName': po_data.get(class="hl-string">'BillToBU'),
        class="hl-string">'VendorName': po_data.get(class="hl-string">'Supplier'),
        class="hl-string">'PurchaseOrderTotalAmount': po_data.get(class="hl-string">'Total'),
        class="hl-string">'RequestedDeliveryDate': None, class=class="hl-string">"hl-comment"># Often on the line level
        class="hl-string">'ApproverName': approver
    }
    event_log.append(event)

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    event_log = []
    print(class="hl-string">"Starting P2P event log extraction...")

    class=class="hl-string">"hl-comment"># 1. Get Purchase Orders within the date range
    print(fclass="hl-string">"Fetching Purchase Orders created between {START_DATE} and {END_DATE}...")
    po_query = fclass="hl-string">"CreationDate between '{START_DATE}' and '{END_DATE}'"
    purchase_orders = get_api_data(class="hl-string">"/fscmRestApi/resources/11.13.18.05/purchaseOrders", {class="hl-string">'q': po_query, class="hl-string">'fields': class="hl-string">'OrderNumber,CreationDate,POHeaderId,CreatedBy,Supplier,Total,POStatus,LastUpdateDate,BillToBU,VersionNumber,ChangeOrderDescription,lines'})
    print(fclass="hl-string">"Found {len(purchase_orders)} Purchase Orders.")

    unique_requisition_nums = set()
    for po in purchase_orders:
        if po.get(class="hl-string">'lines'):
            for line in po[class="hl-string">'lines']:
                if line.get(class="hl-string">'RequisitionNumber'):
                    unique_requisition_nums.add(line[class="hl-string">'RequisitionNumber'])

    class=class="hl-string">"hl-comment"># 2. Fetch Requisition data in a batch for efficiency
    print(fclass="hl-string">"Fetching data for {len(unique_requisition_nums)} related requisitions...")
    requisitions_data = {}
    if unique_requisition_nums:
        class=class="hl-string">"hl-comment"># API has a limit on URL length, so we may need to batch this for very large sets
        req_query = class="hl-string">" or ".join([fclass="hl-string">"RequisitionNumber='{req_num}'" for req_num in unique_requisition_nums])
        reqs = get_api_data(class="hl-string">"/fscmRestApi/resources/11.13.18.05/purchaseRequisitions", {class="hl-string">'q': req_query})
        for req in reqs:
            requisitions_data[req[class="hl-string">'RequisitionNumber']] = req

    class=class="hl-string">"hl-comment"># 3. Process each Purchase Order to create events
    for i, po in enumerate(purchase_orders):
        print(fclass="hl-string">"Processing PO {i+1}/{len(purchase_orders)}: {po.get('OrderNumber')}")

        class=class="hl-string">"hl-comment"># PO Events
        add_event(event_log, po, class="hl-string">'Purchase Order Created', po.get(class="hl-string">'CreationDate'), po.get(class="hl-string">'CreatedBy'))
        if po.get(class="hl-string">'VersionNumber', 0) > 1:
            add_event(event_log, po, class="hl-string">'Purchase Order Changed', po.get(class="hl-string">'LastUpdateDate'), po.get(class="hl-string">'LastUpdatedBy', po.get(class="hl-string">'CreatedBy')))
        if po.get(class="hl-string">'POStatus') == class="hl-string">'CANCELED':
            add_event(event_log, po, class="hl-string">'Purchase Order Canceled', po.get(class="hl-string">'LastUpdateDate'), po.get(class="hl-string">'LastUpdatedBy', po.get(class="hl-string">'CreatedBy')))
        if po.get(class="hl-string">'POStatus') == class="hl-string">'CLOSED_FOR_RECEIVING' or po.get(class="hl-string">'POStatus') == class="hl-string">'CLOSED':
            add_event(event_log, po, class="hl-string">'Purchase Order Finally Closed', po.get(class="hl-string">'LastUpdateDate'), po.get(class="hl-string">'LastUpdatedBy', po.get(class="hl-string">'CreatedBy')))

        class=class="hl-string">"hl-comment"># Requisition Events (linked to this PO)
        po_req_nums = set()
        if po.get(class="hl-string">'lines'):
            for line in po[class="hl-string">'lines']:
                 if line.get(class="hl-string">'RequisitionNumber'):
                    po_req_nums.add(line[class="hl-string">'RequisitionNumber'])
        
        for req_num in po_req_nums:
            req = requisitions_data.get(req_num)
            if req:
                add_event(event_log, po, class="hl-string">'Purchase Requisition Created', req.get(class="hl-string">'CreationDate'), req.get(class="hl-string">'EnteredBy'))
                if req.get(class="hl-string">'RequisitionStatus') == class="hl-string">'APPROVED':
                     class=class="hl-string">"hl-comment"># Note: This timestamp is the final approval, not an event stream
                     add_event(event_log, po, class="hl-string">'Purchase Requisition Approved', req.get(class="hl-string">'LastUpdateDate'), req.get(class="hl-string">'LastUpdatedBy'))

        class=class="hl-string">"hl-comment"># Approval History Events
        approvals = get_api_data(fclass="hl-string">"/fscmRestApi/resources/11.13.18.05/purchaseOrders/{po['POHeaderId']}/child/approvals")
        is_first_approval = True
        for action in approvals:
            activity = None
            user = action.get(class="hl-string">'PerformedBy')
            approver = user if action.get(class="hl-string">'ActionCode') in [class="hl-string">'APPROVE', class="hl-string">'REJECT'] else None
            if action.get(class="hl-string">'ActionCode') == class="hl-string">'SUBMIT':
                activity = class="hl-string">'Purchase Order Submitted'
            elif action.get(class="hl-string">'ActionCode') == class="hl-string">'APPROVE':
                activity = class="hl-string">'Purchase Order Approved'
                if is_first_approval:
                    add_event(event_log, po, class="hl-string">'Purchase Order Sent to Vendor', action.get(class="hl-string">'ActionDate'), user, approver)
                    is_first_approval = False
            elif action.get(class="hl-string">'ActionCode') == class="hl-string">'REJECT':
                activity = class="hl-string">'Purchase Order Rejected'
            
            if activity:
                add_event(event_log, po, activity, action.get(class="hl-string">'ActionDate'), user, approver)

        class=class="hl-string">"hl-comment"># Receiving Events
        receipts_query = fclass="hl-string">"POHeaderId={po['POHeaderId']}"
        receipts = get_api_data(class="hl-string">"/fscmRestApi/resources/11.13.18.05/receivingReceipts", {class="hl-string">'q': receipts_query})
        for receipt in receipts:
            add_event(event_log, po, class="hl-string">'Goods Receipt Created', receipt.get(class="hl-string">'CreationDate'), receipt.get(class="hl-string">'CreatedBy'))
            class=class="hl-string">"hl-comment"># Lines contain the detailed transactions
            if receipt.get(class="hl-string">'lines'):
                for rcv_line in receipt[class="hl-string">'lines']:
                    tx_type = rcv_line.get(class="hl-string">'TransactionType')
                    tx_time = rcv_line.get(class="hl-string">'TransactionDate')
                    tx_user = receipt.get(class="hl-string">'CreatedBy') class=class="hl-string">"hl-comment"># User is on header
                    activity = None
                    if tx_type == class="hl-string">'RECEIVE':
                        activity = class="hl-string">'Goods Received'
                    elif tx_type == class="hl-string">'INSPECT':
                        activity = class="hl-string">'Quality Inspection Performed'
                    elif tx_type == class="hl-string">'RETURN TO VENDOR':
                        activity = class="hl-string">'Goods Returned to Vendor'
                    elif tx_type == class="hl-string">'MATCH' and po.get(class="hl-string">'OrderTypeCode') == class="hl-string">'SERVICES': class=class="hl-string">"hl-comment"># Approximation for services
                        activity = class="hl-string">'Services Delivered Confirmed'
                    
                    if activity:
                        add_event(event_log, po, activity, tx_time, tx_user)

    class=class="hl-string">"hl-comment"># 4. Write to CSV
    if not event_log:
        print(class="hl-string">"No events generated. Exiting.")
        return

    print(fclass="hl-string">"Writing {len(event_log)} events to {OUTPUT_FILE}...")
    class=class="hl-string">"hl-comment"># Sort events by case ID and timestamp
    event_log.sort(key=lambda x: (x[class="hl-string">'PurchaseOrder'], x[class="hl-string">'EventTime']))

    with open(OUTPUT_FILE, class="hl-string">'w', newline=class="hl-string">'', encoding=class="hl-string">'utf-8') as f:
        writer = csv.DictWriter(f, fieldnames=event_log[0].keys())
        writer.writeheader()
        writer.writerows(event_log)

    print(class="hl-string">"Extraction complete.")

if __name__ == class="hl-string">"__main__":
    main()