> Continuous Process Improvement > Accounts Payable Invoice Processing > Microsoft Dynamics 365 > Data Template

Data Template: Accounts Payable Invoice Processing

Microsoft Dynamics 365

Accounts Payable Invoice Processing Attributes (22) Accounts Payable Invoice Processing Activities (14) Extraction Guides (4)

Your Accounts Payable Invoice Processing Data Template

This template is designed to guide you through collecting the essential data required to analyze and optimize your Accounts Payable Invoice Processing. It outlines the crucial attributes, the key activities, and provides practical guidance for extracting this information. Using this template will ensure you capture a comprehensive event log, enabling deep insights into your process performance.

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

Accounts Payable Invoice Processing Attributes

These are the recommended data fields to include in your event log for comprehensive analysis of your accounts payable invoice processing, allowing for in-depth insights.

3 Required 6 Recommended 13 Optional

	Name	Description
	Invoice Invoice	The unique identifier for each vendor invoice document.
Description The Invoice serves as the primary case identifier, linking all activities from the moment an invoice is received until its final payment. This allows for an end-to-end analysis of each individual invoice's journey through the Accounts Payable process. In analysis, grouping events by this identifier is the first step to reconstruct the process flow for each invoice. It enables the calculation of case-level KPIs like total cycle time and helps in identifying variants and bottlenecks specific to individual invoice journeys. Why it matters This is the essential key to trace the entire lifecycle of an invoice, making it the foundation for all process mining analysis in Accounts Payable. Where to get This is typically the Vendor Invoice Number from the 'Vendor invoice' page or related data entities in Dynamics 365 Finance. Examples INV-00125475000921DE-8832-2023
	Activity ActivityName	The name of the business process step that was performed.
Description This attribute records the specific action or event that occurred at a point in time for an invoice, such as 'Invoice Registered' or 'Invoice Approved'. These activities form the nodes in the discovered process map. Analyzing the sequence and frequency of activities is fundamental to process mining. It helps visualize the process flow, identify common and rare paths (variants), and pinpoint activities that cause delays or rework. Why it matters Activities define the 'what' in the process, allowing for the construction of a process map and the analysis of process flow and variations. Where to get This is often derived from status changes, workflow history logs, or document posting records within the Dynamics 365 AP module. Examples Invoice RegisteredInvoice ApprovedDiscrepancy ResolvedPayment Executed
	Start Time EventTime	The timestamp indicating when an activity or event occurred.
Description This attribute provides the date and time for each activity, which is essential for ordering events chronologically and calculating durations. It forms the time-based backbone of the event log. In analysis, the Start Time is used to calculate all time-related metrics, including cycle times between activities, waiting times, and overall case duration. It is critical for identifying bottlenecks and measuring process performance against SLAs. Why it matters This timestamp is crucial for sequencing events correctly and calculating all performance metrics, such as cycle times and bottlenecks. Where to get Retrieved from creation or modification date fields on transaction records, workflow history logs, or posting date fields in Dynamics 365. Examples 2023-04-15T09:00:12Z2023-04-18T14:35:00Z2023-05-02T11:21:45Z
	Invoice Amount InvoiceAmount	The total monetary value of the invoice.
Description Represents the total amount due on the vendor invoice. This is a critical financial metric for each case. This attribute allows for financial analysis of the AP process. It can be used to prioritize high-value invoices, analyze approval times based on amount thresholds, and understand the financial impact of payment delays or early payment discounts. Why it matters Provides financial context, enabling analysis of process behavior based on monetary value, such as identifying if high-value invoices are processed differently. Where to get Located in the vendor invoice header, often in fields like 'InvoiceAmount' in 'VendInvoiceInfoTable'. Examples 1500.75250.0012345.50
	Invoice Due Date InvoiceDueDate	The date by which the invoice payment is due, as calculated from the payment terms.
Description This date indicates the deadline for paying the invoice to avoid penalties and comply with vendor agreements. It is usually calculated based on the invoice date and the specified payment terms. This attribute is essential for the 'Payment Terms Compliance' analysis. By comparing the 'Payment Executed' date against the 'Invoice Due Date', the analysis can automatically flag late payments, calculate compliance rates, and help prioritize payments to maintain good vendor relationships. Why it matters Crucial for measuring on-time payment performance, managing supplier relationships, and avoiding late payment fees. Where to get Calculated field based on invoice date and payment terms. Stored in fields like 'DueDate' in vendor transaction tables (e.g., 'VendTrans'). Examples 2023-05-152023-06-302023-07-01
	Payment Terms PaymentTerms	The agreed-upon terms for invoice payment with the vendor.
Description This attribute defines the conditions under which a vendor invoice should be paid, such as 'Net 30' (due in 30 days) or '2/10 Net 30' (a 2% discount if paid in 10 days, otherwise due in 30 days). Analyzing by payment terms helps understand how different agreements affect payment timeliness and cash flow. It is the basis for identifying early payment discount opportunities and segmenting compliance analysis to see if certain terms are harder to meet. Why it matters Defines payment deadlines and discount opportunities, directly impacting cash flow management and cost savings. Where to get Found on the vendor master data or specified on the purchase order/invoice header. Stored in fields like 'PaymTermId'. Examples Net 30Net 602/10 Net 30
	Purchase Order Number PurchaseOrderNumber	The unique identifier for the purchase order associated with the invoice.
Description This attribute links the invoice to its corresponding purchase order (PO). Invoices can be PO-backed or non-PO. Analyzing this attribute helps differentiate between PO-based and non-PO-based invoice processing, which often follow different workflows. It's essential for measuring the efficiency of PO matching and identifying issues that arise when invoice details do not align with the purchase order. Why it matters Distinguishes between PO and non-PO invoices, which follow different processes, and is essential for analyzing matching efficiency. Where to get Found on the vendor invoice header or lines, typically in a field named 'PurchId' on 'VendInvoiceInfoTable'. Examples PO-000432PO-000511
	User UserName	The user who executed the activity.
Description Identifies the specific user responsible for completing a process step, such as registering an invoice or approving it. This is often linked to the user's system ID in Dynamics 365. Analyzing performance by user helps identify training needs, high-performers, and workload distribution. It is essential for dashboards that focus on approval times and resource efficiency, allowing managers to see which users or teams are bottlenecks. Why it matters Attributes work to specific individuals, enabling analysis of workload, performance, and identification of training opportunities. Where to get Typically found in 'Created by' or 'Modified by' fields on transactions, or from the workflow history logs in Dynamics 365. Examples j.doea.smithr.williams
	Vendor VendorName	The name of the supplier or vendor who submitted the invoice.
Description This attribute identifies the vendor associated with the invoice. Vendor data often includes details like name, ID, and category. Analyzing the AP process by vendor can reveal which suppliers consistently submit problematic invoices (e.g., those with frequent discrepancies), which ones have special payment terms, and which are associated with long processing cycles. This insight can be used to improve supplier relationships and collaboration. Why it matters Allows for segmenting the process to identify vendor-specific issues, such as frequent discrepancies or payment delays. Where to get Linked from the vendor invoice header, typically from the 'VendTable' data entity based on the vendor account number. Examples Contoso LtdFabrikam IncNorthwind Traders
	Approver ApproverName	The user who approved or rejected the invoice at a specific step.
Description This attribute identifies the individual responsible for an approval decision in the workflow. For invoices with multi-level approvals, there may be different approvers for different steps. It is essential for the 'Invoice Approval Cycle Time Analysis' dashboard. The analysis can be broken down by approver to identify individuals who may be bottlenecks in the process, either due to workload or other factors. This allows for targeted interventions to speed up the approval cycle. Why it matters Enables detailed analysis of the approval process, helping to identify bottlenecks at the individual or team level. Where to get Extracted from the workflow history logs ('WorkflowTrackingStatusTable'), which record the user who completed each approval step. Examples David ChenMaria GarciaAP_Manager_Group
	Company Code CompanyCode	The identifier for the legal entity processing the invoice.
Description This attribute identifies the specific company or legal entity within the organization that is responsible for the invoice. In multi-company setups, this is a critical organizational dimension. Analyzing the AP process by Company Code allows for performance comparison between different business units or legal entities. It helps identify if certain entities are less efficient, have higher rework rates, or follow different process variants, highlighting opportunities for standardization and best-practice sharing. Why it matters Enables performance benchmarking and process comparison across different legal entities or business units within the organization. Where to get A standard field on almost all transactional tables in Dynamics 365, typically named 'DataAreaId'. Examples USMFDEMFGBSI
	Discount Due Date EarlyPaymentDiscountDate	The deadline to pay the invoice to be eligible for an early payment discount.
Description This attribute specifies the last day an invoice can be paid to receive the vendor's offered discount. It is derived from the invoice date and the discount portion of the payment terms (e.g., the '10' in '2/10 Net 30'). This date is the primary driver for the 'Early Payment Discount Status' analysis. By comparing the 'Payment Scheduled' or 'Payment Executed' date to this deadline, the system can determine whether a discount was successfully captured, providing clear metrics on financial optimization. Why it matters Defines the target date for capturing cost savings, making it a key driver for payment prioritization and efficiency improvements. Where to get Calculated by the system based on payment terms and invoice date. Stored in fields like 'CashDiscDate' in 'VendTrans'. Examples 2023-04-252023-05-102023-06-15
	Discrepancy Reason DiscrepancyReason	The reason code or description for an invoice discrepancy.
