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

Your Purchase to Pay - Invoice Processing Data Template

Microsoft Dynamics 365

Purchase to Pay - Invoice Processing Attributes (23) Purchase to Pay - Invoice Processing Activities (14) Extraction Guides (4)

Your Purchase to Pay - Invoice Processing Data Template

This template provides a clear guide for extracting the essential data needed to analyze your Purchase to Pay - Invoice Processing in Microsoft Dynamics 365. It outlines the crucial attributes to collect, the key activities to track, and practical guidance for data extraction. Use this resource to ensure you gather all necessary information for comprehensive process analysis and optimization.

Recommended attributes to collect for in-depth analysis
Key activities to track throughout the invoice processing lifecycle
Guidance on extracting data from Microsoft Dynamics 365

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

Download Template

Purchase to Pay - Invoice Processing Attributes

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

5 Required 7 Recommended 11 Optional

	Name	Description
	Activity Name ActivityName	The name of the specific business event or task that occurred at a point in time within the invoice processing lifecycle.
Description The Activity Name describes a specific step or status change in the invoice process, such as 'Invoice Registered', 'Invoice Sent For Approval', or 'Payment Executed'. This data is crucial for constructing the process map and understanding the sequence of events. Analyzing this attribute reveals the process flow, identifies common pathways, and highlights deviations or bottlenecks. It is used to calculate cycle times between activities, such as the duration from approval to posting, and to measure the frequency of specific events like rejections or payment blocks. Why it matters It defines the steps in the process map, allowing for visualization of the process flow and analysis of transitions between different activities. Where to get This attribute is typically derived from a combination of status fields, transaction types, or change log entries within various Dynamics 365 tables related to invoice processing. Examples Invoice Sent For ApprovalMatching Discrepancy FoundPayment Executed
	Event Time EventTime	The precise timestamp indicating when a specific activity or event occurred.
Description Event Time, or the timestamp, records the exact date and time that an activity took place. This is a critical component of the event log, providing the temporal sequence needed to order activities correctly and calculate durations. In analysis, this timestamp is the basis for all time-related metrics. It is used to calculate the duration of activities, the cycle time between different steps (e.g., approval time), and the total end-to-end processing time for each invoice. It also enables trend analysis over time. Why it matters This timestamp is essential for ordering events, calculating all cycle times and durations, and identifying process bottlenecks. Where to get This is sourced from various date/time fields across multiple tables, such as the createdDateTime or modifiedDateTime fields in tables like VendInvoiceInfoTable, VendTrans, or workflow history tables. Examples 2023-04-15T09:00:12Z2023-05-20T14:30:00Z2023-06-01T11:05:45Z
	Invoice Number InvoiceNumber	The unique identifier for each vendor invoice, serving as the primary case ID for tracking its lifecycle.
Description The Invoice Number is the unique key that links all activities associated with a single vendor invoice. It allows for the end-to-end tracing of the invoice's journey, from receipt and registration through matching, approval, and final payment. In process mining analysis, this attribute is fundamental. It defines the case, enabling the reconstruction of each invoice's process flow. This allows for the calculation of total cycle times, identification of process variants, and analysis of invoice-specific characteristics and outcomes. Why it matters This is the essential Case ID that connects all related events, making it possible to analyze the entire lifecycle of each individual invoice. Where to get This is typically found in the main vendor invoice table, such as VendInvoiceInfoTable, field Num. Examples INV-10056773245-AUS-001-98432
	Last Data Update LastDataUpdate	The timestamp indicating the last time the data for this process was refreshed from the source system.
Description This attribute records when the dataset was last extracted and updated from Microsoft Dynamics 365. It is typically the same for all records within a single data load. This information is vital for users to understand the freshness of the data they are analyzing. It provides context for how current the process insights are and is essential for managing data refresh schedules and validating data pipelines. Why it matters Informs users about the timeliness of the data, ensuring they know how recent the analysis is and when the next data update is expected. Where to get This timestamp is generated and stamped onto the dataset by the data extraction or ETL process at the time of execution. Examples 2023-10-27T02:00:00Z2023-10-28T02:00:00Z
	Source System SourceSystem	The system of record from which the event data was extracted.
Description This attribute identifies the source application where the activity data originated. For this process, it will typically be 'Microsoft Dynamics 365'. In environments with multiple systems, such as an external OCR or capture solution, this field helps differentiate where each step of the process occurs. It ensures data lineage is clear and helps in troubleshooting data extraction issues. Why it matters It provides crucial context about data origin, which is important for data validation and understanding the system landscape of the process. Where to get This is a static value, 'Microsoft Dynamics 365', added during data transformation to label the dataset's origin. Examples Microsoft Dynamics 365
	End Time EndTime	The precise timestamp indicating when a specific activity or event was completed.
Description The End Time records when an activity concluded. When combined with the Start Time (EventTime), it allows for the precise calculation of how long each individual step took to complete. This attribute is essential for calculating the 'ProcessingTime' for each activity, a key metric for performance analysis. It helps pinpoint exactly which steps in the process are consuming the most time, enabling targeted improvement efforts. For example, it can measure the exact duration of a manual coding or approval step. Why it matters Enables the direct calculation of activity processing times, which is fundamental for identifying the most time-consuming steps in the process. Where to get Like StartTime, this is sourced from various date/time fields. For some activities, it might be the StartTime of the next activity. In other cases, it might be a specific 'completed on' timestamp. Examples 2023-04-15T09:15:20Z2023-05-20T18:00:00Z2023-06-01T11:05:45Z
	Invoice Amount InvoiceAmount	The total monetary value of the invoice.
Description This attribute represents the total amount due as stated on the vendor invoice. It is a fundamental financial data point for each case. Invoice Amount is widely used for segmentation and analysis. It can reveal if high-value invoices follow a different, more stringent approval process or if they take longer to process. It is also used in dashboards to analyze invoice volumes by value and can be aggregated to understand total spend under analysis. Why it matters Enables analysis based on financial value, helping to prioritize process improvement efforts on high-value invoices and understand cost drivers. Where to get Found in the invoice header table, for example, VendInvoiceInfoTable, in a field like InvoiceAmount or Amount. Examples 1500.00250.7512500.50
	Invoice Status InvoiceStatus	The current status of the invoice at the time of data extraction.
Description The Invoice Status indicates the current state of an invoice within its lifecycle, such as 'Pending Approval', 'Approved', 'Posted', or 'Paid'. This provides a snapshot of the invoice's progress. This attribute is primarily used for creating operational dashboards that show the current workload and backlog. It helps managers understand how many invoices are in each stage of the process, supporting resource allocation and operational monitoring. It is key for the 'Invoice Processing Throughput & Status' dashboard. Why it matters Provides a current-state view of all invoices, essential for operational monitoring, workload management, and identifying current bottlenecks. Where to get This is typically derived from status fields on the main invoice table, like VendInvoiceInfoTable. Examples Pending ApprovalPostedPaid
	Payment Due Date PaymentDueDate	The date by which the invoice should be paid according to the agreed payment terms.
Description The Payment Due Date is calculated based on the invoice date and the vendor's payment terms. It represents the contractual deadline for payment. This date is critical for measuring financial performance and vendor relationship management. It is the baseline used to calculate the 'On-Time Payment Rate' KPI by comparing it to the actual 'Payment Executed' date. Analyzing this helps identify systemic issues causing late payments, which can lead to damaged vendor relationships or missed early payment discounts. Why it matters This is the benchmark for measuring on-time payment performance, a key KPI for financial health and vendor relations. Where to get This date is often calculated by the system and stored on the vendor transaction table, like VendTrans, based on invoice date and payment terms. Examples 2023-05-152023-06-302023-07-20
	Purchase Order Number PurchaseOrderNumber	The identifier of the Purchase Order (PO) to which the invoice is related.
Description The Purchase Order Number links an invoice to the original procurement document. This is critical for processes that involve 2-way or 3-way matching between the invoice, PO, and goods receipt. This attribute allows for analysis of PO versus non-PO invoices, which often follow different process paths. It is key to understanding the effectiveness of the matching process, investigating matching discrepancies, and measuring the rate of straight-through processing for PO-backed invoices. Why it matters It distinguishes PO-backed invoices from non-PO invoices, which is a primary driver of process variation and is crucial for matching analysis. Where to get Located in the invoice header table, such as VendInvoiceInfoTable, often in a field like PurchId. Examples PO-001234PO-005678PO-009101
	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. For automated steps, this might be a system or service account. Analyzing the process by user helps to understand workload distribution, identify training needs, and compare performance across individuals or teams. It is crucial for analyzing rework loops, where work is passed back and forth between users, and for understanding variations in how different users complete the same task. Why it matters It enables analysis of performance and workload by user or team, and helps identify automation levels and sources of rework. Where to get Sourced from user ID fields in transaction or workflow history tables, such as createdby or modifiedby fields. This information may be in the workflow tracking tables. Examples j.doea.smithAX_Admin
	Vendor Number VendorNumber	The unique identifier for the vendor or supplier who submitted the invoice.
Description The Vendor Number is the code that uniquely identifies a supplier in the master data. It links the invoice transaction to a specific vendor, enabling vendor-centric analysis. This attribute is essential for segmenting the process data by vendor. It helps answer questions like 'Which vendors have the longest approval times?' or 'Are matching discrepancies more common with certain vendors?'. Analyzing performance by vendor can uncover opportunities for supplier collaboration and process improvement. Why it matters Allows for filtering and root cause analysis by vendor, helping to identify performance issues or patterns tied to specific suppliers. Where to get This is typically found in the header of the vendor invoice table, such as VendInvoiceInfoTable, linked to the main vendor master data in VendTable. Examples V-1001V-2050V-8342
	Company Code CompanyCode	The identifier for the legal entity or company within the organization processing the invoice.
