> Continuous Process Improvement > Record to Report Period Close & Reconciliation > Microsoft Dynamics 365 > Data Template

Your Record to Report - Period Close & Reconciliation Data Template

Microsoft Dynamics 365

← Back to Improvement Guide

Record to Report - Period Close & Reconciliation Attributes (19) Record to Report - Period Close & Reconciliation Activities (14) Extraction Guides (2)

Your Record to Report - Period Close & Reconciliation Data Template

This template provides a comprehensive guide to collecting the necessary data for optimizing your Record to Report, Period Close & Reconciliation process. It outlines essential data attributes, key activities to track, and practical guidance for extracting your data from Microsoft Dynamics 365. Use this resource to streamline your data preparation and kickstart your process analysis.

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 - Period Close & Reconciliation Attributes

These are the recommended data fields to include in your event log for comprehensive analysis of your Record to Report, Period Close & Reconciliation process.

5 Required 4 Recommended 10 Optional

	Name	Description
	Activity Name ActivityName	The name of a specific business event or task that occurred within the period close process, such as 'Trial Balance Prepared' or 'Account Reconciliation Started'.
Description The Activity Name describes a single step in the end-to-end period close workflow. Each event record in the source data corresponds to one of these activities, forming the sequence of events for each financial period. This attribute is fundamental to process mining as it allows for the visualization of the process map, showing how work is actually done. It is used to analyze process flow, identify deviations from the standard procedure, and measure the duration between different key milestones. Why it matters This attribute forms the backbone of the process map, enabling the visualization and analysis of the sequence of steps, bottlenecks, and deviations in the period close cycle. Where to get Derived from business logic based on transaction types, status changes, or specific event logs within Dynamics 365 modules like General Ledger or Financial reporting. Examples Accrual Journals PostedBank Reconciliation CompletedFinancial Statements GeneratedGL Period Closed
	Event Time EventTime	The precise date and time when a specific period close activity was executed or completed.
Description Event Time is the timestamp associated with each activity, providing the chronological order of events. This data is essential for constructing the process flow and performing any time-based analysis. In process mining, this timestamp is used to calculate cycle times, durations, and waiting times between activities. It is the basis for identifying bottlenecks, monitoring process performance against deadlines, and understanding the temporal dynamics of the period close process. Why it matters This is a critical attribute for all time-based analysis, including calculating cycle times, identifying bottlenecks, and understanding the sequence of events. Where to get Typically corresponds to system-generated timestamp fields like 'CreatedDateTime' or 'ModifiedDateTime' on transactions, journals, or status records in Dynamics 365. Examples 2023-12-28T14:20:15Z2024-01-02T09:05:00Z2024-01-05T17:30:45Z
	Financial Period FinancialPeriod	A unique identifier for the financial reporting cycle, such as '2023-12' for December 2023. This serves as the primary case identifier, grouping all related period-end activities.
Description The Financial Period represents the complete lifecycle of closing the books for a specific time frame, typically a month or a quarter. All activities, from initiating the close to approving the final financial statements for that period, are linked by this identifier. In process mining analysis, this attribute is crucial for defining the scope of each case. It allows for the measurement of the end-to-end period close cycle time, comparison of performance across different periods, and identification of systemic bottlenecks that reoccur every closing cycle. Why it matters This is the essential case identifier that frames the analysis, allowing for the evaluation of the entire period close process from start to finish for each distinct reporting cycle. Where to get This is often a composite key derived from the Fiscal Year and Period fields found in financial calendar or ledger period tables within Microsoft Dynamics 365. Examples 2023-122024-012024-Q1FY2023-P12
	Last Data Update LastDataUpdate	The timestamp indicating the last time the data was refreshed or extracted from the source system.
Description This attribute provides the 'as-of' date for the data being analyzed. It is crucial for understanding the timeliness and relevance of the process mining analysis and dashboards. Users rely on this timestamp to know if they are looking at the most current data available. It is typically displayed prominently in dashboards to provide context about the data's freshness. Why it matters Provides crucial context on data freshness, allowing users to understand how up-to-date their process analysis is. Where to get This value is generated and stored during the data extraction, transformation, and loading (ETL) process. Examples 2024-03-15T02:00:00Z2024-03-16T02:00:00Z2024-03-17T02:00:00Z
	Source System SourceSystem	Identifies the information system from which the data was extracted. For this process, it will typically be 'Microsoft Dynamics 365'.
Description This attribute specifies the origin of the process data. In environments with multiple ERPs or financial systems, it helps differentiate data sources and ensures data lineage is clear. For analysis, it is useful for traceability and for creating models that may span multiple systems. It ensures that any findings can be traced back to the correct system of record for further investigation. Why it matters Ensures data traceability and is crucial in multi-system environments to correctly attribute process events to their system of origin. Where to get This is typically a static value added during the data extraction, transformation, and loading (ETL) process to label the dataset's origin. Examples Microsoft Dynamics 365D365 F&OAX2012
	Company Code CompanyCode	The identifier for the legal entity or company for which the period close is being performed.
Description The Company Code represents a distinct legal entity within the organization. Financial data and closing activities are segregated by this code. This is a critical dimension for analysis, allowing for performance comparison across different entities. It helps to identify which companies have the most efficient closing processes and which ones struggle, supporting targeted improvement initiatives. Most dashboards, such as 'Overall Period Close Cycle Time', require segmentation by this attribute. Why it matters This is a primary dimension for filtering and comparison, allowing analysis of period close performance across different legal entities. Where to get In Dynamics 365, this corresponds to the 'DataAreaId' field, which is present on almost every transaction table. Examples USMFDEMFGBSI
	Department Department	The functional department associated with the activity or the user performing it, such as 'Finance', 'Controlling', or 'AP'.
Description The Department attribute provides organizational context, linking activities to specific business functions. This could be the department of the user who posted a journal or the department responsible for a set of GL accounts. In analysis, it is used to understand how different departments contribute to the period close process. It helps identify inter-departmental handoffs, delays, and resource bottlenecks, and supports dashboards like 'User Workload & Performance'. Why it matters Provides organizational context, enabling analysis of process performance and workload distribution by functional area. Where to get Can be derived from user profiles or from financial dimensions associated with transactions in Dynamics 365. Examples Corporate AccountingAccounts PayableFP&AInternal Audit
	GL Account GlAccount	The specific General Ledger account number involved in the activity, such as a journal posting or reconciliation.
Description The GL Account identifies the specific account in the chart of accounts that is affected by a transaction. It provides a granular level of detail for financial activities. This attribute is essential for analyzing reconciliation performance and identifying problematic accounts. Dashboards like 'Post-Close Adjustment Frequency' and 'GL Account Reconciliation Performance' rely on this field to pinpoint accounts that are frequently adjusted or take a long time to reconcile. Why it matters Allows for detailed financial analysis, helping to pinpoint problematic accounts with high adjustment rates or long reconciliation times. Where to get Found in ledger transaction tables such as 'GeneralJournalAccountEntry' or 'LedgerJournalTrans' in Dynamics 365. Examples 110110601500212100
	Responsible User ResponsibleUser	The user ID or name of the person who executed the activity.