Description When an invoice is put on hold or a discrepancy is identified, this attribute captures the reason, such as 'Price Mismatch', 'Quantity Difference', or 'Missing Goods Receipt'. This attribute is key to the 'Discrepancy Resolution & Rework' analysis. By categorizing rework based on the reason, the analysis can identify the root causes of process inefficiencies. For example, if 'Price Mismatch' is the most common reason, it points to a need for better data alignment between purchasing and vendors. Why it matters Provides the root cause for rework, enabling targeted process improvements to reduce exceptions and manual intervention. Where to get May be stored in invoice hold tables, workflow comments, or specific discrepancy logging fields within the AP module. Examples Price MismatchQuantity DifferenceInvalid PO
	Early Pmt Discount Amount EarlyPaymentDiscountAmount	The potential discount amount if the invoice is paid early.
Description This attribute shows the monetary value of the discount offered by the vendor for early payment, as defined by the payment terms. This financial data is critical for the 'Early Payment Discount Status' dashboard. It allows the business to quantify the value of discounts captured versus those missed, providing a clear financial incentive for improving the efficiency of the invoice approval and payment scheduling processes. Why it matters Quantifies the financial opportunity of process efficiency, providing a direct link between process performance and cost savings. Where to get Calculated field based on invoice amount and payment terms. This value is available in tables like 'VendTrans' or related cash discount fields. Examples 30.015.00246.91
	Goods Receipt Number GoodsReceiptNumber	The identifier for the goods receipt document related to the invoice.
Description This attribute links the invoice to the record of received goods or services, which is necessary for three-way matching (PO, Goods Receipt, and Invoice). This is essential for analyzing the matching efficiency of the process. Delays or failures in 'Goods Receipt Matched' activities can be investigated using this identifier to trace back to the source receipt document, helping to identify issues in the procurement or receiving steps that impact accounts payable. Why it matters Facilitates analysis of three-way matching efficiency and helps pinpoint issues originating in the goods receiving process. Where to get Linked via the purchase order lines, often found in packing slip journals ('VendPackingSlipJour') or related tables. Examples GRN-00981GRN-01024
	Invoice Processing Time InvoiceProcessingTime	The total cycle time for an invoice from receipt to payment.
Description This metric represents the total elapsed time for an invoice case, typically calculated from the timestamp of the first activity (e.g., 'Invoice Registered') to the timestamp of a terminal activity (e.g., 'Payment Executed'). This is a primary KPI ('Average Invoice Cycle Time') for measuring overall process efficiency. Analyzing this duration helps to identify systemic delays and provides a high-level benchmark for process improvement initiatives. It can be sliced by dimensions like vendor or company code to find areas with the longest cycle times. Why it matters Directly measures end-to-end process efficiency and serves as a key performance indicator for monitoring overall AP health. Where to get This is not a source system field. It is calculated in the process mining tool by taking the difference between the timestamps of the first and last events of a case. Examples 15 days 4 hours3 days 11 hours32 days 1 hour
	Invoice Status InvoiceStatus	The current processing status of the invoice.
Description This attribute reflects the last known state of an invoice in the process, such as 'In Progress', 'Approved', 'Paid', or 'Cancelled'. It provides a snapshot of the invoice's position in its lifecycle. While process mining derives the flow from activities, having the final status is useful for validation and for creating business-level dashboards that summarize the current state of all open invoices. It can be used to filter for all invoices that are currently 'Approved' but not yet 'Paid'. Why it matters Provides a high-level summary of the invoice's current state, useful for filtering and creating status-based dashboards. Where to get Often derived from document status or workflow status fields within the AP module. Examples In ProgressApprovedPaidCancelled
	Is Automated IsAutomated	A flag indicating if the activity was performed automatically by the system.
Description This boolean attribute distinguishes between activities performed by human users and those executed by system automation, such as automated invoice posting or matching. Analyzing this attribute is key to measuring the level of automation in the AP process. It helps calculate the Straight-Through Processing (STP) rate and identify which manual activities are prime candidates for future automation initiatives, ultimately reducing costs and processing time. Why it matters Helps measure the straight-through processing rate and identify opportunities for increasing automation and efficiency. Where to get Derived by checking if the user associated with an activity is a system or batch user (e.g., 'Admin', 'BatchUser'). Examples truefalse
	Is Late Payment IsLatePayment	A calculated flag indicating if the payment was made after the due date.
Description This boolean attribute is set to true if the 'Payment Executed' activity occurs after the 'Invoice Due Date'. It provides a clear, case-level indicator of non-compliance with payment terms. This attribute simplifies the calculation of the 'Payment Terms Compliance Rate' KPI. It allows for quick filtering and dashboarding to show the volume and value of late payments, and enables root cause analysis on these specific cases to understand why they were delayed. Why it matters Provides a clear and simple flag for analyzing non-compliant payments and calculating on-time payment KPIs. Where to get This is not a source system field. It is calculated in the process mining tool by comparing the timestamp of the 'Payment Executed' activity to the 'InvoiceDueDate' attribute. Examples truefalse
	Is Rework IsRework	A calculated flag that identifies if an invoice has undergone rework.
Description This boolean flag is set to true if an invoice's process flow includes rework activities, such as 'Discrepancy Resolved' or a second 'Invoice Data Validated' step after an initial failure. It is calculated across the entire case. This attribute directly supports the 'Discrepancy Rework Rate' KPI. It simplifies analysis by allowing for easy filtering and aggregation of all invoices that required extra manual effort, helping to quantify the impact of poor data quality or process exceptions. Why it matters Directly measures rework, allowing for simple quantification and analysis of process exceptions and inefficiencies. Where to get This is not a source system field. It is calculated in the process mining tool by checking for the occurrence of specific rework-indicating activities within a case. Examples truefalse
	Last Data Update LastDataUpdate	The timestamp of the last data refresh from the source system.
Description This attribute indicates the last time the event log was updated with fresh data from Microsoft Dynamics 365. It provides context on the recency of the data being analyzed. For dashboards and ongoing monitoring, this timestamp is crucial for users to understand if they are viewing the most current process data. It helps manage expectations about the data's freshness and aids in monitoring the data pipeline's health. Why it matters Informs users about the freshness of the data, which is critical for making timely and accurate business decisions based on the analysis. Where to get This is a metadata field generated and stamped by the data extraction and loading (ETL) tool at the time of data ingestion. Examples 2023-06-01T02:00:00Z2023-06-02T02:00:00Z
	Source System SourceSystem	The system from which the data was extracted.
Description This attribute identifies the origin of the event data, which is typically 'Microsoft Dynamics 365' in this context. It becomes particularly important when data from multiple systems (e.g., an OCR scanning tool and D365) is combined. In a process mining analysis, this helps in data lineage, troubleshooting, and understanding the technological landscape of the process. It allows for filtering the analysis to events from a specific system. Why it matters Provides essential context about data origin, which is critical for data validation and in multi-system process analyses. Where to get This is typically a static value ('Microsoft Dynamics 365') added during the data extraction and transformation process. Examples Microsoft Dynamics 365 FinanceD365 F&OAX2012

Required Recommended Optional

Accounts Payable Invoice Processing Activities

These are the key process steps and significant milestones to capture in your event log for accurate process discovery and performance analysis.

5 Recommended 9 Optional

	Activity	Description
	Invoice Approved	Represents the final approval in the workflow, authorizing the invoice to be posted for payment. This is a critical milestone before the invoice enters the payment stage.
Why it matters Marks the end of the approval cycle. The time between 'Submitted for Approval' and this activity is a key KPI for identifying approval bottlenecks and long-running workflows. Where to get An explicit event captured in the workflow history logs (WorkflowTrackingStatusTable) when the workflow status is updated to 'Completed' or 'Approved'. Capture Extract the 'Approved' or 'Completed' event for the invoice from the workflow tracking history. Event type explicit
	Invoice Posted	The approved invoice is officially recorded in the general ledger, creating a financial liability. This is a critical, often irreversible, accounting transaction.
Why it matters Posting is a major milestone that makes the invoice eligible for payment. It confirms that all validation and approval steps are complete and the liability is recognized. Where to get This is an explicit transaction. The posting date and time are recorded on the vendor invoice journal (VendInvoiceJour) and related general ledger entries (GeneralJournalEntry). Capture Use the posting timestamp from the VendInvoiceJour or GeneralJournalEntry tables. Event type explicit
	Invoice Registered	Marks the initial creation of an invoice record in the system, either through manual entry, OCR scanning, or electronic data interchange (EDI). This is the starting point of the invoice processing lifecycle.
Why it matters This activity is the primary start event for the process. Analyzing the time from this point to payment provides the overall invoice cycle time, which is a key performance indicator. Where to get Inferred from the creation timestamp (CreatedDateTime field) of the invoice header record in the pending vendor invoices or invoice journal tables, such as VendInvoiceInfoTable. Capture Use the creation timestamp of the invoice header record. Event type inferred
	Invoice Submitted For Approval	The invoice is formally submitted into a workflow for review and approval by authorized personnel. This marks the beginning of the approval cycle.
