Votre modèle de données pour le processus Achat au Paiement - Bon de Commande

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Requisition Created' AS ActivityName,
    rh.created_at AS EventTime,
    u_req.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory, -- Adjust segment_x based on your chart of accounts for category
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM requisition_headers rh
JOIN purchase_order_headers ph ON rh.id = ph.requisition_header_id
LEFT JOIN users u_req ON rh.requester_id = u_req.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1 -- Join to get account info, assuming one per PO for simplicity
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code -- Adjust segment_x based on your chart of accounts for department

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Requisition Approved' AS ActivityName,
    a.updated_at AS EventTime,
    u_app.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM approvals a
JOIN requisition_headers rh ON a.approvable_id = rh.id AND a.approvable_type = 'RequisitionHeader'
JOIN purchase_order_headers ph ON rh.id = ph.requisition_header_id
LEFT JOIN users u_app ON a.approver_id = u_app.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE a.status = 'approved'
-- This subquery gets only the FINAL requisition approval for each PO
AND a.updated_at = (SELECT MAX(a_inner.updated_at) FROM approvals a_inner WHERE a_inner.approvable_id = rh.id AND a_inner.approvable_type = 'RequisitionHeader' AND a_inner.status = 'approved')

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Drafted' AS ActivityName,
    ph.created_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_creator ON ph.created_by_id = u_creator.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Submitted' AS ActivityName,
    ph.submitted_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_creator ON ph.created_by_id = u_creator.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.submitted_at IS NOT NULL

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Approved' AS ActivityName,
    a.updated_at AS EventTime,
    u_app.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM approvals a
JOIN purchase_order_headers ph ON a.approvable_id = ph.id AND a.approvable_type = 'PurchaseOrderHeader'
LEFT JOIN users u_app ON a.approver_id = u_app.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE a.status = 'approved'
AND a.updated_at = (SELECT MAX(a_inner.updated_at) FROM approvals a_inner WHERE a_inner.approvable_id = ph.id AND a_inner.approvable_type = 'PurchaseOrderHeader' AND a_inner.status = 'approved')

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Rejected' AS ActivityName,
    a.updated_at AS EventTime,
    u_app.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM approvals a
JOIN purchase_order_headers ph ON a.approvable_id = ph.id AND a.approvable_type = 'PurchaseOrderHeader'
LEFT JOIN users u_app ON a.approver_id = u_app.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE a.status = 'rejected'

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Changed' AS ActivityName,
    ph.updated_at AS EventTime,
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_updater ON ph.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.version > 1

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Sent to Vendor' AS ActivityName,
    ph.exported_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_creator ON ph.created_by_id = u_creator.id -- Sent by system but triggered by creator/approver
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.exported = 1 AND ph.exported_at IS NOT NULL

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Vendor Acknowledged Order' AS ActivityName,
    ph.supplier_ack_date AS EventTime,
    'System/Vendor' AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.supplier_ack_date IS NOT NULL

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Goods Receipt Initiated' AS ActivityName,
    rh.created_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_headers ph ON rl.order_header_id = ph.id
LEFT JOIN users u_creator ON rh.created_by_id = u_creator.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Goods Receipt Posted' AS ActivityName,
    rh.updated_at AS EventTime, -- Assuming update time reflects final posting
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_headers ph ON rl.order_header_id = ph.id
LEFT JOIN users u_updater ON rh.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE rh.status = 'received'

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Services Confirmation Entered' AS ActivityName,
    rh.updated_at AS EventTime, -- Services are also handled via receipts
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_lines pl_service ON rl.order_line_id = pl_service.id
JOIN purchase_order_headers ph ON pl_service.purchase_order_header_id = ph.id
LEFT JOIN users u_updater ON rh.updated_by_id = u_updater.id
LEFT JOIN accounts acc ON pl_service.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE rh.status = 'received' AND pl_service.line_type = 'service'

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Goods Returned' AS ActivityName,
    rh.updated_at AS EventTime,
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_headers ph ON rl.order_header_id = ph.id
LEFT JOIN users u_updater ON rh.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE rl.quantity < 0 -- Negative quantity indicates a return

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Invoice Received for PO' AS ActivityName,
    ih.created_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM invoice_headers ih
