> Continuous Process Improvement > Order to Cash Billing & Invoicing > Microsoft Dynamics 365 > Data Template

Your Order to Cash - Billing & Invoicing Data Template

Microsoft Dynamics 365

Order to Cash - Billing & Invoicing Attributes (18) Order to Cash - Billing & Invoicing Activities (12) Extraction Guides (2)

Your Order to Cash - Billing & Invoicing Data Template

This template provides a clear roadmap for collecting the essential data points needed to analyze your Order to Cash - Billing & Invoicing process. You will find recommended attributes to include in your event log, key activities to track, and practical guidance on extracting this information. It is designed to help you quickly set up your data for insightful process mining.

Recommended attributes to collect
Key activities to track
Extraction guidance for Microsoft Dynamics 365

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

Download Template

Order to Cash - Billing & Invoicing Attributes

These are the recommended data fields to include in your event log for comprehensive Order to Cash - Billing & Invoicing analysis.

3 Required 6 Recommended 9 Optional

	Name	Description
	Invoice Number InvoiceNumber	The unique identifier for each customer invoice, serving as the primary case identifier for the billing process.
Description The Invoice Number is the central key that groups all activities related to a single billing document. It allows for a complete, end-to-end analysis of the invoice lifecycle, from generation and approval to customer payment and final closure. By using this as the Case ID, every event log entry corresponds to a specific invoice, enabling detailed variant analysis, cycle time calculation, and performance monitoring for each billing transaction. Why it matters This attribute is essential for tracking the entire lifecycle of an invoice, enabling the analysis of process efficiency, bottlenecks, and deviations for each individual billing document. Where to get This is typically found in the Sales and marketing module, within tables like CustInvoiceJour (field InvoiceId) or exposed through data entities like SalesInvoiceHeaderV2. Examples CIV-001254INV-2023-9876US-004321
	Activity Name ActivityName	The name of the business activity or event that occurred at a specific point in the invoice lifecycle.
Description This attribute describes a specific step or milestone within the billing process, such as 'Invoice Generated', 'Invoice Approved', or 'Customer Payment Received'. The sequence of these activities forms the process flow. Analyzing the activities is fundamental to process mining, as it allows for the discovery of process models, identification of variants, and measurement of performance between different steps. Why it matters It defines the steps in the process, allowing for the visualization of process maps, analysis of process variants, and identification of bottlenecks or deviations from the standard procedure. Where to get This attribute is typically derived from multiple sources, such as status change fields (e.g., DocumentState), specific date fields being populated, or workflow history logs within Microsoft Dynamics 365. Examples Invoice GeneratedInvoice ApprovedCustomer Payment ReceivedCredit Note Issued
	Event Time EventTime	The precise timestamp indicating when a specific activity or event occurred.
Description Event Time, also known as the timestamp, records the exact date and time for each activity in the process. This data is critical for all time-based process mining analyses. It is used to calculate cycle times between activities, measure the total duration of a case, and understand process performance over time. Accurate and reliable timestamps are the foundation for KPIs like Days Sales Outstanding and Invoice Generation Time. Why it matters This is the foundational attribute for all performance and time-related analysis, enabling the calculation of cycle times, durations, and throughput to measure process efficiency. Where to get Timestamps are sourced from various date and time fields across different tables in Dynamics 365, such as CreatedDateTime on the CustInvoiceJour table for invoice generation, or specific workflow history timestamps. Examples 2023-04-15T10:22:15Z2023-05-01T14:05:00Z2023-05-10T09:00:30Z
	Customer Name CustomerName	The name of the customer to whom the invoice was issued.
Description The customer name provides a human-readable identifier for the business or individual being billed. In process mining analysis, this allows for segmenting the billing process by customer. This can reveal which customers consistently pay on time, which ones are frequently late, or if certain customer groups experience unique process variations or delays. It is key for dashboards related to customer payment behavior and Days Sales Outstanding analysis. Why it matters This allows for process analysis to be segmented by customer, helping to identify customer-specific behaviors, payment patterns, or process issues. Where to get This information is retrieved by joining the invoice data with the customer master data table, typically CustTable, based on the customer account number. Examples Contoso Ltd.Fabrikam, Inc.Northwind Traders
	Department Department	The business department associated with the user or the activity.
Description This attribute assigns an activity or user to a specific organizational department, such as 'Accounts Receivable' or 'Sales'. It allows for a higher-level view of process performance than the individual user level. Analyzing by department helps in understanding cross-functional handoffs, comparing efficiency between teams, and identifying department-level bottlenecks, as required by the 'Billing Department Activity Load' dashboard. Why it matters Allows for performance analysis at an organizational level, comparing efficiency across different teams and understanding how inter-departmental handoffs impact the process. Where to get This is typically not a direct field on the invoice transaction. It is derived by joining the UserResponsible ID with the employee or user directory which contains department information. Examples Accounts ReceivableFinanceBilling Operations
	Invoice Amount InvoiceAmount	The total monetary value of the invoice.
Description This attribute represents the total amount due on the invoice. It is a critical metric for financial analysis, allowing for the segmentation of invoices by value. For example, analysts can focus on high-value invoices to understand their payment behaviors or identify if larger invoices face longer approval times. It's also fundamental for calculating the total value of outstanding invoices and for DSO calculations. Why it matters It enables financial impact analysis, allowing you to filter and segment processes by monetary value to prioritize high-value invoices and understand their specific process behaviors. Where to get Found in the CustInvoiceJour table, typically in a field like InvoiceAmount. Also available in related transaction tables. Examples 5430.5012500.00750.25
	Invoice Status InvoiceStatus	The current status of the invoice in its lifecycle (e.g., Open, Paid, Canceled).