Why it matters This is a key milestone that starts the clock for measuring the invoice approval cycle time. It helps distinguish data entry/matching time from approval latency. Where to get This is an explicit event captured in the D365 workflow history logs (WorkflowTrackingStatusTable) for the specific invoice document. The submission timestamp is recorded. Capture Extract the 'Submitted' event for the invoice from the workflow tracking history. Event type explicit
	Payment Executed	The payment is officially made through the posting of a payment journal. This transaction settles the liability created by the posted invoice.
Why it matters This is a primary end event for the process. It is used to calculate payment term compliance, identify late payments, and measure the final end-to-end cycle time. Where to get An explicit event captured when the payment journal is posted. The settlement details are stored in the vendor transactions table (VendTrans), linking the payment to the invoice. Capture Use the transaction date of the payment settlement record in VendTrans. Event type explicit
	Discrepancy Identified	Occurs when the invoice fails validation or matching against the purchase order or goods receipt, requiring manual intervention. A hold or specific status is often placed on the invoice.
Why it matters This activity initiates a rework loop. Analyzing its frequency and cause is crucial for identifying process inefficiencies, data quality issues, or supplier problems. Where to get This can be explicitly logged in the invoice's history or inferred when an invoice is placed 'On Hold' with a reason code related to matching variance. Look for status changes in the invoice header. Capture Capture the timestamp when the invoice status is set to 'On Hold' or a discrepancy flag is set. Event type inferred
	Discrepancy Resolved	The action taken to clear a hold or correct an invoice after a discrepancy was identified. The invoice is now ready to be re-submitted for matching or approval.
Why it matters This activity closes a rework loop. The time taken to resolve discrepancies is a key indicator of the efficiency of the exception handling process. Where to get Inferred from the timestamp when an 'On Hold' status is removed from the invoice, or when the invoice is re-submitted to the workflow after a rejection. Capture Capture the timestamp when a hold is released or the invoice is re-submitted post-rejection. Event type inferred
	Goods Receipt Matched	For three-way matching, this activity confirms that the invoiced goods or services have been received by cross-referencing with goods receipt notes. This step verifies physical delivery against the invoice.
Why it matters Tracking this activity helps analyze the efficiency of the three-way matching process and identify delays caused by missing or incorrect goods receipt information. Where to get Similar to PO matching, this is inferred from status updates on the invoice indicating successful matching against product receipt journals (Packing Slips). Capture Look for status changes or flags indicating successful three-way matching. Event type inferred
	Invoice Cancelled	The invoice is voided or cancelled after being posted, usually to correct an error. This is an alternative, exceptional endpoint for the process.
Why it matters Tracking cancellations is important for understanding process quality and error rates. A high frequency of cancellations may indicate systemic issues in the upstream process. Where to get This is an explicit event. D365 creates a reversing or credit transaction that is linked back to the original invoice journal. The posting date of this reversal marks the cancellation. Capture Identify the posting date of the reversing transaction associated with the original invoice. Event type explicit
	Invoice Data Validated	Represents the system or user performing initial checks on the captured invoice data for completeness and correctness, before matching or approval. This can be an automated system validation or a manual review step.
Why it matters Tracking this activity helps identify delays caused by poor data quality. A high rate of failures or long duration here indicates issues with data capture processes, such as OCR accuracy. Where to get This is often an inferred event. It can be derived from the timestamp when the invoice status changes from 'New' or 'Draft' to a state like 'Validated' or 'Ready for Matching', or the last modification before submission to workflow. Capture Capture the timestamp of a status change indicating successful validation or the update event before workflow submission. Event type inferred
	Invoice Rejected	An approver denies the invoice within the workflow, typically sending it back to the originator for correction or clarification. This initiates a rework loop.
Why it matters Tracking rejections helps identify reasons for approval delays and rework. It can highlight issues with invoice coding, policy violations, or insufficient documentation. Where to get This is an explicit event captured in the workflow history (WorkflowTrackingStatusTable) when an approver selects the 'Reject' action. Capture Extract the 'Rejected' event for the invoice from the workflow tracking history. Event type explicit
	Payment Cleared	The payment executed by the company has cleared the bank, as confirmed through the bank reconciliation process. This is the final financial confirmation of payment completion.
Why it matters This activity provides the ultimate confirmation of cash outflow. Analyzing the time between payment execution and clearing is important for treasury and cash management. Where to get This information is derived from bank reconciliation modules. It's inferred when a bank statement line corresponding to the payment is matched and posted in D365. Capture Requires linking bank transaction data (BankStmtISOAccountStatement) back to the original payment. Event type inferred
	Payment Scheduled	The posted invoice is selected and included in a payment proposal or a payment journal, scheduling it for a future payment run. This indicates the intent to pay.
Why it matters This activity is crucial for cash flow forecasting and analyzing payment term compliance. It helps identify if early payment discounts are being considered and planned for. Where to get Inferred from the creation of a payment journal line (LedgerJournalTrans) that includes the invoice. The transaction date on the journal line indicates the scheduled payment date. Capture Use the creation date of the payment journal line referencing the invoice. Event type inferred
	Purchase Order Matched	The process of associating an invoice with one or more purchase orders to verify quantities, prices, and terms. This is a critical validation step for PO-based invoices.
Why it matters This activity is essential for measuring first-pass match rates and identifying bottlenecks in the matching process. Failures here often lead to discrepancy resolution loops. Where to get Can be inferred when the matching status on the invoice record is updated to 'Matched' or when line-level matching details are successfully saved. This information is typically stored in tables related to vendor invoice lines. Capture Identify status changes on the invoice header or lines related to PO matching success. 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 Finance and Operations. Navigate to the main dashboard and search for or select the Data management workspace.
Create New Export Project: Within the Data management workspace, select the Export tile. Create a new export project by clicking New. Provide a meaningful name (e.g., AP_Invoice_Event_Log_Export) and a description.
Add Required Data Entities: In the project details, click Add entity. Search for and add the following standard data entities. You may need to add each one individually:
- Vendor invoice headers (VendInvoiceInfoTable)
- Vendor posted invoices (VendInvoiceJour)
- Vendor transactions (VendTrans)
- Vendor settlement transactions (VendSettlement)
- Workflow tracking status (WorkflowTrackingStatusTable)
- Payment journal lines (LedgerJournalTrans)
- Bank transactions (BankAccountTrans)
Set Export Format: For each added entity, specify the Target data format. CSV is recommended for its simplicity and compatibility with most data transformation tools. Ensure the 'Skip staging' option is set to 'No' for initial runs to allow for filtering and review.
Apply Filters and Date Ranges: For each entity, select it and click Filter. It is critical to filter by the legal entity (Company) you want to analyze. Apply a date range filter on a primary date field (e.g., Created date and time on Vendor invoice headers) to limit the data volume to a manageable period, such as the last 6 months.
Configure Export Job: Save the project configuration. You can now run the export manually by clicking the Export button in the action pane. For recurring extractions, use the Create recurring data job option to schedule the export to run automatically (e.g., weekly).
Download Exported Files: Once the job completes, navigate to the job history. From there, you can download the package, which will be a ZIP file containing separate CSV files for each data entity.
Combine and Transform Data: Unzip the downloaded package. Use an external tool like Power BI (Power Query), a Python script, or a database (e.g., SQL Server) to load the individual CSV files. Apply the transformation logic provided in the Query section below to join these files and pivot the data into the required event log format (one row per activity).
Finalize Event Log File: After applying the transformation logic, export the resulting table to a single CSV file. Ensure the column headers match the required attributes: Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber.
Upload to Process Mining Tool: The final CSV file is now ready to be uploaded to your process mining application.

Configuration

Core Data Entities: The extraction relies on a set of standard entities. The most critical are Vendor invoice headers (for core invoice data), Vendor posted invoices (for posted data), Workflow tracking status (for approval events), and Vendor transactions (for payment information).
Export Format: Use CSV as the target data format. It is lightweight and universally supported by data transformation tools.
Incremental vs. Full Export: For initial analysis, perform a full export for a defined historical period. For ongoing monitoring, configure recurring jobs. The Data Management Framework supports incremental exports, but it requires careful configuration of change tracking on entities.
Date Range Filtering: Always apply a date range filter to limit the dataset size. A common practice is to extract 3-6 months of data for a meaningful analysis without overwhelming the system. Filter on the createdDateTime field of the Vendor invoice headers entity.
Company (Legal Entity) Filter: It is crucial to filter by Company (DataAreaId). Analyzing data from multiple legal entities in one go can be complex unless the process is standardized across them.
Security and Permissions: The user running the export requires a security role with permissions to the Data management workspace and read access to all the specified data entities. Roles like Data management administrator or a custom-configured equivalent are typically needed.

a Sample Query config

-- This is a conceptual SQL query representing the logic to be applied in an external tool (e.g., Power BI, Python, SQL DB)
-- to transform the exported D365 CSV files into a single event log.
-- Replace '[Your Company]' with the legal entity ID you are analyzing.

-- Step 1: Define CTEs (Common Table Expressions) to represent your loaded CSV files.

