> Continuous Process Improvement > Accounts Receivable > Microsoft Dynamics 365 > Data Template

Your Accounts Receivable Data Template

Microsoft Dynamics 365

Accounts Receivable Attributes (21) Accounts Receivable Activities (15) Extraction Guides (2)

Your Accounts Receivable Data Template

This template provides a comprehensive framework for mapping your invoice-to-cash process within Microsoft Dynamics 365. It outlines the essential data fields and milestones required to build a high-quality event log for process mining. By following this structure, you can gain deep visibility into payment delays and streamline your collection workflows.

Essential data fields for ledger analysis
Standard process milestones for tracking
Technical extraction guidance for Microsoft Dynamics 365

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

Download Template

Accounts Receivable Attributes

These are the recommended data fields to include in your event log for a comprehensive analysis of your accounts receivable performance.

5 Required 8 Recommended 8 Optional

	Name	Description
	Activity Activity	The specific event or task performed on the invoice.
Description This attribute describes the step occurring in the process, such as Invoice Posted, Payment Received, or Dispute Opened. In Microsoft Dynamics 365, these are often derived from the transaction type (TransType) or the specific table where the record is logged (e.g., CustSettlement for payments). This attribute is essential for visualizing the process map. Why it matters It defines the nodes in the process map and allows for the analysis of process flow and variant discovery. Where to get Derived from TransType enum or table context (CustTrans, CustSettlement, etc.) Examples Invoice PostedPayment ReceivedCollection Letter SentInterest Note Posted
	Event Timestamp EventTimestamp	The exact date and time when the activity occurred.
Description This attribute records the specific point in time when an event was logged in the system. It is used to calculate durations between activities, determine process lead times, and order events sequentially. In D365, this is typically the CreatedDateTime or TransDate combined with a time component. Why it matters Time stamps are critical for calculating all time based KPIs, such as Invoice Cycle Time and Dispatch Lead Time. Where to get Commonly CreatedDateTime or TransDate fields across various tables Examples 2023-10-15T08:30:00Z2023-10-15T14:45:22Z2023-11-01T09:00:00Z
	Invoice Number InvoiceNumber	The unique identifier for the financial invoice document.
Description The Invoice Number is the primary key for tracking the lifecycle of a receivable within Microsoft Dynamics 365. It links the initial sales order, the posted invoice, subsequent payments, and any collection activities into a single case. In analysis, this attribute serves as the case identifier to reconstruct the end to end process flow. Why it matters It is the fundamental unit of analysis for Accounts Receivable, allowing the process mining engine to group disparate events into a cohesive case. Where to get CustInvoiceJour.InvoiceId or CustTrans.Invoice Examples INV-2023-001CIV-88921US-004321DE-99120
	Last Data Update LastDataUpdate	The timestamp of when the data was extracted or refreshed.
Description This attribute indicates when the data was last pulled from Microsoft Dynamics 365. It helps users understand the freshness of the analysis and validity of the KPIs. It is typically generated by the extraction tool at the time of the query. Why it matters It provides context on data latency and helps users trust the currency of the dashboards. Where to get Generated by the ETL / extraction script Examples 2023-11-05T00:00:00Z2023-11-05T12:00:00Z
	Source System SourceSystem	The name of the system where the data originated.
Description This attribute identifies the system of record for the data, which is Microsoft Dynamics 365 in this context. It is useful when combining data from multiple ERP instances or external collection tools into a single view. This allows analysts to filter or segment processes by their originating environment. Why it matters It ensures data lineage and traceability, especially in multi-system landscapes. Where to get Hardcoded during extraction or derived from connection string Examples D365 F&O ProdD365 FinanceDynamics AX 2012
	Business Unit BusinessUnit	The operating unit or division responsible for the invoice.
Description This attribute segments data by internal organizational structure, such as Electronics Division or Services Division. In D365, this is often a financial dimension linked to the transaction. It supports the Invoice Issuance Lead Time dashboard by allowing comparisons across units. Why it matters Enables internal benchmarking to find which divisions are most efficient. Where to get Financial Dimensions (DefaultDimension) on CustInvoiceJour Examples BU-001Sales-NorthServices-Global
	Company Code CompanyCode	The legal entity identifier within D365.
Description This attribute represents the DataAreaId in D365, which corresponds to the legal entity or company where the transaction occurred. It is essential for filtering data in multi-company deployments and ensures that invoice numbers, which may overlap between companies, are treated distinctly. Why it matters Crucial for uniqueness in multi-entity environments and regional analysis. Where to get DataAreaId field on all tables Examples USMFDEMFGBSI
	Customer Account CustomerAccount	The unique identifier or account number of the customer.
Description The Customer Account identifies the specific entity being billed. In D365, this is the AccountNum field. Grouping by this attribute allows for analysis of payment behaviors, dispute frequencies, and clearing times across different customers. Why it matters Essential for identifying high risk customers and segmenting performance by client. Where to get CustInvoiceJour.InvoiceAccount or CustTrans.AccountNum Examples US-001DE-550CUST-9921
	Customer Group CustomerGroup	The classification or segment the customer belongs to.
Description This attribute categorizes customers into logical groups, such as Wholesale, Retail, or Intercompany. It is used to perform comparative analysis, such as checking if certain segments have longer payment cycles or higher dispute rates. In D365, this is the CustGroup field. Why it matters Enables the comparison of process performance across different market segments. Where to get CustTable.CustGroup Examples WholesaleRetailIntercompanyExport
	Due Date DueDate	The date by which the payment is expected to be received.
Description The Due Date is the deadline for payment as defined by the payment terms. It is compared against the actual payment date to determine on time performance and calculate days sales outstanding (DSO). It is a standard field on the customer transaction record. Why it matters Fundamental for calculating On Time Payment Rate and analyzing overdue invoices. Where to get CustTrans.DueDate Examples 2023-11-302023-12-15
	Invoice Amount InvoiceAmount	The total monetary value of the invoice.