Description This attribute identifies the individual responsible for performing a specific task in the period close process. It could be the person who posted a journal, approved a reconciliation, or generated a report. Analyzing by user helps in understanding workload distribution, identifying training needs, and assessing individual or team performance. It is a key dimension for dashboards related to user productivity and resource management, and is required to calculate the 'Avg User Activity Count Variance' KPI. Why it matters Enables analysis of workload distribution, user performance, and resource allocation, helping to identify overburdened teams or individuals. Where to get Corresponds to fields like 'CreatedBy' or 'ModifiedBy' on transaction records in Dynamics 365. User information is typically managed in the System administration module. Examples j.does.smithr.williamsFINANCE_AUTOMATION_BOT
	Actual Completion Date ActualCompletionDate	The actual date when the final step of the period close process for a financial period was completed.
Description This attribute marks the timestamp of the final, concluding activity for a given financial period case, such as 'GL Period Closed' or 'Financial Statements Approved'. It is a derived attribute that is critical for calculating the overall cycle time of the period close. It is also compared against the 'TargetCompletionDate' to calculate the 'Period Close On-Time Rate' KPI, providing a clear measure of timeliness. Why it matters Defines the end point of the process for each period, enabling the calculation of overall cycle time and on-time completion KPIs. Where to get Derived by finding the timestamp of the last recorded activity for each 'FinancialPeriod' case in the event log. Examples 2023-01-06T11:00:00Z2023-02-04T18:30:00Z2023-03-07T09:45:00Z
	Amount Amount	The monetary value associated with a financial transaction, such as the amount of a journal entry.
Description This attribute captures the financial value of an activity. It is particularly relevant for journal postings, adjustments, and reconciliations. In process mining analysis, the amount can be used to prioritize issues. For example, analyzing the value of post-close adjustments can help focus attention on high-value corrections. It also allows for filtering the process map to view only high-value or low-value transaction flows. Why it matters Provides financial context to process events, allowing for analysis based on monetary impact, such as prioritizing investigation of high-value adjustments. Where to get Found in amount fields like 'AmountCur' or 'TransactionCurrencyAmount' in tables like 'GeneralJournalAccountEntry' in Dynamics 365. Examples 15000.50-250.00125000.00
	End Time EndTime	The timestamp indicating when an activity with a measurable duration concluded.
Description For activities that are not instantaneous, such as a reconciliation task, this timestamp marks the completion of that specific task. It complements the StartTime (EventTime) to define a discrete duration. This attribute is primarily used to calculate the processing time of individual activities. It is essential for KPIs like 'Avg Adjusting Entry Processing Time' and for dashboards that analyze the breakdown of time spent on specific tasks. Why it matters Enables the calculation of precise processing times for individual activities, which is key to identifying specific task-level bottlenecks. Where to get This data can be challenging to source. It may come from a status change timestamp, a 'ModifiedDateTime' field, or a separate start/end event pair in the logs. Examples 2024-01-02T11:00:00Z2024-01-02T15:30:00Z2024-01-03T10:00:00Z
	Is Automated IsAutomated	A boolean flag indicating whether an activity was performed by a system or automation bot rather than a human user.
Description This attribute distinguishes between manual and automated activities within the period close process. This could identify journal entries posted by an automated interface or system-generated reports. Analyzing this dimension helps to understand the level of automation in the process. It can be used to measure the efficiency gains from automation, identify new automation opportunities, and ensure automated steps are performing as expected without errors or exceptions. Why it matters Helps to measure the impact of automation on the process and identify opportunities for further automation to improve efficiency. Where to get This is typically derived by checking if the 'ResponsibleUser' corresponds to a known system or service account. Examples truefalse
	Is Post-Close Adjustment IsPostCloseAdjustment	A calculated boolean flag that is true if an adjusting journal entry is posted after the trial balance has been prepared for a period.
Description This attribute specifically identifies adjustments made late in the closing cycle. It flags any 'Adjusting Journal Entries Posted' activity that occurs after the 'Trial Balance Prepared' or 'Financial Statements Approved' milestones within the same financial period. This is a critical indicator of data quality and process control. It is used to calculate the 'Post-Close Adjustment Entry Rate' KPI and populate the 'Post-Close Adjustment Frequency' dashboard, helping to identify the root causes of late changes. Why it matters Highlights issues with data quality and process control by identifying late adjustments, which can compromise the integrity of financial reports. Where to get Calculated during data transformation by checking the sequence of events within each Financial Period case. Examples truefalse
	Is Rework IsRework	A calculated boolean flag that is true if an activity or a sequence of activities is repeated for the same item within a single case.
Description The Is Rework flag identifies instances where work has to be done more than once. This could be a reconciliation that is re-opened after being approved, or a journal that is reversed and re-posted. This attribute is crucial for quantifying process inefficiency and waste. It directly feeds the 'Reconciliation Rework Rate' KPI and the 'Period Close Rework & Deviations' dashboard, highlighting areas with quality issues or unclear procedures that lead to redundant effort. Why it matters Quantifies process inefficiency by flagging repeated work, helping to identify quality issues and opportunities for procedural improvements. Where to get This flag is calculated during data transformation by applying logic that detects repeated activities for the same entity (e.g., the same GL Account) within the same Financial Period. Examples truefalse
	Journal Entry ID JournalEntryId	The unique identifier for a journal entry document.
Description The Journal Entry ID is a unique number assigned to each journal created in the general ledger. It serves as a direct link to the source transaction in Dynamics 365. While not always used for high-level process analysis, it is invaluable for drill-down and root cause analysis. When an anomaly like a post-close adjustment is found, this ID allows an analyst to quickly locate the exact transaction in the source system for detailed investigation. Why it matters Provides a direct link to the source transaction in the ERP, enabling quick drill-down for root cause analysis of specific journal entries. Where to get This corresponds to the 'JournalNumber' field in tables like 'LedgerJournalTable' and 'LedgerJournalTrans'. Examples JV-001234GENJ005678ACCR-000987
	Processing Time ProcessingTime	The calculated duration of a specific activity, representing the time from its start to its completion.
Description Processing Time, or activity duration, measures how long it takes to execute a single task. This is calculated as the difference between an activity's EndTime and StartTime. This metric is a core component of performance analysis. It is used in dashboards like 'Key Activity Cycle Time Breakdown' to highlight which steps consume the most time. It directly supports KPIs such as 'Avg Adjusting Entry Processing Time' and helps pinpoint inefficiencies at the task level. Why it matters Directly measures the time spent on individual tasks, making it essential for identifying the most time-consuming activities and process bottlenecks. Where to get Calculated by subtracting the 'StartTime' from the 'EndTime' for each activity during the data transformation process. Examples 2 hours3 days45 minutes
	Reconciliation Category ReconciliationCategory	Classifies the type of reconciliation being performed, for example 'Bank', 'Intercompany', or 'Fixed Assets'.
