> Continuous Process Improvement > Record to Report Journal Entry > Microsoft Dynamics 365 > Data Template

Your Record to Report - Journal Entry Data Template

Microsoft Dynamics 365

Record to Report - Journal Entry Attributes (21) Record to Report - Journal Entry Activities (12) Extraction Guides (4)

Your Record to Report - Journal Entry Data Template

This template provides a comprehensive guide to the essential data points required for analyzing your Record to Report - Journal Entry process. It outlines the crucial attributes to collect and the key activities to track, ensuring you capture all necessary information for an effective process mining initiative. Additionally, you will find practical guidance on extracting this data from your source system.

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

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

Download Template

Record to Report - Journal Entry Attributes

These are the recommended data fields to include in your event log for comprehensive Record to Report - Journal Entry analysis.

3 Required 7 Recommended 11 Optional

	Name	Description
	Activity Name ActivityName	The name of the specific business process step or event that occurred.
Description This attribute describes a single event or task within the journal entry lifecycle, such as 'Journal Entry Created', 'Journal Submitted For Approval', or 'Journal Entry Posted'. These activities form the nodes of the discovered process map. Analyzing the sequence and frequency of these activities is core to process mining. It reveals the actual process flow, helps identify deviations from the standard procedure, and highlights bottlenecks where activities take longer than expected or are repeated. Why it matters This attribute defines the steps in the process, forming the backbone of the process map and enabling the analysis of process flow and variations. Where to get This is a conceptual attribute derived from system events, status changes, or workflow logs within Microsoft Dynamics 365. Examples Journal Entry CreatedJournal Submitted For ApprovalJournal ApprovedJournal Entry Posted
	Event Time EventTime	The precise timestamp indicating when an activity or event occurred.
Description The Event Time records the date and time that a specific activity in the journal entry process took place. This chronological data is essential for ordering events and calculating the duration between them. In analysis, this timestamp is used to construct the timeline for each case, which is critical for calculating all time-based metrics such as cycle times, processing times, and waiting times. It allows for the identification of delays between steps and supports dashboards related to bottlenecks and SLA compliance. Why it matters This timestamp is essential for ordering events correctly and calculating all duration-based KPIs, such as cycle time and processing delays. Where to get Event timestamps are typically found in workflow history logs, audit trail tables (e.g., SysDatabaseLog), or as creation/modification timestamps on related records like LedgerJournalTable and LedgerJournalTrans. Examples 2023-10-26T10:00:00Z2023-10-26T11:30:00Z2023-10-27T14:45:10Z
	Journal Entry ID JournalEntryId	The unique identifier for a journal entry, serving as the primary case identifier.
Description The Journal Entry ID uniquely links all activities and data points related to a single journal entry transaction. It allows for the complete end-to-end tracking of a journal's lifecycle, from creation and review through to its final posting in the general ledger. In process mining analysis, this attribute is fundamental for reconstructing the process flow. Each unique Journal Entry ID represents a single instance of the process, enabling detailed examination of process variants, cycle times, and compliance for individual financial transactions. Why it matters This is the essential key for tracking a journal entry from start to finish, making it possible to analyze the entire process flow for each case. Where to get This identifier is typically found in the journal header table, such as LedgerJournalTable, often in a field named JournalNum. Examples JRN-0012345JV-2023-08-156GENJ0000891
	Event End Time EventEndTime	The timestamp indicating when an activity or event was completed.
Description The Event End Time marks the completion of a specific activity. While StartTime indicates when an event begins, EndTime provides the other boundary, allowing for precise duration calculation of individual tasks. In process mining, having both a start and end time enables the calculation of activity processing time, which is distinct from waiting time. This is crucial for dashboards analyzing user performance and identifying which specific tasks, not just the gaps between them, are consuming the most time. Why it matters Enables the precise calculation of how long each activity takes, which is vital for analyzing user performance and identifying resource-intensive tasks. Where to get This data may be explicitly stored in workflow logs or may need to be derived by using the StartTime of the subsequent activity in the sequence. Examples 2023-10-26T10:05:15Z2023-10-26T11:45:00Z2023-10-27T15:00:00Z
	Journal Status JournalStatus	The current or final status of the journal entry.
Description This attribute indicates the state of the journal entry at a given point in time, such as 'Draft', 'In Review', 'Approved', 'Rejected', or 'Posted'. It provides a snapshot of where the entry is in its lifecycle. Analyzing the status is helpful for understanding the outcomes of cases. It can be used to filter for all rejected journals or to track the volume of journals in a specific stage. This attribute supports multiple dashboards by providing context to throughput and status analysis. Why it matters Provides a clear outcome for each journal entry, enabling analysis of success rates, rejection rates, and the volume of work in different stages. Where to get The status is often found on the journal header table, LedgerJournalTable, or derived from the workflow status. Examples DraftSubmittedApprovedPostedRejected
	Journal Total Amount JournalTotalAmount	The total monetary value of the journal entry, typically the sum of debits.
Description This attribute represents the total financial value of the journal entry. It can be used to categorize entries into value bands, such as low, medium, and high value. Analyzing the process based on financial value can reveal important patterns. For instance, high-value journal entries might follow a more rigorous approval process with more steps and longer cycle times. This attribute is essential for materiality analysis and understanding the financial impact of process inefficiencies. Why it matters Allows for segmenting the process by financial value, which often correlates with process complexity, risk, and approval workflows. Where to get This value may need to be calculated by summing the debit amounts from the journal line table, LedgerJournalTrans, for each journal entry. Examples 1500.75125000.0050.25
	Journal Type JournalType	The classification of the journal entry, such as General Journal or Accrual.
Description Journal Type categorizes entries based on their business purpose, for example, daily entries, accruals, allocations, or eliminations. This segmentation is crucial for understanding different process paths and behaviors. This attribute allows for filtering and comparing the process across different journal types. It is essential for the 'Journal Entry Throughput Volume by Type' dashboard, helping to analyze whether certain types of journals are more prone to delays, rejections, or rework. Why it matters Allows for segmenting the analysis to compare processes for different business purposes, which may have distinct paths, cycle times, and approval requirements. Where to get This is typically stored on the journal header table, LedgerJournalTable, in a field related to journal names or types (e.g., JournalName). Examples General JournalAccrual AdjustmentIntercompany TransferPayroll
	Legal Entity LegalEntity	The legal entity or company code for which the journal entry is being recorded.
Description The Legal Entity represents the specific company or business unit within an organization for which the financial transaction is being recorded. This is a fundamental organizational dimension in financial systems. In process mining, this attribute allows for the comparison of the journal entry process across different parts of the organization. It can reveal if certain legal entities have more efficient processes, higher rejection rates, or longer cycle times, helping to identify and share best practices. Why it matters Enables comparison of process performance across different companies or business units, highlighting variations and improvement opportunities. Where to get This is a core field in Dynamics 365, often available in transaction tables like LedgerJournalTable, typically tied to the DataAreaId. Examples USMFDEMFGBSI
	Rejection Reason RejectionReason	The reason provided when a journal entry is rejected during the approval process.
Description When a journal entry is rejected, the approver often provides a reason for the rejection. This attribute captures that information, which is invaluable for root cause analysis. This is the primary attribute for the 'Journal Entry Rejection Rate Analysis' dashboard. By categorizing and analyzing rejection reasons, organizations can identify common problems, such as incorrect documentation, policy violations, or data entry errors, and then implement targeted training or process improvements. Why it matters Explains why rework occurs, providing the direct insight needed to reduce rejection rates and improve first-time right approvals. Where to get This information is typically stored in the workflow history or comments section associated with the rejection step. Examples Insufficient supporting documentationIncorrect account usedExceeds approval thresholdDuplicate entry
	User Name UserName	The name of the user who performed the activity.
Description This attribute identifies the employee or system user responsible for executing a specific activity, such as creating, approving, or posting a journal entry. It links process steps to human resources. Analyzing performance by user is a key capability in process mining. It helps build the 'Journal Entry User Performance' dashboard to compare activity durations and throughput across different users or teams. This can highlight top performers, identify training needs, and assist in workload balancing. Why it matters Links process activities to specific individuals, enabling analysis of user performance, workload distribution, and resource allocation. Where to get User information is typically found in createdBy or modifiedBy fields on tables like LedgerJournalTable or in associated workflow history logs. Examples Alice SmithBob Johnsonsystem.batch
	Approval Level ApprovalLevel	Indicates the current or completed stage in a multi-level approval workflow.