Description This attribute provides a snapshot of where the invoice currently stands. It indicates whether an invoice is still awaiting payment, has been fully paid, is overdue, or has been canceled via a credit note. This is useful for filtering cases to analyze only open invoices or to confirm the final outcome of a closed invoice. It helps in understanding the current workload and the financial state of receivables. Why it matters Provides a quick snapshot of the invoice's current state, which is useful for filtering cases and understanding the outcomes of different process variants. Where to get This can be derived from the payment status of the invoice, often by checking if the balance on the related CustTrans record is zero. There isn't always a single 'status' field. Examples OpenPaidOverdueCanceled
	Payment Due Date PaymentDueDate	The date by which the customer is expected to pay the invoice.
Description The Payment Due Date is a critical date field calculated based on the invoice date and the customer's payment terms. It serves as the baseline for measuring payment timeliness. This attribute is essential for the 'Payment Terms Adherence Analysis' dashboard and for calculating KPIs like 'On-Time Payment Rate' and 'Days Sales Outstanding'. Comparing this date with the actual payment date reveals customer payment behavior and the effectiveness of the collections process. Why it matters This is the benchmark for measuring payment performance. It's essential for calculating on-time payment rates and analyzing adherence to customer payment terms. Where to get This is typically found in the CustInvoiceJour table in a field like DueDate. Examples 2023-05-152023-06-302023-07-01
	User Responsible UserResponsible	The user or employee responsible for executing a specific activity.
Description This attribute identifies the individual who performed a particular task in the billing process, such as approving an invoice or applying cash. It is crucial for workload analysis, performance comparison, and identifying training needs. For example, the 'Billing Department Activity Load' dashboard relies on this attribute to see if work is distributed evenly or if certain users are bottlenecks. It also helps in understanding resource allocation and efficiency. Why it matters It enables resource-level analysis, helping to identify bottlenecks, measure individual or team performance, and analyze workload distribution across the billing department. Where to get This can be sourced from workflow history logs (e.g., WorkflowTrackingStatusTable) or ownership fields like Owner or ModifiedBy on relevant tables. Examples John SmithAlicia BakerSystem Administrator
	Currency Currency	The currency code for the invoice amount (e.g., USD, EUR).
Description This attribute specifies the currency in which the invoice amount is denominated. It is essential for multi-national organizations that deal with different currencies. Without this context, aggregating or comparing invoice amounts would be meaningless. It allows for proper currency conversion and accurate financial reporting and analysis across different regions. Why it matters It provides essential context for any financial metrics like Invoice Amount, preventing incorrect aggregations and enabling accurate analysis in multi-currency environments. Where to get Found in the CustInvoiceJour table, typically in a field like CurrencyCode. Examples USDEURGBP
	Days Sales Outstanding DaysSalesOutstanding	The number of days between invoice generation and receiving customer payment.
Description Days Sales Outstanding (DSO) is a key financial metric that measures the average number of days it takes for a company to collect payment after a sale has been made. This calculated attribute computes this duration for each individual invoice. It is the primary metric for the 'Days Sales Outstanding Trend Analysis' dashboard and the 'DSO' KPI. Analyzing DSO helps in managing cash flow and evaluating the efficiency of the credit and collections function. Why it matters This is a critical KPI for measuring the health of a company's cash flow and the efficiency of its collections process. It quantifies how quickly revenue is converted to cash. Where to get This attribute is not in the source system. It is calculated as the duration between the 'Invoice Generated' activity and the 'Customer Payment Received' activity. Examples 284592
	Is On-Time Payment IsOnTimePayment	A flag indicating if the customer payment was received on or before the due date.
Description This calculated boolean attribute compares the 'Customer Payment Received' timestamp with the 'Payment Due Date'. It flags each invoice as either paid on time or late. This is the core component for calculating the 'On-Time Payment Rate' KPI and is used heavily in the 'Payment Terms Adherence Analysis' dashboard. It provides a clear, binary outcome for payment performance, simplifying analysis and reporting. Why it matters Directly measures customer adherence to payment terms and is the basis for the On-Time Payment Rate KPI, simplifying the analysis of collection effectiveness. Where to get This attribute is not in the source system. It is calculated during data transformation by comparing the payment date to the PaymentDueDate. Examples truefalse
	Is Rework IsRework	A flag indicating if the invoice process involved rework activities, such as multiple approvals.
Description This is a calculated boolean attribute that flags invoices that have undergone rework. Rework can be defined as having multiple 'Invoice Approved' events or reversals of payments. This attribute is specifically designed to support the 'Invoice Rework Analysis' dashboard and the 'Invoice Rework Rate' KPI. By isolating these cases, analysts can investigate the root causes of rework, which often point to process inefficiencies, data quality issues, or training gaps. Why it matters This flag directly quantifies process inefficiency by identifying cases with repetitive or corrective actions, helping to pinpoint root causes of waste and delay. Where to get This attribute is not available in the source system. It is calculated in the data transformation layer by checking for repeated activities or specific rework patterns within a case. Examples truefalse
	Last Data Update LastDataUpdate	The timestamp indicating the last time the data for this event was refreshed from the source system.
Description This attribute records when the data was last extracted or updated from Microsoft Dynamics 365. It is a metadata field that is crucial for understanding the freshness of the data being analyzed. This helps analysts and business users know if they are looking at the most current information and is vital for managing data refresh schedules and ensuring the reliability of the analysis. Why it matters It ensures users understand the recency of the data, which is critical for data governance, dashboard refresh management, and trusting the timeliness of the insights. Where to get This attribute is generated and stamped onto the dataset during the data extraction, transformation, and loading (ETL) process. Examples 2023-06-20T05:00:00Z2023-06-21T05:00:00Z
	Payment Terms PaymentTerms	The agreed-upon conditions for invoice payment (e.g., Net 30, Net 60).
Description Payment Terms define the rules for when a customer is expected to pay an invoice. This attribute is used to calculate the Payment Due Date and is a key dimension for analyzing payment behavior. The 'Payment Terms Adherence Analysis' dashboard uses this attribute to group customers and invoices to see if certain payment terms are more or less effective or are associated with higher rates of late payments. Why it matters It provides context for why an invoice is due on a certain date and allows for segmenting analysis to see if certain payment terms correlate with late payments. Where to get This is typically stored on the customer master record but copied to the invoice header. Look for a Payment or PaymTermId field on the CustInvoiceJour table. Examples Net 30Net 60Due on receipt
	Requires Manual Intervention RequiresManualIntervention	A flag indicating if a payment required manual adjustment during cash application.
