Your Accounts Payable Payment Processing Data Template
Your Accounts Payable Payment Processing Data Template
- Process specific attributes for financial analysis
- Critical activity milestones for payment tracking
- Detailed extraction instructions for Dynamics 365
Accounts Payable Payment Processing Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Activity | The specific task or status change that occurred. | ||
| Description This attribute describes the event or step performed in the process, such as 'Invoice Created', 'Invoice Approved', or 'Payment Posted'. It transforms technical transaction types and workflow status changes into readable business events. In Dynamics 365, these activities are often derived from a combination of table inserts (e.g., a new record in Why it matters It defines the process flow and sequence of events for the process map. Where to get Derived from various transaction tables and workflow history logs Examples Invoice CreatedInvoice ApprovedPayment Generated | |||
| Event Time EventTime | The timestamp when the activity occurred. | ||
| Description This attribute records the exact date and time when a specific activity took place. It is used to sequence events chronologically and calculate durations between steps. For Dynamics 365, this is usually sourced from Why it matters Essential for calculating cycle times, lead times, and identifying bottlenecks. Where to get System fields CreatedDateTime or ModifiedDateTime on transaction tables Examples 2023-10-01T08:30:00Z2023-10-01T14:15:22Z2023-10-05T09:00:00Z | |||
| Invoice Number InvoiceNumber | The unique identifier assigned to the vendor invoice. | ||
| Description The Invoice Number serves as the definitive case identifier for this process view. It uniquely groups all events pertaining to a single vendor invoice, allowing for a comprehensive analysis of its journey from reception to settlement. In Microsoft Dynamics 365, this typically corresponds to the Why it matters It is the fundamental key for linking disjointed AP activities into a single process instance. Where to get Table: VendInvoiceJour, Field: InvoiceId Examples INV-2023-00198223344ACME-OCT-22 | |||
| Last Data Update LastDataUpdate | The timestamp when the data was extracted or refreshed. | ||
| Description Indicates the freshness of the data used for the analysis. It helps users understand if they are looking at real-time data or a snapshot from a previous period. This is typically generated by the ETL (Extract, Transform, Load) process rather than being a field within Dynamics 365 itself. Why it matters Critical for establishing trust in the dashboards and KPIs. Where to get Generated by the extraction script Examples 2023-10-25T12:00:00Z2023-11-01T06:00:00Z | |||
| Source System SourceSystem | The name of the system where the data originated. | ||
| Description Identifies the source software or environment from which the process data was extracted. In this context, it will consistently indicate the Microsoft Dynamics 365 instance. This is particularly useful in multi-system landscapes where data might be blended from ERPs and external scanning solutions. Why it matters Ensures data lineage and traceability in multi-system analyses. Where to get Hardcoded or configured during extraction Examples Dynamics 365 F&OD365 PRODMicrosoft Dynamics | |||
| Company Code CompanyCode | The legal entity or subsidiary identifier. | ||
| Description Represents the legal entity within the organization where the invoice is being processed. In Microsoft Dynamics 365, this is strictly enforced via the This attribute is essential for the 'End to End Lead Time Analysis', enabling comparisons across different subsidiaries or geographical units. Why it matters Allows for comparative analysis across different business units or countries. Where to get Table: VendInvoiceJour, Field: DataAreaId Examples USMFDEMFGBSI | |||
| Department Department | The department responsible for the cost. | ||
| Description The financial dimension indicating the internal department. In Dynamics 365, dimensions are stored dynamically (often in This attribute is used in the 'Vendor Relationship Complexity View' to see which internal departments generate the most AP volume. Why it matters Enables organizational drill-down and accountability analysis. Where to get Table: VendInvoiceJour, Field: DefaultDimension (Requires DimensionAttributeLevelValue view) Examples ITFinanceOperations | |||
| Due Date DueDate | The date by which the invoice should be paid. | ||
| Description The contractual date by which the payment must be settled to avoid penalties. In Dynamics 365, this is stored as It is the primary baseline for the 'On-Time Payment Rate' KPI and helps prioritize work in the 'AP Process Throughput and Volume' view. Why it matters The benchmark for measuring on-time payment performance. Where to get Table: VendInvoiceJour or VendTrans, Field: DueDate Examples 2023-11-302023-12-15 | |||
| Invoice Amount InvoiceAmount | The total monetary value of the invoice. | ||
| Description The total value of the invoice in the transaction currency. In Dynamics 365, this is found in fields like Used in the 'Duplicate Payment Risk Detection' dashboard to correlate amounts with vendor details. Why it matters Critical for analyzing spend volume and financial risk. Where to get Table: VendInvoiceJour, Field: InvoiceAmount Examples 1500.00245.5010000.00 | |||
| Invoice Date InvoiceDate | The document date stated on the invoice. | ||
| Description The date printed on the vendor's invoice. In Dynamics 365, this is the Used in 'End to End Lead Time Analysis' to measure the total lifecycle from the vendor's perspective. Why it matters Defines the start of the aging period for the invoice. Where to get Table: VendInvoiceJour, Field: InvoiceDate Examples 2023-10-012023-10-15 | |||
| Purchase Order Number PurchaseOrderNumber | The reference number of the associated purchase order. | ||
| Description Links the invoice to the original purchasing document. In Dynamics 365, this is the This attribute supports the 'PO Match and Discrepancy Trends' dashboard by distinguishing between PO-backed invoices and non-PO invoices. Why it matters Essential for analyzing the procure-to-pay match rate. Where to get Table: VendInvoiceJour, Field: PurchId Examples PO-000455000342PO-22-998 | |||
| User ID UserId | The identifier of the user who performed the activity. | ||
| Description Identifies the system user responsible for a specific activity, such as approving an invoice or posting a payment. Sourced from Used in the 'Payment Block and Friction Analysis' to see if specific processors trigger more blocks than others. Why it matters Enables analysis of resource behavior and segregation of duties. Where to get System fields CreatedBy/ModifiedBy on transaction/history tables Examples jdoeadminworkflow_sys | |||
| Vendor Account VendorAccount | The unique account number for the vendor. | ||
| Description The unique identifier for the supplier involved in the transaction. In Dynamics 365, this corresponds to the This attribute is central to the 'Vendor Relationship Complexity View', allowing analysis of performance and friction per specific vendor relationship. Why it matters Enables segmentation of process performance by supplier. Where to get Table: VendInvoiceJour, Field: InvoiceAccount or OrderAccount Examples US-101V000452001 | |||
| Vendor Name VendorName | The name of the vendor organization. | ||
| Description The descriptive name of the supplier. In D365, the vendor account acts as a foreign key to the Global Address Book ( Providing human-readable names facilitates the 'Vendor Relationship Complexity View' and makes dashboards accessible to business users. Why it matters Provides context for the Vendor Account number. Where to get Table: DirPartyTable (via VendTable), Field: Name Examples Contoso Office SupplyFabrikam ElectronicsLitware Inc. | |||
| Cash Discount Date CashDiscountDate | The date by which payment must occur to receive a discount. | ||
| Description The deadline for capturing early payment incentives. In Dynamics 365, this is the This attribute powers the 'Cash Discount Capture Performance' dashboard, allowing the organization to quantify missed savings opportunities. Why it matters Directly impacts the financial efficiency KPI of the process. Where to get Table: VendInvoiceJour or VendTrans, Field: CashDiscDate Examples 2023-10-102023-10-20 | |||
| Currency Currency | The currency code of the invoice. | ||
| Description The ISO code for the currency in which the invoice was issued. In Dynamics 365, this is the Important for standardizing amounts in the 'Activity Amount' mapping if multi-currency normalization is required. Why it matters Necessary context for interpreting financial values. Where to get Table: VendInvoiceJour, Field: CurrencyCode Examples USDEURGBP | |||
| Is Payment Blocked IsPaymentBlocked | Flag indicating if the invoice is currently blocked for payment. | ||
| Description A boolean indicator identifying if the invoice is on hold. In Dynamics 365, this is often derived from the This is the core driver for the 'Payment Block and Friction Analysis', highlighting process interruptions. Why it matters Identifies immediate friction points and manual interventions. Where to get Table: VendTrans, Field: Approved (inverted) or specialized Hold fields Examples truefalse | |||
| Payment Method PaymentMethod | The method used to pay the invoice (e.g., Check, Wire, EFT). | ||
| Description Defines how the funds are transferred to the vendor. In Dynamics 365, this is the This attribute is used in the 'Payment Execution Lead Times' dashboard to evaluate the efficiency of different payment batch types. Why it matters Explains variations in the payment execution phase. Where to get Table: VendInvoiceJour (joined to PaymMode info) or VendTrans Examples CHECKACHWIRE | |||
| Payment Terms PaymentTerms | The code representing the agreed payment terms. | ||
| Description The configuration code that dictates due dates and discounts (e.g., Net30). In Dynamics 365, this is the Analyzed alongside 'Cycle Time' to see if process delays are violating agreed terms. Why it matters Provides context for the Due Date calculation. Where to get Table: VendInvoiceJour, Field: PaymTermId Examples Net302%10Net30COD | |||
| Voucher Number VoucherNumber | The ledger voucher number associated with the transaction. | ||
| Description The internal General Ledger identifier for the accounting entry. In Dynamics 365, the While technical, it is useful for the 'Process Path and Compliance Audit' to trace entries back to the GL for reconciliation. Why it matters Key for financial audit and reconciliation. Where to get Table: VendInvoiceJour, Field: LedgerVoucher Examples VOU-10023INV-ACC-992 | |||
Accounts Payable Payment Processing Activities
| Activity | Description | ||
|---|---|---|---|
| Invoice Approved | The workflow instance for the pending invoice reaches a completed or approved status. The invoice is now ready to be posted to the ledger. | ||
| Why it matters Calculates the Average Approval Lead Time. Delays here directly impact the ability to capture early payment discounts. Where to get WorkflowTrackingStatusTable.CreatedDateTime where TrackingStatus is Completed. Alternatively, VendInvoiceInfoTable.RequestStatus equals Approved. Capture Logged when workflow instance completes Event type explicit | |||
| Invoice Created | The initial creation of a pending vendor invoice record in the system. This marks the entry of the invoice into the Dynamics 365 workflow, either manually or via data entity import. | ||
| Why it matters Establishes the start time for the process lead time calculations. It allows organizations to measure how long invoices sit in the system before being processed or posted. Where to get VendInvoiceInfoTable.CreatedDateTime or VendInvoiceInfoTable.RecId creation timestamp. This represents the Pending Vendor Invoice header. Capture Logged when record created in VendInvoiceInfoTable Event type explicit | |||
| Invoice Matched to PO | The system successfully links the invoice line to a Purchase Order or Product Receipt. This activity signifies the validation of the invoice against the procurement order. | ||
| Why it matters Critical for the First-Pass PO Matching Rate KPI. It differentiates between touchless processing and invoices requiring manual intervention. Where to get VendInvoiceInfoLine.PurchId and VendInvoiceInfoTable.MatchStatus. Inferred when MatchStatus changes to Passed. Capture Compare MatchStatus field before/after Event type inferred | |||
| Invoice Posted | The invoice is posted to the General Ledger, creating a liability in the system. The record moves from pending tables to the posted transaction tables. | ||
| Why it matters A major milestone indicating the financial recognition of debt. This activity enables the invoice to be selected for payment. Where to get Creation of record in VendInvoiceJour and VendTrans. The TransDate represents the posting date. Capture Logged when transaction X executed Event type explicit | |||
| Payment Generated | The system generates the payment file (EFT, ISO20022) or prints checks. The payment status on the journal line updates to Sent or Generated. | ||
| Why it matters Supports the Approved-to-Executed Lag Time KPI. It confirms that the instruction to pay has been generated. Where to get LedgerJournalTrans.PaymentStatus changes to Sent/Recieved. Often inferred from updates to the line. Capture Compare PaymentStatus field before/after Event type inferred | |||
| Payment Journal Created | The invoice is selected and added to a Payment Journal line. This indicates the intention to pay and usually initiates the payment review workflow. | ||
| Why it matters Marks the transition from liability to cash disbursement processing. Used to measure Payment Execution Lead Times. Where to get LedgerJournalTrans.CreatedDateTime. The invoice is linked via the MarkedInvoice field or settlement tables. Capture Logged when record created in LedgerJournalTrans Event type explicit | |||
| Payment Posted | The Payment Journal is posted to the General Ledger, settling the invoice and clearing the vendor balance. This completes the financial process. | ||
| Why it matters The final activity for the Average Invoice-to-Payment Cycle Time. It confirms the accounting entries for cash reduction are finalized. Where to get LedgerJournalTrans is posted. Updates VendTrans to show settlement. The actual event is the posting of the journal. Capture Logged when transaction X executed Event type explicit | |||
| Invoice Match Failed | The matching process identifies a discrepancy between the invoice and the PO/Receipt (price or quantity variance). This often halts the process until resolved. | ||
| Why it matters Identifies specific friction points in the matching process. Supports the PO Match and Discrepancy Trends dashboard. Where to get VendInvoiceInfoTable.MatchStatus changes to Failed or Discrepancy. Also visible in VendInvoiceInfoLine matching variances. Capture Compare MatchStatus field before/after Event type inferred | |||
| Invoice Submitted for Approval | The pending invoice is submitted to the workflow engine for review. This marks the transition from data entry/matching to the authorization phase. | ||
| Why it matters Marks the start of the approval cycle time. Essential for analyzing the efficiency of internal hierarchies. Where to get WorkflowTrackingStatusTable.CreatedDateTime where ContextTableId equals VendInvoiceInfoTable ID and Status is Submitted. Capture Logged when workflow instance initiated Event type explicit | |||
| Invoice Updated | Records changes made to the invoice header or lines prior to posting. Frequent updates may indicate data extraction issues or manual corrections required during validation. | ||
| Why it matters High frequency of updates suggests rework loops or poor data quality from the source (e.g., OCR errors). This supports the Rework and Data Accuracy Monitor. Where to get Database log (SysDatabaseLog) on VendInvoiceInfoTable if enabled, or inferred from ModifiedDateTime changes if polling frequency is high. Capture Compare ModifiedDateTime on subsequent extracts Event type inferred | |||
| Payment Block Applied | A hold is placed on the vendor transaction preventing it from being selected in a payment proposal. This is often done manually for disputes. | ||
| Why it matters Supports Payment Block and Friction Analysis. Reveals manual interventions that delay cash outflow. Where to get VendTrans.Approved flag is set to No, or specific OnHold status fields are populated. Requires tracking updates to VendTrans. Capture Compare status field before/after Event type inferred | |||
| Payment Journal Approved | The Payment Journal workflow is approved, authorizing the generation of payments. This is the final check before funds are prepared for transfer. | ||
| Why it matters Separates the administrative preparation of payments from the authorization bottleneck. Where to get WorkflowTrackingStatusTable linking to the LedgerJournalTable (Header) ID. Status is Completed. Capture Logged when workflow instance completes Event type explicit | |||
Extraction Guides
Steps
Access Data Management Workspace: Log in to your Microsoft Dynamics 365 Finance environment. Navigate to Workspaces and select Data Management. This is the central hub for configuring data export projects.
Create Export Project: Click on the Export tile to create a new data project. Name the project clearly, for example, ProcessMining_AP_Export. In the Target data format field, select the destination format (e.g., Azure SQL DB for BYOD or CSV for file-based export).
Add Data Entities: Add the following standard data entities to the project one by one: VendorInvoiceHeaderEntity (for pending invoices), VendorInvoiceLineEntity (for invoice lines), VendorInvoiceJournalHeaderEntity (for posted invoices), VendorPaymentJournalLineEntity (for payments), and WorkflowHistoryEntity (for approval logs). If WorkflowHistoryEntity is not available by default, you may need to enable a custom entity or specific system entity exposed for export.
Configure Entity Filters: For each entity, click the Filter icon. Apply filters to restrict data to the relevant CompanyInfo (DataAreaId) and set a date range on the CreatedDateTime or InvoiceDate fields to extract data only for the desired analysis period (e.g., last 12 months).
Set Up Recurring Export: To ensure the event log stays current, create a recurring data job. Define the recurrence frequency (e.g., daily or hourly) and enable Incremental push where supported. This reduces system load by only exporting changed records.
Execute Initial Export: Run the project manually for the first time by clicking Export now. Monitor the Execution summary to ensure all records are exported successfully without errors.
Transform Data: Once the data is exported to your destination (Azure SQL or files), use the SQL script provided in the Query section to join these tables. This transformation logic converts the disparate entity records into a single, chronological event log.
Map Attributes: Ensure the resulting dataset maps the InvoiceNumber to Case ID, EventTime to Timestamp, and Activity to Activity Name as per the process mining tool requirements.
Validate and Upload: Run the validation checks listed below to confirm data accuracy. Once verified, export the final result as a CSV or Parquet file and upload it to ProcessMind.
Configuration
- Entity Selection: Use VendorInvoiceHeaderEntity and VendorInvoiceLineEntity for process steps prior to posting. Use VendorInvoiceJournalHeaderEntity for the legal, posted document. Use VendorPaymentJournalLineEntity for payment tracking.
- Incremental Push: Enable this setting in the Data Management project to export only new or modified records after the initial full load. This is critical for performance.
- Date Ranges: Filter by InvoiceDate >= [Start Date]. Avoid unbounded exports which can time out.
- Company Filter: D365 is a multi-entity system. Always filter by DataAreaId to avoid mixing data from different legal entities unless cross-company analysis is intended.
- Workflow History: Standard entities for workflow history can be heavy. Ensure you only export history related to VendInvoice types to keep volume manageable.
a Sample Query config
/*
SQL Transformation Script for D365 Finance AP Process
Assumes data is loaded into Staging tables in a SQL environment (BYOD/Data Lake)
*/
SELECT
I.InvoiceNumber AS [InvoiceNumber],
'Invoice Created' AS [Activity],
I.CreatedDateTime AS [EventTime],
I.DataAreaId AS [CompanyCode],
I.InvoiceAccount AS [VendorAccount],
I.InvoiceAmount AS [InvoiceAmount],
I.CurrencyCode AS [Currency],
'D365 FO' AS [SourceSystem],
GETDATE() AS [LastDataUpdate]
FROM Staging_VendorInvoiceHeaderEntity I
UNION ALL
/* Capture updates to invoice headers */
SELECT
I.InvoiceNumber,
'Invoice Updated',
I.ModifiedDateTime,
I.DataAreaId,
I.InvoiceAccount,
I.InvoiceAmount,
I.CurrencyCode,
'D365 FO',
GETDATE()
FROM Staging_VendorInvoiceHeaderEntity I
WHERE I.ModifiedDateTime > I.CreatedDateTime
UNION ALL
/* Invoice Matching Activities */
SELECT
I.InvoiceNumber,
'Invoice Matched to PO',
I.ModifiedDateTime,
I.DataAreaId,
I.InvoiceAccount,
I.InvoiceAmount,
I.CurrencyCode,
'D365 FO',
GETDATE()
FROM Staging_VendorInvoiceHeaderEntity I
WHERE I.MatchStatus = 'Matched' -- Adjust value based on system config
UNION ALL
SELECT
I.InvoiceNumber,
'Invoice Match Failed',
I.ModifiedDateTime,
I.DataAreaId,
I.InvoiceAccount,
I.InvoiceAmount,
I.CurrencyCode,
'D365 FO',
GETDATE()
FROM Staging_VendorInvoiceHeaderEntity I
WHERE I.MatchStatus = 'Failed'
UNION ALL
/* Workflow Activities */
SELECT
RelatedContext AS InvoiceNumber,
CASE
WHEN Status = 'Submitted' THEN 'Invoice Submitted for Approval'
WHEN Status = 'Approved' THEN 'Invoice Approved'
ELSE 'Workflow Activity'
END AS [Activity],
CreatedDateTime AS [EventTime],
DataAreaId,
NULL AS [VendorAccount],
NULL AS [InvoiceAmount],
NULL AS [Currency],
'D365 FO',
GETDATE()
FROM Staging_WorkflowHistoryEntity
WHERE ContextTableId = 12345 -- Replace with TableId for VendInvoiceInfoTable
AND Status IN ('Submitted', 'Approved')
UNION ALL
/* Invoice Posted */
SELECT
J.InvoiceNumber,
'Invoice Posted',
J.PostedDateTime,
J.DataAreaId,
J.InvoiceAccount,
J.InvoiceAmount,
J.CurrencyCode,
'D365 FO',
GETDATE()
FROM Staging_VendorInvoiceJournalHeaderEntity J
UNION ALL
/* Payment Block */
SELECT
I.InvoiceNumber,
'Payment Block Applied',
I.ModifiedDateTime,
I.DataAreaId,
I.InvoiceAccount,
I.InvoiceAmount,
I.CurrencyCode,
'D365 FO',
GETDATE()
FROM Staging_VendorInvoiceJournalHeaderEntity I
WHERE I.OnHold = 'Yes'
UNION ALL
/* Payment Activities */
SELECT
J.InvoiceId AS [InvoiceNumber],
'Payment Journal Created' AS [Activity],
P.CreatedDateTime AS [EventTime],
P.DataAreaId,
P.AccountDisplayValue AS [VendorAccount],
P.DebitAmount AS [InvoiceAmount],
P.CurrencyCode,
'D365 FO',
GETDATE()
FROM Staging_VendorPaymentJournalLineEntity P
JOIN Staging_VendorInvoiceJournalHeaderEntity J ON P.InvoiceId = J.InvoiceNumber AND P.DataAreaId = J.DataAreaId
UNION ALL
SELECT
J.InvoiceId AS [InvoiceNumber],
'Payment Journal Approved' AS [Activity],
P.ModifiedDateTime AS [EventTime],
P.DataAreaId,
P.AccountDisplayValue,
P.DebitAmount,
P.CurrencyCode,
'D365 FO',
GETDATE()
FROM Staging_VendorPaymentJournalLineEntity P
JOIN Staging_VendorInvoiceJournalHeaderEntity J ON P.InvoiceId = J.InvoiceNumber AND P.DataAreaId = J.DataAreaId
WHERE P.PaymentStatus = 'Approved'
UNION ALL
SELECT
J.InvoiceId AS [InvoiceNumber],
'Payment Generated' AS [Activity],
P.ModifiedDateTime AS [EventTime],
P.DataAreaId,
P.AccountDisplayValue,
P.DebitAmount,
P.CurrencyCode,
'D365 FO',
GETDATE()
FROM Staging_VendorPaymentJournalLineEntity P
JOIN Staging_VendorInvoiceJournalHeaderEntity J ON P.InvoiceId = J.InvoiceNumber AND P.DataAreaId = J.DataAreaId
WHERE P.PaymentStatus = 'Sent'
UNION ALL
SELECT
J.InvoiceId AS [InvoiceNumber],
'Payment Posted' AS [Activity],
P.PostedDate AS [EventTime],
P.DataAreaId,
P.AccountDisplayValue,
P.DebitAmount,
P.CurrencyCode,
'D365 FO',
GETDATE()
FROM Staging_VendorPaymentJournalLineEntity P
JOIN Staging_VendorInvoiceJournalHeaderEntity J ON P.InvoiceId = J.InvoiceNumber AND P.DataAreaId = J.DataAreaId
WHERE P.IsPosted = 'Yes'Steps
Verify BYOD Connectivity: Ensure you have SQL Server Management Studio (SSMS) or a similar tool installed and can connect to the Azure SQL Database configured as the Bring Your Own Database (BYOD) target for your Dynamics 365 Finance & Operations environment.
Confirm Entity Export: Navigate to the Data Management workspace in Dynamics 365. Verify that the following entities (or their underlying tables) are configured for export to the BYOD database:
VendInvoiceInfoTable(Pending Invoices),VendInvoiceInfoLine(Pending Lines),VendInvoiceJour(Posted Invoices),VendTrans(Vendor Transactions),LedgerJournalTrans(Journal Lines),LedgerJournalTable(Journal Headers), andWorkflowTrackingStatusTable(Workflow History).Configure Export Job: If these tables are not currently exporting, create a new export job. Set the Target Data Format to your BYOD SQL database. Choose Incremental Push to keep the data synchronized without full re-exports. Run the job to populate the tables.
Prepare the SQL Environment: Open SSMS and connect to the BYOD Azure SQL database. Open a new query window.
Set Parameters: In the script provided below, locate the variable declaration section at the top. Update the
@StartDateand@EndDatevariables to match the period you wish to analyze. If you need to filter by a specific legal entity, update theDATAAREAIDfilter conditions.Execute the Script: Run the full T-SQL script. This script uses
UNION ALLto combine data from multiple tables into a single standardized event log format.Validate Data: Check the results for null values in the
InvoiceNumberorEventTimecolumns. Ensure that both posted invoices (fromVendInvoiceJour) and pending invoices (fromVendInvoiceInfoTable) are appearing.Export the Result: Right-click the results grid in SSMS and select Save Results As.... Save the file as a CSV (Comma Delimited) file.
Format for Upload: Open the CSV in Excel or a text editor to ensure date formats are ISO 8601 compliant (YYYY-MM-DD HH:MM:SS) if required by ProcessMind. No further transformation should be needed if the script ran successfully.
Upload to ProcessMind: Import the CSV file into ProcessMind, mapping the columns
InvoiceNumberto Case ID,Activityto Activity Name, andEventTimeto Timestamp.
Configuration
- Export Strategy: Use Incremental Push for high-volume tables like
LedgerJournalTransandVendTransto minimize BYOD load. Only use Full Push if data inconsistencies are suspected. - Timezone Handling: Dynamics 365 stores data in UTC. The script assumes UTC. If your analysis requires local time, apply a
DATEADDadjustment in the script or during the ProcessMind import. - Company Filtering: The
DataAreaIdcolumn represents the Legal Entity. The script extracts data for all entities by default. AddWHERE DataAreaId = 'usmf'(example) to filter for a specific subsidiary. - Workflow History: The table
WorkflowTrackingStatusTableis critical for approval timestamps. Ensure this table is included in your BYOD export configuration, as it is often omitted by default. - Data Retention: Be aware of any cleanup routines in D365 that might delete completed workflow history or posted journal lines, as this will limit the historical depth of the process mining analysis.
a Sample Query sql
/* T-SQL Extraction Script for D365 AP Payment Processing */
/* Tables required: VendInvoiceInfoTable, VendInvoiceInfoLine, VendInvoiceJour, VendTrans, LedgerJournalTrans, LedgerJournalTable, WorkflowTrackingStatusTable */
DECLARE @StartDate DATETIME = '2023-01-01 00:00:00';
DECLARE @EndDate DATETIME = GETDATE();
WITH RawData AS (
/* 1. Invoice Created: Pending Invoice Header Creation */
SELECT
T1.Num AS InvoiceNumber,
'Invoice Created' AS Activity,
T1.CreatedDateTime AS EventTime,
'D365 FO' AS SourceSystem,
GETDATE() AS LastDataUpdate,
T1.InvoiceAccount AS VendorAccount,
T1.DataAreaId AS CompanyCode,
CAST(T1.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
T1.DueDate AS DueDate,
T1.PurchId AS PurchaseOrderNumber,
T1.CreatedBy AS UserId,
T1.VendorName AS VendorName,
T1.DocumentDate AS InvoiceDate,
'Unknown' AS Department
FROM dbo.VendInvoiceInfoTable T1
WHERE T1.CreatedDateTime BETWEEN @StartDate AND @EndDate
UNION ALL
/* 2. Invoice Updated: Modifications to Pending Invoice */
SELECT
T1.Num AS InvoiceNumber,
'Invoice Updated' AS Activity,
T1.ModifiedDateTime AS EventTime,
'D365 FO' AS SourceSystem,
GETDATE() AS LastDataUpdate,
T1.InvoiceAccount AS VendorAccount,
T1.DataAreaId AS CompanyCode,
CAST(T1.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
T1.DueDate AS DueDate,
T1.PurchId AS PurchaseOrderNumber,
T1.ModifiedBy AS UserId,
T1.VendorName AS VendorName,
T1.DocumentDate AS InvoiceDate,
'Unknown' AS Department
FROM dbo.VendInvoiceInfoTable T1
WHERE T1.ModifiedDateTime BETWEEN @StartDate AND @EndDate
AND T1.ModifiedDateTime > T1.CreatedDateTime
UNION ALL
/* 3. Invoice Matched to PO: Line Matching Success */
SELECT
H.Num AS InvoiceNumber,
'Invoice Matched to PO' AS Activity,
L.ModifiedDateTime AS EventTime,
'D365 FO' AS SourceSystem,
GETDATE() AS LastDataUpdate,
H.InvoiceAccount AS VendorAccount,
H.DataAreaId AS CompanyCode,
CAST(H.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
H.DueDate AS DueDate,
H.PurchId AS PurchaseOrderNumber,
L.ModifiedBy AS UserId,
H.VendorName AS VendorName,
H.DocumentDate AS InvoiceDate,
'Unknown' AS Department
FROM dbo.VendInvoiceInfoLine L
JOIN dbo.VendInvoiceInfoTable H ON L.TableRefId = H.TableRefId AND L.DataAreaId = H.DataAreaId
WHERE L.MatchStatus = 1 /* 1 usually denotes Matched/Passed in enum */
AND L.ModifiedDateTime BETWEEN @StartDate AND @EndDate
UNION ALL
/* 4. Invoice Match Failed: Line Matching Discrepancy */
SELECT
H.Num AS InvoiceNumber,
'Invoice Match Failed' AS Activity,
L.ModifiedDateTime AS EventTime,
'D365 FO' AS SourceSystem,
GETDATE() AS LastDataUpdate,
H.InvoiceAccount AS VendorAccount,
H.DataAreaId AS CompanyCode,
CAST(H.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
H.DueDate AS DueDate,
H.PurchId AS PurchaseOrderNumber,
L.ModifiedBy AS UserId,
H.VendorName AS VendorName,
H.DocumentDate AS InvoiceDate,
'Unknown' AS Department
FROM dbo.VendInvoiceInfoLine L
JOIN dbo.VendInvoiceInfoTable H ON L.TableRefId = H.TableRefId AND L.DataAreaId = H.DataAreaId
WHERE L.MatchStatus = 2 /* 2 usually denotes Failed in enum */
AND L.ModifiedDateTime BETWEEN @StartDate AND @EndDate
UNION ALL
/* 5. Invoice Submitted for Approval: Workflow Submission */
SELECT
T1.Num AS InvoiceNumber,
'Invoice Submitted for Approval' AS Activity,
W.CreatedDateTime AS EventTime,
'D365 FO' AS SourceSystem,
GETDATE() AS LastDataUpdate,
T1.InvoiceAccount AS VendorAccount,
T1.DataAreaId AS CompanyCode,
CAST(T1.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
T1.DueDate AS DueDate,
T1.PurchId AS PurchaseOrderNumber,
W.User AS UserId,
T1.VendorName AS VendorName,
T1.DocumentDate AS InvoiceDate,
'Unknown' AS Department
FROM dbo.WorkflowTrackingStatusTable W
JOIN dbo.VendInvoiceInfoTable T1 ON W.ContextRecId = T1.RecId
WHERE W.TrackingStatus = 1 /* Submitted */
AND W.ContextTableId = 1425 /* TableId for VendInvoiceInfoTable, adjust if different in version */
AND W.CreatedDateTime BETWEEN @StartDate AND @EndDate
UNION ALL
/* 6. Invoice Approved: Workflow Completion */
SELECT
T1.Num AS InvoiceNumber,
'Invoice Approved' AS Activity,
W.CreatedDateTime AS EventTime,
'D365 FO' AS SourceSystem,
GETDATE() AS LastDataUpdate,
T1.InvoiceAccount AS VendorAccount,
T1.DataAreaId AS CompanyCode,
CAST(T1.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
T1.DueDate AS DueDate,
T1.PurchId AS PurchaseOrderNumber,
W.User AS UserId,
T1.VendorName AS VendorName,
T1.DocumentDate AS InvoiceDate,
'Unknown' AS Department
FROM dbo.WorkflowTrackingStatusTable W
JOIN dbo.VendInvoiceInfoTable T1 ON W.ContextRecId = T1.RecId
WHERE W.TrackingStatus = 2 /* Completed/Approved */
AND W.ContextTableId = 1425
AND W.CreatedDateTime BETWEEN @StartDate AND @EndDate
UNION ALL
/* 7. Invoice Posted: Creation of VendInvoiceJour */
SELECT
J.InvoiceId AS InvoiceNumber,
'Invoice Posted' AS Activity,
J.CreatedDateTime AS EventTime,
'D365 FO' AS SourceSystem,
GETDATE() AS LastDataUpdate,
J.InvoiceAccount AS VendorAccount,
J.DataAreaId AS CompanyCode,
CAST(J.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
J.DueDate AS DueDate,
J.PurchId AS PurchaseOrderNumber,
J.CreatedBy AS UserId,
J.InvoicingName AS VendorName,
J.InvoiceDate AS InvoiceDate,
'Unknown' AS Department
FROM dbo.VendInvoiceJour J
WHERE J.CreatedDateTime BETWEEN @StartDate AND @EndDate
UNION ALL
/* 8. Payment Block Applied: Updated on VendTrans */
SELECT
J.InvoiceId AS InvoiceNumber,
'Payment Block Applied' AS Activity,
VT.ModifiedDateTime AS EventTime,
'D365 FO' AS SourceSystem,
GETDATE() AS LastDataUpdate,
J.InvoiceAccount AS VendorAccount,
J.DataAreaId AS CompanyCode,
CAST(J.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
J.DueDate AS DueDate,
J.PurchId AS PurchaseOrderNumber,
VT.ModifiedBy AS UserId,
J.InvoicingName AS VendorName,
J.InvoiceDate AS InvoiceDate,
'Unknown' AS Department
FROM dbo.VendTrans VT
JOIN dbo.VendInvoiceJour J ON VT.Invoice = J.InvoiceId AND VT.AccountNum = J.InvoiceAccount AND VT.DataAreaId = J.DataAreaId
WHERE VT.Approved = 0 /* 0 indicates Not Approved/Blocked */
AND VT.ModifiedDateTime BETWEEN @StartDate AND @EndDate
UNION ALL
/* 9. Payment Journal Created: Line added to Journal */
SELECT
LJT.Invoice AS InvoiceNumber,
'Payment Journal Created' AS Activity,
LJT.CreatedDateTime AS EventTime,
'D365 FO' AS SourceSystem,
GETDATE() AS LastDataUpdate,
LJT.AccountNum AS VendorAccount,
LJT.DataAreaId AS CompanyCode,
CAST(LJT.AmountCurCredit AS DECIMAL(18,2)) AS InvoiceAmount,
NULL AS DueDate,
NULL AS PurchaseOrderNumber,
LJT.CreatedBy AS UserId,
NULL AS VendorName,
LJT.TransDate AS InvoiceDate,
'Unknown' AS Department
FROM dbo.LedgerJournalTrans LJT
WHERE LJT.AccountType = 2 /* Vendor */
AND LJT.Invoice IS NOT NULL AND LJT.Invoice <> ''
AND LJT.CreatedDateTime BETWEEN @StartDate AND @EndDate
UNION ALL
/* 10. Payment Journal Approved: Workflow on Journal Header */
SELECT
LJT.Invoice AS InvoiceNumber,
'Payment Journal Approved' AS Activity,
LJH.ModifiedDateTime AS EventTime,
'D365 FO' AS SourceSystem,
GETDATE() AS LastDataUpdate,
LJT.AccountNum AS VendorAccount,
LJT.DataAreaId AS CompanyCode,
CAST(LJT.AmountCurCredit AS DECIMAL(18,2)) AS InvoiceAmount,
NULL AS DueDate,
NULL AS PurchaseOrderNumber,
LJH.ModifiedBy AS UserId,
NULL AS VendorName,
LJT.TransDate AS InvoiceDate,
'Unknown' AS Department
FROM dbo.LedgerJournalTable LJH
JOIN dbo.LedgerJournalTrans LJT ON LJH.JournalNum = LJT.JournalNum AND LJH.DataAreaId = LJT.DataAreaId
WHERE LJH.WorkflowApprovalStatus = 2 /* Approved */
AND LJT.AccountType = 2
AND LJT.Invoice IS NOT NULL AND LJT.Invoice <> ''
AND LJH.ModifiedDateTime BETWEEN @StartDate AND @EndDate
UNION ALL
/* 11. Payment Generated: Payment Status Changed to Sent */
SELECT
LJT.Invoice AS InvoiceNumber,
'Payment Generated' AS Activity,
LJT.ModifiedDateTime AS EventTime,
'D365 FO' AS SourceSystem,
GETDATE() AS LastDataUpdate,
LJT.AccountNum AS VendorAccount,
LJT.DataAreaId AS CompanyCode,
CAST(LJT.AmountCurCredit AS DECIMAL(18,2)) AS InvoiceAmount,
NULL AS DueDate,
NULL AS PurchaseOrderNumber,
LJT.ModifiedBy AS UserId,
NULL AS VendorName,
LJT.TransDate AS InvoiceDate,
'Unknown' AS Department
FROM dbo.LedgerJournalTrans LJT
WHERE LJT.PaymentStatus = 2 /* Sent/Generated */
AND LJT.AccountType = 2
AND LJT.Invoice IS NOT NULL AND LJT.Invoice <> ''
AND LJT.ModifiedDateTime BETWEEN @StartDate AND @EndDate
UNION ALL
/* 12. Payment Posted: Journal Line Posted */
SELECT
LJT.Invoice AS InvoiceNumber,
'Payment Posted' AS Activity,
LJT.ModifiedDateTime AS EventTime,
'D365 FO' AS SourceSystem,
GETDATE() AS LastDataUpdate,
LJT.AccountNum AS VendorAccount,
LJT.DataAreaId AS CompanyCode,
CAST(LJT.AmountCurCredit AS DECIMAL(18,2)) AS InvoiceAmount,
NULL AS DueDate,
NULL AS PurchaseOrderNumber,
LJT.ModifiedBy AS UserId,
NULL AS VendorName,
LJT.TransDate AS InvoiceDate,
'Unknown' AS Department
FROM dbo.LedgerJournalTrans LJT
WHERE LJT.Posted = 1 /* Posted */
AND LJT.AccountType = 2
AND LJT.Invoice IS NOT NULL AND LJT.Invoice <> ''
AND LJT.ModifiedDateTime BETWEEN @StartDate AND @EndDate
)
SELECT *
FROM RawData
WHERE InvoiceNumber IS NOT NULL AND InvoiceNumber <> ''
ORDER BY InvoiceNumber, EventTime;