Description The Company Code represents the specific legal entity that is financially responsible for the invoice. In a multi-company organization, this is a critical data point for financial segmentation. This attribute allows for process analysis to be filtered or compared across different legal entities. It can reveal if process performance, policies, or bottlenecks are specific to certain companies within the group, supporting localized process improvement initiatives. Why it matters Allows for process analysis to be segmented by legal entity, which is crucial for large, multi-company organizations. Where to get This is a standard field in most financial tables in Dynamics 365, often called DataAreaId. Examples USMFDEMFGBSI
	Department Department	The cost center or department to which the invoice costs are allocated.
Description The Department identifies the business unit or cost center responsible for the expense on the invoice. This is usually determined during the invoice coding activity. Analyzing the process by department is key to understanding departmental differences in spending, approval times, and compliance. It helps to identify which departments have the most invoice exceptions or the longest approval cycles, providing a basis for targeted communication and training. Why it matters Enables cost and process analysis by department, highlighting variations in performance and compliance across the organization. Where to get Sourced from financial dimensions linked to the invoice lines or header. This requires querying the dimension-related data structures. Examples SalesMarketingIT Operations
	Discrepancy Type MatchingDiscrepancyType	Categorizes the type of mismatch found during the invoice and PO matching process.
Description When a discrepancy occurs during 2-way or 3-way matching, this attribute specifies the nature of the problem, for example, 'Price Mismatch', 'Quantity Mismatch', or 'Missing Goods Receipt'. This data is central to the 'Matching Discrepancy Resolution Time' dashboard. By analyzing the frequency of different discrepancy types, the business can identify the root causes of matching failures. For instance, frequent price mismatches may point to issues with outdated master data, while quantity mismatches could indicate shipping problems. Why it matters It categorizes matching failures, enabling root cause analysis to reduce the rate of discrepancies and improve straight-through processing. Where to get This may be stored in a dedicated matching or exception log table, or it might need to be derived from status messages or workflow comments. Examples Price MismatchQuantity MismatchMissing Goods Receipt
	Invoice Currency InvoiceCurrency	The currency code for the invoice amount (e.g., USD, EUR).
Description The Invoice Currency specifies the currency in which the invoice amount is denominated. This is important for multinational organizations dealing with suppliers from different countries. This attribute provides necessary context for the Invoice Amount and is used for filtering and reporting, especially in global operations. It ensures that financial values are interpreted correctly and allows for currency-specific analysis or conversion to a standard reporting currency. Why it matters Provides essential context for financial amounts, allowing for accurate interpretation and analysis of invoice values across different regions. Where to get Located in the invoice header table alongside the invoice amount, for example, VendInvoiceInfoTable field CurrencyCode. Examples USDEURGBP
	Is Automated IsAutomated	A flag indicating whether an activity was performed automatically by the system or by a human user.
Description This boolean attribute (true/false) distinguishes between system-executed events and manual user tasks. For example, an invoice matching step might be automated, while discrepancy handling is manual. Analyzing this attribute is key to understanding the level of automation in the process. It helps measure the success of automation initiatives, identifies remaining manual bottlenecks, and highlights opportunities for further automation to improve efficiency and reduce errors. Why it matters Helps measure the level of automation in the process, identifying manual bottlenecks and opportunities for efficiency gains. Where to get This is typically derived based on the 'User' attribute. If the user is a known system or service account, this flag is set to true. Examples truefalse
	Is On-Time Payment IsOnTimePayment	A calculated flag that indicates if an invoice was paid on or before its due date.
Description This boolean attribute is derived by comparing the timestamp of the 'Payment Executed' activity with the 'Payment Due Date'. If the payment date is less than or equal to the due date, the value is true, otherwise it is false. This attribute directly supports the 'On-Time Payment Rate' KPI and the 'Payment Terms Compliance' dashboard. It simplifies the analysis by providing a clear binary outcome for each invoice, making it easy to filter for late payments and investigate their root causes by vendor, department, or country. Why it matters Directly measures compliance with payment terms, simplifying the calculation of the On-Time Payment Rate KPI and related analyses. Where to get This attribute is calculated in the data transformation layer. The logic is: (Timestamp of 'Payment Executed' activity) <= (Payment Due Date). Examples truefalse
	Payment Block Reason PaymentBlockReason	The reason code explaining why a payment block was applied to an invoice.
Description A payment block prevents an invoice from being paid. This attribute captures the reason for the block, which is usually a standardized code selected by a user, such as 'Disputed charges' or 'Waiting for goods receipt'. This attribute is essential for the 'Payment Block Frequency & Duration' dashboard. Analyzing the most common reasons for payment blocks helps identify underlying issues in the P2P process. Resolving these root causes can significantly reduce payment delays and improve vendor relationships. Why it matters Explains why payments are intentionally delayed, helping to identify and resolve upstream issues that cause payment blocks. Where to get This is typically a field on the vendor transaction or invoice record, often related to the payment block status. Look in tables like VendTrans. Examples Quality disputeAwaiting credit memoManual block
	Payment Terms PaymentTerms	The payment terms agreement with the vendor, such as 'Net 30' or '2% 10, Net 30'.
Description Payment Terms define the agreed-upon conditions for paying the vendor, including the timeframe and any potential discounts. This information is usually sourced from the vendor master data or the purchase order. This attribute provides crucial context for the Payment Due Date and the On-Time Payment Rate KPI. Analyzing whether certain payment terms are correlated with late payments can reveal operational challenges. It also helps in financial planning and managing working capital effectively. Why it matters Provides context for payment due dates and helps analyze the financial impact of payment timing and discount capture rates. Where to get This information is stored in the vendor master (VendTable) and copied to transaction tables like VendInvoiceInfoTable. Examples Net 30Net 602% 10, Net 30
	Processing Time ProcessingTime	The duration of a single activity, calculated as the difference between its end and start time.
Description Processing Time measures the active time spent on a specific task. It is calculated by subtracting the StartTime of an event from its EndTime. This is also referred to as activity duration. This metric is fundamental for performance analysis. It helps identify which specific activities are the most time-consuming, as opposed to waiting times between activities. This allows for focused improvements, such as providing better training to speed up manual coding or optimizing a system step that is running slowly. Why it matters Measures the actual work duration of activities, helping to pinpoint inefficient tasks that are ripe for optimization or automation. Where to get This is not a field in the source system. It is calculated during data transformation using the formula: EndTime - StartTime. Examples PT1H30MP2DT4HPT5M15S
	Rejection Reason RejectionReason	The reason provided by an approver for rejecting an invoice.
Description When an invoice is rejected during the approval workflow, the approver typically provides a reason. This attribute captures that explanation, which could be a predefined code or free-form text. This is a critical attribute for the 'Invoice Rejection Analysis' dashboard. It provides the qualitative insight needed to understand the root causes of rejections, such as 'Incorrect PO Number', 'Duplicate Invoice', or 'Price Mismatch'. Analyzing these reasons helps prioritize actions to reduce the rejection rate. Why it matters Provides direct insight into why invoices are rejected, enabling targeted actions to reduce rework and improve first-time-right rates. Where to get Typically found in the comments or history logs of the workflow processing tables. Examples Incorrect quantityDuplicate invoiceApproval limit exceeded
	Total Cycle Time TotalCycleTime	The total end-to-end duration for processing a single invoice, from receipt to payment.
Description Total Cycle Time measures the entire duration an invoice spends in the process. It is calculated as the time difference between the very first activity (e.g., 'Invoice Registered') and the very last activity (e.g., 'Payment Executed') for a given invoice number. This is a primary KPI for measuring overall process efficiency. It provides a high-level view of performance and is used in the 'Invoice End-to-End Cycle Time' dashboard. Analyzing trends in this metric and segmenting it by attributes like vendor or amount helps identify broad opportunities for improvement. Why it matters Represents the key high-level KPI for overall process speed and efficiency, summarizing the performance for each invoice. Where to get This is calculated at the case level within the process mining tool or during data preparation. It is the time difference between the first and last event timestamps for each CaseId. Examples P15DP22DT5H30MP7D

Required Recommended Optional

Purchase to Pay - Invoice Processing Activities

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

6 Recommended 8 Optional

	Activity	Description
	Invoice Approved	Represents the final, successful approval of the invoice within the workflow, authorizing it for posting and payment. This is an explicit event logged by the workflow engine upon completion.
Why it matters This is a critical milestone that concludes the approval process. The time from 'Invoice Sent for Approval' to this event is a key measure of approval efficiency and helps identify bottlenecks. Where to get This is an explicit event logged in the workflow history tables (e.g., WorkflowTrackingStatusTable) when the workflow status changes to 'Approved'. Capture Timestamp of the 'Approved' status entry in the workflow history. Event type explicit
	Invoice Matched To Purchase Order	Represents the successful completion of the matching process, where invoice details align with the purchase order and goods receipt information. This event is inferred when the matching status of the invoice is updated to 'Passed'.
Why it matters Successful matching is a key milestone for PO-backed invoices, confirming the validity of the charge before payment. It's essential for tracking matching efficiency and automation rates. Where to get Inferred from the update of the matching status field on the vendor invoice header or lines (e.g., VendInvoiceInfoTable) to a 'Passed' or 'Successfully Matched' value. Capture Timestamp of the status change to 'Passed' in the invoice matching validation details. Event type inferred
	Invoice Posted	This is the formal accounting event where the approved invoice is recorded in the General Ledger, creating a liability. This is an explicit, transactional event in the system.