Description This attribute represents the financial value of the invoice document. It is used to calculate the total exposure in Accounts Receivable and to prioritize high value collections. This field is typically found in the invoice journal table. Why it matters Critical for financial impact analysis and identifying high value bottlenecks. Where to get CustInvoiceJour.InvoiceAmount Examples 1500.00250.5010000.00
	Is Automated IsAutomated	Flag indicating if the activity was performed by a system account.
Description This attribute determines if a specific step was performed by a user or an automated batch job. It is calculated by comparing the User attribute against a list of known system accounts (e.g., 'Workflow', 'Batch'). It supports the Manual Clearing Rate KPI. Why it matters Critical for identifying automation opportunities and measuring current automation levels. Where to get Derived from User field logic Examples truefalse
	User User	The user ID of the person or system account that performed the activity.
Description This attribute identifies who executed the specific process step, derived from the CreatedBy or ModifiedBy fields. It helps in analyzing resource utilization, identifying training needs, and distinguishing between automated system tasks and manual user interventions. Why it matters Allows for resource performance analysis and automation rate calculation. Where to get CreatedBy field on transaction tables Examples AdminJSmithWorkflowAgentBSmith
	Cash Discount Date CashDiscountDate	The date until which an early payment discount is valid.
Description This attribute indicates the deadline for the customer to receive a discount for early payment. It is compared against the payment date to calculate the Missed Discount Rate KPI and analyze the effectiveness of discount incentives. Why it matters Key for the Early Payment Discount Optimization dashboard. Where to get CustTrans.CashDisc or calculated from CashDiscCode Examples 2023-11-102023-11-15
	Currency Currency	The currency code of the invoice transaction.
Description This attribute specifies the currency in which the invoice was issued (e.g., USD, EUR). It is important for normalizing financial values if global analysis is required across different regions. Why it matters Necessary for understanding value distribution in multi-currency systems. Where to get CustInvoiceJour.CurrencyCode Examples USDEURGBPJPY
	Days Overdue DaysOverdue	The number of days the payment was late compared to the due date.
Description This metric calculates the difference between the payment date and the due date. Positive values indicate late payments, while negative values indicate early payments. It provides a granular view of payment performance beyond simple boolean flags. Why it matters Provides depth to the On Time Payment Rate KPI analysis. Where to get Calculated: PaymentDate - DueDate Examples 50-230
	Dispute Reason DisputeReason	The reason code assigned when a case is in dispute.
Description This attribute captures the categorical reason why an invoice was disputed, such as 'Price Error' or 'Damaged Goods'. It is vital for the Dispute Resolution Lifecycle dashboard to identify root causes of payment delays. Why it matters Enables root cause analysis for the Dispute Resolution Time KPI. Where to get CustTrans.ReasonRefRecId or related collections case table Examples PriceDiscrepancyGoodsDamagedMissingPOServiceQuality
	Is Cleared IsCleared	Flag indicating if the invoice has been fully settled.
Description This boolean attribute indicates whether the invoice has reached a balance of zero through payment or write-off. It allows for the quick filtering of open vs. closed cases to calculate the Average Invoice Cycle Time. Why it matters Distinguishes between active and historical cases. Where to get Derived from CustTrans.Closed date presence Examples truefalse
	Is Credit Memo IsCreditMemo	Flag indicating if the document is a credit note.
Description This boolean attribute identifies whether the transaction represents a credit memo issued to the customer. It is used to calculate the Credit Memo Issuance Rate and filter out standard invoices from rework analysis. Why it matters Directly supports the Credit Memo and Rework Frequency dashboard. Where to get Derived from CustInvoiceJour.InvoiceAmount < 0 or TransType Examples truefalse
	Payment Terms PaymentTerms	The code defining the agreed upon payment schedule.
Description This attribute holds the code that dictates when payment is due (e.g., Net30, Net60). It is used to analyze if customers are adhering to their specific terms and to validate if the Due Date was calculated correctly. Why it matters Provides context for the Due Date and helps identify if terms are too lenient or strict. Where to get CustInvoiceJour.Payment or CustTable.PaymTermId Examples Net30Net45CODEOM
	Sales Order ID SalesOrderId	The reference number of the sales order associated with the invoice.
Description This attribute links the invoice back to the originating sales order. It allows for cross-process analysis if data from the Order-to-Cash process is available and helps investigate upstream causes for billing issues. Why it matters Connects the billing process to the sales process. Where to get CustInvoiceJour.SalesId Examples SO-10022SO-55912

Required Recommended Optional

Accounts Receivable Activities

These are the key process steps and milestones to capture in your event log for accurate discovery of your payment settlement flow.

8 Recommended 7 Optional

	Activity	Description
	Credit Memo Issued	The posting of a credit note that offsets the original invoice amount. Often the result of a resolved dispute or return.
Why it matters High frequency indicates revenue leakage and upstream quality issues. Distinct from payment as it represents non-cash settlement. Where to get CustTrans table where Type is 'Credit Note' or CustInvoiceJour with a negative InvoiceAmount. Capture Logged when transaction posted to CustTrans Event type explicit
	Dispute Case Opened	The creation of a case in the Case Management module linked to the invoice, signaling a customer disagreement. Blocks standard collection flows.
Why it matters Disputes are a primary cause of late payments. Tracking this identifies quality issues in upstream processes like pricing or fulfillment. Where to get CaseDetailBase table where Category relates to Collections/AR and RefRecId links to the invoice context. Capture Logged when transaction X executed in Case Management Event type explicit
	Full Payment Received	The posting of a payment transaction that covers the remaining balance of the invoice. This is the cash inflow event.
Why it matters The most critical success event. Used to calculate On-Time Payment Rate and effectiveness of discount terms. Where to get CustTrans table with Type 'Payment', linked via CustSettlement to the invoice, bringing the balance to zero. Capture Logged when transaction posted to CustTrans and settled Event type explicit
	Invoice Cleared	The final status change where the invoice balance becomes zero and the transaction is marked as 'Closed' in the system.
