> Continuous Process Improvement > Supply Chain Management > Microsoft Dynamics 365 SCM > Data Template

Your Supply Chain Management Data Template

Microsoft Dynamics 365 SCM

Supply Chain Management Attributes (22) Supply Chain Management Activities (15) Extraction Guides (4)

Your Supply Chain Management Data Template

This comprehensive data template provides a structured approach to collecting the essential information needed for powerful process mining of your supply chain. It outlines the crucial attributes and activities to track, along with clear guidance on how to extract this data specifically from Microsoft Dynamics 365 SCM. Use this template to ensure your event log is perfectly prepared for insightful analysis.

Recommended attributes to collect for detailed analysis
Key supply chain activities to track across your process
Practical extraction guidance for Microsoft Dynamics 365 SCM

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

Download Template

Supply Chain Management Attributes

These are the recommended data fields to include in your event log, enabling a thorough and accurate analysis of your supply chain management processes.

5 Required 9 Recommended 8 Optional

	Name	Description
	Logistics Order LogisticsOrder	The unique identifier for a specific supply chain fulfillment request, serving as the primary case identifier.
Description The Logistics Order is the unique identifier for the entire logistics process, linking all activities from customer demand to final delivery. This attribute serves as the central thread connecting various sub-processes like procurement, production, and shipping for a specific fulfillment request. In analysis, every event is associated with a Logistics Order, enabling a complete, end-to-end reconstruction of the process flow. This allows for tracking an order's journey across different departments and systems, identifying bottlenecks, and accurately measuring the overall cycle time from start to finish. Why it matters It is the essential key for linking all related process events together, allowing for the end-to-end analysis of the supply chain process for a single fulfillment request. Where to get This is a conceptual identifier, likely derived by combining fields from tables such as SalesTable (Sales Order), WHSLoadTable (Load ID), or InventTransOrigin (Reference ID) to create a unique case ID across different modules. Examples LO-2024-00123LO-2024-00124LO-2024-00125
	Activity ActivityName	The name of the business event or task that occurred at a point in the logistics process.
Description This attribute records the specific business event or task that occurred, such as 'Purchase Order Issued', 'Goods Produced', or 'Shipment Scheduled'. These activities are the fundamental building blocks of the process. Analyzing the sequence, frequency, and duration of these activities forms the core of process mining. It allows for the automatic discovery and visualization of process maps, the detection of deviations from standard procedures, and the identification of frequent or inefficient process steps that are ripe for improvement. Why it matters It defines the steps of the process, making it possible to visualize the process flow, analyze variations, and identify bottlenecks or non-compliant activities. Where to get Derived from business logic based on status changes in various transaction tables, such as SalesTable, PurchTable, ProdTable, or document posting dates from tables like CustInvoiceJour. Examples Purchase Order IssuedGoods ProducedCustomer Invoice PostedGoods Delivered
	Event Time EventTime	The timestamp indicating when the activity occurred.
Description This attribute provides the exact date and time an activity was recorded in the system. It is fundamental for any time-based process analysis, including calculating cycle times, understanding process performance, and identifying bottlenecks. The chronological ordering of events based on this timestamp allows process mining tools to reconstruct the precise sequence of activities as they happened. This is essential for understanding the actual process flow and its dynamics, rather than relying on idealized process models. Why it matters This timestamp is critical for sequencing events correctly and for calculating all time-based metrics, such as cycle times and lead times. Where to get Typically sourced from creation or status change datetime fields in D365 SCM tables. Examples include CreatedDateTime in SalesTable, DeliveryDate in PurchLine, or physical transaction dates in InventTrans. Examples 2023-04-15T09:00:00Z2023-04-18T14:30:00Z2023-05-01T11:22:00Z
	Last Data Update LastDataUpdate	The timestamp when the data was last refreshed from the source system.
Description This attribute indicates the date and time when the data was last extracted or refreshed. It is a critical piece of metadata for any process analysis dashboard. This information helps users understand the recency of the data they are analyzing, ensuring that their conclusions are based on up-to-date information. It is also vital for managing data pipelines and verifying that data loading processes are running as expected and on schedule. Why it matters Informs users about the freshness of the data, which is crucial for making timely and relevant business decisions based on the analysis. Where to get This value is generated and stamped on the dataset by the data extraction or ETL tool at the end of each refresh cycle. Examples 2023-06-10T02:00:00Z2023-06-11T02:00:00Z2023-06-12T02:00:00Z
	Source System SourceSystem	The system of record from which the event data was extracted.
Description This attribute identifies the system or module where the data originated. In a complex supply chain environment, data may come from multiple modules within Dynamics 365 SCM, such as Sales, Procurement, or Warehouse Management. Specifying the source system is crucial for data governance, troubleshooting data inconsistencies, and understanding the context of the recorded activities. It ensures data quality and provides traceability back to the origin of the information, which is especially important in integrated environments. Why it matters Provides context about data origin, which is essential for data validation, troubleshooting, and understanding process variations that cross system boundaries. Where to get This is typically a static value added during the data extraction process to label the records from this specific system. Examples Microsoft Dynamics 365 SCMD365-PRODAX2012-FIN
	Customer Name CustomerName	The name of the customer who placed the sales order.