WITH VendInvoiceInfoTable AS (
    -- Represents the 'Vendor invoice headers' CSV file
    SELECT * FROM [dbo].[VendInvoiceInfoTable_Export]
),
VendInvoiceJour AS (
    -- Represents the 'Vendor posted invoices' CSV file
    SELECT * FROM [dbo].[VendInvoiceJour_Export]
),
VendTrans AS (
    -- Represents the 'Vendor transactions' CSV file
    SELECT * FROM [dbo].[VendTrans_Export]
),
VendSettlement AS (
    -- Represents the 'Vendor settlement transactions' CSV file
    SELECT * FROM [dbo].[VendSettlement_Export]
),
WorkflowTrackingStatusTable AS (
    -- Represents the 'Workflow tracking status' CSV file
    SELECT * FROM [dbo].[WorkflowTrackingStatusTable_Export]
),
LedgerJournalTrans AS (
    -- Represents the 'Payment journal lines' CSV file
    SELECT * FROM [dbo].[LedgerJournalTrans_Export]
),
BankAccountTrans AS (
    -- Represents the 'Bank transactions' CSV file
    SELECT * FROM [dbo].[BankAccountTrans_Export]
),
-- Step 2: Extract each activity as a separate SELECT statement and UNION them together.

FinalEventLog AS (
    -- 1. Invoice Registered
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Registered' AS "ActivityName",
        i.CREATEDDATETIME AS "EventTime",
        i.CREATEDBY AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceInfoTable i
    WHERE i.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 2. Invoice Data Validated (approximated by workflow start)
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Data Validated' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Submission' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 3 & 4. Purchase Order & Goods Receipt Matched (approximated by posting for PO invoices)
    SELECT
        vj.INVOICENUM AS "Invoice",
        'Purchase Order Matched' AS "ActivityName",
        vj.INVOICEDATE AS "EventTime",
        vj.MODIFIEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceJour vj
    WHERE vj.PURCHID IS NOT NULL AND vj.PURCHID <> '' AND vj.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 5. Discrepancy Identified (approximated by a rejection event)
    SELECT
        i.INVOICENUM AS "Invoice",
        'Discrepancy Identified' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Rejection' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 6. Invoice Submitted For Approval
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Submitted For Approval' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Submission' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 7. Invoice Approved
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Approved' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGSTATUS = 'Completed' AND w.TRACKINGTYPE = 'Approval' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 8. Invoice Rejected
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Rejected' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Rejection' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 9. Discrepancy Resolved (approximated by resubmission after rejection)
    SELECT
        i.INVOICENUM AS "Invoice",
        'Discrepancy Resolved' AS "ActivityName",
        w_submit.CREATEDDATETIME AS "EventTime",
        w_submit.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w_submit
    JOIN VendInvoiceInfoTable i ON w_submit.CONTEXTRECID = i.RECID
    WHERE w_submit.TRACKINGTYPE = 'Submission' AND w_submit.DATACOMPANY = '[Your Company]'
      AND EXISTS (
          SELECT 1 FROM WorkflowTrackingStatusTable w_reject
          WHERE w_reject.CONTEXTRECID = w_submit.CONTEXTRECID
            AND w_reject.TRACKINGTYPE = 'Rejection'
            AND w_reject.CREATEDDATETIME < w_submit.CREATEDDATETIME
      )

    UNION ALL

    -- 10. Invoice Posted
    SELECT
        vj.INVOICENUM AS "Invoice",
        'Invoice Posted' AS "ActivityName",
        vj.CREATEDDATETIME AS "EventTime",
        vj.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceJour vj
    WHERE vj.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 11. Payment Scheduled
    SELECT
        ljt.INVOICENUM AS "Invoice",
        'Payment Scheduled' AS "ActivityName",
        ljt.CREATEDDATETIME AS "EventTime",
        ljt.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM LedgerJournalTrans ljt
    JOIN VendInvoiceJour vj ON ljt.INVOICENUM = vj.INVOICENUM AND ljt.DATACOMPANY = vj.DATACOMPANY
    WHERE ljt.POSTED <> 'Yes' AND ljt.INVOICENUM <> '' AND ljt.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 12. Payment Executed
    SELECT 
        vt.INVOICE AS "Invoice",
        'Payment Executed' AS "ActivityName",
        vt.TRANSDATE AS "EventTime",
        vt.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendTrans vt
    JOIN VendSettlement vs ON vt.RECID = vs.OFFSETRECID
    JOIN VendTrans vt_inv ON vs.TRANSRECID = vt_inv.RECID
    JOIN VendInvoiceJour vj ON vt_inv.VOUCHER = vj.LEDGERVOUCHER AND vt_inv.DATACOMPANY = vj.DATACOMPANY
    WHERE vt.TRANSTYPE = 'Payment' AND vt.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 13. Payment Cleared
    SELECT 
        vt.INVOICE AS "Invoice",
        'Payment Cleared' AS "ActivityName",
        bt.CLEAREDDATE AS "EventTime",
        bt.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM BankAccountTrans bt
    JOIN VendTrans vt ON bt.VOUCHER = vt.VOUCHER AND bt.DATACOMPANY = vt.DATACOMPANY
    JOIN VendInvoiceJour vj ON vt.INVOICE = vj.INVOICENUM AND vt.DATACOMPANY = vj.DATACOMPANY
    WHERE bt.CLEAREDDATE IS NOT NULL AND vt.TRANSTYPE = 'Payment' AND bt.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 14. Invoice Cancelled
    SELECT
        vj.INVOICENUM AS "Invoice",
        'Invoice Cancelled' AS "ActivityName",
        vj.MODIFIEDDATETIME AS "EventTime",
        vj.MODIFIEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceJour vj
    WHERE vj.APPROVALSTATUS = 'Canceled' AND vj.DATACOMPANY = '[Your Company]'
)

SELECT * FROM FinalEventLog;

Steps

Prerequisites & Access: Ensure you have read-only SQL access to the Microsoft Dynamics 365 Finance and Operations database. This method targets either a production database replica (Azure SQL) or a Bring-Your-Own-Database (BYOD) instance. Direct querying on the primary production database is not recommended.
Connect to the Database: Use a SQL client like SQL Server Management Studio (SSMS) or Azure Data Studio to connect to the database instance using the provided credentials.
Prepare the SQL Query: Copy the T-SQL query provided in the query section into a new query window in your SQL client.
Configure Parameters: At the top of the query, locate the parameter section. Set the @StartDate, @EndDate to define your analysis period, and set @CompanyId to the specific legal entity (e.g., 'USMF') you want to analyze.
Execute the Query: Run the complete T-SQL script. The execution time will vary depending on the date range and the volume of invoice data in your system.
Review Initial Results: Once the query finishes, review the output in the results grid to ensure it contains data and the columns look correct. Check for a variety of ActivityName values.
Export to CSV: Export the result set to a CSV file. In SSMS, right-click the result grid, select 'Save Results As...', and choose the CSV format. Name the file event_log.csv.
Verify CSV Format: Open the CSV file and confirm it has a header row with the correct column names (Invoice, ActivityName, EventTime, etc.). Ensure the EventTime column is in a standard format like YYYY-MM-DD HH:MI:SS.
Upload to Process Mining Tool: The CSV file is now ready to be uploaded to your process mining application. Map the columns as follows: Invoice to Case ID, ActivityName to Activity, and EventTime to Timestamp.

Configuration

Database Connection: This method requires a direct connection to the Dynamics 365 Azure SQL database. It is strongly recommended to use a read-only replica or a BYOD instance to avoid impacting production performance.
Date Range: The query includes @StartDate and @EndDate parameters. It's advisable to start with a limited range (e.g., 3-6 months) to manage query performance and data volume. The filter is applied on the invoice creation date.
Company Filter: The @CompanyId parameter (known as DataAreaId in D365) is mandatory. You must set this to a specific legal entity ID to ensure you are analyzing data from only one company.
Workflow Data: The query relies on standard D365 workflow history tables (WorkflowTrackingStatusTable). If your organization uses a highly customized approval workflow, the query logic for approval steps may need adjustments.
Performance Considerations: Executing this query on a large dataset can be resource-intensive. Run it during off-peak business hours to minimize potential impact on system performance. For very large datasets, consider running the query in smaller time-based batches.

a Sample Query sql

-- ==========================================================================================
-- T-SQL EXTRACTOR FOR DYNAMICS 365 F&O - ACCOUNTS PAYABLE INVOICE PROCESSING
-- ==========================================================================================

-- Parameters: Configure these values for your extraction
DECLARE @StartDate DATE = '2023-01-01'; -- The start date of the extraction window
DECLARE @EndDate DATE = '2023-12-31';   -- The end date of the extraction window
DECLARE @CompanyId NVARCHAR(4) = '[YourCompanyId]'; -- The legal entity/company ID (e.g., 'USMF')

-- ==========================================================================================
-- Main Query: Do not edit below this line
-- ==========================================================================================

