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

Your Purchase to Pay - Requisition Data Template

Microsoft Dynamics 365

Purchase to Pay - Requisition Attributes (18) Purchase to Pay - Requisition Activities (12) Extraction Guides (3)

Your Purchase to Pay - Requisition Data Template

This template outlines the key attributes to collect and the crucial activities to track, enabling a deep dive into your requisition process. It also provides practical guidance on extracting this data, helping you prepare your event log efficiently for detailed analysis.

Recommended attributes to collect
Key activities to track
Extraction guidance

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

Download Template

Purchase to Pay - Requisition Attributes

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

5 Required 7 Recommended 6 Optional

	Name	Description
	Activity Name ActivityName	The name of the specific business event or step that occurred in the requisition process.
Description This attribute records the name of each activity performed within the purchase requisition lifecycle. Examples include 'Requisition Created', 'Approval Step Approved', and 'Purchase Order Created'. These activities form the nodes in the discovered process map. Analyzing the sequence, frequency, and duration between these activities is the core of process mining. It helps identify bottlenecks, rework loops, and deviations from the standard process flow, providing insights into operational inefficiencies. Why it matters This attribute defines the steps in the process map, making it possible to visualize, analyze, and understand the requisition workflow. Where to get This is typically derived from status change logs, workflow history tables, or specific event tables within Microsoft Dynamics 365, such as WorkflowTrackingStatusTable. Examples Requisition Submitted for ApprovalApproval Step ApprovedRequisition Amended
	Event Time EventTime	The precise timestamp when a specific activity or event occurred.
Description Event Time, or the timestamp, captures the date and time that a business event was recorded in the system. It is the temporal foundation for all time-based process analysis. This attribute is critical for calculating cycle times, durations, and waiting times between activities. It enables the analysis of process performance, bottleneck identification, and SLA compliance monitoring. Accurate timestamps are essential for a reliable process mining analysis. Why it matters It provides the chronological order of events, which is necessary for calculating process durations, identifying bottlenecks, and analyzing performance over time. Where to get Found in workflow history or document log tables, often as a 'CreatedDateTime' or 'ModifiedDateTime' field associated with each status change or event record. Examples 2023-10-26T10:00:00Z2023-10-26T11:30:15Z2023-10-27T14:22:05Z
	Purchase Requisition ID PurchaseRequisitionId	The unique identifier for a purchase requisition, serving as the primary case identifier.
Description The Purchase Requisition ID is the central key that links all activities related to a single request for goods or services. Each requisition process, from creation to final approval and closure, is tracked under this unique ID. In process mining, this attribute is fundamental for reconstructing the end-to-end journey of each requisition. It allows for the analysis of process variants, cycle times, and compliance for individual cases, providing a complete view of the requisition lifecycle. Why it matters It is essential for grouping all related events into a single process instance, enabling a complete, end-to-end analysis of each requisition's lifecycle. Where to get This is typically the primary key in the main purchase requisition header table, such as PurchReqTable in Microsoft Dynamics 365. Examples PR-001254PR-001255PR-001256
	Last Data Update LastDataIngestionTimestamp	The timestamp when the data was last extracted and loaded into the process mining tool.
Description This attribute indicates the freshness of the data being analyzed. It shows the date and time of the most recent data refresh from the source system. This is not a field from Dynamics 365 itself, but metadata added during data ingestion. This timestamp is critical for users to understand the timeliness of the insights. It helps them know if they are looking at real-time data or a snapshot from a specific point in time, which affects the relevance of their conclusions. Why it matters It informs users about the freshness of the data, ensuring they understand the time frame of the analysis and the relevance of the insights. Where to get This value is generated and appended to the dataset during the data ingestion or ETL process. Examples 2024-05-20T08:00:00Z2024-05-21T08:00:00Z2024-05-22T08:00:00Z
	Source System SourceSystemId	The system of record from which the data was extracted.
Description This attribute identifies the source system where the event data originated. For this context, it would be 'Microsoft Dynamics 365'. In environments with multiple integrated systems, this field is crucial for data lineage and context. In analysis, it helps differentiate processes that may span multiple systems or confirms that the data comes from a single, authoritative source. This is important for data validation and ensuring the analysis is based on the correct dataset. Why it matters It provides context about the data's origin, which is crucial for data governance, validation, and in environments where multiple systems are integrated. Where to get This is a static value, 'Microsoft Dynamics 365', added during the data extraction and transformation process. Examples Microsoft Dynamics 365 F&OD365MSD365
	Approval Step ApprovalStep	The name or stage of a specific approval step in the workflow.
Description This attribute identifies the particular stage in the approval workflow, such as 'Manager Approval' or 'Finance Approval'. It provides more granular detail than the general activity name. This attribute is essential for the 'Approval Step Bottleneck Analysis'. By tracking the time spent in each distinct approval step, it becomes possible to pinpoint exactly which stages are causing delays in the overall process. This allows for targeted interventions to improve workflow efficiency. Why it matters It allows for a granular analysis of the approval workflow, making it possible to identify specific stages that are causing bottlenecks. Where to get This information is contained within the workflow history tables, such as WorkflowTrackingStatusTable, which details each step of the configured workflow. Examples Manager ApprovalDepartment Head ApprovalFinance Review
	Department Department	The department of the requester or the cost center associated with the requisition.
Description This attribute specifies the business department or cost center that initiated the purchase requisition, such as 'Marketing', 'IT', or 'Operations'. This information is usually part of the requisition header. Segmenting the process by department is crucial for comparative analysis. It allows you to see which departments have the longest cycle times, highest rejection rates, or most frequent amendments. These insights help tailor process improvements to specific departmental needs. Why it matters It allows for filtering and comparing process performance across different business units, revealing department-specific patterns, bottlenecks, or inefficiencies. Where to get This information is often stored on the purchase requisition header (PurchReqTable) and linked to the financial dimensions configuration in Dynamics 365. Examples IT DepartmentFinanceOperations
	Processing Time ProcessingTime	The active time spent working on a specific task.
