> Continuous Process Improvement > Purchase to Pay Purchase Order > Microsoft Dynamics 365 > Data Template

Data Template: Purchase to Pay - Purchase Order

Microsoft Dynamics 365

Purchase to Pay - Purchase Order Attributes (20) Purchase to Pay - Purchase Order Activities (14) Extraction Guides (4)

Your Purchase to Pay - Purchase Order Data Template

This template offers a clear roadmap for collecting the essential data needed to optimize your Purchase to Pay, Purchase Order process. It outlines the crucial attributes to gather, the key activities to track, and provides practical guidance on extracting this information from your source system. Use this resource to ensure your data is ready for comprehensive process analysis.

Recommended attributes to collect
Key activities to track for process mapping
Practical data extraction guidance

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

Download Template

Purchase to Pay - Purchase Order Attributes

These are the recommended data fields to include in your event log for a comprehensive analysis of your Purchase to Pay, Purchase Order process.

5 Required 5 Recommended 10 Optional

	Name	Description
	Activity Name ActivityName	The name of the specific business event or step that occurred within the purchase order lifecycle.
Description This attribute describes a single step in the purchase order process, such as 'Purchase Order Created', 'Purchase Order Approved', or 'Goods Receipt Posted'. The sequence of these activities forms the process flow for each purchase order. Analyzing activities is the core of process mining. It allows for the visualization of the process map, discovery of process variants, and identification of activities that are frequently repeated or cause delays. Understanding the sequence and frequency of activities is essential for process optimization. Why it matters This attribute is essential for building the process map and understanding the sequence of events that make up the purchase order lifecycle. Where to get Derived from business logic based on status changes in tables like `PurchTable`, `PurchReqTable`, and related posting journals like `VendPackingSlipJour` or `VendInvoiceJour`. Examples Purchase Order CreatedPurchase Order ApprovedGoods Receipt PostedPurchase Order Invoiced
	Event Time EventTime	The precise date and time when a specific activity or event occurred.
Description This timestamp records when each activity in the purchase order process took place. It is the chronological backbone of the process, allowing events to be ordered correctly. In process analysis, event timestamps are fundamental for calculating cycle times, durations between activities, and overall case duration. They are used to identify bottlenecks, measure performance against SLAs, and understand the temporal dynamics of the process. For example, it is used to calculate the time between 'Purchase Order Created' and 'Purchase Order Approved'. Why it matters Timestamps are critical for calculating all time-based performance metrics, such as cycle times and durations, which are essential for identifying process bottlenecks. Where to get Extracted from various date/time fields across multiple tables, such as `CreatedDateTime` on `PurchTable`, or posting dates from related journal tables. Examples 2023-10-26T10:00:00Z2023-10-26T14:35:10Z2023-11-05T09:12:00Z
	Purchase Order PurchaseOrderNumber	The unique identifier for the Purchase Order, serving as the primary case for process analysis.
Description The Purchase Order number is the central identifier that links all related activities, from the initial draft to final completion or cancellation. Each unique number represents a single instance of the purchase order process. In process mining, this attribute is used to reconstruct the end-to-end journey of every purchase order. Analyzing the process based on this identifier allows for a detailed view of the entire lifecycle, helping to identify common paths, deviations, and bottlenecks for individual orders. Why it matters It is the fundamental key for reconstructing the process flow, enabling the analysis of each purchase order's journey from beginning to end. Where to get This is the primary key in the purchase order header table, typically `PurchTable` with the field name `PurchId` in Microsoft Dynamics 365. Examples PO-001245PO-001246PO-001247
	Last Data Update LastDataUpdate	The timestamp indicating the last time the data for this process was refreshed.
Description This attribute records the date and time of the most recent data extraction from the source system. It provides context on the freshness of the data being analyzed. Knowing the last update time is important for users to understand if they are viewing the most current process data. It helps in assessing the relevance of the analysis and in scheduling regular data refreshes. Why it matters Ensures transparency about the timeliness of the data, allowing users to know how up-to-date their process analysis is. Where to get This is a metadata attribute generated and stored during the data ingestion process. Examples 2024-05-21T05:00:00Z
	Source System SourceSystem	Indicates the system from which the data was extracted.
Description This attribute identifies the source application where the purchase order data originates. For this data model, the value will typically be 'Microsoft Dynamics 365'. In larger organizations, procurement processes might span multiple systems. This attribute helps in data governance and ensures that the origin of the data is clear, which is particularly important when merging data from different sources. Why it matters Provides essential context about the data's origin, which is crucial for data governance, validation, and understanding the technological landscape of the process. Where to get This is a static value added during the data extraction and transformation process to label the dataset. Examples Microsoft Dynamics 365 F&OD365
	PO Status PurchaseOrderStatus	The current status of the purchase order in its lifecycle.
