공급망 관리 데이터 템플릿

/*
This is a conceptual representation of the configuration within the Dynamics 365 Data Management Framework.
You will create one export project and add each of the following 'Data Entities' to it.
For each entity, you will select the specified 'Source Field' and map it to the target attribute.
The post-processing script is responsible for combining these separate files and standardizing column names.
The LogisticsOrder is primarily the SalesOrderNumber.
*/

-- Activity: Customer Order Created
Data Entity: Sales order headers V2
Filter: [Specify date range on CREATEDDATETIME]
Fields to Export:
  - LogisticsOrder: SALESORDERNUMBER
  - ActivityName: 'Customer Order Created'
  - EventTime: CREATEDDATETIME
  - UserId: CREATEDBY
  - SalesOrderNumber: SALESORDERNUMBER
  - CustomerName: CUSTOMERACCOUNTNUMBER
  - RequestedDeliveryDate: REQUESTEDSHIPPINGDATE

-- Activity: Order Canceled
Data Entity: Sales order headers V2
Filter: SALESORDERSTATUS = 'Canceled' AND [Specify date range on MODIFIEDDATETIME]
Fields to Export:
  - LogisticsOrder: SALESORDERNUMBER
  - ActivityName: 'Order Canceled'
  - EventTime: MODIFIEDDATETIME
  - UserId: MODIFIEDBY
  - SalesOrderNumber: SALESORDERNUMBER

-- Activity: Purchase Requisition Created
Data Entity: Purchase requisition lines V2
Filter: [Specify date range on CREATEDDATETIME]
Fields to Export:
  - LogisticsOrder: SALESORDERNUMBER -- (May require joining to find the originating sales order)
  - ActivityName: 'Purchase Requisition Created'
  - EventTime: CREATEDDATETIME
  - UserId: CREATEDBY
  - SalesOrderNumber: SALESORDERNUMBER

-- Activity: Purchase Order Issued
Data Entity: Purchase order headers V2
Filter: DOCUMENTSTATE = 'Confirmed' OR DOCUMENTSTATE = 'InExternalReview' AND [Specify date range on CONFIRMATIONDATE]
Fields to Export:
  - LogisticsOrder: [Originating Sales Order Number, may need to be mapped post-export]
  - ActivityName: 'Purchase Order Issued'
  - EventTime: CONFIRMATIONDATE
  - UserId: CREATEDBY
  - PurchaseOrderNumber: PURCHASEORDERNUMBER
  - SupplierName: VENDORACCOUNTNUMBER

-- Activity: Raw Materials Received
Data Entity: Product receipt headers
Filter: [Specify date range on RECEIPTDATE]
Fields to Export:
  - LogisticsOrder: [Originating Sales Order Number, may need to be mapped post-export]
  - ActivityName: 'Raw Materials Received'
  - EventTime: RECEIPTDATE
  - UserId: WORKERPERSONNELNUMBER
  - PurchaseOrderNumber: PURCHASEORDERNUMBER

-- Activity: Production Order Created
Data Entity: Production orders V3
Filter: [Specify date range on CREATEDDATETIME]
Fields to Export:
  - LogisticsOrder: INVENTORYSALESORDERNUMBER
  - ActivityName: 'Production Order Created'
  - EventTime: CREATEDDATETIME
  - UserId: CREATEDBY
  - ProductionOrderNumber: PRODUCTIONORDERNUMBER
  - SalesOrderNumber: INVENTORYSALESORDERNUMBER

-- Activity: Goods Produced
Data Entity: Report as finished production journal entries
Filter: [Specify date range on POSTINGDATETIME]
Fields to Export:
  - LogisticsOrder: [Originating Sales Order Number, requires lookup via ProductionOrderNumber]
  - ActivityName: 'Goods Produced'
  - EventTime: POSTINGDATETIME
  - UserId: [User who posted the journal]
  - ProductionOrderNumber: PRODUCTIONORDERNUMBER

-- Activity: Quality Control Performed
Data Entity: Inventory quality orders
Filter: QUALITYORDERSTATUS = 'Pass' OR QUALITYORDERSTATUS = 'Fail' AND [Specify date range on [Completion DateTime field]]
Fields to Export:
  - LogisticsOrder: INVENTORYSALESID
  - ActivityName: 'Quality Control Performed'
  - EventTime: [A field representing the completion or closing timestamp]
  - UserId: RESPONSIBLEWORKERPERSONNELNUMBER
  - SalesOrderNumber: INVENTORYSALESID

-- Activity: Warehouse Pick List Generated
Data Entity: Warehouse work headers
Filter: [Specify date range on CREATEDDATETIME]
Fields to Export:
  - LogisticsOrder: ORDERNUMBER
  - ActivityName: 'Warehouse Pick List Generated'
  - EventTime: CREATEDDATETIME
  - UserId: CREATEDBY
  - SalesOrderNumber: ORDERNUMBER

-- Activity: Goods Picked and Packed
Data Entity: Warehouse work headers
Filter: WORKSTATUS = 'Closed' AND [Specify date range on WORKCLOSEDUTCDATETIME]
Fields to Export:
  - LogisticsOrder: ORDERNUMBER
  - ActivityName: 'Goods Picked and Packed'
  - EventTime: WORKCLOSEDUTCDATETIME
  - UserId: CLOSEDWORKUSERID
  - SalesOrderNumber: ORDERNUMBER

-- Activity: Shipment Scheduled
Data Entity: Shipments
Filter: [Specify date range on CONFIRMEDSHIPDATE]
Fields to Export:
  - LogisticsOrder: [Originating Sales Order Number, requires lookup via shipment relations]
  - ActivityName: 'Shipment Scheduled'
  - EventTime: CONFIRMEDSHIPDATE
  - UserId: [User related to confirmation]

-- Activity: Goods Loaded For Transport
Data Entity: Shipments
Filter: SHIPMENTSTATUS = 'Shipped' AND [Specify date range on ACTUALSHIPDATE]
Fields to Export:
  - LogisticsOrder: [Originating Sales Order Number, requires lookup via shipment relations]
  - ActivityName: 'Goods Loaded For Transport'
  - EventTime: ACTUALSHIPDATE
  - UserId: [User related to ship confirm]