Why it matters Posting is the final step before payment and is a key financial control point. The time from 'Invoice Approved' to 'Invoice Posted' measures the efficiency of the final accounting steps. Where to get This is an explicit event captured from the creation of the posted vendor transaction in tables like VendTrans and the associated GeneralJournalEntry and LedgerEntry records. Capture Creation timestamp of the record in the vendor transaction table (VendTrans). Event type explicit
	Invoice Registered	This is the initial entry of an invoice into the system, creating a placeholder record before full details are processed. This activity is captured when a new record is created in the vendor invoice register journal.
Why it matters This activity marks the official start of the invoice processing lifecycle. Analyzing the time from this point helps measure the total processing duration and identifies early-stage delays. Where to get This is an explicit event captured from the creation of a record in the vendor invoice register, typically in the VendInvoiceRegisterJournalTable or a similar pending invoice table. Capture Creation timestamp of the record in the Vendor Invoice Register Journal. Event type explicit
	Invoice Sent For Approval	This activity marks the formal submission of an invoice into a workflow for review and approval. The Dynamics 365 workflow engine explicitly records this submission event.
Why it matters This is the starting point for measuring the entire approval cycle. It helps identify how long invoices wait before the approval process even begins and is the trigger for approval-related KPIs. Where to get This is an explicit event logged in the workflow history tables (e.g., WorkflowTrackingStatusTable) when an invoice is submitted to a workflow. Capture Creation timestamp of the workflow submission record in the workflow history. Event type explicit
	Payment Executed	This is the final activity, representing the posting of the payment journal which creates the payment and settles the invoice. This is an explicit transactional event that closes the invoice lifecycle.
Why it matters This activity marks the successful end of the invoice-to-pay process. It is the basis for measuring the 'On-Time Payment Rate' and the total end-to-end cycle time. Where to get This is an explicit event captured from the posting of the payment journal. The settlement information is recorded in tables like VendSettlement, linking the payment to the invoice. Capture Posting timestamp of the payment journal that settles the vendor transaction. Event type explicit
	Invoice Coded	This activity signifies that General Ledger account distributions have been assigned to the invoice lines. It is typically inferred by the creation or finalization of ledger account distributions linked to the invoice.
Why it matters Coding is a critical step for financial accuracy. Measuring the time taken to code invoices helps identify bottlenecks in the accounting review process and supports the 'Avg Data Capture to Coded Time' KPI. Where to get Inferred from the creation and validation of records in accounting distribution tables (e.g., AccountingDistribution) associated with the pending vendor invoice. Capture Timestamp when accounting distributions for the invoice are saved and validated. Event type inferred
	Invoice Data Captured	Represents the completion of data entry for the invoice, including header and line details, before it is submitted for matching or approval. This is often inferred when the invoice record moves from a 'new' or 'registered' status to a 'ready for processing' state.
Why it matters Tracking this activity helps measure the efficiency of the data entry process, whether manual or automated (OCR). Delays here can cascade through the entire process. Where to get Inferred from status changes on the pending vendor invoice record (e.g., VendInvoiceInfoTable). The event occurs when all required fields are populated and the invoice is ready for the next step. Capture Detect status change on the pending invoice table indicating data entry completion. Event type inferred
	Invoice Rejected	Indicates that an approver has rejected the invoice, halting the process and typically sending it back for correction. The workflow engine explicitly logs this rejection event.
Why it matters Tracking rejections helps quantify rework and identify common reasons for failure, such as incorrect coding or policy violations. This directly supports the 'Invoice Rejection Rate' KPI. Where to get This is an explicit event logged in the workflow history tables (e.g., WorkflowTrackingStatusTable) when the workflow status changes to 'Rejected' or 'Canceled'. Capture Timestamp of the 'Rejected' status entry in the workflow history. Event type explicit
	Matching Discrepancy Found	This activity occurs when the invoice matching process fails due to misalignments between the invoice, purchase order, or goods receipt. It is captured when the invoice matching status is set to 'Failed' or 'Discrepancy'.
Why it matters Identifying when and why discrepancies occur is crucial for the 'Matching Discrepancy Rate' KPI. This activity marks the start of the resolution process, which is often a source of significant delays. Where to get Inferred from the update of the matching status field on the vendor invoice (e.g., VendInvoiceInfoTable) to a 'Failed' value. The reason for failure is often logged as well. Capture Timestamp of the status change to 'Failed' or 'Discrepancy' during matching. Event type inferred
	Matching Discrepancy Handled	Marks the resolution of a previously identified matching discrepancy, allowing the invoice to proceed. This is inferred when an invoice with a 'Failed' matching status is successfully re-matched or manually overridden.
Why it matters This activity concludes the discrepancy resolution sub-process. The time between 'Matching Discrepancy Found' and this event is a key KPI for measuring resolution efficiency. Where to get Inferred by a successful matching event ('Invoice Matched To Purchase Order') that occurs after a 'Matching Discrepancy Found' event for the same invoice. Capture Identify a 'Passed' matching status timestamp that follows a 'Failed' status timestamp. Event type inferred
	Payment Block Released	Represents the removal of a payment hold, allowing the invoice to proceed to payment scheduling. This is captured when the payment block field is cleared or changed to an unblocked state.
Why it matters Measures the time it takes to resolve issues causing payment holds. Long durations between a block being set and released indicate inefficient problem-solving processes. Where to get Inferred from a change that clears the payment hold or block field on the posted vendor transaction (VendTrans). The change log can provide the timestamp. Capture Timestamp of the update that clears the payment block field on the vendor transaction. Event type inferred
	Payment Block Set	This activity occurs when a hold is placed on an invoice, preventing it from being paid. This is captured by a change in the payment block or hold status field on the posted invoice record.
Why it matters Payment blocks are a primary cause of late payments. Analyzing when and why they are set is crucial for improving on-time payment performance and vendor relations. Where to get Inferred from a change to the payment hold or block field on the posted vendor transaction (VendTrans). The change log can be used to capture the timestamp. Capture Timestamp of the update that sets the payment block field on the vendor transaction. Event type inferred
	Payment Scheduled	This activity occurs when a posted invoice is included in a payment proposal or a payment journal, but before the payment is executed. It is an explicit action creating a payment journal line.
Why it matters This marks the transition from Accounts Payable to Treasury operations. Analyzing this step can reveal delays between invoice posting and payment initiation. Where to get This is an explicit event captured from the creation of a line in a payment journal (LedgerJournalTrans) that settles the posted vendor transaction. Capture Creation timestamp of the payment journal line referencing the invoice. Event type explicit

Recommended Optional

Extraction Guides

How to get your data from Microsoft Dynamics 365

Steps

Navigate to the Data Management Workspace: Log in to Microsoft Dynamics 365 Finance. Use the search bar to find and navigate to the Data management workspace.
Create a New Export Project: Within the workspace, select the Export tile to create a new data export project. Provide a meaningful name for the project, for example, ProcessMind_Invoice_Processing_Export.
Define the Data Project: In the General tab for the new project, specify the Data format. CSV is a commonly used and recommended format. Set other parameters as needed.
Add Required Data Entities: Go to the Selected entities section and click Add entity. Search for and add the following core entities one by one. You will need to specify the target data format for each.
- VendInvoiceInfoTableEntity (Vendor invoice header)
- VendInvoiceTransEntity (Vendor invoice lines)
- VendInvoiceRegisterHeaderEntity (Invoice register journal header)
- WorkflowTrackingStatusTableEntity (Workflow history)
- AccountingDistributionEntity (GL distributions)
- VendInvoiceJour (Posted vendor invoices)
- VendTrans (Vendor transactions, for payment blocks)
- LedgerJournalTransVendPaymEntity (Vendor payment journal lines)
- VendSettlement (Vendor settlement transactions)
Apply Filters: For each entity, it is critical to apply filters to limit the data scope. Select an entity and click Filter. At a minimum, filter by Company (Legal entity) and a specific date range on a relevant date field like CREATEDDATETIME or INVOICEDATE. A typical range for initial analysis is the last 3 to 6 months.
Configure Entity Mappings: For each entity, review the source-to-target mapping. Ensure all required fields for the final event log are included in the export. No complex mapping changes are typically needed at this stage.
Run the Export Job: Once all entities are added and filtered, click Export on the Action Pane to start the job. You can run it immediately or schedule it for off-peak hours to minimize system load.
Download the Exported Data: After the export job completes successfully, navigate to the job history. From there, you can download the exported data package, which will contain separate files for each data entity.
Transform Data into an Event Log: The exported files are entity snapshots, not a consolidated event log. You must use an external tool like a database, Python script, or Power Query to process and combine these files. The conceptual query in the query section provides the complete logic for this transformation.
Execute the Transformation Logic: Load the exported CSV files into your chosen transformation environment. Execute the provided transformation query, which unnions data from all source files to create a single event log table with one row per business activity.
Finalize and Upload: Export the final transformed data into a single CSV file. Ensure the column headers match the requirements, for example, InvoiceNumber, ActivityName, EventTime. This file is now ready to be uploaded to ProcessMind.

Configuration