WITH BaseInvoices AS (
    -- Selects the core set of invoices to be analyzed based on the date range and company
    SELECT
        viit.NUM AS Invoice,
        viit.PURCHID AS PurchaseOrderNumber,
        viit.INVOICEACCOUNT,
        viit.INVOICEDATE,
        viit.INVOICEAMOUNTMST AS InvoiceAmount,
        dp.NAME AS VendorName,
        pt.DESCRIPTION AS PaymentTerms,
        viit.DUEDATE AS InvoiceDueDate,
        viit.CREATEDBY AS CreatedUserId,
        viit.CREATEDDATETIME,
        viit.RECID AS VendInvoiceInfoTableRecId,
        viit.DATAREAID
    FROM VendInvoiceInfoTable viit
    LEFT JOIN VendTable vt ON viit.INVOICEACCOUNT = vt.ACCOUNTNUM AND viit.DATAREAID = vt.DATAREAID
    LEFT JOIN DirPartyTable dp ON vt.PARTY = dp.RECID
    LEFT JOIN PaymTerm pt ON vt.PAYMTERMID = pt.PAYMTERMID AND vt.DATAREAID = pt.DATAREAID
    WHERE viit.CREATEDDATETIME >= @StartDate 
      AND viit.CREATEDDATETIME < DATEADD(day, 1, @EndDate) 
      AND viit.DATAREAID = @CompanyId
)

-- 1. Invoice Registered
SELECT
    bi.Invoice,
    'Invoice Registered' AS ActivityName,
    bi.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
LEFT JOIN USERINFO u ON bi.CreatedUserId = u.ID

UNION ALL

-- 2. Invoice Data Validated (Approximated by first review step in workflow)
SELECT
    bi.Invoice,
    'Invoice Data Validated' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable')
  AND wst.TRACKINGTYPE = 2 -- Task/Work Item
  AND wst.WORKFLOWSTEPID LIKE '%Review%'
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 3 & 4. Purchase Order / Goods Receipt Matched (Approximated by last modification when status is 'Passed')
SELECT
    bi.Invoice,
    'Purchase Order Matched' AS ActivityName, -- Assuming PO and GR matching is one event
    viit.MODIFIEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendInvoiceInfoTable viit ON bi.VendInvoiceInfoTableRecId = viit.RECID
LEFT JOIN USERINFO u ON viit.MODIFIEDBY = u.ID
WHERE viit.LASTMATCHINGSTAT = 2 -- Passed

UNION ALL

-- 5. Discrepancy Identified (Approximated by last modification when status is 'Failed')
SELECT
    bi.Invoice,
    'Discrepancy Identified' AS ActivityName,
    viit.MODIFIEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendInvoiceInfoTable viit ON bi.VendInvoiceInfoTableRecId = viit.RECID
LEFT JOIN USERINFO u ON viit.MODIFIEDBY = u.ID
WHERE viit.LASTMATCHINGSTAT = 3 -- Failed

UNION ALL

-- 6. Invoice Submitted For Approval
SELECT
    bi.Invoice,
    'Invoice Submitted For Approval' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable') 
  AND wst.TRACKINGTYPE = 1 -- Submission
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 7. Invoice Approved (Final approval of the workflow)
SELECT
    bi.Invoice,
    'Invoice Approved' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable') 
  AND wst.TRACKINGTYPE = 5 -- Completion
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 8. Invoice Rejected
SELECT
    bi.Invoice,
    'Invoice Rejected' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable') 
  AND wst.TRACKINGTYPE = 7 -- Rejection
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 9. Discrepancy Resolved (Approximated by workflow 'Request Change' step)
SELECT
    bi.Invoice,
    'Discrepancy Resolved' AS ActivityName,
    wst.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN WorkflowTrackingStatusTable wst ON bi.VendInvoiceInfoTableRecId = wst.CONTEXTRECID
LEFT JOIN USERINFO u ON wst.USERID = u.ID
WHERE wst.CONTEXTTABLEID = tableidget('VendInvoiceInfoTable') 
  AND wst.TRACKINGTYPE = 3 -- RequestChange
  AND bi.DATAREAID = wst.CONTEXTCOMPANY

UNION ALL

-- 10. Invoice Posted
SELECT
    bi.Invoice,
    'Invoice Posted' AS ActivityName,
    vij.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendInvoiceJour vij ON bi.Invoice = vij.INVOICEID AND bi.INVOICEDATE = vij.INVOICEDATE AND bi.INVOICEACCOUNT = vij.INVOICEACCOUNT AND bi.DATAREAID = vij.DATAREAID
LEFT JOIN USERINFO u ON vij.CREATEDBY = u.ID

UNION ALL

-- 11. Payment Scheduled (Creation of a payment journal line for the invoice)
SELECT DISTINCT
    bi.Invoice,
    'Payment Scheduled' AS ActivityName,
    ljt.CREATEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendTrans vt ON bi.Invoice = vt.INVOICENUM AND bi.DATAREAID = vt.DATAREAID
JOIN LedgerJournalTrans ljt ON vt.VOUCHER = ljt.VOUCHER AND vt.TRANSDATE = ljt.TRANSDATE AND vt.DATAREAID = ljt.DATAREAID
JOIN LedgerJournalTable ljh ON ljt.JOURNALNUM = ljh.JOURNALNUM AND ljt.DATAREAID = ljh.DATAREAID
LEFT JOIN USERINFO u ON ljt.CREATEDBY = u.ID
WHERE ljh.JOURNALTYPE = 2 -- Vendor Payment
  AND ljh.POSTED = 0

UNION ALL

-- 12. Payment Executed (Posting of the payment journal)
SELECT
    bi.Invoice,
    'Payment Executed' AS ActivityName,
    vs.TRANSDATE AS EventTime, -- Settlement Date
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendTrans vt_inv ON bi.Invoice = vt_inv.INVOICENUM AND bi.DATAREAID = vt_inv.DATAREAID
JOIN VendSettlement vs ON vt_inv.RECID = vs.TRANSRECID
JOIN VendTrans vt_pay ON vs.OFFSETRECID = vt_pay.RECID
LEFT JOIN USERINFO u ON vs.CREATEDBY = u.ID

UNION ALL

-- 13. Payment Cleared (Bank reconciliation date)
SELECT 
    bi.Invoice,
    'Payment Cleared' AS ActivityName,
    bt.CLEAREDDATE AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendTrans vt_inv ON bi.Invoice = vt_inv.INVOICENUM AND bi.DATAREAID = vt_inv.DATAREAID
JOIN VendSettlement vs ON vt_inv.RECID = vs.TRANSRECID
JOIN VendTrans vt_pay ON vs.OFFSETRECID = vt_pay.RECID
JOIN BankAccountTrans bat ON vt_pay.VOUCHER = bat.VOUCHER AND vt_pay.TRANSDATE = bat.TRANSDATE AND vt_pay.DATAREAID = bat.DATAREAID
JOIN BankTrans bt ON bat.RECID = bt.BANKACCOUNTTRANSRECID
LEFT JOIN USERINFO u ON bt.CREATEDBY = u.ID
WHERE bt.CLEAREDDATE > '1900-01-01'

UNION ALL

-- 14. Invoice Cancelled
SELECT
    bi.Invoice,
    'Invoice Cancelled' AS ActivityName,
    vij.MODIFIEDDATETIME AS EventTime,
    u.NAME AS UserName,
    bi.VendorName,
    bi.InvoiceAmount,
    bi.InvoiceDueDate,
    bi.PaymentTerms,
    bi.PurchaseOrderNumber
FROM BaseInvoices bi
JOIN VendInvoiceJour vij ON bi.Invoice = vij.INVOICEID AND bi.INVOICEDATE = vij.INVOICEDATE AND bi.INVOICEACCOUNT = vij.INVOICEACCOUNT AND bi.DATAREAID = vij.DATAREAID
LEFT JOIN USERINFO u ON vij.MODIFIEDBY = u.ID
WHERE vij.INVOICECANCELLED_RU = 1 -- Note: This field can be localization-specific. Adjust if needed.

ORDER BY Invoice, EventTime;

Steps