JOIN invoice_lines il ON ih.id = il.invoice_header_id
JOIN purchase_order_headers ph ON il.order_header_id = ph.id
LEFT JOIN users u_creator ON ih.created_by_id = u_creator.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Closed' AS ActivityName,
    ph.updated_at AS EventTime, -- Event time is the last update when status became 'closed'
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_updater ON ph.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.status = 'closed'

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Canceled' AS ActivityName,
    ph.updated_at AS EventTime, -- Event time is the last update when status became 'cancelled'
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_updater ON ph.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.status = 'cancelled'

UNION ALL

-- Placeholder for Quality Inspection Performed, as this is highly custom
-- You must adapt this block based on your specific Coupa configuration (e.g., custom fields, receipt statuses)
SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Quality Inspection Performed' AS ActivityName,
    rh.updated_at AS EventTime, -- Placeholder: use a relevant timestamp from your quality process
    '[Configure based on your system]' AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_headers ph ON rl.order_header_id = ph.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE rh.status = '[Your Quality Inspection Status]' -- Replace with your specific status, e.g., 'pending_qc', 'qc_passed'
AND 1=0; -- Disable this block by default until configured

import requests
import pandas as pd
import os
from datetime import datetime, timezone

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Set your Coupa instance URL (e.g., class="hl-string">'https://mycompany.coupahost.com')
COUPA_INSTANCE_URL = class="hl-string">'[YOUR_COUPA_INSTANCE_URL]'
class=class="hl-string">"hl-comment"># It is recommended to set the API key as an environment variable for security
API_KEY = os.environ.get(class="hl-string">'COUPA_API_KEY')

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

class=class="hl-string">"hl-comment"># --- Script ---

HEADERS = {
    class="hl-string">'Accept': class="hl-string">'application/json',
    class="hl-string">'X-COUPA-API-KEY': API_KEY
}

LAST_DATA_UPDATE = datetime.now(timezone.utc).isoformat()

def get_paginated_data(endpoint, params=None):
    class="hl-string">"""Fetches all pages of data from a Coupa API endpoint."""
    all_data = []
    url = fclass="hl-string">"{COUPA_INSTANCE_URL}{endpoint}"
    
    if params is None:
        params = {}
    params[class="hl-string">'limit'] = 50
    offset = 0

    while True:
        params[class="hl-string">'offset'] = offset
        try:
            response = requests.get(url, headers=HEADERS, params=params)
            response.raise_for_status()
            data = response.json()
            if not data:
                break
            all_data.extend(data)
            offset += len(data)
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"Error fetching data from {url}: {e}")
            break
    return all_data

def get_single_record(endpoint):
    class="hl-string">"""Fetches a single record from a Coupa API endpoint."""
    url = fclass="hl-string">"{COUPA_INSTANCE_URL}{endpoint}"
    try:
        response = requests.get(url, headers=HEADERS)
        response.raise_for_status()
        return response.json()
    except requests.exceptions.RequestException as e:
        print(fclass="hl-string">"Error fetching single record from {url}: {e}")
        return None