Description This attribute indicates the overall state of the purchase order at a given point in time, such as 'Open order', 'Received', 'Invoiced', or 'Canceled'. It represents the outcome of the latest activity. Tracking the status is useful for understanding the current state of all open purchase orders. In process mining, it can be used to analyze the outcomes of cases, for example, by filtering for all purchase orders that ended in a 'Canceled' state to investigate the reasons why. Why it matters Provides a snapshot of the purchase order's current state, which is useful for filtering cases and analyzing process outcomes, such as completion or cancellation rates. Where to get Found in the `PurchTable`. The primary status fields are `DocumentState` and `PurchStatus`. Examples Open orderReceivedInvoicedCanceled
	PO Total Amount PurchaseOrderTotalAmount	The total monetary value of the purchase order.
Description This attribute represents the total cost of all items and services included in the purchase order. It is a key financial metric for the procurement process. Analyzing the process by total amount can reveal important insights. For instance, higher-value purchase orders may follow a different, more stringent approval path or experience longer cycle times. It is also used in financial reporting and for categorizing POs into value bands for analysis. Why it matters Allows for financial analysis of the procurement process, helping to identify how order value impacts process behavior, such as approval times and pathways. Where to get This value can be calculated from the `PurchLine` table by summing the `LineAmount` for a given `PurchId`, or found in header-level amount fields on `PurchTable`. Examples 5250.00120.50150000.00
	Requested Delivery Date RequestedDeliveryDate	The date on which the business requested the vendor to deliver the goods or services.
Description This date is specified on the purchase order and communicates the desired delivery timeline to the vendor. It serves as a baseline for measuring vendor delivery performance. This attribute is essential for the 'Vendor Delivery Adherence' dashboard and the 'On-Time Delivery Rate' KPI. By comparing the `RequestedDeliveryDate` with the actual goods receipt date, businesses can quantify vendor reliability and identify chronic delays in the supply chain. Why it matters It is the baseline for measuring on-time delivery performance, which is a critical KPI for assessing vendor reliability and supply chain efficiency. Where to get Typically found on the `PurchTable` (header level) or `PurchLine` (line item level) as `DeliveryDate`. Examples 2023-11-152023-12-012024-01-20
	User Name UserName	The name of the user who performed a specific activity.
Description This attribute identifies the individual responsible for executing an event, such as creating, approving, or changing a purchase order. It can be a system user ID or a full name. Analyzing user activity helps to understand workload distribution, identify training needs, and pinpoint individuals or teams involved in process deviations. It is essential for dashboards related to approver performance and can be used to filter the process for activities performed by specific users. Why it matters It enables performance analysis by user, helps identify bottlenecks related to specific individuals, and provides accountability for process steps. Where to get Can be found in fields like `CreatedBy` or `ModifiedBy` on tables such as `PurchTable`. User details are typically stored in `UserInfo` table. Examples Alice JohnsonBob WilliamsSysAdmin
	Vendor Name VendorName	The name of the supplier or vendor for whom the purchase order is created.
Description This attribute contains the name of the external supplier providing the goods or services. It is a critical dimension for analyzing procurement activities. Segmenting the process by vendor name is crucial for evaluating supplier performance. It allows for the analysis of on-time delivery rates, goods return rates, and quality inspection outcomes for each vendor. This helps in identifying reliable partners and those who may be causing delays or quality issues. Why it matters This attribute is essential for vendor performance management, enabling analysis of delivery times, return rates, and overall reliability by supplier. Where to get The vendor account is stored in `PurchTable` (field `OrderAccount`). The name is retrieved by joining with the `VendTable`. Examples Contoso Office SuppliesFabrikam RoboticsNorthwind Traders
	Approval Cycle Time ApprovalCycleTime	The duration between the creation of a purchase order and its final approval.
Description This calculated metric measures the time elapsed from the 'Purchase Order Created' activity to the 'Purchase Order Approved' activity. It is a direct measure of the efficiency of the approval workflow. This attribute is the primary measure for the 'Average PO Approval Time' KPI and the 'PO Approval Cycle Time Analysis' dashboard. Analyzing this duration helps identify bottlenecks in the approval chain and assess whether approval processes are meeting internal service level agreements. Why it matters Directly measures the efficiency of the approval workflow, a common area for delays in the procurement process. Where to get Calculated by finding the time difference between the `EventTime` of the 'Purchase Order Approved' and 'Purchase Order Created' activities for each case. Examples P2DT12H30MPT8HP7D
	Approver Name ApproverName	The name of the user who approved the purchase order or a step in the approval workflow.