Description For journal entries that require multiple approvals, this attribute tracks which level of the hierarchy the entry has reached, for example, 'Manager Approval' or 'Director Approval'. This attribute is useful for analyzing approval bottlenecks in more detail. It can help pinpoint if delays consistently occur at a specific level in the approval chain, suggesting a need for process redesign or resource reallocation at that stage. Why it matters Provides visibility into multi-stage approval workflows, helping to identify bottlenecks at specific approval levels. Where to get This information would be derived from the workflow history log, tracking the completion of different approval steps. Examples Level 1: ManagerLevel 2: DirectorLevel 3: VP Finance
	Approval SLA Status ApprovalSlaState	Indicates whether the journal entry approval met its service level agreement.
Description This attribute categorizes each journal entry's approval cycle based on whether it was completed within the target time defined by a Service Level Agreement (SLA). Possible values are typically 'Met' or 'Breached'. This is the core metric for the 'Journal Entry Approval SLA Compliance' dashboard. It provides a clear, business-oriented view of performance against targets, helping to monitor and manage the timeliness of the approval process and drive improvements where SLAs are frequently missed. Why it matters Translates raw cycle time data into a clear business outcome (met or breached), making it easy to track performance against key targets. Where to get This is a calculated attribute. The logic requires comparing the calculated Approval Cycle Time KPI against a predefined SLA target. Examples MetBreached
	Currency Code CurrencyCode	The currency of the journal entry amount.
Description This attribute specifies the currency in which the journal entry is denominated, such as USD, EUR, or GBP. It provides essential context for the Journal Total Amount. While not always used for analyzing the process flow itself, the currency is vital for any financial reporting or analysis based on the journal amounts. It allows for correct aggregation and comparison of values, especially in multinational organizations. Why it matters Provides necessary context for any financial analysis, ensuring monetary values are interpreted correctly, especially in multi-currency environments. Where to get The currency code is typically available on the journal line table, LedgerJournalTrans. Examples USDEURGBPJPY
	Department DepartmentName	The department or cost center associated with the journal entry.
Description The department or cost center identifies the internal business unit responsible for or affected by the financial transaction. This is a key dimension for internal management reporting. This attribute allows for analyzing the journal entry process by department. It can help answer questions like: Which departments submit the most journals? Do certain departments have higher rejection rates or longer approval times? This is useful for targeted process improvement efforts. Why it matters Provides a way to analyze process performance by business function, helping to identify department-specific bottlenecks or training needs. Where to get This information is typically found on the journal line level (LedgerJournalTrans) as a financial dimension. Examples SalesFinanceMarketingOperations
	Is Automated Posting IsAutomatedPosting	A flag indicating if the journal entry was created or posted automatically.
Description This boolean attribute distinguishes between journal entries that are created manually by a user and those generated automatically by the system or a subsystem, such as a system integration or an automated allocation process. Analyzing this attribute helps to compare the efficiency and error rates of automated versus manual processes. It can highlight opportunities for further automation by showing if manual entries are more prone to errors, rework, or delays. Why it matters Separates manual from automated processes, allowing for a comparison of their efficiency, accuracy, and compliance. Where to get This may be indicated by the 'Created by' user (e.g., a system or batch user) or a specific flag on the journal header or type configuration. Examples truefalse
	Is First Time Right IsFirstTimeRight	A flag indicating if the journal was approved without any prior rejections.
Description This case-level boolean attribute is true if a journal entry proceeds from submission to approval without any intervening 'Journal Rejected' or 'Journal Entry Corrected' activities. It is a key measure of process quality. The 'First-Time Right Approval Rate' KPI is directly calculated from this attribute. A high rate indicates an efficient and high-quality process, while a low rate signals systemic issues with initial data quality, clarity of requirements, or submission procedures. Why it matters This is a critical measure of process quality, highlighting how many journals flow through the approval process smoothly without any rework. Where to get This is a calculated attribute derived at the case level by analyzing the sequence of activities for each Journal Entry ID. Examples truefalse
	Is Rework IsRework	A flag that identifies activities that are part of a rework or correction loop.
Description This calculated boolean attribute is set to true for activities that occur after a rejection, such as 'Journal Entry Corrected' or a repeated 'Journal Submitted For Approval'. It helps to isolate and quantify rework. This attribute is essential for the 'Journal Entry Rework and Correction Loops' dashboard and the 'Rework Rate' KPI. By flagging rework, it becomes easy to visualize and measure the frequency and impact of correction cycles, which are a primary source of inefficiency in the process. Why it matters Directly flags inefficient rework loops, making it easy to quantify the impact of rejections and corrections on overall cycle time and cost. Where to get This is a calculated attribute derived during data transformation by analyzing the sequence of activities within a case. Examples truefalse
	Last Data Update LastDataUpdate	The timestamp when the data was last refreshed from the source system.
Description This attribute indicates the date and time of the most recent data extraction from the source system. It provides context for the freshness of the analysis and the data included. Displaying this information in dashboards assures users of the data's timeliness and helps them understand the time frame covered by the current process analysis. It is a key piece of metadata for any process mining project. Why it matters Provides critical context about the data's freshness, ensuring users understand how current the process analysis is. Where to get This timestamp is generated and stored during the data extraction, transformation, and loading (ETL) process. Examples 2023-10-27T02:00:00Z
	Posting Date PostingDate	The date the journal entry is posted to the general ledger.
Description The Posting Date is the official date on which the transaction affects the general ledger balances. This date is critical for financial reporting and closing periods. This attribute is used to analyze the lag between approval and posting, which is the focus of the 'Journal Entry Posting Lead Time' KPI. Reducing this lag is often a key goal for accelerating the financial close process. It can also be used to analyze posting volumes over time. Why it matters Crucial for calculating the posting lead time KPI and understanding delays between approval and when a transaction becomes official in the ledger. Where to get This date is typically stored on the journal header table (LedgerJournalTable) or related posted transaction tables. Examples 2023-10-282023-11-012023-10-31
	Processing Time ProcessingTime	The duration of time spent actively working on an activity.
Description Processing Time measures the time between the start and end of an activity, representing the actual work duration. This is distinct from cycle time, which includes waiting time between activities. This calculated metric is vital for the 'Journal Entry User Performance' dashboard. It helps to understand how long users or teams take to complete specific tasks, removing the influence of queueing or waiting time. This allows for a fairer and more accurate assessment of resource efficiency. Why it matters Measures active work time for an activity, allowing for analysis of user and team efficiency without the noise of waiting periods. Where to get This is calculated by taking the difference between the EventEndTime and the EventTime (StartTime) for each activity. Examples PT5M15SPT1H15MP1DT2H
	Source System SourceSystem	The system of record from which the data was extracted.
Description This attribute identifies the source application where the journal entry data originates. For this process, the value is typically a constant, such as 'Microsoft Dynamics 365'. In a broader analysis context, especially in environments with multiple ERPs or integrated systems, this field helps differentiate processes and data sources. It ensures clarity on data provenance and is important for data governance and validation. Why it matters Identifies the origin of the data, which is crucial for data governance and for analyses that might span multiple enterprise systems. Where to get This is a static value added during the data extraction, transformation, and loading (ETL) process to label the dataset's origin. Examples Microsoft Dynamics 365D365 F&O

Required Recommended Optional

Record to Report - Journal Entry Activities

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

5 Recommended 7 Optional

	Activity	Description
	Journal Approved	The journal entry has been approved by the designated authority, completing the final step in the approval workflow. This is typically captured from a status change on the journal header, like moving to 'Approved'.
Why it matters This is a critical milestone that concludes the approval process and enables posting. It is essential for calculating approval cycle time, posting lead time, and the first-time right rate. Where to get Inferred from a status field change (e.g., ApprovalStatus changing to 'Approved') on the LedgerJournalTable or from the workflow history table indicating a final approval state. Capture Identify timestamp when the journal status changes to 'Approved'. Event type inferred
	Journal Entry Created	This activity marks the initiation of a new journal entry. It is captured when a user creates a new journal header record in the system, establishing a unique Journal Entry ID that serves as the case identifier for process analysis.
Why it matters This is the primary start event for the process. Analyzing the time from this point to posting is crucial for measuring the end-to-end cycle time and identifying initial data entry delays. Where to get This event is captured from the creation timestamp of the journal header in the GeneralJournalEntry or LedgerJournalTable entity. It is typically an explicit record creation event. Capture Use the 'createdDateTime' field on the GeneralJournalEntry or LedgerJournalTable. Event type explicit
	Journal Entry Posted	This activity marks the successful posting of the journal entry to the general ledger, making it an official financial record. This is a critical event, captured when the journal header status is updated to 'Posted'.
Why it matters This is the primary success end event for the process. It is used to calculate the end-to-end cycle time and posting lead time, which are key indicators of financial close efficiency. Where to get Captured from a status field change (e.g., JournalStatus changing to 'Posted') on the LedgerJournalTable and the creation of corresponding entries in the GeneralJournalAccountEntry table. Capture Identify the timestamp when the journal's status becomes 'Posted'. Event type inferred
	Journal Rejected	The journal entry has been rejected by a reviewer or approver and requires correction. This event is captured from a status change on the journal, such as moving to a 'Rejected' or 'Needs Correction' state.