Description The Reconciliation Category groups individual account reconciliations into logical buckets based on their nature. This provides a higher-level view than looking at individual GL accounts. Analyzing by category helps to identify systemic issues within specific types of reconciliations. For instance, the 'GL Account Reconciliation Performance' dashboard can use this to show if Intercompany reconciliations consistently take longer than Bank reconciliations, guiding targeted process improvement efforts. Why it matters Helps categorize reconciliation activities, enabling performance comparison and identification of systemic issues within specific reconciliation types. Where to get This information might be stored in a dedicated reconciliation module or derived from the properties of the GL Account being reconciled. Consult Microsoft Dynamics 365 documentation. Examples Bank ReconciliationIntercompanyBalance Sheet - AccrualsFixed Assets Sub-Ledger
	Target Completion Date TargetCompletionDate	The planned or target date for completing the period close process for a given financial period.
Description The Target Completion Date is the deadline by which a financial period is expected to be closed. This is often defined as part of a closing calendar or schedule. This attribute is essential for evaluating performance against schedule. It is the basis for the 'Period Close On-Time Rate' KPI, which compares the actual completion date to this target. Analyzing deviations from this date helps in understanding the predictability and reliability of the close process. Why it matters Crucial for performance measurement against deadlines, this attribute enables the calculation of on-time completion rates for the period close. Where to get This may be stored in a financial period or calendar configuration table in Dynamics 365, or it may need to be sourced from an external planning tool. Consult Microsoft Dynamics 365 documentation. Examples 2023-01-052023-02-042023-03-06

Required Recommended Optional

Record to Report - Period Close & Reconciliation Activities

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

5 Recommended 9 Optional

	Activity	Description
	Account Reconciliation Approved	This activity indicates that a prepared account reconciliation has been reviewed and approved by a manager or reviewer. It is typically captured as a final status update on a task in the Financial close workspace.
Why it matters This is a major milestone that concludes the reconciliation for an account. It is essential for calculating the 'Avg Reconciliation Approval Time' KPI and identifying approval bottlenecks. Where to get From the Financial close workspace task management entities. The event is the timestamp when a reconciliation task status is changed to 'Completed' or 'Approved'. Capture Capture the timestamp of the status update on a closing task to 'Completed' or an equivalent final status. Event type explicit
	Financial Statements Approved	This is the final approval milestone, indicating that management has reviewed and signed off on the financial statements for the period. It's often captured via a task in the Financial close workspace.
Why it matters This is a critical business milestone that concludes the review phase. It is essential for measuring the Financial Statement Review Time and overall process governance. Where to get From the Financial close workspace. It is captured by the completion timestamp of the final approval task in the closing checklist for the period. Capture Use the completion timestamp of the designated 'Final Approval' task in the Financial close workspace. Event type explicit
	GL Period Closed	The final activity in the process, where the general ledger period is formally closed, preventing any further postings. This is an explicit and auditable status change within Dynamics 365.
Why it matters As the definitive end event, this activity is essential for calculating the Average Period Close Cycle Time. It represents the final, irreversible completion of the financial period. Where to get Captured from the Ledger calendar (LedgerPeriod entity). The event is the timestamp when the period status is changed to 'Closed'. Capture Identify the timestamp from the audit trail when the LedgerPeriod status field is set to 'Closed'. Event type explicit
	Period Close Process Initiated	This activity marks the beginning of the financial close process for a specific period. It is typically inferred from the creation of a closing schedule in the Financial close workspace or the first status change on the ledger period.
Why it matters As the start event, it is crucial for measuring the total period close cycle time. Analyzing the time from this event helps identify overall process duration and adherence to schedules. Where to get Inferred from the creation date of a 'Financial close' configuration or the first recorded closing task's timestamp for the financial period. This data resides within the Financial close workspace entities. Capture Use the creation timestamp of the financial close checklist or the earliest timestamp of any closing activity for the period. Event type inferred
	Trial Balance Prepared	A key milestone representing the point where all known adjustments have been posted and a preliminary trial balance is considered ready for review. This is an inferred event, marking the completion of data entry.
Why it matters This activity serves as a critical checkpoint before the generation of financial statements. It delineates the data correction phase from the reporting and review phase. Where to get This is a conceptual milestone. It can be inferred as the timestamp of the last adjusting journal entry posted before financial statement generation begins. Capture Use the timestamp of the last GeneralJournalEntry posted for the period before statement generation tasks start. Event type inferred
	Account Reconciliation Started	Represents a user beginning the reconciliation task for a specific general ledger account, such as a balance sheet account. This is captured as a status change on a task within the Financial close workspace.
Why it matters This activity is the starting point for measuring reconciliation cycle times for individual accounts. It helps identify which accounts take the longest to begin reconciling. Where to get From the Financial close workspace task management entities. The event is the timestamp when a reconciliation task status is changed to 'In progress'. Capture Capture the timestamp of the status update on a closing task to 'In progress' or a similar state. Event type explicit
	Accrual Journals Posted	This event signifies the posting of journal entries for accrued expenses and revenues. It is an explicit action captured from general ledger transaction data, identified by specific journal names or types.
Why it matters Tracking the timing and volume of accruals is important for understanding the workload during the close. It helps identify dependencies and potential bottlenecks in gathering information for these entries. Where to get Captured from the GeneralJournalEntry and GeneralJournalAccountEntry tables. Filter for transactions posted during the closing window with a journal name designated for accruals, such as 'ACCR'. Capture Filter GeneralJournalEntry records by a specific JournalName or PostingType used for accruals. Event type explicit
	Adjusting Journal Entries Posted	This event captures the posting of adjusting journal entries after initial reconciliations are done but before the final close. These are explicit transactions identified by a specific journal name or posting date.
Why it matters A high frequency of adjustments can indicate issues with data quality or upstream processes. Monitoring this helps measure the Post-Close Adjustment Entry Rate and identify problematic accounts. Where to get From the GeneralJournalEntry table. These are identified by filtering for journals with a designated 'Adjustment' journal name, or those posted after the trial balance preparation date. Capture Filter GeneralJournalEntry records using a specific JournalName or by posting date relative to other milestones. Event type explicit
	Bank Reconciliation Completed	This activity signifies that a bank reconciliation has been finished and posted. It is an explicit event captured from a status change on the bank reconciliation record.
Why it matters This is a key milestone in the closing process. Tracking its completion helps measure the throughput and duration of the bank reconciliation cycle. Where to get Inferred from the status change to 'Reconciled' on the BankStatement entity or the posting timestamp of the associated reconciliation journal. Capture Identify the timestamp when the BankStatement status field is updated to 'Reconciled'. Event type inferred
	Bank Reconciliation Started	Marks the beginning of a bank reconciliation process for a specific bank account and period. This is an explicit event captured when a user creates a new bank reconciliation worksheet.
Why it matters The timing of this activity is key to understanding the bank reconciliation sub-process. It serves as the starting point for measuring the efficiency of cash reconciliation activities. Where to get From the BankStatement and BankStatementLine tables. The creation timestamp of a new bank statement reconciliation record serves as the event time. Capture Use the 'CreatedDateTime' field on the BankStatement table for the relevant financial period. Event type explicit
	Financial Statements Generated	This activity represents the generation of formal financial reports like the income statement and balance sheet. Capturing this event is often difficult as it involves running a report.