Description This attribute identifies the manager or user who gave formal approval to the purchase order, allowing it to proceed. In multi-level approval workflows, there may be several approvers for a single purchase order. Tracking the approver is fundamental for the 'PO Approval Cycle Time Analysis' and 'Approver Performance Metrics' dashboards. It allows for the measurement of how long each approver takes, identifies bottlenecks in the approval chain, and helps in assessing workload distribution and efficiency. Why it matters Enables analysis of the approval process, helping to identify approval bottlenecks and measure the performance and workload of different approvers. Where to get Approval information is typically stored in workflow tracking tables, not directly on `PurchTable`. It requires querying the workflow history associated with the purchase order. Examples Charles GreenDiana PrinceEdward Nigma
	Company Code CompanyCode	The identifier for the legal entity or company placing the purchase order.
Description In a multi-company environment, this attribute specifies which legal entity is making the purchase. This is a fundamental organizational data point. This attribute allows for a comparative analysis of the P2P process across different legal entities within the same organization. It can highlight differences in process efficiency, compliance, and vendor management from one company to another, supporting standardization efforts. Why it matters Essential for multi-entity organizations to compare and standardize the procurement process across different legal entities. Where to get This is the `DataAreaId` field, which is present on almost every table in Dynamics 365, including `PurchTable`. Examples USMFDEMFGBSI
	Delivery Location DeliveryLocation	The specific site, warehouse, or address where the goods are to be delivered.
Description This attribute specifies the physical location for the delivery of items on the purchase order. It could be a warehouse, a specific office, or a project site. Analyzing the process by delivery location can help identify regional or site-specific bottlenecks, particularly in the goods receipt process. The 'Goods Receipt Processing Time' dashboard can use this attribute to compare efficiency across different locations. Why it matters Helps to identify location-specific process variations or delays, especially in the goods receipt and quality inspection stages. Where to get Delivery address and location information is stored on the `PurchTable` and can be defaulted from the company or vendor setup. Examples Main Warehouse ABuilding C OfficeWest Coast Distribution Center
	Department DepartmentName	The name of the department that initiated the purchase requisition or order.
Description This attribute identifies the internal business unit or department responsible for the purchase. It is often derived from the person who created the purchase requisition. Analyzing the process by department is key to understanding how different parts of the organization utilize the procurement process. It can help identify departments with longer approval cycles, higher rates of PO changes, or specific purchasing patterns. This allows for targeted process improvement initiatives. Why it matters Enables comparison of process performance across different business units, helping to identify department-specific behaviors, bottlenecks, or inefficiencies. Where to get This information is often linked via the requester or creator of the purchase requisition (`PurchReqTable`) or purchase order (`PurchTable`) and their associated department in the HR module. Examples FinanceITManufacturingMarketing
	Is On-Time Delivery IsOnTimeDelivery	A boolean flag that indicates if the goods were received on or before the requested delivery date.
Description This calculated attribute compares the timestamp of the 'Goods Receipt Posted' activity with the `RequestedDeliveryDate`. It is set to 'true' if the receipt date is on or before the requested date. This flag directly supports the calculation of the 'On-Time Delivery Rate' KPI. It simplifies the analysis of vendor performance and allows for easy filtering and visualization of on-time versus late deliveries, which is central to the 'Vendor Delivery Adherence' dashboard. Why it matters Provides a clear, binary outcome for delivery performance, simplifying the calculation of on-time delivery KPIs and vendor scorecards. Where to get This is a calculated attribute derived by comparing the `RequestedDeliveryDate` with the `EventTime` of the 'Goods Receipt Posted' activity. Examples truefalse
	Is PO Changed IsPurchaseOrderChanged	A boolean flag that indicates if the purchase order was modified after its initial approval.
Description This calculated attribute is set to 'true' if any 'Purchase Order Changed' activity occurs after the 'Purchase Order Approved' activity for a given case. It simplifies the analysis of rework and modifications. This flag is crucial for calculating the 'PO Modification Rate Post-Approval' KPI and for the 'Purchase Order Modification Trends' dashboard. It provides a straightforward way to isolate and analyze purchase orders that required rework, helping to identify the root causes of such changes. Why it matters Simplifies the measurement of rework and change frequency, which are key indicators of process instability and inefficiency. Where to get This is a calculated attribute derived from the sequence of activities in the event log. Examples truefalse
	Purchase Category PurchaseCategory	The classification of the purchased item or service, such as 'IT Hardware' or 'Office Supplies'.
Description This attribute provides a way to group purchase order items into logical categories. This classification helps in analyzing spending patterns and process variations across different types of procurement. In process analysis, filtering by purchase category can reveal different behaviors. For example, the procurement process for capital expenditures might be longer and more complex than for operational supplies. It is used in dashboards to analyze modification trends and return rates by category. Why it matters Allows for segmentation of the process by the type of goods or services being purchased, revealing different process behaviors for different spend categories. Where to get Item categories are linked to the released products (`InventTable`) which are then used on the `PurchLine`. The category information itself is stored in the category management tables. Examples IT HardwareOffice SuppliesProfessional ServicesRaw Materials
	Purchase Requisition PurchaseRequisitionNumber	The identifier of the purchase requisition that preceded the purchase order.