Why it matters The absolute end of the process instance. Used for total Cycle Time calculation. Where to get CustTrans.Closed field (date). This date is populated when the sum of settlements equals the invoice amount. Capture Inferred from CustTrans.Closed date field Event type inferred
	Invoice Dispatched	The event where the invoice is sent to the customer via email, print, or EDI. This is often inferred from the incrementing of the 'Printed' count or print management logs.
Why it matters Delays between posting and dispatching directly shorten the effective payment window for the customer, leading to perceived late payments. Where to get Inferred from CustInvoiceJour.PrintedOriginals changing from 0 to 1, or tracked via the PrintJobHeader table if logging is enabled. Capture Compare status field before/after or PrintJobHeader log Event type inferred
	Invoice Posted	The initial recording of the invoice in the financial ledger. This activity captures the creation of the financial obligation in the CustInvoiceJour table.
Why it matters Marks the official start of the Accounts Receivable lifecycle and the beginning of the payment term clock. Essential for calculating Days Sales Outstanding (DSO). Where to get CustInvoiceJour table. The CreatedDateTime or InvoiceDate field serves as the timestamp. Capture Logged when transaction posted to CustInvoiceJour Event type explicit
	Partial Payment Posted	The recording of a payment that does not fully cover the open balance of the invoice. Often triggers a remaining balance to be pursued.
Why it matters Distinguishes between installment behaviors and full settlements. Important for cash flow forecasting accuracy. Where to get CustSettlement table linking a Payment (CustTrans) to the Invoice (CustTrans) where SettlementAmount < InvoiceAmount. Capture Logged when transaction X executed in CustSettlement Event type explicit
	Payment Reminder Sent	Records the issuance of a formal collection letter to the customer. This is captured from the Collection Letter Journal.
Why it matters Indicates the start of active collection efforts. Frequency analysis helps optimize the dunning strategy and reduce administrative costs. Where to get CustCollectionLetterJour table. Captures the date the letter code was posted for the specific invoice transaction. Capture Logged when transaction posted to CustCollectionLetterJour Event type explicit
	Bank Statement Matched	The reconciliation event where the payment recorded in AR is matched against a line on the imported bank statement.
Why it matters Validates that funds are actually in the bank. Delays here indicate inefficiencies in the Cash and Bank Management processes. Where to get BankReconciliationLine or BankAccountTrans matched status. Requires Advanced Bank Reconciliation module. Capture Logged when transaction matched in Bank Reconciliation Event type explicit
	Dispute Resolved	The closing of the dispute case, allowing the invoice to proceed to payment or credit. Marks the end of the exception handling sub-process.
Why it matters The timestamp delta between Opened and Resolved is a key KPI for administrative efficiency. Where to get CaseDetailBase table where Status changes to Closed/Resolved. Capture Compare status field before/after in CaseDetailBase Event type inferred
	Dispute Under Review	Indicates the dispute case has moved from 'Opened' to an 'In Process' state. Represents the working time of the resolution team.
Why it matters Bottlenecks here increase the Cash Conversion Cycle. Segregates wait time from active resolution time. Where to get Inferred from CaseDetailBase.Status field changes or CaseLog table entries corresponding to status progression. Capture Compare status field before/after in CaseDetailBase Event type inferred
	Due Date Passed	A calculated milestone indicating that the current date has surpassed the invoice due date while the invoice remains open. This flags the invoice as overdue.
Why it matters Critical for aging analysis and triggering collection workflows. It segments the process between standard billing and collections management. Where to get Calculated by comparing the CustInvoiceJour.DueDate against the current simulation time or payment timestamp. Capture Derive from comparing field DueDate to current time Event type calculated
	Interest Note Generated	The creation of an interest note for overdue payments. Represents a financial penalty applied to the customer account.
Why it matters Indicates severe delinquency. Analysis helps determine if penalties effectively accelerate payment or damage customer relationships. Where to get CustInterestJour table. This table logs interest notes generated for specific customer transactions. Capture Logged when transaction posted to CustInterestJour Event type explicit
	Payment Written Off	The act of declaring the invoice uncollectible and removing the balance via a write-off journal. Represents a financial loss.
Why it matters End state for failed collection processes. Essential for analyzing bad debt ratios and credit policy effectiveness. Where to get CustTrans table where Type is 'WriteOff' or GeneralJournalEntry specifically marked with write-off reason codes. Capture Logged when transaction posted with WriteOff type Event type explicit
	Promise to Pay Received	A specific commitment logged by a collections agent that the customer will pay by a certain date. Captured from the Collections Management module.
Why it matters Measures the effectiveness of collection calls. Comparing this date to actual payment reveals the reliability of customer promises. Where to get CustPromiseToPay table. Linked to the customer transaction via TransRecId. Capture Logged when record created in CustPromiseToPay Event type explicit

Recommended Optional

Extraction Guides

How to get your data from Microsoft Dynamics 365

Steps

Configure Azure Resources: Provision an Azure SQL Database to serve as the Bring Your Own Database (BYOD) target. Ensure the connection string includes credentials with read/write permissions.
Configure Data Management in D365: Navigate to System Administration > Workspaces > Data Management. Click on the 'Configure Entity Export to Database' tile. Enter the connection string from the previous step to register the external database.
Select Required Entities: Create a new Export Project within the Data Management workspace. Add the following standard entities to the project: CustInvoiceJournalHeaderEntity, CustTransactionEntity, CustSettlementEntity, CustCollectionLetterJournalHeaderEntity, CaseDetailEntity, CaseAssociationEntity, CustInterestJournalEntity, BankAccountTransEntity, and CustPromiseToPayEntity.
Map Source to Target: Ensure all fields are mapped correctly, specifically ensuring that RecId and CreatedDateTime fields are included, as these are critical for event sequencing.
Run Initial Export: Execute the export job manually (Job type: Export) to perform a full load of historical data into the Azure SQL Database. Monitor the execution summary to ensure all records transferred successfully.
Schedule Incremental Updates: Configure a recurring data job to run the export project periodically (e.g., every 4 hours or nightly) using 'Incremental push' to update only changed records.
Connect to Azure SQL: Use SQL Server Management Studio (SSMS) or a similar tool to connect to the BYOD Azure SQL Database.
Execute Transformation Script: Run the SQL script provided in the Query section below against the BYOD tables. This script unifies the disparate tables into a single event log structure.
Verify and Extract: Check the output for consistency (see Validation Steps). Export the result set to a CSV or Parquet file for ingestion into ProcessMind.