Description Processing Time represents the duration a resource actively spends executing a task. It is calculated as the difference between the end time and start time of an activity. Unlike cycle time, it excludes waiting or queue time. This calculated metric is vital for understanding resource efficiency and the actual effort required for each process step. It helps in the 'Approval Step Bottleneck Analysis' by distinguishing between long processing times, which may indicate a complex task, and long queue times, which suggest a resource availability issue. Why it matters It measures the active work duration for activities, helping to distinguish between value-adding time and waiting time for accurate bottleneck analysis. Where to get This is calculated during data transformation by subtracting an activity's start time from its end time. This requires having both StartTime and EndTime for each activity. Examples 864000003600000600000
	Requisition Status RequisitionStatus	The current or final status of the purchase requisition.
Description This attribute indicates the overall status of the purchase requisition at any given point, such as 'In Review', 'Approved', 'Rejected', or 'Closed'. This is often a case-level attribute representing the final outcome. Analyzing the final status helps in understanding the overall outcomes of the process. For example, a high number of 'Rejected' or 'Withdrawn' requisitions can indicate problems in the initial request phase or a cumbersome approval process. It is key for measuring success rates and process efficiency. Why it matters It provides a clear outcome for each case, enabling analysis of approval, rejection, and withdrawal rates, which are key performance indicators. Where to get The status field is typically located on the purchase requisition header table, PurchReqTable, often named 'Status' or 'PurchReqStatus'. Examples ApprovedIn reviewRejectedDraft
	Requisition Total Amount RequisitionTotalAmount	The total monetary value of the purchase requisition.
Description This attribute captures the total value of all line items on a purchase requisition. The amount often influences the complexity of the approval workflow, with higher value requisitions requiring more approval steps. In process analysis, this attribute is essential for value-based filtering and analysis. It helps answer questions like, 'Do high-value requisitions take longer to approve?' or 'What is the value of requisitions currently stuck in the process?'. This provides a financial context to process performance. Why it matters It adds a financial dimension to the analysis, allowing for the prioritization of high-value cases and understanding how monetary value impacts process behavior. Where to get This value is typically on the purchase requisition header table or calculated as a sum of line item amounts from the purchase requisition line table (PurchReqLine). Examples 1500.0025000.50500.75
	Urgency Level UrgencyLevel	A classification of the requisition's urgency, such as 'High', 'Medium', or 'Low'.
Description The Urgency Level, or priority, indicates how quickly the requested goods or services are needed. This attribute is often used to influence the approval path or to prioritize work for approvers. Analyzing this attribute helps to determine if the priority system is effective. For instance, you can compare the cycle times of 'High' urgency requisitions to 'Low' urgency ones. If there's no significant difference, it may indicate that the priority field is being ignored or misused, which is a key insight for the 'Urgency Level Impact Analysis' dashboard. Why it matters It helps assess whether priority settings effectively expedite critical requests and uncovers potential misuse of the urgency classification. Where to get This could be a standard or custom field on the PurchReqTable. Its existence and name may vary based on system configuration. Examples HighMediumLow
	User User	The user ID or name of the person who performed the activity.
Description This attribute identifies the employee or system user responsible for executing a specific process step, such as submitting a requisition or approving a request. It can be a user ID, full name, or email address. Analyzing activities by user helps identify training needs, high-performing individuals or teams, and workload distribution. It is also essential for compliance analysis, such as segregation of duties, and for understanding how different user roles interact with the process. Why it matters It enables analysis of user-specific behavior, workload, and performance, which is key for resource management and identifying training opportunities. Where to get Typically found in workflow history tables (e.g., WorkflowTrackingStatusTable) or transaction tables (e.g., PurchReqTable) linked to a user table (e.g., UserInfo). Examples j.smitha.joness.patel
	Amendment Count AmendmentCount	The total number of times a requisition was amended.
Description This is a calculated numeric attribute that counts the occurrences of the 'Requisition Amended' activity for each purchase requisition case. This attribute is essential for the 'Requisition Amendment Frequency' dashboard and the 'Requisition Amendment Ratio' KPI. It quantifies the amount of rework per case, making it easy to identify which requisitions, departments, or users are associated with high levels of change and inefficiency. This helps target efforts to improve initial request quality. Why it matters It quantifies rework within a case, making it easy to measure and analyze the frequency of amendments and its impact on process efficiency. Where to get This is a calculated attribute. It is derived during data transformation by counting the 'Requisition Amended' activities for each unique PurchaseRequisitionId. Examples 013
	Approval Workflow Path ApprovalWorkflowPath	A representation of the sequence of approval steps taken.
Description This attribute is a derived field that concatenates the sequence of approval steps for a given requisition, such as 'Manager Approval -> Department Head Approval -> Finance Approval'. It effectively summarizes the process variant for the approval sub-process. This is crucial for the 'Compliance Deviation Monitor' dashboard. By comparing the actual workflow path against a predefined standard or expected path, it becomes easy to flag non-compliant or unusual process flows that may represent policy violations or operational risks. Why it matters It simplifies compliance analysis by providing a clear string representation of the process variant, making it easy to spot deviations from standard procedures. Where to get This attribute is not a standard field. It must be derived by concatenating the 'ApprovalStep' values in chronological order for each case during data transformation. Examples Manager -> DirectorManager -> Director -> VP FinanceManager -> Auto-Approved
	Approver Group ApproverGroup	The user group or role responsible for an approval step.
Description This attribute identifies the group, role, or queue assigned to handle a specific approval task, for example, 'Finance Approvers' or 'IT Managers'. Analyzing process performance by approver group is key to understanding workload distribution and identifying which groups may be under-resourced or require additional training. It directly supports the 'Approval Step Bottleneck Analysis' dashboard by allowing performance to be sliced by the teams responsible for approvals. Why it matters It helps identify performance differences between approval teams, highlighting potential resource constraints or training needs within specific groups. Where to get This information is part of the workflow history (e.g., WorkflowTrackingStatusTable), which records the assigned user or user group for each task. Examples Finance ApproversIT ManagersSenior Leadership
	Currency Currency	The currency code for the requisition amount.