Description This attribute links a purchase order back to the original internal request, the purchase requisition. Not all purchase orders originate from a requisition. This link is vital for analyzing the full 'Requisition to PO' process. It allows for measuring the 'Requisition to PO Conversion Speed' KPI and understanding how quickly internal demand is translated into an external order. It also helps in analyzing compliance, for instance, by identifying POs created without a formal requisition. Why it matters Links the PO to the initial request, enabling analysis of the requisition-to-order cycle time and ensuring process compliance. Where to get Found on the `PurchLine` table, in the `PurchReqId` field, which links back to the `PurchReqTable`. Examples PR-000871PR-000872PR-000873
	Return Reason ReturnReason	The reason provided when goods from a purchase order are returned to the vendor.
Description When a 'Goods Returned to Vendor' activity occurs, this attribute captures the justification for the return, such as 'Damaged Goods', 'Incorrect Item', or 'Poor Quality'. This data is invaluable for the 'Purchase Order Returns Rate' dashboard. Analyzing return reasons helps to identify the root causes of returns, whether they are related to vendor quality, internal ordering errors, or shipping issues. This allows for targeted actions to reduce the rate of returns. Why it matters Provides insight into why goods are returned, helping to diagnose issues with vendor quality, ordering accuracy, or logistics. Where to get Return reasons are typically captured on return order transactions or through reason codes associated with negative receipt journals. Examples Damaged in transitWrong item deliveredFailed quality inspection

Required Recommended Optional

Purchase to Pay - Purchase Order Activities

These are the essential process steps and milestones to capture in your event log for accurate discovery and optimization.

6 Recommended 8 Optional

	Activity	Description
	Goods Receipt Posted	Marks the official recording of received goods against the purchase order in the system. This event is captured when a product receipt journal is posted.
Why it matters This is a key milestone that updates inventory and signifies the start of the invoice matching process. It is the endpoint for measuring 'On-Time Delivery Rate' and vendor lead time. Where to get Captured from the creation of the product receipt journal, stored in `VendPackingSlipJour`. The `createdDateTime` or `PackingSlipDate` on this table indicates when goods were officially received. Capture Use the creation or posting timestamp from the VendPackingSlipJour record linked to the PO. Event type explicit
	Purchase Order Approved	Marks the final approval of the purchase order, authorizing it to be sent to the vendor. This event is typically inferred from a status change on the PO or captured directly from workflow history.
Why it matters This is a critical milestone, as no further action can be taken until the PO is approved. It is essential for analyzing approval bottlenecks and measuring the 'PO Approval Cycle Time' KPI. Where to get Inferred from the `DocumentState` field on the `PurchTable` changing to 'Approved'. Alternatively, it can be sourced from the completion timestamp of the final approval step in the `WorkflowTrackingStatusTable`. Capture Identify the timestamp when the DocumentState on PurchTable transitions to 'Approved'. Event type inferred
	Purchase Order Completed	Indicates the successful conclusion of the purchase order lifecycle, where all goods have been received and invoiced. This is typically inferred when the PO status is updated to a final, closed state.
Why it matters This activity defines the end of a successful process instance. Measuring the 'Overall Purchase Order Cycle Time' from creation to completion provides a holistic view of process efficiency. Where to get Inferred from the status fields on the `PurchTable`, such as when both `DocumentState` is 'Invoiced' and the line item statuses indicate full receipt and invoicing. Capture Identify the timestamp when the PO's header and line statuses are updated to a final, closed state (e.g., 'Invoiced'). Event type inferred
	Purchase Order Created	This activity signifies the creation of a draft purchase order document in the system. It is captured from the creation timestamp of the purchase order header record, often following an approved requisition.
Why it matters This marks the transition from an internal request to a formal purchasing document. It is a key starting point for measuring PO processing and approval cycle times. Where to get This event is the creation of a record in the `PurchTable`. The `createdDateTime` field on this table provides the timestamp for the activity. Capture Extract creation timestamp from the PurchTable for each purchase order. Event type explicit
	Purchase Order Sent to Vendor	This activity indicates that the approved purchase order has been communicated to the vendor. It is captured when the PO is confirmed, which generates a confirmation journal and typically triggers the sending of the document.