Data Entities: The core of the configuration is selecting the correct set of entities to cover the invoice lifecycle. Key entities include VendInvoiceInfoTableEntity, VendInvoiceJour, and WorkflowTrackingStatusTableEntity.
Export Data Format: Select CSV as the target data format for easy processing in subsequent transformation steps.
Filtering by Company: It is crucial to filter every data entity by the specific legal entity (Company) you are analyzing to avoid pulling irrelevant data.
Date Range Filtering: Apply a consistent date range filter across all relevant entities, such as VendInvoiceInfoTableEntity and VendInvoiceJour, based on creation or posting dates. Start with a recent period, for example, the last 6 months, to manage data volume.
Full push vs. Incremental: For the initial extraction, perform a full push. For subsequent refreshes, you can configure an incremental export to fetch only the data that has changed since the last run, reducing processing time.
Prerequisites: The user running the export must have appropriate security roles in Dynamics 365, typically including Data management administrator or a similar role with permissions to access the required data entities.

a Sample Query config

/*
-- This is a conceptual query to be executed in a database or data transformation tool.
-- The tables referenced (e.g., 'VendInvoiceInfoTableEntity') are the CSV files exported from Dynamics 365.
-- Replace '[Your_Source_System_Name]' with the actual source system identifier.
*/

-- 1. Invoice Registered
SELECT
    h.INVOICENUMBER AS "InvoiceNumber",
    'Invoice Registered' AS "ActivityName",
    h.CREATEDDATETIME AS "EventTime",
    h.CREATEDBY AS "User",
    h.INVOICEACCOUNT AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    h.INVOICEAMOUNT AS "InvoiceAmount",
    NULL AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    h.CREATEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceRegisterHeaderEntity h

UNION ALL

-- 2. Invoice Data Captured
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Invoice Data Captured' AS "ActivityName",
    i.CREATEDDATETIME AS "EventTime",
    i.CREATEDBY AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Pending' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    i.CREATEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i

UNION ALL

-- 3. Invoice Coded
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Invoice Coded' AS "ActivityName",
    MIN(a.CREATEDDATETIME) AS "EventTime",
    MIN(a.CREATEDBY) AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Pending' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    MIN(a.CREATEDDATETIME) AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i
JOIN AccountingDistributionEntity a ON i.INVOICENUMBER = a.SOURCEDOCUMENTLINE_INVOICENUMBER
GROUP BY i.INVOICENUMBER, i.VENDORACCOUNT, i.PURCHASEORDERNUMBER, i.INVOICEAMOUNT

UNION ALL

-- 4. Invoice Matched To Purchase Order
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Invoice Matched To Purchase Order' AS "ActivityName",
    i.MODIFIEDDATETIME AS "EventTime",
    i.MODIFIEDBY AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Matched' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    i.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i
WHERE i.LASTMATCHSTATUS = 'Passed'

UNION ALL

-- 5. Matching Discrepancy Found
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Matching Discrepancy Found' AS "ActivityName",
    i.MODIFIEDDATETIME AS "EventTime",
    i.MODIFIEDBY AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Discrepancy' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    i.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i
WHERE i.LASTMATCHSTATUS = 'Failed'

UNION ALL

-- 6. Matching Discrepancy Handled (Approximation)
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Matching Discrepancy Handled' AS "ActivityName",
    i.MODIFIEDDATETIME AS "EventTime",
    i.MODIFIEDBY AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Matched' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    i.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i
WHERE i.LASTMATCHSTATUS = 'Passed' AND i.MODIFIEDDATETIME > i.CREATEDDATETIME

UNION ALL

-- 7. Invoice Sent For Approval, 8. Invoice Approved, 9. Invoice Rejected
SELECT
    w.CONTEXT_INVOICENUMBER AS "InvoiceNumber",
    CASE w.TRACKINGSTATUS
        WHEN 'Submitted' THEN 'Invoice Sent For Approval'
        WHEN 'Completed' THEN 'Invoice Approved'
        WHEN 'Canceled' THEN 'Invoice Rejected'
        ELSE CONCAT('Workflow: ', w.TRACKINGSTATUS)
    END AS "ActivityName",
    w.CREATEDDATETIME AS "EventTime",
    w.USERID AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    w.TRACKINGSTATUS AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    w.CREATEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM WorkflowTrackingStatusTableEntity w
JOIN VendInvoiceInfoTableEntity i ON w.CONTEXT_RECID = i.RECID AND w.CONTEXT_TABLEID = i.TABLEID
WHERE w.TRACKINGTYPE = 'Approval'

UNION ALL

-- 10. Payment Block Set
SELECT
    v.INVOICENUMBER AS "InvoiceNumber",
    'Payment Block Set' AS "ActivityName",
    v.MODIFIEDDATETIME AS "EventTime",
    v.MODIFIEDBY AS "User",
    v.ACCOUNTNUM AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    v.AMOUNT AS "InvoiceAmount",
    'Blocked' AS "InvoiceStatus",
    v.DUEDATE AS "PaymentDueDate",
    v.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendTrans v
WHERE v.BLOCKED = 'Yes'

UNION ALL

-- 11. Payment Block Released
SELECT
    v.INVOICENUMBER AS "InvoiceNumber",
    'Payment Block Released' AS "ActivityName",
    v.MODIFIEDDATETIME AS "EventTime",
    v.MODIFIEDBY AS "User",
    v.ACCOUNTNUM AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    v.AMOUNT AS "InvoiceAmount",
    'Open' AS "InvoiceStatus",
    v.DUEDATE AS "PaymentDueDate",
    v.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendTrans v
WHERE v.BLOCKED = 'No' AND v.MODIFIEDDATETIME > v.CREATEDDATETIME

UNION ALL

-- 12. Invoice Posted
SELECT
    vj.INVOICENUM AS "InvoiceNumber",
    'Invoice Posted' AS "ActivityName",
    vj.POSTINGDATE AS "EventTime",
    vj.CREATEDBY AS "User",
    vj.INVOICEACCOUNT AS "VendorNumber",
    vj.PURCHID AS "PurchaseOrderNumber",
    vj.INVOICEAMOUNT AS "InvoiceAmount",
    'Posted' AS "InvoiceStatus",
    vj.DUEDATE AS "PaymentDueDate",
    vj.POSTINGDATE AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceJour vj

UNION ALL

-- 13. Payment Scheduled
SELECT
    l.INVOICENUMBER AS "InvoiceNumber",
    'Payment Scheduled' AS "ActivityName",
    l.CREATEDDATETIME AS "EventTime",
    l.CREATEDBY AS "User",
    l.ACCOUNTDISPLAYVALUE AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    l.DEBITAMOUNT + l.CREDITAMOUNT AS "InvoiceAmount",
    'Scheduled for Payment' AS "InvoiceStatus",
    l.DUEDATE AS "PaymentDueDate",
    l.CREATEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM LedgerJournalTransVendPaymEntity l

UNION ALL

-- 14. Payment Executed
SELECT
    vs.INVOICENUMBER AS "InvoiceNumber",
    'Payment Executed' AS "ActivityName",
    vs.TRANSDATE AS "EventTime",
    vs.CREATEDBY AS "User",
    vs.ACCOUNTNUM AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    vs.SETTLEMENTAMOUNT AS "InvoiceAmount",
    'Paid' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    vs.TRANSDATE AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendSettlement vs;

Steps

Prerequisite: Configure BYOD: Ensure that the Bring Your Own Database feature is configured for your Microsoft Dynamics 365 Finance and Operations environment. This involves setting up an Azure SQL Database and configuring D365 to export data to it.
Identify Data Entities: In D365, navigate to the Data management workspace. The core of this extraction relies on exporting specific data entities. Ensure the following entities are included in an export job targeted at your BYOD database: Vendor invoice journal, Vendor invoice lines, Vendor invoice document register, Workflow tracking status table, Vendor payment journal lines, Vendor settlements, and Accounting distributions.
Create and Schedule Export Job: In the Data management workspace, create a new export project. Add the entities listed above to the project. Configure the target data format to be your BYOD database. Set up a recurring schedule for the export job, for example, daily or hourly, to keep the data in your BYOD fresh.
Verify Data in Azure SQL: Connect to your Azure SQL Database using a tool like SQL Server Management Studio (SSMS) or Azure Data Studio. Verify that the tables corresponding to the D365 entities have been created and are populated with data.
Prepare SQL Query: Copy the provided SQL query. This query is designed to run directly against the tables created by the BYOD export process in your Azure SQL Database.
Connect and Execute: Open a new query window in your SQL client connected to the BYOD. Paste the copied SQL query into the window.
Set Query Parameters: Locate the placeholder variables at the top of the script. Replace {StartDate}, {EndDate}, and {CompanyCode} with your desired start date, end date, and the legal entity identifier, respectively. For example, SET @StartDate = '2023-01-01';, SET @EndDate = '2023-12-31';, SET @CompanyCode = 'USMF';.
Run the Query: Execute the complete SQL script. The query will collect and union data from multiple tables to build a chronological event log for each invoice.
Review the Results: The output will be a single table containing the event log with columns for InvoiceNumber, ActivityName, EventTime, and other specified attributes. Review the results to ensure they appear correct and complete.
Export the Event Log: Export the query results to a CSV file. Most SQL clients have a built-in function to save results to a file. Ensure the file is saved with UTF-8 encoding.
Prepare for Upload: Before uploading to a process mining tool, confirm the CSV file has the exact headers as specified in the query (InvoiceNumber, ActivityName, etc.). No further transformation of the file structure is needed.

Configuration