Description This attribute identifies the customer associated with a sales order. It allows for a customer-centric view of the supply chain process. Analyzing the logistics process by customer can reveal unique fulfillment patterns, preferences, or challenges associated with specific clients. It is particularly useful for key account management and for tailoring service levels, helping to improve customer satisfaction and retention. Why it matters Enables customer-centric analysis, helping to identify which customers experience the most delays and to evaluate service levels for key accounts. Where to get Derived by joining the sales order table (SalesTable) with the customer master table (CustTable) on the customer account number. Examples Alpine Ski HouseAdventure WorksCity Power & Light
	Is On-Time Delivery IsOnTimeDelivery	A flag indicating if the order was delivered on or before the requested date.
Description This is a calculated boolean attribute that indicates whether a logistics order was delivered on time. It is derived by comparing the timestamp of the 'Goods Delivered' activity with the 'Requested Delivery Date'. This attribute is the foundation for calculating the On-Time Delivery Rate KPI and is a primary filter in the On-Time Delivery Performance Monitor dashboard. It simplifies analysis by converting a date comparison into a simple true or false value, making it easy to filter for and count late orders. Why it matters Directly measures customer service performance and is a key input for the On-Time Delivery KPI, simplifying the analysis of delivery reliability. Where to get This is a calculated field. The logic is: IF ('Goods Delivered' timestamp <= 'Requested Delivery Date') THEN True ELSE False. Examples truefalse
	Procurement Lead Time ProcurementLeadTime	The total time taken from creating a purchase requisition to receiving the raw materials.
Description This is a calculated duration metric that measures the efficiency of the procurement process. It is calculated as the time difference between the 'Purchase Requisition Created' activity and the 'Raw Materials Received' activity for a given purchase order. This attribute directly supports the Procurement Lead Time Breakdown dashboard and the Average Procurement Cycle Time KPI. By pre-calculating this value, analysis of procurement performance, especially when segmented by supplier or product category, becomes much more efficient. Why it matters Quantifies the duration of the entire procurement cycle, which is a critical KPI for managing inventory levels and production schedules. Where to get This is a calculated field. The logic is: Timestamp('Raw Materials Received') - Timestamp('Purchase Requisition Created'). Examples 10 days25 days4 hours
	Production Order Number ProductionOrderNumber	The unique identifier for a production or manufacturing order.
Description This number identifies an order to produce a specific quantity of a product within the manufacturing facility. It tracks the consumption of raw materials and the output of finished goods. For businesses with a manufacturing component, this ID is vital for tracking the production process. It supports the Production Schedule Adherence Tracker dashboard by linking scheduled production dates to actual completion dates, highlighting any variances or delays in the manufacturing process. Why it matters It enables detailed analysis of the manufacturing lifecycle, helping to measure production schedule adherence and identify plant-floor bottlenecks. Where to get Located in the ProdId field of the ProdTable table. Examples PRD-000112PRD-000113PRD-000114
	Purchase Order Number PurchaseOrderNumber	The unique identifier for a purchase order sent to a supplier.
Description This is the official document number used to order raw materials or goods from an external supplier. It contains details about the items, quantities, prices, and delivery dates. Analyzing the process by Purchase Order Number is essential for the Procurement Lead Time Breakdown and Supplier Performance Benchmarking dashboards. It allows for tracking the entire procure-to-pay cycle, from requisition to supplier payment, and helps identify delays in material acquisition. Why it matters This ID is crucial for analyzing the procurement portion of the supply chain, monitoring supplier performance, and understanding material lead times. Where to get Located in the PurchId field of the PurchTable table. Examples PO-000541PO-000542PO-000543
	Requested Delivery Date RequestedDeliveryDate	The delivery date requested by the customer.
Description This attribute captures the delivery date that the customer specified when placing their order. It serves as the primary benchmark against which actual delivery performance is measured. This date is the foundation for the On-Time Delivery Performance Monitor dashboard and the On-Time Delivery Rate KPI. Comparing the Requested Delivery Date with the actual delivery date is the standard method for calculating delivery performance and is a key measure of customer satisfaction. Why it matters This is the baseline for measuring on-time delivery performance, a critical KPI for customer satisfaction and supply chain reliability. Where to get Located in the ReceiptDateRequested field on the SalesLine table or ShippingDateRequested on the SalesTable. Examples 2023-05-102023-06-012023-07-20
	Sales Order Number SalesOrderNumber	The unique identifier for a customer's sales order.
Description This is the primary reference number for a customer's request for goods or services. The sales order initiates many downstream supply chain activities, including procurement and production. In process mining, the Sales Order Number is a critical dimension for filtering and analysis. It allows business users to trace the complete fulfillment process for a specific customer order and is often a primary component of the conceptual Logistics Order case ID. Why it matters It links the supply chain process directly to customer demand, enabling analysis of the order-to-cash cycle and customer-specific fulfillment performance. Where to get Located in the SalesId field of the SalesTable table. Examples SO-001872SO-001873SO-001874
	Supplier Name SupplierName	The name of the supplier providing raw materials or goods.