Why it matters This is the first external-facing step and starts the clock on supplier lead time. It's crucial for tracking vendor performance and the 'On-Time Delivery Rate' KPI. Where to get The event is marked by the creation of a record in the `PurchPurchaseOrderJour` table (the PO confirmation journal). The creation date of this journal serves as the activity timestamp. Capture Use the creation timestamp of the first PurchPurchaseOrderJour record for the PO. Event type explicit
	Purchase Requisition Created	This activity marks the creation of a purchase requisition, the formal request for goods or services. It is captured when a new record is created in the purchase requisition table, signifying the start of the procurement demand.
Why it matters This is the initial trigger for the purchase order process. Analyzing the time from this event to PO creation helps measure internal process efficiency and responsiveness to demand. Where to get This event corresponds to the creation of a record in the `PurchReqTable`. The creation timestamp (`createdDateTime`) of the record marks the event time. Capture Extract creation timestamp from the PurchReqTable for each purchase requisition. Event type explicit
	Goods Returned to Vendor	Indicates that previously received goods have been returned to the vendor due to issues like damage or incorrect items. This is captured by posting a return transaction.
Why it matters Returns represent process failures and extra costs. Tracking this activity helps calculate the 'Purchase Order Return Rate' and identify issues with vendors or products. Where to get This event is inferred from the creation of a purchase order with a negative quantity or a specific return order document that references the original PO. The transaction date of the return posting is the event time. Capture Identify posting of a purchase return order or a debit note against the original PO. Event type explicit
	Purchase Order Canceled	Represents the termination of a purchase order before it was fully completed. This is captured by a specific status change on the purchase order document.
Why it matters Cancellations are an important exception path. Analyzing their frequency and reasons can highlight issues in planning or vendor reliability. Where to get This is inferred from the `DocumentState` field on the `PurchTable` being updated to 'Canceled'. The timestamp of this status change marks the event. Capture Identify the timestamp when the DocumentState on PurchTable is set to 'Canceled'. Event type inferred
	Purchase Order Changed	This activity captures any modification made to a purchase order after it has been approved. Dynamics 365 can track versions of the PO, allowing for the identification of changes.
Why it matters Tracking changes is critical for identifying rework, understanding process instability, and measuring the 'PO Modification Rate'. Changes can lead to delays and cost variations. Where to get Inferred by comparing different versions of the purchase order stored in archival or versioning tables (e.g., `PurchTableHistory`). An increase in the version number signifies a change. Capture Identify records where the version number on the PurchTable has incremented post-approval. Event type inferred
	Purchase Order Confirmed by Vendor	Represents the vendor's acknowledgement and confirmation of the purchase order details. This is often a manual data entry step based on communication from the vendor.
Why it matters Vendor confirmation provides assurance that the order is being processed. Delays or discrepancies at this stage can signal potential fulfillment issues. Where to get This is typically inferred from the population of confirmation-related date or status fields on the `PurchTable`, such as delivery confirmation dates. This may not be a discrete event. Capture Infer from the population of a specific confirmation date field on the PurchTable or PurchLine. Event type inferred
	Purchase Order Invoiced	This activity marks the point where a vendor invoice has been received and posted against the purchase order. This event links the procurement and payment processes.
Why it matters This is the final step before payment and is crucial for calculating the final cost of the purchase. It provides an endpoint for three-way matching analysis. Where to get This event is captured from the posting of a vendor invoice journal (`VendInvoiceJour`) that is matched to the purchase order. The `InvoiceDate` or posting date on this record is the timestamp. Capture Use the posting timestamp from the VendInvoiceJour table linked to the PurchTable record. Event type explicit
	Purchase Order Submitted for Approval	Represents the point when a draft purchase order is formally submitted into the approval workflow. This is typically an explicit action taken by the user, captured via workflow logs.
Why it matters This activity officially starts the PO approval cycle. Tracking this allows for precise measurement of how long POs wait for approval and the total approval duration. Where to get Captured from the `WorkflowTrackingStatusTable` for the purchase order, which logs the submission event and timestamp. Capture Identify the 'Submitted' event in the workflow history associated with the PurchTable record. Event type explicit
	Purchase Requisition Approved	Represents the formal approval of a purchase requisition by an authorized manager. This event is typically captured from the workflow history logs or by tracking a status change on the requisition record.
Why it matters Approval is a critical milestone that enables the conversion of a requisition into a purchase order. Delays here directly impact the entire procurement timeline. Where to get Can be captured from the `WorkflowTrackingStatusTable` associated with the purchase requisition, or inferred from a change in the status field on the `PurchReqTable` to an 'Approved' state. Capture Use the completion timestamp of the final approval step in the workflow history for the requisition. Event type explicit
	Quality Inspection Performed	Represents the completion of a quality inspection for the received goods. This event is often managed through the Quality Management module or a status update.