Description This attribute specifies the currency, for example, USD, EUR, GBP, in which the requisition's total amount is denominated. It is crucial for financial analysis, especially in multinational organizations dealing with multiple currencies. Using the currency attribute allows for proper handling and aggregation of financial data. It ensures that monetary values are interpreted correctly and enables conversions to a common currency for accurate reporting and comparison across different regions or business units. Why it matters It provides necessary context for financial attributes, ensuring accurate interpretation and aggregation of monetary values in multi-currency environments. Where to get This field is typically found on the purchase requisition header table, PurchReqTable, alongside the amount fields. Examples USDEURGBP
	Is First Pass Approved IsFirstPass	A flag indicating if a requisition was approved without any prior amendments or rejections.
Description This is a calculated boolean attribute that is 'true' if a requisition's path to approval did not include any 'Requisition Amended' or 'Approval Step Rejected' activities. Otherwise, it is 'false'. This attribute directly supports the 'Requisition First-Pass Approval Rate' KPI. It simplifies the analysis of process efficiency by providing a clear, case-level indicator of rework. A low rate of first-pass approvals highlights issues with initial data quality or unclear requirements, signaling an opportunity for process improvement. Why it matters It directly measures process quality and efficiency by identifying cases that required rework, supporting KPIs focused on first-time-right rates. Where to get This is a calculated attribute. It requires analyzing the full activity sequence for each case during data transformation to check for the absence of rework activities before approval. Examples truefalse
	Purchase Order Number PurchaseOrderNumber	The identifier of the purchase order created from the requisition.
Description This attribute stores the unique ID of the purchase order that was generated from an approved purchase requisition. It serves as the link between the requisition process and the downstream procurement process. Tracking this number is essential for analyzing the 'Requisition to PO Conversion Time'. It confirms that a requisition has successfully moved to the next stage of the Purchase-to-Pay cycle and allows for end-to-end process analysis that spans both requisitions and purchase orders. Why it matters It connects the requisition to the subsequent purchase order, enabling analysis of the Requisition-to-PO conversion and linking different stages of the P2P process. Where to get This information is usually found on the purchase requisition line table (PurchReqLine) after a PO has been created, linking back to the PurchTable. Examples PO-000987PO-000988PO-000989

Required Recommended Optional

Purchase to Pay - Requisition Activities

These are the essential process steps and milestones to capture in your event log for accurate discovery and analysis of your requisition workflow.

7 Recommended 5 Optional

	Activity	Description
	Approval Step Approved	An approver completes their assigned task, approving the requisition for their stage of the process. This moves the requisition to the next step or to final approval.
Why it matters Measures the processing time for each approval stage and helps pinpoint efficient parts of the workflow. It's a key component of variant analysis. Where to get Explicitly logged in the 'WorkflowTrackingStatusTable' when a user completes a work item with an 'Approve' outcome. Capture Identify 'WorkItemCompleted' events with an 'Approve' outcome in the workflow history logs. Event type explicit
	Purchase Order Created	An approved purchase requisition line is converted into a purchase order line, signaling the handoff to the procurement team. This is captured by linking the requisition line to a purchase order line.
Why it matters This is a critical milestone connecting the requisition to the downstream procurement process. It's essential for measuring the 'Requisition to PO Conversion Time' KPI. Where to get Inferred by finding a record in the 'PurchLine' table that references the ID of a 'PurchReqLine' associated with the requisition case. Capture Join PurchReqLine with PurchLine on the linking reference field (e.g., PurchReqLineRefId). Event type inferred
	Requisition Approved	The requisition has successfully passed all required approval steps in the workflow. This activity is captured when the workflow instance completes with a final approved status.
Why it matters This is a major milestone, marking the end of the approval cycle and the start of the procurement phase. It's the end event for 'Requisition Approval Cycle Time' KPI. Where to get Captured explicitly from the 'WorkflowTrackingStatusTable' when the workflow completes. This also updates the 'Status' field on the 'PurchReqTable' to 'Approved'. Capture Filter for workflow 'Completion' events with an 'Approved' status or track status change on PurchReqTable. Event type explicit
	Requisition Closed	The entire purchase requisition is considered complete, meaning all its lines have been processed into purchase orders or cancelled. This is a final, successful end state.
Why it matters This activity marks the successful completion of the requisition's lifecycle. It is the final endpoint for measuring the total end-to-end process duration. Where to get This status is typically calculated or inferred. It occurs when all associated 'PurchReqLine' records have reached a terminal status (e.g., 'Closed', 'Cancelled'). Capture Derive this event by checking if all child PurchReqLine records for a PurchReqTable have a final status. Event type calculated
	Requisition Created	This event marks the initial creation of the purchase requisition record in a draft state. It is captured by identifying the creation timestamp of the purchase requisition header.
Why it matters As the process start, this activity is essential for measuring overall requisition lifecycle time and analyzing daily requisition throughput volumes. Where to get This activity is inferred from the 'createdDateTime' field on the 'PurchReqTable' for each new Purchase Requisition ID. Capture Use the creation timestamp of the record in the PurchReqTable. Event type inferred
	Requisition Rejected	The requisition has been rejected during the approval workflow and will not be processed further. This represents a terminal failure state for the requisition.
Why it matters This end event is critical for analyzing overall rejection rates and understanding the financial or operational impact of failed requests. Where to get Captured explicitly from the 'WorkflowTrackingStatusTable' upon workflow completion with a 'Rejected' status, which updates the 'PurchReqTable' status field. Capture Filter for workflow 'Completion' events with 'Rejected' status or track status change on PurchReqTable. Event type explicit
	Requisition Submitted for Approval	The user submits the completed requisition, which initiates the formal approval workflow. This is an explicit action logged by the system's workflow engine.
Why it matters This activity is a critical milestone that begins the approval cycle. It is the starting point for measuring 'Requisition Approval Cycle Time' and 'First-Pass Approval Rate'. Where to get Captured from the 'WorkflowTrackingStatusTable' or a similar workflow history table, where a 'Submission' event is logged against the purchase requisition. Capture Filter workflow history logs for the 'Submission' or 'Start' event type tied to the requisition. Event type explicit
	Approval Step Rejected	An approver rejects their assigned task, typically sending the requisition back to the originator for correction. This is an explicit action logged by the workflow engine.
