Your Record to Report - Period Close & Reconciliation Data Template
Your Record to Report - Period Close & Reconciliation Data Template
- Recommended attributes to collect
- Key activities to track for process analysis
- Step-by-step data extraction guidance
Record to Report - Period Close & Reconciliation Attributes
| Name | Description | ||
|---|---|---|---|
| Financial Period FinancialPeriod | A unique identifier for the financial reporting cycle, such as '2023-12' for December 2023, serving as the case for the period-end closing process. | ||
| Description The Financial Period is the core case identifier that groups all activities related to closing the books for a specific time frame, typically a calendar month or fiscal period. Each financial period represents one complete run of the period-end process. In process mining analysis, this attribute allows for tracking the entire lifecycle of a period close from initiation to final reporting. It is used to compare cycle times across different periods, identify common process paths, and analyze variations between monthly, quarterly, and annual closing procedures. By treating each period as a case, analysts can measure performance over time and pinpoint systemic bottlenecks. Why it matters This is the essential case identifier that frames the analysis, enabling comparison of period close performance over time and the identification of trends or recurring issues. Where to get Derived by combining the Fiscal Year (GJAHR) and Posting Period (MONAT) fields from financial document headers, typically from tables like BKPF. Examples 2023-122024-032024-Q1 | |||
| Activity ActivityName | The name of a specific business step or task performed within the period close process, such as 'Adjusting Journal Entry Posted' or 'Period Closed For Posting'. | ||
| Description This attribute represents a single event or task in the end-to-end period close and reconciliation process. Activities are the building blocks of the process map, showing the sequence of work performed. They are often derived from a combination of transaction codes, document types, or status changes within SAP S/4HANA. Analyzing activities is fundamental to process mining. It helps visualize the process flow, identify deviations from the standard procedure, discover bottlenecks where activities take too long, and understand the frequency and order of tasks. This is crucial for dashboards like the Period Close Variant Analysis and Current Period Close Status. Why it matters Defines the steps in the process, forming the basis of the process map and enabling analysis of process flow, deviations, and bottlenecks. Where to get Derived from various sources, such as Transaction Codes (TCODE), Document Types (BLART), or status fields in tables related to financial closing tasks (e.g., from SAP Financial Closing cockpit). Examples Period Opened For PostingAccount Reconciliation StartedFinancial Statements GeneratedIntercompany Reconciliation Started | |||
| Start Time EventTime | The timestamp indicating when a specific activity started or was recorded in the system. | ||
| Description Event Time captures the precise date and time an activity occurred. For financial postings, this is often the creation time (CPUTM) or posting date (BUDAT) of a document. For status changes or tasks, it's the timestamp when the change was logged. This attribute is critical for chronological ordering of activities and is the foundation for all time-based analysis. It is used to calculate cycle times between activities, the duration of specific tasks, and the overall end-to-end process duration. It directly supports KPIs like Average Period Close Cycle Time and Avg Reconciliation Approval Time. Why it matters This mandatory timestamp enables the chronological ordering of events and is the basis for all duration and performance calculations. Where to get Sourced from timestamp fields like Creation Date (CPUDT) and Creation Time (CPUTM) in tables like BKPF, or from change date fields in various status tables (e.g., CDHDR, CDPOS). Examples 2023-12-28T09:15:30Z2024-01-02T14:00:12Z2024-01-05T11:21:00Z | |||
| Company Code CompanyCode | A unique identifier for a legal entity or company within the SAP organization. | ||
| Description The Company Code represents an independent accounting unit within an enterprise, for which a complete, self-contained set of accounts can be created. All period-end closing activities are performed at the Company Code level. This is a fundamental organizational dimension for financial analysis. In process mining, it allows for comparing the period close process across different legal entities. The 'Period Close Variant Analysis' dashboard uses Company Code to identify inconsistencies and promote standardization of closing procedures. Why it matters Provides a critical organizational context, allowing for the comparison of processes across different legal entities to identify variations and best practices. Where to get Found in virtually all financial tables in SAP, most notably in the header table BKPF (field BUKRS). Examples 10001710US01DE01 | |||
| Document Number DocumentNumber | The unique identifier for a financial document, such as a journal entry. | ||
| Description The Document Number is a unique key assigned by SAP to every financial posting. It serves as the primary reference for tracking and auditing individual transactions that make up the activities in the close process. While not a case ID itself, it provides a crucial link back to the source system. Analysts can use the Document Number to drill down from a process mining view into the specific transaction details in SAP S/4HANA for root cause analysis. It's particularly useful when investigating specific adjusting journal entries or reconciliation postings. Why it matters Provides a direct reference to the underlying transaction in SAP, enabling easy drill-down and auditability from the process model. Where to get Located in the financial document header table BKPF (field BELNR) and line item tables like BSEG or ACDOCA. Examples 100000045190000001300000023 | |||
| End Time EndTime | The timestamp indicating when an activity was completed. | ||
| Description The End Time marks the completion of a task. In some cases, it may be the same as the Start Time for instantaneous events logged in the system. For activities with a measurable duration, it represents the finalization timestamp. Combined with Start Time, this attribute is essential for calculating the Why it matters Enables the calculation of activity durations (Processing Time), which is crucial for identifying performance bottlenecks and analyzing efficiency. Where to get Like Start Time, this can be sourced from various timestamp fields. For some activities, it might be the same as the Start Time. For others, it could be a separate 'completed on' or 'changed on' date in application tables or change logs (CDHDR, CDPOS). Examples 2023-12-28T09:16:10Z2024-01-02T17:30:00Z2024-01-05T11:21:00Z | |||
| GL Account Number GlAccountNumber | The identifier for a specific account in the General Ledger. | ||
| Description The General Ledger (GL) Account Number identifies individual accounts used to record financial transactions, such as cash, accounts payable, or revenue. Many period-end activities, like reconciliations and adjusting entries, are directly related to specific GL accounts. This attribute is essential for detailed financial process analysis. The 'Adjusting Journal Entry Analysis' dashboard uses it to show the volume of adjustments by account, helping to identify problematic areas. The 'Avg GL Account Reconciliation Time' KPI also relies on this to pinpoint delays related to specific accounts. Why it matters Links process activities to specific financial accounts, enabling detailed analysis of reconciliations and journal entries to find root causes of issues. Where to get Found in financial document line item tables, primarily BSEG (field HKONT) or the S/4HANA universal journal table ACDOCA. Examples 113100004000000073000000 | |||
| Processing Time ProcessingTime | The duration of an individual activity, calculated as the difference between its End Time and Start Time. | ||
| Description Processing Time, also known as activity duration, measures the time spent on a single task. It is calculated by subtracting the This calculated metric is fundamental for performance analysis. It helps pinpoint bottlenecks at the activity level and is the primary measure used in the 'Resource Allocation Efficiency' and 'Reconciliation Approval Throughput' dashboards. Summing the processing times can also provide insights into the total touch time within the process. Why it matters Measures the duration of individual tasks, directly highlighting bottlenecks and forming the basis for efficiency analysis. Where to get Calculated field: Examples PT1M30SPT8HP2D | |||
| Reconciliation Status ReconciliationStatus | The status of an account reconciliation activity, such as 'Open', 'In Progress', or 'Approved'. | ||
| Description This attribute tracks the state of a reconciliation process for a given account or task. It indicates whether a reconciliation has been started, submitted for review, approved, or rejected. The status changes are key events in the process. This is fundamental for the 'Reconciliation Rework Analysis' dashboard. By analyzing the sequence of statuses, it's possible to identify when an 'Approved' status is followed by further adjustments, indicating rework. It also supports the 'First-Pass Reconciliation Rate' KPI by showing which reconciliations are approved without any preceding rejection or re-opening. Why it matters Tracks the progress and outcome of reconciliation tasks, making it possible to identify rework, measure approval rates, and analyze bottlenecks. Where to get This information is typically stored in tables related to the specific reconciliation tool being used, such as the SAP Financial Closing cockpit or SAP Account Substantiation and Automation by BlackLine. Examples Not StartedIn PreparationApprovedRejected | |||
| Responsible User ResponsibleUser | The user ID of the person who executed the activity or is responsible for the task. | ||
| Description This attribute identifies the employee who performed a given task, such as posting a journal entry or approving a reconciliation. It is typically captured as a user ID in system logs and document headers. Analyzing the process by user is critical for understanding workload distribution, individual performance, and training needs. The Resource Allocation Efficiency dashboard directly uses this attribute to visualize activity processing times by user. It can also help identify automation candidates by highlighting highly repetitive tasks performed by many users. Why it matters Connects process activities to the individuals who performed them, enabling workload analysis, resource optimization, and performance management. Where to get Commonly found in document header tables like BKPF (field USNAM - User Name) or in change document tables like CDHDR (field USERNAME). Examples ABROWNCSMITHDJOHNSON | |||
| Target Completion Date TargetCompletionDate | The planned or deadline date for completing a key activity, such as generating financial statements. | ||
| Description The Target Completion Date represents the internal or external deadline for a specific milestone in the period close process. It is the date by which a task is expected to be finished. This attribute is essential for measuring schedule adherence and performance against deadlines. It is the basis for the 'Reporting Deadline Adherence' dashboard and the 'Financial Statement On-Time Rate' KPI, which compare this target date with the Why it matters Enables analysis of performance against deadlines, helping to measure and improve on-time completion rates for critical closing activities. Where to get This data often resides in a task management or scheduling system, like the SAP Financial Closing cockpit, where closing task lists are defined with planned end dates. Examples 2024-01-05T23:59:59Z2024-04-04T23:59:59Z2024-07-05T23:59:59Z | |||
| Actual Completion Date ActualCompletionDate | The actual date when a key activity or the entire period close was completed. | ||
| Description This attribute captures the real completion date of a significant milestone. It is the timestamp of the final approval for financial statements or the closing of the posting period. It is the counterpart to Why it matters Provides the actual timing of milestones, which is necessary to measure performance against planned deadlines and analyze delays. Where to get Sourced from the EventTime of the final activity in a sequence, such as 'Financial Statements Approved' or 'Period Closed For Posting'. Examples 2024-01-06T10:00:00Z2024-04-05T17:00:00Z2024-07-04T11:00:00Z | |||
| Document Amount DocumentAmount | The total value of the financial posting in the document currency. | ||
| Description This attribute represents the monetary value associated with a financial document. For period-end adjustments, this could be the amount of the accrual or correction being posted. Analyzing the amount is useful for materiality assessments. It allows analysts to prioritize investigation of high-value adjusting entries, which may pose a greater financial risk. It can be used to filter the process map to focus only on transactions above a certain threshold, simplifying the view to the most significant activities. Why it matters Adds a financial dimension to the analysis, allowing for the prioritization of issues based on monetary impact and materiality. Where to get This value typically needs to be aggregated from line item tables like BSEG or ACDOCA (field WRBTR - Amount in document currency). Examples 15000.00250.75-500.00 | |||
| Document Currency DocumentCurrency | The currency code for the amounts in the financial document. | ||
| Description This attribute specifies the currency in which the transaction was recorded, such as USD, EUR, or JPY. It provides essential context for the In multi-national organizations, analyzing by currency can be important. It ensures that monetary values are interpreted correctly and is necessary for any conversion to a single reporting currency for aggregate analysis. Without this context, Why it matters Provides necessary context for any monetary values, ensuring amounts are interpreted correctly, especially in a multi-currency environment. Where to get Located in the financial document header table BKPF (field WAERS). Examples USDEURJPY | |||
| Is Automated IsAutomated | A boolean flag indicating if the activity was performed by a system user or an automated process. | ||
| Description This flag differentiates between tasks performed manually by a human user and those executed automatically by the system, such as a scheduled batch job for currency valuation or automated clearing runs. It is typically derived based on the This attribute is key to understanding the level of automation in the close process. It helps identify opportunities for further automation by highlighting high-volume manual tasks. It is also used in root cause analysis to see if bottlenecks are occurring in manual or automated steps. Why it matters Distinguishes between human and system activities, which is critical for identifying automation opportunities and analyzing the efficiency of both. Where to get This is a derived attribute, often based on a rule applied to the 'ResponsibleUser' field (e.g., checking for system or batch user IDs like 'BATCHUSER') or the Transaction Code. Examples truefalse | |||
| Is On Time IsOnTime | A boolean flag that is true if a milestone was completed on or before its target date. | ||
| Description This attribute provides a simple binary outcome for schedule adherence. It is calculated by comparing the This is the core calculation behind the 'Financial Statement On-Time Rate' KPI. It simplifies performance tracking by converting date comparisons into a clear 'on time' or 'late' status, which is easily visualized in dashboards and reports. It helps measure the effectiveness of initiatives aimed at accelerating the close. Why it matters Simplifies deadline adherence analysis into a clear success or failure outcome, making it easy to track and report on-time performance. Where to get Calculated field: Examples truefalse | |||
| Is Reconciliation Rework IsReconciliationRework | A boolean flag that is true if a reconciliation activity is followed by a rejection or further adjustments. | ||
| Description This calculated attribute identifies instances of rework within the reconciliation sub-process. The logic flags activities or cases where a reconciliation is rejected, re-opened after being submitted, or where adjusting entries are posted after a reconciliation was supposedly completed for a GL account. This flag is specifically designed to power the 'Reconciliation Rework Analysis' dashboard and the 'Reconciliation Rework Rate' KPI. It transforms complex process sequences into a simple, measurable attribute that quantifies the extent of rework, helping teams focus on improving first-time quality. Why it matters Directly quantifies rework, a key form of process inefficiency, enabling targeted analysis and improvement efforts for reconciliation quality. Where to get Calculated based on the sequence of activities. For example, a flag is set to true if the sequence 'Reconciliation Approved' is followed by 'Adjusting Journal Entry Posted' for the same GL Account within the same period. Examples truefalse | |||
| Journal Entry Type JournalEntryType | The type of financial document being posted, differentiating standard entries, accruals, and adjustments. | ||
| Description The Journal Entry Type, or Document Type in SAP terminology, is a key that classifies accounting documents. It controls which account types a document can be posted to and is used to distinguish different business transactions, such as a vendor invoice (KR), a general ledger posting (SA), or a customer payment (DZ). In the context of period close, this attribute is crucial for isolating specific types of activities. For example, it allows analysts to focus on adjusting journal entries to support the 'Adjusting Journal Entry Analysis' dashboard and the 'Adjusting Journal Entries Count' KPI. It also helps in calculating the 'Manual Postings Ratio'. Why it matters Categorizes financial transactions, allowing analysis to focus on specific types of postings, such as manual adjustments, which are critical to a smooth period close. Where to get This is a standard field in the financial document header table BKPF (field BLART). Examples SAKRAB | |||
| Last Data Update LastDataUpdate | The timestamp indicating when the data was last refreshed or extracted from the source system. | ||
| Description This attribute records the date and time of the most recent data extraction. It provides context on the freshness of the data being analyzed in the process mining tool. Its primary purpose is for data governance and user awareness. Dashboard viewers can see how current the analysis is, which is especially important for near-real-time monitoring dashboards like the 'Current Period Close Status'. It helps users understand if they are looking at data from today, yesterday, or last week. Why it matters Informs users about the timeliness of the data, ensuring they understand how current the process insights are. Where to get This is metadata generated and stamped on the dataset by the data extraction or ETL tool at the time of execution. Examples 2024-06-10T08:00:00Z2024-06-09T08:00:00Z | |||
| Period Close Cycle Time PeriodCloseCycleTime | The total time taken for a financial period, from the first activity to the last. | ||
| Description This metric represents the end-to-end duration of the period close process for a single case (Financial Period). It is calculated as the difference between the timestamp of the very last activity and the very first activity within that period. This is a primary key performance indicator, directly supporting the 'Period Close Cycle Time Overview' dashboard and the 'Average Period Close Cycle Time' KPI. It provides a high-level measure of the overall efficiency and speed of the closing process, allowing for comparisons over time and benchmarking between different company codes. Why it matters Represents the primary KPI for the overall process duration, allowing for high-level tracking of efficiency improvements over time. Where to get Calculated at the case level: Examples P5D10H30MP7D2HP6D | |||
| Source System SourceSystemId | Identifier for the specific SAP S/4HANA instance or client from which the data was extracted. | ||
| Description This attribute specifies the origin system of the data. In environments with multiple SAP instances (e.g., for different regions or business units), this field is crucial for distinguishing data sources. It is primarily used for data governance, auditing, and ensuring data integrity. In analysis, it can be used as a filter to compare processes across different source systems if they are combined into a single process mining model. Why it matters Provides essential data lineage and context, ensuring clarity on the origin of the data, especially in multi-system landscapes. Where to get This is typically metadata added during the data extraction process, often sourced from the system ID (SY-SYSID) or client (SY-MANDT) in SAP. Examples S4H_PROD_100S4H_QAS_200ECC_LEGACY_001 | |||
| Transaction Code TransactionCode | The SAP transaction code (T-code) used to execute the activity. | ||
| Description A transaction code is a shortcut for initiating a specific function or program in SAP. For example, FB01 is used for posting a document, and FAGLB03 is used for displaying GL account balances. The T-code often provides a clear technical indicator of the activity that was performed. This attribute helps in creating the activity definitions for the process model. It's a reliable way to differentiate between automated and manual activities and to understand exactly how a task was performed in the system. Analyzing T-codes can reveal non-standard or inefficient methods being used by employees. Why it matters Provides a technical, system-level view of how an activity was performed, which is useful for identifying manual vs. automated steps and non-standard procedures. Where to get Found in the financial document header table BKPF (field TCODE) or in change document logs. Examples FB01FAGL_FCVF.13 | |||
Record to Report - Period Close & Reconciliation Activities
| Activity | Description | ||
|---|---|---|---|
| Account Reconciliation Approved | This activity signifies the final approval of a GL account reconciliation, confirming that the balance is substantiated and any issues have been resolved. This is inferred from a status change to 'Approved' in a reconciliation tool. | ||
| Why it matters This milestone marks the completion of a critical control activity. Tracking approval times helps identify bottlenecks in the review process and is essential for measuring rework and first-pass yield. Where to get Captured from a reconciliation management system, such as SAP Account Substantiation and Automation, by tracking the timestamp of the final approval status. Capture Identify the timestamp when a reconciliation's status field is updated to its terminal 'Approved' state. Event type inferred | |||
| Account Reconciliation Started | Marks the beginning of the manual or semi-automated process of substantiating GL account balances. This is inferred when a reconciliation's status is changed from 'New' to 'In Progress' or when the first preparer action is logged. | ||
| Why it matters This is a key milestone for understanding the duration and effort involved in balance sheet substantiation. It helps identify which accounts or departments start their reconciliations late. Where to get This event is typically captured from a dedicated reconciliation tool like SAP Account Substantiation and Automation by BlackLine or other third-party systems. It is inferred from status change timestamps. Capture Track the timestamp of the first status change from a 'Not Started' state for a given GL account reconciliation. Event type inferred | |||
| Adjusting Journal Entry Posted | Represents a corrective journal entry made after initial reconciliations have been performed, typically to fix discrepancies found during the review process. This is an explicit transaction posted in the General Ledger. | ||
| Why it matters A high frequency of adjusting entries indicates poor first-time quality in upstream processes or reconciliations. Analyzing these entries is key to identifying root causes of errors and reducing rework. Where to get Recorded as explicit documents in tables ACDOCA and BKPF. They can be identified by specific document types, posting dates late in the close cycle, or specific text descriptions. Capture Filter journal entries in BKPF based on a combination of document type and posting date relative to reconciliation activities. Event type explicit | |||
| Financial Statements Generated | This activity marks the creation of the official financial statements, such as the Balance Sheet and Income Statement. It represents the culmination of the period-end accounting and consolidation processes. | ||
| Why it matters This is a major milestone in the Record to Report cycle. Its timing is critical for meeting reporting deadlines and providing timely information to stakeholders. Where to get Best captured through a financial close management tool like SAP Financial Closing Cockpit, where it is an explicit task. Otherwise, it must be inferred from report generation logs or file creation timestamps. Capture Track the completion status of the 'Generate Financial Statements' task in a closing tool or analyze report execution logs. Event type inferred | |||
| Intercompany Reconciliation Started | This activity signifies the beginning of the process to match and reconcile transactions between different company codes within the same corporate group. This is often initiated by running a reconciliation job or creating a new reconciliation case in SAP's ICMR tool. | ||
| Why it matters Intercompany reconciliation is often complex and time-consuming. Analyzing its duration and flow is crucial for streamlining the group close and ensuring consolidated financials are accurate. Where to get Inferred from status changes within the SAP Intercompany Matching and Reconciliation (ICMR) module or from the creation of the first reconciliation documents for the period. Capture Track the creation date or status change timestamp for reconciliation units within the ICMR tool for the given period. Event type inferred | |||
| Period Closed For Posting | This is the final activity in the cycle, where the posting period is formally closed to prevent any further transactions. This action ensures the integrity of the data for the finalized financial reports. | ||
| Why it matters This activity serves as the definitive end event for the process. It is essential for calculating the total period close cycle time and analyzing process completion trends. Where to get Similar to opening a period, this event is captured from change documents (tables CDHDR and CDPOS) that log updates to the posting period control table T001B via transaction OB52. Capture Filter change logs for table T001B to identify the closing of the relevant fiscal year and period. Event type explicit | |||
| Period Opened For Posting | This marks the start of the financial period, where posting periods are opened for transaction recording. This is an explicit, logged configuration change performed by a finance administrator in SAP, allowing journal entries for the new period. | ||
| Why it matters This activity serves as the primary start event for the period-end close process. It enables the accurate measurement of the total cycle time and provides a clear beginning for process analysis. Where to get This event is captured from change documents (tables CDHDR and CDPOS) that log modifications to the posting period control table T001B, typically executed via transaction OB52. Capture Filter change logs for table T001B related to the specific fiscal year and period. Event type explicit | |||
| Accruals And Provisions Posted | This event captures the creation of manual journal entries for accruals, deferrals, and other provisions that are necessary for accurate financial reporting under the accrual basis of accounting. These are explicit transactions posted to the General Ledger. | ||
| Why it matters Tracking these postings helps analyze the volume and timing of manual adjustments. A high volume may indicate opportunities for automation or improvement in upstream processes. Where to get Recorded as explicit journal entries in the universal journal table ACDOCA and header table BKPF. These entries can be identified by specific document types configured for accruals. Capture Filter documents in table BKPF by document type (e.g., 'SA' or custom types) and posting date within the period. Event type explicit | |||
| Automated Clearing Run Executed | Represents the execution of automated clearing programs in SAP, such as the GR/IR (Goods Receipt/Invoice Receipt) clearing run. These jobs match and clear open items on specific GL accounts, reducing manual reconciliation effort. | ||
| Why it matters This activity highlights the level of automation in the reconciliation process. Failures or exceptions in these runs can lead to significant manual work and delays. Where to get Captured from the execution logs of specific batch jobs, such as F.13 or F.13E for automatic clearing. Job log tables TBTCO and TBTCP contain the execution details. Capture Monitor the start and end timestamps of jobs associated with transactions like F.13 in the SAP job logs. Event type explicit | |||
| Consolidation Tasks Executed | Represents the execution of consolidation activities within SAP Group Reporting, such as currency translation, reclassification, and elimination of intercompany profits. These tasks are typically managed and logged in the consolidation monitor. | ||
| Why it matters For organizations with multiple legal entities, the consolidation process is a critical path item. Monitoring these tasks helps to streamline the group close and ensure timely consolidated reporting. Where to get Captured from the status logs within the Data Monitor or Consolidation Monitor in SAP S/4HANA for Group Reporting. Each task typically has a user, timestamp, and status. Capture Extract task execution logs from the underlying tables of the Group Reporting consolidation monitor. Event type explicit | |||
| Financial Statements Approved | Represents the final sign-off on the financial statements by authorized management before they are published. This is often an offline process, but can be captured if a formal digital approval step exists. | ||
| Why it matters This is the final quality and control gate. Tracking its timing is essential for understanding adherence to reporting deadlines and the overall efficiency of the management review cycle. Where to get This event is captured if a workflow or status field exists for statement approval, for example, within SAP Financial Closing Cockpit or a document management system. Capture Identify a data field and timestamp that indicates the final approval of the financial statement package. Event type inferred | |||
| Foreign Currency Valuation Run | This is a standard month-end procedure to revalue open items and balances in foreign currencies based on the period-end exchange rates. The execution of this program posts the resulting valuation differences. | ||
| Why it matters Ensures compliance with accounting standards and provides an accurate financial picture. Tracking this activity helps ensure all currency valuations are performed in a timely manner. Where to get Captured from the execution logs of the foreign currency valuation program (e.g., FAGL_FCV). Job details are stored in tables TBTCO and TBTCP. Capture Monitor the execution logs for jobs related to transaction FAGL_FCV or its underlying program. Event type explicit | |||
| Subledger Data Transfer Completed | Represents the completion of batch jobs that transfer summarized data from subsidiary ledgers like Accounts Receivable, Accounts Payable, and Asset Accounting to the General Ledger. This ensures all underlying operational transactions are reflected in the GL before closing procedures begin. | ||
| Why it matters Delays in subledger closing can be a major bottleneck for the overall process. Tracking this activity helps identify inefficiencies in data collection and integration from source systems. Where to get Inferred from the successful completion status of specific batch jobs responsible for ledger settlement and data transfer. Job logs can be found in tables TBTCO and TBTCP. Capture Identify and monitor the completion timestamps of relevant batch jobs in SAP's job overview (transaction SM37). Event type explicit | |||
| Trial Balance Prepared | Indicates that a trial balance has been generated and saved for review, serving as a key checkpoint before the final financial statements are created. The capture of this event often relies on procedures where the report output is saved. | ||
| Why it matters The preparation of the trial balance is a crucial quality gate. Analyzing its timing helps understand the readiness for final reporting and can highlight delays in preceding activities. Where to get This is often not an explicit event. It may be inferred from the execution of a specific reporting transaction if variants are saved with user and date stamps, or if the process is managed in a tool like SAP Financial Closing Cockpit. Capture Requires system analysis to determine if a reliable logging mechanism exists, such as report execution logs or closing cockpit task lists. Event type inferred | |||
Extraction Guides
Steps
- Identify and Verify CDS Views: Log in to your SAP S/4HANA system using SAP HANA Studio or Eclipse with ADT. Use the ABAP Development Tools to search for and verify the existence of the standard CDS views mentioned in the query, such as I_JournalEntryItem, I_BatchJob, and I_ConsolidationTaskLog. For processes like Account Reconciliation, you may need to create custom CDS views, for example Z_I_ACCOUNTRECONCILIATION, to capture status changes.
- Expose CDS Views via OData: In the SAP backend, use the transaction
SEGW(Gateway Service Builder) to create a new project. Right-click the Data Model folder and select 'Reference -> Data Source'. Add the required CDS views. Generate the runtime objects to create an OData service that exposes these views for external consumption. - Activate OData Service: Go to transaction
/IWFND/MAINT_SERVICEon the SAP Gateway hub. Click 'Add Service', find the service you generated in the previous step, and add it. This makes the service active and available for querying. - Establish Data Connection: From your chosen data extraction tool (such as Python, Power BI, or a dedicated ETL platform), establish a connection to the newly created OData service. You will need the service URL and appropriate credentials with authorization to access the CDS views.
- Adapt and Execute the Query: Copy the provided SQL query. Modify the placeholder values for date ranges, company codes, and any custom CDS view or batch job names to match your specific SAP environment. Execute the query against the OData service endpoint. Note that OData uses a different syntax ($filter, $select), so you may need to translate the logic of the UNION ALL query into a series of separate API calls, one for each activity.
- Consolidate Activity Data: Since OData does not typically support complex UNION ALL queries directly in a single call, you will execute a separate request for each activity block in the query. Consolidate the results from all requests into a single dataset or table within your client tool.
- Transform and Clean Data: Review the consolidated data. Ensure the
EventTimeandEndTimecolumns are in a consistent timestamp format. Verify thatFinancialPeriodis correctly formatted as 'YYYY-MM'. Handle any NULL values in columns likeResponsibleUserorDocumentNumberas needed. - Export as Event Log: Export the final, consolidated dataset to a CSV file. Ensure the column headers match the required attributes:
FinancialPeriod,ActivityName,EventTime,EndTime,ResponsibleUser, etc. This file is now ready to be uploaded to ProcessMind.
Configuration
- CDS Views: The query relies on a combination of standard and custom CDS views. Standard views like
I_JournalEntryItem(for journal entries) andI_BatchJob(for automated runs) are essential. Custom views, likeZ_I_ACCOUNTRECONCILIATION, are often necessary to capture the status of internal processes not covered by standard views. - Date Range: Define a specific date range for the extraction to manage data volume. The query uses placeholders
[Start Date]and[End Date]which should typically cover a period of 3 to 6 full fiscal periods for meaningful analysis. - Key Filters: Filtering is crucial for performance and relevance. Always filter by
CompanyCode. Other important filters includeLedger(e.g., '0L' for the leading ledger),FiscalYear, and specificDocumentTypevalues to differentiate between regular postings, accruals, and adjustments. - Prerequisites: The user running the extraction requires authorizations in SAP to access the specified CDS views and execute OData services (e.g., authorization objects
S_SERVICE,S_RFC). SAP Gateway services must be configured and activated. - Performance: Querying
I_JournalEntryItemcan be performance-intensive. Always apply selective filters, especially on dates, company codes, and ledgers. Consider running the extraction during off-peak hours or using an incremental loading strategy for continuous monitoring.
a Sample Query sql
SELECT
CONCAT(CAST(PostingDate AS DATE FORMAT 'YYYY'), '-', CAST(PostingDate AS DATE FORMAT 'MM')) AS FinancialPeriod,
'Period Opened For Posting' AS ActivityName,
CAST(CreationDateTime AS TIMESTAMP) AS EventTime,
CAST(CreationDateTime AS TIMESTAMP) AS EndTime,
CreatedByUser AS ResponsibleUser,
CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
0 AS ProcessingTime
FROM I_FinPostingPeriodControl -- This is a proxy; this activity is a configuration change often found in change logs (CDHDR/CDPOS for table T001B)
WHERE PostingPeriodVariant = '[Your Variant]' AND FiscalYear = '[Your Fiscal Year]' AND FromPostingPeriod = 1 AND AuthorizationGroup IS NOT NULL -- Logic to detect the opening event
UNION ALL
SELECT
CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod, -- Infer period from Job Name if patterned, otherwise use job start date
'Subledger Data Transfer Completed' AS ActivityName,
CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
JobCreatedByUser AS ResponsibleUser,
'[Configure based on your system]' AS CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE '[Subledger Job Prefix]%' AND JobStatus = 'F' -- 'F' for Finished
AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(CAST(CreationDate AS DATE FORMAT 'YYYY'), '-', CAST(CreationDate AS DATE FORMAT 'MM')) AS FinancialPeriod,
'Intercompany Reconciliation Started' AS ActivityName,
CAST(CreationDateTime AS TIMESTAMP) AS EventTime,
NULL AS EndTime,
CreatedByUser AS ResponsibleUser,
LeadingCompanyCode AS CompanyCode,
NULL AS GlAccountNumber,
ReconciliationCase AS DocumentNumber,
ReconciliationCaseStatus AS ReconciliationStatus,
NULL AS TargetCompletionDate,
NULL AS ProcessingTime
FROM I_ICRcnCase -- Part of SAP S/4HANA for group reporting
WHERE CreationDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
'Accruals And Provisions Posted' AS ActivityName,
CAST(CreationDateTime AS TIMESTAMP) AS EventTime,
CAST(CreationDateTime AS TIMESTAMP) AS EndTime,
CreatedByUser AS ResponsibleUser,
CompanyCode,
GLAccountNumber,
AccountingDocument AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
0 AS ProcessingTime
FROM I_JournalEntryItem
WHERE DocumentType IN ('[Accrual Doc Type 1]', '[Accrual Doc Type 2]') -- e.g., 'SA', 'AC'
AND PostingDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod,
'Automated Clearing Run Executed' AS ActivityName,
CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
JobCreatedByUser AS ResponsibleUser,
'[Configure based on your system]' AS CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE 'SAPF124%' AND JobStatus = 'F'
AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod,
'Foreign Currency Valuation Run' AS ActivityName,
CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
JobCreatedByUser AS ResponsibleUser,
'[Configure based on your system]' AS CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE 'FAGL_FCV%' AND JobStatus = 'F'
AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
-- This activity often requires a custom view on a custom table or an add-on like SAP Account Substantiation and Automation.
-- The following is a placeholder for such a custom view.
SELECT
CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
'Account Reconciliation Started' AS ActivityName,
CAST(ChangedAt AS TIMESTAMP) AS EventTime,
NULL AS EndTime,
ChangedBy AS ResponsibleUser,
CompanyCode,
GLAccountNumber,
ReconciliationID AS DocumentNumber,
'In Progress' AS ReconciliationStatus,
PlannedCompletionDate AS TargetCompletionDate,
NULL AS ProcessingTime
FROM Z_I_ACCOUNTRECONCILIATION -- Placeholder for your custom reconciliation status view
WHERE Status = 'IN_PROGRESS' AND OldStatus = 'NEW'
AND ChangeDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
'Adjusting Journal Entry Posted' AS ActivityName,
CAST(CreationDateTime AS TIMESTAMP) AS EventTime,
CAST(CreationDateTime AS TIMESTAMP) AS EndTime,
CreatedByUser AS ResponsibleUser,
CompanyCode,
GLAccountNumber,
AccountingDocument AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
0 AS ProcessingTime
FROM I_JournalEntryItem
WHERE DocumentType IN ('[Adjustment Doc Type 1]', '[Adjustment Doc Type 2]') -- e.g., 'AJ'
AND PostingDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
-- This activity often requires a custom view on a custom table or an add-on product.
-- The following is a placeholder for such a custom view.
SELECT
CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
'Account Reconciliation Approved' AS ActivityName,
CAST(ChangedAt AS TIMESTAMP) AS EventTime,
CAST(ChangedAt AS TIMESTAMP) AS EndTime,
ApprovedBy AS ResponsibleUser,
CompanyCode,
GLAccountNumber,
ReconciliationID AS DocumentNumber,
'Approved' AS ReconciliationStatus,
PlannedCompletionDate AS TargetCompletionDate,
NULL AS ProcessingTime
FROM Z_I_ACCOUNTRECONCILIATION -- Placeholder for your custom reconciliation status view
WHERE Status = 'APPROVED'
AND ChangeDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
'Consolidation Tasks Executed' AS ActivityName,
CAST(TaskExecutionUTCTimestamp AS TIMESTAMP) AS EventTime,
CAST(TaskCompletionUTCTimestamp AS TIMESTAMP) AS EndTime,
ExecutedByUser AS ResponsibleUser,
ConsolidationUnitCompanyCode AS CompanyCode, -- Assuming mapping exists
NULL AS GlAccountNumber,
DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
SECONDS_BETWEEN(CAST(TaskExecutionUTCTimestamp AS TIMESTAMP), CAST(TaskCompletionUTCTimestamp AS TIMESTAMP)) AS ProcessingTime
FROM I_ConsolidationTaskLog
WHERE ConsolidationTaskStatus = 'S' -- Successful
AND TaskExecutionUTCDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod,
'Trial Balance Prepared' AS ActivityName,
CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
JobCreatedByUser AS ResponsibleUser,
'[Configure based on your system]' AS CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE '[Trial Balance Job Prefix]%' AND JobStatus = 'F' -- Example: FAGL_TRIAL_BALANCE_RUN
AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod,
'Financial Statements Generated' AS ActivityName,
CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
JobCreatedByUser AS ResponsibleUser,
'[Configure based on your system]' AS CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE '[Fin Statement Job Prefix]%' AND JobStatus = 'F'
AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
-- Approval is often a manual step. This requires a custom solution to capture.
-- The following is a placeholder for such a custom view.
SELECT
CONCAT(ApprovalYear, '-', LPAD(ApprovalPeriod, 2, '0')) AS FinancialPeriod,
'Financial Statements Approved' AS ActivityName,
CAST(ApprovalTimestamp AS TIMESTAMP) AS EventTime,
CAST(ApprovalTimestamp AS TIMESTAMP) AS EndTime,
ApproverUser AS ResponsibleUser,
CompanyCode,
NULL AS GlAccountNumber,
FinancialStatementVersion AS DocumentNumber,
'Approved' AS ReconciliationStatus,
NULL AS TargetCompletionDate,
0 AS ProcessingTime
FROM Z_I_FINSTATEMENTAPPROVAL -- Placeholder for custom approval tracking view
WHERE ApprovalDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(CAST(LastChangeDate AS DATE FORMAT 'YYYY'), '-', CAST(LastChangeDate AS DATE FORMAT 'MM')) AS FinancialPeriod,
'Period Closed For Posting' AS ActivityName,
CAST(LastChangeDateTime AS TIMESTAMP) AS EventTime,
CAST(LastChangeDateTime AS TIMESTAMP) AS EndTime,
LastChangedByUser AS ResponsibleUser,
CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
0 AS ProcessingTime
FROM I_FinPostingPeriodControl -- Proxy, see note on 'Period Opened'
WHERE PostingPeriodStatus = 'C' -- 'C' for Closed
AND LastChangeDate BETWEEN '[Start Date]' AND '[End Date]';Steps
- Identify and Verify CDS Views: Log in to your SAP S/4HANA system using SAP HANA Studio or Eclipse with ADT. Use the ABAP Development Tools to search for and verify the existence of the standard CDS views mentioned in the query, such as I_JournalEntryItem, I_BatchJob, and I_ConsolidationTaskLog. For processes like Account Reconciliation, you may need to create custom CDS views, for example Z_I_ACCOUNTRECONCILIATION, to capture status changes.
- Expose CDS Views via OData: In the SAP backend, use the transaction
SEGW(Gateway Service Builder) to create a new project. Right-click the Data Model folder and select 'Reference -> Data Source'. Add the required CDS views. Generate the runtime objects to create an OData service that exposes these views for external consumption. - Activate OData Service: Go to transaction
/IWFND/MAINT_SERVICEon the SAP Gateway hub. Click 'Add Service', find the service you generated in the previous step, and add it. This makes the service active and available for querying. - Establish Data Connection: From your chosen data extraction tool (such as Python, Power BI, or a dedicated ETL platform), establish a connection to the newly created OData service. You will need the service URL and appropriate credentials with authorization to access the CDS views.
- Adapt and Execute the Query: Copy the provided SQL query. Modify the placeholder values for date ranges, company codes, and any custom CDS view or batch job names to match your specific SAP environment. Execute the query against the OData service endpoint. Note that OData uses a different syntax ($filter, $select), so you may need to translate the logic of the UNION ALL query into a series of separate API calls, one for each activity.
- Consolidate Activity Data: Since OData does not typically support complex UNION ALL queries directly in a single call, you will execute a separate request for each activity block in the query. Consolidate the results from all requests into a single dataset or table within your client tool.
- Transform and Clean Data: Review the consolidated data. Ensure the
EventTimeandEndTimecolumns are in a consistent timestamp format. Verify thatFinancialPeriodis correctly formatted as 'YYYY-MM'. Handle any NULL values in columns likeResponsibleUserorDocumentNumberas needed. - Export as Event Log: Export the final, consolidated dataset to a CSV file. Ensure the column headers match the required attributes:
FinancialPeriod,ActivityName,EventTime,EndTime,ResponsibleUser, etc. This file is now ready to be uploaded to ProcessMind.
Configuration
- CDS Views: The query relies on a combination of standard and custom CDS views. Standard views like
I_JournalEntryItem(for journal entries) andI_BatchJob(for automated runs) are essential. Custom views, likeZ_I_ACCOUNTRECONCILIATION, are often necessary to capture the status of internal processes not covered by standard views. - Date Range: Define a specific date range for the extraction to manage data volume. The query uses placeholders
[Start Date]and[End Date]which should typically cover a period of 3 to 6 full fiscal periods for meaningful analysis. - Key Filters: Filtering is crucial for performance and relevance. Always filter by
CompanyCode. Other important filters includeLedger(e.g., '0L' for the leading ledger),FiscalYear, and specificDocumentTypevalues to differentiate between regular postings, accruals, and adjustments. - Prerequisites: The user running the extraction requires authorizations in SAP to access the specified CDS views and execute OData services (e.g., authorization objects
S_SERVICE,S_RFC). SAP Gateway services must be configured and activated. - Performance: Querying
I_JournalEntryItemcan be performance-intensive. Always apply selective filters, especially on dates, company codes, and ledgers. Consider running the extraction during off-peak hours or using an incremental loading strategy for continuous monitoring.
a Sample Query sql
SELECT
CONCAT(CAST(PostingDate AS DATE FORMAT 'YYYY'), '-', CAST(PostingDate AS DATE FORMAT 'MM')) AS FinancialPeriod,
'Period Opened For Posting' AS ActivityName,
CAST(CreationDateTime AS TIMESTAMP) AS EventTime,
CAST(CreationDateTime AS TIMESTAMP) AS EndTime,
CreatedByUser AS ResponsibleUser,
CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
0 AS ProcessingTime
FROM I_FinPostingPeriodControl -- This is a proxy; this activity is a configuration change often found in change logs (CDHDR/CDPOS for table T001B)
WHERE PostingPeriodVariant = '[Your Variant]' AND FiscalYear = '[Your Fiscal Year]' AND FromPostingPeriod = 1 AND AuthorizationGroup IS NOT NULL -- Logic to detect the opening event
UNION ALL
SELECT
CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod, -- Infer period from Job Name if patterned, otherwise use job start date
'Subledger Data Transfer Completed' AS ActivityName,
CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
JobCreatedByUser AS ResponsibleUser,
'[Configure based on your system]' AS CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE '[Subledger Job Prefix]%' AND JobStatus = 'F' -- 'F' for Finished
AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(CAST(CreationDate AS DATE FORMAT 'YYYY'), '-', CAST(CreationDate AS DATE FORMAT 'MM')) AS FinancialPeriod,
'Intercompany Reconciliation Started' AS ActivityName,
CAST(CreationDateTime AS TIMESTAMP) AS EventTime,
NULL AS EndTime,
CreatedByUser AS ResponsibleUser,
LeadingCompanyCode AS CompanyCode,
NULL AS GlAccountNumber,
ReconciliationCase AS DocumentNumber,
ReconciliationCaseStatus AS ReconciliationStatus,
NULL AS TargetCompletionDate,
NULL AS ProcessingTime
FROM I_ICRcnCase -- Part of SAP S/4HANA for group reporting
WHERE CreationDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
'Accruals And Provisions Posted' AS ActivityName,
CAST(CreationDateTime AS TIMESTAMP) AS EventTime,
CAST(CreationDateTime AS TIMESTAMP) AS EndTime,
CreatedByUser AS ResponsibleUser,
CompanyCode,
GLAccountNumber,
AccountingDocument AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
0 AS ProcessingTime
FROM I_JournalEntryItem
WHERE DocumentType IN ('[Accrual Doc Type 1]', '[Accrual Doc Type 2]') -- e.g., 'SA', 'AC'
AND PostingDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod,
'Automated Clearing Run Executed' AS ActivityName,
CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
JobCreatedByUser AS ResponsibleUser,
'[Configure based on your system]' AS CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE 'SAPF124%' AND JobStatus = 'F'
AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod,
'Foreign Currency Valuation Run' AS ActivityName,
CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
JobCreatedByUser AS ResponsibleUser,
'[Configure based on your system]' AS CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE 'FAGL_FCV%' AND JobStatus = 'F'
AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
-- This activity often requires a custom view on a custom table or an add-on like SAP Account Substantiation and Automation.
-- The following is a placeholder for such a custom view.
SELECT
CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
'Account Reconciliation Started' AS ActivityName,
CAST(ChangedAt AS TIMESTAMP) AS EventTime,
NULL AS EndTime,
ChangedBy AS ResponsibleUser,
CompanyCode,
GLAccountNumber,
ReconciliationID AS DocumentNumber,
'In Progress' AS ReconciliationStatus,
PlannedCompletionDate AS TargetCompletionDate,
NULL AS ProcessingTime
FROM Z_I_ACCOUNTRECONCILIATION -- Placeholder for your custom reconciliation status view
WHERE Status = 'IN_PROGRESS' AND OldStatus = 'NEW'
AND ChangeDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
'Adjusting Journal Entry Posted' AS ActivityName,
CAST(CreationDateTime AS TIMESTAMP) AS EventTime,
CAST(CreationDateTime AS TIMESTAMP) AS EndTime,
CreatedByUser AS ResponsibleUser,
CompanyCode,
GLAccountNumber,
AccountingDocument AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
0 AS ProcessingTime
FROM I_JournalEntryItem
WHERE DocumentType IN ('[Adjustment Doc Type 1]', '[Adjustment Doc Type 2]') -- e.g., 'AJ'
AND PostingDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
-- This activity often requires a custom view on a custom table or an add-on product.
-- The following is a placeholder for such a custom view.
SELECT
CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
'Account Reconciliation Approved' AS ActivityName,
CAST(ChangedAt AS TIMESTAMP) AS EventTime,
CAST(ChangedAt AS TIMESTAMP) AS EndTime,
ApprovedBy AS ResponsibleUser,
CompanyCode,
GLAccountNumber,
ReconciliationID AS DocumentNumber,
'Approved' AS ReconciliationStatus,
PlannedCompletionDate AS TargetCompletionDate,
NULL AS ProcessingTime
FROM Z_I_ACCOUNTRECONCILIATION -- Placeholder for your custom reconciliation status view
WHERE Status = 'APPROVED'
AND ChangeDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(FiscalYear, '-', LPAD(FiscalPeriod, 2, '0')) AS FinancialPeriod,
'Consolidation Tasks Executed' AS ActivityName,
CAST(TaskExecutionUTCTimestamp AS TIMESTAMP) AS EventTime,
CAST(TaskCompletionUTCTimestamp AS TIMESTAMP) AS EndTime,
ExecutedByUser AS ResponsibleUser,
ConsolidationUnitCompanyCode AS CompanyCode, -- Assuming mapping exists
NULL AS GlAccountNumber,
DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
SECONDS_BETWEEN(CAST(TaskExecutionUTCTimestamp AS TIMESTAMP), CAST(TaskCompletionUTCTimestamp AS TIMESTAMP)) AS ProcessingTime
FROM I_ConsolidationTaskLog
WHERE ConsolidationTaskStatus = 'S' -- Successful
AND TaskExecutionUTCDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod,
'Trial Balance Prepared' AS ActivityName,
CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
JobCreatedByUser AS ResponsibleUser,
'[Configure based on your system]' AS CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE '[Trial Balance Job Prefix]%' AND JobStatus = 'F' -- Example: FAGL_TRIAL_BALANCE_RUN
AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(SUBSTRING(JobName, STRPOS(JobName, '_') + 1, 4), '-', SUBSTRING(JobName, STRPOS(JobName, '_') + 5, 2)) AS FinancialPeriod,
'Financial Statements Generated' AS ActivityName,
CAST(JobStartDate || JobStartTime AS TIMESTAMP) AS EventTime,
CAST(JobEndDate || JobEndTime AS TIMESTAMP) AS EndTime,
JobCreatedByUser AS ResponsibleUser,
'[Configure based on your system]' AS CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
SECONDS_BETWEEN(CAST(JobStartDate || JobStartTime AS TIMESTAMP), CAST(JobEndDate || JobEndTime AS TIMESTAMP)) AS ProcessingTime
FROM I_BatchJob
WHERE JobName LIKE '[Fin Statement Job Prefix]%' AND JobStatus = 'F'
AND JobEndDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
-- Approval is often a manual step. This requires a custom solution to capture.
-- The following is a placeholder for such a custom view.
SELECT
CONCAT(ApprovalYear, '-', LPAD(ApprovalPeriod, 2, '0')) AS FinancialPeriod,
'Financial Statements Approved' AS ActivityName,
CAST(ApprovalTimestamp AS TIMESTAMP) AS EventTime,
CAST(ApprovalTimestamp AS TIMESTAMP) AS EndTime,
ApproverUser AS ResponsibleUser,
CompanyCode,
NULL AS GlAccountNumber,
FinancialStatementVersion AS DocumentNumber,
'Approved' AS ReconciliationStatus,
NULL AS TargetCompletionDate,
0 AS ProcessingTime
FROM Z_I_FINSTATEMENTAPPROVAL -- Placeholder for custom approval tracking view
WHERE ApprovalDate BETWEEN '[Start Date]' AND '[End Date]'
UNION ALL
SELECT
CONCAT(CAST(LastChangeDate AS DATE FORMAT 'YYYY'), '-', CAST(LastChangeDate AS DATE FORMAT 'MM')) AS FinancialPeriod,
'Period Closed For Posting' AS ActivityName,
CAST(LastChangeDateTime AS TIMESTAMP) AS EventTime,
CAST(LastChangeDateTime AS TIMESTAMP) AS EndTime,
LastChangedByUser AS ResponsibleUser,
CompanyCode,
NULL AS GlAccountNumber,
NULL AS DocumentNumber,
NULL AS ReconciliationStatus,
NULL AS TargetCompletionDate,
0 AS ProcessingTime
FROM I_FinPostingPeriodControl -- Proxy, see note on 'Period Opened'
WHERE PostingPeriodStatus = 'C' -- 'C' for Closed
AND LastChangeDate BETWEEN '[Start Date]' AND '[End Date]';