Configuration

Export Method: Bring Your Own Database (BYOD) or Azure Data Lake Storage Gen2 (ADLS).
Refresh Strategy: Full push for initial load, Incremental push for recurring updates to minimize performance impact.
Required Entities: CustInvoiceJournalHeaderEntity (Invoices), CustSettlementEntity (Payments/Clearance), CustCollectionLetterJournalHeaderEntity (Reminders), CaseDetailEntity (Disputes), BankAccountTransEntity (Bank Matching).
Company Filter: Apply filters on the 'DataAreaId' column if the analysis should be restricted to specific legal entities.
Date Range: Recommended 12-month rolling window based on 'InvoiceDate' or 'TransDate'.
Change Tracking: Must be enabled on the SQL tables in D365 for incremental export to work efficiently.

a Sample Query config

/* SQL Transformation Script for D365 BYOD Tables */
/* Target: Azure SQL Database containing exported entities */

WITH InvoiceBasics AS (
    SELECT 
        InvoiceNumber AS InvoiceId,
        InvoiceDate,
        DueDate,
        InvoiceAccount AS CustomerAccount,
        SalesId,
        DataAreaId AS CompanyCode,
        InvoiceAmount,
        Currency
    FROM dbo.CustInvoiceJournalHeaderEntity
    WHERE InvoiceDate >= DATEADD(month, -12, GETDATE())
)

/* 1. Invoice Posted */
SELECT 
    T1.InvoiceId AS InvoiceNumber,
    'Invoice Posted' AS Activity,
    CAST(T1.InvoiceDate AS DATETIME) AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    T1.CustomerAccount,
    NULL AS CustomerGroup,
    T1.InvoiceAmount,
    T1.DueDate,
    NULL AS [User],
    NULL AS BusinessUnit,
    T1.CompanyCode,
    1 AS IsAutomated
FROM InvoiceBasics T1

UNION ALL

/* 2. Invoice Dispatched (Inferred from Document Date as Proxy if Print Log unavailable) */
SELECT 
    T1.InvoiceId,
    'Invoice Dispatched' AS Activity,
    DATEADD(minute, 5, CAST(T1.InvoiceDate AS DATETIME)) AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    T1.CustomerAccount,
    NULL,
    T1.InvoiceAmount,
    T1.DueDate,
    NULL,
    NULL,
    T1.CompanyCode,
    1
FROM InvoiceBasics T1

UNION ALL

/* 3. Due Date Passed (Synthetic Event) */
SELECT 
    T1.InvoiceId,
    'Due Date Passed' AS Activity,
    CAST(T1.DueDate AS DATETIME) AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    T1.CustomerAccount,
    NULL,
    T1.InvoiceAmount,
    T1.DueDate,
    NULL,
    NULL,
    T1.CompanyCode,
    1
FROM InvoiceBasics T1
WHERE GETDATE() > T1.DueDate

UNION ALL

/* 4. Payment Reminder Sent */
SELECT 
    Inv.InvoiceId,
    'Payment Reminder Sent' AS Activity,
    CL.JournalDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    CL.CreatedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CustCollectionLetterJournalHeaderEntity CL
JOIN dbo.CustCollectionLetterLineEntity CLL ON CL.JournalNumber = CLL.JournalNumber AND CL.DataAreaId = CLL.DataAreaId
JOIN InvoiceBasics Inv ON CLL.InvoiceId = Inv.InvoiceId AND CLL.DataAreaId = Inv.CompanyCode

UNION ALL

/* 5. Promise to Pay Received */
SELECT 
    Inv.InvoiceId,
    'Promise to Pay Received' AS Activity,
    PTP.PromiseDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    NULL,
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CustPromiseToPayEntity PTP
JOIN InvoiceBasics Inv ON PTP.Invoice = Inv.InvoiceId AND PTP.DataAreaId = Inv.CompanyCode

UNION ALL

/* 6. Dispute Case Opened */
SELECT 
    Inv.InvoiceId,
    'Dispute Case Opened' AS Activity,
    CD.CreatedDateTime AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    CD.CreatedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CaseDetailEntity CD
JOIN dbo.CaseAssociationEntity CA ON CD.CaseId = CA.CaseId AND CD.DataAreaId = CA.DataAreaId
JOIN InvoiceBasics Inv ON CA.RefRecId = Inv.InvoiceId /* Logic assumes linking via Invoice Number or manual mapping might be needed depending on RefTableId */
WHERE CA.RefEntityName = 'CustInvoiceTable'

UNION ALL

/* 7. Dispute Under Review */
SELECT 
    Inv.InvoiceId,
    'Dispute Under Review' AS Activity,
    CD.ModifiedDateTime AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    CD.ModifiedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CaseDetailEntity CD
JOIN dbo.CaseAssociationEntity CA ON CD.CaseId = CA.CaseId AND CD.DataAreaId = CA.DataAreaId
JOIN InvoiceBasics Inv ON CA.RefRecId = Inv.InvoiceId
WHERE CD.Status = 'InProcess'

UNION ALL

/* 8. Dispute Resolved */
SELECT 
    Inv.InvoiceId,
    'Dispute Resolved' AS Activity,
    CD.ClosedDateTime AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    CD.ModifiedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CaseDetailEntity CD
JOIN dbo.CaseAssociationEntity CA ON CD.CaseId = CA.CaseId AND CD.DataAreaId = CA.DataAreaId
JOIN InvoiceBasics Inv ON CA.RefRecId = Inv.InvoiceId
WHERE CD.Status = 'Closed'