Why it matters This activity is fundamental for calculating the 'Requisition Rejection Rate' and identifying at which stages rejections most often occur, highlighting areas for process improvement. Where to get Explicitly logged in the 'WorkflowTrackingStatusTable' when a user completes a work item with a 'Reject' outcome. Capture Identify 'WorkItemCompleted' events with a 'Reject' outcome in the workflow history logs. Event type explicit
	Approval Step Started	An individual approval task is assigned to a user or group as part of the workflow. This represents the beginning of a wait or processing time for a specific approver.
Why it matters This activity is crucial for 'Approval Step Bottleneck Analysis', allowing measurement of queue times for specific approval stages. Where to get Captured from the 'WorkflowTrackingStatusTable' when a new work item is created and assigned for the requisition's workflow instance. Capture Identify 'WorkItemCreated' or similar events in the workflow history logs for the specific requisition. Event type explicit
	Requisition Amended	This event occurs when a user recalls a submitted requisition from the workflow to make changes. The activity is typically captured by identifying a recall action followed by a later resubmission.
Why it matters Tracking amendments is key to identifying rework, unclear initial requests, and process inefficiencies. It directly supports the 'Requisition Amendment Frequency' dashboard. Where to get Can be inferred from the workflow history ('WorkflowTrackingStatusTable') by detecting a 'Recall' or 'RequestChange' action. It can also be inferred from changes to the 'modifiedDateTime' field on 'PurchReqTable' after submission. Capture Detect workflow recall events or record version changes between submission events. Event type inferred
	Requisition Line Closed	An individual line item on the purchase requisition is considered fully processed. This typically occurs after the line has been completely converted to a purchase order.
Why it matters Provides granular detail on requisition fulfillment, helping identify if requisitions are partially or fully converted to purchase orders. Where to get Inferred from the status field on the individual 'PurchReqLine' table. A status indicating it is ordered or received would signify closure. Capture Monitor the status field on the PurchReqLine table for a terminal value like 'Invoiced' or 'Closed'. Event type inferred
	Requisition Withdrawn	The creator or an authorized user cancels the requisition after it has been submitted. This action terminates the workflow and the request.
Why it matters Tracking withdrawals helps identify issues with demand planning or overly complex processes. This supports the 'Requisition Withdrawal Insights' dashboard. Where to get This is inferred from a status change on the 'PurchReqTable' to 'Cancelled' or from a 'Cancel' event in the 'WorkflowTrackingStatusTable'. Capture Detect status change to 'Cancelled' on PurchReqTable or a workflow cancellation event. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Microsoft Dynamics 365

Steps

Access Data Management Workspace: Log in to Microsoft Dynamics 365 and navigate to the Workspaces menu. Select Data management to open the central hub for data import and export operations.
Create New Export Project: Within the Data Management workspace, click the Export tile. Provide a unique Group name, a descriptive Description, and set the Project category to Project. This organizes your data extraction tasks.
Add Data Entities: Add the necessary standard data entities to your project. Click Add entity and search for each of the following, adding them one by one:
- Purchase requisition headers
- Purchase requisition lines
- Workflow tracking status table
- Workflow work items
- Purchase order lines V2
Define Export Format: For each added entity, select a Target data format such as CSV or Excel. CSV is generally recommended for performance and compatibility with data transformation tools.
Apply Data Filters: Select an entity, then click the Filter button. On the Range tab, specify the Legal entity (company code) to ensure you are extracting data from the correct business unit. It is also critical to add a date filter on the Created date and time field of the Purchase requisition headers entity to limit the data volume. We recommend starting with a 3 to 6 month period.
Run the Export Job: Once all entities and filters are configured, click Export on the Action Pane. The system will create a job package and begin processing. You can monitor the job's progress in the Job history section.
Download the Data Package: After the export job completes successfully, navigate to the Job history details and click Download package. This will download a ZIP file containing the separate data files for each entity.
Transform Raw Data into Event Log: The exported files represent raw system tables, not a final event log. You must use an external tool like Python, Power Query, or an ETL platform to combine and reshape the data. This involves:
- Joining the header and line files.
- Joining workflow data to requisitions using the CorrelationId and the RecId of the purchase requisition header.
- Pivoting status changes and workflow events from the workflow tables into distinct activity rows (Requisition Submitted, Approval Step Approved, etc.).
- Creating a new row for each activity with the correct ActivityName, EventTime, and PurchaseRequisitionId.
Calculate and Map Attributes: During the transformation, map source columns to the required attributes like User and Department. Calculate derived attributes like RequisitionTotalAmount by summing line amounts. Set a static value for SourceSystemId (e.g., 'D365_F&O') and use the execution time of your script for LastDataIngestionTimestamp.
Finalize and Format the Output: Ensure the final output file is a single CSV file where each row represents one event in the process. The columns must match the required event log structure, including PurchaseRequisitionId, ActivityName, and EventTime.

Configuration

Required Permissions: Users must have a security role with permissions to the Data management workspace and access to the specific data entities being exported, such as Data management administrator or a similar custom role.
Key Data Entities:
- Purchase requisition headers: Provides the main case identifier, creation timestamp, and overall status.
- Purchase requisition lines: Contains details about each requested item, including amounts and links to purchase orders.
- Workflow tracking status table: The primary source for workflow events like submission, approval, rejection, and recall.
- Workflow work items: Details the specific tasks assigned to users during the approval process.
- Purchase order lines V2: Used to find the creation timestamp of the purchase order linked to a requisition line.
Date Range Filtering: It is crucial to apply a date range filter to the Created date and time field on the Purchase requisition headers entity. A range of 3-6 months is recommended for initial analysis to balance data volume and process insight.
Company Code Filtering: Always apply a filter for the Legal entity to avoid extracting data from unintended or test companies. This significantly reduces data size and improves job performance.
Data Transformation: This method requires a significant post-processing step outside of Dynamics 365 to transform the raw entity exports into a structured event log. Be prepared to use a scripting or ETL tool.

a Sample Query config

/*
This is not a single executable query. Instead, it is a configuration manifest for a Dynamics 365 Data Management Framework Export Project.
You must add each entity listed below to the project and select the specified fields for export.
The transformation logic to create the final event log must be applied after exporting these files.
*/

