เทมเพลตข้อมูลกระบวนการจัดซื้อจนถึงชำระเงินสำหรับใบสั่งซื้อ

import pandas as pd
import numpy as np
from datetime import datetime

# --- Configuration ---
# This script assumes you have unzipped the export package into a folder named 'd365_export'
# Update file paths as needed.

SOURCE_SYSTEM = 'Microsoft Dynamics 365'
LAST_DATA_UPDATE = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

# File paths to the CSV files from the D365 export
file_paths = {
    'req_lines': './d365_export/Purchase requisition lines V2.csv',
    'po_headers': './d365_export/Purchase order headers V2.csv',
    'po_confirmations': './d365_export/Purchase order confirmations V2.csv',
    'po_versions': './d365_export/Purchase order header versions.csv',
    'receipts': './d365_export/Product receipt headers.csv',
    'quality': './d365_export/Quality orders.csv',
    'returns': './d365_export/Return order headers.csv',
    'invoices': './d365_export/Vendor invoice journal headers.csv'
}

def load_data(path):
    try:
        return pd.read_csv(path, low_memory=False)
    except FileNotFoundError:
        print(f'Warning: File not found at {path}. Skipping...')
        return None

# --- Main Transformation Logic ---
event_log_frames = []

# Load all data sources
dataframes = {name: load_data(path) for name, path in file_paths.items()}

# Activity: Purchase Requisition Created & Approved
if dataframes['req_lines'] is not None:
    req = dataframes['req_lines'].copy()
    req = req[req['PurchaseOrderNumber'].notna() & (req['PurchaseOrderNumber'] != '')]
    
    # Created event
    created = req[['RequisitionNumber', 'CreatedDateTime', 'RequestedBy', 'PurchaseOrderNumber']].copy()
    created.rename(columns={'RequisitionNumber': 'CaseID_temp', 'CreatedDateTime': 'EventTime', 'RequestedBy': 'UserName'}, inplace=True)
    created['ActivityName'] = 'Purchase Requisition Created'
    event_log_frames.append(created)

    # Approved event (using status change)
    approved = req[req['RequisitionStatus'].isin(['Approved', 'Closed'])].copy()
    approved = approved[['RequisitionNumber', 'ModifiedDateTime', 'RequestedBy', 'PurchaseOrderNumber']]
    approved.rename(columns={'RequisitionNumber': 'CaseID_temp', 'ModifiedDateTime': 'EventTime', 'RequestedBy': 'UserName'}, inplace=True)
    approved['ActivityName'] = 'Purchase Requisition Approved'
    event_log_frames.append(approved)