Why it matters This marks the start of the final review cycle. The duration from this activity to final approval is a key KPI for measuring the efficiency of management review. Where to get System logs for the Financial Reporter (formerly Management Reporter) tool may contain this information. However, it is not typically stored in transactional tables and may require custom logging. Capture Requires analysis of system logs or custom event capture. May not be available in a standard implementation. Event type inferred
	GL Period Re-Opened	This exception activity occurs when a previously closed period is reopened to post a late adjustment. This is an explicit status change from 'Closed' to 'On hold' or 'Open'.
Why it matters Reopening a period is a significant deviation from the standard process and indicates a control weakness or a major error. Tracking its frequency is vital for process improvement and compliance. Where to get Captured from the Ledger calendar (LedgerPeriod entity). The event is the timestamp when the period status is changed from 'Closed' back to a non-closed status. Capture Identify the timestamp from the audit trail when the LedgerPeriod status is changed from 'Closed'. Event type explicit
	Intercompany Journals Posted	This activity represents the posting of journal entries to reconcile balances between different legal entities within the organization. This is an explicit event captured from the general journal transaction logs.
Why it matters Intercompany reconciliations are often a source of complexity and delay. Monitoring this activity helps measure the Intercompany Reconciliation Time KPI and pinpoint inefficiencies. Where to get Captured from the GeneralJournalEntry table where the 'Intercompany' flag is marked or the transaction involves intercompany accounts. Capture Filter GeneralJournalEntry records where the transaction type indicates an intercompany posting. Event type explicit
	Subledgers Placed On Hold	Represents the point where transactional subledgers like Accounts Payable and Accounts Receivable are prevented from posting new transactions for the period. This is captured by observing status changes on the ledger periods for each module.
Why it matters This is a critical control point that ensures data integrity before general ledger work begins. Delays here can have a cascading effect on the entire closing timeline. Where to get Inferred from the Ledger calendar period status for each subledger module. A status change to 'On hold' for AP, AR, etc., indicates this activity has occurred. Capture Identify status changes to 'On hold' in the audit trail for the LedgerPeriod entity, filtering by module. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Microsoft Dynamics 365

Steps

Prerequisite Confirmation: Verify that your Microsoft Dynamics 365 environment is configured with the Azure Data Lake Link. This ensures that a replica of the necessary F&O database tables is available for querying in your organization's Azure subscription.
Gain Access: Obtain the necessary security credentials and permissions to access the Azure Data Lake Storage account. You will also need access to a query engine capable of running SQL against this data, such as Azure Synapse Analytics Serverless SQL pools or Azure Databricks.
Locate Target Tables: Connect to your data lake using your chosen query tool. Identify the paths to the required Dynamics 365 tables. Key tables for this extraction include LedgerFiscalPeriod, LedgerPeriodModuleAccessControl, GeneralJournalEntry, GeneralJournalAccountEntry, BankCStmt, and ClosingSheetTask.
Configure the SQL Query: Copy the provided SQL script into your query editor. Locate the configuration section at the top of the script. Set the StartDate, EndDate, and CompanyFilter parameters to define the scope of your extraction. You must also update the placeholder journal names, for example [Your Accrual Journal Name], to match your organization's specific configuration.
Execute the Extraction Script: Run the configured SQL query. The script is designed to consolidate events from multiple source tables into a single event log format. It will gather data for all 14 specified activities and union them together.
Review Initial Results: Once the query completes, perform a quick review of the output. Check that the row count seems reasonable for the selected time period and that key columns like FinancialPeriod, ActivityName, and EventTime are populated as expected.
Export the Event Log: Export the query results to a CSV file format. Ensure the file uses UTF-8 encoding to prevent character issues.
Prepare for Upload: Before uploading to a process mining tool, confirm the column headers in the exported CSV file exactly match the required attribute names: FinancialPeriod, ActivityName, EventTime, SourceSystem, LastDataUpdate, ResponsibleUser, CompanyCode, Department, and GlAccount.

Configuration

Date Range: It is recommended to extract data for a period of at least 6 to 12 closed fiscal periods to identify trends and recurring patterns. Define this range using the StartDate and EndDate variables in the query.
Company Code Filter: Use the CompanyFilter variable, which corresponds to the DataAreaId field, to limit the extraction to specific legal entities. Running the query for all companies at once can be resource-intensive.
Journal Name Configuration: The identification of activities like 'Accrual Journals Posted', 'Intercompany Journals Posted', and 'Adjusting Journal Entries Posted' depends on specific journal names used in your Dynamics 365 setup. You must replace the placeholder values in the query, for example [Your Accrual Journal Name], with the actual journal names used by your organization for these purposes.
Financial Close Workspace: Several activities, such as 'Account Reconciliation Started' and 'Financial Statements Approved', rely on the use of the Financial Close Workspace in Dynamics 365. If this module is not used, these activities will not be extracted.
Prerequisites: This method requires that the Azure Data Lake Link for Dynamics 365 is enabled and fully synchronized. The user or service principal executing the query must have read permissions on the data lake storage.

a Sample Query sql