Why it matters This activity can be a significant bottleneck between receiving goods and making them available for use. Analyzing its duration helps improve the 'Quality Inspection Cycle Time' KPI. Where to get This can be inferred from the completion of a Quality Order (`InventQualityOrderTable`) linked to the purchase order receipt. The timestamp of the status change to 'Passed' or 'Failed' marks the event. Capture Track the status completion timestamp on the InventQualityOrderTable associated with the PO line. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Microsoft Dynamics 365

Steps

Access Data Management Workspace: Log in to Dynamics 365. Use the search bar to navigate to the Data management workspace.
Create New Export Project: Within the Data management workspace, select the Export tile. Create a new export project by clicking New. Provide a meaningful name, such as ProcessMind_P2P_Export, and a description. Set the Target data format to CSV.
Add Data Entities: Add the required data entities to the project one by one using the Add entity button. Select the entity name and set the target data format again if prompted. Add the following core entities:
- Purchase requisition lines V2
- Purchase order headers V2
- Purchase order confirmations V2 (for sent to vendor events)
- Purchase order header versions (for change events)
- Product receipt headers
- Quality orders
- Return order headers
- Vendor invoice journal headers
Apply Filters: For each entity, select it in the list and click Filter. At a minimum, filter by your company code (DataAreaId). It is critical to apply a date range filter on a relevant date field (e.g., CreatedDateTime on Purchase order headers V2) to limit the data volume. For example, filter for purchase orders created in the last 6 months.
Configure Advanced Settings: In the project settings, review the Advanced options. Ensure error handling and other settings are configured according to your environment's policies. For large datasets, consider using the Run as batch option to avoid UI timeouts.
Run the Export Job: Once all entities and filters are configured, click Export on the main project screen. The job will be added to the queue and executed. You can monitor its progress in the Job history section.
Download the Exported Package: After the job completes successfully, go to the job history, find your job, and click Download package. This will download a ZIP file containing a separate CSV file for each data entity.
Unzip the Package: Extract the contents of the downloaded ZIP file to a local folder. You will have multiple CSV files, one for each entity.
Transform and Combine Data: The separate CSV files must be processed and combined into a single event log. Use the provided Python script in the Query section as a template. This script loads each file, selects relevant columns, standardizes them, creates the ActivityName and EventTime columns for each event type, and unions them together.
Finalize the Event Log: The script outputs a single CSV file named p2p_event_log.csv. Review this file to ensure it has the required columns: PurchaseOrderNumber, ActivityName, EventTime, SourceSystem, LastDataUpdate, and any recommended attributes. This file is now ready to be uploaded to ProcessMind.

Configuration

Required Entities:
- Purchase requisition lines V2: For requisition creation and approval.
- Purchase order headers V2: For PO creation, approval, completion, and cancellation statuses.
- Purchase order confirmations V2: To capture when a PO is confirmed and sent to the vendor.
- Purchase order header versions: To track changes made to a PO after its initial approval.
- Product receipt headers: For Goods Receipt events.
- Quality orders: For quality inspection events.
- Return order headers: For goods return events.
- Vendor invoice journal headers: For invoice posting events.
Key Filters:
- Company (DataAreaId): This is essential to limit data to the correct legal entity. Apply this filter to all entities.
- Date Range: Apply a filter on a primary date field, such as CreatedDateTime on Purchase order headers V2, for a manageable period (e.g., 3-6 months).
Export Format:
- Target data format: CSV. Ensure the field delimiter and other settings are standard.
Prerequisites:
- Permissions: The user running the export requires a security role with permissions for the Data management workspace and access to all the specified data entities. Roles like Data management administrator are typically sufficient.
Performance Considerations:
- For large datasets, run export jobs as a batch process during off-peak hours to minimize impact on system performance.

a Sample Query config

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Steps

Establish Database Connection: Obtain read-only credentials for the Microsoft Dynamics 365 Finance & Operations Azure SQL database. This access is typically provisioned by an IT administrator, often through features like 'Export to Data Lake' or a direct read-only replica.
Connect with a SQL Client: Use a database management tool such as SQL Server Management Studio (SSMS) or Azure Data Studio to connect to the Azure SQL instance using the provided credentials.
Prepare the SQL Query: Open a new query window in your SQL client. Copy the complete SQL query provided in the query section of this document.
Set Query Parameters: Locate the DECLARE section at the top of the query. Modify the placeholder values for @StartDate, @EndDate, and @DATAAREAID to define the desired time frame and the specific D365 legal entity (company) you wish to analyze.
Execute the Query: Run the modified SQL query. Depending on the volume of data within the selected date range, the execution may take several minutes.
Review the Results: Once the query completes, the results pane will display the raw event log data. Briefly inspect the columns and rows to ensure they look correct and no errors were returned.
Export Data to CSV: Most SQL clients have a built-in function to export query results. Right-click the result set and choose an option like 'Save Results As...' or 'Export to CSV'.
Format for Upload: Save the file in CSV format with UTF-8 encoding. Ensure the column headers in the file match the required attribute names for your process mining tool. The provided query already uses the correct aliases.
Upload to ProcessMind: The resulting CSV file is now ready to be uploaded as an event log.