-- Entity 1: Purchase requisition headers
-- Purpose: Captures requisition creation, overall status, and header-level attributes.
SELECT
    REQUISITIONNUMBER AS PurchaseRequisitionId,
    REQUISITIONSTATUS AS RequisitionStatus,
    CREATEDDATETIME AS EventTime_RequisitionCreated,
    MODIFIEDDATETIME AS EventTime_RequisitionClosed, -- Used if status is Closed
    PREPARERPERSONNELNUMBER AS User_Preparer,
    REQUESTERPERSONNELNUMBER AS User_Requester,
    DEFAULTLEDGERDIMENSIONDISPLAYVALUE AS Department, -- Or similar dimension field
    REQUISITIONTOTAL AS RequisitionTotalAmount
FROM PurchaseRequisitionHeaderEntity;

-- Entity 2: Purchase requisition lines
-- Purpose: Provides line-level details, amounts, and links to Purchase Orders.
SELECT
    REQUISITIONNUMBER AS PurchaseRequisitionId,
    LINENUMBER,
    REQUISITIONINGSTATUS AS LineStatus,
    MODIFIEDDATETIME AS EventTime_LineClosed, -- Used if status is Closed
    PURCHASEORDERNUMBER,
    PURCHASEORDERLINENUMBER
FROM PurchaseRequisitionLineEntity;

-- Entity 3: Workflow tracking status table
-- Purpose: The primary source for all workflow-driven activities.
-- Note: Requires joining to PurchReqTable on RecId = CorrelationId post-export.
SELECT
    CONTEXT, -- Contains information like step name
    CORRELATIONID, -- Links to source document's RecId
    CREATEDDATETIME AS EventTime_WorkflowStep,
    TRACKINGSTATUS AS ActivityName, -- (e.g., Submitted, Approved, Rejected, Canceled)
    USERID AS User
FROM WorkflowTrackingStatusTableEntity;

-- Entity 4: Workflow work items
-- Purpose: Captures the start of an approval task assigned to a user.
-- Note: Requires joining to PurchReqTable on RecId = CorrelationId post-export.
SELECT
    CORRELATIONID, -- Links to source document's RecId
    CREATEDDATETIME AS EventTime_ApprovalStepStarted,
    USERID AS User, -- The user assigned the task
    DESCRIPTION AS ApprovalStep
FROM WorkflowWorkItemTableEntity;

-- Entity 5: Purchase order lines V2
-- Purpose: Used to find the creation date of the resulting Purchase Order.
-- Note: Requires joining to PurchaseRequisitionLineEntity on PO/Line number post-export.
SELECT
    PURCHASEORDERNUMBER,
    LINENUMBER,
    CREATEDDATETIME AS EventTime_PurchaseOrderCreated
FROM PurchaseOrderLinesV2Entity;

Steps

Register an Azure Active Directory Application: Navigate to the Azure portal and register a new application. This application will be used to authenticate with the Dynamics 365 API. Note the Application (client) ID and Directory (tenant) ID.
Generate a Client Secret: Within the newly registered application in Azure, create a new client secret under 'Certificates & secrets'. Securely copy the secret value, as it will not be visible again.
Grant API Permissions: In the application's 'API permissions' section, add a permission for 'Dynamics ERP'. Select 'Delegated permissions' and grant Financials.ReadWrite.All or a more restrictive read-only equivalent. Grant admin consent for the permissions.
Install Python Libraries: Ensure you have Python installed. Then, install the necessary libraries using pip: pip install requests pandas.
Configure the Python Script: Copy the provided Python script into a file, for example extract_d365_requisitions.py. Fill in the placeholder variables at the top of the script: your Dynamics 365 URL, Tenant ID, Client ID, Client Secret, the desired date range, and the company ID.
Understand OData Entities: The script queries three main data entities: PurchaseRequisitionHeaders for the main requisition details, PurchaseRequisitionLines for line-item specifics including conversion to Purchase Orders, and WorkflowTrackingStatusTable for all workflow-related events like submissions and approvals.
Execute the Script: Run the script from your command line: python extract_d365_requisitions.py. The script will authenticate, fetch data for each activity, and handle API pagination automatically.
Data Transformation: The script processes the raw JSON data from the API calls. It reshapes the data by iterating through records and creating a distinct row for each of the 12 business activities, mapping source fields to the standard event log columns like PurchaseRequisitionId, ActivityName, and EventTime.
Combine and Standardize Events: All generated events are collected into a single list and then converted into a pandas DataFrame. The script adds static columns like SourceSystemId and the LastDataIngestionTimestamp.
Export to CSV: The final DataFrame is exported to a CSV file named d365_purchase_requisition_log.csv. This file is formatted for direct upload into a process mining tool. Verify the file is encoded in UTF-8.

Configuration

Prerequisites: Access to an Azure subscription to register an application. An Application (client) ID, Client Secret, and Tenant ID from Azure Active Directory. The user running the script needs credentials that grant access to the Dynamics 365 Finance and Operations OData API endpoints.
API Endpoint Configuration: The script requires the base URL of your Dynamics 365 environment, typically in the format https://[your-environment-name].operations.dynamics.com.
Date Range: Set the START_DATE and END_DATE variables in the script to define the extraction window. For initial analysis, a period of 3 to 6 months is recommended to balance data volume and insight.
Company Filter: The script is configured to filter for a specific legal entity using the COMPANY_ID variable. To extract data for all companies, you may need to modify the OData filter and include cross-company=true in the request headers.
Performance Considerations: The script fetches only the columns necessary for the event log by using $select in the OData queries, which improves performance. It also handles pagination to manage large datasets without overwhelming the API. For very large historical loads, consider running the script in batches with smaller date ranges.

a Sample Query python

import requests
import pandas as pd
from datetime import datetime, timezone
import os

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Dynamics 365 environment details
D365_URL = class="hl-string">"https://[your-d365-url].operations.dynamics.com"
TENANT_ID = class="hl-string">"[your-tenant-id]"
CLIENT_ID = class="hl-string">"[your-client-id]"
CLIENT_SECRET = class="hl-string">"[your-client-secret]"

