> Continuous Process Improvement > Credit Management & Collections > Microsoft Dynamics 365 > Data Template

Your Credit Management & Collections Data Template

Microsoft Dynamics 365

Credit Management & Collections Attributes (20) Credit Management & Collections Activities (12) Extraction Guides (2)

Your Credit Management & Collections Data Template

This template provides a comprehensive guide to collecting the necessary data for analyzing your credit management and collections process. It outlines essential data attributes, key activities to track, and practical guidance for data extraction. Use this resource to streamline your data preparation and accelerate your process mining journey.

Recommended attributes to collect for comprehensive analysis
Key process activities to track for accurate discovery
Practical guidance for data extraction from your system

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

Download Template

Credit Management & Collections Attributes

These are the recommended data fields to include in your event log for a comprehensive analysis of your credit management and collections process.

3 Required 8 Recommended 9 Optional

	Name	Description
	Activity Name ActivityName	The name of the specific event or task that occurred at a point in the invoice lifecycle.
Description The Activity Name describes a specific step or event within the credit and collections process, such as 'Invoice Generated', 'Dunning Letter Sent', or 'Payment Received'. This attribute is fundamental to process mining as it defines the nodes in the process map. Analyzing the sequence and frequency of these activities reveals the actual process flow, allowing for the identification of rework loops, inefficiencies, and non-compliant process paths. It directly supports the creation of all process flow visualizations and is used to segment cycle time calculations between different stages of the process. Why it matters This attribute defines the steps in the process map, making it possible to visualize and analyze the invoice lifecycle. Where to get This attribute is typically derived by mapping specific system events, status changes, or record creation dates from various tables like 'CustInvoiceJour', 'CustTrans', 'CustCollectionLetterJour', and 'CustPaymPromise' to standardized activity names. Examples Invoice Posted and SentDunning Letter GeneratedDispute RegisteredPayment Received
	Event Time EventTime	The timestamp indicating when a specific activity or event occurred.
Description Event Time is the precise date and time that an activity was recorded in the system. It is a cornerstone of process mining, providing the chronological order necessary to build the process flow and calculate all time-based metrics. This attribute is essential for analyzing durations, identifying bottlenecks, and monitoring SLAs. For instance, the time difference between the 'Invoice Generated' and 'Payment Received' events is used to calculate Days Sales Outstanding (DSO), a critical business KPI. Without accurate timestamps, performance analysis and bottleneck identification are impossible. Why it matters This timestamp is essential for ordering events, discovering the process map, and calculating all performance metrics like cycle times and durations. Where to get This attribute is sourced from various date/time fields across D365 tables, such as 'CreatedDateTime' on 'CustInvoiceJour' or 'TransDate' on 'CustTrans', depending on the specific activity. Examples 2023-01-15T09:30:00Z2023-02-28T14:12:55Z2023-03-20T11:05:10Z
	Invoice Number InvoiceNumber	The unique identifier for each customer invoice, serving as the primary case identifier for the credit management process.
Description The Invoice Number is a critical attribute that uniquely identifies each financial transaction with a customer. It links all related activities, from invoice generation and posting to payment collection and final settlement. In process mining, this number is used to reconstruct the end-to-end journey of each invoice, allowing for a detailed analysis of its lifecycle. By tracking each invoice as a separate case, organizations can identify common process paths, bottlenecks, and deviations from the standard procedure. It is the foundation for calculating key metrics like Days Sales Outstanding and analyzing process variants. Why it matters This is the essential Case ID that connects all process events, enabling the reconstruction and analysis of the entire invoice-to-cash lifecycle. Where to get This is typically the 'Invoice' field from the 'CustInvoiceJour' table in Microsoft Dynamics 365 Finance. Examples INV-0012345CIV-2023-8876SI-9510034
	Collector Assigned CollectorAssigned	The name of the collection agent or team member responsible for managing the overdue invoice.
Description This attribute identifies the individual employee responsible for collection activities on a specific invoice. It is crucial for performance and workload analysis. By filtering the process map or dashboards by collector, managers can assess individual productivity, compare collection strategies, and identify coaching opportunities. This attribute is a key dimension for the 'Collection Team Productivity' and 'Dispute Resolution Cycle Time' dashboards, as it helps attribute process outcomes to specific individuals or teams. Why it matters Enables performance analysis of the collections team, helps in balancing workloads, and identifies best practices by comparing collector effectiveness. Where to get Consult Microsoft Dynamics 365 documentation. This could be linked from a collections management module or user responsibility fields on customer or transaction records. Examples John SmithEmily JonesCollections Team A
	Customer Name CustomerName	The legal name of the customer to whom the invoice was issued.
Description The Customer Name identifies the specific client associated with an invoice. This attribute is a primary dimension for filtering and segmentation. Analyzing the process by customer helps identify problematic clients who consistently pay late, raise disputes, or require extensive collection efforts. It is a key attribute for the 'Dispute Resolution Cycle Time' dashboard and for any deep-dive analysis into specific customer accounts. Why it matters Enables customer-specific process analysis, helping to identify patterns and manage relationships with key or problematic accounts. Where to get This information is typically joined from the 'CustTable' using the customer account number present in 'CustInvoiceJour'. Examples Contoso Ltd.Adventure WorksFabrikam Inc.
	Customer Segment CustomerSegment	A classification of the customer, such as by size, industry, or strategic importance.
Description Customer Segment is a categorical attribute used to group customers into meaningful cohorts, for example 'Strategic Accounts', 'SMB', or 'Government'. This segmentation is vital for strategic analysis. It helps answer questions like 'Do strategic accounts have a lower DSO?' or 'Are write-offs more common in a particular industry segment?'. It is used directly in the 'Days Sales Outstanding Trend', 'Uncollectible Invoice Write-Offs', and 'Overdue Invoices By Segment' dashboards to provide higher-level insights beyond individual customers. Why it matters Allows for aggregated analysis across customer groups to identify strategic trends and tailor collection strategies for different segments. Where to get This is often a custom field or derived from attributes on the 'CustTable' (customer master table). Examples EnterpriseMid-MarketPublic SectorPartner
	Days Overdue DaysOverdue	The number of days an invoice is past its payment due date.
Description Days Overdue is a calculated metric that measures the time elapsed since the Payment Due Date for an unpaid invoice. It is calculated as the current date minus the Payment Due Date. This is a key performance indicator for the collections team, as it helps prioritize which invoices to focus on. It is the primary metric in the 'Overdue Invoices By Segment' dashboard and is fundamental for assessing the health of accounts receivable and the effectiveness of collection efforts. Why it matters This is a critical operational metric used to prioritize collection efforts and measure the severity of payment delays. Where to get This is a calculated field. The logic is: IF Invoice is unpaid THEN (Today() - PaymentDueDate) ELSE 0. Examples 1532910
	Dunning Level DunningLevel	Indicates the current stage or severity of the dunning (collections) process for an overdue invoice.
