> Continuous Process Improvement > Order to Cash Sales Order Processing > Microsoft Dynamics 365 > Data Template

Data Template: Order to Cash - Sales Order Processing

Microsoft Dynamics 365

Order to Cash - Sales Order Processing Attributes (21) Order to Cash - Sales Order Processing Activities (13) Extraction Guides (2)

Your Order to Cash - Sales Order Processing Data Template

This template provides a structured overview of the essential data elements required to analyze your Order to Cash - Sales Order Processing in Microsoft Dynamics 365. It outlines the crucial attributes to collect and the key activities to track within your event log. Additionally, you will find practical guidance on extracting this data effectively from your system.

Recommended attributes for comprehensive analysis
Key process activities to track
Specific data extraction guidance for Microsoft Dynamics 365

New to event logs? Learn how to create a process mining event log.

Download Template

Order to Cash - Sales Order Processing Attributes

These are the recommended data fields to include in your event log for comprehensive analysis of your Order to Cash - Sales Order Processing in Microsoft Dynamics 365.

5 Required 5 Recommended 11 Optional

	Name	Description
	Activity ActivityName	The name of the specific business event or task that occurred at a point in time within the sales order process.
Description This attribute represents a distinct step or event in the sales order lifecycle, such as 'Sales Order Created', 'Goods Shipped', or 'Payment Received'. The sequence of these activities for a given sales order forms the process flow. Analyzing the sequence, frequency, and transitions between activities is the core of process mining. It helps visualize the process map, identify common and rare process variants, detect bottlenecks, and pinpoint areas of rework or non-compliance. This attribute is fundamental to understanding what is actually happening in the process. Why it matters It defines the steps of the process, making it possible to construct and visualize the process flow, which is the primary goal of process mining. Where to get This attribute is conceptually derived by mapping specific system events or status changes in tables like 'SalesTable' and related logistics or financial tables to a standardized activity name. Examples Sales Order CreatedGoods ShippedInvoice CreatedPayment Received
	Sales Order SalesOrderNumber	The unique identifier for each sales order, serving as the primary case identifier for the process.
Description The Sales Order Number is a unique alphanumeric code assigned to each customer order in Microsoft Dynamics 365. It functions as the core Case ID, linking all related activities and events from creation to closure. In process mining, this attribute is essential for reconstructing the end-to-end journey of every individual sales order. It allows analysts to trace the complete sequence of activities, measure case durations, and analyze variations for each specific order, forming the foundation of the entire process analysis. Why it matters This identifier is crucial for correlating all related events, enabling a complete, end-to-end analysis of each sales order's lifecycle. Where to get Located in the 'SalesTable' table, field 'SalesId'. Examples SO-00102345SO-00102346SO-00102347
	Start Time EventTime	The precise date and time when a specific activity or event occurred.
Description The Event Time, or timestamp, records the exact moment an activity took place. Each activity in the event log has an associated timestamp, creating a chronological record of the process for each case. This attribute is critical for all time-based analysis in process mining. It is used to calculate cycle times between activities, measure the total duration of a case, analyze waiting times, and identify bottlenecks where the process is delayed. It also allows for performance monitoring over time, such as tracking throughput by day, week, or month. Why it matters This timestamp is essential for calculating all duration-based metrics, such as cycle times and bottlenecks, and for ordering events chronologically. Where to get This is derived from various date/time fields associated with specific transactions, such as 'SalesTable.CreatedDateTime' for order creation or payment journal posting dates for payments. Examples 2023-04-15T09:02:11Z2023-04-18T14:30:00Z2023-04-25T11:21:45Z
	Last Data Update LastDataUpdate	The timestamp indicating the last time the data was refreshed or extracted from the source system.
Description This attribute records the date and time of the most recent data pull from Microsoft Dynamics 365. It provides transparency into the freshness of the data being analyzed. For any process analysis, understanding the recency of the data is critical for making informed decisions. This timestamp helps users trust the data by showing exactly when it was last updated, ensuring that conclusions are based on current information. Why it matters It ensures users are aware of the data's freshness, which is critical for the relevance and accuracy of the process mining analysis. Where to get This is generated at the time of data extraction and appended to each record during the data ingestion process. Examples 2023-10-27T02:00:00Z2023-10-28T02:00:00Z
	Source System SourceSystem	Identifies the information system from which the data originates.