UNION ALL

/* 9. Credit Memo Issued */
SELECT 
    T1.InvoiceId,
    'Credit Memo Issued' AS Activity,
    T1.InvoiceDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    T1.CustomerAccount,
    NULL,
    T1.InvoiceAmount,
    T1.DueDate,
    NULL,
    NULL,
    T1.CompanyCode,
    0
FROM dbo.CustInvoiceJournalHeaderEntity T1
WHERE T1.InvoiceAmount < 0

UNION ALL

/* 10. Interest Note Generated */
SELECT 
    Inv.InvoiceId,
    'Interest Note Generated' AS Activity,
    IJ.InterestDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    IJ.CreatedBy AS [User],
    NULL,
    Inv.CompanyCode,
    1
FROM dbo.CustInterestJournalEntity IJ
JOIN InvoiceBasics Inv ON IJ.AccountDisplayValue = Inv.CustomerAccount AND IJ.DataAreaId = Inv.CompanyCode
/* Note: Direct link to specific Invoice ID is often via CustInterestTrans, requiring an additional join */

UNION ALL

/* 11. Partial Payment Posted */
SELECT 
    Inv.InvoiceId,
    'Partial Payment Posted' AS Activity,
    Sett.TransDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Sett.SettleAmountCur AS InvoiceAmount,
    Inv.DueDate,
    Sett.CreatedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CustSettlementEntity Sett
JOIN dbo.CustTransactionEntity Trans ON Sett.TransRecId = Trans.RecId
JOIN InvoiceBasics Inv ON Trans.Invoice = Inv.InvoiceId AND Trans.DataAreaId = Inv.CompanyCode
WHERE ABS(Sett.SettleAmountCur) < ABS(Trans.AmountCur) AND Sett.SettleAmountCur <> 0

UNION ALL

/* 12. Full Payment Received */
SELECT 
    Inv.InvoiceId,
    'Full Payment Received' AS Activity,
    Sett.TransDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Sett.SettleAmountCur AS InvoiceAmount,
    Inv.DueDate,
    Sett.CreatedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CustSettlementEntity Sett
JOIN dbo.CustTransactionEntity Trans ON Sett.TransRecId = Trans.RecId
JOIN InvoiceBasics Inv ON Trans.Invoice = Inv.InvoiceId AND Trans.DataAreaId = Inv.CompanyCode
WHERE ABS(Sett.SettleAmountCur) >= ABS(Trans.AmountCur)

UNION ALL

/* 13. Bank Statement Matched */
SELECT 
    Inv.InvoiceId,
    'Bank Statement Matched' AS Activity,
    Bank.TransDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Bank.AmountCur AS InvoiceAmount,
    Inv.DueDate,
    NULL,
    NULL,
    Inv.CompanyCode,
    1
FROM dbo.BankAccountTransEntity Bank
JOIN dbo.CustSettlementEntity Sett ON Bank.Voucher = Sett.OffsetTransVoucher AND Bank.DataAreaId = Sett.DataAreaId
JOIN dbo.CustTransactionEntity Trans ON Sett.TransRecId = Trans.RecId
JOIN InvoiceBasics Inv ON Trans.Invoice = Inv.InvoiceId

UNION ALL

/* 14. Payment Written Off */
SELECT 
    Inv.InvoiceId,
    'Payment Written Off' AS Activity,
    Trans.TransDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Trans.AmountCur AS InvoiceAmount,
    Inv.DueDate,
    Trans.CreatedBy AS [User],
    NULL,
    Inv.CompanyCode,
    0
FROM dbo.CustTransactionEntity Trans
JOIN InvoiceBasics Inv ON Trans.Invoice = Inv.InvoiceId AND Trans.DataAreaId = Inv.CompanyCode
WHERE Trans.TransactionType = 'WriteOff'

UNION ALL

/* 15. Invoice Cleared */
SELECT 
    Inv.InvoiceId,
    'Invoice Cleared' AS Activity,
    Trans.ClosedDate AS EventTimestamp,
    'D365 F&O' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    Inv.CustomerAccount,
    NULL,
    Inv.InvoiceAmount,
    Inv.DueDate,
    NULL,
    NULL,
    Inv.CompanyCode,
    1
FROM dbo.CustTransactionEntity Trans
JOIN InvoiceBasics Inv ON Trans.Invoice = Inv.InvoiceId AND Trans.DataAreaId = Inv.CompanyCode
WHERE Trans.ClosedDate IS NOT NULL AND Trans.ClosedDate <> '1900-01-01'

Steps

Prerequisites Setup: Ensure Python 3.x is installed on the execution machine. Install the required libraries using pip: pandas, requests, and msal (if using Microsoft Authentication Library, though this script uses direct REST for simplicity).
Azure App Registration: Register an application in Azure Active Directory (Entra ID) to allow access to Dynamics 365. Note down the Client ID, Client Secret, and Tenant ID. Ensure the app has API permissions for Dynamics ERP (OData).
Configure the Script: Open the provided Python script. Update the config dictionary at the top with your specific D365 URL, Client ID, Client Secret, Tenant ID, and the target Company Code (dataAreaId).
Define Date Range: Modify the start_date and end_date variables in the script to define the extraction window. A sliding window of the last 90 to 180 days is recommended for incremental loads.
Execute Extraction: Run the script. It will authenticate against Azure AD, retrieve an access token, and iterate through multiple OData entities (CustomerInvoices, PaymentJournals, CaseDetails, etc.) to fetch data.
Data Processing: The script automatically performs data transformations, such as renaming columns to match the ProcessMind schema, calculating 'Due Date Passed' events based on system date, and distinguishing between Partial and Full payments based on transaction amounts.
Merge and Sort: The script concatenates all activity dataframes into a single event log and sorts them by InvoiceNumber and EventTimestamp.
Export: The final dataframe is exported to a CSV file named AR_Event_Log.csv. Verify this file is created in the script's directory.
Validation: Open the CSV file and check that dates are in ISO format (YYYY-MM-DD HH:MM:SS) and that all required columns are populated.
Upload: Import the generated CSV file into ProcessMind using the standard file upload wizard. Map the columns InvoiceNumber to Case ID, Activity to Activity, and EventTimestamp to Timestamp.