Description The Dunning Level represents the step in a structured dunning procedure, for example, 'Level 1: Gentle Reminder', 'Level 2: Formal Notice', or 'Level 3: Final Warning'. Tracking this attribute is essential for evaluating the success of the collections strategy. The 'Dunning Strategy Effectiveness' dashboard uses this attribute to show what percentage of invoices are paid after reaching each level. This helps businesses refine their dunning workflows, making them more effective at securing payment while maintaining customer relationships. Why it matters This is key to measuring the effectiveness of the dunning strategy and understanding at which stage customers are most likely to pay. Where to get This information would be found in tables related to collections and dunning letters, such as 'CustCollectionLetterJour'. Examples 123 - FinalLegal Action
	Invoice Amount InvoiceAmount	The total monetary value of the invoice.
Description Invoice Amount represents the total financial value of the goods or services billed. This is a fundamental attribute for financial analysis within the process. It allows for segmenting cases by value to see if high-value invoices follow a different process or experience longer delays. This attribute is essential for the 'Uncollectible Invoice Write-Offs' and 'Credit Limit Management Analysis' dashboards, helping to quantify financial impact and assess risk. Why it matters Allows for financial impact analysis, prioritization of high-value invoices, and understanding how invoice value affects process behavior. Where to get Likely the 'InvoiceAmount' field in the 'CustInvoiceJour' table. Examples 1500.7525000.00549.99
	Invoice Status InvoiceStatus	The current status of the invoice in its lifecycle.
Description Invoice Status provides a snapshot of where an invoice is in the process, such as 'Open', 'Paid', 'Disputed', or 'Written Off'. While the activity log provides a detailed history, the current status is a useful case-level attribute for filtering and high-level reporting. It allows analysts to quickly segment the data to focus only on open overdue invoices or to analyze the characteristics of all written-off invoices. It's a simple yet powerful way to understand the current state of accounts receivable. Why it matters Provides a quick and easy way to filter for and analyze invoices based on their current state, such as focusing on all open or disputed cases. Where to get Derived from the settlement status of transactions in the 'CustTrans' table. An open transaction is unpaid, while a closed one is settled. Examples OpenPaidPartially PaidWritten Off
	Payment Due Date PaymentDueDate	The date by which the invoice payment is contractually due.
Description The Payment Due Date is a critical date attribute defined by the payment terms agreed upon with the customer. This date serves as the baseline for determining if an invoice is overdue. It is the primary trigger for initiating collection activities, such as sending reminders or starting a formal dunning procedure. In process mining, it's used to calculate the 'Days Overdue' metric and to check for compliance with dunning policies, for instance, verifying that a reminder was sent within X days after the due date passed. Why it matters This date is the benchmark for measuring payment timeliness and is the trigger for all overdue and collections-related activities. Where to get Typically found as the 'DueDate' field in the 'CustTrans' or 'CustInvoiceJour' tables. Examples 2023-02-142023-03-312023-04-30
	Country Country	The country of the customer's billing address.
Description This attribute specifies the country associated with the customer's account. It is a common dimension for geographical analysis. Segmenting process KPIs like DSO or write-off rates by country can reveal regional differences in customer payment behavior, collection effectiveness, or economic conditions. This can lead to geographically tailored collection strategies or different payment terms for certain regions. Why it matters Enables geographical analysis to identify regional trends in payment behavior and collection process performance. Where to get This is derived by joining from the customer master record ('CustTable') to its primary address record, often in 'LogisticsPostalAddress'. Examples USADEUGBRCAN
	Credit Limit CreditLimit	The maximum amount of credit approved for the customer.
Description The Credit Limit is the total amount of debt a customer is authorized to accrue. This attribute is crucial for risk management. The 'Credit Limit Management Analysis' dashboard compares this value against the invoice amounts for delinquent or written-off accounts. This analysis helps determine if credit limits are set appropriately, if they are being enforced, and whether customers who default tend to have recently increased limits, highlighting potential weaknesses in the credit approval process. Why it matters Essential for risk assessment, this attribute helps analyze if write-offs are related to poorly managed or inadequate customer credit limits. Where to get This value is typically stored on the customer master record in the 'CustTable'. Examples 10000.0050000.000.00
	Currency Code CurrencyCode	The currency of the invoice, for example, USD or EUR.
Description The Currency Code specifies the transactional currency of the Invoice Amount. This is an important contextual attribute, especially for multinational organizations. It is necessary for correctly interpreting financial values and may be used to analyze if payment cycles or issues are correlated with specific currencies, which could be related to complexities in foreign exchange or international payment processing. Why it matters Provides essential context for all monetary values and allows for analysis of processes involving multiple currencies. Where to get Typically found as the 'CurrencyCode' field on the 'CustInvoiceJour' table. Examples USDEURGBPCAD
	Days Sales Outstanding DaysSalesOutstanding	The time in days from invoice generation to payment receipt.
Description Days Sales Outstanding (DSO) is a key financial KPI that measures the average number of days it takes to collect payment after a sale has been made. In this process model, it is calculated as the duration between the 'Invoice Generated' and 'Payment Received' activities for each invoice. A lower DSO indicates a more efficient collections process and better cash flow. This attribute allows for trend analysis over time and segmentation by dimensions like Customer Segment to see which groups are impacting the overall metric. Why it matters This is a critical KPI for measuring cash flow efficiency and the overall performance of the invoice-to-cash process. Where to get Calculated as the duration between the timestamps of the 'Invoice Generated' and 'Payment Received' activities. Examples 28.545.261.0
	Dispute Reason DisputeReason	The reason provided by the customer for disputing an invoice.
Description When a customer disputes an invoice, they typically provide a reason, such as 'Incorrect Pricing', 'Damaged Goods', or 'Duplicate Billing'. This attribute captures that reason. Analyzing dispute reasons is critical for root cause analysis. It helps identify recurring problems in the order-to-cash process, such as issues with the sales, shipping, or billing departments. Reducing disputes at the source is a key outcome of this analysis, which directly impacts DSO by preventing payment delays. Why it matters Provides critical data for root cause analysis of payment delays, helping to identify and fix upstream issues that lead to invoice disputes. Where to get Consult Microsoft Dynamics 365 documentation. This would be part of the dispute management or case management functionality. Examples Pricing ErrorQuantity ShortProduct Not ReceivedService Not As Described
	Dispute Resolution Time DisputeResolutionTime	The time taken to resolve a customer dispute from when it was first registered.
Description Dispute Resolution Time measures the efficiency of the dispute handling process. It is calculated as the duration between the 'Dispute Registered' and 'Dispute Resolved' activities. Long resolution times can delay payment and negatively impact customer satisfaction. Analyzing this metric, segmented by collector or dispute reason, helps identify bottlenecks and areas for improvement in the dispute management workflow. Why it matters Measures the efficiency of the dispute handling process, which is a common cause of significant payment delays. Where to get Calculated as the duration between the timestamps of the 'Dispute Registered' and 'Dispute Resolved' activities. Examples 7.215.83.5
	Is Disputed IsDisputed	A boolean flag indicating if an invoice has ever been disputed.
Description This is a calculated flag that is set to true if an invoice case contains a 'Dispute Registered' activity. It simplifies analysis by allowing users to easily filter or compare the process flows of disputed versus non-disputed invoices. For example, one could easily compare the average DSO for disputed invoices against the average for non-disputed ones to quantify the impact of disputes on cash flow. It's a useful attribute for high-level dashboarding and comparative analysis. Why it matters Simplifies analysis and filtering by allowing for easy comparison between disputed and non-disputed invoice populations. Where to get This is a calculated field. The logic is: IF the case contains a 'Dispute Registered' activity THEN true ELSE false. Examples truefalse
	Last Data Update LastDataUpdate	The timestamp of the last data refresh or update.