Configuration

Prerequisites: You must have read-only SQL access to the underlying Azure SQL database of the Dynamics 365 F&O environment. This is a privileged level of access and must be granted by a system administrator.
Date Range Filters: The query includes @StartDate and @EndDate parameters. It's recommended to start with a smaller date range, such as 3-6 months, to ensure performance before running it on a larger historical dataset.
Company Code Filter: The @DATAAREAID parameter is mandatory for filtering the data to a specific legal entity (company) within D365. You can find the relevant ID in the 'Legal entities' form in the D365 client.
Performance Considerations: Executing this query directly on a production database can impact performance. It is highly recommended to run it against a read-only replica or during non-business hours to avoid impacting system users.
Data Freshness: As this method queries the database directly, the extracted data reflects the real-time state of the system at the moment of execution.

a Sample Query sql

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

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

UNION ALL

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

Steps

Prerequisites: Ensure you have Python 3.6+ installed along with the requests and pandas libraries. You can install them using pip: pip install requests pandas.
Register an Application in Azure AD: To authenticate with the Dynamics 365 API, you must register an application in the Azure Active Directory (Azure AD) tenant associated with your D365 environment. Navigate to Azure Portal > Azure Active Directory > App registrations > New registration. Provide a name and choose 'Accounts in this organizational directory only'.
Generate Client Secret: Once the app is registered, go to 'Certificates & secrets' and create a new client secret. Copy the secret value immediately, as it will be hidden later. Note down the Application (client) ID and the Directory (tenant) ID from the app's 'Overview' page.
Grant API Permissions: In your app registration, go to 'API permissions' > 'Add a permission'. Select 'Dynamics ERP', choose 'Delegated permissions', check the 'user_impersonation' box, and click 'Add permissions'. Then, grant admin consent for the directory.
Link Azure App in Dynamics 365: In your Dynamics 365 Finance & Operations environment, navigate to System administration > Setup > Azure Active Directory applications. Add a new record with the Client ID from your Azure app registration, a descriptive name, and the user ID of a D365 user with appropriate data access permissions.
Configure the Python Script: Copy the provided Python script into a file (e.g., extract_d365_p2p.py). Populate the placeholder variables at the top of the script with your D365 environment URL, Client ID, Client Secret, and Tenant ID.
Set Extraction Parameters: Adjust the data_area_id variable to match the company code you want to extract from. Modify the start_date_filter to define the desired time window for the data extraction.
Execute the Script: Run the script from your terminal using python extract_d365_p2p.py. The script will authenticate, fetch data for each defined activity from the OData API, and handle pagination automatically.
Review the Output: The script will print progress updates to the console. Upon completion, it will generate a CSV file named d365_p2p_event_log.csv in the same directory.
Prepare for Upload: The generated CSV file is structured with the required columns (PurchaseOrderNumber, ActivityName, EventTime, etc.) and is ready for upload into a process mining tool. No further structural changes are needed.

Configuration