Description This attribute specifies the source application where the event data was recorded. In this context, it will typically be 'Microsoft Dynamics 365'. While it may seem redundant in a single-system analysis, it becomes crucial when merging data from multiple systems, such as a separate CRM or a warehouse management system. It ensures data lineage and helps in troubleshooting data extraction issues by pinpointing the origin of the records. Why it matters It provides crucial context about data origin, especially when integrating data from multiple systems, ensuring clear data lineage. Where to get This is a static value, typically added during the data transformation process to label the dataset's origin. Examples Microsoft Dynamics 365 F&OMicrosoft Dynamics 365 Sales
	Confirmed Delivery Date ConfirmedDeliveryDate	The delivery date that the company has confirmed and committed to the customer.
Description The Confirmed Delivery Date is the date that the selling organization promises to the customer for the delivery of goods. This date is set after internal checks, such as inventory availability and production schedules, are completed. This attribute is essential for calculating the 'Delivery Date Adherence Rate' KPI from an operational commitment standpoint. It provides a more realistic internal benchmark for on-time delivery than the customer's initial request. Analyzing deviations from this date helps identify internal process failures in logistics and fulfillment. Why it matters Represents the company's commitment to the customer, making it a critical internal benchmark for measuring fulfillment reliability and operational performance. Where to get Located in the sales order line data, often in the 'SalesLine' table with a field name like 'ConfirmedDlv'. Examples 2023-05-122023-06-012023-05-28
	Customer Name CustomerName	The name of the customer who placed the sales order.
Description This attribute contains the legal name of the customer associated with the sales order. It is derived by linking the customer account number on the sales order to the main customer master data. Analyzing the process by customer is fundamental to understanding customer-specific behaviors and service levels. It helps identify which customers experience the most delays, which ones have the highest rework rates, or which ones follow non-standard process paths. This is vital for improving customer satisfaction and managing key accounts effectively. Why it matters Enables customer-centric analysis to identify patterns, delays, or issues specific to certain customers, directly impacting customer satisfaction. Where to get Looked up from the 'CustTable' using the 'CustAccount' field from the 'SalesTable'. Examples Contoso LtdAdatum CorporationFabrikam Inc.
	Order Value OrderValue	The total monetary value of the sales order.
Description This attribute represents the total financial amount of the sales order, including all items, taxes, and charges. It is a key financial metric associated with each case. Order Value is critical for value-based process analysis. It allows for segmenting the process to see if high-value orders are handled differently or experience more delays than low-value orders. This helps prioritize process improvement efforts on the most financially significant cases and supports dashboards like 'Sales Order Value by Segment'. Why it matters Allows for financial segmentation of the process, helping to prioritize improvements on high-value orders and understand cost implications of process deviations. Where to get Located in the sales order header data. Consult Microsoft Dynamics 365 documentation for the specific table and field, often calculated from sales line amounts. Examples 5250.7512300.00899.50
	Requested Delivery Date RequestedDeliveryDate	The delivery date for the order as requested by the customer.
Description This attribute stores the date when the customer initially requested to receive their goods. This date is captured at the time of order creation and serves as the baseline for measuring delivery performance from the customer's point of view. This date is a critical input for the 'Delivery Date Adherence' dashboard. Comparing the 'RequestedDeliveryDate' with the 'ConfirmedDeliveryDate' and the actual 'Goods Delivered' date reveals how well the organization meets customer expectations. Large gaps can indicate issues with planning, inventory, or logistics. Why it matters Serves as the customer's expectation for delivery, providing a key baseline for measuring customer satisfaction and on-time delivery performance. Where to get Located in the 'SalesTable' table, commonly named 'DeliveryDate' or a similar variant. Examples 2023-05-102023-06-012023-05-25
	Sales Channel SalesChannel	The channel through which the sales order was received, such as Web, Direct Sales, or Partner.