class=class="hl-string">"hl-comment"># --- Parameters ---
class=class="hl-string">"hl-comment"># Set the data extraction window
START_DATE = class="hl-string">"2023-01-01T00:00:00Z"
END_DATE = class="hl-string">"2023-12-31T23:59:59Z"
COMPANY_ID = class="hl-string">"[YourCompanyLegalEntity]" class=class="hl-string">"hl-comment"># e.g., class="hl-string">"USMF"
OUTPUT_FILENAME = class="hl-string">"d365_purchase_requisition_log.csv"


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_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">'Accept': class="hl-string">'application/json'
    }
    while entity_url:
        response = requests.get(entity_url, headers=headers)
        response.raise_for_status()
        data = response.json()
        all_records.extend(data[class="hl-string">'value'])
        entity_url = data.get(class="hl-string">'@odata.nextLink')
    return all_records

def main():
    class="hl-string">"""Main function to extract, transform, and save the event log."""
    print(class="hl-string">"Acquiring access token...")
    access_token = get_access_token()
    events = []
    last_ingestion_ts = datetime.now(timezone.utc).isoformat()

    class=class="hl-string">"hl-comment"># 1. Fetch Purchase Requisition Headers for class="hl-string">'Created' and class="hl-string">'Closed' events
    print(class="hl-string">"Fetching Purchase Requisition Headers...")
    pr_headers_url = (
        fclass="hl-string">"{D365_URL}/data/PurchaseRequisitionHeaders?" 
        fclass="hl-string">"$filter=dataAreaId eq '{COMPANY_ID}' and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}" 
        fclass="hl-string">"&$select=RequisitionNumber,RequisitionStatus,CreatedDateTime,CreatedBy,RequestingDepartmentName,RequisitionTotalAmount,RequisitionUrgencyLevel"
    )
    pr_headers = fetch_data(access_token, pr_headers_url)

    for prh in pr_headers:
        case_id = prh.get(class="hl-string">'RequisitionNumber')
        class=class="hl-string">"hl-comment"># Activity: Requisition Created
        events.append({
            class="hl-string">'PurchaseRequisitionId': case_id,
            class="hl-string">'ActivityName': class="hl-string">'Requisition Created',
            class="hl-string">'EventTime': prh.get(class="hl-string">'CreatedDateTime'),
            class="hl-string">'User': prh.get(class="hl-string">'CreatedBy'),
            class="hl-string">'Department': prh.get(class="hl-string">'RequestingDepartmentName'),
            class="hl-string">'RequisitionStatus': prh.get(class="hl-string">'RequisitionStatus'),
            class="hl-string">'RequisitionTotalAmount': prh.get(class="hl-string">'RequisitionTotalAmount'),
            class="hl-string">'UrgencyLevel': prh.get(class="hl-string">'RequisitionUrgencyLevel'),
            class="hl-string">'ApprovalStep': None,
            class="hl-string">'ProcessingTime': None
        })
        class=class="hl-string">"hl-comment"># Activity: Requisition Closed
        if prh.get(class="hl-string">'RequisitionStatus') == class="hl-string">'Closed':
            class=class="hl-string">"hl-comment"># Note: We need a reliable timestamp for closure. Using CreatedDateTime as a placeholder.
            class=class="hl-string">"hl-comment"># A better source might be the last workflow approval or line closure date.
            events.append({
                class="hl-string">'PurchaseRequisitionId': case_id,
                class="hl-string">'ActivityName': class="hl-string">'Requisition Closed',
                class="hl-string">'EventTime': prh.get(class="hl-string">'CreatedDateTime'), class=class="hl-string">"hl-comment"># Placeholder timestamp
                class="hl-string">'User': prh.get(class="hl-string">'CreatedBy'), class=class="hl-string">"hl-comment"># Placeholder user
                class="hl-string">'Department': prh.get(class="hl-string">'RequestingDepartmentName'),
                class="hl-string">'RequisitionStatus': class="hl-string">'Closed',
                class="hl-string">'RequisitionTotalAmount': prh.get(class="hl-string">'RequisitionTotalAmount'),
                class="hl-string">'UrgencyLevel': prh.get(class="hl-string">'RequisitionUrgencyLevel'),
                class="hl-string">'ApprovalStep': None,
                class="hl-string">'ProcessingTime': None
            })

    class=class="hl-string">"hl-comment"># 2. Fetch Workflow History for workflow-related events
    print(class="hl-string">"Fetching Workflow Tracking Status Table...")
    workflow_url = (
        fclass="hl-string">"{D365_URL}/data/WorkflowTrackingStatusTable?"
        fclass="hl-string">"$filter=dataAreaId eq '{COMPANY_ID}' and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE} and startswith(Context,'PurchReq')"
        fclass="hl-string">"&$select=Context,CreatedDateTime,TrackingType,User,WorkItemSubject"
    )
    workflow_items = fetch_data(access_token, workflow_url)

    for item in workflow_items:
        context_parts = item.get(class="hl-string">'Context', class="hl-string">'').split(class="hl-string">':')
        if len(context_parts) < 2:
            continue
        case_id = context_parts[1].strip()
        tracking_type = item.get(class="hl-string">'TrackingType')
        activity_map = {
            class="hl-string">'Submission': class="hl-string">'Requisition Submitted for Approval',
            class="hl-string">'Started': class="hl-string">'Approval Step Started',
            class="hl-string">'Completion': class="hl-string">'Approval Step Approved',
            class="hl-string">'Rejection': class="hl-string">'Approval Step Rejected',
            class="hl-string">'Cancellation': class="hl-string">'Requisition Withdrawn',
            class="hl-string">'Recall': class="hl-string">'Requisition Amended',
            class="hl-string">'Approved': class="hl-string">'Requisition Approved',
            class="hl-string">'Fault': class="hl-string">'Requisition Rejected'
        }
        activity_name = activity_map.get(tracking_type)
        if activity_name:
            events.append({
                class="hl-string">'PurchaseRequisitionId': case_id,
                class="hl-string">'ActivityName': activity_name,
                class="hl-string">'EventTime': item.get(class="hl-string">'CreatedDateTime'),
                class="hl-string">'User': item.get(class="hl-string">'User'),
                class="hl-string">'Department': None, class=class="hl-string">"hl-comment"># Department info is not in this entity
                class="hl-string">'RequisitionStatus': None, class=class="hl-string">"hl-comment"># Status info is not in this entity
                class="hl-string">'RequisitionTotalAmount': None,
                class="hl-string">'UrgencyLevel': None,
                class="hl-string">'ApprovalStep': item.get(class="hl-string">'WorkItemSubject'),
                class="hl-string">'ProcessingTime': None
            })

    class=class="hl-string">"hl-comment"># 3. Fetch Purchase Requisition Lines for PO Creation and Line Closure
    print(class="hl-string">"Fetching Purchase Requisition Lines...")
    pr_lines_url = (
        fclass="hl-string">"{D365_URL}/data/PurchaseRequisitionLines?"
        fclass="hl-string">"$filter=dataAreaId eq '{COMPANY_ID}' and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}"
        fclass="hl-string">"&$select=RequisitionNumber,PurchaseOrderNumber,IsPrepaymentRequired,LineStatus,ModifiedDateTime,ModifiedBy"
    )
    pr_lines = fetch_data(access_token, pr_lines_url)

    for prl in pr_lines:
        case_id = prl.get(class="hl-string">'RequisitionNumber')
        po_number = prl.get(class="hl-string">'PurchaseOrderNumber')
        line_status = prl.get(class="hl-string">'LineStatus')
        
        class=class="hl-string">"hl-comment"># Activity: Purchase Order Created
        if po_number:
             events.append({
                class="hl-string">'PurchaseRequisitionId': case_id,
                class="hl-string">'ActivityName': class="hl-string">'Purchase Order Created',
                class="hl-string">'EventTime': prl.get(class="hl-string">'ModifiedDateTime'),
                class="hl-string">'User': prl.get(class="hl-string">'ModifiedBy'),
                class="hl-string">'Department': None,
                class="hl-string">'RequisitionStatus': None,
                class="hl-string">'RequisitionTotalAmount': None,
                class="hl-string">'UrgencyLevel': None,
                class="hl-string">'ApprovalStep': fclass="hl-string">"PO: {po_number}",
                class="hl-string">'ProcessingTime': None
            })

        class=class="hl-string">"hl-comment"># Activity: Requisition Line Closed
        if line_status == class="hl-string">'Closed':
             events.append({
                class="hl-string">'PurchaseRequisitionId': case_id,
                class="hl-string">'ActivityName': class="hl-string">'Requisition Line Closed',
                class="hl-string">'EventTime': prl.get(class="hl-string">'ModifiedDateTime'),
                class="hl-string">'User': prl.get(class="hl-string">'ModifiedBy'),
                class="hl-string">'Department': None,
                class="hl-string">'RequisitionStatus': class="hl-string">'Closed',
                class="hl-string">'RequisitionTotalAmount': None,
                class="hl-string">'UrgencyLevel': None,
                class="hl-string">'ApprovalStep': None,
                class="hl-string">'ProcessingTime': None
            })

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

    class=class="hl-string">"hl-comment"># 4. Create and format the DataFrame
    print(class="hl-string">"Formatting data...")
    df = pd.DataFrame(events)
    df[class="hl-string">'SourceSystemId'] = class="hl-string">'Microsoft Dynamics 365'
    df[class="hl-string">'LastDataIngestionTimestamp'] = last_ingestion_ts

    class=class="hl-string">"hl-comment"># Fill forward missing data within each case
    df = df.sort_values(by=[class="hl-string">'PurchaseRequisitionId', class="hl-string">'EventTime'])
    df[[class="hl-string">'Department', class="hl-string">'RequisitionTotalAmount', class="hl-string">'UrgencyLevel']] = \
        df.groupby(class="hl-string">'PurchaseRequisitionId')[[class="hl-string">'Department', class="hl-string">'RequisitionTotalAmount', class="hl-string">'UrgencyLevel']].ffill().bfill()

    class=class="hl-string">"hl-comment"># Ensure required columns exist and are in order
    final_columns = [
        class="hl-string">'PurchaseRequisitionId',
        class="hl-string">'ActivityName',
        class="hl-string">'EventTime',
        class="hl-string">'SourceSystemId',
        class="hl-string">'LastDataIngestionTimestamp',
        class="hl-string">'User',
        class="hl-string">'Department',
        class="hl-string">'RequisitionStatus',
        class="hl-string">'RequisitionTotalAmount',
        class="hl-string">'UrgencyLevel',
        class="hl-string">'ApprovalStep',
        class="hl-string">'ProcessingTime'
    ]
    for col in final_columns:
        if col not in df.columns:
            df[col] = None
    
    df = df[final_columns]

    class=class="hl-string">"hl-comment"># 5. Save to CSV
    df.to_csv(OUTPUT_FILENAME, index=False, encoding=class="hl-string">'utf-8')
    print(fclass="hl-string">"Successfully extracted {len(df)} events to {OUTPUT_FILENAME}")

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