Authentication: The script uses OAuth 2.0 client credentials. You must provide the following from your Azure AD App Registration:
- CLIENT_ID: The Application (client) ID.
- CLIENT_SECRET: The generated client secret value.
- TENANT_ID: The Directory (tenant) ID.
Environment URL: The base URL of your Dynamics 365 Finance & Operations environment (e.g., https://your-environment.operations.dynamics.com).
Company Filter: The dataAreaId variable must be set to the specific company (legal entity) you wish to analyze. This is a mandatory filter to avoid cross-company data conflicts.
Date Range: The start_date_filter variable is critical for managing performance. It is recommended to start with a recent 3 to 6 month period to ensure the extraction completes in a reasonable time. The filter is applied to the creation date of the records.
OData Entities: The script queries several OData entities. Ensure the D365 user associated with the app registration has read permissions for at least:
- PurchaseRequisitionLinesV2
- PurchaseOrderHeadersV2
- PurchaseOrderConfirmationsV2
- PurchaseOrderReceiptsV2
- QualityOrders
- PurchaseOrderInvoiceLinesV2
Script Customization: Some activities, like 'Goods Returned to Vendor', may rely on system customizations. You might need to adjust the entity name [YourVendorReturnEntity] in the script to match your specific implementation.

a Sample Query python

import requests
import pandas as pd
import datetime

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

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


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

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

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

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

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

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

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

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

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

    class=class="hl-string">"hl-comment"># Create DataFrame and save to CSV
    df = pd.DataFrame(all_events)
    df = df.dropna(subset=[class="hl-string">'PurchaseOrderNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime'])
    df[class="hl-string">'EventTime'] = pd.to_datetime(df[class="hl-string">'EventTime'], errors=class="hl-string">'coerce')
    df = df.sort_values(by=[class="hl-string">'PurchaseOrderNumber', class="hl-string">'EventTime'])

    class=class="hl-string">"hl-comment"># Reorder columns for clarity
    column_order = [
        class="hl-string">'PurchaseOrderNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'UserName', class="hl-string">'VendorName', class="hl-string">'PurchaseOrderTotalAmount', class="hl-string">'PurchaseOrderStatus', class="hl-string">'RequestedDeliveryDate'
    ]
    df = df[column_order]

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

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

Steps

Access Data Management Workspace: Log in to Dynamics 365. Use the search bar to navigate to the Data management workspace.
Create New Export Project: Within the Data management workspace, select the Export tile. Create a new export project by clicking New. Provide a meaningful name, such as ProcessMind_P2P_Export, and a description. Set the Target data format to CSV.
Add Data Entities: Add the required data entities to the project one by one using the Add entity button. Select the entity name and set the target data format again if prompted. Add the following core entities:
- Purchase requisition lines V2
- Purchase order headers V2
- Purchase order confirmations V2 (for sent to vendor events)
- Purchase order header versions (for change events)
- Product receipt headers
- Quality orders
- Return order headers
- Vendor invoice journal headers
Apply Filters: For each entity, select it in the list and click Filter. At a minimum, filter by your company code (DataAreaId). It is critical to apply a date range filter on a relevant date field (e.g., CreatedDateTime on Purchase order headers V2) to limit the data volume. For example, filter for purchase orders created in the last 6 months.
Configure Advanced Settings: In the project settings, review the Advanced options. Ensure error handling and other settings are configured according to your environment's policies. For large datasets, consider using the Run as batch option to avoid UI timeouts.
Run the Export Job: Once all entities and filters are configured, click Export on the main project screen. The job will be added to the queue and executed. You can monitor its progress in the Job history section.
Download the Exported Package: After the job completes successfully, go to the job history, find your job, and click Download package. This will download a ZIP file containing a separate CSV file for each data entity.
Unzip the Package: Extract the contents of the downloaded ZIP file to a local folder. You will have multiple CSV files, one for each entity.
Transform and Combine Data: The separate CSV files must be processed and combined into a single event log. Use the provided Python script in the Query section as a template. This script loads each file, selects relevant columns, standardizes them, creates the ActivityName and EventTime columns for each event type, and unions them together.
Finalize the Event Log: The script outputs a single CSV file named p2p_event_log.csv. Review this file to ensure it has the required columns: PurchaseOrderNumber, ActivityName, EventTime, SourceSystem, LastDataUpdate, and any recommended attributes. This file is now ready to be uploaded to ProcessMind.

Configuration

Required Entities:
- Purchase requisition lines V2: For requisition creation and approval.
- Purchase order headers V2: For PO creation, approval, completion, and cancellation statuses.
- Purchase order confirmations V2: To capture when a PO is confirmed and sent to the vendor.
- Purchase order header versions: To track changes made to a PO after its initial approval.
- Product receipt headers: For Goods Receipt events.
- Quality orders: For quality inspection events.
- Return order headers: For goods return events.
- Vendor invoice journal headers: For invoice posting events.
Key Filters:
- Company (DataAreaId): This is essential to limit data to the correct legal entity. Apply this filter to all entities.
- Date Range: Apply a filter on a primary date field, such as CreatedDateTime on Purchase order headers V2, for a manageable period (e.g., 3-6 months).
Export Format:
- Target data format: CSV. Ensure the field delimiter and other settings are standard.
Prerequisites:
- Permissions: The user running the export requires a security role with permissions for the Data management workspace and access to all the specified data entities. Roles like Data management administrator are typically sufficient.
Performance Considerations:
- For large datasets, run export jobs as a batch process during off-peak hours to minimize impact on system performance.

a Sample Query config

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Data Template: Purchase to Pay - Purchase Order

Your Purchase to Pay - Purchase Order Data Template

Purchase to Pay - Purchase Order Attributes

Purchase to Pay - Purchase Order Activities

Extraction Guides

Dynamics 365 Data Management Framework Export

Steps

Configuration

a Sample Query config

Direct Azure SQL Database Query for D365 F&O

Steps

Configuration

a Sample Query sql

Python Extraction via Dynamics 365 OData REST API

Steps

Configuration

a Sample Query python

D365 Data Export to Azure Data Lake Service

Steps

Configuration

a Sample Query config

Ready to Get Started?

Boost Your Purchase to Pay - Purchase Order Efficiency Now