Description This attribute contains the name of the vendor from whom materials are procured. It is a key dimension for analyzing procurement-related activities. It is used extensively in the Procurement Lead Time Breakdown and Supplier Performance Benchmarking dashboards. Analyzing process metrics by supplier helps identify the most reliable and efficient partners, as well as those who are frequently late or cause disruptions. This information is critical for strategic sourcing and supplier relationship management. Why it matters Allows for segmenting and benchmarking procurement performance by supplier, which is crucial for improving supplier relationships and reducing material lead times. Where to get Derived by joining the purchase order table (PurchTable) with the vendor master table (VendTable) on the vendor account number. Examples Contoso Raw MaterialsFabrikam Inc.Northwind Traders
	User ID UserId	The identifier of the user who performed the activity.
Description This attribute stores the ID of the employee or system user who executed a specific activity. This could be a buyer who created a purchase order or a warehouse worker who confirmed a shipment. Analyzing activities by user helps in understanding workload distribution, identifying training needs, and investigating process deviations. It is also key for compliance analysis, allowing auditors to trace actions back to specific individuals. This view is essential for performance management and resource optimization. Why it matters Enables analysis of process performance by user or team, helps identify automation opportunities, and is critical for compliance and audit trail analysis. Where to get Found in 'Created by' or 'Modified by' fields on most transaction tables in D365 SCM, such as CreatedBy on SalesTable or PurchTable. Examples j.doea.smithAX_BATCH_USER
	Carrier Name CarrierName	The name of the transportation company or carrier handling the shipment.
Description This attribute identifies the third-party logistics provider or internal fleet responsible for transporting the goods. It specifies which company is physically moving the product. The carrier is another critical dimension for the Transportation Efficiency & Costs dashboard. By analyzing performance metrics like transit time and on-time delivery by carrier, companies can benchmark their logistics partners, negotiate better rates, and make informed decisions about carrier selection. Why it matters Allows for performance analysis of different logistics partners, which is crucial for carrier management and transportation cost optimization. Where to get Found in fields like 'CarrierService' on sales orders or in dedicated transportation management tables like TMSCarrier. Examples FedExUPSMaerskDHL
	Is Rework IsRework	A flag that indicates if an activity or process loop constitutes rework.
Description This is a calculated boolean attribute that identifies activities or process paths that represent rework or correction loops. For example, if a 'Quality Control Performed' activity fails and leads back to an earlier 'Goods Produced' step, this would be flagged as rework. This attribute is essential for calculating the Order Fulfillment Rework Rate KPI. By flagging rework, analysts can easily quantify the frequency and impact of quality issues or process errors, helping to pinpoint areas that need process improvement and control. Why it matters Helps to quantify process inefficiencies and quality issues by explicitly flagging activities that are not part of the 'happy path'. Where to get This is a calculated field, typically derived using business rules. For instance, flagging specific activity names like 'Repack Goods' as rework, or identifying backward loops in the process flow. Examples truefalse
	Mode Of Transport ModeOfTransport	The method of transportation used for shipment, such as truck, air, or sea.
Description This attribute specifies the transportation method used to move goods from the origin to the destination. Examples include road, rail, air, and sea freight. It is a key dimension for the Transportation Efficiency & Costs dashboard. Analyzing cycle times and costs by mode of transport helps in optimizing logistics strategies, selecting the right balance of speed and cost, and identifying inefficiencies within specific transportation channels. Why it matters Essential for analyzing transportation costs and efficiency, helping to optimize logistics networks and reduce freight expenses. Where to get Found in the ModeOfDelivery field in tables like SalesTable or PurchTable, or in more detail in transportation management tables like TMSRoute. Examples TruckAirSeaRail
	Order Value OrderValue	The total monetary value of the sales order.
Description This attribute represents the total financial value of a customer's sales order. It is a key metric for understanding the business impact of process performance. Analyzing process metrics, such as cycle time or late deliveries, in the context of order value allows for prioritization of improvement efforts. For instance, delays affecting high-value orders may be more critical to address than those affecting smaller orders. This provides a financial dimension to the process analysis. Why it matters Provides financial context to process analysis, allowing for the prioritization of issues based on their monetary impact. Where to get Calculated from the SalesLine table by summing the LineAmount field for all lines belonging to a specific sales order (SalesId). Examples 15200.50850.00125000.75
	Product Category ProductCategory	The category to which a product belongs.
Description This attribute groups individual products into broader categories, such as 'Electronics', 'Raw Materials', or 'Finished Goods'. This allows for a higher-level analysis of the supply chain. Instead of looking at thousands of individual SKUs, analysts can use product categories to identify trends and bottlenecks that affect entire groups of products. This is particularly useful in the Procurement Lead Time Breakdown dashboard for understanding supplier performance across different types of materials. Why it matters Enables aggregated analysis across product groups, which is useful for strategic decision-making and identifying systemic issues affecting certain types of products. Where to get Derived by joining the item master table (InventTable) with product category tables like EcoResProductCategory. Examples Audio ComponentsPackaging MaterialsHydraulic Parts
	Product Number ProductNumber	The unique identifier for the product being ordered or produced.
