Data Template: Order to Cash – Kundenauftragsbearbeitung

WITH SalesOrderBase AS (
    SELECT
        ST.SALESID,
        ST.DATAREAID,
        CT.NAME AS CustomerName,
        ST.SHIPPINGDATECONFIRMED AS ConfirmedDeliveryDate,
        ST.DELIVERYDATE AS RequestedDeliveryDate,
        ST.PURCHORDERFORMNUM AS SalesChannel, -- Example mapping, adjust if you use a different field for Sales Channel
        SUM(SL.LINEAMOUNT) AS OrderValue
    FROM dbo.SALESTABLE ST
    LEFT JOIN dbo.SALESLINE SL ON ST.SALESID = SL.SALESID AND ST.DATAREAID = SL.DATAREAID
    LEFT JOIN dbo.CUSTTABLE CT ON ST.CUSTACCOUNT = CT.ACCOUNTNUM AND ST.DATAREAID = CT.DATAREAID
    WHERE ST.DATAREAID = '{YourDataAreaId}'
      AND ST.CREATEDDATETIME >= '{StartDate}' AND ST.CREATEDDATETIME < '{EndDate}'
    GROUP BY
        ST.SALESID,
        ST.DATAREAID,
        CT.NAME,
        ST.SHIPPINGDATECONFIRMED,
        ST.DELIVERYDATE,
        ST.PURCHORDERFORMNUM
)
-- 1. Sales Order Created
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Sales Order Created' AS ActivityName,
    ST.CREATEDDATETIME AS EventTime,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.SALESTABLE ST ON SO.SALESID = ST.SALESID AND SO.DATAREAID = ST.DATAREAID

UNION ALL

-- 2. Credit Check Performed
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Credit Check Performed' AS ActivityName,
    ST.MODIFIEDDATETIME AS EventTime, -- This assumes a status change date reflects the credit check time
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.SALESTABLE ST ON SO.SALESID = ST.SALESID AND SO.DATAREAID = ST.DATAREAID
WHERE ST.CREDITLIMITTYPE > 0 -- Example condition for a credit check being done. Adjust based on your configuration.

UNION ALL

-- 3. Order Confirmed
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Order Confirmed' AS ActivityName,
    CJ.CONFIRMDATE AS EventTime,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.CUSTCONFIRMJOUR CJ ON SO.SALESID = CJ.SALESID AND SO.DATAREAID = CJ.DATAAREAID

UNION ALL

-- 4. Inventory Reserved (approximated by first line item reservation)
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Inventory Reserved' AS ActivityName,
    MIN(SL.CREATEDDATETIME) AS EventTime,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.SALESLINE SL ON SO.SALESID = SL.SALESID AND SO.DATAREAID = SL.DATAREAID
WHERE SL.RESERVATION > 0 -- Identifies lines with a reservation status
GROUP BY SO.SALESID, SO.OrderValue, SO.CustomerName, SO.SalesChannel, SO.RequestedDeliveryDate, SO.ConfirmedDeliveryDate

UNION ALL

-- 5. Released To Warehouse
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Released To Warehouse' AS ActivityName,
    WLT.CREATEDDATETIME AS EventTime,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.WHSLOADTABLE WLT ON SO.SALESID = WLT.SALESID AND SO.DATAREAID = WLT.DATAAREAID

UNION ALL

-- 6. Goods Picked (approximated by last pick work closure)
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Goods Picked' AS ActivityName,
    MAX(WWT.MODIFIEDDATETIME) AS EventTime,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.WHSWORKTABLE WWT ON SO.SALESID = WWT.ORDERNUM AND SO.DATAREAID = WWT.DATAAREAID
WHERE WWT.WORKTYPE = 'Pick' AND WWT.WORKSTATUS = 'Closed'
GROUP BY SO.SALESID, SO.OrderValue, SO.CustomerName, SO.SalesChannel, SO.RequestedDeliveryDate, SO.ConfirmedDeliveryDate

UNION ALL

-- 7. Goods Packed
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Goods Packed' AS ActivityName,
    PSJ.CREATEDDATETIME AS EventTime, -- Using creation time of packing slip as proxy
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.CUSTPACKINGSLIPJOUR PSJ ON SO.SALESID = PSJ.SALESID AND SO.DATAREAID = PSJ.DATAAREAID