Description This boolean attribute flags payments that needed manual intervention or correction during the cash application process. This is key for the 'Cash Application Discrepancy Rate' KPI, as it helps quantify the accuracy and automation level of payment posting. Identifying the reasons for manual intervention, such as missing remittance information or payment discrepancies, can lead to process improvements that increase straight-through processing rates. Why it matters This attribute helps identify process friction and inaccuracies in the cash application step, which is crucial for improving automation and reducing manual effort. Where to get This information might be inferred from the user who posted the payment (if it's not a system user) or from specific reason codes used during payment reconciliation. Consult Microsoft Dynamics 365 documentation. Examples truefalse
	Sales Order Number SalesOrderNumber	The identifier of the sales order that originated the invoice.
Description The Sales Order Number links the billing process back to the preceding sales process. This connection is invaluable for broader, end-to-end Order-to-Cash analysis. It allows analysts to investigate how issues in the sales and fulfillment process, such as order changes or shipping delays, might impact the billing cycle. For example, it can help answer questions like 'Do invoices from complex sales orders take longer to get paid?'. Why it matters This attribute connects the billing process to the upstream sales process, enabling a more comprehensive end-to-end Order-to-Cash analysis to identify root causes of billing issues. Where to get Found on the invoice header or line details, linking back to the sales order. Look for a field like SalesId on the CustInvoiceJour table. Examples SO-009876SO-010234US-SO-00543
	Source System SourceSystem	The system from which the data was extracted.
Description This attribute identifies the origin of the process data. In this context, it specifies that the data comes from Microsoft Dynamics 365. This is important in multi-system environments to differentiate data sources and ensure data lineage is clear, which aids in data validation and troubleshooting. Why it matters It provides crucial context about data origin, which is essential for data governance, validation, and when merging data from multiple enterprise systems. Where to get This is a static value added during the data extraction and transformation process to label the dataset's origin. Examples Microsoft Dynamics 365 FinanceD365 F&O

Required Recommended Optional

Order to Cash - Billing & Invoicing Activities

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

7 Recommended 5 Optional

	Activity	Description
	Cash Applied to Invoice	Represents the settlement of a customer payment against a specific invoice, reducing the invoice's outstanding balance. This is a distinct step in Dynamics 365 that links the payment transaction to the invoice transaction.
Why it matters This is a critical milestone for accurately calculating DSO and understanding the efficiency of the cash application team. It marks the point where the payment is fully reconciled with what was billed. Where to get Recorded in the CustSettlement table. The settlement date field on this table provides the timestamp for when a specific payment was applied to a specific invoice transaction. Capture Use the TransDate or CreatedDateTime from the CustSettlement table. Event type explicit
	Customer Payment Received	This activity marks the creation of a journal entry to record a payment received from a customer. It signifies that cash has been received, but it has not yet been applied to a specific invoice.
Why it matters This is the starting point for measuring the cash application lead time. It differentiates between receiving the money and actually reconciling it against outstanding invoices. Where to get Captured from the creation or posting of a customer payment journal. This is recorded in tables like LedgerJournalTrans, which then creates a customer transaction in CustTrans upon posting. Capture Use the posting timestamp of the customer payment journal from LedgerJournalTable. Event type explicit
	Invoice Approved	Marks the completion of the internal review process, where the invoice is formally approved. This event is logged by the Dynamics 365 workflow system and signifies that the invoice is ready to be sent to the customer.
Why it matters This is a key milestone for measuring the approval cycle time. Delays at this stage can directly increase the overall time to receive payment from the customer. Where to get Recorded in the workflow history tables, such as WorkflowTrackingStatusTable, as a completion or approval step for the specific invoice workflow instance. Capture Extract workflow event logs where the status is 'Approved' or 'Completed'. Event type explicit
	Invoice Closed	This activity marks the final state of an invoice, where its balance is zero as it has been fully paid or settled with credit notes. This is not a direct transaction but an inferred state based on the invoice's financial status.
Why it matters This event signifies the successful completion of the Order to Cash cycle for a single invoice. The time taken to reach this state is a primary measure of the process's overall efficiency. Where to get Inferred by checking if the invoice transaction in the CustTrans table has a remaining balance of zero. This is determined by comparing the invoice amount to the total settled amount against it. Capture Derive from CustTrans records where AmountCur equals SettleAmountCur. The timestamp is the date of the last settlement. Event type inferred
	Invoice Generated	Represents the creation and posting of the sales invoice document in the system. This is an explicit transaction in Dynamics 365 that creates a legal document and financial entries in the accounts receivable subledger.
Why it matters This is the official start of the billing lifecycle for an invoice. It is a critical event for tracking Days Sales Outstanding (DSO) and overall process duration. Where to get Captured from the creation timestamp of the posted sales invoice record in the CustInvoiceJour table. The corresponding financial transactions are created in the GeneralJournalEntry and LedgerEntry tables upon posting. Capture Use the CreatedDateTime field from the CustInvoiceJour table for the posted invoice. Event type explicit
	Invoice Sent to Customer	This activity represents the moment the invoice was transmitted to the customer, either electronically or physically. In Dynamics 365, this can be an explicit event if sent via system email, or inferred to occur at the time of posting if no specific tracking is enabled.
Why it matters Measures the efficiency of the invoice delivery process. A delay between approval and sending the invoice unnecessarily extends the payment cycle. Where to get Can be explicitly captured from email logs like SysOutgoingEmailTable if using D365's email capabilities. Otherwise, it is often inferred to be the same as the invoice posting time from CustInvoiceJour. Capture Use email log timestamp if available; otherwise, use the invoice posting timestamp. Event type inferred
	Payment Due Date Reached	A calculated event that occurs when the invoice's due date is reached without a payment being fully applied. This activity does not correspond to a transaction in the system but is derived from existing data.
Why it matters This event is crucial for analyzing on-time payment rates and identifying invoices that are becoming overdue. It acts as a trigger point for collection and dunning activities. Where to get This is not recorded as an event. It is calculated by using the DueDate field from the CustInvoiceJour table. The event timestamp is the due date itself. Capture Create an event where the timestamp is the value of the CustInvoiceJour.DueDate field. Event type calculated
	Credit Note Issued	Represents the creation of a credit note or credit memo, typically to correct a billing error, grant a price adjustment, or account for returned goods. A credit note is essentially a negative invoice.
Why it matters Frequent credit notes can indicate systemic issues in the order fulfillment or billing process. Analyzing why credit notes are issued is key to process improvement and reducing revenue leakage. Where to get This is an explicit transaction, recorded as a new posted invoice with a negative amount in the CustInvoiceJour table. It is often linked to the original invoice via a reference field. Capture Identify records in CustInvoiceJour with a negative total amount and link them to the original invoice. Event type explicit
	Invoice Submitted for Approval	This activity indicates that a generated invoice has been submitted into a formal approval workflow. This is common in organizations with controls requiring management review before an invoice is sent to a customer. This is logged as a specific step in the D365 workflow engine.
Why it matters Tracking submission for approval helps separate the invoice creation time from the approval waiting time. It is the first step in analyzing the efficiency of the internal review and control processes. Where to get Captured from the workflow history tables, such as WorkflowTrackingStatusTable, by filtering for the submission event related to the invoice document. Capture Extract workflow event logs where the status is 'Submitted'. Event type explicit
	Payment Reminder Issued	Represents the sending of a dunning letter or collection notice to a customer regarding an overdue invoice. Dynamics 365 has a formal dunning process that generates and logs these communications.
Why it matters Tracking reminders helps evaluate the effectiveness of the dunning process. It allows for analysis of how many reminders are needed before a payment is received. Where to get This is an explicit event captured from the creation date of the collection letter journal in the CustCollectionLetterJour table, which is linked to the overdue invoice transaction. Capture Use the creation timestamp from the CustCollectionLetterJour table. Event type explicit
	Payment Reversed	This activity signifies the reversal of a previously posted customer payment. This occurs due to errors such as incorrect posting, non-sufficient funds, or other payment failures.
Why it matters Payment reversals highlight operational errors or issues with customer payments. Analyzing their frequency helps identify root causes and improve the accuracy of the cash application process. Where to get Captured as a transaction reversal in the general ledger. A reversed journal can be identified in GeneralJournalEntry, often with a specific flag or by its relationship to the original transaction. Capture Identify journal entries with a reversal flag or a reference to a reversed transaction. Event type explicit
	Sales Order Fulfilled	This activity marks the point where goods have been shipped or services rendered for a sales order, triggering the billing process. It is typically inferred from the posting of a packing slip or delivery note in Microsoft Dynamics 365, which changes the status of the related sales order lines.
Why it matters This event serves as the starting point for measuring the invoice generation cycle time. Understanding the delay between fulfillment and invoicing helps identify administrative bottlenecks that can impact cash flow. Where to get Inferred from the creation date of a packing slip journal entry linked to the sales order. This involves tables such as CustPackingSlipJour and CustPackingSlipTrans, which are related to the sales order that the invoice is based on. Capture Identify the latest packing slip posting date for the sales order associated with the invoice. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Microsoft Dynamics 365

Steps

Navigate to the Data Management Workspace: Log in to Microsoft Dynamics 365 Finance and Operations. Use the search bar to find and navigate to the Data management workspace. This is the central hub for all data import and export operations.
Create a New Export Project: Within the Data management workspace, click the Export tile to create a new data export project. Give the project a meaningful name, such as ProcessMind_O2C_Billing_Export, and a description.
Select Target Data Format: In the project configuration, specify the Target data format. For compatibility with most data transformation tools, select CSV. Configure the format options as needed, ensuring a common delimiter and text qualifier.
Add Required Data Entities: This is the most critical step. You will add multiple standard data entities to your project to capture all required activities. For each entity, click Add entity and search for the entity name listed in the Query/Script section. Add each of the following entities to your project: Sales invoice headers V2, Sales packing slip headers, Workflow tracking status table, CustPaymentJournalLineEntity, Customer settlement, and Dunning history letters.
Apply Entity Filters: To limit the data to a relevant scope, apply filters to your entities. At a minimum, filter Sales invoice headers V2 by InvoiceDate for a specific period, for example, the last 6 months. Also, filter by the specific legal entities (companies) you wish to analyze.
Run the Export Job: Once all entities are added and filters are applied, click the Export button in the action pane. The system will create a job package and begin processing the data extraction in the background.
Monitor and Download the Data: You can monitor the job's progress in the Job history section. Once the job completes successfully, you can download the exported data package. The package will be a ZIP file containing separate CSV files for each data entity.
Unzip the Package: Extract the contents of the downloaded ZIP file to a local folder. You will now have multiple CSV files, one for each entity.
Transform and Combine Data: The raw files must be transformed into a single event log. Use a data preparation tool like Power Query, Python, or a database staging area. For each source file, create the required columns: InvoiceNumber (Case ID), ActivityName (a hardcoded value representing the event, like 'Invoice Generated'), and EventTime (the relevant date field from the source file). Map other attributes like InvoiceAmount and CustomerName.
Union All Transformed Data: Combine the transformed data from all source files into a single table using a union or append operation. This creates the final, unified event log where each row represents one activity in the invoice lifecycle.
Final Formatting for Upload: Ensure the final CSV file has the correct column headers (InvoiceNumber, ActivityName, EventTime, etc.) and that the EventTime column is in a consistent timestamp format (e.g., YYYY-MM-DD HH:MI:SS). This file is now ready for upload into ProcessMind.

Configuration

Data Entities: The extraction relies on a set of standard D365 data entities. Key entities include Sales invoice headers V2 (for invoices and credit notes), Sales packing slip headers (for order fulfillment), CustPaymentJournalLineEntity (for payments), Customer settlement (for cash application), and Workflow tracking status table (for approvals).
Export Format: The recommended target data format is CSV for its broad compatibility with data transformation tools.
Date Range Filtering: It is crucial to apply a date range filter to the primary entities, especially Sales invoice headers V2 on the InvoiceDate field. A range of 3 to 6 months is recommended to keep the export manageable while providing sufficient data for analysis.
Company Filtering: The export should be filtered by the specific legal entity or company code you are analyzing to ensure data relevance and reduce export size.
Data Staging and Transformation: A post-processing step outside of Dynamics 365 is required. The exported files must be combined and reshaped into a single event log format. This typically involves using tools like Power BI (Power Query), Python, or SQL.
Prerequisites: The user running the export must have appropriate security roles assigned in Dynamics 365, typically including a role with permissions for the Data management workspace and access to the required data entities.

a Sample Query config

The following configuration outlines the data entities and field mappings required for the export project within the Dynamics 365 Data Management Framework. A post-processing script or tool is required to union these outputs into a single event log.

**1. Activity: Sales Order Fulfilled**
*   **Entity Name:** `Sales packing slip headers`
*   **Mapping:**
    *   `InvoiceNumber`: Use `SalesOrderNumber` as a key to later join to the invoice data and find the corresponding `InvoiceNumber`.
    *   `ActivityName`: 'Sales Order Fulfilled' (Hardcoded Value)
    *   `EventTime`: `DeliveryDate`
    *   `UserResponsible`: `CreatedBy`

**2. Activity: Invoice Generated**
*   **Entity Name:** `Sales invoice headers V2`
*   **Filter:** `InvoiceType` is not 'CreditNote'.
*   **Mapping:**
    *   `InvoiceNumber`: `InvoiceNumber`
    *   `ActivityName`: 'Invoice Generated' (Hardcoded Value)
    *   `EventTime`: `InvoiceDate` and `CreatedTime`
    *   `InvoiceAmount`: `TotalInvoiceAmount`
    *   `CustomerName`: `InvoiceCustomerName`
    *   `UserResponsible`: `CreatedBy`
    *   `PaymentDueDate`: `DueDate`
    *   `InvoiceStatus`: `DocumentState`

**3. Activities: Invoice Submitted for Approval & Invoice Approved**
*   **Entity Name:** `Workflow tracking status table`
*   **Filter:** `ContextTableName` is 'CustInvoiceJour'.
*   **Mapping:**
    *   `InvoiceNumber`: Join `ContextRecId` to `RecId` from `Sales invoice headers V2` to get the `InvoiceNumber`.
    *   `ActivityName`: Use a conditional rule, if `TrackingStatus` is 'Submitted', set to 'Invoice Submitted for Approval'; if 'Approved', set to 'Invoice Approved'.
    *   `EventTime`: `CreatedDateTime`
    *   `UserResponsible`: `User`

**4. Activity: Invoice Sent to Customer**
*   **Note:** This is an inferred event. The posting date is used as a proxy.
*   **Entity Name:** `Sales invoice headers V2`
*   **Mapping:**
    *   `InvoiceNumber`: `InvoiceNumber`
    *   `ActivityName`: 'Invoice Sent to Customer' (Hardcoded Value)
    *   `EventTime`: `PostedDateTime` (or equivalent posting timestamp)

**5. Activity: Payment Due Date Reached**
*   **Note:** This is a calculated event generated during post-processing.
*   **Source Entity:** `Sales invoice headers V2`
*   **Logic:** For each invoice where the `DueDate` has passed and the invoice is not fully settled.
*   **Mapping:**
    *   `InvoiceNumber`: `InvoiceNumber`
    *   `ActivityName`: 'Payment Due Date Reached' (Hardcoded Value)
    *   `EventTime`: `DueDate`

**6. Activity: Payment Reminder Issued**
*   **Entity Name:** `Dunning history letters`
*   **Mapping:**
    *   `InvoiceNumber`: Join from the dunning history to the customer transactions to find the related `InvoiceNumber`.
    *   `ActivityName`: 'Payment Reminder Issued' (Hardcoded Value)
    *   `EventTime`: `DunningLetterDate`
    *   `UserResponsible`: `CreatedBy`

**7. Activity: Customer Payment Received**
*   **Entity Name:** `CustPaymentJournalLineEntity`
*   **Mapping:**
    *   `InvoiceNumber`: The `Invoice` field may be populated, but often settlements are needed to link payments to invoices. This might require staging the payment data first.
    *   `ActivityName`: 'Customer Payment Received' (Hardcoded Value)
    *   `EventTime`: `ReceivedDate` or `DocumentDate`
    *   `UserResponsible`: `CreatedBy`

**8. Activity: Cash Applied to Invoice**
*   **Entity Name:** `Customer settlement`
*   **Mapping:**
    *   `InvoiceNumber`: `OffsetInvoice`
    *   `ActivityName`: 'Cash Applied to Invoice' (Hardcoded Value)
    *   `EventTime`: `TransDate`
    *   `UserResponsible`: `CreatedBy`

**9. Activity: Payment Reversed**
*   **Source Entity:** `Customer settlement`
*   **Logic:** Filter for transactions where a settlement was later reversed. This may require looking for cancellation markers.
*   **Mapping:**
    *   `InvoiceNumber`: `OffsetInvoice`
    *   `ActivityName`: 'Payment Reversed' (Hardcoded Value)
    *   `EventTime`: Reversal transaction timestamp.

**10. Activity: Credit Note Issued**
*   **Entity Name:** `Sales invoice headers V2`
*   **Filter:** `InvoiceType` is 'CreditNote'.
*   **Mapping:**
    *   `InvoiceNumber`: `InvoiceNumber`
    *   `ActivityName`: 'Credit Note Issued' (Hardcoded Value)
    *   `EventTime`: `InvoiceDate`
    *   `InvoiceAmount`: `TotalInvoiceAmount` (will be negative)
    *   `CustomerName`: `InvoiceCustomerName`
    *   `UserResponsible`: `CreatedBy`

**11. Activity: Invoice Closed**
*   **Note:** This is an inferred event based on settlement data.
*   **Source Entity:** `Customer settlement`
*   **Logic:** Use the latest `TransDate` for an invoice from the settlement data where the invoice balance becomes zero.
*   **Mapping:**
    *   `InvoiceNumber`: `OffsetInvoice`
    *   `ActivityName`: 'Invoice Closed' (Hardcoded Value)
    *   `EventTime`: The latest `TransDate` that resulted in the invoice being fully settled.

Steps

Prerequisites: Ensure you have Python 3.6+ installed with the requests and pandas libraries. You will also need credentials for a service principal (App Registration) in Azure Active Directory that has been granted access to the Dynamics 365 Finance & Operations environment.
Application Registration: In Azure AD, register a new application. Create a client secret and note down the Application (client) ID, Directory (tenant) ID, and the client secret. In Dynamics 365, add this application ID under System administration > Setup > Azure Active Directory applications and assign it appropriate user rights to read the required data entities.
Configure the Script: Open the provided Python script. Populate the configuration variables at the top of the script, including d365_url, tenant_id, client_id, client_secret, company, start_date, and end_date. The d365_url should be the base URL of your Dynamics 365 environment, for example, https://your-d365-instance.operations.dynamics.com.
Authentication: The script contains a function get_access_token that automatically uses your credentials to request an OAuth2 access token from Microsoft's identity platform. This token is required to authorize subsequent API calls.
Data Extraction: Execute the script from your terminal using python your_script_name.py. The script will start by fetching all posted sales invoices (CustInvoiceJour) within the specified date range. For each invoice, it will then make additional API calls to retrieve related data for activities like payments, settlements, credit notes, and workflow history.
Activity Generation: The script processes the raw data from multiple API endpoints. It explicitly generates a row for each of the 12 required activities. For instance, it uses the CustInvoiceJour entity for the 'Invoice Generated' event and queries the CustSettlement entity to create 'Cash Applied to Invoice' events.
Calculated Events: Note that events like 'Payment Due Date Reached' and 'Invoice Closed' are not direct transactions in Dynamics 365. The script derives these events based on an invoice's due date, status, and settlement history, creating corresponding entries in the event log.
Handling Pagination: The fetch_all_data helper function is designed to handle pagination automatically. Dynamics 365 OData APIs return a limited number of records per page, so this function follows the @odata.nextLink provided in the API response to retrieve the complete dataset.
Data Transformation and Output: After collecting all events, the script compiles them into a single list, converts it into a pandas DataFrame, and standardizes the column names to InvoiceNumber, ActivityName, EventTime, and other required attributes.
Export to CSV: Finally, the script saves the resulting DataFrame to a CSV file named d365_billing_event_log.csv. This file is formatted and ready for upload into ProcessMind or another process mining tool.

Configuration

D365 URL: The base URL of your Dynamics 365 Finance & Operations instance. This is the primary endpoint for all API calls.
Azure AD Credentials: A client_id, client_secret, and tenant_id are required for authentication. These should be for an application with read access to the relevant D365 entities.
Company: Specify the DataAreaId, which corresponds to the legal entity in Dynamics 365 from which you want to extract data (e.g., 'USMF').
Date Range: Define a start_date and end_date to limit the extraction scope. For an initial analysis, a period of 3 to 6 months is recommended to ensure a manageable dataset size while capturing sufficient process variations.
Key OData Entities: The script primarily queries the following entities: CustInvoiceJours, CustSettlements, CustTrans, CustCollectionLetterJours, and WorkflowTrackingStatusTables. Ensure the configured user has permissions to read from all these entities.
Performance: Extracting data for a long period or from a very large company can be slow due to the nature of making multiple sequential API calls. It is advisable to run the script during off-peak hours and start with a smaller date range to estimate the total execution time.

a Sample Query python

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

class=class="hl-string">"hl-comment"># --- Configuration ---
d365_url = class="hl-string">"https://[your-d365-instance].operations.dynamics.com" class=class="hl-string">"hl-comment"># Your D365 F&O URL
tenant_id = class="hl-string">"[Your Tenant ID]"
client_id = class="hl-string">"[Your Client ID]"
client_secret = class="hl-string">"[Your Client Secret]"
company = class="hl-string">"[Your Company DataAreaId, e.g., USMF]"
start_date = class="hl-string">"2023-01-01"
end_date = class="hl-string">"2023-12-31"

class=class="hl-string">"hl-comment"># --- Authentication ---
def get_access_token():
    url = fclass="hl-string">"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"
    payload = {
        class="hl-string">'grant_type': class="hl-string">'client_credentials',
        class="hl-string">'client_id': client_id,
        class="hl-string">'client_secret': client_secret,
        class="hl-string">'scope': fclass="hl-string">"{d365_url}/.default"
    }
    response = requests.post(url, data=payload)
    response.raise_for_status()
    return response.json()[class="hl-string">'access_token']

class=class="hl-string">"hl-comment"># --- OData Helper ---
def fetch_all_data(access_token, resource_path):
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json'
    }
    full_url = fclass="hl-string">"{d365_url}/data/{resource_path}"
    results = []
    while full_url:
        response = requests.get(full_url, headers=headers)
        response.raise_for_status()
        data = response.json()
        results.extend(data[class="hl-string">'value'])
        full_url = data.get(class="hl-string">'@odata.nextLink')
    return results