Why it matters Tracking rejections is fundamental to calculating the rejection rate and identifying the root causes of rework. It highlights issues with data quality, compliance, or user training. Where to get Inferred from a status field change (e.g., ApprovalStatus changing to 'Rejected') on the LedgerJournalTable or from the workflow history log. Capture Identify timestamp when the journal status changes to 'Rejected'. Event type inferred
	Journal Submitted For Approval	Represents the formal submission of a completed journal entry into a review and approval workflow. This is typically inferred from a status change on the journal header, such as from 'Draft' to 'In Review' or 'Submitted'.
Why it matters This is a key milestone that initiates the approval cycle. Measuring the time from submission to final approval is critical for identifying bottlenecks in the review process and monitoring SLA compliance. Where to get Inferred from a status field change (e.g., ApprovalStatus changing to 'InReview') on the LedgerJournalTable or from the workflow history logs associated with the journal. Capture Identify timestamp when the journal status changes to a 'submitted' or 'in review' state. Event type inferred
	Journal Entry Corrected	This activity indicates that a previously rejected journal entry has been modified by a user. This is typically inferred by detecting a modification to the journal header or its lines after a 'Rejected' status has been recorded.
Why it matters This activity explicitly identifies rework. Analyzing the frequency and duration of correction loops helps to streamline the process and reduce manual effort. Where to get Inferred by tracking the 'modifiedDateTime' and 'modifiedBy' fields on the LedgerJournalTable or LedgerJournalTrans tables after a 'Journal Rejected' event has occurred. Capture Compare the timestamp of a 'Rejected' status with subsequent modification timestamps by the creator. Event type inferred
	Journal Entry Reversal Initiated	This event represents the start of a process to reverse a previously posted journal entry. This is captured when a user initiates the reversal action in the system.
Why it matters Tracking reversals helps identify the frequency of and reasons for correcting posted entries. This can point to underlying issues in the initial data entry or approval stages. Where to get This is typically an explicit user action that can be captured from audit trails or by identifying the creation of a new reversing journal linked to the original. Capture Use the creation event of a new journal entry that is marked as a reversal of a posted journal. Event type explicit
	Journal Entry Reversed	Marks the completion of the journal entry reversal process, where a reversing entry is successfully posted. This serves as an alternative end point for the lifecycle of an incorrect journal.
Why it matters This activity provides closure on corrected entries and helps analyze the total effort spent on post-posting adjustments, which impacts overall process efficiency. Where to get Captured when the new reversing journal entry is itself moved to a 'Posted' status. The link to the original journal is maintained through a reference field. Capture Identify the 'Posted' status event for the associated reversing journal entry. Event type inferred
	Journal Line Added	This event signifies that a debit or credit line has been added to the journal entry. It is captured each time a new transaction line is created and associated with the journal header.
Why it matters Tracking line item creation helps understand the complexity and data entry effort for different journal types. It can also highlight delays between header creation and line completion. Where to get Captured from the creation timestamp of records in the LedgerJournalTrans entity, linked back to the journal header. Each line creation is a discrete event. Capture Use the 'createdDateTime' field for each record in the LedgerJournalTrans table. Event type explicit
	Journal Posting Attempted	This activity signifies that a user has initiated the posting process for an approved journal. This may be captured explicitly if the system logs the start of the posting job.
Why it matters Distinguishing between the attempt to post and the successful posting can help diagnose system performance issues or batch job delays that affect the financial close. Where to get This may be an explicit event logged in a batch job history table or inferred from a status change to 'Posting in progress' on the LedgerJournalTable. Capture Requires analysis of batch job or system logs related to the General Ledger posting routine. Event type explicit
	Journal Resubmitted For Approval	A corrected journal entry is sent back into the approval workflow for a new review cycle. This is inferred from a status change from 'Rejected' or 'Draft' back to 'In Review' or 'Submitted'.
Why it matters This activity marks the beginning of a rework loop. Counting these events helps quantify the rework rate and the average number of approval loops per journal entry. Where to get Inferred from the workflow history or by tracking status changes on the LedgerJournalTable where the status moves from a rejected state back to a submitted state. Capture Identify a 'Submitted' status event that occurs after a 'Rejected' status event for the same journal. Event type inferred
	Journal Review Started	This activity marks when a reviewer begins actively working on a submitted journal. It can be inferred when the journal is assigned to a reviewer or when the reviewer first opens the record for examination.
Why it matters This activity helps measure reviewer handoff time, which is the delay between submission and the start of the review. It can expose resource allocation or notification issues. Where to get This is often not explicitly logged. It may be inferred from workflow assignment logs or requires comparing the submission timestamp to the first modification timestamp by a reviewer user. Capture Requires analysis of workflow user assignment logs or user activity logs, which may not be standard. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Microsoft Dynamics 365

Steps

Navigate to the Data Management Workspace: Log in to your Microsoft Dynamics 365 environment. Use the main search bar to find and navigate to the 'Data management' workspace.
Create a New Export Project: Within the Data Management workspace, select the 'Export' tile to create a new data export project. Provide a descriptive name for the project, such as 'ProcessMind_JournalEntry_Export'.
Select Data Format: In the project details, specify the target data format. For ease of use in subsequent transformation steps, select 'CSV' or 'Excel'.
Add Required Data Entities: This is the most critical step. You will add several entities to the project to capture all required activities and attributes. For each entity, click 'Add entity' and search for the following entity names. It is recommended to add them one by one.
- Ledger journal headers: Captures creation, posting, and reversal events.
- Ledger journal lines: Captures the addition of individual debit/credit lines.
- Workflow tracking status table: Captures all workflow-related events like submission, approval, and rejection.
Configure Entity Filters: For each added entity, select the 'Filter' option. Apply filters to limit the scope of the export. At a minimum, filter by 'Legal entity' (Company) and a specific date range on a relevant date field, like CREATEDDATETIME on Ledger journal headers, to keep the data volume manageable. For example, export data for the last 3 to 6 months.
Map Entity Fields: Review the field mappings for each entity. Ensure all fields needed for the final attributes are included in the export file. The system usually includes all fields by default.
Execute the Export Job: Once the project is configured, click 'Export' in the Action Pane. The system will create a data package containing separate files for each data entity.
Download the Exported Data: After the export job completes successfully, go to the job history and download the generated data package, which will be a zip file.
Transform Data into an Event Log: This step must be performed outside of Dynamics 365 using a script (e.g., Python, PowerShell) or a data preparation tool. The goal is to combine and transform the data from the multiple exported files into a single event log CSV file. For each source file, create rows corresponding to specific activities. For example:
- From the Ledger journal headers file, create 'Journal Entry Created' events using the creation timestamp and 'Journal Entry Posted' events using the posting timestamp.
- From the Ledger journal lines file, create 'Journal Line Added' events.
- From the Workflow tracking status table file, join it with header data and create events for 'Journal Submitted For Approval', 'Journal Approved', and 'Journal Rejected' based on the workflow status and timestamps.
- Infer events like 'Journal Entry Corrected' by looking for a modified timestamp after a 'Journal Rejected' event.
Structure the Final CSV File: Ensure the final transformed file has the required columns: JournalEntryId, ActivityName, and EventTime, along with any recommended attributes. The file should be sorted by JournalEntryId and then EventTime.
Upload to ProcessMind: The final, single CSV event log is now ready to be uploaded to the ProcessMind platform for analysis.

Configuration

Data Entities: The core of the configuration is selecting the correct entities. The primary ones are Ledger journal headers, Ledger journal lines, and Workflow tracking status table. You may need additional entities depending on your specific workflow configuration.
Export Format: CSV is recommended as it is a universal format easily handled by scripting languages and data tools for the required transformation step.
Filtering: Filtering by Legal Entity (Company) is essential to avoid exporting data from irrelevant business units. Applying a date range filter (e.g., on CREATEDDATETIME) is critical for managing performance and file size. A range of 3-6 months is typical for an initial analysis.
Incremental Exports: For ongoing monitoring, configure the export project to perform incremental exports. This will only extract records that have changed since the last run, which is much more efficient than a full export each time.
Permissions: The user account configuring and running the export job must have the 'Data management administrator' role or equivalent permissions to access the Data Management workspace and the underlying data entities.

a Sample Query config

/*
This is not a single executable query. Instead, it defines the configuration of a Data Management Framework export project. The following entities and fields must be selected and then combined using an external script to build the final event log.
*/