# Activities from Purchase Order Headers
if dataframes['po_headers'] is not None:
    po = dataframes['po_headers'].copy()
    po_base = po[['PurchaseOrderNumber', 'VendorAccount', 'VendorName', 'TotalAmount', 'PurchaseOrderStatus', 'RequestedDeliveryDate']].copy()
    po_base.rename(columns={'TotalAmount': 'PurchaseOrderTotalAmount'}, inplace=True)

    # Created Event
    created = po[['PurchaseOrderNumber', 'CreatedDateTime', 'CreatedBy']].copy()
    created.rename(columns={'CreatedDateTime': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    created['ActivityName'] = 'Purchase Order Created'
    event_log_frames.append(created)

    # Submitted & Approved Events (approximated by status changes)
    approved = po[po['DocumentApprovalStatus'].isin(['Approved', 'Confirmed', 'Finalized'])].copy()
    approved = approved[['PurchaseOrderNumber', 'ModifiedDateTime', 'ModifiedBy']]
    approved.rename(columns={'ModifiedDateTime': 'EventTime', 'ModifiedBy': 'UserName'}, inplace=True)
    # Logic: Submitted is often the first change from Draft, Approved is the final state. We use ModifiedDateTime for both as a proxy.
    submitted = approved.copy()
    submitted['ActivityName'] = 'Purchase Order Submitted for Approval'
    approved['ActivityName'] = 'Purchase Order Approved'
    event_log_frames.append(submitted)
    event_log_frames.append(approved)

    # Completed & Canceled Events
    completed = po[po['PurchaseOrderStatus'].isin(['Invoiced', 'Received'])].copy()
    completed = completed[['PurchaseOrderNumber', 'ModifiedDateTime', 'ModifiedBy']]
    completed.rename(columns={'ModifiedDateTime': 'EventTime', 'ModifiedBy': 'UserName'}, inplace=True)
    completed['ActivityName'] = 'Purchase Order Completed'
    event_log_frames.append(completed)

    canceled = po[po['PurchaseOrderStatus'] == 'Canceled'].copy()
    canceled = canceled[['PurchaseOrderNumber', 'ModifiedDateTime', 'ModifiedBy']]
    canceled.rename(columns={'ModifiedDateTime': 'EventTime', 'ModifiedBy': 'UserName'}, inplace=True)
    canceled['ActivityName'] = 'Purchase Order Canceled'
    event_log_frames.append(canceled)

# Activity: Purchase Order Sent to Vendor (Confirmation)
if dataframes['po_confirmations'] is not None:
    confirm = dataframes['po_confirmations'].copy()
    confirm = confirm[['PurchaseOrderNumber', 'ConfirmationDate', 'CreatedBy']].copy()
    confirm.rename(columns={'ConfirmationDate': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    confirm['ActivityName'] = 'Purchase Order Sent to Vendor'
    event_log_frames.append(confirm)

# Activity: Purchase Order Changed
if dataframes['po_versions'] is not None:
    versions = dataframes['po_versions'].copy()
    versions = versions[['PurchaseOrderNumber', 'CreatedDateTime', 'CreatedBy']].copy()
    # We only care about changes after the first version
    versions = versions.sort_values('CreatedDateTime').groupby('PurchaseOrderNumber').tail(-1)
    versions.rename(columns={'CreatedDateTime': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    versions['ActivityName'] = 'Purchase Order Changed'
    event_log_frames.append(versions)

# Activity: Goods Receipt Posted
if dataframes['receipts'] is not None:
    receipts = dataframes['receipts'].copy()
    receipts = receipts[['PurchaseOrderNumber', 'ReceiptDate', 'CreatedBy']].copy()
    receipts.rename(columns={'ReceiptDate': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    receipts['ActivityName'] = 'Goods Receipt Posted'
    event_log_frames.append(receipts)

# Activity: Quality Inspection Performed
if dataframes['quality'] is not None:
    quality = dataframes['quality'][dataframes['quality']['ReferenceType'] == 'Purchase'].copy()
    quality = quality[['InventoryLotID', 'AcceptedDateTime', 'CreatedBy']].copy()
    # Note: Linking Quality Order to PO may require InventoryLotID or custom logic
    quality.rename(columns={'InventoryLotID': 'PurchaseOrderNumber', 'AcceptedDateTime': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    quality['ActivityName'] = 'Quality Inspection Performed'
    event_log_frames.append(quality)

# Activity: Goods Returned to Vendor
if dataframes['returns'] is not None:
    returns = dataframes['returns'].copy()
    returns = returns[['PurchaseOrderNumber', 'CreatedDateTime', 'CreatedBy']].copy()
    returns.rename(columns={'CreatedDateTime': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    returns['ActivityName'] = 'Goods Returned to Vendor'
    event_log_frames.append(returns)

# Activity: Purchase Order Invoiced
if dataframes['invoices'] is not None:
    invoices = dataframes['invoices'].copy()
    invoices = invoices[['PurchaseOrderNumber', 'InvoiceDate', 'CreatedBy']].copy()
    invoices.rename(columns={'InvoiceDate': 'EventTime', 'CreatedBy': 'UserName'}, inplace=True)
    invoices['ActivityName'] = 'Purchase Order Invoiced'
    event_log_frames.append(invoices)

# --- Final Assembly ---
if event_log_frames:
    event_log = pd.concat(event_log_frames, ignore_index=True)

    # Clean up and merge attributes
    event_log = event_log[event_log['PurchaseOrderNumber'].notna()]
    if dataframes['po_headers'] is not None:
      po_base = dataframes['po_headers'][['PurchaseOrderNumber', 'VendorName', 'PurchaseOrderTotalAmount', 'PurchaseOrderStatus', 'RequestedDeliveryDate']].copy()
      event_log = pd.merge(event_log, po_base, on='PurchaseOrderNumber', how='left')

    # Add constant columns
    event_log['SourceSystem'] = SOURCE_SYSTEM
    event_log['LastDataUpdate'] = LAST_DATA_UPDATE
    
    # Ensure data types are correct
    event_log['EventTime'] = pd.to_datetime(event_log['EventTime'], errors='coerce')
    event_log.dropna(subset=['EventTime'], inplace=True)
    
    # Final column selection and ordering
    final_columns = [
        'PurchaseOrderNumber', 'ActivityName', 'EventTime', 'SourceSystem', 'LastDataUpdate',
        'UserName', 'VendorName', 'PurchaseOrderTotalAmount', 'PurchaseOrderStatus', 'RequestedDeliveryDate'
    ]
    # Select only columns that exist in the dataframe to avoid errors
    event_log = event_log[[col for col in final_columns if col in event_log.columns]]
    
    event_log.sort_values(by=['PurchaseOrderNumber', 'EventTime'], inplace=True)

    # Save to CSV
    event_log.to_csv('p2p_event_log.csv', index=False)
    print('Event log saved to p2p_event_log.csv')
else:
    print('No data processed. Event log not created.')

DECLARE @StartDate AS DATETIME = '2023-01-01';
DECLARE @EndDate AS DATETIME = '2024-01-01';
DECLARE @DATAAREAID AS NVARCHAR(4) = '[YourCompanyID]'; -- Example: 'USMF'

WITH PO_Base AS (
    SELECT
        PT.PURCHID AS PurchaseOrderNumber,
        PT.CREATEDDATETIME,
        PT.MODIFIEDDATETIME,
        PT.DOCUMENTSTATE,
        PT.PURCHSTATUS,
        PT.DELIVERYDATE AS RequestedDeliveryDate,
        PT.INVOICEACCOUNT,
        VT.NAME AS VendorName,
        PT.createdByUser AS UserName, -- Using createdByUser as a general user, can be changed per event
        PT.DATAAREAID,
        PT.RECID
    FROM PURCHTABLE PT
    LEFT JOIN VENDTABLE VT ON PT.INVOICEACCOUNT = VT.ACCOUNTNUM AND PT.DATAAREAID = VT.DATAAREAID
    WHERE PT.DATAAREAID = @DATAAREAID AND PT.CREATEDDATETIME BETWEEN @StartDate AND @EndDate
)
-- 1. Purchase Requisition Created
SELECT
    PL.PURCHID AS PurchaseOrderNumber,
    'Purchase Requisition Created' AS ActivityName,
    PRT.CREATEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    PRT.CREATEDBY AS UserName,
    PB.VendorName,
    NULL AS PurchaseOrderTotalAmount, -- Amount not yet known on PO
    NULL AS PurchaseOrderStatus,
    PB.RequestedDeliveryDate
FROM PURCHREQTABLE PRT
JOIN PURCHREQLINE PRL ON PRT.RECID = PRL.PURCHREQTABLE
JOIN PURCHLINE PL ON PRL.RECID = PL.PURCHREQLINEREFID
JOIN PO_Base PB ON PL.PURCHID = PB.PurchaseOrderNumber AND PL.DATAAREAID = PB.DATAAREAID
WHERE PRT.CREATEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 2. Purchase Requisition Approved
-- Note: This uses the requisition's status change to approved. A more precise method involves workflow tables.
SELECT
    PL.PURCHID AS PurchaseOrderNumber,
    'Purchase Requisition Approved' AS ActivityName,
    PRT.MODIFIEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    PRT.MODIFIEDBY AS UserName,
    PB.VendorName,
    NULL AS PurchaseOrderTotalAmount,
    NULL AS PurchaseOrderStatus,
    PB.RequestedDeliveryDate
FROM PURCHREQTABLE PRT
JOIN PURCHREQLINE PRL ON PRT.RECID = PRL.PURCHREQTABLE
JOIN PURCHLINE PL ON PRL.RECID = PL.PURCHREQLINEREFID
JOIN PO_Base PB ON PL.PURCHID = PB.PurchaseOrderNumber AND PL.DATAAREAID = PB.DATAAREAID
WHERE PRT.STATUS = 2 -- Status for 'Approved'

UNION ALL

-- 3. Purchase Order Created
SELECT
    PurchaseOrderNumber,
    'Purchase Order Created' AS ActivityName,
    CREATEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base

UNION ALL

-- 4. Purchase Order Submitted for Approval
-- Note: This is approximated by creation if workflow is immediate. For detailed tracking, join with WORKFLOWTRACKINGSTATUSTABLE.
SELECT
    PurchaseOrderNumber,
    'Purchase Order Submitted for Approval' AS ActivityName,
    CREATEDDATETIME AS EventTime, -- Approximated as creation time
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base
WHERE DOCUMENTSTATE > 1 -- Any state after Draft

UNION ALL

-- 5. Purchase Order Approved
SELECT
    PurchaseOrderNumber,
    'Purchase Order Approved' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base
WHERE DOCUMENTSTATE = 2 -- Approved

UNION ALL

-- 6. Purchase Order Sent to Vendor (Confirmed)
SELECT
    PCJ.PURCHID AS PurchaseOrderNumber,
    'Purchase Order Sent to Vendor' AS ActivityName,
    PCJ.CONFIRMATIONDATE AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    PCJ.CREATEDBY AS UserName,
    PB.VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PCJ.PURCHID) AS PurchaseOrderTotalAmount,
    CAST(PB.DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    PB.RequestedDeliveryDate
FROM PURCHCONFIRMATIONJOUR PCJ
JOIN PO_Base PB ON PCJ.PURCHID = PB.PurchaseOrderNumber AND PCJ.DATAAREAID = PB.DATAAREAID

UNION ALL

-- 7. Purchase Order Confirmed by Vendor
-- Note: This is often not a standard event. The logic here is a placeholder.
-- You may need to adapt this based on custom fields or statuses indicating vendor confirmation.
SELECT
    NULL AS PurchaseOrderNumber,
    'Purchase Order Confirmed by Vendor' AS ActivityName,
    NULL AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    NULL AS UserName,
    NULL AS VendorName,
    NULL AS PurchaseOrderTotalAmount,
    NULL AS PurchaseOrderStatus,
    NULL AS RequestedDeliveryDate
WHERE 1=0 -- Disable this by default

UNION ALL

-- 8. Purchase Order Changed
SELECT
    PurchaseOrderNumber,
    'Purchase Order Changed' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base
WHERE MODIFIEDDATETIME <> CREATEDDATETIME AND DOCUMENTSTATE > 1 -- Change after initial creation/draft

UNION ALL

-- 9. Goods Receipt Posted
SELECT
    VPJ.PURCHID AS PurchaseOrderNumber,
    'Goods Receipt Posted' AS ActivityName,
    VPJ.DELIVERYDATE AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    VPJ.CREATEDBY AS UserName,
    PB.VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = VPJ.PURCHID) AS PurchaseOrderTotalAmount,
    CAST(PB.DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    PB.RequestedDeliveryDate
FROM VENDPACKINGSLIPJOUR VPJ
JOIN PO_Base PB ON VPJ.PURCHID = PB.PurchaseOrderNumber AND VPJ.DATAAREAID = PB.DATAAREAID

UNION ALL

-- 10. Quality Inspection Performed
SELECT
    IT.REFID AS PurchaseOrderNumber,
    'Quality Inspection Performed' AS ActivityName,
    IQT.CREATEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    IQT.CREATEDBY AS UserName,
    PB.VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = IT.REFID) AS PurchaseOrderTotalAmount,
    CAST(PB.DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    PB.RequestedDeliveryDate
FROM INVENTQUALITYORDERTABLE IQT
JOIN INVENTTRANS IT ON IQT.INVENTTRANSORIGIN = IT.INVENTTRANSORIGIN
JOIN PO_Base PB ON IT.REFID = PB.PurchaseOrderNumber AND IT.DATAAREAID = PB.DATAAREAID
WHERE IQT.STATUS = 2 -- Status for 'Passed' or 'Closed', adjust as needed

UNION ALL

-- 11. Goods Returned to Vendor
SELECT
    PurchaseOrderNumber,
    'Goods Returned to Vendor' AS ActivityName,
    CREATEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base
WHERE EXISTS (SELECT 1 FROM PURCHLINE PL WHERE PL.PURCHID = PO_Base.PurchaseOrderNumber AND PL.PURCHQTY < 0)

UNION ALL

-- 12. Purchase Order Invoiced
SELECT
    VIJ.PURCHID AS PurchaseOrderNumber,
    'Purchase Order Invoiced' AS ActivityName,
    VIJ.INVOICEDATE AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    VIJ.CREATEDBY AS UserName,
    PB.VendorName,
    VIJ.INVOICEAMOUNT AS PurchaseOrderTotalAmount,
    CAST(PB.DOCUMENTSTATE AS NVARCHAR(255)) AS PurchaseOrderStatus,
    PB.RequestedDeliveryDate
FROM VENDINVOICEJOUR VIJ
JOIN PO_Base PB ON VIJ.PURCHID = PB.PurchaseOrderNumber AND VIJ.DATAAREAID = PB.DATAAREAID

UNION ALL

-- 13. Purchase Order Completed
SELECT
    PurchaseOrderNumber,
    'Purchase Order Completed' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(PURCHSTATUS AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base
WHERE PURCHSTATUS = 4 -- Status for 'Invoiced'

UNION ALL

-- 14. Purchase Order Canceled
SELECT
    PurchaseOrderNumber,
    'Purchase Order Canceled' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    'Microsoft Dynamics 365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    UserName,
    VendorName,
    (SELECT SUM(LINEAMOUNT) FROM PURCHLINE WHERE PURCHID = PO_Base.PurchaseOrderNumber) AS PurchaseOrderTotalAmount,
    CAST(PURCHSTATUS AS NVARCHAR(255)) AS PurchaseOrderStatus,
    RequestedDeliveryDate
FROM PO_Base
WHERE PURCHSTATUS = 5; -- Status for 'Canceled'

import requests
import pandas as pd
import datetime

class=class="hl-string">"hl-comment"># --- PLACEHOLDER CONFIGURATION ---
D365_URL = class="hl-string">"https://[your-environment].operations.dynamics.com"
CLIENT_ID = class="hl-string">"[your-client-id]"
CLIENT_SECRET = class="hl-string">"[your-client-secret]"
TENANT_ID = class="hl-string">"[your-tenant-id]"

class=class="hl-string">"hl-comment"># --- EXTRACTION PARAMETERS ---
data_area_id = class="hl-string">"[YourCompanyCode]"  class=class="hl-string">"hl-comment"># e.g., class="hl-string">'USMF'
start_date_filter = class="hl-string">"2023-01-01T00:00:00Z"


def get_access_token():
    class="hl-string">"""Authenticates with Azure AD to get an access token."""
    auth_url = fclass="hl-string">"https://login.microsoftonline.com/{TENANT_ID}/oauth2/token"
    payload = {
        class="hl-string">'grant_type': class="hl-string">'client_credentials',
        class="hl-string">'client_id': CLIENT_ID,
        class="hl-string">'client_secret': CLIENT_SECRET,
        class="hl-string">'resource': D365_URL
    }
    response = requests.post(auth_url, data=payload)
    response.raise_for_status()
    return response.json()[class="hl-string">'access_token']

def fetch_all_pages(url, headers):
    class="hl-string">"""Fetches all pages of data from a D365 OData endpoint."""
    all_results = []
    while url:
        response = requests.get(url, headers=headers)
        response.raise_for_status()
        data = response.json()
        all_results.extend(data[class="hl-string">'value'])
        url = data.get(class="hl-string">'@odata.nextLink')
        print(fclass="hl-string">"Fetched {len(all_results)} records...")
    return all_results

def extract_p2p_data():
    class="hl-string">"""Main function to extract P2P event log data."""
    try:
        access_token = get_access_token()
    except requests.exceptions.HTTPError as e:
        print(fclass="hl-string">"Authentication failed: {e.response.text}")
        return

    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json'
    }

    all_events = []
    last_data_update = datetime.datetime.now(datetime.timezone.utc).isoformat()

    class=class="hl-string">"hl-comment"># Define queries for each activity
    activity_queries = {
        class="hl-string">"Purchase Requisition Created": fclass="hl-string">"{D365_URL}/data/PurchaseRequisitionLinesV2?$filter=dataAreaId eq '{data_area_id}' and CreatedDateTime ge {start_date_filter}&$select=PurchaseRequisitionNumber,PurchaseOrderNumber,CreatedDateTime,CreatedBy,RequisitionStatus,RequestedDeliveryDate",
        class="hl-string">"Purchase Requisition Approved": fclass="hl-string">"{D365_URL}/data/PurchaseRequisitionLinesV2?$filter=dataAreaId eq '{data_area_id}' and RequisitionStatus eq 'Approved' and ModifiedDateTime ge {start_date_filter}&$select=PurchaseRequisitionNumber,PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,RequisitionStatus,RequestedDeliveryDate",
        class="hl-string">"Purchase Order Created": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and CreatedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,CreatedDateTime,CreatedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
        class="hl-string">"Purchase Order Submitted for Approval": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and ApprovalStatus eq 'InReview' and ModifiedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
        class="hl-string">"Purchase Order Approved": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and ApprovalStatus eq 'Approved' and ModifiedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
        class="hl-string">"Purchase Order Sent to Vendor": fclass="hl-string">"{D365_URL}/data/PurchaseOrderConfirmationsV2?$filter=dataAreaId eq '{data_area_id}' and ConfirmationDate ge {start_date_filter}&$select=PurchaseOrderNumber,ConfirmationDate,CreatedBy",
        class="hl-string">"Purchase Order Confirmed by Vendor": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and PurchaseOrderStatus eq 'Confirmed' and ModifiedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
        class="hl-string">"Purchase Order Changed": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and ModifiedDateTime gt CreatedDateTime and ModifiedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
        class="hl-string">"Goods Receipt Posted": fclass="hl-string">"{D365_URL}/data/PurchaseOrderReceiptsV2?$filter=dataAreaId eq '{data_area_id}' and ReceiptDate ge {start_date_filter}&$select=PurchaseOrderNumber,ReceiptDate,CreatedBy,ProductReceiptNumber",
        class="hl-string">"Quality Inspection Performed": fclass="hl-string">"{D365_URL}/data/QualityOrders?$filter=dataAreaId eq '{data_area_id}' and ReferenceType eq 'Purchase' and CreatedDateTime ge {start_date_filter}&$select=ReferenceNumber,CreatedDateTime,CreatedBy",
        class="hl-string">"Goods Returned to Vendor": fclass="hl-string">"{D365_URL}/data/[YourVendorReturnEntity]?$filter=dataAreaId eq '{data_area_id}' and CreatedDateTime ge {start_date_filter}&$select=OriginalPurchaseOrderNumber,ReturnDate,CreatedBy",
        class="hl-string">"Purchase Order Invoiced": fclass="hl-string">"{D365_URL}/data/PurchaseOrderInvoiceLinesV2?$filter=dataAreaId eq '{data_area_id}' and InvoiceDate ge {start_date_filter}&$select=PurchaseOrderNumber,InvoiceDate,CreatedBy",
        class="hl-string">"Purchase Order Completed": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and PurchaseOrderStatus eq 'Invoiced' and ModifiedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
        class="hl-string">"Purchase Order Canceled": fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeadersV2?$filter=dataAreaId eq '{data_area_id}' and PurchaseOrderStatus eq 'Canceled' and ModifiedDateTime ge {start_date_filter}&$select=PurchaseOrderNumber,ModifiedDateTime,ModifiedBy,VendorAccount,PurchaseOrderTotalAmount,PurchaseOrderStatus,RequestedDeliveryDate&$expand=VendorV2($select=VendorName)",
    }

    for activity, url in activity_queries.items():
        print(fclass="hl-string">'\n--- Fetching data for activity: {activity} ---')
        try:
            results = fetch_all_pages(url, headers)
            for item in results:
                po_number = item.get(class="hl-string">'PurchaseOrderNumber') or item.get(class="hl-string">'ReferenceNumber') or item.get(class="hl-string">'OriginalPurchaseOrderNumber')
                if not po_number:
                    continue

                event = {
                    class="hl-string">'PurchaseOrderNumber': po_number,
                    class="hl-string">'ActivityName': activity,
                    class="hl-string">'EventTime': item.get(class="hl-string">'ModifiedDateTime') or item.get(class="hl-string">'CreatedDateTime') or item.get(class="hl-string">'ConfirmationDate') or item.get(class="hl-string">'ReceiptDate') or item.get(class="hl-string">'InvoiceDate') or item.get(class="hl-string">'ReturnDate'),
                    class="hl-string">'SourceSystem': class="hl-string">'Microsoft Dynamics 365',
                    class="hl-string">'LastDataUpdate': last_data_update,
                    class="hl-string">'UserName': item.get(class="hl-string">'ModifiedBy') or item.get(class="hl-string">'CreatedBy'),
                    class="hl-string">'VendorName': item.get(class="hl-string">'VendorV2', {}).get(class="hl-string">'VendorName') if item.get(class="hl-string">'VendorV2') else None,
                    class="hl-string">'PurchaseOrderTotalAmount': item.get(class="hl-string">'PurchaseOrderTotalAmount'),
                    class="hl-string">'PurchaseOrderStatus': item.get(class="hl-string">'PurchaseOrderStatus'),
                    class="hl-string">'RequestedDeliveryDate': item.get(class="hl-string">'RequestedDeliveryDate'),
                }
                all_events.append(event)
        except requests.exceptions.HTTPError as e:
            print(fclass="hl-string">"Could not fetch data for '{activity}'. URL: {url}. Error: {e.response.text}")
        except Exception as e:
            print(fclass="hl-string">"An unexpected error occurred for '{activity}': {e}")

    if not all_events:
        print(class="hl-string">"No events were extracted. Please check your configuration and D365 data.")
        return

    class=class="hl-string">"hl-comment"># Create DataFrame and save to CSV
    df = pd.DataFrame(all_events)
    df = df.dropna(subset=[class="hl-string">'PurchaseOrderNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime'])
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'], errors=class="hl-string">'coerce')
    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">'UserName', class="hl-string">'VendorName', class="hl-string">'PurchaseOrderTotalAmount', class="hl-string">'PurchaseOrderStatus', class="hl-string">'RequestedDeliveryDate'
    ]
    df = df[column_order]

    df.to_csv(class="hl-string">'d365_p2p_event_log.csv', index=False)
    print(class="hl-string">'\nExtraction complete. Event log saved to d365_p2p_event_log.csv')

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

WITH
RequisitionCreated AS (
    SELECT
        CAST([Purchase requisition number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Purchase Requisition Created' AS ActivityName,
        CAST([Purchase requisition created timestamp field] AS datetime2) AS EventTime,
        CAST([Purchase requisition created by field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase requisition amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase requisition status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your purchase requisition table]
    WHERE CAST([Purchase requisition created timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([Purchase requisition created timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
RequisitionApproved AS (
    SELECT
        CAST([Purchase requisition number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Purchase Requisition Approved' AS ActivityName,
        CAST([Requisition approval timestamp field] AS datetime2) AS EventTime,
        CAST([Approver field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase requisition amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase requisition status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your requisition workflow history table]
    WHERE [Requisition workflow action field] = '[Your requisition approved value]'
      AND CAST([Requisition approval timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([Requisition approval timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
PurchaseOrderCreated AS (
    SELECT
        CAST([Purchase order number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Purchase Order Created' AS ActivityName,
        CAST([Purchase order created timestamp field] AS datetime2) AS EventTime,
        CAST([Purchase order created by field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase order total amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase order status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your purchase order header table]
    WHERE CAST([Purchase order created timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([Purchase order created timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
PurchaseOrderSubmitted AS (
    SELECT
        CAST([Purchase order number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Purchase Order Submitted for Approval' AS ActivityName,
        CAST([PO submission timestamp field] AS datetime2) AS EventTime,
        CAST([Submitted by field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase order total amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase order status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your purchase order workflow history table]
    WHERE [PO workflow action field] = '[Your PO submitted value]'
      AND CAST([PO submission timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([PO submission timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
PurchaseOrderApproved AS (
    SELECT
        CAST([Purchase order number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Purchase Order Approved' AS ActivityName,
        CAST([PO approval timestamp field] AS datetime2) AS EventTime,
        CAST([Approver field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase order total amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase order status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your purchase order workflow history table]
    WHERE [PO workflow action field] = '[Your PO approved value]'
      AND CAST([PO approval timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([PO approval timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
PurchaseOrderSent AS (
    SELECT
        CAST([Purchase order number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Purchase Order Sent to Vendor' AS ActivityName,
        CAST([PO confirmation or sent timestamp field] AS datetime2) AS EventTime,
        CAST([Confirmed or sent by field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase order total amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase order status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your purchase order confirmation table]
    WHERE [PO confirmation or sending status field] = '[Your sent or confirmed value]'
      AND CAST([PO confirmation or sent timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([PO confirmation or sent timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
PurchaseOrderVendorConfirmed AS (
    SELECT
        CAST([Purchase order number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Purchase Order Confirmed by Vendor' AS ActivityName,
        CAST([Vendor confirmation timestamp field] AS datetime2) AS EventTime,
        CAST([Vendor confirmation entered by field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase order total amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase order status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your vendor confirmation table]
    WHERE [Vendor confirmation status field] = '[Your vendor confirmed value]'
      AND CAST([Vendor confirmation timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([Vendor confirmation timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
PurchaseOrderChanged AS (
    SELECT
        CAST([Purchase order number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Purchase Order Changed' AS ActivityName,
        CAST([PO version or change timestamp field] AS datetime2) AS EventTime,
        CAST([Changed by field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase order total amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase order status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your purchase order version or audit table]
    WHERE [PO change indicator field] = '[Your change indicator value]'
      AND CAST([PO version or change timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([PO version or change timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
GoodsReceiptPosted AS (
    SELECT
        CAST([Purchase order number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Goods Receipt Posted' AS ActivityName,
        CAST([Product receipt posting timestamp field] AS datetime2) AS EventTime,
        CAST([Posted by field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase order total amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase order status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your product receipt table]
    WHERE [Product receipt posting status field] = '[Your posted value]'
      AND CAST([Product receipt posting timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([Product receipt posting timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
QualityInspectionPerformed AS (
    SELECT
        CAST([Purchase order number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Quality Inspection Performed' AS ActivityName,
        CAST([Quality inspection completion timestamp field] AS datetime2) AS EventTime,
        CAST([Inspector field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase order total amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase order status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your quality inspection table]
    WHERE [Quality inspection status field] = '[Your completed value]'
      AND CAST([Quality inspection completion timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([Quality inspection completion timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
GoodsReturnedToVendor AS (
    SELECT
        CAST([Purchase order number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Goods Returned to Vendor' AS ActivityName,
        CAST([Return posting timestamp field] AS datetime2) AS EventTime,
        CAST([Return posted by field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase order total amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase order status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your purchase return transaction table]
    WHERE [Return transaction status field] = '[Your posted return value]'
      AND CAST([Return posting timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([Return posting timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
PurchaseOrderInvoiced AS (
    SELECT
        CAST([Purchase order number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Purchase Order Invoiced' AS ActivityName,
        CAST([Vendor invoice posting timestamp field] AS datetime2) AS EventTime,
        CAST([Invoice posted by field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase order total amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase order status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your vendor invoice table]
    WHERE [Vendor invoice posting status field] = '[Your posted invoice value]'
      AND CAST([Vendor invoice posting timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([Vendor invoice posting timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
PurchaseOrderCompleted AS (
    SELECT
        CAST([Purchase order number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Purchase Order Completed' AS ActivityName,
        CAST([PO completion timestamp field] AS datetime2) AS EventTime,
        CAST([Status changed by field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase order total amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase order status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your purchase order status history table]
    WHERE [Purchase order status field] = '[Your completed status value]'
      AND CAST([PO completion timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([PO completion timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
PurchaseOrderCanceled AS (
    SELECT
        CAST([Purchase order number field] AS varchar(100)) AS PurchaseOrderNumber,
        'Purchase Order Canceled' AS ActivityName,
        CAST([PO cancellation timestamp field] AS datetime2) AS EventTime,
        CAST([Canceled by field] AS varchar(255)) AS UserName,
        CAST([Vendor name field] AS varchar(255)) AS VendorName,
        CAST([Purchase order total amount field] AS decimal(38, 6)) AS PurchaseOrderTotalAmount,
        CAST([Purchase order status field] AS varchar(100)) AS PurchaseOrderStatus,
        CAST([Requested delivery date field] AS date) AS RequestedDeliveryDate
    FROM [Your purchase order status history table]
    WHERE [Purchase order status field] = '[Your canceled status value]'
      AND CAST([PO cancellation timestamp field] AS datetime2) >= CAST('[Start date parameter]' AS datetime2)
      AND CAST([PO cancellation timestamp field] AS datetime2) < CAST('[End date parameter]' AS datetime2)
      AND [Your legal entity or company field] IN ([Your company filter])
),
AllEvents AS (
    SELECT * FROM RequisitionCreated
    UNION ALL SELECT * FROM RequisitionApproved
    UNION ALL SELECT * FROM PurchaseOrderCreated
    UNION ALL SELECT * FROM PurchaseOrderSubmitted
    UNION ALL SELECT * FROM PurchaseOrderApproved
    UNION ALL SELECT * FROM PurchaseOrderSent
    UNION ALL SELECT * FROM PurchaseOrderVendorConfirmed
    UNION ALL SELECT * FROM PurchaseOrderChanged
    UNION ALL SELECT * FROM GoodsReceiptPosted
    UNION ALL SELECT * FROM QualityInspectionPerformed
    UNION ALL SELECT * FROM GoodsReturnedToVendor
    UNION ALL SELECT * FROM PurchaseOrderInvoiced
    UNION ALL SELECT * FROM PurchaseOrderCompleted
    UNION ALL SELECT * FROM PurchaseOrderCanceled
)
SELECT
    PurchaseOrderNumber,
    ActivityName,
    EventTime,
    CAST('[Your Dynamics 365 source system name]' AS varchar(255)) AS SourceSystem,
    CAST(SYSUTCDATETIME() AS datetime2) AS LastDataUpdate,
    UserName,
    VendorName,
    PurchaseOrderTotalAmount,
    PurchaseOrderStatus,
    RequestedDeliveryDate
FROM AllEvents
WHERE PurchaseOrderNumber IS NOT NULL
  AND ActivityName IS NOT NULL
  AND EventTime IS NOT NULL
ORDER BY PurchaseOrderNumber, EventTime, ActivityName;