Description This attribute is the stock keeping unit (SKU) or item number for the product involved in a transaction. It can be a finished good sold to a customer or a raw material purchased from a supplier. Analyzing the supply chain process by product helps to identify items that have long lead times, frequent quality issues, or complex production paths. This information is vital for inventory management, demand forecasting, and product portfolio optimization. Why it matters Allows for analysis of process performance for different products, which can reveal supply chain challenges specific to certain items. Where to get Found in the ItemId field on transaction line tables such as SalesLine, PurchLine, and ProdBOM. Examples A0001D0010M9201
	Scheduled Shipment Date ScheduledShipmentDate	The date on which the shipment was planned to depart.
Description This attribute represents the planned or scheduled date for a shipment to leave the warehouse or production facility. It is a key internal milestone in the fulfillment process. This date is used to calculate the Shipment Schedule Adherence Rate KPI. Comparing the scheduled date with the actual date of the 'Goods Loaded For Transport' activity helps measure the reliability and predictability of internal logistics planning and execution. Why it matters Helps measure internal schedule adherence and the predictability of the shipping process, which impacts downstream transportation planning. Where to get Located in fields like ConfirmedShipDate on SalesLine or related fields in the WHSLoadTable for planned loads. Examples 2023-05-082023-05-302023-07-18
	Warehouse ID WarehouseId	The identifier of the warehouse where goods are stored or handled.
Description This attribute identifies the specific warehouse or distribution center involved in an activity, such as picking, packing, or shipping. It is essential for the Warehouse Operations Throughput dashboard, allowing for performance comparisons between different facilities. Analyzing by warehouse can help identify which locations are most efficient, which are struggling with capacity, and where operational improvements are needed most urgently. Why it matters Allows for performance comparison and bottleneck analysis across different physical locations, supporting warehouse efficiency improvements. Where to get Found in the InventLocationId field in tables related to inventory and warehouse management, such as InventSum and WHSWorkTable. Examples WH-MainWH-EastDC-West

Required Recommended Optional

Supply Chain Management Activities

These are the key process steps and milestones to capture in your event log, ensuring accurate process discovery and performance insights for your supply chain.

8 Recommended 7 Optional

	Activity	Description
	Customer Order Created	Represents the formal creation of a new sales order in Dynamics 365 SCM. This is an explicit event logged with a timestamp when a user saves a new sales order document, initiating the fulfillment process.
Why it matters This activity marks the official start of the order fulfillment cycle. It is the primary anchor for measuring overall lead times, on-time delivery performance, and analyzing order intake patterns. Where to get This is an explicit event recorded in the Sales and marketing module. The creation is logged in the SalesTable (Sales Order Header) with a specific creation timestamp. Capture Event logged upon creation of a Sales Order record in the SalesTable. Event type explicit
	Goods Delivered	The goods have arrived at the customer's destination. This event might be inferred from carrier tracking data or a manual status update, as Dynamics 365 does not have a native 'delivered' transaction.
Why it matters Marks the completion of the physical delivery. It provides the 'Actual Delivery Date' which is essential for calculating on-time performance KPIs and customer-facing metrics. Where to get This is often not a standard field. It may be captured in a custom date field on the sales order or shipment, updated manually or via an integration with a carrier's system. Capture Inferred from a manual update to a delivery date field or via an external carrier API integration. Event type inferred
	Goods Loaded For Transport	The packed goods are physically loaded onto the carrier's vehicle, and the shipment is confirmed in the system. This event corresponds to the 'Ship confirm' action in Dynamics 365.
Why it matters Marks the physical departure of goods from the warehouse. This activity is the starting point for measuring the 'Goods In Transit' duration and for financial recognition of the shipment. Where to get Recorded in the Warehouse or Transportation management modules. The 'Ship confirm' action on a load or shipment (WHSLoadTable) creates a timestamped event. Capture Logged when the 'Ship confirm' action is executed for the associated load or shipment. Event type explicit
	Goods Picked and Packed	Warehouse workers have physically picked the items from inventory and packed them for shipment. This is captured when the picking work is completed and the packing status is updated in the system.
Why it matters Represents the completion of the core warehouse fulfillment task. It's a key measure for the Warehouse Operations Throughput dashboard and for identifying bottlenecks in the pick-and-pack process. Where to get Recorded in the Warehouse management module. This is typically inferred from the status of warehouse work (WHSWorkTable) changing to 'Closed' or by posting a packing slip. Capture Inferred from the status change of warehouse work to 'Closed' or from the posting date of the packing slip. Event type inferred
	Goods Produced	The manufacturing process is complete, and the finished goods are formally reported as finished in the system. This is captured by posting a 'Report as Finished' journal for the production order, which updates inventory.
Why it matters Marks the completion of the production phase and makes the goods available for quality control and shipping. It is a key milestone for measuring production schedule adherence. Where to get Recorded in the Production control module. Posting the 'Report as Finished' journal creates a timestamped inventory transaction and updates the production order status. Capture Logged when a 'Report as Finished' journal is posted for the production order. Event type explicit
	Proof of Delivery Signed	The customer has formally acknowledged receipt of the goods, often by signing a delivery note. This is typically captured by attaching a document or updating a status in the system.