-- Activity: Customer Invoice Posted
Data Entity: Sales invoice headers V2
Filter: [Specify date range on INVOICEDATE]
Fields to Export:
  - LogisticsOrder: SALESORDERNUMBER
  - ActivityName: 'Customer Invoice Posted'
  - EventTime: INVOICEDATE
  - UserId: [User who posted invoice, if available]
  - SalesOrderNumber: SALESORDERNUMBER

-- Activity: Goods Delivered
Data Entity: [Your custom entity or Sales order headers V2 with custom status]
Note: This is not a standard event. This requires a custom field or integration.
Fields to Export:
  - LogisticsOrder: SALESORDERNUMBER
  - ActivityName: 'Goods Delivered'
  - EventTime: [Your custom delivery date field]
  - UserId: [Your custom user field]

-- Activity: Proof of Delivery Signed
Data Entity: [Your custom entity or Document management entity]
Note: This is not a standard event. This requires a custom field or process.
Fields to Export:
  - LogisticsOrder: SALESORDERNUMBER
  - ActivityName: 'Proof of Delivery Signed'
  - EventTime: [Your custom POD date field]
  - UserId: [Your custom user field]

WITH EventLog_Raw AS (
-- 1. Customer Order Created
SELECT
    soh.SALESORDERNUMBER AS LogisticsOrder,
    'Customer Order Created' AS ActivityName,
    soh.CREATIONDATETIME AS EventTime,
    soh.CREATEDBY AS UserId,
    soh.SALESORDERNUMBER AS SalesOrderNumber,
    NULL AS PurchaseOrderNumber,
    NULL AS ProductionOrderNumber,
    NULL AS SupplierName,
    soh.INVOICECUSTOMERACCOUNTNUMBER AS CustomerName, -- Simplified, join with customer master for full name
    soh.REQUESTEDSHIPPINGDATE AS RequestedDeliveryDate
FROM SalesOrderHeadersV2 soh
WHERE soh.DATACOMPANYID = '[YourD365Company]' AND soh.CREATIONDATETIME >= '2023-01-01'

UNION ALL

-- 2. Purchase Requisition Created
SELECT
    prl.SALESORDERNUMBER AS LogisticsOrder,
    'Purchase Requisition Created' AS ActivityName,
    prh.CREATIONDATETIME AS EventTime,
    prh.CREATEDBY AS UserId,
    prl.SALESORDERNUMBER AS SalesOrderNumber,
    NULL AS PurchaseOrderNumber,
    NULL AS ProductionOrderNumber,
    NULL AS SupplierName,
    NULL AS CustomerName,
    NULL AS RequestedDeliveryDate
FROM PurchaseRequisitionLines prl
JOIN PurchaseRequisitionHeaders prh ON prl.REQUISITIONHEADER_REQUISITIONNUMBER = prh.REQUISITIONNUMBER AND prl.DATACOMPANYID = prh.DATACOMPANYID
WHERE prl.DATACOMPANYID = '[YourD365Company]' AND prh.CREATIONDATETIME >= '2023-01-01' AND prl.SALESORDERNUMBER IS NOT NULL

UNION ALL

-- 3. Purchase Order Issued
SELECT
    sol.SALESORDERNUMBER AS LogisticsOrder,
    'Purchase Order Issued' AS ActivityName,
    poh.CONFIRMEDDELIVERYDATE AS EventTime, -- Using confirmation date as event time
    poh.CREATEDBY AS UserId,
    sol.SALESORDERNUMBER AS SalesOrderNumber,
    poh.PURCHASEORDERNUMBER AS PurchaseOrderNumber,
    NULL AS ProductionOrderNumber,
    poh.VENDORACCOUNTNUMBER AS SupplierName, -- Simplified, join with vendor master for full name
    NULL AS CustomerName,
    NULL AS RequestedDeliveryDate
FROM PurchaseOrderHeadersV2 poh
JOIN PurchaseOrderLines pol ON poh.PURCHASEORDERNUMBER = pol.PURCHASEORDERNUMBER AND poh.DATACOMPANYID = pol.DATACOMPANYID
JOIN SalesOrderLines sol ON pol.INVENTTRANSID = sol.INVENTTRANSID AND pol.DATACOMPANYID = sol.DATACOMPANYID
WHERE poh.DATACOMPANYID = '[YourD365Company]' AND poh.CONFIRMEDDELIVERYDATE IS NOT NULL AND poh.CONFIRMEDDELIVERYDATE >= '2023-01-01'

UNION ALL

-- 4. Raw Materials Received
SELECT
    sol.SALESORDERNUMBER AS LogisticsOrder,
    'Raw Materials Received' AS ActivityName,
    prh.RECEIPTDATE AS EventTime,
    prh.CREATEDBY AS UserId,
    sol.SALESORDERNUMBER AS SalesOrderNumber,
    prh.PURCHASEORDERNUMBER AS PurchaseOrderNumber,
    NULL AS ProductionOrderNumber,
    poh.VENDORACCOUNTNUMBER AS SupplierName,
    NULL AS CustomerName,
    NULL AS RequestedDeliveryDate
FROM PurchReceiptsJournalHeader prh
JOIN PurchaseOrderHeadersV2 poh ON prh.PURCHASEORDERNUMBER = poh.PURCHASEORDERNUMBER AND prh.DATACOMPANYID = poh.DATACOMPANYID
JOIN PurchaseOrderLines pol ON poh.PURCHASEORDERNUMBER = pol.PURCHASEORDERNUMBER AND poh.DATACOMPANYID = pol.DATACOMPANYID
JOIN SalesOrderLines sol ON pol.INVENTTRANSID = sol.INVENTTRANSID AND pol.DATACOMPANYID = sol.DATACOMPANYID
WHERE prh.DATACOMPANYID = '[YourD365Company]' AND prh.RECEIPTDATE >= '2023-01-01'

UNION ALL

-- 5. Production Order Created
SELECT
    pro.SALESORDERNUMBER AS LogisticsOrder,
    'Production Order Created' AS ActivityName,
    pro.CREATIONDATETIME AS EventTime,
    pro.CREATEDBY AS UserId,
    pro.SALESORDERNUMBER AS SalesOrderNumber,
    NULL AS PurchaseOrderNumber,
    pro.PRODUCTIONORDERNUMBER AS ProductionOrderNumber,
    NULL AS SupplierName,
    NULL AS CustomerName,
    NULL AS RequestedDeliveryDate
FROM ProductionOrders pro
WHERE pro.DATACOMPANYID = '[YourD365Company]' AND pro.CREATIONDATETIME >= '2023-01-01' AND pro.SALESORDERNUMBER IS NOT NULL

UNION ALL

-- 6. Goods Produced
SELECT
    pro.SALESORDERNUMBER AS LogisticsOrder,
    'Goods Produced' AS ActivityName,
    pro.LASTSCHEDULINGDATETIME AS EventTime, -- Approximation for 'Reported as Finished'
    pro.LASTMODIFIEDBY AS UserId,
    pro.SALESORDERNUMBER AS SalesOrderNumber,
    NULL AS PurchaseOrderNumber,
    pro.PRODUCTIONORDERNUMBER AS ProductionOrderNumber,
    NULL AS SupplierName,
    NULL AS CustomerName,
    NULL AS RequestedDeliveryDate
FROM ProductionOrders pro
WHERE pro.DATACOMPANYID = '[YourD365Company]' AND pro.PRODUCTIONORDERSTATUS = 'ReportedAsFinished' AND pro.LASTSCHEDULINGDATETIME >= '2023-01-01'

UNION ALL

-- 7. Quality Control Performed
SELECT
    qo.INVENTTRANSORIGINID AS LogisticsOrder, -- This link might need adjustment based on your setup
    'Quality Control Performed' AS ActivityName,
    qo.COMPLETIONDATETIME AS EventTime,
    qo.COMPLETEDBY AS UserId,
    qo.INVENTTRANSORIGINID AS SalesOrderNumber, -- Assuming the origin is the SO
    NULL AS PurchaseOrderNumber,
    qo.REFERENCEID AS ProductionOrderNumber, -- Assuming reference is Prod Order
    NULL AS SupplierName,
    NULL AS CustomerName,
    NULL AS RequestedDeliveryDate
FROM QualityOrders qo
WHERE qo.DATACOMPANYID = '[YourD365Company]' AND qo.QUALITYORDERSTATUS = 'Pass' AND qo.COMPLETIONDATETIME >= '2023-01-01'

UNION ALL

-- 8. Warehouse Pick List Generated
SELECT
    wh.ORDERNUMBER AS LogisticsOrder,
    'Warehouse Pick List Generated' AS ActivityName,
    wh.CREATIONDATETIME AS EventTime,
    wh.CREATEDBY AS UserId,
    wh.ORDERNUMBER AS SalesOrderNumber,
    NULL AS PurchaseOrderNumber,
    NULL AS ProductionOrderNumber,
    NULL AS SupplierName,
    NULL AS CustomerName,
    NULL AS RequestedDeliveryDate
FROM WarehouseWorkHeaders wh
WHERE wh.DATACOMPANYID = '[YourD365Company]' AND wh.ORDERTYPE = 'Sales' AND wh.CREATIONDATETIME >= '2023-01-01'

UNION ALL

-- 9. Goods Picked and Packed
SELECT
    wh.ORDERNUMBER AS LogisticsOrder,
    'Goods Picked and Packed' AS ActivityName,
    wh.LASTMODIFIEDDATETIME AS EventTime,
    wh.LASTMODIFIEDBY AS UserId,
    wh.ORDERNUMBER AS SalesOrderNumber,
    NULL AS PurchaseOrderNumber,
    NULL AS ProductionOrderNumber,
    NULL AS SupplierName,
    NULL AS CustomerName,
    NULL AS RequestedDeliveryDate
FROM WarehouseWorkHeaders wh
WHERE wh.DATACOMPANYID = '[YourD365Company]' AND wh.ORDERTYPE = 'Sales' AND wh.WORKSTATUS = 'Closed' AND wh.LASTMODIFIEDDATETIME >= '2023-01-01'

UNION ALL

-- 10. Shipment Scheduled and 11. Goods Loaded For Transport (using Packing Slip as proxy)
SELECT
    psh.SALESORDERNUMBER AS LogisticsOrder,
    'Goods Loaded For Transport' AS ActivityName, -- Packing slip date is a good proxy for ship confirm
    psh.DELIVERYDATETIME AS EventTime,
    psh.CREATEDBY AS UserId,
    psh.SALESORDERNUMBER AS SalesOrderNumber,
    NULL AS PurchaseOrderNumber,
    NULL AS ProductionOrderNumber,
    NULL AS SupplierName,
    NULL AS CustomerName,
    NULL AS RequestedDeliveryDate
FROM SalesPackingSlipHeaders psh
WHERE psh.DATACOMPANYID = '[YourD365Company]' AND psh.DELIVERYDATETIME >= '2023-01-01'

UNION ALL

-- 12. Customer Invoice Posted
SELECT
    sih.SALESORDERNUMBER AS LogisticsOrder,
    'Customer Invoice Posted' AS ActivityName,
    sih.INVOICEDATE AS EventTime,
    sih.CREATEDBY AS UserId,
    sih.SALESORDERNUMBER AS SalesOrderNumber,
    NULL AS PurchaseOrderNumber,
    NULL AS ProductionOrderNumber,
    NULL AS SupplierName,
    sih.INVOICECUSTOMERACCOUNTNUMBER AS CustomerName,
    NULL AS RequestedDeliveryDate
FROM SalesInvoiceHeadersV2 sih
WHERE sih.DATACOMPANYID = '[YourD365Company]' AND sih.INVOICEDATE >= '2023-01-01'

UNION ALL

-- 13. Goods Delivered (Placeholder - Assumes a custom status or manual update)
SELECT
    soh.SALESORDERNUMBER AS LogisticsOrder,
    'Goods Delivered' AS ActivityName,
    soh.LASTMODIFIEDDATETIME AS EventTime,
    soh.LASTMODIFIEDBY AS UserId,
    soh.SALESORDERNUMBER AS SalesOrderNumber,
    NULL, NULL, NULL, NULL, NULL
FROM SalesOrderHeadersV2 soh
WHERE soh.DATACOMPANYID = '[YourD365Company]' AND soh.SALESORDERSTATUS = '[YourDeliveredStatus]' AND soh.LASTMODIFIEDDATETIME >= '2023-01-01'

UNION ALL

-- 14. Proof of Delivery Signed (Placeholder - Assumes a custom status or manual update)
SELECT
    soh.SALESORDERNUMBER AS LogisticsOrder,
    'Proof of Delivery Signed' AS ActivityName,
    soh.LASTMODIFIEDDATETIME AS EventTime,
    soh.LASTMODIFIEDBY AS UserId,
    soh.SALESORDERNUMBER AS SalesOrderNumber,
    NULL, NULL, NULL, NULL, NULL
FROM SalesOrderHeadersV2 soh
WHERE soh.DATACOMPANYID = '[YourD365Company]' AND soh.SALESORDERSTATUS = '[YourPODStatus]' AND soh.LASTMODIFIEDDATETIME >= '2023-01-01'

UNION ALL

-- 15. Order Canceled
SELECT
    soh.SALESORDERNUMBER AS LogisticsOrder,
    'Order Canceled' AS ActivityName,
    soh.LASTMODIFIEDDATETIME AS EventTime,
    soh.LASTMODIFIEDBY AS UserId,
    soh.SALESORDERNUMBER AS SalesOrderNumber,
    NULL, NULL, NULL, NULL, NULL
FROM SalesOrderHeadersV2 soh
WHERE soh.DATACOMPANYID = '[YourD365Company]' AND soh.SALESORDERSTATUS = 'Canceled' AND soh.LASTMODIFIEDDATETIME >= '2023-01-01'
),
FinalEvents AS (
SELECT 
    *,
    MIN(CASE WHEN ActivityName = 'Purchase Requisition Created' THEN EventTime ELSE NULL END) OVER (PARTITION BY LogisticsOrder) AS first_pr_creation_time,
    MAX(CASE WHEN ActivityName = 'Raw Materials Received' THEN EventTime ELSE NULL END) OVER (PARTITION BY LogisticsOrder) AS last_material_receipt_time,
    MAX(CASE WHEN ActivityName = 'Goods Delivered' THEN EventTime ELSE NULL END) OVER (PARTITION BY LogisticsOrder) AS actual_delivery_time
FROM EventLog_Raw
)
SELECT
    e.LogisticsOrder,
    e.ActivityName,
    e.EventTime,
    'Dynamics 365 SCM' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    e.UserId,
    e.SalesOrderNumber,
    e.PurchaseOrderNumber,
    e.ProductionOrderNumber,
    e.SupplierName,
    e.CustomerName,
    e.RequestedDeliveryDate,
    CASE 
        WHEN e.actual_delivery_time IS NOT NULL AND e.RequestedDeliveryDate IS NOT NULL AND e.actual_delivery_time <= e.RequestedDeliveryDate THEN 'true'
        WHEN e.actual_delivery_time IS NOT NULL AND e.RequestedDeliveryDate IS NOT NULL THEN 'false'
        ELSE NULL
    END AS IsOnTimeDelivery,
    DATEDIFF(day, e.first_pr_creation_time, e.last_material_receipt_time) AS ProcurementLeadTime