WITH Config AS (
    -- Configuration: Set the start date, end date, and company code filter for the extraction.
    SELECT
        CAST('2023-01-01' AS DATETIME) AS StartDate,
        CAST('2023-12-31' AS DATETIME) AS EndDate,
        'USMF' AS CompanyFilter -- Replace 'USMF' with your target company code (DataAreaId)
),
AllEvents AS (
    -- 1. Period Close Process Initiated
    -- Inferred from the first recorded action on a fiscal period module within the date range.
    SELECT
        FORMAT(lpmac.MODIFIEDDATETIME, 'yyyy-MM') AS FinancialPeriod,
        'Period Close Process Initiated' AS ActivityName,
        MIN(lpmac.MODIFIEDDATETIME) AS EventTime,
        lpmac.MODIFIEDBY AS ResponsibleUser,
        lpmac.DATAREAID AS CompanyCode,
        NULL AS Department,
        NULL AS GlAccount
    FROM LedgerPeriodModuleAccessControl lpmac
    JOIN Config ON lpmac.DATAREAID = Config.CompanyFilter AND lpmac.MODIFIEDDATETIME BETWEEN Config.StartDate AND Config.EndDate
    GROUP BY FORMAT(lpmac.MODIFIEDDATETIME, 'yyyy-MM'), lpmac.MODIFIEDBY, lpmac.DATAREAID

    UNION ALL

    -- 2. Subledgers Placed On Hold
    -- Captures when modules like Accounts Payable (Vend) and Accounts Receivable (Cust) are put on hold.
    SELECT
        FORMAT(lpmac.MODIFIEDDATETIME, 'yyyy-MM') AS FinancialPeriod,
        'Subledgers Placed On Hold' AS ActivityName,
        lpmac.MODIFIEDDATETIME AS EventTime,
        lpmac.MODIFIEDBY AS ResponsibleUser,
        lpmac.DATAREAID AS CompanyCode,
        NULL AS Department,
        NULL AS GlAccount
    FROM LedgerPeriodModuleAccessControl lpmac
    JOIN Config ON lpmac.DATAREAID = Config.CompanyFilter AND lpmac.MODIFIEDDATETIME BETWEEN Config.StartDate AND Config.EndDate
    WHERE lpmac.ACCESSLEVEL = 2 -- 2 corresponds to 'On hold'
      AND lpmac.MODULENUMBER IN (2, 3) -- 2 for 'Cust', 3 for 'Vend'. Adjust if needed.

    UNION ALL

    -- 3. Accrual Journals Posted
    -- Captured from General Journal Entries with a specific journal name for accruals.
    SELECT 
        FORMAT(gje.ACCOUNTINGDATE, 'yyyy-MM') AS FinancialPeriod,
        'Accrual Journals Posted' AS ActivityName,
        gje.CREATEDDATETIME AS EventTime,
        gje.CREATEDBY AS ResponsibleUser,
        gje.LEDGER_DATAREAID AS CompanyCode,
        NULL AS Department,
        gjae.LEDGERACCOUNT AS GlAccount
    FROM GeneralJournalEntry gje
    JOIN GeneralJournalAccountEntry gjae ON gje.RECID = gjae.GENERALJOURNALENTRY
    JOIN Config ON gje.LEDGER_DATAREAID = Config.CompanyFilter AND gje.ACCOUNTINGDATE BETWEEN Config.StartDate AND Config.EndDate
    WHERE gje.JOURNALNAME = '[Your Accrual Journal Name]' -- !! IMPORTANT: Replace with your actual journal name for accruals

    UNION ALL

    -- 4. Intercompany Journals Posted
    -- Captured from General Journal Entries with a specific journal name for intercompany transactions.
    SELECT 
        FORMAT(gje.ACCOUNTINGDATE, 'yyyy-MM') AS FinancialPeriod,
        'Intercompany Journals Posted' AS ActivityName,
        gje.CREATEDDATETIME AS EventTime,
        gje.CREATEDBY AS ResponsibleUser,
        gje.LEDGER_DATAREAID AS CompanyCode,
        NULL AS Department,
        gjae.LEDGERACCOUNT AS GlAccount
    FROM GeneralJournalEntry gje
    JOIN GeneralJournalAccountEntry gjae ON gje.RECID = gjae.GENERALJOURNALENTRY
    JOIN Config ON gje.LEDGER_DATAREAID = Config.CompanyFilter AND gje.ACCOUNTINGDATE BETWEEN Config.StartDate AND Config.EndDate
    WHERE gje.JOURNALNAME = '[Your Intercompany Journal Name]' -- !! IMPORTANT: Replace with your actual journal name for intercompany postings

    UNION ALL

    -- 5. Bank Reconciliation Started
    -- The creation of a bank reconciliation statement record.
    SELECT 
        FORMAT(bs.CREATEDDATETIME, 'yyyy-MM') AS FinancialPeriod,
        'Bank Reconciliation Started' AS ActivityName,
        bs.CREATEDDATETIME AS EventTime,
        bs.CREATEDBY AS ResponsibleUser,
        bs.DATAREAID AS CompanyCode,
        NULL AS Department,
        bs.ACCOUNTID AS GlAccount
    FROM BankCStmt bs
    JOIN Config ON bs.DATAREAID = Config.CompanyFilter AND bs.CREATEDDATETIME BETWEEN Config.StartDate AND Config.EndDate

    UNION ALL

    -- 6. Bank Reconciliation Completed
    -- When a bank reconciliation is marked as reconciled.
    SELECT 
        FORMAT(bs.RECONCILEDATE, 'yyyy-MM') AS FinancialPeriod,
        'Bank Reconciliation Completed' AS ActivityName,
        bs.MODIFIEDDATETIME AS EventTime,
        bs.MODIFIEDBY AS ResponsibleUser,
        bs.DATAREAID AS CompanyCode,
        NULL AS Department,
        bs.ACCOUNTID AS GlAccount
    FROM BankCStmt bs
    JOIN Config ON bs.DATAREAID = Config.CompanyFilter AND bs.MODIFIEDDATETIME BETWEEN Config.StartDate AND Config.EndDate
    WHERE bs.RECONCILE = 1 -- 1 typically means 'Reconciled'

    UNION ALL

    -- 7. Account Reconciliation Started & 8. Account Reconciliation Approved
    -- Captured from the Financial Close Workspace task statuses.
    SELECT 
        FORMAT(cst.DUEDATE, 'yyyy-MM') AS FinancialPeriod,
        CASE cst.STATUS
            WHEN 1 THEN 'Account Reconciliation Started'   -- 1 for 'In progress', adjust status value if needed
            WHEN 3 THEN 'Account Reconciliation Approved'  -- 3 for 'Completed' or 'Approved', adjust status value
        END AS ActivityName,
        cst.MODIFIEDDATETIME AS EventTime,
        cst.MODIFIEDBY AS ResponsibleUser,
        cst.DATAREAID AS CompanyCode,
        cst.RESPONSIBLEDEPARTMENT AS Department,
        cst.ACCOUNTDISPLAYVALUE AS GlAccount
    FROM ClosingSheetTask cst
    JOIN Config ON cst.DATAREAID = Config.CompanyFilter AND cst.MODIFIEDDATETIME BETWEEN Config.StartDate AND Config.EndDate
    WHERE cst.TASKDESCRIPTION LIKE '%Reconcile%'
      AND cst.STATUS IN (1, 3)

    UNION ALL

    -- 9. Adjusting Journal Entries Posted
    -- Captured from General Journal Entries with a specific journal name for adjustments.
    SELECT 
        FORMAT(gje.ACCOUNTINGDATE, 'yyyy-MM') AS FinancialPeriod,
        'Adjusting Journal Entries Posted' AS ActivityName,
        gje.CREATEDDATETIME AS EventTime,
        gje.CREATEDBY AS ResponsibleUser,
        gje.LEDGER_DATAREAID AS CompanyCode,
        NULL AS Department,
        gjae.LEDGERACCOUNT AS GlAccount
    FROM GeneralJournalEntry gje
    JOIN GeneralJournalAccountEntry gjae ON gje.RECID = gjae.GENERALJOURNALENTRY
    JOIN Config ON gje.LEDGER_DATAREAID = Config.CompanyFilter AND gje.ACCOUNTINGDATE BETWEEN Config.StartDate AND Config.EndDate
    WHERE gje.JOURNALNAME = '[Your Adjustment Journal Name]' -- !! IMPORTANT: Replace with your actual journal name for adjustments

    UNION ALL

    -- 10. Trial Balance Prepared
    -- Inferred as the time of the last general journal posting for the period before the close.
    SELECT 
        FORMAT(gje.ACCOUNTINGDATE, 'yyyy-MM') AS FinancialPeriod,
        'Trial Balance Prepared' AS ActivityName,
        MAX(gje.CREATEDDATETIME) AS EventTime,
        MAX(gje.CREATEDBY) AS ResponsibleUser,
        gje.LEDGER_DATAREAID AS CompanyCode,
        NULL AS Department,
        NULL AS GlAccount
    FROM GeneralJournalEntry gje
    JOIN Config ON gje.LEDGER_DATAREAID = Config.CompanyFilter AND gje.ACCOUNTINGDATE BETWEEN Config.StartDate AND Config.EndDate
    GROUP BY FORMAT(gje.ACCOUNTINGDATE, 'yyyy-MM'), gje.LEDGER_DATAREAID

    UNION ALL

    -- 11. Financial Statements Generated & 12. Financial Statements Approved
    -- Captured from tasks in the Financial Close Workspace.
    SELECT 
        FORMAT(cst.DUEDATE, 'yyyy-MM') AS FinancialPeriod,
        CASE
            WHEN cst.TASKDESCRIPTION LIKE '%Generate Financial Statement%' AND cst.STATUS = 3 THEN 'Financial Statements Generated'
            WHEN cst.TASKDESCRIPTION LIKE '%Approve Financial Statement%' AND cst.STATUS = 3 THEN 'Financial Statements Approved'
        END AS ActivityName,
        cst.MODIFIEDDATETIME AS EventTime,
        cst.MODIFIEDBY AS ResponsibleUser,
        cst.DATAREAID AS CompanyCode,
        cst.RESPONSIBLEDEPARTMENT AS Department,
        NULL AS GlAccount
    FROM ClosingSheetTask cst
    JOIN Config ON cst.DATAREAID = Config.CompanyFilter AND cst.MODIFIEDDATETIME BETWEEN Config.StartDate AND Config.EndDate
    WHERE cst.TASKDESCRIPTION LIKE '%Financial Statement%'
      AND cst.STATUS = 3 -- 3 for 'Completed', assumes completion implies generation/approval

    UNION ALL

    -- 13. GL Period Closed
    -- The final closing of the General Ledger period.
    SELECT
        FORMAT(lfp.ENDDATE, 'yyyy-MM') AS FinancialPeriod,
        'GL Period Closed' AS ActivityName,
        lfp.MODIFIEDDATETIME AS EventTime,
        lfp.MODIFIEDBY AS ResponsibleUser,
        lfp.DATAREAID AS CompanyCode,
        NULL AS Department,
        NULL AS GlAccount
    FROM LedgerFiscalPeriod lfp
    JOIN Config ON lfp.DATAREAID = Config.CompanyFilter AND lfp.MODIFIEDDATETIME BETWEEN Config.StartDate AND Config.EndDate
    WHERE lfp.STATUS = 2 -- 2 corresponds to 'Closed'
      AND lfp.TYPE = 1 -- 1 corresponds to 'Operating'

    UNION ALL

    -- 14. GL Period Re-Opened
    -- When a previously closed period is set back to 'On Hold' or 'Open'.
    SELECT
        FORMAT(lfp.ENDDATE, 'yyyy-MM') AS FinancialPeriod,
        'GL Period Re-Opened' AS ActivityName,
        lfp.MODIFIEDDATETIME AS EventTime,
        lfp.MODIFIEDBY AS ResponsibleUser,
        lfp.DATAREAID AS CompanyCode,
        NULL AS Department,
        NULL AS GlAccount
    FROM LedgerFiscalPeriod lfp
    JOIN Config ON lfp.DATAREAID = Config.CompanyFilter AND lfp.MODIFIEDDATETIME BETWEEN Config.StartDate AND Config.EndDate
    WHERE lfp.STATUS IN (1, 3) -- 1 is 'On Hold', 3 is 'Open'
      AND lfp.TYPE = 1 -- 'Operating'
      AND EXISTS (
          -- This logic is complex; a simpler check is to assume any modification to Open/On Hold after a close is a reopen.
          -- A more robust solution requires change tracking, which is outside a simple SQL query.
          SELECT 1 FROM LedgerFiscalPeriod prev_status
          WHERE prev_status.RECID = lfp.RECID AND prev_status.MODIFIEDDATETIME < lfp.MODIFIEDDATETIME
      )
)
SELECT
    FinancialPeriod,
    ActivityName,
    EventTime,
    'Microsoft Dynamics 365' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    ResponsibleUser,
    CompanyCode,
    Department,
    GlAccount