Key Data Entities: The extraction relies on several D365 data entities being exported to the BYOD. Critical entities include: VendInvoiceJour, VendInvoiceInfoTable, WorkflowTrackingStatusTable, VendSettlement, VendInvoiceRegisterHeader, LedgerJournalTrans, AccountingDistribution, and VendTrans.
Date Range Filtering: The provided query includes @StartDate and @EndDate parameters. It is recommended to start with a limited date range, such as 3-6 months of data, to ensure performance. The filter is applied to the invoice posting date or creation date.
Company Code Filtering: The @DataAreaId parameter is crucial for multi-company environments. Always filter for a specific legal entity (Company Code) to ensure the data context is correct.
BYOD Refresh Frequency: The timeliness of the data is entirely dependent on the schedule of the D365 export job. For near-real-time analysis, a high frequency (e.g., hourly) is required, which may have performance and cost implications on both D365 and Azure.
Prerequisites: This method requires an active Azure subscription, a provisioned Azure SQL Database, and administrator-level access in D365 to configure the Data management workspace.

a Sample Query sql

DECLARE @StartDate DATETIME = '{StartDate}';
DECLARE @EndDate DATETIME = '{EndDate}';
DECLARE @CompanyCode NVARCHAR(4) = '{CompanyCode}';

WITH BaseInvoices AS (
    SELECT
        h.INVOICEID AS InvoiceNumber,
        h.VENDORACCOUNT AS VendorNumber,
        h.PURCHID AS PurchaseOrderNumber,
        h.INVOICEAMOUNT AS InvoiceAmount,
        p.DUEDATE AS PaymentDueDate,
        h.DOCUMENTSTATUS AS InvoiceStatus,
        h.CREATEDBY AS CreatedByUser,
        h.CREATEDDATETIME AS CreatedDateTime,
        h.MODIFIEDBY AS ModifiedByUser,
        h.MODIFIEDDATETIME AS ModifiedDateTime,
        h.BLOCKED AS IsBlocked,
        h.POSTINGDATE AS PostingDate
    FROM VendInvoiceJour h
    LEFT JOIN VendTrans p ON h.INVOICEID = p.INVOICE AND h.DATAAREAID = p.DATAAREAID AND h.VENDORACCOUNT = p.ACCOUNTNUM
    WHERE h.DATAAREAID = @CompanyCode
      AND h.POSTINGDATE BETWEEN @StartDate AND @EndDate
)
-- 1. Invoice Registered
SELECT
    vpr.JOURNALID AS InvoiceNumber,
    'Invoice Registered' AS ActivityName,
    vpr.CREATEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    vpr.VENDORACCOUNT AS VendorNumber,
    NULL AS PurchaseOrderNumber,
    vpr.INVOICEAMOUNT AS InvoiceAmount,
    vpr.CREATEDBY AS User,
    'Registered' AS InvoiceStatus,
    vpr.DUEDATE AS PaymentDueDate,
    vpr.CREATEDDATETIME AS EndTime
FROM VendInvoiceRegisterHeader vpr
WHERE vpr.DATAAREAID = @CompanyCode AND vpr.CREATEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 2. Invoice Data Captured
SELECT
    viit.NUM AS InvoiceNumber,
    'Invoice Data Captured' AS ActivityName,
    viit.CREATEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    viit.VENDORACCOUNT AS VendorNumber,
    viit.PURCHID AS PurchaseOrderNumber,
    viit.INVOICEAMOUNT AS InvoiceAmount,
    viit.CREATEDBY AS User,
    'Pending' AS InvoiceStatus,
    NULL AS PaymentDueDate,
    viit.CREATEDDATETIME AS EndTime
FROM VendInvoiceInfoTable viit
WHERE viit.DATAAREAID = @CompanyCode AND viit.CREATEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 3. Invoice Coded
SELECT
    vil.INVOICEID AS InvoiceNumber,
    'Invoice Coded' AS ActivityName,
    MAX(ad.CREATEDDATETIME) AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    bij.VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    MAX(ad.CREATEDBY) AS User,
    bij.InvoiceStatus,
    bij.PaymentDueDate,
    MAX(ad.CREATEDDATETIME) AS EndTime
FROM AccountingDistribution ad
JOIN VendInvoiceInfoLine vil ON ad.SOURCEDOCUMENTLINE = vil.RECID
JOIN BaseInvoices bij ON vil.INVOICEID = bij.InvoiceNumber
WHERE ad.DATAAREAID = @CompanyCode
GROUP BY vil.INVOICEID, bij.VendorNumber, bij.PurchaseOrderNumber, bij.InvoiceAmount, bij.InvoiceStatus, bij.PaymentDueDate

UNION ALL

-- 4. Invoice Matched To Purchase Order
SELECT
    viit.NUM AS InvoiceNumber,
    'Invoice Matched To Purchase Order' AS ActivityName,
    viit.MODIFIEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    viit.VENDORACCOUNT AS VendorNumber,
    viit.PURCHID AS PurchaseOrderNumber,
    viit.INVOICEAMOUNT AS InvoiceAmount,
    viit.MODIFIEDBY AS User,
    'Matched' AS InvoiceStatus,
    NULL AS PaymentDueDate,
    viit.MODIFIEDDATETIME AS EndTime
FROM VendInvoiceInfoTable viit
WHERE viit.DATAAREAID = @CompanyCode AND viit.PURCHMATCHINGPOLICY > 0 AND viit.LASTMATCHINGSTATUS = 1 -- Enum for Passed
  AND viit.MODIFIEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 5. Matching Discrepancy Found
SELECT
    viit.NUM AS InvoiceNumber,
    'Matching Discrepancy Found' AS ActivityName,
    viit.MODIFIEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    viit.VENDORACCOUNT AS VendorNumber,
    viit.PURCHID AS PurchaseOrderNumber,
    viit.INVOICEAMOUNT AS InvoiceAmount,
    viit.MODIFIEDBY AS User,
    'Matching Failed' AS InvoiceStatus,
    NULL AS PaymentDueDate,
    viit.MODIFIEDDATETIME AS EndTime
FROM VendInvoiceInfoTable viit
WHERE viit.DATAAREAID = @CompanyCode AND viit.PURCHMATCHINGPOLICY > 0 AND viit.LASTMATCHINGSTATUS = 2 -- Enum for Failed
  AND viit.MODIFIEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 6. Matching Discrepancy Handled (Inferred by successful match after a failure)
-- This is an approximation. A true 'handled' event is difficult to capture without change tracking.
-- This query assumes the modification time of the 'Passed' status represents the handling time.
SELECT
    viit.NUM AS InvoiceNumber,
    'Matching Discrepancy Handled' AS ActivityName,
    viit.MODIFIEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    viit.VENDORACCOUNT AS VendorNumber,
    viit.PURCHID AS PurchaseOrderNumber,
    viit.INVOICEAMOUNT AS InvoiceAmount,
    viit.MODIFIEDBY AS User,
    'Matched' AS InvoiceStatus,
    NULL AS PaymentDueDate,
    viit.MODIFIEDDATETIME AS EndTime
FROM VendInvoiceInfoTable viit
WHERE viit.DATAAREAID = @CompanyCode AND viit.PURCHMATCHINGPOLICY > 0 AND viit.LASTMATCHINGSTATUS = 1 -- Enum for Passed
  AND viit.DISCREPANCYSTATE > 0 -- Indicates there was a discrepancy at some point
  AND viit.MODIFIEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 7, 8, 9. Workflow Events (Submitted, Approved, Rejected)
SELECT
    bij.InvoiceNumber,
    CASE wts.TRACKINGTYPE
        WHEN 1 THEN 'Invoice Sent For Approval' -- Submitted
        WHEN 5 THEN 'Invoice Approved' -- Completed/Approved
        WHEN 4 THEN 'Invoice Rejected' -- Rejection
    END AS ActivityName,
    wts.CREATEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    bij.VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    wts.USERID AS User,
    bij.InvoiceStatus,
    bij.PaymentDueDate,
    wts.CREATEDDATETIME AS EndTime
FROM WorkflowTrackingStatusTable wts
JOIN VendInvoiceInfoTable viit ON wts.CONTEXTRECID = viit.RECID
JOIN BaseInvoices bij ON viit.NUM = bij.InvoiceNumber
WHERE wts.DATAAREAID = @CompanyCode AND wts.TRACKINGTYPE IN (1, 4, 5)

UNION ALL

-- 10. Payment Block Set
SELECT
    bij.InvoiceNumber,
    'Payment Block Set' AS ActivityName,
    bij.ModifiedDateTime AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    bij.VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    bij.ModifiedByUser AS User,
    'Blocked' AS InvoiceStatus,
    bij.PaymentDueDate,
    bij.ModifiedDateTime AS EndTime
FROM BaseInvoices bij
WHERE bij.IsBlocked = 1 -- Enum for Yes

UNION ALL

-- 11. Payment Block Released (Inferred by modification of a previously blocked invoice)
SELECT
    bij.InvoiceNumber,
    'Payment Block Released' AS ActivityName,
    bij.ModifiedDateTime AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    bij.VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    bij.ModifiedByUser AS User,
    bij.InvoiceStatus,
    bij.PaymentDueDate,
    bij.ModifiedDateTime AS EndTime
FROM BaseInvoices bij
-- This logic assumes modification date reflects the unblocking action.
-- It requires a more sophisticated change log for true accuracy.
WHERE bij.IsBlocked = 0 AND bij.ModifiedDateTime > bij.CreatedDateTime

UNION ALL

-- 12. Invoice Posted
SELECT
    bij.InvoiceNumber,
    'Invoice Posted' AS ActivityName,
    bij.PostingDate AS EventTime, -- Or CreatedDateTime depending on business process
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    bij.VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    bij.CreatedByUser AS User,
    'Posted' AS InvoiceStatus,
    bij.PaymentDueDate,
    bij.PostingDate AS EndTime
FROM BaseInvoices bij

UNION ALL