Description The Sales Channel indicates the origin of the customer order. This could be an e-commerce website, a direct sales team, a retail store, a call center, or a partner network. This dimension is often configured based on business needs in Dynamics 365. Analyzing the process by sales channel helps uncover performance differences between channels. For example, web orders might be processed faster and more automatically than orders taken by phone. This insight allows for channel-specific process optimization and resource allocation, supporting dashboards like 'Sales Order Value by Segment'. Why it matters Allows for performance comparison across different sales channels, revealing inefficiencies or best practices specific to how orders are initiated. Where to get This information is typically stored on the sales order header. Consult Microsoft Dynamics 365 documentation for the specific field. Examples WebDirectPartnerRetail
	Country CountryRegion	The country of the customer's shipping address.
Description This attribute indicates the destination country for the sales order shipment. It is derived from the customer's delivery address information stored in Dynamics 365. Analyzing process performance by country is important for identifying regional variations. International shipments may involve additional steps like customs clearance, leading to longer cycle times. This analysis helps in understanding and optimizing logistics for different geographic markets. Why it matters Facilitates geographical analysis, helping to identify regional bottlenecks, compliance issues, or performance variations in the supply chain. Where to get Derived from the customer's delivery address, which is linked to the sales order. The country information is typically in the 'LogisticsPostalAddress' table, joined via the delivery address link on the 'SalesTable'. Examples USADEUCANGBR
	Cycle Time CycleTime	The total time elapsed from the creation of the sales order to its final closure.
Description Cycle Time is a calculated metric that measures the total duration of a process instance. For the sales order process, it is typically the time difference between the 'Sales Order Created' event and the 'Order Closed' event. This is a primary key performance indicator for process efficiency. Dashboards and KPIs like 'Overall Sales Order Cycle Time' rely directly on this calculation. Analyzing its average, median, and distribution helps to quantify process performance, set improvement targets, and measure the impact of optimization initiatives. Why it matters This is a key performance indicator for overall process efficiency, directly measuring the end-to-end time it takes to fulfill a customer order. Where to get Calculated by subtracting the timestamp of the first event (e.g., 'Sales Order Created') from the timestamp of the last event (e.g., 'Order Closed') for each 'SalesOrderNumber'. Examples 10 days 4 hours5 days 11 hours22 days 1 hour
	End Time EndTime	The precise date and time when an activity was completed.
Description The End Time timestamp captures the moment an activity concludes. When available, it provides a more accurate measure of an activity's duration compared to inferring it from the start time of the next activity. In analysis, having both a start and end time allows for the precise calculation of 'Processing Time' for each activity, distinguishing it from 'Waiting Time' between activities. This is invaluable for identifying which specific tasks are time-consuming versus which process steps involve long delays. Why it matters Enables the precise calculation of individual activity processing times, distinguishing active work time from idle waiting time. Where to get Like Start Time, this is derived from various date/time fields. It might be a 'ModifiedDateTime' field or a specific status update timestamp in tables like 'SalesTable' or 'WHSLoadTable'. Examples 2023-04-15T09:12:30Z2023-04-18T14:35:00Z2023-04-25T11:21:55Z
	Is Rework IsRework	A boolean flag indicating if a sales order has experienced rework, such as a repeated activity.
Description This calculated attribute identifies cases that have deviated from a direct, 'happy path' process flow. Rework is detected by identifying sequences of activities that indicate a step was repeated, such as an order being un-confirmed and then re-confirmed, or goods being picked and then returned to stock. Flagging cases with rework is essential for the 'Sales Order Rework Rate' KPI. It allows analysts to quickly isolate and investigate inefficient process flows to understand the root causes of rework, which could be data entry errors, credit issues, or inventory problems. Reducing rework is a primary goal of many process improvement projects. Why it matters Helps to quantify process inefficiency by flagging cases that required repeated steps, enabling targeted analysis to reduce waste and delays. Where to get This is calculated by the process mining tool by analyzing the sequence of activities for each case. For example, detecting a pattern like (A -> B -> C -> B) would flag the case as rework. Examples truefalse
	Item Number ItemNumber	The unique identifier for a product or service on the sales order.