class=class="hl-string">"hl-comment"># --- Main Extraction Logic ---
def main():
    print(class="hl-string">"Acquiring access token...")
    token = get_access_token()
    event_log = []
    today = datetime.now(timezone.utc).date()

    print(class="hl-string">"Fetching Invoices and Credit Notes...")
    invoice_filter = fclass="hl-string">"$filter=InvoiceDate ge {start_date} and InvoiceDate le {end_date}"
    invoice_fields = class="hl-string">"$select=InvoiceNumber,InvoiceDate,SalesOrderNumber,InvoiceAmount,CustomerAccount,Due_Date,WorkerSalespersonName,createdDateTime,RecId,SalesInvoiceHeader_SalesStatus"
    invoices = fetch_all_data(token, fclass="hl-string">"CustInvoiceJours?cross-company=true&{invoice_filter}&{invoice_fields}")

    class=class="hl-string">"hl-comment"># Create a lookup for invoice RecIds for workflow queries
    invoice_recid_map = {inv[class="hl-string">'RecId']: inv[class="hl-string">'InvoiceNumber'] for inv in invoices}

    for inv in invoices:
        case_id = inv[class="hl-string">'InvoiceNumber']
        invoice_amount = inv[class="hl-string">'InvoiceAmount']
        customer_name = inv.get(class="hl-string">'CustomerAccount', class="hl-string">'') class=class="hl-string">"hl-comment"># Placeholder, join to CustTable for name
        user_responsible = inv.get(class="hl-string">'WorkerSalespersonName', class="hl-string">'System')
        due_date = inv.get(class="hl-string">'Due_Date')
        invoice_status = inv.get(class="hl-string">'SalesInvoiceHeader_SalesStatus', class="hl-string">'Open')

        class=class="hl-string">"hl-comment"># Activity: Invoice Generated / Credit Note Issued
        activity_name = class="hl-string">'Credit Note Issued' if invoice_amount < 0 else class="hl-string">'Invoice Generated'
        event_log.append({
            class="hl-string">'InvoiceNumber': case_id,
            class="hl-string">'ActivityName': activity_name,
            class="hl-string">'EventTime': inv[class="hl-string">'createdDateTime'],
            class="hl-string">'InvoiceAmount': invoice_amount,
            class="hl-string">'CustomerName': customer_name,
            class="hl-string">'UserResponsible': user_responsible,
            class="hl-string">'Department': class="hl-string">'[Configure based on your system]',
            class="hl-string">'PaymentDueDate': due_date,
            class="hl-string">'InvoiceStatus': invoice_status
        })

        class=class="hl-string">"hl-comment"># Activity: Invoice Sent to Customer (proxy)
        event_log.append({
            class="hl-string">'InvoiceNumber': case_id,
            class="hl-string">'ActivityName': class="hl-string">'Invoice Sent to Customer',
            class="hl-string">'EventTime': inv[class="hl-string">'InvoiceDate'],
            class="hl-string">'InvoiceAmount': invoice_amount,
            class="hl-string">'CustomerName': customer_name,
            class="hl-string">'UserResponsible': user_responsible,
            class="hl-string">'Department': class="hl-string">'[Configure based on your system]',
            class="hl-string">'PaymentDueDate': due_date,
            class="hl-string">'InvoiceStatus': invoice_status
        })
        
        class=class="hl-string">"hl-comment"># Activity: Sales Order Fulfilled (proxy via invoice date, complex to get actual packing slip date)
        class=class="hl-string">"hl-comment"># A more accurate query would trace SalesInvoiceLinesV2 -> SalesOrderLines -> CustPackingSlipJour
        event_log.append({
            class="hl-string">'InvoiceNumber': case_id,
            class="hl-string">'ActivityName': class="hl-string">'Sales Order Fulfilled',
            class="hl-string">'EventTime': inv[class="hl-string">'InvoiceDate'], class=class="hl-string">"hl-comment"># Using InvoiceDate as a proxy
            class="hl-string">'InvoiceAmount': invoice_amount,
            class="hl-string">'CustomerName': customer_name,
            class="hl-string">'UserResponsible': user_responsible,
            class="hl-string">'Department': class="hl-string">'[Configure based on your system]',
            class="hl-string">'PaymentDueDate': due_date,
            class="hl-string">'InvoiceStatus': invoice_status
        })

        class=class="hl-string">"hl-comment"># Activity: Payment Due Date Reached (calculated)
        if due_date and pd.to_datetime(due_date).date() < today and invoice_status != class="hl-string">'Paid':
            event_log.append({
                class="hl-string">'InvoiceNumber': case_id,
                class="hl-string">'ActivityName': class="hl-string">'Payment Due Date Reached',
                class="hl-string">'EventTime': due_date,
                class="hl-string">'InvoiceAmount': invoice_amount,
                class="hl-string">'CustomerName': customer_name,
                class="hl-string">'UserResponsible': class="hl-string">'System',
                class="hl-string">'Department': class="hl-string">'Finance',
                class="hl-string">'PaymentDueDate': due_date,
                class="hl-string">'InvoiceStatus': invoice_status
            })

    print(class="hl-string">"Fetching Settlements for Cash Applied and Invoice Closed events...")
    settlement_filter = fclass="hl-string">"$filter=InvoiceDate ge {start_date} and InvoiceDate le {end_date}"
    settlement_fields = class="hl-string">"$select=InvoiceNumber,SettlementDate,OffsetTransactionVoucher,OffsetRecId,CanBeReversed"
    settlements = fetch_all_data(token, fclass="hl-string">"CustSettlements?cross-company=true&{settlement_filter}&{settlement_fields}")

    class=class="hl-string">"hl-comment"># Group settlements by invoice
    settlements_by_invoice = pd.DataFrame(settlements).groupby(class="hl-string">'InvoiceNumber')

    for case_id, group in settlements_by_invoice:
        last_settlement = group.sort_values(class="hl-string">'SettlementDate').iloc[-1]

        for _, settlement in group.iterrows():
            class=class="hl-string">"hl-comment"># Activity: Cash Applied to Invoice
            event_log.append({
                class="hl-string">'InvoiceNumber': case_id,
                class="hl-string">'ActivityName': class="hl-string">'Cash Applied to Invoice',
                class="hl-string">'EventTime': settlement[class="hl-string">'SettlementDate'],
                class="hl-string">'UserResponsible': class="hl-string">'System',
                class=class="hl-string">"hl-comment"># Add other attributes from original invoice if needed
            })

            class=class="hl-string">"hl-comment"># Activity: Customer Payment Received (date of original payment)
            class=class="hl-string">"hl-comment"># This requires a second hop to CustTrans using OffsetRecId
            class=class="hl-string">"hl-comment"># For simplicity, we use settlement date as a proxy. A full implementation would query CustTrans.
            event_log.append({
                class="hl-string">'InvoiceNumber': case_id,
                class="hl-string">'ActivityName': class="hl-string">'Customer Payment Received',
                class="hl-string">'EventTime': settlement[class="hl-string">'SettlementDate'], class=class="hl-string">"hl-comment"># Proxy timestamp
                class="hl-string">'UserResponsible': class="hl-string">'System',
            })

            class=class="hl-string">"hl-comment"># Activity: Payment Reversed (if applicable)
            if settlement[class="hl-string">'CanBeReversed']: class=class="hl-string">"hl-comment"># A simplistic check, real logic is more complex
                 event_log.append({
                    class="hl-string">'InvoiceNumber': case_id,
                    class="hl-string">'ActivityName': class="hl-string">'Payment Reversed',
                    class="hl-string">'EventTime': settlement[class="hl-string">'SettlementDate'], class=class="hl-string">"hl-comment"># Reversal date would be different in reality
                    class="hl-string">'UserResponsible': class="hl-string">'System',
                 })
        
        class=class="hl-string">"hl-comment"># Activity: Invoice Closed (inferred from last settlement)
        class=class="hl-string">"hl-comment"># This logic assumes the invoice is fully paid by the last settlement. A robust check would sum amounts.
        event_log.append({
            class="hl-string">'InvoiceNumber': case_id,
            class="hl-string">'ActivityName': class="hl-string">'Invoice Closed',
            class="hl-string">'EventTime': last_settlement[class="hl-string">'SettlementDate'],
            class="hl-string">'UserResponsible': class="hl-string">'System',
        })

    print(class="hl-string">"Fetching Payment Reminders...")
    reminder_filter = fclass="hl-string">"$filter=CollectionLetterDate ge {start_date} and CollectionLetterDate le {end_date}"
    reminders = fetch_all_data(token, fclass="hl-string">"CustCollectionLetterJours?cross-company=true&{reminder_filter}")
    for rem in reminders:
        class=class="hl-string">"hl-comment"># Linking reminders to invoices can be complex. This is a simplified approach.
        if rem.get(class="hl-string">'InvoiceNum'):
            event_log.append({
                class="hl-string">'InvoiceNumber': rem[class="hl-string">'InvoiceNum'],
                class="hl-string">'ActivityName': class="hl-string">'Payment Reminder Issued',
                class="hl-string">'EventTime': rem[class="hl-string">'CollectionLetterDate'],
                class="hl-string">'UserResponsible': rem.get(class="hl-string">'UserId', class="hl-string">'System'),
            })

    print(class="hl-string">"Fetching Workflow History...")
    class=class="hl-string">"hl-comment"># This is a complex query. It requires knowing the TableId for CustInvoiceJour (e.g., 523)
    class=class="hl-string">"hl-comment"># The filter below is illustrative and needs to be adapted for your specific workflow setup.
    workflow_filter = fclass="hl-string">"$filter=createdDateTime ge {start_date}T00:00:00Z"
    wf_history = fetch_all_data(token, fclass="hl-string">"WorkflowTrackingStatusTables?cross-company=true&{workflow_filter}")
    for item in wf_history:
        if item.get(class="hl-string">'ContextRecId') in invoice_recid_map:
            case_id = invoice_recid_map[item[class="hl-string">'ContextRecId']]
            activity_name = None
            if item[class="hl-string">'TrackingStatus'] == class="hl-string">'Submitted':
                activity_name = class="hl-string">'Invoice Submitted for Approval'
            elif item[class="hl-string">'TrackingStatus'] == class="hl-string">'Approved':
                activity_name = class="hl-string">'Invoice Approved'
            
            if activity_name:
                event_log.append({
                    class="hl-string">'InvoiceNumber': case_id,
                    class="hl-string">'ActivityName': activity_name,
                    class="hl-string">'EventTime': item[class="hl-string">'createdDateTime'],
                    class="hl-string">'UserResponsible': item.get(class="hl-string">'User', class="hl-string">'System'),
                })

    print(fclass="hl-string">"Total events extracted: {len(event_log)}")
    if not event_log:
        print(class="hl-string">"No events found for the given criteria.")
        return

    df = pd.DataFrame(event_log)
    df = df.sort_values(by=[class="hl-string">'InvoiceNumber', class="hl-string">'EventTime'])
    df.to_csv(class="hl-string">'d365_billing_event_log.csv', index=False)
    print(class="hl-string">"Event log saved to d365_billing_event_log.csv")

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

Your Order to Cash - Billing & Invoicing Data Template

Your Order to Cash - Billing & Invoicing Data Template

Order to Cash - Billing & Invoicing Attributes

Order to Cash - Billing & Invoicing Activities

Extraction Guides

Microsoft Dynamics 365 Data Entity Export

Steps

Configuration

a Sample Query config

Python Script using Dynamics 365 OData Web API

Steps

Configuration

a Sample Query python

Ready to Get Started?

End Billing Delays: Optimize Order to Cash & Invoicing Today

Your Order to Cash - Billing & Invoicing Data Template

Your Order to Cash - Billing & Invoicing Data Template

Order to Cash - Billing & Invoicing Attributes

Order to Cash - Billing & Invoicing Activities

Extraction Guides

Microsoft Dynamics 365 Data Entity Export

Steps

Configuration

a Sample Query config

Python Script using Dynamics 365 OData Web API

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

End Billing Delays: Optimize Order to Cash & Invoicing Today