-- 13. Payment Scheduled
SELECT
    ljt.INVOICE AS InvoiceNumber,
    'Payment Scheduled' AS ActivityName,
    ljt.CREATEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    bij.VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    ljt.CREATEDBY AS User,
    'Payment Scheduled' AS InvoiceStatus,
    bij.PaymentDueDate,
    ljt.CREATEDDATETIME AS EndTime
FROM LedgerJournalTrans ljt
JOIN BaseInvoices bij ON ljt.INVOICE = bij.InvoiceNumber
WHERE ljt.DATAAREAID = @CompanyCode AND ljt.ACCOUNTPLUSTYPE = 2 -- Vendor type
  AND ljt.CREATEDDATETIME BETWEEN @StartDate AND @EndDate

UNION ALL

-- 14. Payment Executed
SELECT
    vs.INVOICEID AS InvoiceNumber,
    'Payment Executed' AS ActivityName,
    vs.CREATEDDATETIME AS EventTime,
    'D365 F&O' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    vs.VENDORACCOUNT AS VendorNumber,
    bij.PurchaseOrderNumber,
    bij.InvoiceAmount,
    vs.CREATEDBY AS User,
    'Paid' AS InvoiceStatus,
    bij.PaymentDueDate,
    vs.CREATEDDATETIME AS EndTime
FROM VendSettlement vs
JOIN BaseInvoices bij ON vs.INVOICEID = bij.InvoiceNumber
WHERE vs.DATAAREAID = @CompanyCode AND vs.CREATEDDATETIME BETWEEN @StartDate AND @EndDate;

Steps

Prerequisites Setup: Ensure you have Python 3.6 or newer installed on your system. Install the necessary libraries using pip: pip install pandas requests msal. These libraries are for data manipulation, making HTTP requests to the API, and handling Microsoft identity platform authentication.
Register an Application in Azure Active Directory: To authenticate with the Dynamics 365 API, you must register an application in your organization's Azure AD tenant. Create a new App Registration, generate a client secret, and take note of the Application (client) ID, Directory (tenant) ID, and the client secret value.
Grant API Permissions: Within the newly created App Registration in Azure AD, navigate to API permissions. Add a permission for 'Dynamics ERP' or 'Dynamics 365 Business Central' (depending on your license), and grant the user_impersonation delegated permission. An administrator must grant admin consent for these permissions.
Create an Application User in Dynamics 365: In your Dynamics 365 environment, navigate to System administration > Users > Users. Create a new user and enter the Application (client) ID from the Azure App Registration into the Application ID field. Assign appropriate security roles to this user, such as 'System administrator' or a more restricted role that has read access to all required data entities.
Configure the Python Script: Copy the provided Python script into a file, for example extract_invoices.py. Open the file and update the placeholder variables at the top with your specific Dynamics 365 environment URL, tenant ID, client ID, client secret, and the company code (dataAreaId) you wish to extract data from.
Set the Date Range: Adjust the START_DATE and END_DATE variables in the script to define the time window for the data extraction. It is recommended to start with a smaller range, such as one month, to test the script and verify the results.
Execute the Script: Run the script from your command line or terminal using the command: python extract_invoices.py. The script will authenticate, query the different OData endpoints for each activity, and process the data.
Monitor the Execution: The script will print progress messages to the console as it fetches data for each activity. This can take a significant amount of time depending on the date range and data volume. Look for any error messages that might indicate issues with authentication or API access.
Review the Output File: Once the script completes successfully, it will generate a CSV file named d365_invoice_event_log.csv in the same directory. This file contains the consolidated event log.
Prepare for Upload: Open the CSV file to ensure it is correctly formatted. Verify that all required columns (InvoiceNumber, ActivityName, EventTime, SourceSystem, LastDataUpdate) are present and populated. The file is now ready to be uploaded to ProcessMind.

Configuration

Authentication: The script uses OAuth 2.0 client credentials flow. You must configure an Azure AD App Registration and grant it API permissions to Dynamics 365. An application user with appropriate security roles must be created in Dynamics 365 itself.
API Endpoint: The base URL for the Dynamics 365 Finance and Operations OData API. The format is typically https://[your-d365-environment-name].operations.dynamics.com.
Date Range: The script includes START_DATE and END_DATE variables. These are used to filter records based on their creation or modification dates to manage the data volume. For initial extractions, a period of 3 to 6 months is recommended.
Company Filter: Data must be filtered by a specific legal entity or company. This is done by specifying the dataAreaId in the API query filter, which is a mandatory configuration in the script.
Key Data Entities: The script queries several standard Dynamics 365 OData entities, including VendorInvoiceRegisterJournalsV2, VendorInvoiceHeaders, VendorPostedInvoices, VendorPaymentJournalLines, and VendSettlements. Custom entities may be required if the standard process is heavily modified.
Pagination: The script automatically handles paginated responses from the API using the @odata.nextLink property to ensure all records for the specified period are retrieved.

a Sample Query python

import os
import pandas as pd
import requests
import msal
import datetime
import time

class=class="hl-string">"hl-comment"># --- Configuration: Update these values --- #
D365_URL = class="hl-string">"https://[your-d365-environment].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]"
COMPANY_ID = class="hl-string">"[your-company-id]" class=class="hl-string">"hl-comment"># e.g., class="hl-string">'USMF'
START_DATE = class="hl-string">"2023-01-01T00:00:00Z"
END_DATE = class="hl-string">"2023-12-31T23:59:59Z"

class=class="hl-string">"hl-comment"># --- End Configuration --- #

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

LAST_DATA_UPDATE = datetime.datetime.now(datetime.timezone.utc).isoformat()
SOURCE_SYSTEM = class="hl-string">"Microsoft Dynamics 365"

def get_access_token():
    class="hl-string">"""Acquires an access token from Azure AD."""
    app = msal.ConfidentialClientApplication(
        CLIENT_ID,
        authority=AUTHORITY,
        client_credential=CLIENT_SECRET
    )
    result = app.acquire_token_for_client(scopes=[fclass="hl-string">"{RESOURCE}/.default"])
    if class="hl-string">"access_token" in result:
        return result[class="hl-string">'access_token']
    else:
        print(class="hl-string">"Error acquiring token:", result.get(class="hl-string">"error_description"))
        return None

def fetch_all_data(access_token, entity_url):
    class="hl-string">"""Fetches all pages of data from a given D365 OData entity URL."""
    all_data = []
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json',
        class="hl-string">'OData-MaxVersion': class="hl-string">'4.0',
        class="hl-string">'OData-Version': class="hl-string">'4.0',
        class="hl-string">'Prefer': class="hl-string">'odata.include-annotations="*"'
    }
    
    next_link = entity_url
    while next_link:
        try:
            response = requests.get(next_link, headers=headers)
            response.raise_for_status()
            data = response.json()
            all_data.extend(data[class="hl-string">'value'])
            next_link = data.get(class="hl-string">'@odata.nextLink')
            time.sleep(0.1) class=class="hl-string">"hl-comment"># Small delay to respect API limits
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"An error occurred: {e}")
            print(fclass="hl-string">"Response body: {response.text}")
            next_link = None
    return all_data