UNION ALL

-- 8. Goods Shipped
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Goods Shipped' AS ActivityName,
    PSJ.DELIVERYDATE AS EventTime,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.CUSTPACKINGSLIPJOUR PSJ ON SO.SALESID = PSJ.SALESID AND SO.DATAREAID = PSJ.DATAAREAID

UNION ALL

-- 9. Goods Delivered (placeholder, often requires custom fields)
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Goods Delivered' AS ActivityName,
    PSJ.DELIVERYDATE AS EventTime, -- Placeholder, using shipment date. True delivery requires external data.
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.CUSTPACKINGSLIPJOUR PSJ ON SO.SALESID = PSJ.SALESID AND SO.DATAREAID = PSJ.DATAAREAID
WHERE 1 = 2 -- This will return no rows unless logic is added to read from a custom field with actual delivery dates

UNION ALL

-- 10. Invoice Created
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Invoice Created' AS ActivityName,
    CIJ.INVOICEDATE AS EventTime,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.CUSTINVOICEJOUR CIJ ON SO.SALESID = CIJ.SALESID AND SO.DATAREAID = CIJ.DATAAREAID

UNION ALL

-- 11. Payment Received (approximated by last settlement date)
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Payment Received' AS ActivityName,
    MAX(CT.SETTLEMENTDATE) AS EventTime,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.CUSTINVOICEJOUR CIJ ON SO.SALESID = CIJ.SALESID AND SO.DATAREAID = CIJ.DATAAREAID
JOIN dbo.CUSTTRANS CT ON CIJ.INVOICEID = CT.INVOICE AND CIJ.DATAAREAID = CT.DATAAREAID
WHERE CT.TRANSTYPE = 'Payment'
GROUP BY SO.SALESID, SO.OrderValue, SO.CustomerName, SO.SalesChannel, SO.RequestedDeliveryDate, SO.ConfirmedDeliveryDate

UNION ALL

-- 12. Order Closed
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Order Closed' AS ActivityName,
    ST.MODIFIEDDATETIME AS EventTime,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.SALESTABLE ST ON SO.SALESID = ST.SALESID AND SO.DATAREAID = ST.DATAREAID
WHERE ST.SALESSTATUS = 4 -- Status for 'Invoiced'

UNION ALL

-- 13. Order Cancelled
SELECT
    SO.SALESID AS SalesOrderNumber,
    'Order Cancelled' AS ActivityName,
    ST.MODIFIEDDATETIME AS EventTime,
    'Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    SO.OrderValue,
    SO.CustomerName,
    SO.SalesChannel,
    SO.RequestedDeliveryDate,
    SO.ConfirmedDeliveryDate
FROM SalesOrderBase SO
JOIN dbo.SALESTABLE ST ON SO.SALESID = ST.SALESID AND SO.DATAREAID = ST.DATAREAID
WHERE ST.SALESSTATUS = 3; -- Status for 'Canceled'

import requests
import pandas as pd
import msal
import json
import datetime

class=class="hl-string">"hl-comment"># --- CONFIGURATION --- 
class=class="hl-string">"hl-comment"># Replace with your D365 environment and Azure AD App details
D365_URL = class="hl-string">"https://[your-d365-environment-name].operations.dynamics.com"
TENANT_ID = class="hl-string">"[Your-Azure-AD-Tenant-ID]"
CLIENT_ID = class="hl-string">"[Your-Azure-AD-Client-ID]"
CLIENT_SECRET = class="hl-string">"[Your-Azure-AD-Client-Secret]"

class=class="hl-string">"hl-comment"># --- EXTRACTION PARAMETERS ---
COMPANY_FILTER = class="hl-string">"[Your-Company-Code]" class=class="hl-string">"hl-comment"># e.g., class="hl-string">'USMF'
START_DATE = class="hl-string">"2023-01-01"
END_DATE = class="hl-string">"2023-12-31"

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

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