Description The Item Number identifies the specific product being sold. Since a sales order can contain multiple products, this attribute is typically associated with event data at the line-item level. Analyzing the process by product helps uncover product-specific issues. For instance, certain products may be associated with longer fulfillment times, higher rework rates, or more frequent credit holds. This enables targeted improvements in inventory management, product data setup, or fulfillment processes for specific items. Why it matters Allows for product-level analysis, revealing if certain items are associated with process delays, rework, or other inefficiencies. Where to get Located in the 'SalesLine' table, field 'ItemId'. Examples PROD-00123PROD-00548SVC-00045
	On Time Delivery OnTimeDelivery	A boolean flag indicating if the goods were delivered on or before the confirmed delivery date.
Description This calculated attribute compares the timestamp of the 'Goods Delivered' activity with the 'ConfirmedDeliveryDate' for each sales order. It is set to 'true' if the delivery was on time or early, and 'false' if it was late. This flag is the basis for calculating the 'Delivery Date Adherence Rate' KPI. It simplifies analysis by allowing for easy filtering and aggregation of on-time versus late orders. This helps to quickly identify the factors that correlate with late deliveries, such as specific products, customers, regions, or shipping methods. Why it matters Directly measures fulfillment performance against commitment, which is crucial for monitoring customer satisfaction and supply chain reliability. Where to get Calculated by comparing the 'EventTime' of the 'Goods Delivered' activity against the 'ConfirmedDeliveryDate' attribute. Formula: ('Goods Delivered' Timestamp <= ConfirmedDeliveryDate). Examples truefalse
	On Time Payment OnTimePayment	A boolean flag indicating if payment was received on or before the payment due date.
Description This calculated attribute compares the timestamp of the 'Payment Received' activity with the 'PaymentDueDate'. It is set to 'true' if the payment was on time and 'false' if it was late. This flag is the core component of the 'On-Time Payment Rate' KPI. It allows for quick segmentation of customers into 'on-time' and 'late' payers. This analysis can inform credit policies, collection strategies, and customer relationship management by identifying chronically late-paying customers. Why it matters Measures customer payment behavior against agreed terms, which is fundamental for managing cash flow and assessing credit risk. Where to get Calculated by comparing the 'EventTime' of the 'Payment Received' activity against the 'PaymentDueDate' attribute. Formula: ('Payment Received' Timestamp <= PaymentDueDate). Examples truefalse
	Payment Due Date PaymentDueDate	The date by which the customer is required to make payment for the invoice.
Description The Payment Due Date is calculated based on the invoice date and the payment terms agreed upon with the customer. This date is recorded on the customer invoice. This attribute is fundamental for the 'Payment Due Date Compliance' analysis and the 'On-Time Payment Rate' KPI. By comparing the 'PaymentDueDate' with the actual 'Payment Received' date, the business can identify late payments, analyze payment behavior by customer segment, and take proactive measures to improve cash flow and reduce days sales outstanding (DSO). Why it matters This is the benchmark for measuring payment performance, which is critical for analyzing cash flow and managing accounts receivable effectively. Where to get Located in the 'CustInvoiceJour' table, field 'DueDate'. Examples 2023-05-302023-06-152023-06-30
	Sales Order Status SalesOrderStatus	The current status of the sales order at the time of data extraction.
Description This attribute reflects the overall status of the sales order, such as 'Open order', 'Invoiced', 'Canceled', or 'Delivered'. This is a summary status maintained on the sales order header. While the activity log provides a dynamic view of the process, the final status is useful for filtering and segmentation. It allows analysts to easily isolate all open orders for a view of the current workload, or to separate successfully completed orders from canceled ones to analyze the reasons for cancellation. Why it matters Provides a snapshot of the order's state, enabling analysis to be filtered for open, closed, or canceled orders, which is useful for workload management and outcome analysis. Where to get Located in the 'SalesTable' table, field 'SalesStatus'. Examples BackorderDeliveredInvoicedCanceled
	Shipping Method ShippingMethod	The method or carrier used to ship the goods to the customer.