Description This attribute indicates the last time the dataset was updated from the source system. It is a critical piece of metadata for users of the process mining analysis, as it provides context on the freshness of the data. Knowing the data is current up to a specific point in time helps analysts make informed decisions and understand the scope of their findings. It is typically applied to the entire dataset during the data ingestion pipeline. Why it matters Informs users about the timeliness of the data, ensuring analyses are based on an understood timeframe and preventing decisions on stale information. Where to get This value is generated and stamped onto the dataset by the ETL/data pipeline at the time of execution. Examples 2023-04-01T02:00:00Z2023-04-02T02:00:00Z
	Source System SourceSystem	Identifies the system from which the data originates.
Description This attribute specifies the source application where the event data was recorded. In this context, it would be 'Microsoft Dynamics 365'. While it may seem static in a single-system analysis, it is crucial for data governance, troubleshooting, and future integrations. If data from other systems, like a separate CRM or a collection agency portal, were to be included, this field would be essential for distinguishing between them and understanding the complete cross-system process. Why it matters Provides clear data lineage and is essential for maintaining data quality and enabling analysis across multiple integrated systems. Where to get This is typically a static value added during the data extraction process to label the origin of the records. Examples Microsoft Dynamics 365D365 F&O

Required Recommended Optional

Credit Management & Collections Activities

These are the key process steps and milestones to capture in your event log for accurate discovery of your credit management and collections workflow.

9 Recommended 3 Optional

	Activity	Description
	Dispute Registered	Signifies that the customer has formally disputed the invoice, and its status is updated accordingly. This is typically captured by a user changing the invoice's status to 'Disputed' in the collections management module.
Why it matters This is the starting point for the dispute resolution process. Tracking this helps measure the frequency of disputes and the time taken to resolve them, impacting customer satisfaction and cash flow. Where to get Inferred from a status change on the customer transaction (CustTrans) or a related dispute management table (smmCaseDetail). A timestamped log of the status change is required. Capture Identify when the dispute status field for an invoice transaction is populated or changed to 'Disputed'. Event type inferred
	Dunning Letter Generated	Indicates that a formal dunning or collection letter has been created for the overdue invoice. This is captured when the periodic dunning process is run in Dynamics 365, which generates letter records for relevant invoices.
Why it matters Tracks the initiation and frequency of formal collection efforts. It is essential for measuring dunning effectiveness and compliance with internal collection policies. Where to get An explicit record is created in the Dunning History table (CustCollectionLetterJour). The creation date of the dunning letter journal is the timestamp. Capture Extract creation events from the CustCollectionLetterJour table, linking back to the invoice. Event type explicit
	Invoice Generated	Marks the creation of a sales invoice record in the system, before it is officially posted. This activity is typically an explicit event captured when a user finalizes a sales order and generates the corresponding invoice document.
Why it matters This is the primary start event for the invoice lifecycle. Analyzing the time from this point to payment is crucial for measuring Days Sales Outstanding (DSO). Where to get Recorded in the Sales Invoice Header table (SalesInvoiceHeader) or Sales Table (SalesTable) upon invoice creation. The creation timestamp of the record serves as the event time. Capture Capture the creation timestamp of the sales invoice record. Event type explicit
	Invoice Posted and Sent	Represents the official recording of the invoice in the general ledger, making it a formal accounts receivable. This event usually coincides with the invoice being sent to the customer and is captured when the posting routine is completed in Dynamics 365.
Why it matters This is a critical milestone that officially starts the clock for payment terms and aging. Delays between generation and posting can hide inefficiencies in the billing process. Where to get Inferred from the posting date on the Customer Invoice Journal (CustInvoiceJour) or a status change to 'Posted'. The posting timestamp is the key data point. Capture Use the posting timestamp from the Customer Invoice Journal (CustInvoiceJour). Event type inferred
	Invoice Settled	The invoice is fully paid and its balance is zero, marking the successful completion of the process. This status is inferred when the sum of applied payments equals the total invoice amount.
Why it matters This is the primary successful end point for the credit-to-cash process. Analyzing paths that lead here highlights best practices and efficient process variants. Where to get Inferred from the settlement date in the customer settlement table (CustSettlement). This occurs when the 'AmountCur' and 'SettleAmountCur' fields for the invoice in CustTrans sum to zero. Capture Identify the latest settlement date associated with the invoice transaction that makes its balance zero. Event type inferred
	Invoice Written Off	The invoice has been deemed uncollectible and is formally written off as bad debt. This is an explicit action performed by an authorized user, which clears the receivable and posts the loss.
Why it matters This is the primary unsuccessful end point of the process. Tracking write-offs is critical for understanding credit risk, collection failures, and financial losses. Where to get Captured from a specific general journal transaction used for writing off bad debt, linked to the original invoice. The posting date of the write-off journal serves as the timestamp. Capture Identify journal entries with a specific write-off posting profile that reference the invoice. Event type explicit
	Payment Due Date Passed	A calculated event that occurs when the current date surpasses the invoice's due date. This activity does not correspond to a direct user action but is derived by comparing the system date to the invoice due date field.
Why it matters This event is the trigger for all collection activities. It allows for the analysis of overdue invoice volume and the timeliness of dunning actions, supporting KPIs like Dunning Policy Adherence. Where to get Calculated by comparing the current timestamp against the 'DueDate' field in the Customer Transactions table (CustTrans). The event timestamp is the DueDate itself. Capture Create an event when 'NOW()' is greater than the invoice's DueDate. Event type calculated
	Payment Posted	Represents the official posting of the customer payment to the general ledger and its application to settle an invoice. This is captured from the posting date of the customer payment journal.
Why it matters Finalizes the payment part of the process. The time between payment receipt and posting, the Cash Application Lag, is a key efficiency metric for the finance department. Where to get Captured from the posting timestamp of the Customer Payment Journal (LedgerJournalTrans). This confirms the payment has been fully processed in the ledger. Capture Use the posting date from the posted Customer Payment Journal. Event type explicit
	Payment Received	Indicates that a payment from a customer has been received and entered into the system, typically via a payment journal. This event precedes the final posting and application of the cash to the specific invoice.
Why it matters This is a crucial milestone for calculating DSO and understanding cash flow. The time lag between this event and Payment Posted reveals bottlenecks in the cash application process. Where to get An explicit event captured from the creation date of a customer payment journal line (LedgerJournalTrans) before it is posted. It represents the date the payment was recorded as received. Capture Use the transaction date from the Customer Payment Journal lines. Event type explicit
	Collection Activity Logged	Represents a manual collection action, such as a phone call or email, logged against the customer or invoice. This is captured when a collector uses the Activities feature within the collections workspace to record their interactions.