def get_access_token():
    class="hl-string">"""Authenticates with Azure AD and returns an access token."""
    app = msal.ConfidentialClientApplication(
        client_id=CLIENT_ID,
        client_credential=CLIENT_SECRET,
        authority=AUTHORITY_URL
    )
    result = app.acquire_token_for_client(scopes=[fclass="hl-string">"{RESOURCE}/.default"])
    if class="hl-string">"access_token" in result:
        return result[class="hl-string">'access_token']
    else:
        print(class="hl-string">"Error acquiring token:", result.get(class="hl-string">"error_description"))
        raise Exception(class="hl-string">"Could not authenticate.")

def execute_odata_query(access_token, entity, select_fields, filter_query, expand_fields=None):
    class="hl-string">"""Executes an OData query against a D365 entity and handles pagination."""
    all_results = []
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json',
        class="hl-string">'OData-MaxVersion': class="hl-string">'4.0',
        class="hl-string">'OData-Version': class="hl-string">'4.0',
        class="hl-string">'Prefer': class="hl-string">'odata.maxpagesize=1000' class=class="hl-string">"hl-comment"># Request pagination
    }
    
    query_url = fclass="hl-string">"{API_ENDPOINT}/{entity}?$select={select_fields}&$filter={filter_query}&cross-company=true"
    if expand_fields:
        query_url += fclass="hl-string">"&$expand={expand_fields}"
    
    print(fclass="hl-string">"Querying {entity}...")
    while query_url:
        try:
            response = requests.get(query_url, headers=headers)
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raise HTTPError for bad responses (4xx or 5xx)
            data = response.json()
            all_results.extend(data.get(class="hl-string">'value', []))
            query_url = data.get(class="hl-string">'@odata.nextLink') class=class="hl-string">"hl-comment"># Get URL for the next page
        except requests.exceptions.HTTPError as err:
            print(fclass="hl-string">"HTTP Error for {entity}: {err}")
            print(fclass="hl-string">"Response content: {err.response.text}")
            return [] class=class="hl-string">"hl-comment"># Return empty list on error
        except json.JSONDecodeError:
            print(fclass="hl-string">"JSON Decode Error for {entity}. Response was not valid JSON.")
            print(fclass="hl-string">"Response text: {response.text}")
            return []

    print(fclass="hl-string">"...found {len(all_results)} records for {entity}.")
    return all_results

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

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

    class=class="hl-string">"hl-comment"># Shared attributes for most queries
    base_select_fields = class="hl-string">"SalesOrderNumber,TotalInvoiceAmount,SalesOrderOriginCode,RequestedShippingDate,ConfirmedShippingDate,CustomerAccount"
    base_expand_fields = class="hl-string">"CustomerV3(CustomerName)"

    class=class="hl-string">"hl-comment"># Common date filter for creation/modification timestamps
    date_filter = fclass="hl-string">"(CreatedDateTime ge {START_DATE}T00:00:00Z and CreatedDateTime le {END_DATE}T23:59:59Z)"

    class=class="hl-string">"hl-comment"># 1. Sales Order Created
    data = execute_odata_query(token, class="hl-string">'SalesOrderHeadersV2', fclass="hl-string">"{base_select_fields},CreatedDateTime", fclass="hl-string">"dataAreaId eq '{COMPANY_FILTER}' and {date_filter}", base_expand_fields)
    for record in data:
        all_events.append({
            class="hl-string">'SalesOrderNumber': record.get(class="hl-string">'SalesOrderNumber'),
            class="hl-string">'ActivityName': class="hl-string">'Sales Order Created',
            class="hl-string">'EventTime': record.get(class="hl-string">'CreatedDateTime'),
            class="hl-string">'OrderValue': record.get(class="hl-string">'TotalInvoiceAmount'),
            class="hl-string">'CustomerName': record.get(class="hl-string">'CustomerV3', {}).get(class="hl-string">'CustomerName'),
            class="hl-string">'SalesChannel': record.get(class="hl-string">'SalesOrderOriginCode'),
            class="hl-string">'RequestedDeliveryDate': record.get(class="hl-string">'RequestedShippingDate'),
            class="hl-string">'ConfirmedDeliveryDate': record.get(class="hl-string">'ConfirmedShippingDate')
        })

    class=class="hl-string">"hl-comment"># 2. Credit Check Performed (based on status change)
    class=class="hl-string">"hl-comment"># This assumes a credit check is reflected by the CreditLimitApprovalStatus. This logic may need adjustment.
    class=class="hl-string">"hl-comment"># We look for orders modified in the period, then check the status.
    modified_date_filter = fclass="hl-string">"(ModifiedDateTime ge {START_DATE}T00:00:00Z and ModifiedDateTime le {END_DATE}T23:59:59Z)"
    data = execute_odata_query(token, class="hl-string">'SalesOrderHeadersV2', fclass="hl-string">"{base_select_fields},ModifiedDateTime,CreditLimitApprovalStatus", fclass="hl-string">"dataAreaId eq '{COMPANY_FILTER}' and {modified_date_filter} and CreditLimitApprovalStatus ne 'NoCheck'", base_expand_fields)
    for record in data:
        all_events.append({
            class="hl-string">'SalesOrderNumber': record.get(class="hl-string">'SalesOrderNumber'),
            class="hl-string">'ActivityName': class="hl-string">'Credit Check Performed',
            class="hl-string">'EventTime': record.get(class="hl-string">'ModifiedDateTime'), class=class="hl-string">"hl-comment"># Using ModifiedDateTime as a proxy
            class="hl-string">'OrderValue': record.get(class="hl-string">'TotalInvoiceAmount'),
            class="hl-string">'CustomerName': record.get(class="hl-string">'CustomerV3', {}).get(class="hl-string">'CustomerName'),
            class="hl-string">'SalesChannel': record.get(class="hl-string">'SalesOrderOriginCode'),
            class="hl-string">'RequestedDeliveryDate': record.get(class="hl-string">'RequestedShippingDate'),
            class="hl-string">'ConfirmedDeliveryDate': record.get(class="hl-string">'ConfirmedShippingDate')
        })

    class=class="hl-string">"hl-comment"># 3. Order Confirmed
    confirmation_date_filter = fclass="hl-string">"(ConfirmationDate ge {START_DATE} and ConfirmationDate le {END_DATE})"
    data = execute_odata_query(token, class="hl-string">'SalesConfirmationHeaders', class="hl-string">"SalesOrderNumber,ConfirmationDate", fclass="hl-string">"dataAreaId eq '{COMPANY_FILTER}' and {confirmation_date_filter}")
    class=class="hl-string">"hl-comment"># We need to get details from the SalesOrderHeader
    order_numbers = class="hl-string">"'" + class="hl-string">"','".join([rec[class="hl-string">'SalesOrderNumber'] for rec in data if rec.get(class="hl-string">'SalesOrderNumber')]) + class="hl-string">"'"
    if order_numbers != class="hl-string">"''":
        header_data = execute_odata_query(token, class="hl-string">'SalesOrderHeadersV2', base_select_fields, fclass="hl-string">"dataAreaId eq '{COMPANY_FILTER}' and SalesOrderNumber in ({order_numbers})", base_expand_fields)
        header_map = {h[class="hl-string">'SalesOrderNumber']: h for h in header_data}
        for record in data:
            header = header_map.get(record.get(class="hl-string">'SalesOrderNumber'))
            if header:
                all_events.append({
                    class="hl-string">'SalesOrderNumber': header.get(class="hl-string">'SalesOrderNumber'),
                    class="hl-string">'ActivityName': class="hl-string">'Order Confirmed',
                    class="hl-string">'EventTime': str(record.get(class="hl-string">'ConfirmationDate')) + class="hl-string">'T12:00:00Z', class=class="hl-string">"hl-comment"># Date only field, adding midday time
                    class="hl-string">'OrderValue': header.get(class="hl-string">'TotalInvoiceAmount'),
                    class="hl-string">'CustomerName': header.get(class="hl-string">'CustomerV3', {}).get(class="hl-string">'CustomerName'),
                    class="hl-string">'SalesChannel': header.get(class="hl-string">'SalesOrderOriginCode'),
                    class="hl-string">'RequestedDeliveryDate': header.get(class="hl-string">'RequestedShippingDate'),
                    class="hl-string">'ConfirmedDeliveryDate': header.get(class="hl-string">'ConfirmedShippingDate')
                })
    
    class=class="hl-string">"hl-comment"># 4 & 5. Inventory Reserved & Released To Warehouse (WMS)
    class=class="hl-string">"hl-comment"># These are complex. This is a proxy based on Warehouse Work creation time.
    work_date_filter = fclass="hl-string">"(WorkCreatedDateTime ge {START_DATE}T00:00:00Z and WorkCreatedDateTime le {END_DATE}T23:59:59Z)"
    data = execute_odata_query(token, class="hl-string">'WarehouseWorkHeaders', class="hl-string">"OrderNumber,WorkCreatedDateTime", fclass="hl-string">"dataAreaId eq '{COMPANY_FILTER}' and {work_date_filter} and WorkType eq 'SalesOrders'")
    for record in data:
        class=class="hl-string">"hl-comment"># Assuming reservation happens just before release to warehouse
        all_events.append({class="hl-string">'SalesOrderNumber': record.get(class="hl-string">'OrderNumber'), class="hl-string">'ActivityName': class="hl-string">'Inventory Reserved', class="hl-string">'EventTime': record.get(class="hl-string">'WorkCreatedDateTime')})
        all_events.append({class="hl-string">'SalesOrderNumber': record.get(class="hl-string">'OrderNumber'), class="hl-string">'ActivityName': class="hl-string">'Released To Warehouse', class="hl-string">'EventTime': record.get(class="hl-string">'WorkCreatedDateTime')})

    class=class="hl-string">"hl-comment"># 6 & 7. Goods Picked & Goods Packed (WMS)
    class=class="hl-string">"hl-comment"># Proxy using Shipment status updates. This logic may need refinement based on specific WMS setup.
    shipment_date_filter = fclass="hl-string">"(ShipmentStatusLastUpdatedDateTime ge {START_DATE}T00:00:00Z and ShipmentStatusLastUpdatedDateTime le {END_DATE}T23:59:59Z)"
    data = execute_odata_query(token, class="hl-string">'WHSShipmentTables', class="hl-string">"SalesOrderNumber,ShipmentStatus,ShipmentStatusLastUpdatedDateTime", fclass="hl-string">"dataAreaId eq '{COMPANY_FILTER}' and {shipment_date_filter}")
    for record in data:
        if record.get(class="hl-string">'ShipmentStatus') in [class="hl-string">'Picked', class="hl-string">'Loaded', class="hl-string">'Shipped']:
             all_events.append({class="hl-string">'SalesOrderNumber': record.get(class="hl-string">'SalesOrderNumber'), class="hl-string">'ActivityName': class="hl-string">'Goods Picked', class="hl-string">'EventTime': record.get(class="hl-string">'ShipmentStatusLastUpdatedDateTime')})
        if record.get(class="hl-string">'ShipmentStatus') in [class="hl-string">'Loaded', class="hl-string">'Shipped']:
            all_events.append({class="hl-string">'SalesOrderNumber': record.get(class="hl-string">'SalesOrderNumber'), class="hl-string">'ActivityName': class="hl-string">'Goods Packed', class="hl-string">'EventTime': record.get(class="hl-string">'ShipmentStatusLastUpdatedDateTime')})

    class=class="hl-string">"hl-comment"># 8. Goods Shipped (Posting Packing Slip)
    packslip_date_filter = fclass="hl-string">"(ShippingDate ge {START_DATE} and ShippingDate le {END_DATE})"
    data = execute_odata_query(token, class="hl-string">'SalesPackingSlipHeaders', class="hl-string">"SalesOrderNumber,ShippingDate", fclass="hl-string">"dataAreaId eq '{COMPANY_FILTER}' and {packslip_date_filter}")
    for record in data:
        all_events.append({class="hl-string">'SalesOrderNumber': record.get(class="hl-string">'SalesOrderNumber'), class="hl-string">'ActivityName': class="hl-string">'Goods Shipped', class="hl-string">'EventTime': str(record.get(class="hl-string">'ShippingDate')) + class="hl-string">'T12:00:00Z'})

    class=class="hl-string">"hl-comment"># 9. Goods Delivered
    class=class="hl-string">"hl-comment"># This is often not a standard field. We use ModifiedDateTime on Packing Slip as a placeholder.
    packslip_mod_date_filter = fclass="hl-string">"(ModifiedDateTime ge {START_DATE}T00:00:00Z and ModifiedDateTime le {END_DATE}T23:59:59Z)"
    data = execute_odata_query(token, class="hl-string">'SalesPackingSlipHeaders', class="hl-string">"SalesOrderNumber,ModifiedDateTime,DeliveryMode", fclass="hl-string">"dataAreaId eq '{COMPANY_FILTER}' and {packslip_mod_date_filter} and DeliveryMode eq '[Your-Delivered-Status-Code]'")
    for record in data:
        all_events.append({class="hl-string">'SalesOrderNumber': record.get(class="hl-string">'SalesOrderNumber'), class="hl-string">'ActivityName': class="hl-string">'Goods Delivered', class="hl-string">'EventTime': record.get(class="hl-string">'ModifiedDateTime')})

    class=class="hl-string">"hl-comment"># 10. Invoice Created
    invoice_date_filter = fclass="hl-string">"(InvoiceDate ge {START_DATE} and InvoiceDate le {END_DATE})"
    data = execute_odata_query(token, class="hl-string">'SalesInvoiceHeadersV2', class="hl-string">"SalesOrderNumber,InvoiceDate", fclass="hl-string">"dataAreaId eq '{COMPANY_FILTER}' and {invoice_date_filter}")
    for record in data:
        all_events.append({class="hl-string">'SalesOrderNumber': record.get(class="hl-string">'SalesOrderNumber'), class="hl-string">'ActivityName': class="hl-string">'Invoice Created', class="hl-string">'EventTime': str(record.get(class="hl-string">'InvoiceDate')) + class="hl-string">'T12:00:00Z'})

    class=class="hl-string">"hl-comment"># 11. Payment Received
    class=class="hl-string">"hl-comment"># This requires looking at customer payments and settling them to invoices, then to sales orders.
    class=class="hl-string">"hl-comment"># This is a simplified proxy using the ClosedDate of the invoice.
    invoice_closed_date_filter = fclass="hl-string">"(ClosedDate ge {START_DATE} and ClosedDate le {END_DATE})"
    data = execute_odata_query(token, class="hl-string">'CustomerInvoices', class="hl-string">"SalesOrderNumber,ClosedDate", fclass="hl-string">"dataAreaId eq '{COMPANY_FILTER}' and {invoice_closed_date_filter}")
    for record in data:
        if record.get(class="hl-string">'ClosedDate') and record.get(class="hl-string">'SalesOrderNumber'):
            all_events.append({class="hl-string">'SalesOrderNumber': record.get(class="hl-string">'SalesOrderNumber'), class="hl-string">'ActivityName': class="hl-string">'Payment Received', class="hl-string">'EventTime': str(record.get(class="hl-string">'ClosedDate')) + class="hl-string">'T12:00:00Z'})

    class=class="hl-string">"hl-comment"># 12 & 13. Order Closed & Order Cancelled
    class=class="hl-string">"hl-comment"># These are statuses on the Sales Order Header.
    status_date_filter = fclass="hl-string">"(ModifiedDateTime ge {START_DATE}T00:00:00Z and ModifiedDateTime le {END_DATE}T23:59:59Z)"
    data = execute_odata_query(token, class="hl-string">'SalesOrderHeadersV2', class="hl-string">"SalesOrderNumber,SalesOrderStatus,ModifiedDateTime", fclass="hl-string">"dataAreaId eq '{COMPANY_FILTER}' and {status_date_filter} and (SalesOrderStatus eq 'Invoiced' or SalesOrderStatus eq 'Canceled')")
    for record in data:
        activity = class="hl-string">''
        if record.get(class="hl-string">'SalesOrderStatus') == class="hl-string">'Invoiced': activity = class="hl-string">'Order Closed'
        if record.get(class="hl-string">'SalesOrderStatus') == class="hl-string">'Canceled': activity = class="hl-string">'Order Cancelled'
        if activity:
            all_events.append({class="hl-string">'SalesOrderNumber': record.get(class="hl-string">'SalesOrderNumber'), class="hl-string">'ActivityName': activity, class="hl-string">'EventTime': record.get(class="hl-string">'ModifiedDateTime')})


    class=class="hl-string">"hl-comment"># --- Final Processing ---
    if not all_events:
        print(class="hl-string">"No events found. Exiting.")
        return

    df = pd.DataFrame(all_events)
    print(fclass="hl-string">"Total events created: {len(df)}")

    class=class="hl-string">"hl-comment"># Drop events without a case ID or timestamp
    df.dropna(subset=[class="hl-string">'SalesOrderNumber', class="hl-string">'EventTime'], inplace=True)
    print(fclass="hl-string">"Events after dropping nulls: {len(df)}")

    class=class="hl-string">"hl-comment"># Fill in missing recommended attributes by merging back header details
    print(class="hl-string">"Enriching data with header attributes...")
    unique_orders = class="hl-string">"'" + class="hl-string">"','".join(df[class="hl-string">'SalesOrderNumber'].unique()) + class="hl-string">"'"
    if unique_orders != class="hl-string">"''":
        header_data_final = execute_odata_query(token, class="hl-string">'SalesOrderHeadersV2', fclass="hl-string">"{base_select_fields}", fclass="hl-string">"dataAreaId eq '{COMPANY_FILTER}' and SalesOrderNumber in ({unique_orders})", base_expand_fields)
        header_df = pd.DataFrame(header_data_final)
        if not header_df.empty:
            header_df = header_df.rename(columns={
                class="hl-string">'TotalInvoiceAmount': class="hl-string">'OrderValue',
                class="hl-string">'SalesOrderOriginCode': class="hl-string">'SalesChannel',
                class="hl-string">'RequestedShippingDate': class="hl-string">'RequestedDeliveryDate',
                class="hl-string">'ConfirmedShippingDate': class="hl-string">'ConfirmedDeliveryDate'
            })
            class=class="hl-string">"hl-comment"># Extract nested customer name
            header_df[class="hl-string">'CustomerName'] = header_df[class="hl-string">'CustomerV3'].apply(lambda x: x.get(class="hl-string">'CustomerName') if isinstance(x, dict) else None)
            df = df.merge(header_df[[class="hl-string">'SalesOrderNumber', class="hl-string">'OrderValue', class="hl-string">'CustomerName', class="hl-string">'SalesChannel', class="hl-string">'RequestedDeliveryDate', class="hl-string">'ConfirmedDeliveryDate']], on=class="hl-string">'SalesOrderNumber', how=class="hl-string">'left', suffixes=(class="hl-string">'', class="hl-string">'_y'))
            class=class="hl-string">"hl-comment"># Fill NaNs from merge
            for col in [class="hl-string">'OrderValue', class="hl-string">'CustomerName', class="hl-string">'SalesChannel', class="hl-string">'RequestedDeliveryDate', class="hl-string">'ConfirmedDeliveryDate']:
                if col + class="hl-string">'_y' in df.columns:
                    df[col] = df[col].fillna(df[col + class="hl-string">'_y'])
                    df.drop(columns=[col + class="hl-string">'_y'], inplace=True)

    class=class="hl-string">"hl-comment"># Add required static columns
    df[class="hl-string">'SourceSystem'] = class="hl-string">'Microsoft Dynamics 365'
    df[class="hl-string">'LastDataUpdate'] = now_utc

    class=class="hl-string">"hl-comment"># Ensure required columns exist
    final_cols = [class="hl-string">'SalesOrderNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate', 
                  class="hl-string">'OrderValue', class="hl-string">'CustomerName', class="hl-string">'SalesChannel', class="hl-string">'RequestedDeliveryDate', class="hl-string">'ConfirmedDeliveryDate']
    for col in final_cols:
        if col not in df.columns:
            df[col] = None
    
    class=class="hl-string">"hl-comment"># Reorder and save
    df = df[final_cols]
    df.to_csv(class="hl-string">'d365_order_to_cash_event_log.csv', index=False)
    print(class="hl-string">"Successfully created d365_order_to_cash_event_log.csv")

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