Description This attribute specifies the transportation service used for delivery, such as 'Ground Shipping', 'Air Freight', or the name of a specific carrier. This is selected during order processing based on customer preference, cost, and delivery speed. For the 'Shipping Method Performance' dashboard, this dimension is essential. Analyzing cycle times from 'Goods Packed' to 'Goods Delivered' broken down by shipping method helps identify which carriers are faster, more reliable, or more prone to delays. This insight enables better logistics planning and carrier selection. Why it matters Enables performance analysis of different carriers and shipping options, helping to optimize logistics for cost, speed, and reliability. Where to get This information is typically stored on the sales order header or related fulfillment records. Consult Microsoft Dynamics 365 documentation. Examples FedEx GroundUPS Next Day AirDHL Express
	User Name UserName	The name of the user who executed the activity.
Description This attribute identifies the specific employee or system user responsible for performing a given task, such as confirming an order or creating an invoice. It is typically linked to a user ID in Microsoft Dynamics 365. Analyzing performance by user helps identify training needs, recognize top performers, and ensure proper workload distribution. It is also essential for compliance and audit purposes, allowing for clear accountability for each action taken in the process. Why it matters It enables analysis of process performance by individual or team, helping to identify training opportunities, workload imbalances, and resource-related bottlenecks. Where to get Derived from user ID fields like 'CreatedBy' or 'ModifiedBy' on various transaction tables, which are then joined with the main user table (e.g., 'UserInfo') to get the full name. Examples Alice JohnsonRobert BrownSystem Administrator

Required Recommended Optional

Order to Cash - Sales Order Processing Activities

These are the key process steps and critical milestones to capture in your event log for accurate discovery and analysis of your sales order processing.

6 Recommended 7 Optional

	Activity	Description
	Goods Shipped	This event signifies that the packed goods for the order have been dispatched and have left the warehouse. In Dynamics 365, this is formalized by posting the Packing Slip.
Why it matters This is a critical milestone marking the end of the internal fulfillment process and the start of the delivery phase. It is a key timestamp for calculating on-time shipping performance. Where to get This is a very clear and explicit event captured from the posting date and time of the Packing Slip journal (CustPackingSlipJour). Capture Capture the posting timestamp of the Packing Slip journal. Event type explicit
	Invoice Created	This represents the generation and posting of the sales invoice for the shipped goods or services. This is a core financial transaction that formally records the customer's debt.
Why it matters This activity marks the beginning of the financial settlement part of the process. The time from shipment to invoice creation is critical for the 'Invoice Generation Cycle Time' KPI and impacts cash flow. Where to get This is an explicit financial transaction. The event is captured from the posting date and time of the Sales Invoice journal (CustInvoiceJour). Capture Capture the posting timestamp of the Sales Invoice journal. Event type explicit
	Order Closed	The final status of a successfully processed sales order, indicating that it has been fully shipped, invoiced, and no further transactions are expected. This marks the successful completion of the process.
Why it matters This activity serves as the primary end point for successfully completed cases. It is essential for calculating end-to-end cycle times and throughput. Where to get This is inferred from the status fields on the SalesTable. An order is considered closed when the 'Sales status' is 'Invoiced' and the line statuses are also 'Invoiced'. Capture Infer from SalesTable status fields changing to 'Invoiced'. The timestamp is typically the last related transaction date, like invoicing or payment. Event type inferred
	Order Confirmed	This activity signifies the formal confirmation of the sales order, committing to the delivery of the specified goods or services. In Dynamics 365, this is an explicit user action that generates a confirmation journal.
Why it matters Confirmation is a key milestone that officially starts the fulfillment process. Measuring the time from creation to confirmation reveals front-office processing efficiency. Where to get This is an explicit event captured from the posting date of the Sales Order Confirmation journal (SalesConfirmJour). The timestamp can be linked back to the SalesTable. Capture Capture the posting timestamp of the Sales Order Confirmation journal. Event type explicit
	Payment Received	This activity signifies that the customer's payment for the invoice has been received and applied. This event occurs in the Accounts Receivable module and is linked back to the originating invoice.