Prerequisite: Register an Application in Azure AD
- Navigate to the Azure Active Directory admin center.
- Go to App registrations and create a new registration.
- Under API permissions, add a permission for Dynamics ERP and select user_impersonation (delegated permission).
- Create a new client secret under Certificates & secrets and securely store the value.
- Note the Application (client) ID and the Directory (tenant) ID.
Prerequisite: Create an Application User in Dynamics 365
- In Dynamics 365 F&O, navigate to System administration > Users > Users.
- Create a new user and set the User ID and User name. Set the provider to be your Azure AD tenant (e.g., https://sts.windows.net/[your_tenant_id]).
- Enter the Application (client) ID from the Azure AD app registration into the Client ID field.
- Assign appropriate security roles to this user (e.g., a role with read access to vendor invoices, payments, and workflow history).
Set Up Local Python Environment
- Ensure you have Python 3.7 or newer installed.
- Install the required libraries by running the following command in your terminal: pip install requests msal pandas
Configure the Python Script
- Copy the provided Python script into a file (e.g., extract_d365_ap.py).
- Fill in the placeholder variables at the top of the script: D365_URL, TENANT_ID, CLIENT_ID, CLIENT_SECRET, DATA_AREA_ID (your D365 company ID, e.g., 'USMF'), START_DATE, and END_DATE.
Execute the Script
- Run the script from your terminal: python extract_d365_ap.py
- The script will authenticate with Azure AD, fetch data for each defined activity from the Dynamics 365 OData API, and handle pagination automatically.
Data Transformation and Assembly
- The script processes the JSON response from the API for each activity.
- It maps the source fields from various D365 data entities (VendorInvoiceHeaders, WorkflowTrackingStatusTable, VendTrans, etc.) to the required event log structure (Invoice, ActivityName, EventTime, etc.).
- All individual activity data is combined into a single master event log.
Data Sorting and Cleaning
- The combined event log is sorted chronologically for each invoice to ensure the correct sequence of events.
- Basic data type conversions are handled by the pandas library.
Export Final Event Log
- The script saves the final, consolidated event log as a CSV file named d365_ap_event_log.csv in the same directory.
- This file is now formatted and ready for upload into a process mining tool like ProcessMind.

Configuration

Authentication: The script uses OAuth 2.0 client credentials flow. An Azure AD App Registration and a corresponding Application User in Dynamics 365 with appropriate read permissions are mandatory.
Data Entities: The extraction relies on standard D365 F&O OData entities. Key entities include: VendorInvoiceHeaders, VendorInvoiceLines, WorkflowTrackingStatusTable, VendTrans, VendorPaymentJournalLines, and BankCanceledChecks. Ensure the application user has read access to these.
Date Range: Set the START_DATE and END_DATE variables to define the extraction window. For initial analysis, a period of 3-6 months is recommended to balance data volume and performance.
Company Filter: The DATA_AREA_ID variable is a mandatory filter to specify which legal entity (company) in D365 the data should be extracted from.
Workflow Configuration: The script assumes certain activity names within the workflow history (e.g., 'Approve vendor invoice', 'Resolve Discrepancy'). These may need to be adjusted in the script's filtering logic to match your organization's specific workflow configuration.
Performance: The OData API can be slow for very large datasets. The script includes pagination handling but does not have advanced retry logic for API throttling. It is best to run it during off-peak hours for large extractions.

a Sample Query python

import requests
import msal
import pandas as pd
import time
import urllib.parse

class=class="hl-string">"hl-comment"># --- CONFIGURATION --- 
class=class="hl-string">"hl-comment"># Replace with your Dynamics 365 Finance & Operations details
D365_URL = class="hl-string">"https://[your_d365_instance].operations.dynamics.com"
TENANT_ID = class="hl-string">"[your_azure_tenant_id]"
CLIENT_ID = class="hl-string">"[your_azure_app_client_id]"
CLIENT_SECRET = class="hl-string">"[your_azure_app_client_secret]"

class=class="hl-string">"hl-comment"># Replace with your specific data filters
DATA_AREA_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"># --- AUTHENTICATION --- 
def get_access_token():
    class="hl-string">"""Authenticates with Azure AD and returns an access token."""
    authority = fclass="hl-string">"https://login.microsoftonline.com/{TENANT_ID}"
    scope = [fclass="hl-string">"{D365_URL}/.default"]
    app = msal.ConfidentialClientApplication(
        CLIENT_ID, authority=authority, client_credential=CLIENT_SECRET
    )
    result = app.acquire_token_for_client(scopes=scope)
    if class="hl-string">"access_token" in result:
        return result[class="hl-string">'access_token']
    else:
        raise Exception(fclass="hl-string">"Error acquiring token: {result.get('error_description')}")

class=class="hl-string">"hl-comment"># --- DATA FETCHING --- 
def fetch_odata_data(access_token, entity_url):
    class="hl-string">"""Fetches all pages of data from a given OData entity URL."""
    all_records = []
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json',
        class="hl-string">'Prefer': class="hl-string">'odata.maxpagesize=1000' class=class="hl-string">"hl-comment"># Request pagination
    }
    while entity_url:
        try:
            response = requests.get(entity_url, headers=headers, timeout=60)
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raise an exception for bad status codes
            data = response.json()
            all_records.extend(data[class="hl-string">'value'])
            entity_url = data.get(class="hl-string">'@odata.nextLink') class=class="hl-string">"hl-comment"># Get URL for the next page
            print(fclass="hl-string">"Fetched {len(all_records)} records so far...")
        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"An error occurred: {e}")
            time.sleep(5) class=class="hl-string">"hl-comment"># Simple retry logic
            class=class="hl-string">"hl-comment"># More robust error handling might be needed for production
            continue
    return all_records