Why it matters This is the final confirmation of successful order fulfillment and the definitive end of the logistics process. It is crucial for resolving disputes and for calculating the total order cycle time. Where to get This is not a standard, discrete transaction. It is often managed by updating a status on the sales order or by using document handling to attach a scanned proof of delivery document. Capture Inferred from a status update or the attachment of a POD document to the sales order or shipment. Event type inferred
	Purchase Order Issued	A formal purchase order is created and confirmed, committing the company to a purchase from a supplier. This event is logged when the PO status is updated to 'Confirmed' or 'In external review'.
Why it matters This is a crucial milestone in procurement and the primary starting point for measuring supplier lead time. Analyzing the time from this event to material receipt helps evaluate supplier performance. Where to get Logged in the Procurement and sourcing module. The confirmation of a purchase order is tracked with status changes and dates in the PurchTable. Capture Logged when a Purchase Order status is updated to 'Confirmed' via a journalizing action. Event type explicit
	Raw Materials Received	The ordered goods or materials from the supplier are physically received at the warehouse. This is recorded via a product receipt posting transaction in Dynamics 365 against the purchase order.
Why it matters This activity marks the end of the supplier lead time and the availability of materials for production or fulfillment. Delays here directly impact downstream schedules and customer delivery dates. Where to get Recorded in the Procurement and sourcing or Inventory management modules. Posting a Product Receipt (VendPackingSlipJour) creates a timestamped transactional record. Capture Event logged when a Product Receipt is posted against the Purchase Order. Event type explicit
	Customer Invoice Posted	A sales invoice is generated from the sales order and posted to the financial ledgers. This is often done at the time of shipment, but the process can vary, making it a distinct activity.
Why it matters This is a critical financial milestone in the order-to-cash cycle. Analyzing the time from shipment to invoicing can reveal delays in the billing process and impact cash flow. Where to get Recorded in the Accounts receivable module. The posting of a sales order invoice creates a record in the CustInvoiceJour table with a specific posting date. Capture Logged when a sales order invoice is posted, creating a CustInvoiceJour record. Event type explicit
	Order Canceled	The customer sales order is canceled before fulfillment is complete. This is captured by a status change on the sales order header to 'Canceled'.
Why it matters Represents a negative process outcome. Analyzing when and why orders are canceled can reveal issues with product availability, lead times, or customer service that need to be addressed. Where to get Inferred from the status field of the SalesTable changing to 'Canceled'. The date of this change can be captured if change tracking or database logging is enabled. Capture Inferred from the sales order status changing to 'Canceled'. Event type inferred
	Production Order Created	A production order is generated to manufacture the finished goods required for the customer's logistics order. This is a discrete event logged in the production control module.
Why it matters Initiates the manufacturing process for make-to-order scenarios. Tracking the time from this event to goods completion is vital for analyzing production cycle times and schedule adherence. Where to get Logged in the Production control module. The creation of a new record in the ProdTable (Production Order table) includes a creation timestamp. Capture Logged upon creation of a new record in the production order table (ProdTable). Event type explicit
	Purchase Requisition Created	A formal request is made to the purchasing department to procure materials needed for the order, typically when stock is insufficient. This is an explicit event logged when a new purchase requisition document is created and saved.
Why it matters This activity initiates the procurement sub-process. Tracking this is key for analyzing the internal approval portion of the procurement lead time and identifying delays before a PO is issued. Where to get Logged in the Procurement and sourcing module. The creation of a record in the PurchReqTable is a discrete event with a timestamp. Capture Event logged upon creation of a record in the purchase requisition table (PurchReqTable). Event type explicit
	Quality Control Performed	A quality inspection is performed on produced or received goods to ensure they meet standards. This is often recorded as a quality order completion or validation in the system.
Why it matters Ensures product quality and process compliance. Identifying skipped quality checks or long inspection times helps mitigate risks and improve the overall process flow. Where to get Recorded in the Inventory management module via Quality Orders. The validation or closing of a quality order (InventQualityOrderTable) associated with the item provides this event. Capture Logged upon validation or closing of a Quality Order linked to the item lot or serial number. Event type explicit
	Shipment Scheduled	A specific date and carrier are assigned for the shipment of the packed goods. This is often recorded when a load is planned or a shipment is confirmed in the transportation module.
Why it matters Provides a baseline for measuring shipment schedule adherence. Comparing this scheduled date to the actual loading date is a key performance indicator for logistics planning. Where to get Recorded in the Transportation management (TMS) module. The confirmation of a load (WHSLoadTable) or shipment often includes a scheduled ship date that can be captured. Capture Logged upon confirmation of a transportation load or shipment record. Event type explicit
	Warehouse Pick List Generated	A pick list or warehouse work is created, instructing staff which items to pick to fulfill the order. This event is logged when an order is released to the warehouse for processing.
Why it matters This activity initiates the physical warehouse fulfillment operations. Analyzing the time from this point to packing completion helps measure internal warehouse efficiency and responsiveness. Where to get Logged in the Warehouse management module. The creation of warehouse work (WHSWorkTable) or posting of a picking list journal (WMSPickingRoute) is a discrete event. Capture Logged upon generation of a picking list journal or creation of warehouse work. Event type explicit