Configuration

Batch Size: The script uses a default page size (often 1000 records) controlled by the server. The script handles pagination automatically using the @odata.nextLink property.
Date Filters: The OData $filter parameter is used to restrict data retrieval to relevant date ranges (e.g., InvoiceDate ge 2023-01-01T00:00:00Z). This significantly improves performance.
Company Filtering: D365 stores data for multiple legal entities. The script filters by dataAreaId to ensure only data for the relevant business unit is extracted.
Authentication: Uses OAuth 2.0 (Client Credentials Flow). You must have a valid Client Secret and App Registration.
Entity Selection: Uses standard entities like CustomerInvoices, CustomerPaymentJournalLines, and CaseDetail. If your organization uses custom entities, the endpoints in the endpoints dictionary must be updated.
Calculated Events: Events like 'Due Date Passed' are calculated during script execution, as they do not generate a specific transaction log in the database.

a Sample Query python

import requests
import pandas as pd
import datetime
from datetime import  timedelta
import json

class=class="hl-string">"hl-comment"># === CONFIGURATION ===
config = {
    class="hl-string">"resource": class="hl-string">"https://[YourOrg].operations.dynamics.com",
    class="hl-string">"tenant_id": class="hl-string">"[Your-Tenant-ID]",
    class="hl-string">"client_id": class="hl-string">"[Your-Client-ID]",
    class="hl-string">"client_secret": class="hl-string">"[Your-Client-Secret]",
    class="hl-string">"company_code": class="hl-string">"USMF",  class=class="hl-string">"hl-comment"># Example Company Code
    class="hl-string">"start_date": class="hl-string">"2023-01-01T00:00:00Z"
}

class=class="hl-string">"hl-comment"># === AUTHENTICATION ===
def get_token(config):
    token_url = fclass="hl-string">"https://login.microsoftonline.com/{config['tenant_id']}/oauth2/token"
    payload = {
        class="hl-string">'grant_type': class="hl-string">'client_credentials',
        class="hl-string">'client_id': config[class="hl-string">'client_id'],
        class="hl-string">'client_secret': config[class="hl-string">'client_secret'],
        class="hl-string">'resource': config[class="hl-string">'resource']
    }
    response = requests.post(token_url, data=payload)
    if response.status_code == 200:
        return response.json().get(class="hl-string">'access_token')
    else:
        raise Exception(fclass="hl-string">"Auth Failed: {response.text}")

class=class="hl-string">"hl-comment"># === ODATA FETCHER ===
def get_odata_data(token, entity_url, filter_str):
    headers = {class="hl-string">'Authorization': fclass="hl-string">'Bearer {token}', class="hl-string">'Accept': class="hl-string">'application/json'}
    results = []
    next_link = fclass="hl-string">"{config['resource']}/data/{entity_url}?$filter={filter_str}&$format=json"
    
    while next_link:
        print(fclass="hl-string">"Fetching: {next_link}")
        try:
            res = requests.get(next_link, headers=headers)
            if res.status_code != 200:
                print(fclass="hl-string">"Error fetching data: {res.text}")
                break
            data = res.json()
            results.extend(data.get(class="hl-string">'value', []))
            next_link = data.get(class="hl-string">'@odata.nextLink', None)
        except Exception as e:
            print(fclass="hl-string">"Exception: {e}")
            break
    return pd.DataFrame(results)