FROM AllEvents
WHERE ActivityName IS NOT NULL;

Steps

Prerequisites and Environment Setup: Ensure you have Python 3.6 or newer installed, along with the pandas and requests libraries. You can install them using pip: pip install pandas requests. Register an application in Azure Active Directory (Azure AD) for your Dynamics 365 instance and grant it appropriate API permissions to read financial data. Obtain the Tenant ID, Client ID, and Client Secret for this application.
Configure Script Parameters: Open the Python script and locate the configuration section at the top. Populate the placeholders with your D365 base URL, Azure AD Tenant ID, Client ID, and Client Secret. Define the START_DATE, END_DATE, and COMPANY_CODE (DataAreaId in D365) to scope the data extraction.
Implement Authentication: The script includes a function to obtain an OAuth 2.0 access token from Azure AD using the provided credentials. This token will be used to authenticate all subsequent API requests to the D365 OData endpoint.
Define Data Extraction Functions: The script is structured with a main data retrieval function, get_d365_data, that handles API calls, including automatic pagination by following the @odata.nextLink provided in API responses. This ensures all records for a given query are retrieved.
Execute Activity-Specific Queries: For each of the 14 required activities, the script defines a specific function (e.g., extract_gl_period_events, extract_journal_postings). Each function constructs a targeted OData query using $select to specify columns and $filter to narrow down the results to the relevant events, such as status changes or specific journal names.
Fetch and Combine Data: The main part of the script calls each extraction function sequentially. Each function returns a pandas DataFrame formatted to match the event log structure. These individual DataFrames are then collected into a list.
Standardize Data into Event Log: After all data is fetched, the script concatenates the list of DataFrames into a single, unified event log. It adds static columns like SourceSystem and LastDataUpdate (set to the script's execution time).
Handle Inferred Events: The script handles inferred activities like 'Trial Balance Prepared' by creating an event based on the timestamp of the last 'Adjusting Journal Entries Posted' for that financial period. This logic ensures the milestone is captured even without a direct system record.
Data Type Conversion and Cleaning: The script ensures the EventTime column is in the correct datetime format and other columns have appropriate data types. It also derives the FinancialPeriod from the EventTime column, formatting it as 'YYYY-MM'.
Export to CSV: Finally, the script saves the complete event log DataFrame to a CSV file named d365_period_close_event_log.csv. This file is formatted for direct upload into process mining tools.

Configuration

Authentication: An Azure AD application registration is required with a generated Client ID and Client Secret. The application needs delegated user permissions for 'Dynamics ERP' to access the OData API.
D365 Endpoint URL: The D365_BASE_URL variable in the script must be set to your organization's specific Dynamics 365 Finance and Operations URL, for example, https://your-d365-environment.operations.dynamics.com.
Date Range: Set the START_DATE and END_DATE variables to define the extraction window. It is recommended to start with a smaller range, such as 3-6 months, to manage data volume and API performance.
Company Code Filter: The COMPANY_CODE variable must be set to the legal entity identifier (known as DataAreaId in D365) for which you want to analyze the period close process. The script is designed to run for one company code at a time.
Journal Name Filters: The script uses placeholder journal names like 'ACCRUAL', 'INTERCO', and 'ADJUST' to identify specific journal posting activities. These must be updated to match the exact journal names used in your D365 configuration.
Financial Close Workspace: Extraction of reconciliation tasks ('Account Reconciliation Started', 'Account Reconciliation Approved') assumes usage of a specific entity related to the Financial Close workspace, like ClosingSheet. The entity name may need to be adjusted based on your specific implementation.

a Sample Query python

import requests
import pandas as pd
from datetime import datetime
import time

class=class="hl-string">"hl-comment"># --- CONFIGURATION --- 
D365_BASE_URL = class="hl-string">"https://[your-d365-environment].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"># --- PARAMETERS ---
START_DATE = class="hl-string">"2023-01-01"
END_DATE = class="hl-string">"2023-12-31"
COMPANY_CODE = class="hl-string">"[YourCompanyCode]" class=class="hl-string">"hl-comment"># e.g., class="hl-string">'USMF'

class=class="hl-string">"hl-comment"># --- Function to get Access Token ---
def get_access_token():
    url = fclass="hl-string">"https://login.microsoftonline.com/{TENANT_ID}/oauth2/v2.0/token"
    payload = {
        class="hl-string">'grant_type': class="hl-string">'client_credentials',
        class="hl-string">'client_id': CLIENT_ID,
        class="hl-string">'client_secret': CLIENT_SECRET,
        class="hl-string">'scope': fclass="hl-string">"{D365_BASE_URL}/.default"
    }
    response = requests.post(url, data=payload)
    response.raise_for_status()
    return response.json()[class="hl-string">'access_token']

class=class="hl-string">"hl-comment"># --- Generic function to get data from D365 OData ---
def get_d365_data(entity_name, select_fields, filter_query, access_token):
    all_records = []
    api_url = fclass="hl-string">"{D365_BASE_URL}/data/{entity_name}?$select={select_fields}&$filter={filter_query}&cross-company=true"
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'Content-Type': class="hl-string">'application/json'
    }
    
    while api_url:
        try:
            response = requests.get(api_url, headers=headers)
            response.raise_for_status()
            data = response.json()
            all_records.extend(data[class="hl-string">'value'])
            api_url = data.get(class="hl-string">'@odata.nextLink')
            time.sleep(0.1) class=class="hl-string">"hl-comment"># Small delay to be respectful to the API
        except requests.exceptions.HTTPError as err:
            print(fclass="hl-string">"HTTP Error fetching {entity_name}: {err}")
            print(fclass="hl-string">"Response content: {err.response.text}")
            break

    return pd.DataFrame(all_records)