-- Configuration for Entity 1: Ledger journal headers
-- Purpose: Extracts core journal header data for Created, Posted, and Reversal events.
-- Source Entity: Ledger journal headers
-- Key Fields to Map:
--   JournalBatchNumber -> JournalEntryId
--   CREATEDDATETIME -> EventTime (for 'Journal Entry Created')
--   CREATEDBY -> UserName (for 'Journal Entry Created')
--   LedgerJournalWFApprovalStatus -> JournalStatus (for workflow context)
--   JournalType -> JournalType
--   company -> LegalEntity
--   PostedDateTime -> EventTime (for 'Journal Entry Posted')
--   PostedBy -> UserName (for 'Journal Entry Posted')
--   ReverseDate -> EventTime (for 'Journal Entry Reversal Initiated')
--   MODIFIEDDATETIME -> Used to infer 'Journal Entry Corrected'
--   MODIFIEDBY -> UserName

-- Configuration for Entity 2: Ledger journal lines
-- Purpose: Extracts individual transaction lines to create 'Journal Line Added' events.
-- Source Entity: Ledger journal lines
-- Key Fields to Map:
--   JournalBatchNumber -> JournalEntryId
--   CREATEDDATETIME -> EventTime (for 'Journal Line Added')
--   CREATEDBY -> UserName (for 'Journal Line Added')
--   AmountInTransactionCurrency -> JournalTotalAmount (Note: Must be aggregated per JournalEntryId)
--   company -> LegalEntity

-- Configuration for Entity 3: Workflow tracking status table
-- Purpose: Extracts detailed workflow history for approval and rejection events.
-- Source Entity: Workflow tracking status table
-- Key Fields to Map:
--   ContextRecId -> (This needs to be joined back to the RecId of the LedgerJournalTable to get the JournalBatchNumber)
--   ContextTableId -> (Used to filter for Journal related workflows)
--   CREATEDDATETIME -> EventTime
--   User -> UserName
--   TrackingStatus -> Used to derive ActivityName ('Submitted For Approval', 'Journal Approved', 'Journal Rejected')
--   Comment -> RejectionReason

-- **Post-Processing Transformation Logic (to be implemented in an external script)**

-- 1. For each row in 'Ledger journal headers' export:
--    - Create one row with ActivityName = 'Journal Entry Created', EventTime = CREATEDDATETIME.
--    - If PostedDateTime is not null, create one row with ActivityName = 'Journal Entry Posted', EventTime = PostedDateTime.
--    - If ReverseDate is not null, create one row with ActivityName = 'Journal Entry Reversal Initiated', EventTime = ReverseDate. (A separate 'Journal Entry Reversed' event may need to be derived from the creation of the reversing journal).

-- 2. For each row in 'Ledger journal lines' export:
--    - Create one row with ActivityName = 'Journal Line Added', EventTime = CREATEDDATETIME.

-- 3. For each row in 'Workflow tracking status table' export (after joining to get JournalBatchNumber):
--    - IF TrackingStatus indicates submission, create ActivityName = 'Journal Submitted For Approval'.
--    - IF TrackingStatus indicates approval, create ActivityName = 'Journal Approved'.
--    - IF TrackingStatus indicates rejection, create ActivityName = 'Journal Rejected'.
--    - Other activities like 'Journal Review Started', 'Journal Entry Corrected', 'Journal Resubmitted For Approval', and 'Journal Posting Attempted' are often not explicitly logged. They must be inferred from status changes, user actions within a timeframe, or modification timestamps on the journal header after a rejection event.

Steps

Configure D365 to Azure Data Lake Export: Before running any queries, ensure the native Microsoft Dynamics 365 'Export to Azure Data Lake' feature is configured and active. Select the necessary data entities for the journal entry process, including at a minimum: LedgerJournalTable, LedgerJournalTrans, GeneralJournalEntry, GeneralJournalAccountEntry, and WorkflowTrackingStatusTable. Allow time for the initial data synchronization to complete.
Navigate to Azure Synapse Analytics: Open your Azure Portal and navigate to the Synapse Analytics workspace that is connected to the Azure Data Lake Storage account containing your Dynamics 365 data.
Access the SQL Database: Within Synapse Studio, go to the 'Data' hub. Locate the serverless SQL database that has been created over your data lake files. The D365 entities will be available as queryable tables or views.
Create a New SQL Script: Open the 'Develop' hub and create a new, blank SQL script. This is where you will build the event log query.
Copy and Adapt the Query: Copy the complete SQL query provided in the 'query' section of this document into the new script editor in Synapse Studio.
Update Placeholders: Carefully review the query and replace all placeholder values. You must update the database and schema name, for example [YourD365DB].[dbo], and set the desired StartDate and EndDate parameters for your analysis period.
Execute the Query: Run the script using the serverless SQL pool. The execution time will vary based on the date range and the volume of data in your system.
Review the Results: Once the query completes, the results pane will show the generated event log. Perform a quick review to ensure the columns (JournalEntryId, ActivityName, EventTime, etc.) and data look correct.
Export the Event Log: Use the export functionality available in your query tool or Synapse Studio to save the result set as a CSV file. Right-click the results and select an option like 'Save as CSV'.
Prepare for Upload: Ensure the exported CSV file is UTF-8 encoded and that the column headers match the requirements for ProcessMind. No further data transformation should be necessary.

Configuration

Prerequisites: This method requires a functioning integration between Microsoft Dynamics 365 Finance and an Azure Data Lake Storage Gen2 account. An Azure Synapse Analytics workspace must be set up and configured to query this data. Users require appropriate read permissions in both Azure Synapse and the data lake.
Data Entities: Ensure the following D365 entities are included in the export to the data lake: LedgerJournalTable, LedgerJournalTrans, WorkflowTrackingStatusTable, GeneralJournalEntry, and UserInfo.
Date Range Filtering: The provided query includes @StartDate and @EndDate parameters. It is highly recommended to start with a limited date range, such as 3 to 6 months, to manage query performance and cost. Analyze a representative period of business activity.
Legal Entity Filtering: The query should be filtered by the DataAreaId (Legal Entity) to focus the analysis on a specific company. This is a critical filter for performance in large D365 environments. The placeholder [YourLegalEntity] should be replaced.
Workflow Configuration: The query's logic for approval and rejection events assumes the use of the standard D365 workflow engine, which logs data to the WorkflowTrackingStatusTable. If your organization uses a different or custom approval mechanism, the query will need to be adapted.

a Sample Query sql

DECLARE @StartDate DATETIME = '2023-01-01';
DECLARE @EndDate DATETIME = '2023-12-31';

WITH JournalHeaders AS (
    SELECT 
        JOURNALBATCHNUMBER AS JournalEntryId,
        JOURNALTYPE AS JournalType,
        DOCUMENTSTATUS AS JournalStatus, -- Captures Posted, etc.
        APPROVALSTATUS AS ApprovalStatus, -- Captures Draft, InReview, Approved, Rejected
        LOG AS RejectionReason, -- Often contains rejection comments
        POSTEDDATETIME, 
        CREATEDDATETIME,
        CREATEDBY,
        MODIFIEDDATETIME,
        MODIFIEDBY,
        DATA indignationId
    FROM 
        [YourD365DB].[dbo].LedgerJournalTable
    WHERE 
        CREATEDDATETIME BETWEEN @StartDate AND @EndDate
        AND DATA indignationId = '[YourLegalEntity]'
),
WorkflowEvents AS (
    SELECT 
        wtst.CONTEXTRECID,
        wtst.CONTEXTTABLEID,
        wtst.USERID,
        wtst.CREATEDDATETIME,
        wtst.TRACKINGSTATUS, -- Submitted, Approved, Rejected
        wtsi.INSTANCENUMBER
    FROM 
        [YourD365DB].[dbo].WorkflowTrackingStatusTable wtst
    JOIN 
        [YourD365DB].[dbo].WorkflowTrackingTable wtsi ON wtst.WORKFLOWTRACKINGTABLEINSTANCENUMBER = wtsi.RECID
    WHERE 
        wtst.CONTEXTTABLEID = (SELECT TABLEID FROM [YourD365DB].[dbo].SysTableIdTable WHERE NAME = 'LedgerJournalTable')
)

-- 1. Journal Entry Created
SELECT 
    jh.JournalEntryId AS JournalEntryId,
    'Journal Entry Created' AS ActivityName,
    jh.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    CAST(jh.ApprovalStatus AS VARCHAR) AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    (SELECT SUM(ABS(jlt.AMOUNTCREDIT)) FROM [YourD365DB].[dbo].LedgerJournalTrans jlt WHERE jlt.JOURNALBATCHNUMBER = jh.JournalEntryId) AS JournalTotalAmount
FROM 
    JournalHeaders jh
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON jh.CREATEDBY = u.ID

UNION ALL