Why it matters This is a critical milestone for analyzing the cash conversion cycle. It is essential for measuring the 'On-Time Payment Rate' KPI and identifying delays in payment collection. Where to get This is an explicit event from the Accounts Receivable module. It is captured from the transaction date of the customer payment settlement (CustSettlement) that closes the invoice transaction (CustTrans). Capture Capture the settlement date from the CustSettlement table, linking it back to the invoice and sales order. Event type explicit
	Sales Order Created	This event marks the initial creation of the sales order in the system by a sales representative or through an automated channel. It is captured explicitly when a new record is created and saved in the primary sales order table.
Why it matters This activity is the universal starting point for all sales order cases. It provides the initial timestamp required for calculating the overall sales order cycle time and analyzing throughput. Where to get This is an explicit event captured from the 'Created date and time' field on the SalesTable header record in Microsoft Dynamics 365. Capture Read the creation timestamp from the SalesTable entity. Event type explicit
	Credit Check Performed	Represents the completion of a credit check for the customer associated with the sales order. This may be an automated system check or a manual review, often resulting in a change of the order's credit status.
Why it matters Analyzing the duration and outcomes of credit checks helps identify bottlenecks in the order approval process. Frequent holds or long approval times can significantly delay order fulfillment. Where to get Typically inferred from status changes related to credit management on the SalesTable, such as moving from 'On hold' with a credit reason to 'Open'. It may also be logged in credit management tables if the advanced module is used. Capture Infer from status change history on the SalesTable or related credit hold tables. Event type inferred
	Goods Delivered	Indicates that the shipment has been successfully delivered to the customer's specified address. This information is often updated from an external carrier's system or through a manual confirmation.
Why it matters This activity is crucial for measuring the 'Delivery Date Adherence' KPI and understanding the true customer-facing cycle time. It helps evaluate carrier performance. Where to get This is not natively tracked as an explicit event in standard D365. It is typically inferred by receiving an update from a carrier integration or through a manual status update on the sales order or shipment record. Capture Infer from an integrated carrier feed or a manual status field update. Event type inferred
	Goods Packed	This activity marks the completion of the packing process, where picked items are consolidated and prepared for shipment. In D365, this may coincide with the generation of a packing slip.
Why it matters The time between picking and packing can reveal bottlenecks in packing stations. It's a key sub-process within the overall fulfillment cycle time. Where to get This can be an explicit event from the completion of container packing in the WMS module or inferred from the generation of the Packing Slip journal (CustPackingSlipJour), which often signifies the end of packing. Capture Infer from the completion of packing work or the creation date of the Packing Slip journal. Event type inferred
	Goods Picked	Represents the completion of the physical picking of all items for the order from their warehouse locations. This is typically recorded when a picker finalizes a picking list or work order in the WMS module.
Why it matters Tracking picking completion time is essential for analyzing warehouse efficiency. Delays at this stage directly impact the overall time to ship. Where to get This is an explicit event recorded in the Warehouse Management module. It is captured from the completion timestamp of the warehouse work (WHSWorkTable) related to the sales order picking. Capture Capture the timestamp when the picking 'Work' status is updated to 'Closed'. Event type explicit
	Inventory Reserved	This event indicates that the required inventory for the sales order lines has been physically or automatically reserved in the system. This ensures the items are available for picking and fulfillment.
Why it matters Tracking inventory reservation helps analyze delays between order confirmation and the start of warehouse operations. It is crucial for the 'Inventory Allocation Lead Time' KPI. Where to get This can be inferred from the creation or update of inventory transaction records (InventTrans) linked to the sales order lines, where the status indicates a reservation (e.g., 'On order', 'Reserved physical'). Capture Infer from the timestamp when inventory transactions (InventTrans) for the order are marked as reserved. Event type inferred
	Order Cancelled	This event represents the cancellation of a sales order before it was fully shipped and invoiced. This is an alternative, unsuccessful end to the process.
Why it matters Tracking cancellations helps identify reasons for lost sales or process failures. Analyzing when and why orders are cancelled can lead to process improvements. Where to get This is inferred from the 'Sales status' field on the SalesTable changing to 'Canceled'. The timestamp would be when this status change was logged. Capture Infer from the SalesTable status field changing to 'Canceled'. Event type inferred
	Released To Warehouse	Marks the point when the sales order is formally released to the warehouse for picking and shipping operations. This is a distinct step in environments using the Warehouse Management (WMS) module.