class=class="hl-string">"hl-comment"># --- Activity Extraction Functions ---

def extract_gl_period_events(token):
    print(class="hl-string">"Extracting GL Period status changes...")
    entity = class="hl-string">"LedgerFiscalCalendarPeriods"
    fields = class="hl-string">"LedgerFiscalCalendarId,PeriodName,Status,ModifiedDateTime,ModifiedBy,LegalEntityId"
    filters = fclass="hl-string">"ModifiedDateTime ge {START_DATE}T00:00:00Z and ModifiedDateTime le {END_DATE}T23:59:59Z and LegalEntityId eq '{COMPANY_CODE}'"
    df = get_d365_data(entity, fields, filters, token)
    if df.empty:
        return pd.DataFrame()

    df.rename(columns={class="hl-string">'ModifiedDateTime': class="hl-string">'EventTime', class="hl-string">'ModifiedBy': class="hl-string">'ResponsibleUser', class="hl-string">'LegalEntityId': class="hl-string">'CompanyCode'}, inplace=True)
    df[class="hl-string">'FinancialPeriod'] = pd.to_datetime(df[class="hl-string">'EventTime']).dt.strftime(class="hl-string">'%Y-%m')
    
    class=class="hl-string">"hl-comment"># Map status to activities
    activity_map = {
        class="hl-string">'OnHold': [class="hl-string">'Period Close Process Initiated', class="hl-string">'Subledgers Placed On Hold'],
        class="hl-string">'Closed': [class="hl-string">'GL Period Closed'],
        class="hl-string">'Open': [class="hl-string">'GL Period Re-Opened'] class=class="hl-string">"hl-comment"># Assuming re-open moves from Closed to Open
    }
    
    event_dfs = []
    for status, activities in activity_map.items():
        for activity in activities:
            temp_df = df[df[class="hl-string">'Status'] == status].copy()
            temp_df[class="hl-string">'ActivityName'] = activity
            event_dfs.append(temp_df)

    result_df = pd.concat(event_dfs, ignore_index=True)
    return result_df[[class="hl-string">'FinancialPeriod', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'ResponsibleUser', class="hl-string">'CompanyCode']]

def extract_journal_postings(token):
    print(class="hl-string">"Extracting Journal Postings...")
    entity = class="hl-string">"GeneralJournalAccountEntryEntities"
    fields = class="hl-string">"JournalNumber,JournalCategory,PostingLayer,AccountingDate,CreatedBy,CompanyCode,LedgerAccount"
    filters = fclass="hl-string">"AccountingDate ge {START_DATE} and AccountingDate le {END_DATE} and CompanyCode eq '{COMPANY_CODE}'"
    df = get_d365_data(entity, fields, filters, token)
    if df.empty:
        return pd.DataFrame()
    
    df.rename(columns={class="hl-string">'AccountingDate': class="hl-string">'EventTime', class="hl-string">'CreatedBy': class="hl-string">'ResponsibleUser', class="hl-string">'LedgerAccount': class="hl-string">'GlAccount'}, inplace=True)
    df[class="hl-string">'FinancialPeriod'] = pd.to_datetime(df[class="hl-string">'EventTime']).dt.strftime(class="hl-string">'%Y-%m')

    class=class="hl-string">"hl-comment"># Placeholder journal categories, update based on your setup
    journal_map = {
        class="hl-string">'Allocation': class="hl-string">'Accrual Journals Posted', class=class="hl-string">"hl-comment"># Example mapping
        class="hl-string">'Intercompany': class="hl-string">'Intercompany Journals Posted',
        class="hl-string">'Adjustment': class="hl-string">'Adjusting Journal Entries Posted' class=class="hl-string">"hl-comment"># Example mapping
    }
    
    df[class="hl-string">'ActivityName'] = df[class="hl-string">'JournalCategory'].map(journal_map)
    result_df = df.dropna(subset=[class="hl-string">'ActivityName'])
    return result_df[[class="hl-string">'FinancialPeriod', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'ResponsibleUser', class="hl-string">'CompanyCode', class="hl-string">'GlAccount']]