class=class="hl-string">"hl-comment"># === MAIN EXTRACTION ===
def main():
    token = get_token(config)
    all_events = []
    company_filter = fclass="hl-string">"dataAreaId eq '{config['company_code']}'"
    date_filter = fclass="hl-string">"InvoiceDate ge {config['start_date']}"

    class=class="hl-string">"hl-comment"># 1. INVOICE BASIC EVENTS (Posted, Dispatched, Credit Memo, Cleared)
    class=class="hl-string">"hl-comment"># Entity: CustomerInvoices (or SalesInvoiceHeaders)
    print(class="hl-string">"Processing Invoices...")
    df_inv = get_odata_data(token, class="hl-string">"CustomerInvoices", fclass="hl-string">"{company_filter} and {date_filter}")
    
    if not df_inv.empty:
        class=class="hl-string">"hl-comment"># Activity: Invoice Posted
        posted = df_inv.copy()
        posted[class="hl-string">'Activity'] = class="hl-string">'Invoice Posted'
        posted[class="hl-string">'EventTimestamp'] = posted[class="hl-string">'InvoiceDate']
        all_events.append(posted)

        class=class="hl-string">"hl-comment"># Activity: Invoice Dispatched (Assumed same day as posted if not tracked separately)
        dispatched = df_inv.copy()
        dispatched[class="hl-string">'Activity'] = class="hl-string">'Invoice Dispatched'
        dispatched[class="hl-string">'EventTimestamp'] = dispatched[class="hl-string">'InvoiceDate']  class=class="hl-string">"hl-comment"># Often inferred
        all_events.append(dispatched)

        class=class="hl-string">"hl-comment"># Activity: Credit Memo Issued
        credit_memos = df_inv[df_inv[class="hl-string">'InvoiceAmount'] < 0].copy()
        credit_memos[class="hl-string">'Activity'] = class="hl-string">'Credit Memo Issued'
        credit_memos[class="hl-string">'EventTimestamp'] = credit_memos[class="hl-string">'InvoiceDate']
        all_events.append(credit_memos)

        class=class="hl-string">"hl-comment"># Activity: Invoice Cleared (Closed)
        class=class="hl-string">"hl-comment"># Note: ClosedDate might need to be cast to datetime
        cleared = df_inv[df_inv[class="hl-string">'ClosedDate'].notnull()].copy()
        if not cleared.empty:
            cleared[class="hl-string">'Activity'] = class="hl-string">'Invoice Cleared'
            cleared[class="hl-string">'EventTimestamp'] = cleared[class="hl-string">'ClosedDate']
            all_events.append(cleared)

        class=class="hl-string">"hl-comment"># Activity: Due Date Passed (Calculated)
        class=class="hl-string">"hl-comment"># Logic: If not closed, and DueDate < Now
        now = datetime.datetime.now()
        overdue = df_inv[(df_inv[class="hl-string">'ClosedDate'].isnull())].copy()
        class=class="hl-string">"hl-comment"># Convert DueDate to datetime objects for comparison
        overdue[class="hl-string">'DueDate_DT'] = pd.to_datetime(overdue[class="hl-string">'DueDate'])
        overdue = overdue[overdue[class="hl-string">'DueDate_DT'] < now]
        if not overdue.empty:
            overdue[class="hl-string">'Activity'] = class="hl-string">'Due Date Passed'
            class=class="hl-string">"hl-comment"># Timestamp is the day it became overdue
            overdue[class="hl-string">'EventTimestamp'] = overdue[class="hl-string">'DueDate'] 
            all_events.append(overdue)

    class=class="hl-string">"hl-comment"># 2. PAYMENTS (Partial, Full, WriteOff)
    class=class="hl-string">"hl-comment"># Entity: CustomerPaymentJournalLines
    print(class="hl-string">"Processing Payments...")
    class=class="hl-string">"hl-comment"># Note: Filter by TransDate usually
    df_pay = get_odata_data(token, class="hl-string">"CustomerPaymentJournalLines", fclass="hl-string">"{company_filter}")
    
    if not df_pay.empty:
        class=class="hl-string">"hl-comment"># Activity: Partial vs Full Payment
        class=class="hl-string">"hl-comment"># Logic requires comparing payment amount to invoice balance. simplified here based on Amount
        class=class="hl-string">"hl-comment"># Assuming we join with invoices later, strictly classifying based on available fields
        class=class="hl-string">"hl-comment"># Here we assume a field class="hl-string">'PaymentStatus' or we treat all as Partial unless marked Settled
        
        class=class="hl-string">"hl-comment"># Full Payment
        full_pay = df_pay.copy()
        full_pay[class="hl-string">'Activity'] = class="hl-string">'Full Payment Received'
        full_pay[class="hl-string">'EventTimestamp'] = full_pay[class="hl-string">'TransDate']
        all_events.append(full_pay)
        
        class=class="hl-string">"hl-comment"># Partial Payment (In a real scenario, this requires logic checking settlement vs invoice total)
        class=class="hl-string">"hl-comment"># Adding a duplicate row for demonstration of structure
        partial_pay = df_pay[df_pay[class="hl-string">'Amount'] < 1000].copy() class=class="hl-string">"hl-comment"># Placeholder logic
        partial_pay[class="hl-string">'Activity'] = class="hl-string">'Partial Payment Posted'
        partial_pay[class="hl-string">'EventTimestamp'] = partial_pay[class="hl-string">'TransDate']
        all_events.append(partial_pay)

        class=class="hl-string">"hl-comment"># Activity: Payment Written Off
        class=class="hl-string">"hl-comment"># Filter by specific Transaction Type or Journal Name
        write_off = df_pay[df_pay[class="hl-string">'JournalName'].str.contains(class="hl-string">'WriteOff', case=False, na=False)].copy()
        write_off[class="hl-string">'Activity'] = class="hl-string">'Payment Written Off'
        write_off[class="hl-string">'EventTimestamp'] = write_off[class="hl-string">'TransDate']
        all_events.append(write_off)

    class=class="hl-string">"hl-comment"># 3. COLLECTIONS (Reminders, Promise to Pay)
    class=class="hl-string">"hl-comment"># Entity: CollectionLetterJournals
    print(class="hl-string">"Processing Collections...")
    df_col = get_odata_data(token, class="hl-string">"CollectionLetterJournals", fclass="hl-string">"{company_filter}")
    if not df_col.empty:
        reminders = df_col.copy()
        reminders[class="hl-string">'Activity'] = class="hl-string">'Payment Reminder Sent'
        reminders[class="hl-string">'EventTimestamp'] = reminders[class="hl-string">'LetterDate']
        all_events.append(reminders)

    class=class="hl-string">"hl-comment"># Entity: CustomerPromiseToPay
    df_promise = get_odata_data(token, class="hl-string">"CustomerPromiseToPay", fclass="hl-string">"{company_filter}")
    if not df_promise.empty:
        promises = df_promise.copy()
        promises[class="hl-string">'Activity'] = class="hl-string">'Promise to Pay Received'
        promises[class="hl-string">'EventTimestamp'] = promises[class="hl-string">'CreatedDateTime']
        all_events.append(promises)

    class=class="hl-string">"hl-comment"># 4. DISPUTES (Opened, Review, Resolved)
    class=class="hl-string">"hl-comment"># Entity: CaseDetails (requires filtering for Collections/Dispute category)
    print(class="hl-string">"Processing Disputes...")
    df_case = get_odata_data(token, class="hl-string">"CaseDetails", fclass="hl-string">"{company_filter} and Category eq 'Collections'")
    if not df_case.empty:
        class=class="hl-string">"hl-comment"># Opened
        opened = df_case.copy()
        opened[class="hl-string">'Activity'] = class="hl-string">'Dispute Case Opened'
        opened[class="hl-string">'EventTimestamp'] = opened[class="hl-string">'CreatedDateTime']
        all_events.append(opened)

        class=class="hl-string">"hl-comment"># Under Review (In Process)
        in_process = df_case[df_case[class="hl-string">'Status'] == class="hl-string">'InProcess'].copy()
        in_process[class="hl-string">'Activity'] = class="hl-string">'Dispute Under Review'
        in_process[class="hl-string">'EventTimestamp'] = in_process[class="hl-string">'ModifiedDateTime']
        all_events.append(in_process)

        class=class="hl-string">"hl-comment"># Resolved (Closed)
        resolved = df_case[df_case[class="hl-string">'Status'] == class="hl-string">'Closed'].copy()
        resolved[class="hl-string">'Activity'] = class="hl-string">'Dispute Resolved'
        resolved[class="hl-string">'EventTimestamp'] = resolved[class="hl-string">'ClosedDateTime']
        all_events.append(resolved)

    class=class="hl-string">"hl-comment"># 5. INTEREST
    class=class="hl-string">"hl-comment"># Entity: InterestNotes
    print(class="hl-string">"Processing Interest Notes...")
    df_interest = get_odata_data(token, class="hl-string">"InterestNotes", fclass="hl-string">"{company_filter}")
    if not df_interest.empty:
        interest = df_interest.copy()
        interest[class="hl-string">'Activity'] = class="hl-string">'Interest Note Generated'
        interest[class="hl-string">'EventTimestamp'] = interest[class="hl-string">'NoteDate']
        all_events.append(interest)

    class=class="hl-string">"hl-comment"># 6. BANK RECONCILIATION
    class=class="hl-string">"hl-comment"># Entity: BankReconciliationLines (Placeholder for matching)
    print(class="hl-string">"Processing Bank Reconciliation...")
    class=class="hl-string">"hl-comment"># This entity availability varies by version
    df_bank = get_odata_data(token, class="hl-string">"BankReconciliationLines", fclass="hl-string">"{company_filter}")
    if not df_bank.empty:
        matched = df_bank[df_bank[class="hl-string">'Reconciled'] == True].copy()
        matched[class="hl-string">'Activity'] = class="hl-string">'Bank Statement Matched'
        matched[class="hl-string">'EventTimestamp'] = matched[class="hl-string">'TransactionDate']
        class=class="hl-string">"hl-comment"># Need to join with Invoice via Payment Reference ideally
        class=class="hl-string">"hl-comment"># For this script, we assume InvoiceNumber is present or mapped
        all_events.append(matched)

    class=class="hl-string">"hl-comment"># === UNIFICATION & EXPORT ===
    if not all_events:
        print(class="hl-string">"No events extracted.")
        return

    final_df = pd.concat(all_events, ignore_index=True)

    class=class="hl-string">"hl-comment"># Map columns to schema
    class=class="hl-string">"hl-comment"># Note: Column names class="hl-string">'InvoiceNumber', class="hl-string">'InvoiceAccount' etc. depend on exact Entity schema
    class=class="hl-string">"hl-comment"># You may need to rename based on your D365 entity fields, e.g., class="hl-string">'InvoiceNum' -> class="hl-string">'InvoiceNumber'
    schema_map = {
        class="hl-string">'InvoiceNumber': class="hl-string">'InvoiceNumber',
        class="hl-string">'InvoiceId': class="hl-string">'InvoiceNumber', class=class="hl-string">"hl-comment"># Alternative common name
        class="hl-string">'InvoiceAccount': class="hl-string">'CustomerAccount',
        class="hl-string">'CustomerAccount': class="hl-string">'CustomerAccount',
        class="hl-string">'InvoiceAmount': class="hl-string">'InvoiceAmount',
        class="hl-string">'Amount': class="hl-string">'InvoiceAmount', class=class="hl-string">"hl-comment"># Fallback for payments
        class="hl-string">'CurrencyCode': class="hl-string">'Currency',
        class="hl-string">'DueDate': class="hl-string">'DueDate',
        class="hl-string">'CreatedBy': class="hl-string">'User',
        class="hl-string">'BusinessUnit': class="hl-string">'BusinessUnit',
        class="hl-string">'DataAreaId': class="hl-string">'CompanyCode'
    }
    
    class=class="hl-string">"hl-comment"># Normalize columns
    for col in final_df.columns:
        if col in schema_map:
            final_df.rename(columns={col: schema_map[col]}, inplace=True)

    class=class="hl-string">"hl-comment"># Add constant fields
    final_df[class="hl-string">'SourceSystem'] = class="hl-string">'Microsoft D365'
    final_df[class="hl-string">'LastDataUpdate'] = datetime.datetime.now().isoformat()
    
    class=class="hl-string">"hl-comment"># Select final columns (fill missing with None)
    required_cols = [
        class="hl-string">'InvoiceNumber', class="hl-string">'Activity', class="hl-string">'EventTimestamp', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate',
        class="hl-string">'CustomerAccount', class="hl-string">'CustomerGroup', class="hl-string">'InvoiceAmount', class="hl-string">'DueDate', class="hl-string">'User',
        class="hl-string">'BusinessUnit', class="hl-string">'CompanyCode', class="hl-string">'IsAutomated'
    ]
    
    for col in required_cols:
        if col not in final_df.columns:
            final_df[col] = None
            
    output_df = final_df[required_cols]
    
    class=class="hl-string">"hl-comment"># Export
    output_df.to_csv(class="hl-string">"AR_Event_Log.csv", index=False)
    print(class="hl-string">"Extraction complete. Saved to AR_Event_Log.csv")

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

Your Accounts Receivable Data Template

Your Accounts Receivable Data Template

Accounts Receivable Attributes

Accounts Receivable Activities

Extraction Guides

D365 Data Management Framework Entity Export

Steps

Configuration

a Sample Query config

Python OData API Extraction for AR Events

Steps

Configuration

a Sample Query python

Ready to Get Started?

Optimize Your Accounts Receivable and Boost Cash Flow Now

Your Accounts Receivable Data Template

Your Accounts Receivable Data Template

Accounts Receivable Attributes

Accounts Receivable Activities

Extraction Guides

D365 Data Management Framework Entity Export

Steps

Configuration

a Sample Query config

Python OData API Extraction for AR Events

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Optimize Your Accounts Receivable and Boost Cash Flow Now