-- 2. Journal Line Added
SELECT 
    jlt.JOURNALBATCHNUMBER AS JournalEntryId,
    'Journal Line Added' AS ActivityName,
    jlt.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    CAST(jh.ApprovalStatus AS VARCHAR) AS JournalStatus,
    NULL AS RejectionReason,
    jlt.DATA indignationId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    [YourD365DB].[dbo].LedgerJournalTrans jlt
JOIN 
    JournalHeaders jh ON jlt.JOURNALBATCHNUMBER = jh.JournalEntryId AND jlt.DATA indignationId = jh.DataAreaId
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON jlt.CREATEDBY = u.ID

UNION ALL

-- 3. Journal Submitted For Approval
SELECT 
    jh.JournalEntryId,
    'Journal Submitted For Approval' AS ActivityName,
    we.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'InReview' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
JOIN 
    WorkflowEvents we ON jh.RecId = we.CONTEXTRECID
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON we.USERID = u.ID
WHERE 
    we.TRACKINGSTATUS = 1 -- Submitted

UNION ALL

-- 4. Journal Review Started (Approximation)
SELECT 
    jh.JournalEntryId,
    'Journal Review Started' AS ActivityName,
    we.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'InReview' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
JOIN 
    WorkflowEvents we ON jh.RecId = we.CONTEXTRECID
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON we.USERID = u.ID
WHERE 
    we.TRACKINGTYPE = 5 -- Work item creation

UNION ALL

-- 5. Journal Approved
SELECT 
    jh.JournalEntryId,
    'Journal Approved' AS ActivityName,
    we.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Approved' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
JOIN 
    WorkflowEvents we ON jh.RecId = we.CONTEXTRECID
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON we.USERID = u.ID
WHERE 
    we.TRACKINGSTATUS = 5 -- Completion

UNION ALL

-- 6. Journal Rejected
SELECT 
    jh.JournalEntryId,
    'Journal Rejected' AS ActivityName,
    we.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Rejected' AS JournalStatus,
    jh.RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
JOIN 
    WorkflowEvents we ON jh.RecId = we.CONTEXTRECID
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON we.USERID = u.ID
WHERE 
    we.TRACKINGSTATUS = 3 -- Rejection

UNION ALL

-- 7. Journal Entry Corrected (Approximation)
SELECT 
    jh.JournalEntryId,
    'Journal Entry Corrected' AS ActivityName,
    jh.MODIFIEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Corrected' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON jh.MODIFIEDBY = u.ID
WHERE 
    jh.MODIFIEDDATETIME > jh.CREATEDDATETIME AND
    EXISTS (
        SELECT 1 FROM WorkflowEvents we 
        WHERE we.CONTEXTRECID = jh.RecId AND we.TRACKINGSTATUS = 3 -- Rejection
        AND jh.MODIFIEDDATETIME > we.CREATEDDATETIME
    )

UNION ALL

-- 8. Journal Resubmitted For Approval
SELECT 
    jh.JournalEntryId,
    'Journal Resubmitted For Approval' AS ActivityName,
    we.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'InReview' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
JOIN 
    WorkflowEvents we ON jh.RecId = we.CONTEXTRECID
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON we.USERID = u.ID
WHERE 
    we.TRACKINGSTATUS = 1 -- Submitted
    AND EXISTS (
        SELECT 1 FROM WorkflowEvents rej_we 
        WHERE rej_we.CONTEXTRECID = jh.RecId AND rej_we.TRACKINGSTATUS = 3 -- Rejection
        AND we.CREATEDDATETIME > rej_we.CREATEDDATETIME
    )

UNION ALL

-- 9. Journal Posting Attempted (Approximation)
SELECT 
    jh.JournalEntryId,
    'Journal Posting Attempted' AS ActivityName,
    DATEADD(second, -5, jh.POSTEDDATETIME) AS EventTime, -- Approximated as just before posting
    jh.POSTEDDATETIME AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Posting' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON jh.MODIFIEDBY = u.ID -- Often posted by the last modifier or a system account
WHERE 
    jh.POSTEDDATETIME IS NOT NULL

UNION ALL

-- 10. Journal Entry Posted
SELECT 
    jh.JournalEntryId,
    'Journal Entry Posted' AS ActivityName,
    jh.POSTEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Posted' AS JournalStatus,
    NULL AS RejectionReason,
    jh.DataAreaId AS LegalEntity,
    NULL AS JournalTotalAmount
FROM 
    JournalHeaders jh
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON jh.MODIFIEDBY = u.ID
WHERE 
    jh.POSTEDDATETIME IS NOT NULL

UNION ALL

-- 11. Journal Entry Reversal Initiated
SELECT 
    gje.JOURNALNUMBER AS JournalEntryId,
    'Journal Entry Reversal Initiated' AS ActivityName,
    gje_rev.CREATEDDATETIME AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Reversing' AS JournalStatus,
    NULL AS RejectionReason,
    gje.LEDGER AS LegalEntity,
    NULL AS JournalTotalAmount
FROM
    [YourD365DB].[dbo].GeneralJournalEntry gje
JOIN
    [YourD365DB].[dbo].GeneralJournalEntry gje_rev ON gje.JOURNALNUMBER = gje_rev.OriginalJournalNumber
JOIN
    JournalHeaders jh ON gje.JOURNALNUMBER = jh.JournalEntryId
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON gje_rev.CREATEDBY = u.ID
WHERE
    gje.REVERSED = 'Yes'

UNION ALL

-- 12. Journal Entry Reversed
SELECT 
    gje.JOURNALNUMBER AS JournalEntryId,
    'Journal Entry Reversed' AS ActivityName,
    gje.REVERSEDDATE AS EventTime,
    NULL AS EventEndTime,
    u.NAME AS UserName,
    jh.JournalType,
    'Reversed' AS JournalStatus,
    NULL AS RejectionReason,
    gje.LEDGER AS LegalEntity,
    NULL AS JournalTotalAmount
FROM
    [YourD365DB].[dbo].GeneralJournalEntry gje
JOIN
    JournalHeaders jh ON gje.JOURNALNUMBER = jh.JournalEntryId
LEFT JOIN 
    [YourD365DB].[dbo].UserInfo u ON gje.MODIFIEDBY = u.ID
WHERE
    gje.REVERSED = 'Yes' AND gje.REVERSEDDATE IS NOT NULL;

Steps

Prerequisites and Environment Setup: Install Python 3.x on your machine. Install the necessary Python libraries using pip: pip install pandas requests adal. The adal library is used for Azure Active Directory authentication, which is required for Dynamics 365.
Register an Application in Azure AD: To access the Dynamics 365 API, you must register an application in your Azure Active Directory. Note the Application (client) ID, Directory (tenant) ID, and create a client secret. Grant this application delegated permissions to access Dynamics 365 Finance & Operations, such as Financials.Read.
Configure the Python Script: Copy the provided Python script into a file, for example extract_journals.py. Fill in the placeholder variables at the top of the script with your Azure AD and Dynamics 365 information: TENANT_ID, CLIENT_ID, CLIENT_SECRET, and D365_URL.
Set Extraction Parameters: Adjust the START_DATE and END_DATE variables in the script to define the time window for the data extraction. You can also customize the LEGAL_ENTITIES list to filter for specific company codes.
Execute the Extraction Script: Run the script from your command line using python extract_journals.py. The script will authenticate with Azure AD, fetch an access token, and begin querying the Dynamics 365 OData API for journal header and line data within the specified date range.
Monitor Script Execution: The script will print progress messages to the console as it fetches data pages from the API. This can take a significant amount of time depending on the data volume. The script handles API pagination automatically to retrieve all relevant records.
Review the Raw Data (Optional): The script first saves the raw queried data into raw_journal_headers.csv and raw_journal_lines.csv. You can inspect these files to ensure the initial data pull was successful before the transformation logic is applied.
Understand Event Inference: The script processes the raw data to infer the 12 required process activities. For instance, 'Journal Submitted For Approval' is inferred when the WorkflowApprovalStatus field changes from 'Draft' to 'Submitted'. The timestamp for such events is taken from the ModifiedDateTime field of the record.
Generate the Final Event Log: After processing, the script combines all inferred events into a single, structured event log. It creates the required columns: JournalEntryId, ActivityName, EventTime, and other recommended attributes.
Export and Verify the CSV File: The final event log is saved as journal_entry_event_log.csv. Open this file to verify its structure and content. Ensure that it contains the expected columns and that data for various activities has been generated.
Prepare for ProcessMind Upload: The generated CSV file is in a suitable format for upload. Ensure the file is saved with UTF-8 encoding. You can now use this file as a data source in ProcessMind to analyze your Record to Report journal entry process.

Configuration