def main():
    class="hl-string">"""Main function to extract, transform, and save the event log."""
    print(class="hl-string">"Starting Coupa Purchase to Pay extraction...")
    events = []

    class=class="hl-string">"hl-comment"># 1. Fetch all Purchase Orders in the date range
    po_params = {
        class="hl-string">'created_at[gt_or_eq]': START_DATE,
        class="hl-string">'created_at[lt_or_eq]': END_DATE,
        class="hl-string">'return_object': class="hl-string">'deep'
    }
    purchase_orders = get_paginated_data(class="hl-string">'/api/purchase_orders', params=po_params)
    print(fclass="hl-string">"Found {len(purchase_orders)} purchase orders.")

    class=class="hl-string">"hl-comment"># 2. Process each Purchase Order to create events
    for po in purchase_orders:
        po_num = po.get(class="hl-string">'po-number')
        if not po_num:
            continue

        base_event = {
            class="hl-string">'PurchaseOrderNumber': po_num,
            class="hl-string">'SourceSystem': class="hl-string">'Coupa',
            class="hl-string">'LastDataUpdate': LAST_DATA_UPDATE,
            class="hl-string">'VendorName': po.get(class="hl-string">'supplier', {}).get(class="hl-string">'name'),
            class="hl-string">'TotalOrderAmount': po.get(class="hl-string">'total'),
            class="hl-string">'Department': po.get(class="hl-string">'account-allocations', [{}])[0].get(class="hl-string">'account', {}).get(class="hl-string">'segment-1'), class=class="hl-string">"hl-comment"># Example for first line item department
            class="hl-string">'PurchaseCategory': po.get(class="hl-string">'account-allocations', [{}])[0].get(class="hl-string">'account', {}).get(class="hl-string">'segment-2') class=class="hl-string">"hl-comment"># Example for category
        }

        class=class="hl-string">"hl-comment"># PO Drafted (assumed at creation time)
        events.append({
            **base_event,
            class="hl-string">'ActivityName': class="hl-string">'Purchase Order Drafted',
            class="hl-string">'EventTime': po.get(class="hl-string">'created-at'),
            class="hl-string">'User': po.get(class="hl-string">'created-by', {}).get(class="hl-string">'fullname')
        })

        class=class="hl-string">"hl-comment"># PO Submitted, Approved, Rejected
        if po.get(class="hl-string">'approvals'):
            for approval in po.get(class="hl-string">'approvals'):
                if approval.get(class="hl-string">'status') == class="hl-string">'approved':
                    events.append({
                        **base_event,
                        class="hl-string">'ActivityName': class="hl-string">'Purchase Order Approved',
                        class="hl-string">'EventTime': approval.get(class="hl-string">'updated-at'),
                        class="hl-string">'User': approval.get(class="hl-string">'approver', {}).get(class="hl-string">'fullname')
                    })
                elif approval.get(class="hl-string">'status') == class="hl-string">'rejected':
                    events.append({
                        **base_event,
                        class="hl-string">'ActivityName': class="hl-string">'Purchase Order Rejected',
                        class="hl-string">'EventTime': approval.get(class="hl-string">'updated-at'),
                        class="hl-string">'User': approval.get(class="hl-string">'approver', {}).get(class="hl-string">'fullname')
                    })
            class=class="hl-string">"hl-comment"># Assume first approval creation marks submission
            if po.get(class="hl-string">'approvals'):
                 events.append({
                    **base_event,
                    class="hl-string">'ActivityName': class="hl-string">'Purchase Order Submitted',
                    class="hl-string">'EventTime': po.get(class="hl-string">'submitted-at'),
                    class="hl-string">'User': po.get(class="hl-string">'submitted-by', {}).get(class="hl-string">'fullname')
                })

        class=class="hl-string">"hl-comment"># PO Changed (from versions)
        versions = get_paginated_data(fclass="hl-string">"/api/purchase_orders/{po['id']}/versions")
        if len(versions) > 1:
            for version in versions[1:]: class=class="hl-string">"hl-comment"># Skip the first version (creation)
                 events.append({
                    **base_event,
                    class="hl-string">'ActivityName': class="hl-string">'Purchase Order Changed',
                    class="hl-string">'EventTime': version.get(class="hl-string">'created-at'),
                    class="hl-string">'User': version.get(class="hl-string">'created-by', {}).get(class="hl-string">'fullname')
                })

        class=class="hl-string">"hl-comment"># PO Sent to Vendor
        if po.get(class="hl-string">'exported') and po.get(class="hl-string">'last-exported-at'):
            events.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Purchase Order Sent to Vendor',
                class="hl-string">'EventTime': po.get(class="hl-string">'last-exported-at'),
                class="hl-string">'User': class="hl-string">'System'
            })

        class=class="hl-string">"hl-comment"># Vendor Acknowledged Order
        if po.get(class="hl-string">'supplier-ack-at'):
            events.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Vendor Acknowledged Order',
                class="hl-string">'EventTime': po.get(class="hl-string">'supplier-ack-at'),
                class="hl-string">'User': po.get(class="hl-string">'supplier-ack-by', {}).get(class="hl-string">'fullname') or class="hl-string">'Supplier Contact'
            })

        class=class="hl-string">"hl-comment"># PO Canceled / Closed
        if po.get(class="hl-string">'status') == class="hl-string">'closed':
            events.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Purchase Order Closed',
                class="hl-string">'EventTime': po.get(class="hl-string">'updated-at'),
                class="hl-string">'User': po.get(class="hl-string">'updated-by', {}).get(class="hl-string">'fullname')
            })
        elif po.get(class="hl-string">'status') == class="hl-string">'canceled':
            events.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Purchase Order Canceled',
                class="hl-string">'EventTime': po.get(class="hl-string">'updated-at'),
                class="hl-string">'User': po.get(class="hl-string">'updated-by', {}).get(class="hl-string">'fullname')
            })

        class=class="hl-string">"hl-comment"># Requisition Events
        if po.get(class="hl-string">'requisition-header-id'):
            req = get_single_record(fclass="hl-string">"/api/purchase_requisitions/{po['requisition-header-id']}")
            if req:
                events.append({
                    **base_event,
                    class="hl-string">'ActivityName': class="hl-string">'Purchase Requisition Created',
                    class="hl-string">'EventTime': req.get(class="hl-string">'created-at'),
                    class="hl-string">'User': req.get(class="hl-string">'created-by', {}).get(class="hl-string">'fullname')
                })
                if req.get(class="hl-string">'status') == class="hl-string">'ordered' and req.get(class="hl-string">'approved-at'):
                    events.append({
                        **base_event,
                        class="hl-string">'ActivityName': class="hl-string">'Purchase Requisition Approved',
                        class="hl-string">'EventTime': req.get(class="hl-string">'approved-at'),
                        class="hl-string">'User': class="hl-string">'Approval Workflow' class=class="hl-string">"hl-comment"># Approver not easily available on req header
                    })

        class=class="hl-string">"hl-comment"># Receipt and Return Events
        receipts = get_paginated_data(class="hl-string">'/api/receipts', params={class="hl-string">'purchase_order_header_id': po[class="hl-string">'id']})
        for receipt in receipts:
            receipt_user = receipt.get(class="hl-string">'created-by', {}).get(class="hl-string">'fullname')
            events.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Goods Receipt Initiated',
                class="hl-string">'EventTime': receipt.get(class="hl-string">'created-at'),
                class="hl-string">'User': receipt_user
            })
            events.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Goods Receipt Posted',
                class="hl-string">'EventTime': receipt.get(class="hl-string">'updated-at'), class=class="hl-string">"hl-comment"># Assuming final update is posting
                class="hl-string">'User': receipt_user
            })
            class=class="hl-string">"hl-comment"># Check for returns (negative quantity on lines)
            for line in receipt.get(class="hl-string">'receipt-lines', []):
                if float(line.get(class="hl-string">'qty', 0)) < 0:
                    events.append({
                        **base_event,
                        class="hl-string">'ActivityName': class="hl-string">'Goods Returned',
                        class="hl-string">'EventTime': receipt.get(class="hl-string">'updated-at'),
                        class="hl-string">'User': receipt_user
                    })
                    break class=class="hl-string">"hl-comment"># Only one return event per receipt document
            class=class="hl-string">"hl-comment"># Note: Service Confirmation, Quality Inspection might need custom logic

        class=class="hl-string">"hl-comment"># Invoice Events
        invoices = get_paginated_data(class="hl-string">'/api/invoices', params={class="hl-string">'purchase-order-header-id': po[class="hl-string">'id']})
        for invoice in invoices:
            events.append({
                **base_event,
                class="hl-string">'ActivityName': class="hl-string">'Invoice Received for PO',
                class="hl-string">'EventTime': invoice.get(class="hl-string">'created-at'),
                class="hl-string">'User': invoice.get(class="hl-string">'created-by', {}).get(class="hl-string">'fullname')
            })

    if not events:
        print(class="hl-string">"No events were generated. Please check your date range and API permissions.")
        return

    class=class="hl-string">"hl-comment"># 3. Create and save DataFrame
    df = pd.DataFrame(events)
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'])
    df = df.sort_values(by=[class="hl-string">'PurchaseOrderNumber', class="hl-string">'EventTime'])
    
    class=class="hl-string">"hl-comment"># Reorder columns for clarity
    column_order = [
        class="hl-string">'PurchaseOrderNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'User', class="hl-string">'Department', class="hl-string">'VendorName', class="hl-string">'TotalOrderAmount', class="hl-string">'PurchaseCategory'
    ]
    df = df[column_order]
    
    df.to_csv(class="hl-string">'coupa_p2p_event_log.csv', index=False)
    print(class="hl-string">"Extraction complete. Event log saved to 'coupa_p2p_event_log.csv'.")