def main():
    token = get_access_token()
    if not token:
        return

    all_events = []
    cross_company_filter = fclass="hl-string">"cross-company=true&$filter=dataAreaId eq '{COMPANY_ID}'"

    print(class="hl-string">"Fetching 'Invoice Registered' events...")
    entity_url = fclass="hl-string">"{API_BASE_URL}/VendorInvoiceRegisterJournalsV2?{cross_company_filter} and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}&$select=JournalNumber,CreatedDateTime,InvoiceNumber,VendorAccount,CreatedBy,JournalTotalCredit"
    data = fetch_all_data(token, entity_url)
    for item in data:
        all_events.append({
            class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'),
            class="hl-string">'ActivityName': class="hl-string">'Invoice Registered',
            class="hl-string">'EventTime': item.get(class="hl-string">'CreatedDateTime'),
            class="hl-string">'User': item.get(class="hl-string">'CreatedBy'),
            class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'),
            class="hl-string">'InvoiceAmount': item.get(class="hl-string">'JournalTotalCredit'),
            class="hl-string">'PurchaseOrderNumber': None, class="hl-string">'InvoiceStatus': None, class="hl-string">'PaymentDueDate': None
        })

    print(class="hl-string">"Fetching invoice header events...")
    entity_url = fclass="hl-string">"{API_BASE_URL}/VendorInvoiceHeaders?{cross_company_filter} and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}&$select=InvoiceNumber,CreatedDateTime,ModifiedDateTime,VendorAccount,PurchaseOrderNumber,InvoiceAmount,InvoiceMatchingStatus,LastMatchingValidationDate,InvoiceLastActionDate,Worker,InvoiceLastAction,DueDate,VendorInvoiceHeaderStatus"
    data = fetch_all_data(token, entity_url)
    for item in data:
        class=class="hl-string">"hl-comment"># Invoice Data Captured (approximated by creation of the header)
        all_events.append({
            class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Invoice Data Captured', class="hl-string">'EventTime': item.get(class="hl-string">'CreatedDateTime'), class="hl-string">'User': item.get(class="hl-string">'Worker'),
            class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
            class="hl-string">'InvoiceStatus': item.get(class="hl-string">'VendorInvoiceHeaderStatus'), class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
        })
        class=class="hl-string">"hl-comment"># Invoice Coded (inferred by modification after creation)
        if item.get(class="hl-string">'ModifiedDateTime') != item.get(class="hl-string">'CreatedDateTime'):
            all_events.append({
                class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Invoice Coded', class="hl-string">'EventTime': item.get(class="hl-string">'ModifiedDateTime'), class="hl-string">'User': item.get(class="hl-string">'Worker'),
                class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
                class="hl-string">'InvoiceStatus': item.get(class="hl-string">'VendorInvoiceHeaderStatus'), class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
            })
        class=class="hl-string">"hl-comment"># Matching activities
        if item.get(class="hl-string">'InvoiceMatchingStatus') == class="hl-string">'Passed' and item.get(class="hl-string">'LastMatchingValidationDate'):
            all_events.append({
                class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Invoice Matched To Purchase Order', class="hl-string">'EventTime': item.get(class="hl-string">'LastMatchingValidationDate'), class="hl-string">'User': item.get(class="hl-string">'Worker'),
                class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
                class="hl-string">'InvoiceStatus': item.get(class="hl-string">'VendorInvoiceHeaderStatus'), class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
            })
        if item.get(class="hl-string">'InvoiceMatchingStatus') == class="hl-string">'Failed' and item.get(class="hl-string">'LastMatchingValidationDate'):
            all_events.append({
                class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Matching Discrepancy Found', class="hl-string">'EventTime': item.get(class="hl-string">'LastMatchingValidationDate'), class="hl-string">'User': item.get(class="hl-string">'Worker'),
                class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
                class="hl-string">'InvoiceStatus': item.get(class="hl-string">'VendorInvoiceHeaderStatus'), class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
            })
            class=class="hl-string">"hl-comment"># Matching Discrepancy Handled (inferred if invoice is now matched or posted after a discrepancy)
            if item.get(class="hl-string">'VendorInvoiceHeaderStatus') in [class="hl-string">'Matched', class="hl-string">'Posted'] and item.get(class="hl-string">'ModifiedDateTime') > item.get(class="hl-string">'LastMatchingValidationDate'):
                all_events.append({
                    class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Matching Discrepancy Handled', class="hl-string">'EventTime': item.get(class="hl-string">'ModifiedDateTime'), class="hl-string">'User': item.get(class="hl-string">'Worker'),
                    class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
                    class="hl-string">'InvoiceStatus': item.get(class="hl-string">'VendorInvoiceHeaderStatus'), class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
                })

    print(class="hl-string">"Fetching workflow events...")
    class=class="hl-string">"hl-comment"># This is a simplified workflow extraction. A real scenario might need to query WorkflowTrackingWorkItem and join.
    entity_url = fclass="hl-string">"{API_BASE_URL}/WorkflowTrackingStatusTables?{cross_company_filter} and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}&$filter=contains(Context,'VendInvoice')&$select=ContextRecId,CreatedDateTime,TrackingStatus,User,Context"
    class=class="hl-string">"hl-comment"># Note: Linking ContextRecId back to an InvoiceNumber requires another lookup, which is complex. 
    class=class="hl-string">"hl-comment"># A placeholder is used here. For a real implementation, you must resolve this link.
    class=class="hl-string">"hl-comment"># For this script, we will not generate workflow events to avoid complexity.

    print(class="hl-string">"Fetching posted invoice events...")
    entity_url = fclass="hl-string">"{API_BASE_URL}/VendorPostedInvoices?{cross_company_filter} and PostedDateTime ge {START_DATE} and PostedDateTime le {END_DATE}&$select=InvoiceNumber,PostedDateTime,VendorAccount,PurchaseOrderNumber,InvoiceAmount,DueDate,Block,ModifiedDateTime,ModifiedBy"
    data = fetch_all_data(token, entity_url)
    for item in data:
        all_events.append({
            class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Invoice Posted', class="hl-string">'EventTime': item.get(class="hl-string">'PostedDateTime'), class="hl-string">'User': item.get(class="hl-string">'ModifiedBy'),
            class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
            class="hl-string">'InvoiceStatus': class="hl-string">'Posted', class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
        })
        if item.get(class="hl-string">'Block') not in [None, class="hl-string">'No', class="hl-string">'Free']:
            all_events.append({
                class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Payment Block Set', class="hl-string">'EventTime': item.get(class="hl-string">'ModifiedDateTime'), class="hl-string">'User': item.get(class="hl-string">'ModifiedBy'),
                class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccount'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'InvoiceAmount'), class="hl-string">'PurchaseOrderNumber': item.get(class="hl-string">'PurchaseOrderNumber'),
                class="hl-string">'InvoiceStatus': class="hl-string">'Posted (Blocked)', class="hl-string">'PaymentDueDate': item.get(class="hl-string">'DueDate')
            })
        class=class="hl-string">"hl-comment"># Payment Block Released is difficult to capture without audit logs. This is an approximation.

    print(class="hl-string">"Fetching payment schedule events...")
    entity_url = fclass="hl-string">"{API_BASE_URL}/VendorPaymentJournalLines?{cross_company_filter} and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}&$select=InvoiceNumber,CreatedDateTime,CreatedBy,JournalBatchNumber,VendorAccountNumber,Amount"
    data = fetch_all_data(token, entity_url)
    for item in data:
        all_events.append({
            class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Payment Scheduled', class="hl-string">'EventTime': item.get(class="hl-string">'CreatedDateTime'), class="hl-string">'User': item.get(class="hl-string">'CreatedBy'),
            class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccountNumber'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'Amount'), class="hl-string">'PurchaseOrderNumber': None,
            class="hl-string">'InvoiceStatus': class="hl-string">'Payment Scheduled', class="hl-string">'PaymentDueDate': None
        })

    print(class="hl-string">"Fetching payment execution events...")
    entity_url = fclass="hl-string">"{API_BASE_URL}/VendSettlements?{cross_company_filter} and SettlementDate ge {START_DATE} and SettlementDate le {END_DATE}&$select=InvoiceNumber,SettlementDate,VendorAccountNumber,SettlementAmount"
    data = fetch_all_data(token, entity_url)
    for item in data:
        all_events.append({
            class="hl-string">'InvoiceNumber': item.get(class="hl-string">'InvoiceNumber'), class="hl-string">'ActivityName': class="hl-string">'Payment Executed', class="hl-string">'EventTime': item.get(class="hl-string">'SettlementDate'), class="hl-string">'User': None, class=class="hl-string">"hl-comment"># User not available in this entity
            class="hl-string">'VendorNumber': item.get(class="hl-string">'VendorAccountNumber'), class="hl-string">'InvoiceAmount': item.get(class="hl-string">'SettlementAmount'), class="hl-string">'PurchaseOrderNumber': None,
            class="hl-string">'InvoiceStatus': class="hl-string">'Paid', class="hl-string">'PaymentDueDate': None
        })

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

    print(class="hl-string">"Processing and saving data...")
    df = pd.DataFrame(all_events)
    df[class="hl-string">'SourceSystem'] = SOURCE_SYSTEM
    df[class="hl-string">'LastDataUpdate'] = LAST_DATA_UPDATE
    df[class="hl-string">'EndTime'] = df[class="hl-string">'EventTime']
    df.dropna(subset=[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime'], inplace=True)
    df.sort_values(by=[class="hl-string">'InvoiceNumber', class="hl-string">'EventTime'], inplace=True)

    output_filename = class="hl-string">'d365_invoice_event_log.csv'
    df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%SZ')
    print(fclass="hl-string">"Event log successfully created: {output_filename}")

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

Steps

Navigate to the Data Management Workspace: Log in to Microsoft Dynamics 365 Finance. Use the search bar to find and navigate to the Data management workspace.
Create a New Export Project: Within the workspace, select the Export tile to create a new data export project. Provide a meaningful name for the project, for example, ProcessMind_Invoice_Processing_Export.
Define the Data Project: In the General tab for the new project, specify the Data format. CSV is a commonly used and recommended format. Set other parameters as needed.
Add Required Data Entities: Go to the Selected entities section and click Add entity. Search for and add the following core entities one by one. You will need to specify the target data format for each.
- VendInvoiceInfoTableEntity (Vendor invoice header)
- VendInvoiceTransEntity (Vendor invoice lines)
- VendInvoiceRegisterHeaderEntity (Invoice register journal header)
- WorkflowTrackingStatusTableEntity (Workflow history)
- AccountingDistributionEntity (GL distributions)
- VendInvoiceJour (Posted vendor invoices)
- VendTrans (Vendor transactions, for payment blocks)
- LedgerJournalTransVendPaymEntity (Vendor payment journal lines)
- VendSettlement (Vendor settlement transactions)
Apply Filters: For each entity, it is critical to apply filters to limit the data scope. Select an entity and click Filter. At a minimum, filter by Company (Legal entity) and a specific date range on a relevant date field like CREATEDDATETIME or INVOICEDATE. A typical range for initial analysis is the last 3 to 6 months.
Configure Entity Mappings: For each entity, review the source-to-target mapping. Ensure all required fields for the final event log are included in the export. No complex mapping changes are typically needed at this stage.
Run the Export Job: Once all entities are added and filtered, click Export on the Action Pane to start the job. You can run it immediately or schedule it for off-peak hours to minimize system load.
Download the Exported Data: After the export job completes successfully, navigate to the job history. From there, you can download the exported data package, which will contain separate files for each data entity.
Transform Data into an Event Log: The exported files are entity snapshots, not a consolidated event log. You must use an external tool like a database, Python script, or Power Query to process and combine these files. The conceptual query in the query section provides the complete logic for this transformation.
Execute the Transformation Logic: Load the exported CSV files into your chosen transformation environment. Execute the provided transformation query, which unnions data from all source files to create a single event log table with one row per business activity.
Finalize and Upload: Export the final transformed data into a single CSV file. Ensure the column headers match the requirements, for example, InvoiceNumber, ActivityName, EventTime. This file is now ready to be uploaded to ProcessMind.

Configuration

Data Entities: The core of the configuration is selecting the correct set of entities to cover the invoice lifecycle. Key entities include VendInvoiceInfoTableEntity, VendInvoiceJour, and WorkflowTrackingStatusTableEntity.
Export Data Format: Select CSV as the target data format for easy processing in subsequent transformation steps.
Filtering by Company: It is crucial to filter every data entity by the specific legal entity (Company) you are analyzing to avoid pulling irrelevant data.
Date Range Filtering: Apply a consistent date range filter across all relevant entities, such as VendInvoiceInfoTableEntity and VendInvoiceJour, based on creation or posting dates. Start with a recent period, for example, the last 6 months, to manage data volume.
Full push vs. Incremental: For the initial extraction, perform a full push. For subsequent refreshes, you can configure an incremental export to fetch only the data that has changed since the last run, reducing processing time.
Prerequisites: The user running the export must have appropriate security roles in Dynamics 365, typically including Data management administrator or a similar role with permissions to access the required data entities.

a Sample Query config

/*
-- This is a conceptual query to be executed in a database or data transformation tool.
-- The tables referenced (e.g., 'VendInvoiceInfoTableEntity') are the CSV files exported from Dynamics 365.
-- Replace '[Your_Source_System_Name]' with the actual source system identifier.
*/

-- 1. Invoice Registered
SELECT
    h.INVOICENUMBER AS "InvoiceNumber",
    'Invoice Registered' AS "ActivityName",
    h.CREATEDDATETIME AS "EventTime",
    h.CREATEDBY AS "User",
    h.INVOICEACCOUNT AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    h.INVOICEAMOUNT AS "InvoiceAmount",
    NULL AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    h.CREATEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceRegisterHeaderEntity h

UNION ALL

-- 2. Invoice Data Captured
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Invoice Data Captured' AS "ActivityName",
    i.CREATEDDATETIME AS "EventTime",
    i.CREATEDBY AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Pending' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    i.CREATEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i

UNION ALL

-- 3. Invoice Coded
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Invoice Coded' AS "ActivityName",
    MIN(a.CREATEDDATETIME) AS "EventTime",
    MIN(a.CREATEDBY) AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Pending' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    MIN(a.CREATEDDATETIME) AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i
JOIN AccountingDistributionEntity a ON i.INVOICENUMBER = a.SOURCEDOCUMENTLINE_INVOICENUMBER
GROUP BY i.INVOICENUMBER, i.VENDORACCOUNT, i.PURCHASEORDERNUMBER, i.INVOICEAMOUNT

UNION ALL

-- 4. Invoice Matched To Purchase Order
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Invoice Matched To Purchase Order' AS "ActivityName",
    i.MODIFIEDDATETIME AS "EventTime",
    i.MODIFIEDBY AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Matched' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    i.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i
WHERE i.LASTMATCHSTATUS = 'Passed'

UNION ALL

-- 5. Matching Discrepancy Found
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Matching Discrepancy Found' AS "ActivityName",
    i.MODIFIEDDATETIME AS "EventTime",
    i.MODIFIEDBY AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Discrepancy' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    i.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i
WHERE i.LASTMATCHSTATUS = 'Failed'

UNION ALL

-- 6. Matching Discrepancy Handled (Approximation)
SELECT
    i.INVOICENUMBER AS "InvoiceNumber",
    'Matching Discrepancy Handled' AS "ActivityName",
    i.MODIFIEDDATETIME AS "EventTime",
    i.MODIFIEDBY AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    'Matched' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    i.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceInfoTableEntity i
WHERE i.LASTMATCHSTATUS = 'Passed' AND i.MODIFIEDDATETIME > i.CREATEDDATETIME

UNION ALL

-- 7. Invoice Sent For Approval, 8. Invoice Approved, 9. Invoice Rejected
SELECT
    w.CONTEXT_INVOICENUMBER AS "InvoiceNumber",
    CASE w.TRACKINGSTATUS
        WHEN 'Submitted' THEN 'Invoice Sent For Approval'
        WHEN 'Completed' THEN 'Invoice Approved'
        WHEN 'Canceled' THEN 'Invoice Rejected'
        ELSE CONCAT('Workflow: ', w.TRACKINGSTATUS)
    END AS "ActivityName",
    w.CREATEDDATETIME AS "EventTime",
    w.USERID AS "User",
    i.VENDORACCOUNT AS "VendorNumber",
    i.PURCHASEORDERNUMBER AS "PurchaseOrderNumber",
    i.INVOICEAMOUNT AS "InvoiceAmount",
    w.TRACKINGSTATUS AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    w.CREATEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM WorkflowTrackingStatusTableEntity w
JOIN VendInvoiceInfoTableEntity i ON w.CONTEXT_RECID = i.RECID AND w.CONTEXT_TABLEID = i.TABLEID
WHERE w.TRACKINGTYPE = 'Approval'

UNION ALL

-- 10. Payment Block Set
SELECT
    v.INVOICENUMBER AS "InvoiceNumber",
    'Payment Block Set' AS "ActivityName",
    v.MODIFIEDDATETIME AS "EventTime",
    v.MODIFIEDBY AS "User",
    v.ACCOUNTNUM AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    v.AMOUNT AS "InvoiceAmount",
    'Blocked' AS "InvoiceStatus",
    v.DUEDATE AS "PaymentDueDate",
    v.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendTrans v
WHERE v.BLOCKED = 'Yes'

UNION ALL

-- 11. Payment Block Released
SELECT
    v.INVOICENUMBER AS "InvoiceNumber",
    'Payment Block Released' AS "ActivityName",
    v.MODIFIEDDATETIME AS "EventTime",
    v.MODIFIEDBY AS "User",
    v.ACCOUNTNUM AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    v.AMOUNT AS "InvoiceAmount",
    'Open' AS "InvoiceStatus",
    v.DUEDATE AS "PaymentDueDate",
    v.MODIFIEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendTrans v
WHERE v.BLOCKED = 'No' AND v.MODIFIEDDATETIME > v.CREATEDDATETIME

UNION ALL

-- 12. Invoice Posted
SELECT
    vj.INVOICENUM AS "InvoiceNumber",
    'Invoice Posted' AS "ActivityName",
    vj.POSTINGDATE AS "EventTime",
    vj.CREATEDBY AS "User",
    vj.INVOICEACCOUNT AS "VendorNumber",
    vj.PURCHID AS "PurchaseOrderNumber",
    vj.INVOICEAMOUNT AS "InvoiceAmount",
    'Posted' AS "InvoiceStatus",
    vj.DUEDATE AS "PaymentDueDate",
    vj.POSTINGDATE AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendInvoiceJour vj

UNION ALL

-- 13. Payment Scheduled
SELECT
    l.INVOICENUMBER AS "InvoiceNumber",
    'Payment Scheduled' AS "ActivityName",
    l.CREATEDDATETIME AS "EventTime",
    l.CREATEDBY AS "User",
    l.ACCOUNTDISPLAYVALUE AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    l.DEBITAMOUNT + l.CREDITAMOUNT AS "InvoiceAmount",
    'Scheduled for Payment' AS "InvoiceStatus",
    l.DUEDATE AS "PaymentDueDate",
    l.CREATEDDATETIME AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM LedgerJournalTransVendPaymEntity l

UNION ALL

-- 14. Payment Executed
SELECT
    vs.INVOICENUMBER AS "InvoiceNumber",
    'Payment Executed' AS "ActivityName",
    vs.TRANSDATE AS "EventTime",
    vs.CREATEDBY AS "User",
    vs.ACCOUNTNUM AS "VendorNumber",
    NULL AS "PurchaseOrderNumber",
    vs.SETTLEMENTAMOUNT AS "InvoiceAmount",
    'Paid' AS "InvoiceStatus",
    NULL AS "PaymentDueDate",
    vs.TRANSDATE AS "EndTime",
    '[Your_Source_System_Name]' AS "SourceSystem",
    GETUTCDATE() AS "LastDataUpdate"
FROM VendSettlement vs;

Your Purchase to Pay - Invoice Processing Data Template

Your Purchase to Pay - Invoice Processing Data Template

Purchase to Pay - Invoice Processing Attributes

Purchase to Pay - Invoice Processing Activities

Extraction Guides

Dynamics 365 Data Management Framework Export

Steps

Configuration

a Sample Query config

Direct SQL Query via Bring Your Own Database (BYOD)

Steps

Configuration

a Sample Query sql

Python Script using Dynamics 365 OData REST API

Steps

Configuration

a Sample Query python

Microsoft Power Platform Dataflow Extraction

Steps

Configuration

a Sample Query config

Ready to Get Started?

Start Optimizing Your Purchase to Pay - Invoice Processing Today

Your Purchase to Pay - Invoice Processing Data Template

Your Purchase to Pay - Invoice Processing Data Template

Purchase to Pay - Invoice Processing Attributes

Purchase to Pay - Invoice Processing Activities

Extraction Guides

Dynamics 365 Data Management Framework Export

Steps

Configuration

a Sample Query config

Direct SQL Query via Bring Your Own Database (BYOD)

Steps

Configuration

a Sample Query sql

Python Script using Dynamics 365 OData REST API

Steps

Configuration

a Sample Query python

Microsoft Power Platform Dataflow Extraction

Steps

Configuration

a Sample Query config

We value your privacy

Ready to Get Started?

Start Optimizing Your Purchase to Pay - Invoice Processing Today