Why it matters Provides visibility into the manual effort of the collections team. Analyzing these activities against payment success helps measure team productivity and the effectiveness of different contact methods. Where to get Logged in the Activities (smmActivities) or related case management tables associated with the customer account. Requires linking the activity back to a specific invoice if possible. Capture Capture creation of Activity records where the type is 'Phone Call' or 'Email' related to collections. Event type explicit
	Dispute Resolved	Marks the conclusion of the dispute process where a resolution has been reached and documented. This event is captured when a user updates the dispute status of an invoice to 'Resolved' or closes the associated dispute case.
Why it matters This activity concludes the dispute sub-process. The duration between dispute registration and resolution is a key KPI for measuring the efficiency of the resolution team. Where to get Inferred from a status change on the customer transaction (CustTrans) or when the related dispute case (smmCaseDetail) is closed. The timestamp of this status change is the event time. Capture Identify when the dispute status field for an invoice is cleared or changed to 'Resolved'. Event type inferred
	Payment Promise Created	A customer has committed to making a payment on a specific future date, and this promise is recorded in the system. This event is logged explicitly when a collections agent enters a promise-to-pay detail in the collections module.
Why it matters Tracks informal payment agreements and their fulfillment rates. Comparing promises to actual payments can help in forecasting cash flow and evaluating customer reliability. Where to get Likely recorded in tables related to the collections workspace or case management, specifically for promise-to-pay functionality. The creation date of the promise record is the event time. Capture Capture creation events from a promise-to-pay or collections case detail table. Event type explicit

Recommended Optional

Extraction Guides

How to get your data from Microsoft Dynamics 365

Steps

Prerequisites Verification: Ensure that your organization has Microsoft Dynamics 365 Finance and that the Bring Your Own Database (BYOD) feature is configured and actively exporting data entities to an Azure SQL Database. You must have read access credentials to this Azure SQL Database.
Identify Data Entities: Confirm that the necessary data entities are being exported from Dynamics 365 to your BYOD. Key entities for this process include: CustInvoiceJour, CustTrans, CustTable, CustCollectionLetterJour, CustSettlement, smmActivities, CustPromisedPayment, and CustDispute. If any are missing, configure them for export in the Dynamics 365 Data Management workspace.
Connect to the BYOD Database: Use a SQL client, such as SQL Server Management Studio (SSMS) or Azure Data Studio, to connect to the Azure SQL Database that serves as your BYOD destination.
Prepare the SQL Query: Copy the complete T-SQL query provided in the query section of this document into your SQL client. This query is designed to gather all required activities and attributes into a single event log format.
Set Query Parameters: Locate the placeholders at the top of the query. Replace '{CompanyCode}' with your target Dynamics 365 legal entity ID, for example, 'USMF'. Replace '{StartDate}' and '{EndDate}' with the desired date range for the analysis, formatted as 'YYYY-MM-DD'.
Execute the Query: Run the modified query against the BYOD database. The execution time will vary depending on the volume of data in the selected date range.
Review the Results: Once the query completes, the results pane will display the event log. Each row represents a specific activity in the credit and collections lifecycle for an invoice. Review the data for completeness and correctness.
Export the Event Log: Export the query results to a CSV file. Most SQL clients have a built-in function to export query results. Right-click the results grid and look for an option like 'Save Results As' or 'Export'.
Prepare for Upload: Ensure the exported CSV file has UTF-8 encoding and that the column headers match the required attributes: InvoiceNumber, ActivityName, EventTime, and any recommended attributes you included. The file is now ready to be uploaded to ProcessMind.

Configuration

Prerequisites: Access to a Microsoft Dynamics 365 for Finance instance with the Bring Your Own Database (BYOD) feature configured and running. Read permissions are required for the target Azure SQL Database.
Data Entities: The extraction relies on several standard data entities being exported to the BYOD. Ensure at a minimum that CustInvoiceJour, CustTrans, CustTable, and CustSettlement are included in your BYOD export job. Additional entities like CustCollectionLetterJour are needed for specific activities.
Date Range: It is critical to filter the query by a specific date range to manage performance. A period of 3 to 6 months of data is recommended for an initial analysis. The provided query uses '{StartDate}' and '{EndDate}' placeholders for this purpose.
Company Filter: The query must be filtered to a specific legal entity (Company Code) to ensure data consistency. Use the '{CompanyCode}' placeholder to specify the company you are analyzing.
Performance Considerations: For very large datasets spanning multiple years or companies, query execution time can be significant. Consider running the query during off-peak hours or applying additional filters to limit the data scope. Ensure proper indexes exist on date and key columns in the BYOD tables.

a Sample Query sql

WITH BaseInvoices AS (
    SELECT 
        cj.INVOICEID AS InvoiceNumber,
        cj.INVOICEDATE AS InvoiceDate,
        cj.DUEDATE AS PaymentDueDate,
        cj.INVOICEAMOUNTMST AS InvoiceAmount,
        ct.NAME AS CustomerName,
        ct.CUSTGROUP AS CustomerSegment,
        -- The collector assignment can be complex. This is a common approach.
        -- It assumes the collector is assigned at the customer level in CustTable.
        -- Your configuration may differ, for example using collection pools.
        col.NAME AS CollectorAssigned,
        cj.DATAAREAID
    FROM 
        dbo.CustInvoiceJour cj
    JOIN 
        dbo.CustTable ct ON cj.INVOICECUST = ct.ACCOUNTNUM AND cj.DATAAREAID = ct.DATAAREAID
    LEFT JOIN 
        -- Placeholder for how collector might be identified. You may need to join to a different table.
        dbo.CollectionsAgent col ON ct.COLLECTIONAGENT = col.AGENTID
    WHERE 
        cj.DATAAREAID = '{CompanyCode}'
        AND cj.INVOICEDATE BETWEEN '{StartDate}' AND '{EndDate}'
)

-- 1. Invoice Generated
SELECT 
    bi.InvoiceNumber AS InvoiceNumber,
    'Invoice Generated' AS ActivityName,
    cj.CREATEDDATETIME AS EventTime,
    bi.CollectorAssigned,
    bi.InvoiceAmount,
    bi.CustomerName,
    bi.CustomerSegment,
    bi.PaymentDueDate,
    0 AS DunningLevel, 
    'Generated' AS InvoiceStatus,
    0 AS DaysOverdue
FROM 
    BaseInvoices bi
JOIN
    dbo.CustInvoiceJour cj ON bi.InvoiceNumber = cj.INVOICEID AND bi.DATAAREAID = cj.DATAAREAID

UNION ALL

-- 2. Invoice Posted and Sent
SELECT 
    bi.InvoiceNumber AS InvoiceNumber,
    'Invoice Posted and Sent' AS ActivityName,
    bi.InvoiceDate AS EventTime,
    bi.CollectorAssigned,
    bi.InvoiceAmount,
    bi.CustomerName,
    bi.CustomerSegment,
    bi.PaymentDueDate,
    0 AS DunningLevel,
    'Posted' AS InvoiceStatus,
    0 AS DaysOverdue
FROM 
    BaseInvoices bi

UNION ALL

-- 3. Payment Due Date Passed
SELECT
    bi.InvoiceNumber AS InvoiceNumber,
    'Payment Due Date Passed' AS ActivityName,
    bi.PaymentDueDate AS EventTime, -- The event happens on the due date
    bi.CollectorAssigned,
    bi.InvoiceAmount,
    bi.CustomerName,
    bi.CustomerSegment,
    bi.PaymentDueDate,
    0 AS DunningLevel,
    'Overdue' AS InvoiceStatus,
    DATEDIFF(day, bi.PaymentDueDate, GETUTCDATE()) AS DaysOverdue