if __name__ == class="hl-string">'__main__':
    if not all([COUPA_INSTANCE_URL != class="hl-string">'[YOUR_COUPA_INSTANCE_URL]', API_KEY]):
        print(class="hl-string">"ERROR: Please configure COUPA_INSTANCE_URL and set the COUPA_API_KEY environment variable.")
    else:
        main()

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Requisition Created' AS ActivityName,
    rh.created_at AS EventTime,
    u_req.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory, -- Adjust segment_x based on your chart of accounts for category
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM requisition_headers rh
JOIN purchase_order_headers ph ON rh.id = ph.requisition_header_id
LEFT JOIN users u_req ON rh.requester_id = u_req.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1 -- Join to get account info, assuming one per PO for simplicity
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code -- Adjust segment_x based on your chart of accounts for department

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Requisition Approved' AS ActivityName,
    a.updated_at AS EventTime,
    u_app.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM approvals a
JOIN requisition_headers rh ON a.approvable_id = rh.id AND a.approvable_type = 'RequisitionHeader'
JOIN purchase_order_headers ph ON rh.id = ph.requisition_header_id
LEFT JOIN users u_app ON a.approver_id = u_app.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE a.status = 'approved'
-- This subquery gets only the FINAL requisition approval for each PO
AND a.updated_at = (SELECT MAX(a_inner.updated_at) FROM approvals a_inner WHERE a_inner.approvable_id = rh.id AND a_inner.approvable_type = 'RequisitionHeader' AND a_inner.status = 'approved')

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Drafted' AS ActivityName,
    ph.created_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_creator ON ph.created_by_id = u_creator.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Submitted' AS ActivityName,
    ph.submitted_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_creator ON ph.created_by_id = u_creator.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.submitted_at IS NOT NULL

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Approved' AS ActivityName,
    a.updated_at AS EventTime,
    u_app.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM approvals a