Steps

Access Data Management Workspace: Log in to Microsoft Dynamics 365 and navigate to the Workspaces menu. Select Data management to open the central hub for data import and export operations.
Create New Export Project: Within the Data Management workspace, click the Export tile. Provide a unique Group name, a descriptive Description, and set the Project category to Project. This organizes your data extraction tasks.
Add Data Entities: Add the necessary standard data entities to your project. Click Add entity and search for each of the following, adding them one by one:
- Purchase requisition headers
- Purchase requisition lines
- Workflow tracking status table
- Workflow work items
- Purchase order lines V2
Define Export Format: For each added entity, select a Target data format such as CSV or Excel. CSV is generally recommended for performance and compatibility with data transformation tools.
Apply Data Filters: Select an entity, then click the Filter button. On the Range tab, specify the Legal entity (company code) to ensure you are extracting data from the correct business unit. It is also critical to add a date filter on the Created date and time field of the Purchase requisition headers entity to limit the data volume. We recommend starting with a 3 to 6 month period.
Run the Export Job: Once all entities and filters are configured, click Export on the Action Pane. The system will create a job package and begin processing. You can monitor the job's progress in the Job history section.
Download the Data Package: After the export job completes successfully, navigate to the Job history details and click Download package. This will download a ZIP file containing the separate data files for each entity.
Transform Raw Data into Event Log: The exported files represent raw system tables, not a final event log. You must use an external tool like Python, Power Query, or an ETL platform to combine and reshape the data. This involves:
- Joining the header and line files.
- Joining workflow data to requisitions using the CorrelationId and the RecId of the purchase requisition header.
- Pivoting status changes and workflow events from the workflow tables into distinct activity rows (Requisition Submitted, Approval Step Approved, etc.).
- Creating a new row for each activity with the correct ActivityName, EventTime, and PurchaseRequisitionId.
Calculate and Map Attributes: During the transformation, map source columns to the required attributes like User and Department. Calculate derived attributes like RequisitionTotalAmount by summing line amounts. Set a static value for SourceSystemId (e.g., 'D365_F&O') and use the execution time of your script for LastDataIngestionTimestamp.
Finalize and Format the Output: Ensure the final output file is a single CSV file where each row represents one event in the process. The columns must match the required event log structure, including PurchaseRequisitionId, ActivityName, and EventTime.