Recommended Optional

Extraction Guides

How to get your data from Microsoft Dynamics 365 SCM

Steps

Navigate to Data Management Workspace: Log in to Dynamics 365 SCM. Use the search bar to find and navigate to the Data management workspace.
Create a New Export Project: In the Data management workspace, select the Export tile. On the following page, select New to create a new export project.
Define Project Details: Provide a Group name (e.g., SCM_EventLog_Export), a Description, and specify the Data project operations type as Export. Choose the Project category as Project.
Add Required Data Entities: In the Selected entities section, select Add entity. Search for and add each of the required data entities to the project. You will need to add one entity for each source of event data. See the Query section for a complete list of recommended entities.
Configure Entity Export Options: For each entity you added, select a Target data format (CSV is recommended). You can also apply filters to limit the data. Select the Filter option for each entity and add range criteria, for example, on the CreatedDateTime field to extract data for the last 3 to 6 months. It is critical to filter by the primary company or legal entity.
Sequence Entities (Optional): You can define an execution sequence for the entities if there are dependencies, but for this event log extraction, they can typically run in parallel.
Save and Run the Export Project: Save the project configuration. To execute the export, select Export from the action pane. You can run it immediately or schedule it to run in batch mode during off-peak hours.
Monitor the Export Job: Navigate to the Job history section to monitor the progress of your export job. Wait for the status to show as Succeeded.
Download the Data Package: Once the job is complete, select Download package from the Job history page. This will download a single ZIP file containing separate CSV files for each data entity.
Combine and Transform Data: Unzip the package. You will have multiple files. Use an external tool like Power Query, a Python script, or another ETL tool to perform the following transformations:
- Open each CSV file.
- Map the source columns from each file to the standardized event log structure (LogisticsOrder, ActivityName, EventTime, etc.).
- For each file, add a new column for ActivityName and populate it with the static activity name (e.g., 'Customer Order Created' for all rows from the Sales Order entity).
- Union all the transformed data from all files into a single table.
- Calculate derived attributes like ProcurementLeadTime if required.
Export Final Event Log: Save the combined and transformed data as a single CSV file. Ensure the column headers match the required attributes for your process mining tool. This file is now ready for upload.

Configuration

Data Entities: The core of the configuration is selecting the correct data entities. Key entities include Sales order headers V2, Purchase order headers V2, Production orders V3, Product receipt headers, Sales invoice headers V2, Warehouse work headers, and Shipments.
Target Data Format: CSV is the recommended format due to its simplicity and broad compatibility with post-processing tools.
Filtering Strategy: Always apply filters to manage data volume. The two most important filters are:
- Company/Legal Entity: Filter each entity by the specific legal entity (e.g., DAT.DataAreaId = 'usmf').
- Date Range: Apply a date range filter on a primary date field, such as CreatedDateTime on the Sales order headers V2 entity, to extract a relevant period (e.g., the last 6 months).
Incremental vs. Full Export: For the initial extraction, a full export is necessary. For subsequent delta loads, you can configure incremental exports, but this adds complexity to the post-processing logic.
Prerequisites: Users configuring and running the export job need appropriate permissions, such as the Data management administrator security role. The organization must also be using the relevant D365 SCM modules, including Sales and marketing, Procurement and sourcing, Production control, and Warehouse management.

a Sample Query config

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Steps

Configure Bring Your Own Database (BYOD): First, ensure the BYOD feature is configured in your Dynamics 365 SCM environment. This involves setting up an Azure SQL Database and configuring a data connection from D365 to this database. This is a prerequisite and is typically performed by a system administrator.
Identify and Publish Data Entities: Log into your Dynamics 365 SCM instance. Navigate to the Data Management workspace. Identify the standard data entities required for this extraction, such as SalesOrderHeadersV2, PurchaseOrderHeaders, ProductionOrders, and others listed in the query. For each entity, select 'Publish' to start the synchronization process to your BYOD database.
Schedule and Monitor Data Export: Configure an export job in the Data Management workspace to schedule regular, incremental updates of the published entities to your BYOD. Monitor the job history to ensure data is synchronizing successfully and without errors.
Connect to the Azure SQL Database: Use a SQL client, like SQL Server Management Studio (SSMS) or Azure Data Studio, to connect to the Azure SQL Database that you configured for BYOD. You will need the server name, database name, and appropriate credentials.
Prepare the SQL Query: Copy the complete SQL query provided in this document. You will need to modify the placeholders for the company ID, e.g., '[YourD365Company]', and adjust the date range filters in the WHERE clauses to match your desired analysis period.
Execute the Query: Run the modified SQL query against your BYOD. The query is designed to gather data from multiple tables and unify it into a single event log format, with one row per business activity.
Calculate Complex Attributes: The initial query gathers the core events. The final SELECT statement calculates derived attributes like IsOnTimeDelivery and ProcurementLeadTime by using window functions over the event data for each LogisticsOrder.
Review and Validate Results: Examine the output of the query. Check for expected row counts, ensure all required columns are populated, and verify that the timestamps and activity names appear correct. Refer to the validation steps for more detail.
Export to CSV: Once you are satisfied with the results, export the final dataset from your SQL client to a CSV file. This file will serve as the event log for upload into your process mining tool.