def extract_bank_reconciliations(token):
    print(class="hl-string">"Extracting Bank Reconciliations...")
    entity = class="hl-string">"BankReconciliationHeaders"
    fields = class="hl-string">"ReconciliationDate,ReconciliationStatus,CreatedDateTime,CreatedBy,DataAreaId,BankAccount"
    filters = fclass="hl-string">"CreatedDateTime ge {START_DATE}T00:00:00Z and CreatedDateTime le {END_DATE}T23:59:59Z and DataAreaId eq '{COMPANY_CODE}'"
    df = get_d365_data(entity, fields, filters, token)
    if df.empty:
        return pd.DataFrame()
    
    df.rename(columns={class="hl-string">'DataAreaId': class="hl-string">'CompanyCode', class="hl-string">'BankAccount': class="hl-string">'GlAccount'}, inplace=True)

    class=class="hl-string">"hl-comment"># Started event
    started_df = df.copy()
    started_df[class="hl-string">'ActivityName'] = class="hl-string">'Bank Reconciliation Started'
    started_df[class="hl-string">'EventTime'] = started_df[class="hl-string">'CreatedDateTime']
    started_df[class="hl-string">'ResponsibleUser'] = started_df[class="hl-string">'CreatedBy']
    started_df[class="hl-string">'FinancialPeriod'] = pd.to_datetime(started_df[class="hl-string">'EventTime']).dt.strftime(class="hl-string">'%Y-%m')

    class=class="hl-string">"hl-comment"># Completed event
    completed_df = df[df[class="hl-string">'ReconciliationStatus'] == class="hl-string">'Reconciled'].copy()
    completed_df[class="hl-string">'ActivityName'] = class="hl-string">'Bank Reconciliation Completed'
    completed_df[class="hl-string">'EventTime'] = completed_df[class="hl-string">'ReconciliationDate'] class=class="hl-string">"hl-comment"># Using ReconciliationDate as completion time
    completed_df[class="hl-string">'ResponsibleUser'] = completed_df[class="hl-string">'CreatedBy']
    completed_df[class="hl-string">'FinancialPeriod'] = pd.to_datetime(completed_df[class="hl-string">'EventTime']).dt.strftime(class="hl-string">'%Y-%m')
    
    result_df = pd.concat([started_df, completed_df], ignore_index=True)
    return result_df[[class="hl-string">'FinancialPeriod', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'ResponsibleUser', class="hl-string">'CompanyCode', class="hl-string">'GlAccount']]

def extract_close_workspace_tasks(token):
    print(class="hl-string">"Extracting Close Workspace Tasks (placeholder)...")
    class=class="hl-string">"hl-comment"># This is a placeholder. The entity name class="hl-string">'ClosingSheetTasks' might be different in your system.
    class=class="hl-string">"hl-comment"># Common entities: ClosingSheet, FinancialPeriodCloseWorkspace, ClosingSheetTasks
    entity = class="hl-string">"[FinancialCloseTaskEntity]"
    fields = class="hl-string">"TaskDescription,Status,ModifiedDateTime,ModifiedBy,DataAreaId,MainAccount"
    filters = fclass="hl-string">"ModifiedDateTime ge {START_DATE}T00:00:00Z and ModifiedDateTime le {END_DATE}T23:59:59Z and DataAreaId eq '{COMPANY_CODE}'"
    class=class="hl-string">"hl-comment"># df = get_d365_data(entity, fields, filters, token)
    class=class="hl-string">"hl-comment"># if df.empty:
    class=class="hl-string">"hl-comment">#     return pd.DataFrame()
    class=class="hl-string">"hl-comment"># For demonstration, creating a dummy dataframe
    print(fclass="hl-string">"SKIPPING: Entity '{entity}' not queried. This requires system-specific entity name.")
    return pd.DataFrame()

class=class="hl-string">"hl-comment"># --- Main Execution Logic ---
if __name__ == class="hl-string">"__main__":
    print(class="hl-string">"Starting D365 Period Close data extraction...")
    access_token = get_access_token()
    all_events = []

    all_events.append(extract_gl_period_events(access_token))
    all_events.append(extract_journal_postings(access_token))
    all_events.append(extract_bank_reconciliations(access_token))
    all_events.append(extract_close_workspace_tasks(access_token)) class=class="hl-string">"hl-comment"># Placeholder call

    event_log = pd.concat([df for df in all_events if not df.empty], ignore_index=True)
    
    class=class="hl-string">"hl-comment"># --- Handle Inferred and Missing Events ---
    if not event_log.empty and class="hl-string">'Adjusting Journal Entries Posted' in event_log[class="hl-string">'ActivityName'].values:
        print(class="hl-string">"Inferring 'Trial Balance Prepared' events...")
        adj_journals = event_log[event_log[class="hl-string">'ActivityName'] == class="hl-string">'Adjusting Journal Entries Posted'].copy()
        adj_journals[class="hl-string">'EventTime'] = pd.to_datetime(adj_journals[class="hl-string">'EventTime'])
        class=class="hl-string">"hl-comment"># Infer as last adjusting entry per period
        trial_balance_events = adj_journals.loc[adj_journals.groupby(class="hl-string">'FinancialPeriod')[class="hl-string">'EventTime'].idxmax()]
        trial_balance_events[class="hl-string">'ActivityName'] = class="hl-string">'Trial Balance Prepared'
        event_log = pd.concat([event_log, trial_balance_events], ignore_index=True)

    class=class="hl-string">"hl-comment"># Placeholder for class="hl-string">'Financial Statements Generated' and class="hl-string">'Approved'
    class=class="hl-string">"hl-comment"># These are often manual or hard to track. A common approach is to infer them based on the final closing date.

    class=class="hl-string">"hl-comment"># --- Final Formatting ---
    if not event_log.empty:
        event_log[class="hl-string">'SourceSystem'] = class="hl-string">'Microsoft Dynamics 365'
        event_log[class="hl-string">'LastDataUpdate'] = datetime.utcnow().isoformat() + class="hl-string">'Z'
        
        class=class="hl-string">"hl-comment"># Standardize columns
        final_cols = [class="hl-string">'FinancialPeriod', class="hl-string">'ActivityName', class="hl-string">'EventTime', class="hl-string">'SourceSystem', class="hl-string">'LastDataUpdate', 
                        class="hl-string">'ResponsibleUser', class="hl-string">'CompanyCode', class="hl-string">'Department', class="hl-string">'GlAccount']
        for col in final_cols:
            if col not in event_log.columns:
                event_log[col] = None

        event_log = event_log[final_cols]
        event_log.sort_values(by=[class="hl-string">'FinancialPeriod', class="hl-string">'EventTime'], inplace=True)

        class=class="hl-string">"hl-comment"># Save to CSV
        output_filename = class="hl-string">'d365_period_close_event_log.csv'
        event_log.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%SZ')
        print(fclass="hl-string">"Extraction complete. Event log saved to {output_filename}")
    else:
        print(class="hl-string">"No events found for the specified criteria.")

Your Record to Report - Period Close & Reconciliation Data Template

Your Record to Report - Period Close & Reconciliation Data Template

Record to Report - Period Close & Reconciliation Attributes

Record to Report - Period Close & Reconciliation Activities

Extraction Guides

Direct SQL Query via Azure Data Lake Integration

Steps

Configuration

a Sample Query sql

Python Script Extraction via D365 OData API

Steps

Configuration

a Sample Query python

Ready to Get Started?

Accelerate Your Record to Report Period Close & Reconciliation

Your Record to Report - Period Close & Reconciliation Data Template

Your Record to Report - Period Close & Reconciliation Data Template

Record to Report - Period Close & Reconciliation Attributes

Record to Report - Period Close & Reconciliation Activities

Extraction Guides

Direct SQL Query via Azure Data Lake Integration

Steps

Configuration

a Sample Query sql

Python Script Extraction via D365 OData API

Steps

Configuration

a Sample Query python

We value your privacy

Ready to Get Started?

Accelerate Your Record to Report Period Close & Reconciliation