FROM
    BaseInvoices bi
JOIN 
    dbo.CustTrans ct ON bi.InvoiceNumber = ct.INVOICE AND bi.DATAAREAID = ct.DATAAREAID
WHERE
    bi.PaymentDueDate < GETUTCDATE()
    AND ct.CLOSED = '1900-01-01 00:00:00.000' -- Logic to find open/unsettled transactions

UNION ALL

-- 4. Dunning Letter Generated
SELECT 
    clj.INVOICEID AS InvoiceNumber,
    'Dunning Letter Generated' AS ActivityName,
    clj.CREATEDDATETIME AS EventTime,
    bi.CollectorAssigned,
    bi.InvoiceAmount,
    bi.CustomerName,
    bi.CustomerSegment,
    bi.PaymentDueDate,
    clj.COLLECTIONLETTERCODE AS DunningLevel,
    'In Dunning' AS InvoiceStatus,
    DATEDIFF(day, bi.PaymentDueDate, clj.CREATEDDATETIME) AS DaysOverdue
FROM 
    dbo.CustCollectionLetterJour clj
JOIN
    BaseInvoices bi ON clj.INVOICEID = bi.InvoiceNumber AND clj.DATAAREAID = bi.DATAAREAID

UNION ALL

-- 5. Collection Activity Logged
-- This assumes smmActivities are used and linked to the customer.
-- Linking directly to an invoice can be difficult.
SELECT 
    bi.InvoiceNumber AS InvoiceNumber,
    'Collection Activity Logged' AS ActivityName,
    sa.CREATEDDATETIME AS EventTime,
    bi.CollectorAssigned,
    bi.InvoiceAmount,
    bi.CustomerName,
    bi.CustomerSegment,
    bi.PaymentDueDate,
    NULL AS DunningLevel,
    'In Collections' AS InvoiceStatus,
    DATEDIFF(day, bi.PaymentDueDate, sa.CREATEDDATETIME) AS DaysOverdue
FROM 
    dbo.smmActivities sa
JOIN
    dbo.CustTable ct ON sa.PARTY = ct.PARTY
JOIN
    BaseInvoices bi ON ct.ACCOUNTNUM = (SELECT TOP 1 INVOICECUST FROM dbo.CustInvoiceJour WHERE INVOICEID = bi.InvoiceNumber AND DATAAREAID = bi.DATAAREAID) AND ct.DATAAREAID = bi.DATAAREAID
WHERE
    sa.DATAAREAID = '{CompanyCode}' AND sa.CREATEDDATETIME > bi.InvoiceDate

UNION ALL

-- 6. Payment Promise Created
SELECT 
    bi.InvoiceNumber,
    'Payment Promise Created' AS ActivityName,
    cpp.CREATEDDATETIME AS EventTime,
    bi.CollectorAssigned,
    bi.InvoiceAmount,
    bi.CustomerName,
    bi.CustomerSegment,
    bi.PaymentDueDate,
    NULL AS DunningLevel,
    'Payment Promised' AS InvoiceStatus,
    DATEDIFF(day, bi.PaymentDueDate, cpp.CREATEDDATETIME) AS DaysOverdue
FROM 
    dbo.CustPromisedPayment cpp
JOIN 
    dbo.CustTrans ct ON cpp.TRANSACTIONREFRECID = ct.RECID AND cpp.DATAAREAID = ct.DATAAREAID
JOIN
    BaseInvoices bi ON ct.INVOICE = bi.InvoiceNumber AND ct.DATAAREAID = bi.DATAAREAID

UNION ALL

-- 7. Dispute Registered
SELECT 
    bi.InvoiceNumber,
    'Dispute Registered' AS ActivityName,
    cd.CREATEDDATETIME AS EventTime,
    bi.CollectorAssigned,
    bi.InvoiceAmount,
    bi.CustomerName,
    bi.CustomerSegment,
    bi.PaymentDueDate,
    NULL AS DunningLevel,
    'Disputed' AS InvoiceStatus,
    DATEDIFF(day, bi.PaymentDueDate, cd.CREATEDDATETIME) AS DaysOverdue
FROM 
    dbo.CustDispute cd
JOIN 
    dbo.CustTrans ct ON cd.CUSTTRANSRECID = ct.RECID AND cd.DATAAREAID = ct.DATAAREAID
JOIN
    BaseInvoices bi ON ct.INVOICE = bi.InvoiceNumber AND ct.DATAAREAID = bi.DATAAREAID
WHERE
    cd.STATUS = 1 -- Assuming 1 means opened/registered

UNION ALL

-- 8. Dispute Resolved
SELECT 
    bi.InvoiceNumber,
    'Dispute Resolved' AS ActivityName,
    cd.MODIFIEDDATETIME AS EventTime,
    bi.CollectorAssigned,
    bi.InvoiceAmount,
    bi.CustomerName,
    bi.CustomerSegment,
    bi.PaymentDueDate,
    NULL AS DunningLevel,
    'Posted' AS InvoiceStatus, -- Status returns to normal after resolution
    DATEDIFF(day, bi.PaymentDueDate, cd.MODIFIEDDATETIME) AS DaysOverdue
FROM 
    dbo.CustDispute cd
JOIN 
    dbo.CustTrans ct ON cd.CUSTTRANSRECID = ct.RECID AND cd.DATAAREAID = ct.DATAAREAID
JOIN
    BaseInvoices bi ON ct.INVOICE = bi.InvoiceNumber AND ct.DATAAREAID = bi.DATAAREAID
WHERE
    cd.STATUS = 2 -- Assuming 2 means closed/resolved

UNION ALL

-- 9. Payment Received (Journal Created, not yet posted)
-- This can be difficult to capture accurately and link to a specific invoice before settlement.
-- This is an approximation based on journal creation.
SELECT 
    bi.InvoiceNumber,
    'Payment Received' AS ActivityName,
    cpjl.CREATEDDATETIME AS EventTime,
    bi.CollectorAssigned,
    bi.InvoiceAmount,
    bi.CustomerName,
    bi.CustomerSegment,
    bi.PaymentDueDate,
    NULL AS DunningLevel,
    'Payment Pending' AS InvoiceStatus,
    DATEDIFF(day, bi.PaymentDueDate, cpjl.CREATEDDATETIME) AS DaysOverdue
FROM 
    dbo.CustomerPaymentJournalLineEntity cpjl
JOIN
    BaseInvoices bi ON cpjl.AccountDisplayValue = (SELECT TOP 1 INVOICECUST FROM dbo.CustInvoiceJour WHERE INVOICEID = bi.InvoiceNumber AND DATAAREAID = bi.DATAAREAID) AND cpjl.Company = bi.DATAAREAID
WHERE
    cpjl.CREATEDDATETIME >= bi.InvoiceDate

UNION ALL