API Endpoint URL: The base URL for your Dynamics 365 Finance & Operations OData API. This should be set in the D365_URL variable in the script, for example, https://your-org.operations.dynamics.com.
Azure AD Credentials: The script requires an Azure AD application registration. You must provide the TENANT_ID, CLIENT_ID, and CLIENT_SECRET for authentication.
Required API Permissions: The Azure AD application needs delegated permissions to read data from Dynamics 365. The Financials.Read permission is typically sufficient for accessing journal data.
Date Range: The START_DATE and END_DATE variables in the script control the extraction period. For initial analysis, a period of 3 to 6 months is recommended to balance data volume and process insights.
Legal Entity Filter: The LEGAL_ENTITIES list in the script allows you to specify which company codes (dataAreaId) to include in the extraction. This is critical for focusing the analysis on relevant parts of the business.
Data Entities: The script primarily queries the LedgerJournalHeaders and LedgerJournalLines OData entities. These are standard entities for journal entry data in Dynamics 365.
Status Field Mapping: The script's logic for inferring activities is based on the standard WorkflowApprovalStatus field. If your implementation uses a custom field for tracking journal status, you will need to update the field name in the script.

a Sample Query python

import adal
import requests
import pandas as pd
from datetime import datetime

class=class="hl-string">"hl-comment"># --- Configuration --- 
class=class="hl-string">"hl-comment"># Azure AD Application Details
TENANT_ID = class="hl-string">'[Your Tenant ID]'
CLIENT_ID = class="hl-string">'[Your Application Client ID]'
CLIENT_SECRET = class="hl-string">'[Your Client Secret]'

class=class="hl-string">"hl-comment"># Dynamics 365 F&O Environment Details
D365_URL = class="hl-string">'https://[your-d365-url].operations.dynamics.com'
RESOURCE = D365_URL
AUTHORITY_URL = fclass="hl-string">'https://login.microsoftonline.com/{TENANT_ID}'

class=class="hl-string">"hl-comment"># Extraction Parameters
START_DATE = class="hl-string">'2023-01-01T00:00:00Z'
END_DATE = class="hl-string">'2023-12-31T23:59:59Z'
LEGAL_ENTITIES = [class="hl-string">'[YourLegalEntity1]', class="hl-string">'[YourLegalEntity2]'] class=class="hl-string">"hl-comment"># e.g., [class="hl-string">'USMF']

class=class="hl-string">"hl-comment"># --- OData Helper Functions ---

def get_access_token():
    class="hl-string">"""Authenticates with Azure AD to get an access token."""
    context = adal.AuthenticationContext(AUTHORITY_URL)
    token = context.acquire_token_with_client_credentials(RESOURCE, CLIENT_ID, CLIENT_SECRET)
    if class="hl-string">'accessToken' not in token:
        raise Exception(fclass="hl-string">"Failed to get access token: {token.get('error_description')}")
    return token[class="hl-string">'accessToken']

def execute_odata_query(entity_url, headers):
    class="hl-string">"""Executes a paginated OData query and returns a list of records."""
    all_records = []
    next_link = entity_url
    page_num = 1
    while next_link:
        print(fclass="hl-string">"Fetching page {page_num} for entity: {entity_url.split('/')[-1].split('?')[0]}...")
        response = requests.get(next_link, headers=headers)
        if response.status_code != 200:
            raise Exception(fclass="hl-string">"Query failed with status {response.status_code}: {response.text}")
        data = response.json()
        all_records.extend(data[class="hl-string">'value'])
        next_link = data.get(class="hl-string">'@odata.nextLink')
        page_num += 1
    return all_records

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