class=class="hl-string">"hl-comment"># --- MAIN SCRIPT --- 
def main():
    print(class="hl-string">"Acquiring access token...")
    token = get_access_token()
    all_activity_dfs = []

    base_api_url = fclass="hl-string">"{D365_URL}/data"

    class=class="hl-string">"hl-comment"># Common attributes to select
    common_attrs = class="hl-string">'VendorAccount,InvoiceNumber,InvoiceAmount,InvoiceDate,DueDate,PaymentTermsName,PurchaseOrderNumber'

    class=class="hl-string">"hl-comment"># 1. Invoice Registered
    print(class="hl-string">"\nExtracting 'Invoice Registered'...")
    entity = class="hl-string">'VendorInvoiceHeaders'
    filters = fclass="hl-string">"dataAreaId eq '{DATA_AREA_ID}' and CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}"
    url = fclass="hl-string">"{base_api_url}/{entity}?$select={common_attrs},CreatedDateTime,CreatedBy&$filter={filters}"
    records = fetch_odata_data(token, url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Registered'
        df.rename(columns={class="hl-string">'InvoiceNumber': class="hl-string">'Invoice', class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'CreatedBy': class="hl-string">'UserName', class="hl-string">'VendorAccount': class="hl-string">'VendorName'}, inplace=True)
        df[class="hl-string">'PaymentTerms'] = df[class="hl-string">'PaymentTermsName']
        df[class="hl-string">'InvoiceDueDate'] = df[class="hl-string">'DueDate']
        all_activity_dfs.append(df)

    class=class="hl-string">"hl-comment"># Get a list of invoice numbers for subsequent queries
    if class="hl-string">'Invoice' in df.columns:
        invoice_list = df[class="hl-string">'Invoice'].unique().tolist()
        if not invoice_list:
            print(class="hl-string">"No invoices found in the specified date range. Exiting.")
            return
        class=class="hl-string">"hl-comment"># Prepare for class="hl-string">'in' clause in OData filter
        invoice_filter_part = class="hl-string">" or ".join([fclass="hl-string">"RefRecIdTable/InvoiceNumber eq '{inv}'" for inv in invoice_list])
        vendtrans_filter_part = class="hl-string">" or ".join([fclass="hl-string">"Invoice eq '{inv}'" for inv in invoice_list])
    else:
        print(class="hl-string">"No invoices registered in the period. Exiting.")
        return

    class=class="hl-string">"hl-comment"># Activities from Workflow History (Submitted, Approved, Rejected, Discrepancy related)
    print(class="hl-string">"\nExtracting workflow activities...")
    entity = class="hl-string">'WorkflowTrackingStatusTables'
    class=class="hl-string">"hl-comment"># NOTE: The class="hl-string">'Comment' field filter is highly dependent on your specific workflow configuration.
    class=class="hl-string">"hl-comment"># Adjust the activity names like class="hl-string">'Submit', class="hl-string">'Approve', class="hl-string">'Reject', class="hl-string">'Resolve' as needed.
    activity_filters = class="hl-string">"(contains(Comment, 'Submit') or contains(Comment, 'Approv') or contains(Comment, 'Reject') or contains(Comment, 'Discrepancy') or contains(Comment, 'Validat'))"
    filters = fclass="hl-string">"({invoice_filter_part}) and {activity_filters}"
    class=class="hl-string">"hl-comment"># Expand to get invoice number and other details
    url = fclass="hl-string">"{base_api_url}/{entity}?$select=Comment,CreatedDateTime,UserId&$expand=RefRecIdTable($select=InvoiceNumber,VendorAccount,InvoiceAmount,DueDate,PaymentTermsName,PurchaseOrderNumber)&$filter={filters}"
    records = fetch_odata_data(token, url)
    df_wf = pd.DataFrame(records)
    if not df_wf.empty:
        df_wf[class="hl-string">'Invoice'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'InvoiceNumber') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'VendorName'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'VendorAccount') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'InvoiceAmount'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'InvoiceAmount') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'InvoiceDueDate'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'DueDate') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'PaymentTerms'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'PaymentTermsName') if isinstance(x, dict) else None)
        df_wf[class="hl-string">'PurchaseOrderNumber'] = df_wf[class="hl-string">'RefRecIdTable'].apply(lambda x: x.get(class="hl-string">'PurchaseOrderNumber') if isinstance(x, dict) else None)
        
        def map_activity(comment):
            comment = str(comment).lower()
            if class="hl-string">'submit' in comment: return class="hl-string">'Invoice Submitted For Approval'
            if class="hl-string">'approv' in comment: return class="hl-string">'Invoice Approved'
            if class="hl-string">'reject' in comment: return class="hl-string">'Invoice Rejected'
            if class="hl-string">'discrepancy identified' in comment: return class="hl-string">'Discrepancy Identified' class=class="hl-string">"hl-comment"># Assumes specific comment
            if class="hl-string">'discrepancy resolved' in comment: return class="hl-string">'Discrepancy Resolved' class=class="hl-string">"hl-comment"># Assumes specific comment
            if class="hl-string">'match' in comment: return class="hl-string">'Purchase Order Matched' class=class="hl-string">"hl-comment"># Assumes step name
            if class="hl-string">'receipt matched' in comment: return class="hl-string">'Goods Receipt Matched' class=class="hl-string">"hl-comment"># Assumes step name
            if class="hl-string">'validat' in comment: return class="hl-string">'Invoice Data Validated' class=class="hl-string">"hl-comment"># Assumes step name
            return None

        df_wf[class="hl-string">'ActivityName'] = df_wf[class="hl-string">'Comment'].apply(map_activity)
        df_wf.rename(columns={class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'UserId': class="hl-string">'UserName'}, inplace=True)
        df_wf.dropna(subset=[class="hl-string">'ActivityName', class="hl-string">'Invoice'], inplace=True)
        all_activity_dfs.append(df_wf)

    class=class="hl-string">"hl-comment"># Activities from Posted Transactions (Posted, Paid, Cancelled)
    print(class="hl-string">"\nExtracting posted transactions (Invoice, Payment, Cancellation)...")
    entity = class="hl-string">'VendTrans'
    filters = fclass="hl-string">"dataAreaId eq '{DATA_AREA_ID}' and ({vendtrans_filter_part})"
    url = fclass="hl-string">"{base_api_url}/{entity}?$select=Invoice,TransDate,CreatedDateTime,TransactionType,LastSettleVoucher,VendorAccount&$filter={filters}"
    records = fetch_odata_data(token, url)
    df_vt = pd.DataFrame(records)
    if not df_vt.empty:
        class=class="hl-string">"hl-comment"># Invoice Posted
        df_posted = df_vt[df_vt[class="hl-string">'TransactionType'] == class="hl-string">'Invoice'].copy()
        df_posted[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Posted'
        class=class="hl-string">"hl-comment"># Payment Executed
        df_paid = df_vt[df_vt[class="hl-string">'TransactionType'] == class="hl-string">'Payment'].copy()
        df_paid[class="hl-string">'ActivityName'] = class="hl-string">'Payment Executed'
        class=class="hl-string">"hl-comment"># Invoice Cancelled (often a reversal of the invoice transaction)
        class=class="hl-string">"hl-comment"># This logic may need adjustment based on how cancellations are recorded
        df_cancelled = df_vt[(df_vt[class="hl-string">'TransactionType'] == class="hl-string">'Invoice') & (df_vt[class="hl-string">'Invoice'].str.contains(class="hl-string">'CANCEL', na=False))].copy()
        df_cancelled[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Cancelled'

        df_trans = pd.concat([df_posted, df_paid, df_cancelled])
        df_trans.rename(columns={class="hl-string">'TransDate': class="hl-string">'EventTime', class="hl-string">'VendorAccount': class="hl-string">'VendorName'}, inplace=True)
        df_trans[class="hl-string">'UserName'] = class="hl-string">'System' class=class="hl-string">"hl-comment"># Usually a system-driven event
        all_activity_dfs.append(df_trans)

    class=class="hl-string">"hl-comment"># 11. Payment Scheduled
    print(class="hl-string">"\nExtracting 'Payment Scheduled'...")
    entity = class="hl-string">'VendorPaymentJournalLines'
    filters = fclass="hl-string">"dataAreaId eq '{DATA_AREA_ID}' and ({vendtrans_filter_part})"
    url = fclass="hl-string">"{base_api_url}/{entity}?$select=JournalBatchNumber,CreatedDateTime,AccountDisplayValue,Invoice,AmountInTransactionCurrency&$filter={filters}"
    records = fetch_odata_data(token, url)
    df = pd.DataFrame(records)
    if not df.empty:
        df[class="hl-string">'ActivityName'] = class="hl-string">'Payment Scheduled'
        df.rename(columns={class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'AccountDisplayValue': class="hl-string">'VendorName'}, inplace=True)
        df[class="hl-string">'UserName'] = class="hl-string">'System' 
        df[class="hl-string">'InvoiceAmount'] = df[class="hl-string">'AmountInTransactionCurrency']
        all_activity_dfs.append(df)

    class=class="hl-string">"hl-comment"># 13. Payment Cleared
    print(class="hl-string">"\nExtracting 'Payment Cleared'...")
    class=class="hl-string">"hl-comment"># This requires linking payments to bank data, which can be complex.
    class=class="hl-string">"hl-comment"># This example assumes we can find cleared checks and relate them back via voucher.
    settle_vouchers = df_vt[df_vt[class="hl-string">'LastSettleVoucher'].notna()][class="hl-string">'LastSettleVoucher'].unique().tolist()
    if settle_vouchers:
        voucher_filter = class="hl-string">" or ".join([fclass="hl-string">"CheckNumber eq '{v}'" for v in settle_vouchers])
        entity = class="hl-string">'BankCanceledChecks' class=class="hl-string">"hl-comment"># This entity name might vary
        filters = fclass="hl-string">"dataAreaId eq '{DATA_AREA_ID}' and ({voucher_filter})"
        url = fclass="hl-string">"{base_api_url}/{entity}?$select=CheckNumber,ClearedDate,AccountID&$filter={filters}"
        records = fetch_odata_data(token, url)
        df_cleared = pd.DataFrame(records)
        if not df_cleared.empty:
            class=class="hl-string">"hl-comment"># Map back to invoice
            map_voucher_invoice = df_vt.set_index(class="hl-string">'LastSettleVoucher')[class="hl-string">'Invoice'].to_dict()
            df_cleared[class="hl-string">'Invoice'] = df_cleared[class="hl-string">'CheckNumber'].map(map_voucher_invoice)
            df_cleared.dropna(subset=[class="hl-string">'Invoice'], inplace=True)
            df_cleared[class="hl-string">'ActivityName'] = class="hl-string">'Payment Cleared'
            df_cleared.rename(columns={class="hl-string">'ClearedDate': class="hl-string">'EventTime'}, inplace=True)
            df_cleared[class="hl-string">'UserName'] = class="hl-string">'System'
            all_activity_dfs.append(df_cleared)

    class=class="hl-string">"hl-comment"># --- FINAL ASSEMBLY --- 
    print(class="hl-string">"\nAssembling final event log...")
    if not all_activity_dfs:
        print(class="hl-string">"No activities were extracted. Exiting.")
        return

    event_log = pd.concat(all_activity_dfs, ignore_index=True)

    class=class="hl-string">"hl-comment"># Select and reorder columns
    final_cols = [
        class="hl-string">'Invoice', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName',
        class="hl-string">'VendorName', class="hl-string">'InvoiceAmount', class="hl-string">'InvoiceDueDate', 
        class="hl-string">'PaymentTerms', class="hl-string">'PurchaseOrderNumber'
    ]
    class=class="hl-string">"hl-comment"># Add missing columns with None
    for col in final_cols:
        if col not in event_log.columns:
            event_log[col] = None
    
    event_log = event_log[final_cols]
    
    class=class="hl-string">"hl-comment"># Clean up and sort
    event_log.dropna(subset=[class="hl-string">'Invoice', class="hl-string">'ActivityName', class="hl-string">'EventTime'], inplace=True)
    event_log.sort_values(by=[class="hl-string">'Invoice', class="hl-string">'EventTime'], inplace=True)

    class=class="hl-string">"hl-comment"># Export to CSV
    output_filename = class="hl-string">'d365_ap_event_log.csv'
    event_log.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%SZ')
    print(fclass="hl-string">"\nExtraction complete. Event log saved to {output_filename}")

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

Steps

Access Data Management Workspace: Log in to Microsoft Dynamics 365 Finance and Operations. Navigate to the main dashboard and search for or select the Data management workspace.
Create New Export Project: Within the Data management workspace, select the Export tile. Create a new export project by clicking New. Provide a meaningful name (e.g., AP_Invoice_Event_Log_Export) and a description.
Add Required Data Entities: In the project details, click Add entity. Search for and add the following standard data entities. You may need to add each one individually:
- Vendor invoice headers (VendInvoiceInfoTable)
- Vendor posted invoices (VendInvoiceJour)
- Vendor transactions (VendTrans)
- Vendor settlement transactions (VendSettlement)
- Workflow tracking status (WorkflowTrackingStatusTable)
- Payment journal lines (LedgerJournalTrans)
- Bank transactions (BankAccountTrans)
Set Export Format: For each added entity, specify the Target data format. CSV is recommended for its simplicity and compatibility with most data transformation tools. Ensure the 'Skip staging' option is set to 'No' for initial runs to allow for filtering and review.
Apply Filters and Date Ranges: For each entity, select it and click Filter. It is critical to filter by the legal entity (Company) you want to analyze. Apply a date range filter on a primary date field (e.g., Created date and time on Vendor invoice headers) to limit the data volume to a manageable period, such as the last 6 months.
Configure Export Job: Save the project configuration. You can now run the export manually by clicking the Export button in the action pane. For recurring extractions, use the Create recurring data job option to schedule the export to run automatically (e.g., weekly).
Download Exported Files: Once the job completes, navigate to the job history. From there, you can download the package, which will be a ZIP file containing separate CSV files for each data entity.
Combine and Transform Data: Unzip the downloaded package. Use an external tool like Power BI (Power Query), a Python script, or a database (e.g., SQL Server) to load the individual CSV files. Apply the transformation logic provided in the Query section below to join these files and pivot the data into the required event log format (one row per activity).
Finalize Event Log File: After applying the transformation logic, export the resulting table to a single CSV file. Ensure the column headers match the required attributes: Invoice, ActivityName, EventTime, UserName, VendorName, InvoiceAmount, InvoiceDueDate, PaymentTerms, PurchaseOrderNumber.
Upload to Process Mining Tool: The final CSV file is now ready to be uploaded to your process mining application.

Configuration

Core Data Entities: The extraction relies on a set of standard entities. The most critical are Vendor invoice headers (for core invoice data), Vendor posted invoices (for posted data), Workflow tracking status (for approval events), and Vendor transactions (for payment information).
Export Format: Use CSV as the target data format. It is lightweight and universally supported by data transformation tools.
Incremental vs. Full Export: For initial analysis, perform a full export for a defined historical period. For ongoing monitoring, configure recurring jobs. The Data Management Framework supports incremental exports, but it requires careful configuration of change tracking on entities.
Date Range Filtering: Always apply a date range filter to limit the dataset size. A common practice is to extract 3-6 months of data for a meaningful analysis without overwhelming the system. Filter on the createdDateTime field of the Vendor invoice headers entity.
Company (Legal Entity) Filter: It is crucial to filter by Company (DataAreaId). Analyzing data from multiple legal entities in one go can be complex unless the process is standardized across them.
Security and Permissions: The user running the export requires a security role with permissions to the Data management workspace and read access to all the specified data entities. Roles like Data management administrator or a custom-configured equivalent are typically needed.

a Sample Query config

-- This is a conceptual SQL query representing the logic to be applied in an external tool (e.g., Power BI, Python, SQL DB)
-- to transform the exported D365 CSV files into a single event log.
-- Replace '[Your Company]' with the legal entity ID you are analyzing.

-- Step 1: Define CTEs (Common Table Expressions) to represent your loaded CSV files.

WITH VendInvoiceInfoTable AS (
    -- Represents the 'Vendor invoice headers' CSV file
    SELECT * FROM [dbo].[VendInvoiceInfoTable_Export]
),
VendInvoiceJour AS (
    -- Represents the 'Vendor posted invoices' CSV file
    SELECT * FROM [dbo].[VendInvoiceJour_Export]
),
VendTrans AS (
    -- Represents the 'Vendor transactions' CSV file
    SELECT * FROM [dbo].[VendTrans_Export]
),
VendSettlement AS (
    -- Represents the 'Vendor settlement transactions' CSV file
    SELECT * FROM [dbo].[VendSettlement_Export]
),
WorkflowTrackingStatusTable AS (
    -- Represents the 'Workflow tracking status' CSV file
    SELECT * FROM [dbo].[WorkflowTrackingStatusTable_Export]
),
LedgerJournalTrans AS (
    -- Represents the 'Payment journal lines' CSV file
    SELECT * FROM [dbo].[LedgerJournalTrans_Export]
),
BankAccountTrans AS (
    -- Represents the 'Bank transactions' CSV file
    SELECT * FROM [dbo].[BankAccountTrans_Export]
),
-- Step 2: Extract each activity as a separate SELECT statement and UNION them together.

FinalEventLog AS (
    -- 1. Invoice Registered
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Registered' AS "ActivityName",
        i.CREATEDDATETIME AS "EventTime",
        i.CREATEDBY AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceInfoTable i
    WHERE i.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 2. Invoice Data Validated (approximated by workflow start)
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Data Validated' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Submission' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 3 & 4. Purchase Order & Goods Receipt Matched (approximated by posting for PO invoices)
    SELECT
        vj.INVOICENUM AS "Invoice",
        'Purchase Order Matched' AS "ActivityName",
        vj.INVOICEDATE AS "EventTime",
        vj.MODIFIEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceJour vj
    WHERE vj.PURCHID IS NOT NULL AND vj.PURCHID <> '' AND vj.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 5. Discrepancy Identified (approximated by a rejection event)
    SELECT
        i.INVOICENUM AS "Invoice",
        'Discrepancy Identified' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Rejection' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 6. Invoice Submitted For Approval
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Submitted For Approval' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Submission' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 7. Invoice Approved
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Approved' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGSTATUS = 'Completed' AND w.TRACKINGTYPE = 'Approval' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 8. Invoice Rejected
    SELECT
        i.INVOICENUM AS "Invoice",
        'Invoice Rejected' AS "ActivityName",
        w.CREATEDDATETIME AS "EventTime",
        w.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w
    JOIN VendInvoiceInfoTable i ON w.CONTEXTRECID = i.RECID
    WHERE w.TRACKINGTYPE = 'Rejection' AND w.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 9. Discrepancy Resolved (approximated by resubmission after rejection)
    SELECT
        i.INVOICENUM AS "Invoice",
        'Discrepancy Resolved' AS "ActivityName",
        w_submit.CREATEDDATETIME AS "EventTime",
        w_submit.USERID AS "UserName",
        i.VENDORNAME AS "VendorName",
        i.INVOICEAMOUNT AS "InvoiceAmount",
        i.DUEDATE AS "InvoiceDueDate",
        i.PAYMENT AS "PaymentTerms",
        i.PURCHID AS "PurchaseOrderNumber"
    FROM WorkflowTrackingStatusTable w_submit
    JOIN VendInvoiceInfoTable i ON w_submit.CONTEXTRECID = i.RECID
    WHERE w_submit.TRACKINGTYPE = 'Submission' AND w_submit.DATACOMPANY = '[Your Company]'
      AND EXISTS (
          SELECT 1 FROM WorkflowTrackingStatusTable w_reject
          WHERE w_reject.CONTEXTRECID = w_submit.CONTEXTRECID
            AND w_reject.TRACKINGTYPE = 'Rejection'
            AND w_reject.CREATEDDATETIME < w_submit.CREATEDDATETIME
      )

    UNION ALL

    -- 10. Invoice Posted
    SELECT
        vj.INVOICENUM AS "Invoice",
        'Invoice Posted' AS "ActivityName",
        vj.CREATEDDATETIME AS "EventTime",
        vj.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceJour vj
    WHERE vj.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 11. Payment Scheduled
    SELECT
        ljt.INVOICENUM AS "Invoice",
        'Payment Scheduled' AS "ActivityName",
        ljt.CREATEDDATETIME AS "EventTime",
        ljt.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM LedgerJournalTrans ljt
    JOIN VendInvoiceJour vj ON ljt.INVOICENUM = vj.INVOICENUM AND ljt.DATACOMPANY = vj.DATACOMPANY
    WHERE ljt.POSTED <> 'Yes' AND ljt.INVOICENUM <> '' AND ljt.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 12. Payment Executed
    SELECT 
        vt.INVOICE AS "Invoice",
        'Payment Executed' AS "ActivityName",
        vt.TRANSDATE AS "EventTime",
        vt.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendTrans vt
    JOIN VendSettlement vs ON vt.RECID = vs.OFFSETRECID
    JOIN VendTrans vt_inv ON vs.TRANSRECID = vt_inv.RECID
    JOIN VendInvoiceJour vj ON vt_inv.VOUCHER = vj.LEDGERVOUCHER AND vt_inv.DATACOMPANY = vj.DATACOMPANY
    WHERE vt.TRANSTYPE = 'Payment' AND vt.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 13. Payment Cleared
    SELECT 
        vt.INVOICE AS "Invoice",
        'Payment Cleared' AS "ActivityName",
        bt.CLEAREDDATE AS "EventTime",
        bt.CREATEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM BankAccountTrans bt
    JOIN VendTrans vt ON bt.VOUCHER = vt.VOUCHER AND bt.DATACOMPANY = vt.DATACOMPANY
    JOIN VendInvoiceJour vj ON vt.INVOICE = vj.INVOICENUM AND vt.DATACOMPANY = vj.DATACOMPANY
    WHERE bt.CLEAREDDATE IS NOT NULL AND vt.TRANSTYPE = 'Payment' AND bt.DATACOMPANY = '[Your Company]'

    UNION ALL

    -- 14. Invoice Cancelled
    SELECT
        vj.INVOICENUM AS "Invoice",
        'Invoice Cancelled' AS "ActivityName",
        vj.MODIFIEDDATETIME AS "EventTime",
        vj.MODIFIEDBY AS "UserName",
        vj.VENDORNAME AS "VendorName",
        vj.INVOICEAMOUNT AS "InvoiceAmount",
        vj.DUEDATE AS "InvoiceDueDate",
        vj.PAYMENT AS "PaymentTerms",
        vj.PURCHID AS "PurchaseOrderNumber"
    FROM VendInvoiceJour vj
    WHERE vj.APPROVALSTATUS = 'Canceled' AND vj.DATACOMPANY = '[Your Company]'
)

SELECT * FROM FinalEventLog;

Data Template: Accounts Payable Invoice Processing

Your Accounts Payable Invoice Processing Data Template

Accounts Payable Invoice Processing Attributes

Accounts Payable Invoice Processing Activities

Extraction Guides

Dynamics 365 Data Management Framework Export

Steps

Configuration

a Sample Query config

Dynamics 365 Azure SQL Database Direct Query

Steps

Configuration

a Sample Query sql

Python Script using Dynamics 365 OData API

Steps

Configuration

a Sample Query python

Microsoft Power Platform Dataflows Extraction

Steps

Configuration

a Sample Query config

Ready to Get Started?

Stop Duplicate Payments. Optimize AP Invoice Processing.