-- 10. Payment Posted (Settlement Transaction)
-- Captures each partial or full payment application
SELECT 
    bi.InvoiceNumber,
    'Payment Posted' AS ActivityName,
    cs.TRANSDATE AS EventTime,
    bi.CollectorAssigned,
    bi.InvoiceAmount,
    bi.CustomerName,
    bi.CustomerSegment,
    bi.PaymentDueDate,
    NULL AS DunningLevel,
    'Partially Paid' AS InvoiceStatus,
    DATEDIFF(day, bi.PaymentDueDate, cs.TRANSDATE) AS DaysOverdue
FROM 
    dbo.CustSettlement cs
JOIN 
    dbo.CustTrans ct ON cs.TRANSRECID = ct.RECID AND cs.DATAAREAID = ct.DATAAREAID
JOIN
    BaseInvoices bi ON ct.INVOICE = bi.InvoiceNumber AND ct.DATAAREAID = bi.DATAAREAID
WHERE 
    cs.SETTLEAMOUNTCUR > 0

UNION ALL

-- 11. Invoice Settled
SELECT 
    bi.InvoiceNumber,
    'Invoice Settled' AS ActivityName,
    ct.CLOSEDDATE AS EventTime,
    bi.CollectorAssigned,
    bi.InvoiceAmount,
    bi.CustomerName,
    bi.CustomerSegment,
    bi.PaymentDueDate,
    NULL AS DunningLevel,
    'Settled' AS InvoiceStatus,
    DATEDIFF(day, bi.PaymentDueDate, ct.CLOSEDDATE) AS DaysOverdue
FROM 
    dbo.CustTrans ct
JOIN
    BaseInvoices bi ON ct.INVOICE = bi.InvoiceNumber AND ct.DATAAREAID = bi.DATAAREAID
WHERE 
    ct.CLOSEDDATE <> '1900-01-01 00:00:00.000'
    AND ct.TRANSTYPE = 11 -- Enum for Sales Invoice

UNION ALL

-- 12. Invoice Written Off
SELECT
    bi.InvoiceNumber,
    'Invoice Written Off' AS ActivityName,
    ct.TRANSDATE AS EventTime, -- The date of the write-off transaction
    bi.CollectorAssigned,
    bi.InvoiceAmount,
    bi.CustomerName,
    bi.CustomerSegment,
    bi.PaymentDueDate,
    NULL AS DunningLevel,
    'Written Off' AS InvoiceStatus,
    DATEDIFF(day, bi.PaymentDueDate, ct.TRANSDATE) AS DaysOverdue
FROM
    dbo.CustTrans ct
JOIN
    BaseInvoices bi ON ct.INVOICE = bi.InvoiceNumber AND ct.DATAAREAID = bi.DATAAREAID
WHERE 
    ct.TRANSTYPE = 19; -- Enum for Write-off. This may vary based on configuration.

Steps

Prerequisites and Environment Setup: Ensure you have Python 3.8 or newer installed. Install the necessary libraries using pip: pip install pandas requests msal. You will also need credentials for a service principal with access to the Dynamics 365 environment.
Register Application in Azure AD: Navigate to the Azure Active Directory portal. Register a new application to obtain a Client ID and Tenant ID. Create a new client secret under the 'Certificates & secrets' section for the application. Securely store the Client ID, Tenant ID, and Client Secret.
Grant API Permissions: In the registered application's 'API permissions' settings in Azure AD, add a permission. Select 'Dynamics ERP' or a similar entry for Dynamics 365, then grant the 'user_impersonation' delegated permission. Grant admin consent for your organization.
Create a Dynamics 365 User: Create a dedicated application user in Dynamics 365 Finance and Operations. Associate this user with the Client ID of your Azure AD application. Assign a security role to this user that has read permissions for all required data entities, such as CustInvoiceJour, CustCollectionLetterJour, CustSettlements, and smmActivities.
Configure the Python Script: Copy the provided Python script into a file, for example, extract_d365_collections.py. Populate the placeholder variables at the top of the script with your D365 environment URL, Tenant ID, Client ID, Client Secret, and the default company (dataAreaId).
Set Extraction Parameters: Adjust the start_date and end_date variables in the script to define the time window for the data extraction. A period of 3 to 6 months is recommended for initial analysis.
Execute the Script: Run the script from your terminal using the command: python extract_d365_collections.py. The script will authenticate, query the OData endpoints for each of the 12 activities, and handle API pagination automatically.
Retrieve Data for Each Activity: The script makes sequential API calls to fetch data for invoice generation, posting, dunning, payments, and other key events. Each query targets a specific data entity and selects only the necessary fields to optimize performance.
Transform and Combine Data: The script uses the pandas library to process the JSON response from each API call. It transforms the data into a structured table, standardizes the column names, and then merges the data from all activities into a single, unified event log.
Calculate Derived Events: The 'Payment Due Date Passed' activity is not a direct record in the system. The script calculates this event by iterating through all overdue invoices within the specified date range and generating a corresponding row for each one.
Export the Event Log: Once all data is processed and combined, the script saves the final event log as a CSV file named d365_collections_event_log.csv in the same directory. This file is formatted and ready for upload into a process mining tool.

Configuration

API Endpoint URL: The base URL of your Dynamics 365 Finance and Operations environment. This should be provided in the format https://[your-environment-name].operations.dynamics.com.
Authentication Credentials: The script requires a Tenant ID, Client ID, and Client Secret from an Azure Active Directory application registration. This application must be granted permissions to access the Dynamics 365 API.
Company Filter (dataAreaId): Dynamics 365 is a multi-company system. The script is configured to query a specific company using the dataAreaId filter. Ensure you set the default_company variable to the correct legal entity, for example, 'USMF'.
Date Range: The start_date and end_date parameters control the extraction window. It is recommended to start with a recent 3 to 6 month period to manage the data volume and ensure a timely extraction. The filter is applied primarily on the invoice creation date.
Performance Considerations: Extracting data via the OData API for very large datasets can be time consuming and may be subject to API throttling by Microsoft. If you encounter performance issues, consider running the extraction for smaller date ranges or during off peak hours.
Prerequisites: A Python 3.8+ environment with pandas, requests, and msal libraries is required. The associated Dynamics 365 user must have read access to the financial, sales, and collections management modules, specifically the entities mentioned in the script.

a Sample Query python

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

class=class="hl-string">"hl-comment"># --- Configuration --- 
D365_HOST = class="hl-string">"https://[your-d365-environment-url].operations.dynamics.com"
TENANT_ID = class="hl-string">"[Your Tenant ID]"
CLIENT_ID = class="hl-string">"[Your Client ID]"
CLIENT_SECRET = class="hl-string">"[Your Client Secret]"

class=class="hl-string">"hl-comment"># Date range for the extraction
start_date = class="hl-string">"2023-01-01"
end_date = class="hl-string">"2023-06-30"
default_company = class="hl-string">"[Your Company ID, e.g., USMF]"

class=class="hl-string">"hl-comment"># MSAL configuration
authority = fclass="hl-string">"https://login.microsoftonline.com/{TENANT_ID}"
scope = [fclass="hl-string">"{D365_HOST}/.default"]

class=class="hl-string">"hl-comment"># --- Authentication --- 
app = msal.ConfidentialClientApplication(CLIENT_ID, authority=authority, client_credential=CLIENT_SECRET)
result = app.acquire_token_silent(scope, account=None)