Why it matters This activity separates order processing from physical fulfillment. Analyzing the time an order waits to be released can highlight resource planning or system integration issues. Where to get This is an explicit event captured from the warehouse release records (WHSLoadTable, WHSShipmentTable) associated with the sales order. Capture Capture the creation timestamp of the corresponding warehouse load or shipment. Event type explicit

Recommended Optional

Extraction Guides

How to get your data from Microsoft Dynamics 365

Steps

Configure Export to Azure Data Lake: In your Dynamics 365 Finance and Operations environment, ensure the 'Export to Azure Data Lake' add-in is installed and configured. This involves linking D365 to an Azure subscription and a target Azure Data Lake Storage Gen2 account.
Select Tables for Export: Navigate to the 'Export to Data Lake' workspace within Dynamics 365. Search for and add the following tables to the export configuration: SalesTable, SalesLine, CustConfirmJour, CustPackingSlipJour, CustInvoiceJour, CustTrans, WHSLoadTable, WHSWorkTable, and CustTable. Activate the export for all selected tables.
Set up Azure Synapse Analytics: In the Azure portal, deploy an Azure Synapse Analytics workspace. Grant this workspace the necessary permissions, such as 'Storage Blob Data Contributor', on the Data Lake account where the D365 data is stored.
Create a Serverless SQL Database: Within Synapse Studio, navigate to the 'Data' hub. Create a new serverless SQL database to provide a T-SQL query interface over the data in your lake.
Create Views for D365 Tables: To simplify querying, create SQL views in your serverless database that point to the replicated D365 table data in the data lake. This abstracts the underlying file paths and formats. For example: CREATE VIEW dbo.SalesTable AS SELECT * FROM OPENROWSET(BULK 'path/to/salestable', FORMAT='CSV', ...).
Prepare the SQL Query: Open a new SQL script in Synapse Studio and connect to your serverless database. Copy the complete SQL query provided in this documentation into the script editor.
Customize Query Parameters: Locate the placeholders at the top of the query. Replace '{YourDataAreaId}' with the company code you wish to analyze. Adjust the '{StartDate}' and '{EndDate}' placeholders to define the desired time frame for the extraction.
Execute the Query: Run the complete SQL script. The query will collect events from various tables and union them into a single, standardized event log format.
Review and Export Results: Once the query finishes, review the results in the Synapse Studio results pane to ensure they are complete and correctly formatted. Use the built-in export functionality to save the results as a CSV file.
Prepare for Upload: Open the exported CSV file and verify it is UTF-8 encoded. Confirm that the column headers exactly match the required attributes, for example, SalesOrderNumber, ActivityName, EventTime, etc.
Upload to ProcessMind: Upload the final, validated CSV file to your ProcessMind project for analysis and visualization.

Configuration

Prerequisites: This method requires an active Azure subscription and the 'Export to Azure Data Lake' feature configured for your Dynamics 365 F&O instance. Appropriate permissions are needed in both Azure (to manage Synapse and Storage) and D365 (to configure data exports).
Required D365 Modules: Full event capture, particularly for warehouse activities, requires the use of the Warehouse Management (WMS) module. Core events rely on the Sales and Marketing and Accounts Receivable modules.
Table Selection: It is critical to enable the export for all tables referenced in the query, including SalesTable, SalesLine, CustConfirmJour, CustPackingSlipJour, CustInvoiceJour, CustTrans, WHSLoadTable, WHSWorkTable, and CustTable. Missing tables will result in missing activities.
Date Range Filtering: The query is designed to be filtered by a date range using the {StartDate} and {EndDate} parameters. For initial extractions, it is recommended to use a limited range, such as 3-6 months, to ensure manageable query execution times.
Company (DataAreaId) Filtering: The '{YourDataAreaId}' placeholder must be configured to target a specific legal entity within your D365 environment. The query is not designed for cross-company analysis in a single execution.
Performance Considerations: For very large datasets, query performance on a serverless SQL pool can vary. Consider scheduling the extraction during off-peak hours. For frequent extractions, you may want to materialize the final event log as a new table in the data lake to improve downstream performance.