Configuration

BYOD Configuration: The Bring Your Own Database feature must be enabled and fully configured. This requires an active Azure subscription and an Azure SQL Database instance.
Data Entities: Ensure the following key entities are published and successfully synchronizing to your BYOD: SalesOrderHeadersV2, SalesOrderLines, PurchaseOrderHeadersV2, PurchaseRequisitionLines, PurchReceiptsJournalHeader, ProductionOrders, QualityOrders, WarehouseWorkHeaders, SalesPackingSlipHeaders, SalesInvoiceHeadersV2, and potentially others depending on your specific D365 SCM configuration.
Date Range: The provided query includes a placeholder WHERE [TimestampField] >= 'YYYY-MM-DD' for filtering. It is recommended to start with a recent period of 3 to 6 months to ensure manageable data volume and faster query execution.
Company Filter: The query uses a WHERE DATACOMPANYID = '[YourD365Company]' clause. This is critical for environments with multiple legal entities. Replace the placeholder with the correct company ID you wish to analyze.
Permissions: The user executing the query against the Azure SQL Database requires read permissions on the tables corresponding to the published D365 entities.

a Sample Query sql

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

Steps

Prerequisites and Environment Setup: Ensure you have Python 3.6 or newer installed. Install the necessary libraries using pip: pip install requests pandas adal. The adal library simplifies Azure Active Directory authentication, although msal is a newer alternative.
Register 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.
Configure API Permissions: In the newly created Azure AD application, go to 'API permissions' and add permissions for 'Dynamics ERP'. Grant the 'AX.FullAccess' or a more restrictive custom permission to allow data reading via OData. Grant admin consent for the permissions.
Create a Client Secret: Under 'Certificates & secrets' for your Azure AD application, create a new client secret. Securely copy the secret value, as you will need it for the script. Also, note down the Application (client) ID and the Directory (tenant) ID.
Configure Python Script Variables: Open the provided Python script and update the placeholder variables at the top. You will need to input your Dynamics 365 environment URL, Tenant ID, Client ID, Client Secret, the company ID (dataAreaId), and the desired start and end dates for the data extraction.
Execute the Authentication Function: The script includes a function get_access_token that uses the configured credentials to request an OAuth2 access token from Azure AD. This token will be used in the header of all subsequent API requests.
Fetch Data for Each Activity: The main part of the script iterates through different business processes. For each activity, like 'Customer Order Created', it constructs a specific OData API request URL. This URL targets a Data Entity (e.g., SalesOrderHeadersV2) and includes $select to specify columns and $filter to limit data by date range and status.
Handle API Pagination: The script includes a loop to handle paginated results from the API. Dynamics 365 returns a limited number of records per request and provides an @odata.nextLink in the response to get the next page. The script follows these links until all data for the query is retrieved.
Transform and Union Data: For each API response, the script transforms the raw JSON data into a structured pandas DataFrame. It renames columns to match the required schema (LogisticsOrder, ActivityName, EventTime, etc.) and sets the correct data types. These individual DataFrames are then appended to a master list.
Create the Final Event Log: After fetching data for all 15 activities, the script concatenates the list of individual DataFrames into a single, comprehensive event log. It then calculates any derived attributes like IsOnTimeDelivery.
Sort and Export: The final DataFrame is sorted by LogisticsOrder and EventTime to ensure chronological order within each case. The script then saves the event log as a CSV file named d365_scm_logistics_event_log.csv, ready for upload to a process mining tool.

Configuration