FROM FinalEvents e
WHERE e.LogisticsOrder IS NOT NULL;

import requests
import pandas as pd
import datetime
import adal
import json

class=class="hl-string">"hl-comment"># --- Configuration Settings --- #
D365_URL = class="hl-string">"https://[your-instance].operations.dynamics.com" class=class="hl-string">"hl-comment"># Your D365 Finance and Operations URL
TENANT_ID = class="hl-string">"[Your-Tenant-ID]"
CLIENT_ID = class="hl-string">"[Your-Client-ID]"
CLIENT_SECRET = class="hl-string">"[Your-Client-Secret]"
COMPANY_ID = class="hl-string">"[Your-Company-ID]" class=class="hl-string">"hl-comment"># e.g., class="hl-string">'USMF'
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"># --- End of Configuration --- #

RESOURCE = D365_URL
AUTHORITY_URL = fclass="hl-string">"https://login.microsoftonline.com/{TENANT_ID}"

def get_access_token():
    class="hl-string">"""Authenticates with Azure AD to get an access token."""
    context = adal.AuthenticationContext(AUTHORITY_URL)
    token = context.acquire_token_with_client_credentials(RESOURCE, CLIENT_ID, CLIENT_SECRET)
    if class="hl-string">'accessToken' not in token:
        raise Exception(class="hl-string">"Failed to acquire access token: " + token.get(class="hl-string">'error_description', class="hl-string">'Unknown error'))
    return token[class="hl-string">'accessToken']