if not result:
    result = app.acquire_token_for_client(scopes=scope)

if class="hl-string">"access_token" not in result:
    raise Exception(class="hl-string">"Could not obtain access token: " + result.get(class="hl-string">"error_description"))

access_token = result[class="hl-string">'access_token']
headers = {
    class="hl-string">'Authorization': class="hl-string">'Bearer ' + access_token,
    class="hl-string">'Content-Type': class="hl-string">'application/json',
    class="hl-string">'Accept': class="hl-string">'application/json'
}

class=class="hl-string">"hl-comment"># --- Data Fetching Function --- 
def get_odata_data(entity_url):
    all_records = []
    url = entity_url
    while url:
        try:
            response = requests.get(url, headers=headers, params={class="hl-string">'cross-company': class="hl-string">'true'})
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raises an HTTPError for bad responses (4xx or 5xx)
            data = response.json()
            all_records.extend(data.get(class="hl-string">'value', []))
            url = data.get(class="hl-string">'@odata.nextLink')
        except requests.exceptions.HTTPError as err:
            print(fclass="hl-string">"HTTP Error fetching data: {err}")
            print(fclass="hl-string">"Response content: {err.response.text}")
            break
        except json.JSONDecodeError:
            print(fclass="hl-string">"Failed to decode JSON from response: {response.text}")
            break
    return all_records

class=class="hl-string">"hl-comment"># --- Main Extraction Logic --- 
all_events = []

print(class="hl-string">"1. Extracting 'Invoice Generated' and 'Invoice Posted'...")
invoice_entity_url = fclass="hl-string">"{D365_HOST}/data/CustInvoiceJours?$filter=dataAreaId eq '{default_company}' and InvoiceDate ge {start_date}T00:00:00Z and InvoiceDate le {end_date}T23:59:59Z&$select=InvoiceNumber,InvoiceDate,createdDateTime,DueDate,InvoiceAmount,SalesOrderNumber,InvoiceCustomerAccountNumber,CustomerName,dataAreaId"
invoices = get_odata_data(invoice_entity_url)
if invoices:
    df_invoices = pd.DataFrame(invoices)
    df_invoices[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Generated'
    df_invoices[class="hl-string">'EventTime'] = pd.to_datetime(df_invoices[class="hl-string">'createdDateTime']).dt.tz_localize(None)
    df_invoices.rename(columns={class="hl-string">'InvoiceNumber': class="hl-string">'InvoiceNumber', class="hl-string">'InvoiceAmount': class="hl-string">'InvoiceAmount', class="hl-string">'CustomerName': class="hl-string">'CustomerName', class="hl-string">'DueDate': class="hl-string">'PaymentDueDate'}, inplace=True)
    all_events.append(df_invoices[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'InvoiceAmount', class="hl-string">'CustomerName', class="hl-string">'PaymentDueDate']])

    df_posted = df_invoices.copy()
    df_posted[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Posted and Sent'
    df_posted[class="hl-string">'EventTime'] = pd.to_datetime(df_posted[class="hl-string">'InvoiceDate']).dt.tz_localize(None)
    all_events.append(df_posted[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'InvoiceAmount', class="hl-string">'CustomerName', class="hl-string">'PaymentDueDate']])

    print(class="hl-string">"2. Calculating 'Payment Due Date Passed'...")
    df_due_passed = df_invoices[pd.to_datetime(df_invoices[class="hl-string">'PaymentDueDate']).dt.date < datetime.now().date()].copy()
    df_due_passed[class="hl-string">'ActivityName'] = class="hl-string">'Payment Due Date Passed'
    df_due_passed[class="hl-string">'EventTime'] = pd.to_datetime(df_due_passed[class="hl-string">'PaymentDueDate']) + timedelta(days=1)
    all_events.append(df_due_passed[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'InvoiceAmount', class="hl-string">'CustomerName', class="hl-string">'PaymentDueDate']])

print(class="hl-string">"3. Extracting 'Dunning Letter Generated'...")
dunning_entity_url = fclass="hl-string">"{D365_HOST}/data/CustCollectionLetterJours?$filter=dataAreaId eq '{default_company}' and CollectionLetterDate ge {start_date}T00:00:00Z and CollectionLetterDate le {end_date}T23:59:59Z&$select=InvoiceNumber,CollectionLetterCode,CollectionLetterDate"
dunning_letters = get_odata_data(dunning_entity_url)
if dunning_letters:
    df_dunning = pd.DataFrame(dunning_letters)
    df_dunning[class="hl-string">'ActivityName'] = class="hl-string">'Dunning Letter Generated'
    df_dunning[class="hl-string">'EventTime'] = pd.to_datetime(df_dunning[class="hl-string">'CollectionLetterDate']).dt.tz_localize(None)
    df_dunning.rename(columns={class="hl-string">'InvoiceNumber': class="hl-string">'InvoiceNumber', class="hl-string">'CollectionLetterCode': class="hl-string">'DunningLevel'}, inplace=True)
    all_events.append(df_dunning[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'DunningLevel']])