JOIN purchase_order_headers ph ON a.approvable_id = ph.id AND a.approvable_type = 'PurchaseOrderHeader'
LEFT JOIN users u_app ON a.approver_id = u_app.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE a.status = 'approved'
AND a.updated_at = (SELECT MAX(a_inner.updated_at) FROM approvals a_inner WHERE a_inner.approvable_id = ph.id AND a_inner.approvable_type = 'PurchaseOrderHeader' AND a_inner.status = 'approved')

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Rejected' AS ActivityName,
    a.updated_at AS EventTime,
    u_app.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM approvals a
JOIN purchase_order_headers ph ON a.approvable_id = ph.id AND a.approvable_type = 'PurchaseOrderHeader'
LEFT JOIN users u_app ON a.approver_id = u_app.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE a.status = 'rejected'

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Changed' AS ActivityName,
    ph.updated_at AS EventTime,
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_updater ON ph.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.version > 1

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Sent to Vendor' AS ActivityName,
    ph.exported_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_creator ON ph.created_by_id = u_creator.id -- Sent by system but triggered by creator/approver
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.exported = 1 AND ph.exported_at IS NOT NULL

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Vendor Acknowledged Order' AS ActivityName,
    ph.supplier_ack_date AS EventTime,
    'System/Vendor' AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.supplier_ack_date IS NOT NULL

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Goods Receipt Initiated' AS ActivityName,
    rh.created_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_headers ph ON rl.order_header_id = ph.id