Configuration

Required Permissions: Users must have a security role with permissions to the Data management workspace and access to the specific data entities being exported, such as Data management administrator or a similar custom role.
Key Data Entities:
- Purchase requisition headers: Provides the main case identifier, creation timestamp, and overall status.
- Purchase requisition lines: Contains details about each requested item, including amounts and links to purchase orders.
- Workflow tracking status table: The primary source for workflow events like submission, approval, rejection, and recall.
- Workflow work items: Details the specific tasks assigned to users during the approval process.
- Purchase order lines V2: Used to find the creation timestamp of the purchase order linked to a requisition line.
Date Range Filtering: It is crucial to apply a date range filter to the Created date and time field on the Purchase requisition headers entity. A range of 3-6 months is recommended for initial analysis to balance data volume and process insight.
Company Code Filtering: Always apply a filter for the Legal entity to avoid extracting data from unintended or test companies. This significantly reduces data size and improves job performance.
Data Transformation: This method requires a significant post-processing step outside of Dynamics 365 to transform the raw entity exports into a structured event log. Be prepared to use a scripting or ETL tool.

a Sample Query config

/*
This is not a single executable query. Instead, it is a configuration manifest for a Dynamics 365 Data Management Framework Export Project.
You must add each entity listed below to the project and select the specified fields for export.
The transformation logic to create the final event log must be applied after exporting these files.
*/

-- Entity 1: Purchase requisition headers
-- Purpose: Captures requisition creation, overall status, and header-level attributes.
SELECT
    REQUISITIONNUMBER AS PurchaseRequisitionId,
    REQUISITIONSTATUS AS RequisitionStatus,
    CREATEDDATETIME AS EventTime_RequisitionCreated,
    MODIFIEDDATETIME AS EventTime_RequisitionClosed, -- Used if status is Closed
    PREPARERPERSONNELNUMBER AS User_Preparer,
    REQUESTERPERSONNELNUMBER AS User_Requester,
    DEFAULTLEDGERDIMENSIONDISPLAYVALUE AS Department, -- Or similar dimension field
    REQUISITIONTOTAL AS RequisitionTotalAmount
FROM PurchaseRequisitionHeaderEntity;

-- Entity 2: Purchase requisition lines
-- Purpose: Provides line-level details, amounts, and links to Purchase Orders.
SELECT
    REQUISITIONNUMBER AS PurchaseRequisitionId,
    LINENUMBER,
    REQUISITIONINGSTATUS AS LineStatus,
    MODIFIEDDATETIME AS EventTime_LineClosed, -- Used if status is Closed
    PURCHASEORDERNUMBER,
    PURCHASEORDERLINENUMBER
FROM PurchaseRequisitionLineEntity;

-- Entity 3: Workflow tracking status table
-- Purpose: The primary source for all workflow-driven activities.
-- Note: Requires joining to PurchReqTable on RecId = CorrelationId post-export.
SELECT
    CONTEXT, -- Contains information like step name
    CORRELATIONID, -- Links to source document's RecId
    CREATEDDATETIME AS EventTime_WorkflowStep,
    TRACKINGSTATUS AS ActivityName, -- (e.g., Submitted, Approved, Rejected, Canceled)
    USERID AS User
FROM WorkflowTrackingStatusTableEntity;

-- Entity 4: Workflow work items
-- Purpose: Captures the start of an approval task assigned to a user.
-- Note: Requires joining to PurchReqTable on RecId = CorrelationId post-export.
SELECT
    CORRELATIONID, -- Links to source document's RecId
    CREATEDDATETIME AS EventTime_ApprovalStepStarted,
    USERID AS User, -- The user assigned the task
    DESCRIPTION AS ApprovalStep
FROM WorkflowWorkItemTableEntity;

-- Entity 5: Purchase order lines V2
-- Purpose: Used to find the creation date of the resulting Purchase Order.
-- Note: Requires joining to PurchaseRequisitionLineEntity on PO/Line number post-export.
SELECT
    PURCHASEORDERNUMBER,
    LINENUMBER,
    CREATEDDATETIME AS EventTime_PurchaseOrderCreated
FROM PurchaseOrderLinesV2Entity;

Your Purchase to Pay - Requisition Data Template

Your Purchase to Pay - Requisition Data Template

Purchase to Pay - Requisition Attributes

Purchase to Pay - Requisition Activities

Extraction Guides

Dynamics 365 Data Management Framework Export

Steps

Configuration

a Sample Query config

Python Script using Dynamics 365 OData REST API

Steps

Configuration

a Sample Query python

Microsoft Power BI Native Connector Extraction

Steps

Configuration

a Sample Query config

Ready to Get Started?

Optimize Purchase to Pay - Requisition for Faster Approvals Now

Your Purchase to Pay - Requisition Data Template

Your Purchase to Pay - Requisition Data Template

Purchase to Pay - Requisition Attributes

Purchase to Pay - Requisition Activities

Extraction Guides

Dynamics 365 Data Management Framework Export

Steps

Configuration

a Sample Query config

Python Script using Dynamics 365 OData REST API

Steps

Configuration

a Sample Query python

Microsoft Power BI Native Connector Extraction

Steps

Configuration

a Sample Query config

We value your privacy

Ready to Get Started?

Optimize Purchase to Pay - Requisition for Faster Approvals Now