Authentication Credentials: The script requires several credentials from your Azure AD application registration:
- TENANT_ID: The Directory (tenant) ID of your Azure instance.
- CLIENT_ID: The Application (client) ID of your registered app.
- CLIENT_SECRET: The client secret generated for the application.
Dynamics 365 Environment:
- D365_URL: The base URL of your Dynamics 365 SCM environment (e.g., https://[your-instance].operations.dynamics.com).
- COMPANY_ID: The dataAreaId for the legal entity you want to extract data from (e.g., 'USMF').
Date Range Filters:
- START_DATE and END_DATE: These variables define the time window for the extraction. It is recommended to start with a smaller range, such as 3-6 months, to manage the data volume and API response times.
Data Entities: The script queries multiple standard Data Entities. Ensure the service principal (Azure AD App) has read permissions for entities such as:
- SalesOrderHeadersV2
- PurchaseRequisitionHeaders
- PurchaseOrderHeaders
- ProductReceiptHeaders
- ProductionOrders
- QualityOrders
- WarehouseWorkHeaders
- SalesShipmentHeaders
- SalesInvoiceHeadersV2
API Performance: Be aware of OData API throttling limits imposed by Dynamics 365. For very large datasets, consider running the script during off-peak hours or implementing incremental extraction logic.

a Sample Query python

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

class=class="hl-string">"hl-comment"># --- Configuration Settings --- #
D365_URL = class="hl-string">"https://[your-instance].operations.dynamics.com" class=class="hl-string">"hl-comment"># Your D365 Finance and Operations URL
TENANT_ID = class="hl-string">"[Your-Tenant-ID]"
CLIENT_ID = class="hl-string">"[Your-Client-ID]"
CLIENT_SECRET = class="hl-string">"[Your-Client-Secret]"
COMPANY_ID = class="hl-string">"[Your-Company-ID]" class=class="hl-string">"hl-comment"># e.g., class="hl-string">'USMF'
START_DATE = class="hl-string">'2023-01-01T00:00:00Z'
END_DATE = class="hl-string">'2023-12-31T23:59:59Z'
class=class="hl-string">"hl-comment"># --- End of Configuration --- #

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Steps

Navigate to Data Management Workspace: Log in to Dynamics 365 SCM. Use the search bar to find and navigate to the Data management workspace.
Create a New Export Project: In the Data management workspace, select the Export tile. On the following page, select New to create a new export project.
Define Project Details: Provide a Group name (e.g., SCM_EventLog_Export), a Description, and specify the Data project operations type as Export. Choose the Project category as Project.
Add Required Data Entities: In the Selected entities section, select Add entity. Search for and add each of the required data entities to the project. You will need to add one entity for each source of event data. See the Query section for a complete list of recommended entities.
Configure Entity Export Options: For each entity you added, select a Target data format (CSV is recommended). You can also apply filters to limit the data. Select the Filter option for each entity and add range criteria, for example, on the CreatedDateTime field to extract data for the last 3 to 6 months. It is critical to filter by the primary company or legal entity.
Sequence Entities (Optional): You can define an execution sequence for the entities if there are dependencies, but for this event log extraction, they can typically run in parallel.
Save and Run the Export Project: Save the project configuration. To execute the export, select Export from the action pane. You can run it immediately or schedule it to run in batch mode during off-peak hours.
Monitor the Export Job: Navigate to the Job history section to monitor the progress of your export job. Wait for the status to show as Succeeded.
Download the Data Package: Once the job is complete, select Download package from the Job history page. This will download a single ZIP file containing separate CSV files for each data entity.
Combine and Transform Data: Unzip the package. You will have multiple files. Use an external tool like Power Query, a Python script, or another ETL tool to perform the following transformations:
- Open each CSV file.
- Map the source columns from each file to the standardized event log structure (LogisticsOrder, ActivityName, EventTime, etc.).
- For each file, add a new column for ActivityName and populate it with the static activity name (e.g., 'Customer Order Created' for all rows from the Sales Order entity).
- Union all the transformed data from all files into a single table.
- Calculate derived attributes like ProcurementLeadTime if required.
Export Final Event Log: Save the combined and transformed data as a single CSV file. Ensure the column headers match the required attributes for your process mining tool. This file is now ready for upload.

Configuration

Data Entities: The core of the configuration is selecting the correct data entities. Key entities include Sales order headers V2, Purchase order headers V2, Production orders V3, Product receipt headers, Sales invoice headers V2, Warehouse work headers, and Shipments.
Target Data Format: CSV is the recommended format due to its simplicity and broad compatibility with post-processing tools.
Filtering Strategy: Always apply filters to manage data volume. The two most important filters are:
- Company/Legal Entity: Filter each entity by the specific legal entity (e.g., DAT.DataAreaId = 'usmf').
- Date Range: Apply a date range filter on a primary date field, such as CreatedDateTime on the Sales order headers V2 entity, to extract a relevant period (e.g., the last 6 months).
Incremental vs. Full Export: For the initial extraction, a full export is necessary. For subsequent delta loads, you can configure incremental exports, but this adds complexity to the post-processing logic.
Prerequisites: Users configuring and running the export job need appropriate permissions, such as the Data management administrator security role. The organization must also be using the relevant D365 SCM modules, including Sales and marketing, Procurement and sourcing, Production control, and Warehouse management.

a Sample Query config

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Your Supply Chain Management Data Template

Your Supply Chain Management Data Template

Supply Chain Management Attributes

Supply Chain Management Activities

Extraction Guides

Dynamics 365 Data Management Framework Export

Steps

Configuration

a Sample Query config

Direct SQL Query via Bring Your Own Database (BYOD)

Steps

Configuration

a Sample Query sql

Python Script Extraction via Dynamics 365 OData API

Steps

Configuration

a Sample Query python

Power Platform Dataflows for SCM Data Extraction

Steps

Configuration

a Sample Query config

Ready to Get Started?

Master Supply Chain Management: Start Optimizing Today

Your Supply Chain Management Data Template

Your Supply Chain Management Data Template

Supply Chain Management Attributes

Supply Chain Management Activities

Extraction Guides

Dynamics 365 Data Management Framework Export

Steps

Configuration

a Sample Query config

Direct SQL Query via Bring Your Own Database (BYOD)

Steps

Configuration

a Sample Query sql

Python Script Extraction via Dynamics 365 OData API

Steps

Configuration

a Sample Query python

Power Platform Dataflows for SCM Data Extraction

Steps

Configuration

a Sample Query config

We value your privacy

Ready to Get Started?

Master Supply Chain Management: Start Optimizing Today