LEFT JOIN users u_creator ON rh.created_by_id = u_creator.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Goods Receipt Posted' AS ActivityName,
    rh.updated_at AS EventTime, -- Assuming update time reflects final posting
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_headers ph ON rl.order_header_id = ph.id
LEFT JOIN users u_updater ON rh.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE rh.status = 'received'

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Services Confirmation Entered' AS ActivityName,
    rh.updated_at AS EventTime, -- Services are also handled via receipts
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_lines pl_service ON rl.order_line_id = pl_service.id
JOIN purchase_order_headers ph ON pl_service.purchase_order_header_id = ph.id
LEFT JOIN users u_updater ON rh.updated_by_id = u_updater.id
LEFT JOIN accounts acc ON pl_service.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE rh.status = 'received' AND pl_service.line_type = 'service'

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Goods Returned' AS ActivityName,
    rh.updated_at AS EventTime,
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_headers ph ON rl.order_header_id = ph.id
LEFT JOIN users u_updater ON rh.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE rl.quantity < 0 -- Negative quantity indicates a return

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Invoice Received for PO' AS ActivityName,
    ih.created_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM invoice_headers ih
JOIN invoice_lines il ON ih.id = il.invoice_header_id
JOIN purchase_order_headers ph ON il.order_header_id = ph.id
LEFT JOIN users u_creator ON ih.created_by_id = u_creator.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Closed' AS ActivityName,
    ph.updated_at AS EventTime, -- Event time is the last update when status became 'closed'
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_updater ON ph.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.status = 'closed'

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Canceled' AS ActivityName,
    ph.updated_at AS EventTime, -- Event time is the last update when status became 'cancelled'
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_updater ON ph.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.status = 'cancelled'

UNION ALL

-- Placeholder for Quality Inspection Performed, as this is highly custom
-- You must adapt this block based on your specific Coupa configuration (e.g., custom fields, receipt statuses)
SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Quality Inspection Performed' AS ActivityName,
    rh.updated_at AS EventTime, -- Placeholder: use a relevant timestamp from your quality process
    '[Configure based on your system]' AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_headers ph ON rl.order_header_id = ph.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE rh.status = '[Your Quality Inspection Status]' -- Replace with your specific status, e.g., 'pending_qc', 'qc_passed'
AND 1=0; -- Disable this block by default until configured

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Requisition Created' AS ActivityName,
    rh.created_at AS EventTime,
    u_req.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory, -- Adjust segment_x based on your chart of accounts for category
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM requisition_headers rh
JOIN purchase_order_headers ph ON rh.id = ph.requisition_header_id
LEFT JOIN users u_req ON rh.requester_id = u_req.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1 -- Join to get account info, assuming one per PO for simplicity
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code -- Adjust segment_x based on your chart of accounts for department

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Requisition Approved' AS ActivityName,
    a.updated_at AS EventTime,
    u_app.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM approvals a
JOIN requisition_headers rh ON a.approvable_id = rh.id AND a.approvable_type = 'RequisitionHeader'
JOIN purchase_order_headers ph ON rh.id = ph.requisition_header_id
LEFT JOIN users u_app ON a.approver_id = u_app.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE a.status = 'approved'
-- This subquery gets only the FINAL requisition approval for each PO
AND a.updated_at = (SELECT MAX(a_inner.updated_at) FROM approvals a_inner WHERE a_inner.approvable_id = rh.id AND a_inner.approvable_type = 'RequisitionHeader' AND a_inner.status = 'approved')

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Drafted' AS ActivityName,
    ph.created_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_creator ON ph.created_by_id = u_creator.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Submitted' AS ActivityName,
    ph.submitted_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_creator ON ph.created_by_id = u_creator.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.submitted_at IS NOT NULL

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Approved' AS ActivityName,
    a.updated_at AS EventTime,
    u_app.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM approvals a
JOIN purchase_order_headers ph ON a.approvable_id = ph.id AND a.approvable_type = 'PurchaseOrderHeader'
LEFT JOIN users u_app ON a.approver_id = u_app.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE a.status = 'approved'
AND a.updated_at = (SELECT MAX(a_inner.updated_at) FROM approvals a_inner WHERE a_inner.approvable_id = ph.id AND a_inner.approvable_type = 'PurchaseOrderHeader' AND a_inner.status = 'approved')

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Rejected' AS ActivityName,
    a.updated_at AS EventTime,
    u_app.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM approvals a