def fetch_data(access_token, entity_url):
    class="hl-string">"""Fetches all pages of data from a given D365 OData entity URL."""
    all_records = []
    headers = {
        class="hl-string">"Authorization": fclass="hl-string">"Bearer {access_token}",
        class="hl-string">"Content-Type": class="hl-string">"application/json",
        class="hl-string">"Prefer": class="hl-string">"odata.maxpagesize=5000"
    }
    
    next_link = entity_url
    while next_link:
        try:
            response = requests.get(next_link, headers=headers)
            response.raise_for_status()
            data = response.json()
            all_records.extend(data[class="hl-string">'value'])
            next_link = data.get(class="hl-string">'@odata.nextLink')
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"An error occurred: {e}")
            if response.text:
                print(fclass="hl-string">"Response body: {response.text}")
            break
    return all_records

def main():
    class="hl-string">"""Main function to orchestrate the data extraction process."""
    try:
        access_token = get_access_token()
        print(class="hl-string">"Successfully acquired access token.")
    except Exception as e:
        print(e)
        return

    all_events = []
    current_time = datetime.datetime.utcnow().isoformat() + class="hl-string">'Z'

    class=class="hl-string">"hl-comment"># 1. Customer Order Created
    print(class="hl-string">"Fetching: Customer Order Created")
    entity_url = fclass="hl-string">"{D365_URL}/data/SalesOrderHeadersV2?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}&$select=SalesOrderNumber,CreatedDateTime,SalesOrderName,RequestedShippingDate,OrderingCustomerAccountNumber"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Customer Order Created'
        df.rename(columns={
            class="hl-string">'SalesOrderNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'CreatedDateTime': class="hl-string">'EventTime',
            class="hl-string">'SalesOrderName': class="hl-string">'CustomerName',
            class="hl-string">'RequestedShippingDate': class="hl-string">'RequestedDeliveryDate',
            class="hl-string">'OrderingCustomerAccountNumber': class="hl-string">'UserId'
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'CustomerName', class="hl-string">'RequestedDeliveryDate', class="hl-string">'UserId']])

    class=class="hl-string">"hl-comment"># 2. Purchase Requisition Created (Assuming link via Sales Order)
    print(class="hl-string">"Fetching: Purchase Requisition Created")
    entity_url = fclass="hl-string">"{D365_URL}/data/PurchaseRequisitionLines?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and RequisitionCreationDateTime ge {START_DATE} and RequisitionCreationDateTime le {END_DATE} and SalesOrderNumber ne ''&$select=SalesOrderNumber,RequisitionCreationDateTime,RequisitionNumber,RequisitionerPersonnelNumber"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Purchase Requisition Created'
        df.rename(columns={
            class="hl-string">'SalesOrderNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'RequisitionCreationDateTime': class="hl-string">'EventTime',
            class="hl-string">'RequisitionerPersonnelNumber': class="hl-string">'UserId'
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserId']])

    class=class="hl-string">"hl-comment"># 3. Purchase Order Issued
    print(class="hl-string">"Fetching: Purchase Order Issued")
    entity_url = fclass="hl-string">"{D365_URL}/data/PurchaseOrderHeaders?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and ConfirmedDate ge {START_DATE} and ConfirmedDate le {END_DATE} and SalesOrderNumber ne ''&$select=SalesOrderNumber,PurchaseOrderNumber,ConfirmedDate,OrderVendorAccountNumber,VendorName"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Purchase Order Issued'
        df.rename(columns={
            class="hl-string">'SalesOrderNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'ConfirmedDate': class="hl-string">'EventTime',
            class="hl-string">'VendorName': class="hl-string">'SupplierName',
            class="hl-string">'OrderVendorAccountNumber': class="hl-string">'UserId' 
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'PurchaseOrderNumber', class="hl-string">'SupplierName', class="hl-string">'UserId']])

    class=class="hl-string">"hl-comment"># 4. Raw Materials Received
    print(class="hl-string">"Fetching: Raw Materials Received")
    entity_url = fclass="hl-string">"{D365_URL}/data/ProductReceiptHeaders?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and ReceiptDate ge {START_DATE} and ReceiptDate le {END_DATE} and SalesOrderNumber ne ''&$select=SalesOrderNumber,PurchaseOrderNumber,ReceiptDate,VendorName"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Raw Materials Received'
        df.rename(columns={
            class="hl-string">'SalesOrderNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'ReceiptDate': class="hl-string">'EventTime',
            class="hl-string">'VendorName': class="hl-string">'SupplierName' 
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'PurchaseOrderNumber', class="hl-string">'SupplierName']])

    class=class="hl-string">"hl-comment"># 5. Production Order Created
    print(class="hl-string">"Fetching: Production Order Created")
    entity_url = fclass="hl-string">"{D365_URL}/data/ProductionOrders?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE} and SalesOrderNumber ne ''&$select=SalesOrderNumber,ProductionOrderNumber,CreatedDateTime"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Production Order Created'
        df.rename(columns={
            class="hl-string">'SalesOrderNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'CreatedDateTime': class="hl-string">'EventTime'
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'ProductionOrderNumber']])

    class=class="hl-string">"hl-comment"># 6. Goods Produced (Report as Finished)
    print(class="hl-string">"Fetching: Goods Produced")
    entity_url = fclass="hl-string">"{D365_URL}/data/ProductionReportedAsFinishedJournals?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and JournalPostedDateTime ge {START_DATE} and JournalPostedDateTime le {END_DATE} and SalesOrderNumber ne ''&$select=SalesOrderNumber,ProductionOrderNumber,JournalPostedDateTime,PostedUserId"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Goods Produced'
        df.rename(columns={
            class="hl-string">'SalesOrderNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'JournalPostedDateTime': class="hl-string">'EventTime',
            class="hl-string">'PostedUserId': class="hl-string">'UserId'
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'ProductionOrderNumber', class="hl-string">'UserId']])

    class=class="hl-string">"hl-comment"># 7. Quality Control Performed
    print(class="hl-string">"Fetching: Quality Control Performed")
    entity_url = fclass="hl-string">"{D365_URL}/data/QualityOrders?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and QualityOrderLastUpdateDateTime ge {START_DATE} and QualityOrderLastUpdateDateTime le {END_DATE} and ReferenceType eq 'Sales' and QualityOrderStatus eq 'Pass'&$select=ReferenceNumber,QualityOrderLastUpdateDateTime,ValidatedByEmployeePersonnelNumber"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Quality Control Performed'
        df.rename(columns={
            class="hl-string">'ReferenceNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'QualityOrderLastUpdateDateTime': class="hl-string">'EventTime',
            class="hl-string">'ValidatedByEmployeePersonnelNumber': class="hl-string">'UserId'
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserId']])

    class=class="hl-string">"hl-comment"># 8. Warehouse Pick List Generated (Work Created)
    print(class="hl-string">"Fetching: Warehouse Pick List Generated")
    entity_url = fclass="hl-string">"{D365_URL}/data/WarehouseWorkHeaders?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE} and OrderNumber ne '' and WorkType eq 'Sales'&$select=OrderNumber,CreatedDateTime,UserId"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Warehouse Pick List Generated'
        df.rename(columns={
            class="hl-string">'OrderNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'CreatedDateTime': class="hl-string">'EventTime'
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserId']])

    class=class="hl-string">"hl-comment"># 9. Goods Picked and Packed (Work Closed)
    print(class="hl-string">"Fetching: Goods Picked and Packed")
    entity_url = fclass="hl-string">"{D365_URL}/data/WarehouseWorkHeaders?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and LastUpdateDateTime ge {START_DATE} and LastUpdateDateTime le {END_DATE} and OrderNumber ne '' and WorkStatus eq 'Closed' and WorkType eq 'Sales'&$select=OrderNumber,LastUpdateDateTime,ClosedByUserId"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Goods Picked and Packed'
        df.rename(columns={
            class="hl-string">'OrderNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'LastUpdateDateTime': class="hl-string">'EventTime',
            class="hl-string">'ClosedByUserId': class="hl-string">'UserId'
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserId']])

    class=class="hl-string">"hl-comment"># 10. Shipment Scheduled
    print(class="hl-string">"Fetching: Shipment Scheduled")
    entity_url = fclass="hl-string">"{D365_URL}/data/SalesShipmentHeaders?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and ConfirmedShippingDate ge {START_DATE} and ConfirmedShippingDate le {END_DATE}&$select=SalesOrderNumber,ConfirmedShippingDate"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Shipment Scheduled'
        df.rename(columns={
            class="hl-string">'SalesOrderNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'ConfirmedShippingDate': class="hl-string">'EventTime'
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime']])

    class=class="hl-string">"hl-comment"># 11. Goods Loaded For Transport (Ship Confirm)
    print(class="hl-string">"Fetching: Goods Loaded For Transport")
    entity_url = fclass="hl-string">"{D365_URL}/data/SalesPackingSlips?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}&$select=SalesOrderNumber,CreatedDateTime,WorkerName"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Goods Loaded For Transport'
        df.rename(columns={
            class="hl-string">'SalesOrderNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'CreatedDateTime': class="hl-string">'EventTime',
            class="hl-string">'WorkerName': class="hl-string">'UserId' 
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserId']])

    class=class="hl-string">"hl-comment"># 12. Customer Invoice Posted
    print(class="hl-string">"Fetching: Customer Invoice Posted")
    entity_url = fclass="hl-string">"{D365_URL}/data/SalesInvoiceHeadersV2?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and InvoiceDate ge {START_DATE} and InvoiceDate le {END_DATE}&$select=SalesOrderNumber,InvoiceDate,PostingPersonName"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Customer Invoice Posted'
        df.rename(columns={
            class="hl-string">'SalesOrderNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'InvoiceDate': class="hl-string">'EventTime',
            class="hl-string">'PostingPersonName': class="hl-string">'UserId'
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserId']])

    class=class="hl-string">"hl-comment"># 13. Goods Delivered (Placeholder logic, depends on customization)
    print(class="hl-string">"Fetching: Goods Delivered")
    class=class="hl-string">"hl-comment"># This is an example. The actual entity and fields depend on your implementation for tracking delivery.
    class=class="hl-string">"hl-comment"># Often this comes from an external carrier system or a custom field on the shipment.
    class=class="hl-string">"hl-comment"># entity_url = fclass="hl-string">"{D365_URL}/data/[YourCustomShipmentEntity]?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and [DeliveryDate] ge {START_DATE} and [DeliveryDate] le {END_DATE}"

    class=class="hl-string">"hl-comment"># 14. Proof of Delivery Signed (Placeholder logic, depends on customization)
    print(class="hl-string">"Fetching: Proof of Delivery Signed")
    class=class="hl-string">"hl-comment"># Similar to delivery, this is highly dependent on customization, often a status update or document attachment.
    class=class="hl-string">"hl-comment"># entity_url = fclass="hl-string">"{D365_URL}/data/[YourCustomShipmentEntity]?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and [PODSignedDate] ge {START_DATE} and [PODSignedDate] le {END_DATE}"

    class=class="hl-string">"hl-comment"># 15. Order Canceled
    print(class="hl-string">"Fetching: Order Canceled")
    entity_url = fclass="hl-string">"{D365_URL}/data/SalesOrderHeadersV2?cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}' and LastUpdateDateTime ge {START_DATE} and LastUpdateDateTime le {END_DATE} and SalesOrderStatus eq 'Canceled'&$select=SalesOrderNumber,LastUpdateDateTime"
    records = fetch_data(access_token, entity_url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Order Canceled'
        df.rename(columns={
            class="hl-string">'SalesOrderNumber': class="hl-string">'LogisticsOrder',
            class="hl-string">'LastUpdateDateTime': class="hl-string">'EventTime'
        }, inplace=True)
        all_events.append(df[[class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime']])

    if not all_events:
        print(class="hl-string">"No events found for the given criteria.")
        return

    class=class="hl-string">"hl-comment"># Combine all event dataframes
    event_log = pd.concat(all_events, ignore_index=True)

    class=class="hl-string">"hl-comment"># Add static and calculated columns
    event_log[class="hl-string">'SourceSystem'] = class="hl-string">'Dynamics 365 SCM OData'
    event_log[class="hl-string">'LastDataUpdate'] = current_time
    event_log[class="hl-string">'SalesOrderNumber'] = event_log[class="hl-string">'LogisticsOrder'] class=class="hl-string">"hl-comment"># Assuming LogisticsOrder is the SalesOrderNumber

    class=class="hl-string">"hl-comment"># Ensure required columns are present
    required_cols = [class="hl-string">'LogisticsOrder', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate']
    for col in required_cols:
        if col not in event_log.columns:
            event_log[col] = None

    class=class="hl-string">"hl-comment"># Sort and save
    event_log = event_log.sort_values(by=[class="hl-string">'LogisticsOrder', class="hl-string">'EventTime'])
    event_log.to_csv(class="hl-string">'d365_scm_logistics_event_log.csv', index=False)
    print(class="hl-string">"Extraction complete. Event log saved to d365_scm_logistics_event_log.csv")

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

WITH
Parameters AS (
    SELECT
        CAST('[Start date]' AS datetime2) AS StartDate,
        CAST('[End date]' AS datetime2) AS EndDate,
        CAST('[Your company or legal entity filter]' AS nvarchar(50)) AS CompanyFilter,
        CAST(SYSUTCDATETIME() AS datetime2) AS RefreshTime
),
CustomerOrderCreated AS (
    SELECT
        s.LogisticsOrder,
        CAST('Customer Order Created' AS nvarchar(100)) AS ActivityName,
        s.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        s.UserId,
        s.SalesOrderNumber,
        s.PurchaseOrderNumber,
        s.ProductionOrderNumber,
        s.SupplierName,
        s.CustomerName,
        s.RequestedDeliveryDate,
        s.IsOnTimeDelivery
    FROM [Your sales order creation source] AS s
    CROSS JOIN Parameters AS p
    WHERE s.EventTime >= p.StartDate
      AND s.EventTime < p.EndDate
      AND s.CompanyCode = p.CompanyFilter
      AND s.IsNewSalesOrder = 1
),
PurchaseRequisitionCreated AS (
    SELECT
        r.LogisticsOrder,
        CAST('Purchase Requisition Created' AS nvarchar(100)) AS ActivityName,
        r.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        r.UserId,
        r.SalesOrderNumber,
        r.PurchaseOrderNumber,
        r.ProductionOrderNumber,
        r.SupplierName,
        r.CustomerName,
        r.RequestedDeliveryDate,
        r.IsOnTimeDelivery
    FROM [Your purchase requisition creation source] AS r
    CROSS JOIN Parameters AS p
    WHERE r.EventTime >= p.StartDate
      AND r.EventTime < p.EndDate
      AND r.CompanyCode = p.CompanyFilter
      AND r.IsCreatedAndSaved = 1
),
PurchaseOrderIssued AS (
    SELECT
        o.LogisticsOrder,
        CAST('Purchase Order Issued' AS nvarchar(100)) AS ActivityName,
        o.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        o.UserId,
        o.SalesOrderNumber,
        o.PurchaseOrderNumber,
        o.ProductionOrderNumber,
        o.SupplierName,
        o.CustomerName,
        o.RequestedDeliveryDate,
        o.IsOnTimeDelivery
    FROM [Your purchase order status source] AS o
    CROSS JOIN Parameters AS p
    WHERE o.EventTime >= p.StartDate
      AND o.EventTime < p.EndDate
      AND o.CompanyCode = p.CompanyFilter
      AND o.PurchaseOrderStatus IN ('Confirmed', 'In external review')
),
RawMaterialsReceived AS (
    SELECT
        g.LogisticsOrder,
        CAST('Raw Materials Received' AS nvarchar(100)) AS ActivityName,
        g.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        g.UserId,
        g.SalesOrderNumber,
        g.PurchaseOrderNumber,
        g.ProductionOrderNumber,
        g.SupplierName,
        g.CustomerName,
        g.RequestedDeliveryDate,
        g.IsOnTimeDelivery
    FROM [Your product receipt posting source] AS g
    CROSS JOIN Parameters AS p
    WHERE g.EventTime >= p.StartDate
      AND g.EventTime < p.EndDate
      AND g.CompanyCode = p.CompanyFilter
      AND g.IsProductReceiptPosted = 1
),
ProductionOrderCreated AS (
    SELECT
        pr.LogisticsOrder,
        CAST('Production Order Created' AS nvarchar(100)) AS ActivityName,
        pr.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        pr.UserId,
        pr.SalesOrderNumber,
        pr.PurchaseOrderNumber,
        pr.ProductionOrderNumber,
        pr.SupplierName,
        pr.CustomerName,
        pr.RequestedDeliveryDate,
        pr.IsOnTimeDelivery
    FROM [Your production order creation source] AS pr
    CROSS JOIN Parameters AS p
    WHERE pr.EventTime >= p.StartDate
      AND pr.EventTime < p.EndDate
      AND pr.CompanyCode = p.CompanyFilter
      AND pr.IsProductionOrderCreated = 1
),
GoodsProduced AS (
    SELECT
        f.LogisticsOrder,
        CAST('Goods Produced' AS nvarchar(100)) AS ActivityName,
        f.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        f.UserId,
        f.SalesOrderNumber,
        f.PurchaseOrderNumber,
        f.ProductionOrderNumber,
        f.SupplierName,
        f.CustomerName,
        f.RequestedDeliveryDate,
        f.IsOnTimeDelivery
    FROM [Your report as finished posting source] AS f
    CROSS JOIN Parameters AS p
    WHERE f.EventTime >= p.StartDate
      AND f.EventTime < p.EndDate
      AND f.CompanyCode = p.CompanyFilter
      AND f.IsReportAsFinishedPosted = 1
),
QualityControlPerformed AS (
    SELECT
        q.LogisticsOrder,
        CAST('Quality Control Performed' AS nvarchar(100)) AS ActivityName,
        q.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        q.UserId,
        q.SalesOrderNumber,
        q.PurchaseOrderNumber,
        q.ProductionOrderNumber,
        q.SupplierName,
        q.CustomerName,
        q.RequestedDeliveryDate,
        q.IsOnTimeDelivery
    FROM [Your quality inspection completion source] AS q
    CROSS JOIN Parameters AS p
    WHERE q.EventTime >= p.StartDate
      AND q.EventTime < p.EndDate
      AND q.CompanyCode = p.CompanyFilter
      AND q.QualityStatus IN ('Completed', 'Validated')
),
WarehousePickListGenerated AS (
    SELECT
        w.LogisticsOrder,
        CAST('Warehouse Pick List Generated' AS nvarchar(100)) AS ActivityName,
        w.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        w.UserId,
        w.SalesOrderNumber,
        w.PurchaseOrderNumber,
        w.ProductionOrderNumber,
        w.SupplierName,
        w.CustomerName,
        w.RequestedDeliveryDate,
        w.IsOnTimeDelivery
    FROM [Your warehouse release or pick work source] AS w
    CROSS JOIN Parameters AS p
    WHERE w.EventTime >= p.StartDate
      AND w.EventTime < p.EndDate
      AND w.CompanyCode = p.CompanyFilter
      AND w.IsReleasedToWarehouse = 1
      AND w.IsPickWorkCreated = 1
),
GoodsPickedAndPacked AS (
    SELECT
        k.LogisticsOrder,
        CAST('Goods Picked and Packed' AS nvarchar(100)) AS ActivityName,
        k.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        k.UserId,
        k.SalesOrderNumber,
        k.PurchaseOrderNumber,
        k.ProductionOrderNumber,
        k.SupplierName,
        k.CustomerName,
        k.RequestedDeliveryDate,
        k.IsOnTimeDelivery
    FROM [Your warehouse picking and packing completion source] AS k
    CROSS JOIN Parameters AS p
    WHERE k.EventTime >= p.StartDate
      AND k.EventTime < p.EndDate
      AND k.CompanyCode = p.CompanyFilter
      AND k.IsPickingCompleted = 1
      AND k.IsPackingUpdated = 1
),
ShipmentScheduled AS (
    SELECT
        h.LogisticsOrder,
        CAST('Shipment Scheduled' AS nvarchar(100)) AS ActivityName,
        h.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        h.UserId,
        h.SalesOrderNumber,
        h.PurchaseOrderNumber,
        h.ProductionOrderNumber,
        h.SupplierName,
        h.CustomerName,
        h.RequestedDeliveryDate,
        h.IsOnTimeDelivery
    FROM [Your load or shipment planning source] AS h
    CROSS JOIN Parameters AS p
    WHERE h.EventTime >= p.StartDate
      AND h.EventTime < p.EndDate
      AND h.CompanyCode = p.CompanyFilter
      AND (h.IsLoadPlanned = 1 OR h.IsShipmentConfirmed = 1)
),
GoodsLoadedForTransport AS (
    SELECT
        l.LogisticsOrder,
        CAST('Goods Loaded For Transport' AS nvarchar(100)) AS ActivityName,
        l.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        l.UserId,
        l.SalesOrderNumber,
        l.PurchaseOrderNumber,
        l.ProductionOrderNumber,
        l.SupplierName,
        l.CustomerName,
        l.RequestedDeliveryDate,
        l.IsOnTimeDelivery
    FROM [Your ship confirmation source] AS l
    CROSS JOIN Parameters AS p
    WHERE l.EventTime >= p.StartDate
      AND l.EventTime < p.EndDate
      AND l.CompanyCode = p.CompanyFilter
      AND l.IsShipConfirmed = 1
),
CustomerInvoicePosted AS (
    SELECT
        i.LogisticsOrder,
        CAST('Customer Invoice Posted' AS nvarchar(100)) AS ActivityName,
        i.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        i.UserId,
        i.SalesOrderNumber,
        i.PurchaseOrderNumber,
        i.ProductionOrderNumber,
        i.SupplierName,
        i.CustomerName,
        i.RequestedDeliveryDate,
        i.IsOnTimeDelivery
    FROM [Your sales invoice posting source] AS i
    CROSS JOIN Parameters AS p
    WHERE i.EventTime >= p.StartDate
      AND i.EventTime < p.EndDate
      AND i.CompanyCode = p.CompanyFilter
      AND i.IsInvoicePosted = 1
),
GoodsDelivered AS (
    SELECT
        d.LogisticsOrder,
        CAST('Goods Delivered' AS nvarchar(100)) AS ActivityName,
        d.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        d.UserId,
        d.SalesOrderNumber,
        d.PurchaseOrderNumber,
        d.ProductionOrderNumber,
        d.SupplierName,
        d.CustomerName,
        d.RequestedDeliveryDate,
        d.IsOnTimeDelivery
    FROM [Your carrier or manual delivery confirmation source] AS d
    CROSS JOIN Parameters AS p
    WHERE d.EventTime >= p.StartDate
      AND d.EventTime < p.EndDate
      AND d.CompanyCode = p.CompanyFilter
      AND d.IsDeliveryConfirmed = 1
),
ProofOfDeliverySigned AS (
    SELECT
        v.LogisticsOrder,
        CAST('Proof of Delivery Signed' AS nvarchar(100)) AS ActivityName,
        v.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        v.UserId,
        v.SalesOrderNumber,
        v.PurchaseOrderNumber,
        v.ProductionOrderNumber,
        v.SupplierName,
        v.CustomerName,
        v.RequestedDeliveryDate,
        v.IsOnTimeDelivery
    FROM [Your proof of delivery source] AS v
    CROSS JOIN Parameters AS p
    WHERE v.EventTime >= p.StartDate
      AND v.EventTime < p.EndDate
      AND v.CompanyCode = p.CompanyFilter
      AND v.IsProofOfDeliverySigned = 1
),
OrderCanceled AS (
    SELECT
        c.LogisticsOrder,
        CAST('Order Canceled' AS nvarchar(100)) AS ActivityName,
        c.EventTime,
        CAST('Microsoft Dynamics 365 SCM' AS nvarchar(100)) AS SourceSystem,
        p.RefreshTime AS LastDataUpdate,
        c.UserId,
        c.SalesOrderNumber,
        c.PurchaseOrderNumber,
        c.ProductionOrderNumber,
        c.SupplierName,
        c.CustomerName,
        c.RequestedDeliveryDate,
        c.IsOnTimeDelivery
    FROM [Your sales order cancellation source] AS c
    CROSS JOIN Parameters AS p
    WHERE c.EventTime >= p.StartDate
      AND c.EventTime < p.EndDate
      AND c.CompanyCode = p.CompanyFilter
      AND c.SalesOrderStatus = 'Canceled'
)
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM CustomerOrderCreated
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM PurchaseRequisitionCreated
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM PurchaseOrderIssued
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM RawMaterialsReceived
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM ProductionOrderCreated
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM GoodsProduced
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM QualityControlPerformed
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM WarehousePickListGenerated
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM GoodsPickedAndPacked
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM ShipmentScheduled
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM GoodsLoadedForTransport
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM CustomerInvoicePosted
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM GoodsDelivered
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM ProofOfDeliverySigned
UNION ALL
SELECT LogisticsOrder, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserId, SalesOrderNumber, PurchaseOrderNumber, ProductionOrderNumber, SupplierName, CustomerName, RequestedDeliveryDate, IsOnTimeDelivery FROM OrderCanceled;