a Sample Query sql

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'

Steps

Prerequisites and Setup: Ensure you have Python 3.6 or newer installed. Install the necessary Python libraries by running the following command in your terminal: pip install pandas requests msal.
Register an Application in Azure AD: Navigate to the Azure portal and register a new application under Azure Active Directory. This application will be used to authenticate with the Dynamics 365 API. Record the Application (client) ID and Directory (tenant) ID.
Create a Client Secret: Within your newly registered Azure AD application, create a new client secret under 'Certificates & secrets'. Copy the secret's value immediately, as it will not be visible again. This is your CLIENT_SECRET.
Grant API Permissions: In the Azure AD application settings, go to 'API permissions'. Add a permission for 'Dynamics ERP' or 'Dynamics 365 for Finance and Operations'. Select 'Delegated permissions' and grant user_impersonation. Then, add the same permission again but select 'Application permissions' and grant Financials.ReadWrite.All or a more restricted read-only permission if available and sufficient. Grant admin consent for these permissions.
Create an Application User in Dynamics 365: In your Dynamics 365 environment, navigate to 'System administration' > 'Users' > 'Users'. Create a new user and associate it with the Application ID from your Azure AD app registration. Assign a security role, such as 'System administrator' (for full access) or a custom role with read access to the required data entities (SalesOrderHeadersV2, SalesOrderLines, SalesPackingSlipHeaders, SalesInvoiceHeadersV2, etc.).
Configure the Python Script: Open the provided Python script in an editor. Locate the configuration section at the top of the file and replace the placeholder values for D365_URL, TENANT_ID, CLIENT_ID, CLIENT_SECRET, COMPANY_FILTER, START_DATE, and END_DATE with your specific environment details.
Define Entity Mappings (Optional): Review the entity and field names used in the script. If your organization uses customized entities or fields to track certain activities, you may need to adjust the OData queries within the script accordingly.
Execute the Extraction Script: Run the script from your command line using the command python your_script_name.py. The script will authenticate, query the Dynamics 365 OData API for each activity, and process the data. It will print progress updates to the console.
Review the Output File: Upon successful execution, the script will generate a CSV file named d365_order_to_cash_event_log.csv in the same directory. Open this file to inspect the extracted event log for correctness and completeness.
Upload to ProcessMind: The generated CSV file is formatted according to the required schema and is ready for direct upload into ProcessMind for process analysis.

Configuration

D365_URL: The base URL of your Dynamics 365 Finance and Operations environment. It should look like https://[your-d365-environment-name].operations.dynamics.com.
TENANT_ID: The Directory (tenant) ID from your Azure Active Directory instance, found on the overview page of your Azure AD app registration.
CLIENT_ID: The Application (client) ID of the application you registered in Azure AD.
CLIENT_SECRET: The client secret value you generated for the Azure AD application.
COMPANY_FILTER: Specifies the Dynamics 365 legal entity (e.g., 'USMF') from which to extract data. This is a mandatory filter to ensure you are querying the correct company dataset.
START_DATE / END_DATE: Defines the date range for the extraction in 'YYYY-MM-DD' format. For initial analysis, a period of 3 to 6 months is recommended to balance data volume and insight. Be mindful that large date ranges can significantly increase extraction time.
Prerequisites: Access to Azure AD to register an application and grant permissions is required. A Dynamics 365 user account with permissions to create an application user and assign security roles is also necessary. The assigned role must have read access to all entities queried by the script.

a Sample Query python

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()

Data Template: Order to Cash - Sales Order Processing

Your Order to Cash - Sales Order Processing Data Template

Order to Cash - Sales Order Processing Attributes

Order to Cash - Sales Order Processing Activities

Extraction Guides

Azure Data Lake SQL Query via Synapse Analytics

Steps

Configuration

a Sample Query sql

Python Script using Dynamics 365 OData Web API

Steps

Configuration

a Sample Query python

Ready to Get Started?

Optimize Order to Cash - Sales Order Processing Now