JOIN purchase_order_headers ph ON a.approvable_id = ph.id AND a.approvable_type = 'PurchaseOrderHeader'
LEFT JOIN users u_app ON a.approver_id = u_app.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE a.status = 'rejected'

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Changed' AS ActivityName,
    ph.updated_at AS EventTime,
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_updater ON ph.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.version > 1

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Sent to Vendor' AS ActivityName,
    ph.exported_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_creator ON ph.created_by_id = u_creator.id -- Sent by system but triggered by creator/approver
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.exported = 1 AND ph.exported_at IS NOT NULL

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Vendor Acknowledged Order' AS ActivityName,
    ph.supplier_ack_date AS EventTime,
    'System/Vendor' AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.supplier_ack_date IS NOT NULL

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Goods Receipt Initiated' AS ActivityName,
    rh.created_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_headers ph ON rl.order_header_id = ph.id
LEFT JOIN users u_creator ON rh.created_by_id = u_creator.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Goods Receipt Posted' AS ActivityName,
    rh.updated_at AS EventTime, -- Assuming update time reflects final posting
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_headers ph ON rl.order_header_id = ph.id
LEFT JOIN users u_updater ON rh.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE rh.status = 'received'

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Services Confirmation Entered' AS ActivityName,
    rh.updated_at AS EventTime, -- Services are also handled via receipts
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_lines pl_service ON rl.order_line_id = pl_service.id
JOIN purchase_order_headers ph ON pl_service.purchase_order_header_id = ph.id
LEFT JOIN users u_updater ON rh.updated_by_id = u_updater.id
LEFT JOIN accounts acc ON pl_service.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE rh.status = 'received' AND pl_service.line_type = 'service'

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Goods Returned' AS ActivityName,
    rh.updated_at AS EventTime,
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_headers ph ON rl.order_header_id = ph.id
LEFT JOIN users u_updater ON rh.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE rl.quantity < 0 -- Negative quantity indicates a return

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Invoice Received for PO' AS ActivityName,
    ih.created_at AS EventTime,
    u_creator.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM invoice_headers ih
JOIN invoice_lines il ON ih.id = il.invoice_header_id
JOIN purchase_order_headers ph ON il.order_header_id = ph.id
LEFT JOIN users u_creator ON ih.created_by_id = u_creator.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Closed' AS ActivityName,
    ph.updated_at AS EventTime, -- Event time is the last update when status became 'closed'
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_updater ON ph.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.status = 'closed'

UNION ALL

SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Purchase Order Canceled' AS ActivityName,
    ph.updated_at AS EventTime, -- Event time is the last update when status became 'cancelled'
    u_updater.full_name AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM purchase_order_headers ph
LEFT JOIN users u_updater ON ph.updated_by_id = u_updater.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE ph.status = 'cancelled'

UNION ALL

-- Placeholder for Quality Inspection Performed, as this is highly custom
-- You must adapt this block based on your specific Coupa configuration (e.g., custom fields, receipt statuses)
SELECT
    ph.po_number AS PurchaseOrderNumber,
    'Quality Inspection Performed' AS ActivityName,
    rh.updated_at AS EventTime, -- Placeholder: use a relevant timestamp from your quality process
    '[Configure based on your system]' AS User,
    d.name AS Department,
    ph.supplier_name AS VendorName,
    ph.total AS TotalOrderAmount,
    acc.segment_1 AS PurchaseCategory,
    'Coupa' AS SourceSystem,
    CURRENT_TIMESTAMP AS LastDataUpdate
FROM receipt_headers rh
JOIN receipt_lines rl ON rh.id = rl.receipt_header_id
JOIN purchase_order_headers ph ON rl.order_header_id = ph.id
LEFT JOIN purchase_order_lines pl ON ph.id = pl.purchase_order_header_id AND pl.line_num = 1
LEFT JOIN accounts acc ON pl.account_id = acc.id
LEFT JOIN departments d ON acc.segment_2 = d.code
WHERE rh.status = '[Your Quality Inspection Status]' -- Replace with your specific status, e.g., 'pending_qc', 'qc_passed'
AND 1=0; -- Disable this block by default until configured