print(class="hl-string">"4. Extracting 'Payment Received' and 'Payment Posted'...")
settlements_entity_url = f"{D365_HOST}/data/CustSettlements?$filter=dataAreaId eq class="hl-string">'{default_company}' and TransDate ge {start_date}T00:00:00Z and TransDate le {end_date}T23:59:59Z&$select=OffsetRecId,TransDate,createdDateTime,SettlementAmountCur
&expand=CustInvoiceJour($select=InvoiceNumber)"
settlements = get_odata_data(settlements_entity_url)
if settlements:
    class=class="hl-string">"hl-comment"># Flatten the expanded data
    flat_settlements = []
    for s in settlements:
        if s.get(class="hl-string">'CustInvoiceJour'):
            s[class="hl-string">'InvoiceNumber'] = s[class="hl-string">'CustInvoiceJour'][class="hl-string">'InvoiceNumber']
            flat_settlements.append(s)

    df_settlements = pd.DataFrame(flat_settlements)
    df_received = df_settlements.copy()
    df_received[class="hl-string">'ActivityName'] = class="hl-string">'Payment Received'
    df_received[class="hl-string">'EventTime'] = pd.to_datetime(df_received[class="hl-string">'createdDateTime']).dt.tz_localize(None)
    all_events.append(df_received[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime']])
    
    df_posted_payment = df_settlements.copy()
    df_posted_payment[class="hl-string">'ActivityName'] = class="hl-string">'Payment Posted'
    df_posted_payment[class="hl-string">'EventTime'] = pd.to_datetime(df_posted_payment[class="hl-string">'TransDate']).dt.tz_localize(None)
    all_events.append(df_posted_payment[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime']])

class=class="hl-string">"hl-comment"># Note: Other activities like Disputes, Promises, Write-offs often involve custom entities or status fields.
class=class="hl-string">"hl-comment"># The following are conceptual examples assuming certain fields exist.
class=class="hl-string">"hl-comment"># You MUST adapt these to your specific D365 configuration.

print(class="hl-string">"5. Extracting 'Dispute Registered' and 'Dispute Resolved' (Conceptual)... ")
class=class="hl-string">"hl-comment"># Assuming a status field class="hl-string">'CollectionStatus' on the invoice indicates a dispute.
class=class="hl-string">"hl-comment"># This would ideally be sourced from an audit trail if available. This example assumes a snapshot.
dispute_entity_url = fclass="hl-string">"{D365_HOST}/data/CustInvoiceJours?$filter=dataAreaId eq '{default_company}' and (CollectionStatus eq 'Disputed' or CollectionStatus eq 'Resolved') and modifiedDateTime ge {start_date}T00:00:00Z&$select=InvoiceNumber,CollectionStatus,modifiedDateTime"
disputes = get_odata_data(dispute_entity_url)
if disputes:
    df_disputes = pd.DataFrame(disputes)
    df_disputes[class="hl-string">'ActivityName'] = df_disputes[class="hl-string">'CollectionStatus'].apply(lambda x: class="hl-string">'Dispute Registered' if x == class="hl-string">'Disputed' else class="hl-string">'Dispute Resolved')
    df_disputes[class="hl-string">'EventTime'] = pd.to_datetime(df_disputes[class="hl-string">'modifiedDateTime']).dt.tz_localize(None)
    all_events.append(df_disputes[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime']])

print(class="hl-string">"6. Extracting 'Payment Promise Created' (Conceptual)... ")
promise_entity_url = fclass="hl-string">"{D365_HOST}/data/CustPromisedPayments?$filter=dataAreaId eq '{default_company}' and createdDateTime ge {start_date}T00:00:00Z&$select=Invoice,createdDateTime,PromisedDate,PromisedAmount&$expand=CustInvoiceJour($select=InvoiceNumber)"
promises = get_odata_data(promise_entity_url)
if promises:
    flat_promises = []
    for p in promises:
        if p.get(class="hl-string">'CustInvoiceJour'):
            p[class="hl-string">'InvoiceNumber'] = p[class="hl-string">'CustInvoiceJour'][class="hl-string">'InvoiceNumber']
            flat_promises.append(p)
    df_promises = pd.DataFrame(flat_promises)
    df_promises[class="hl-string">'ActivityName'] = class="hl-string">'Payment Promise Created'
    df_promises[class="hl-string">'EventTime'] = pd.to_datetime(df_promises[class="hl-string">'createdDateTime']).dt.tz_localize(None)
    all_events.append(df_promises[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime']])
    
print(class="hl-string">"7. Extracting 'Collection Activity Logged' (Conceptual)... ")
class=class="hl-string">"hl-comment"># Linking activities to invoices can be indirect, often through the customer account.
activity_entity_url = fclass="hl-string">"{D365_HOST}/data/smmActivities?$filter=dataAreaId eq '{default_company}' and StartDateTime ge {start_date}T00:00:00Z and Purpose eq '[Your Collection Activity Purpose]'&$select=ActivityNumber,ParentRecordEntityName,ParentRecordRecId,StartDateTime,ResponsibleWorkerName&$expand=CustTable($select=AccountNumber)"
activities = get_odata_data(activity_entity_url)
class=class="hl-string">"hl-comment"># This section requires custom logic to map customer-level activities to open invoices at that time.
class=class="hl-string">"hl-comment"># A simplified approach is omitted here due to its complexity.

print(class="hl-string">"8. Identifying 'Invoice Settled' (Conceptual)... ")
settled_entity_url = fclass="hl-string">"{D365_HOST}/data/CustInvoiceJours?$filter=dataAreaId eq '{default_company}' and Status eq 'Paid' and LastSettlementDate ge {start_date}T00:00:00Z&$select=InvoiceNumber,LastSettlementDate"
settled_invoices = get_odata_data(settled_entity_url)
if settled_invoices:
    df_settled = pd.DataFrame(settled_invoices)
    df_settled[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Settled'
    df_settled[class="hl-string">'EventTime'] = pd.to_datetime(df_settled[class="hl-string">'LastSettlementDate']).dt.tz_localize(None)
    all_events.append(df_settled[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime']])

print(class="hl-string">"9. Identifying 'Invoice Written Off' (Conceptual)... ")
class=class="hl-string">"hl-comment"># This is identified by a specific type of transaction in CustTrans.
writeoff_entity_url = fclass="hl-string">"{D365_HOST}/data/CustTrans?$filter=dataAreaId eq '{default_company}' and TransType eq 'WriteOff' and TransDate ge {start_date}T00:00:00Z&$select=Invoice,TransDate,Txt"
writeoffs = get_odata_data(writeoff_entity_url)
if writeoffs:
    df_writeoffs = pd.DataFrame(writeoffs)
    df_writeoffs.rename(columns={class="hl-string">'Invoice': class="hl-string">'InvoiceNumber'}, inplace=True)
    df_writeoffs[class="hl-string">'ActivityName'] = class="hl-string">'Invoice Written Off'
    df_writeoffs[class="hl-string">'EventTime'] = pd.to_datetime(df_writeoffs[class="hl-string">'TransDate']).dt.tz_localize(None)
    all_events.append(df_writeoffs[[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime']])

class=class="hl-string">"hl-comment"># --- Final Processing --- 
if all_events:
    print(class="hl-string">"Combining all events...")
    final_df = pd.concat(all_events, ignore_index=True)
    final_df.sort_values(by=[class="hl-string">'InvoiceNumber', class="hl-string">'EventTime'], inplace=True)
    
    class=class="hl-string">"hl-comment"># Fill forward attributes for a complete log
    attributes_to_fill = [class="hl-string">'InvoiceAmount', class="hl-string">'CustomerName', class="hl-string">'PaymentDueDate', class="hl-string">'DunningLevel']
    for attr in attributes_to_fill:
        if attr not in final_df.columns:
            final_df[attr] = None
    
    final_df[attributes_to_fill] = final_df.groupby(class="hl-string">'InvoiceNumber')[attributes_to_fill].fillna(method=class="hl-string">'ffill').fillna(method=class="hl-string">'bfill')
    
    final_df = final_df.dropna(subset=[class="hl-string">'InvoiceNumber', class="hl-string">'ActivityName', class="hl-string">'EventTime'])
    
    output_filename = class="hl-string">'d365_collections_event_log.csv'
    final_df.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%S.%f')
    print(fclass="hl-string">"Event log successfully created: {output_filename}")
else:
    print(class="hl-string">"No events were extracted. Please check your configuration and date range.")

Your Credit Management & Collections Data Template

Your Credit Management & Collections Data Template

Credit Management & Collections Attributes

Credit Management & Collections Activities

Extraction Guides

Direct SQL Query via Bring Your Own Database (BYOD)

Steps

Configuration

a Sample Query sql

Python Script using Dynamics 365 OData REST API

Steps

Configuration

a Sample Query python

Ready to Get Started?

Optimize Your Credit & Collections for Faster Cash Today

Your Credit Management & Collections Data Template

Your Credit Management & Collections Data Template

Credit Management & Collections Attributes

Credit Management & Collections Activities

Extraction Guides

Direct SQL Query via Bring Your Own Database (BYOD)

Steps

Configuration

a Sample Query sql

Python Script using Dynamics 365 OData REST API

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Optimize Your Credit & Collections for Faster Cash Today