if __name__ == class="hl-string">"__main__":
    print(class="hl-string">"Starting Journal Entry data extraction...")
    
    class=class="hl-string">"hl-comment"># 1. Get Access Token
    access_token = get_access_token()
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json',
        class="hl-string">'OData-MaxVersion': class="hl-string">'4.0',
        class="hl-string">'OData-Version': class="hl-string">'4.0',
        class="hl-string">'Accept': class="hl-string">'application/json'
    }

    class=class="hl-string">"hl-comment"># 2. Build and Execute Queries
    legal_entity_filter = class="hl-string">" or ".join([fclass="hl-string">"dataAreaId eq '{le}'" for le in LEGAL_ENTITIES])
    date_filter = fclass="hl-string">"CreatedDateTime ge {START_DATE} and CreatedDateTime le {END_DATE}"

    class=class="hl-string">"hl-comment"># Query LedgerJournalHeaders
    headers_fields = [
        class="hl-string">'JournalBatchNumber', class="hl-string">'dataAreaId', class="hl-string">'LedgerJournalType', class="hl-string">'WorkflowApprovalStatus',
        class="hl-string">'CreatedBy', class="hl-string">'CreatedDateTime', class="hl-string">'ModifiedBy', class="hl-string">'ModifiedDateTime', class="hl-string">'PostedDateTime',
        class="hl-string">'JournalTotalDebit', class="hl-string">'Reversed', class="hl-string">'ReversedDate', class="hl-string">'RejectionReasonComment' class=class="hl-string">"hl-comment"># Assuming a field for rejection reason
    ]
    headers_select = fclass="hl-string">"$select={','.join(headers_fields)}"
    headers_filter = fclass="hl-string">"$filter=({legal_entity_filter}) and ({date_filter})"
    headers_url = fclass="hl-string">"{D365_URL}/data/LedgerJournalHeaders?{headers_select}&{headers_filter}&cross-company=true"
    
    journal_headers_raw = execute_odata_query(headers_url, headers)
    if not journal_headers_raw:
        print(class="hl-string">"No journal headers found for the specified criteria. Exiting.")
        exit()

    df_headers = pd.DataFrame(journal_headers_raw)
    df_headers.to_csv(class="hl-string">'raw_journal_headers.csv', index=False)
    print(fclass="hl-string">"Fetched and saved {len(df_headers)} journal headers.")

    class=class="hl-string">"hl-comment"># Query LedgerJournalLines for class="hl-string">'Line Added' activity
    journal_batch_numbers = df_headers[class="hl-string">'JournalBatchNumber'].unique().tolist()
    journal_batch_filter = class="hl-string">" or ".join([fclass="hl-string">"JournalBatchNumber eq '{jbn}'" for jbn in journal_batch_numbers])
    
    lines_fields = [class="hl-string">'JournalBatchNumber', class="hl-string">'CreatedDateTime', class="hl-string">'CreatedBy']
    lines_select = fclass="hl-string">"$select={','.join(lines_fields)}"
    lines_filter = fclass="hl-string">"$filter=({legal_entity_filter}) and ({journal_batch_filter})"
    lines_url = fclass="hl-string">"{D365_URL}/data/LedgerJournalLines?{lines_select}&{lines_filter}&cross-company=true"

    journal_lines_raw = execute_odata_query(lines_url, headers)
    df_lines = pd.DataFrame(journal_lines_raw)
    df_lines.to_csv(class="hl-string">'raw_journal_lines.csv', index=False)
    print(fclass="hl-string">"Fetched and saved {len(df_lines)} journal lines.")

    class=class="hl-string">"hl-comment"># 3. Transform Data into Event Log Format
    print(class="hl-string">"Transforming raw data into event log...")
    event_log_frames = []

    class=class="hl-string">"hl-comment"># Rename columns for consistency
    df_headers.rename(columns={
        class="hl-string">'JournalBatchNumber': class="hl-string">'JournalEntryId',
        class="hl-string">'dataAreaId': class="hl-string">'LegalEntity',
        class="hl-string">'LedgerJournalType': class="hl-string">'JournalType',
        class="hl-string">'JournalTotalDebit': class="hl-string">'JournalTotalAmount',
        class="hl-string">'WorkflowApprovalStatus': class="hl-string">'JournalStatus',
        class="hl-string">'RejectionReasonComment': class="hl-string">'RejectionReason'
    }, inplace=True)
    df_lines.rename(columns={class="hl-string">'JournalBatchNumber': class="hl-string">'JournalEntryId'}, inplace=True)

    class=class="hl-string">"hl-comment"># Activity: Journal Entry Created
    df_created = df_headers[[class="hl-string">'JournalEntryId', class="hl-string">'CreatedDateTime', class="hl-string">'CreatedBy']].copy()
    df_created[class="hl-string">'ActivityName'] = class="hl-string">'Journal Entry Created'
    df_created.rename(columns={class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'CreatedBy': class="hl-string">'UserName'}, inplace=True)
    event_log_frames.append(df_created)

    class=class="hl-string">"hl-comment"># Activity: Journal Line Added
    df_line_added = df_lines[[class="hl-string">'JournalEntryId', class="hl-string">'CreatedDateTime', class="hl-string">'CreatedBy']].copy()
    df_line_added[class="hl-string">'ActivityName'] = class="hl-string">'Journal Line Added'
    df_line_added.rename(columns={class="hl-string">'CreatedDateTime': class="hl-string">'EventTime', class="hl-string">'CreatedBy': class="hl-string">'UserName'}, inplace=True)
    event_log_frames.append(df_line_added)

    class=class="hl-string">"hl-comment"># Activities inferred from status changes and modifications
    class=class="hl-string">"hl-comment"># This part requires a more stateful analysis if audit trails are not directly available
    class=class="hl-string">"hl-comment"># We use ModifiedDateTime as a proxy for the event time of status changes.
    
    class=class="hl-string">"hl-comment"># NOTE: The logic below is a simplified inference. A real implementation might require parsing audit trail logs for precise timestamps.
    status_events_map = {
        class="hl-string">'Submitted': class="hl-string">'Journal Submitted For Approval',
        class="hl-string">'InProgress': class="hl-string">'Journal Review Started', class=class="hl-string">"hl-comment"># Approximation
        class="hl-string">'Approved': class="hl-string">'Journal Approved',
        class="hl-string">'Rejected': class="hl-string">'Journal Rejected',
        class="hl-string">'ChangeRequested': class="hl-string">'Journal Rejected', class=class="hl-string">"hl-comment"># Map another status to Rejected
        class="hl-string">'Canceled': class="hl-string">'Journal Rejected' class=class="hl-string">"hl-comment"># Map another status to Rejected
    }

    status_change_df = df_headers[df_headers[class="hl-string">'JournalStatus'].isin(status_events_map.keys())].copy()
    status_change_df[class="hl-string">'ActivityName'] = status_change_df[class="hl-string">'JournalStatus'].map(status_events_map)
    status_change_df.rename(columns={class="hl-string">'ModifiedDateTime': class="hl-string">'EventTime', class="hl-string">'ModifiedBy': class="hl-string">'UserName'}, inplace=True)
    event_log_frames.append(status_change_df[[class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName', class="hl-string">'RejectionReason']])

    class=class="hl-string">"hl-comment"># Activity: Journal Resubmitted For Approval & Journal Entry Corrected
    class=class="hl-string">"hl-comment"># Infer this by finding a class="hl-string">'Submitted' status that occurred after a class="hl-string">'Rejected' status for the same journal.
    class=class="hl-string">"hl-comment"># For simplicity, we assume any modification after rejection is a correction.
    df_rejected = status_change_df[status_change_df[class="hl-string">'ActivityName'] == class="hl-string">'Journal Rejected']
    for index, row in df_rejected.iterrows():
        class=class="hl-string">"hl-comment"># Check for modifications on the header after rejection
        header_row = df_headers[df_headers[class="hl-string">'JournalEntryId'] == row[class="hl-string">'JournalEntryId']].iloc[0]
        if pd.to_datetime(header_row[class="hl-string">'ModifiedDateTime']) > pd.to_datetime(row[class="hl-string">'EventTime']):
            class=class="hl-string">"hl-comment"># Add Corrected Event
            corrected_event = row.copy()
            corrected_event[class="hl-string">'ActivityName'] = class="hl-string">'Journal Entry Corrected'
            corrected_event[class="hl-string">'EventTime'] = header_row[class="hl-string">'ModifiedDateTime']
            corrected_event[class="hl-string">'UserName'] = header_row[class="hl-string">'ModifiedBy']
            event_log_frames.append(pd.DataFrame([corrected_event]))
            
            class=class="hl-string">"hl-comment"># If status went back to submitted, it's a resubmission
            if header_row[class="hl-string">'JournalStatus'] == class="hl-string">'Submitted':
                resubmitted_event = row.copy()
                resubmitted_event[class="hl-string">'ActivityName'] = class="hl-string">'Journal Resubmitted For Approval'
                resubmitted_event[class="hl-string">'EventTime'] = header_row[class="hl-string">'ModifiedDateTime']
                resubmitted_event[class="hl-string">'UserName'] = header_row[class="hl-string">'ModifiedBy']
                event_log_frames.append(pd.DataFrame([resubmitted_event]))

    class=class="hl-string">"hl-comment"># Activity: Journal Posting Attempted (Approximation)
    class=class="hl-string">"hl-comment"># This is difficult to capture. We'll use the approval time as a proxy.
    df_approved_for_posting = status_change_df[status_change_df[class="hl-string">'ActivityName'] == class="hl-string">'Journal Approved'].copy()
    df_approved_for_posting[class="hl-string">'ActivityName'] = class="hl-string">'Journal Posting Attempted'
    event_log_frames.append(df_approved_for_posting[[class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']])

    class=class="hl-string">"hl-comment"># Activity: Journal Entry Posted
    df_posted = df_headers[df_headers[class="hl-string">'PostedDateTime'].notna()].copy()
    if not df_posted.empty:
        df_posted[class="hl-string">'ActivityName'] = class="hl-string">'Journal Entry Posted'
        class=class="hl-string">"hl-comment"># Use PostedDateTime if available, otherwise fallback to ModifiedDateTime
        df_posted[class="hl-string">'EventTime'] = df_posted[class="hl-string">'PostedDateTime'].fillna(df_posted[class="hl-string">'ModifiedDateTime'])
        df_posted.rename(columns={class="hl-string">'ModifiedBy': class="hl-string">'UserName'}, inplace=True)
        event_log_frames.append(df_posted[[class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']])

    class=class="hl-string">"hl-comment"># Activity: Journal Entry Reversal Initiated & Reversed
    df_reversed = df_headers[df_headers[class="hl-string">'Reversed'] == True].copy()
    if not df_reversed.empty:
        class=class="hl-string">"hl-comment"># Reversal Initiated (approximated by modification date when Reversed flag is set)
        df_reversal_initiated = df_reversed.copy()
        df_reversal_initiated[class="hl-string">'ActivityName'] = class="hl-string">'Journal Entry Reversal Initiated'
        df_reversal_initiated.rename(columns={class="hl-string">'ModifiedDateTime': class="hl-string">'EventTime', class="hl-string">'ModifiedBy': class="hl-string">'UserName'}, inplace=True)
        event_log_frames.append(df_reversal_initiated[[class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']])
        class=class="hl-string">"hl-comment"># Reversed (using the specific ReversedDate)
        df_reversed_final = df_reversed[df_reversed[class="hl-string">'ReversedDate'].notna()].copy()
        df_reversed_final[class="hl-string">'ActivityName'] = class="hl-string">'Journal Entry Reversed'
        df_reversed_final.rename(columns={class="hl-string">'ReversedDate': class="hl-string">'EventTime', class="hl-string">'ModifiedBy': class="hl-string">'UserName'}, inplace=True)
        event_log_frames.append(df_reversed_final[[class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName']])

    class=class="hl-string">"hl-comment"># 4. Assemble Final Event Log
    print(class="hl-string">"Assembling the final event log...")
    final_event_log = pd.concat(event_log_frames, ignore_index=True)

    class=class="hl-string">"hl-comment"># Merge attributes from the header file
    attributes_to_merge = df_headers[[class="hl-string">'JournalEntryId', class="hl-string">'LegalEntity', class="hl-string">'JournalType', class="hl-string">'JournalStatus', class="hl-string">'JournalTotalAmount', class="hl-string">'RejectionReason']].drop_duplicates()
    final_event_log = pd.merge(final_event_log, attributes_to_merge, on=class="hl-string">'JournalEntryId', how=class="hl-string">'left')

    class=class="hl-string">"hl-comment"># Clean up and order columns
    final_event_log[class="hl-string">'EventTime'] = pd.to_datetime(final_event_log[class="hl-string">'EventTime'])
    final_event_log.sort_values(by=[class="hl-string">'JournalEntryId', class="hl-string">'EventTime'], inplace=True)
    final_event_log.drop_duplicates(subset=[class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime'], inplace=True)
    
    class=class="hl-string">"hl-comment"># Define column order
    final_columns = [
        class="hl-string">'JournalEntryId', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'UserName', class="hl-string">'LegalEntity',
        class="hl-string">'JournalType', class="hl-string">'JournalStatus', class="hl-string">'JournalTotalAmount', class="hl-string">'RejectionReason'
    ]
    class=class="hl-string">"hl-comment"># Add missing columns with None
    for col in final_columns:
        if col not in final_event_log.columns:
            final_event_log[col] = None
    final_event_log = final_event_log[final_columns]
    
    class=class="hl-string">"hl-comment"># Add EventEndTime (optional, can be populated if duration is known)
    final_event_log[class="hl-string">'EventEndTime'] = None
    
    class=class="hl-string">"hl-comment"># 5. Export to CSV
    final_event_log.to_csv(class="hl-string">'journal_entry_event_log.csv', index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%SZ')
    print(fclass="hl-string">"Extraction complete. Event log with {len(final_event_log)} events saved to journal_entry_event_log.csv")

Steps

Navigate to the Data Management Workspace: Log in to your Microsoft Dynamics 365 environment. Use the main search bar to find and navigate to the 'Data management' workspace.
Create a New Export Project: Within the Data Management workspace, select the 'Export' tile to create a new data export project. Provide a descriptive name for the project, such as 'ProcessMind_JournalEntry_Export'.
Select Data Format: In the project details, specify the target data format. For ease of use in subsequent transformation steps, select 'CSV' or 'Excel'.
Add Required Data Entities: This is the most critical step. You will add several entities to the project to capture all required activities and attributes. For each entity, click 'Add entity' and search for the following entity names. It is recommended to add them one by one.
- Ledger journal headers: Captures creation, posting, and reversal events.
- Ledger journal lines: Captures the addition of individual debit/credit lines.
- Workflow tracking status table: Captures all workflow-related events like submission, approval, and rejection.
Configure Entity Filters: For each added entity, select the 'Filter' option. Apply filters to limit the scope of the export. At a minimum, filter by 'Legal entity' (Company) and a specific date range on a relevant date field, like CREATEDDATETIME on Ledger journal headers, to keep the data volume manageable. For example, export data for the last 3 to 6 months.
Map Entity Fields: Review the field mappings for each entity. Ensure all fields needed for the final attributes are included in the export file. The system usually includes all fields by default.
Execute the Export Job: Once the project is configured, click 'Export' in the Action Pane. The system will create a data package containing separate files for each data entity.
Download the Exported Data: After the export job completes successfully, go to the job history and download the generated data package, which will be a zip file.
Transform Data into an Event Log: This step must be performed outside of Dynamics 365 using a script (e.g., Python, PowerShell) or a data preparation tool. The goal is to combine and transform the data from the multiple exported files into a single event log CSV file. For each source file, create rows corresponding to specific activities. For example:
- From the Ledger journal headers file, create 'Journal Entry Created' events using the creation timestamp and 'Journal Entry Posted' events using the posting timestamp.
- From the Ledger journal lines file, create 'Journal Line Added' events.
- From the Workflow tracking status table file, join it with header data and create events for 'Journal Submitted For Approval', 'Journal Approved', and 'Journal Rejected' based on the workflow status and timestamps.
- Infer events like 'Journal Entry Corrected' by looking for a modified timestamp after a 'Journal Rejected' event.
Structure the Final CSV File: Ensure the final transformed file has the required columns: JournalEntryId, ActivityName, and EventTime, along with any recommended attributes. The file should be sorted by JournalEntryId and then EventTime.
Upload to ProcessMind: The final, single CSV event log is now ready to be uploaded to the ProcessMind platform for analysis.

Configuration

Data Entities: The core of the configuration is selecting the correct entities. The primary ones are Ledger journal headers, Ledger journal lines, and Workflow tracking status table. You may need additional entities depending on your specific workflow configuration.
Export Format: CSV is recommended as it is a universal format easily handled by scripting languages and data tools for the required transformation step.
Filtering: Filtering by Legal Entity (Company) is essential to avoid exporting data from irrelevant business units. Applying a date range filter (e.g., on CREATEDDATETIME) is critical for managing performance and file size. A range of 3-6 months is typical for an initial analysis.
Incremental Exports: For ongoing monitoring, configure the export project to perform incremental exports. This will only extract records that have changed since the last run, which is much more efficient than a full export each time.
Permissions: The user account configuring and running the export job must have the 'Data management administrator' role or equivalent permissions to access the Data Management workspace and the underlying data entities.

a Sample Query config

/*
This is not a single executable query. Instead, it defines the configuration of a Data Management Framework export project. The following entities and fields must be selected and then combined using an external script to build the final event log.
*/

-- Configuration for Entity 1: Ledger journal headers
-- Purpose: Extracts core journal header data for Created, Posted, and Reversal events.
-- Source Entity: Ledger journal headers
-- Key Fields to Map:
--   JournalBatchNumber -> JournalEntryId
--   CREATEDDATETIME -> EventTime (for 'Journal Entry Created')
--   CREATEDBY -> UserName (for 'Journal Entry Created')
--   LedgerJournalWFApprovalStatus -> JournalStatus (for workflow context)
--   JournalType -> JournalType
--   company -> LegalEntity
--   PostedDateTime -> EventTime (for 'Journal Entry Posted')
--   PostedBy -> UserName (for 'Journal Entry Posted')
--   ReverseDate -> EventTime (for 'Journal Entry Reversal Initiated')
--   MODIFIEDDATETIME -> Used to infer 'Journal Entry Corrected'
--   MODIFIEDBY -> UserName

-- Configuration for Entity 2: Ledger journal lines
-- Purpose: Extracts individual transaction lines to create 'Journal Line Added' events.
-- Source Entity: Ledger journal lines
-- Key Fields to Map:
--   JournalBatchNumber -> JournalEntryId
--   CREATEDDATETIME -> EventTime (for 'Journal Line Added')
--   CREATEDBY -> UserName (for 'Journal Line Added')
--   AmountInTransactionCurrency -> JournalTotalAmount (Note: Must be aggregated per JournalEntryId)
--   company -> LegalEntity

-- Configuration for Entity 3: Workflow tracking status table
-- Purpose: Extracts detailed workflow history for approval and rejection events.
-- Source Entity: Workflow tracking status table
-- Key Fields to Map:
--   ContextRecId -> (This needs to be joined back to the RecId of the LedgerJournalTable to get the JournalBatchNumber)
--   ContextTableId -> (Used to filter for Journal related workflows)
--   CREATEDDATETIME -> EventTime
--   User -> UserName
--   TrackingStatus -> Used to derive ActivityName ('Submitted For Approval', 'Journal Approved', 'Journal Rejected')
--   Comment -> RejectionReason

-- **Post-Processing Transformation Logic (to be implemented in an external script)**

-- 1. For each row in 'Ledger journal headers' export:
--    - Create one row with ActivityName = 'Journal Entry Created', EventTime = CREATEDDATETIME.
--    - If PostedDateTime is not null, create one row with ActivityName = 'Journal Entry Posted', EventTime = PostedDateTime.
--    - If ReverseDate is not null, create one row with ActivityName = 'Journal Entry Reversal Initiated', EventTime = ReverseDate. (A separate 'Journal Entry Reversed' event may need to be derived from the creation of the reversing journal).

-- 2. For each row in 'Ledger journal lines' export:
--    - Create one row with ActivityName = 'Journal Line Added', EventTime = CREATEDDATETIME.

-- 3. For each row in 'Workflow tracking status table' export (after joining to get JournalBatchNumber):
--    - IF TrackingStatus indicates submission, create ActivityName = 'Journal Submitted For Approval'.
--    - IF TrackingStatus indicates approval, create ActivityName = 'Journal Approved'.
--    - IF TrackingStatus indicates rejection, create ActivityName = 'Journal Rejected'.
--    - Other activities like 'Journal Review Started', 'Journal Entry Corrected', 'Journal Resubmitted For Approval', and 'Journal Posting Attempted' are often not explicitly logged. They must be inferred from status changes, user actions within a timeframe, or modification timestamps on the journal header after a rejection event.

Your Record to Report - Journal Entry Data Template

Your Record to Report - Journal Entry Data Template

Record to Report - Journal Entry Attributes

Record to Report - Journal Entry Activities

Extraction Guides

Microsoft Dynamics 365 Data Management Framework Export

Steps

Configuration

a Sample Query config

Azure Data Lake Query via Synapse for Dynamics 365

Steps

Configuration

a Sample Query sql

Python Script Extraction via Dynamics 365 OData API

Steps

Configuration

a Sample Query python

Power Platform Dataflows Extraction from Dynamics 365

Steps

Configuration

a Sample Query config

Ready to Get Started?

Boost Journal Entry in Dynamics 365, Report Faster Now

Your Record to Report - Journal Entry Data Template

Your Record to Report - Journal Entry Data Template

Record to Report - Journal Entry Attributes

Record to Report - Journal Entry Activities

Extraction Guides

Microsoft Dynamics 365 Data Management Framework Export

Steps

Configuration

a Sample Query config

Azure Data Lake Query via Synapse for Dynamics 365

Steps

Configuration

a Sample Query sql

Python Script Extraction via Dynamics 365 OData API

Steps

Configuration

a Sample Query python

Power Platform Dataflows Extraction from Dynamics 365

Steps

Configuration

a Sample Query config

We value your privacy

Ready to Get Started?

Boost Journal Entry in Dynamics 365, Report Faster Now