Your Record to Report - Journal Entry Data Template
Your Record to Report - Journal Entry Data Template
- Recommended attributes to collect
- Key activities to track
- Practical extraction guidance
Record to Report - Journal Entry Attributes
| Name | Description | ||
|---|---|---|---|
| Activity ActivityName | The name of the business activity that occurred at a specific point in the journal entry process. | ||
| Description The Activity represents a specific step or event in the lifecycle of a journal entry, such as 'Journal Entry Created', 'Journal Submitted For Review', or 'Journal Entry Posted'. These activities are typically derived from change logs, status updates, or transaction codes recorded in the system. Analyzing activities allows for the visualization of the process flow, identification of common pathways, and discovery of deviations from the standard procedure. It is fundamental for calculating metrics like activity frequency, waiting times between steps, and conformance rates. Why it matters It defines the steps in the process, enabling the visualization of process maps and the analysis of workflow patterns. Where to get Derived from various sources including status fields in header/item tables (e.g., BKPF-BSTAT), change document logs (CDHDR/CDPOS), and workflow logs. Examples Journal Entry CreatedJournal Entry ParkedJournal Submitted For ReviewJournal Entry ApprovedJournal Entry Posted | |||
| Event Time EventTime | The timestamp indicating when a specific activity occurred for the journal entry. | ||
| Description Event Time is the precise date and time that a business activity was executed and recorded in the system. Each activity in a case has its own timestamp, creating a chronological sequence of events. This attribute is critical for all time-based process analysis. It is used to calculate cycle times, durations between activities, waiting times, and to understand the temporal distribution of work. Accurate timestamps are essential for building a reliable process model and calculating key performance indicators like Approval Cycle Time. Why it matters It provides the chronological order of events, which is essential for calculating all duration-based metrics and understanding the process timeline. Where to get Sourced from change document logs (CDHDR-UDATE, CDHDR-UTIME), workflow logs, or creation/entry timestamps on tables like BKPF (CPUDT, CPUTM). Examples 2023-10-26T10:05:00Z2023-11-15T14:30:15Z2024-01-20T09:00:45Z | |||
| Journal Entry ID JournalEntryId | The unique identifier for a financial journal entry, which serves as the primary case identifier for the process. | ||
| Description The Journal Entry ID is a unique number assigned to each accounting document upon its creation in SAP S/4HANA. This identifier is essential for tracking the complete lifecycle of a journal entry, from its initial creation or parking, through approval workflows, to its final posting and potential reversal or clearing. In process mining analysis, this ID is used to link all related activities into a single case. By grouping events under a common Journal Entry ID, analysts can reconstruct the end-to-end process flow, measure cycle times, and identify variations or bottlenecks for each specific financial transaction. It is the foundational attribute for building the entire process view. Why it matters This identifier connects all related process steps, making it possible to analyze the end-to-end journey of each journal entry. Where to get This is a composite key, typically formed by concatenating Company Code (BKPF-BUKRS), Document Number (BKPF-BELNR), and Fiscal Year (BKPF-GJAHR). Examples 1000-1000000001-20231710-1900000055-20242000-2100003412-2023 | |||
| Amount In Local Currency AmountInLocalCurrency | The total value of the journal entry expressed in the local currency of the company code. | ||
| Description This attribute represents the financial magnitude of the journal entry. It is typically the sum of the absolute values of all debit or credit line items in the document, converted to the company code's local currency. Analyzing by amount allows for the segmentation of the process based on financial impact. For instance, high-value entries might follow a more rigorous approval process than low-value ones. It helps in prioritizing process improvement efforts on transactions that pose the highest financial risk. Why it matters Provides the financial value of the entry, enabling analysis of how process behavior changes with the monetary value at stake. Where to get Calculated by summing amounts from line item table BSEG (field DMBTR) for a given journal entry (BELNR) and converting to a positive value. Examples 1500.75125000.0050.20 | |||
| Company Code CompanyCode | The unique identifier for the company or legal entity for which the journal entry is posted. | ||
| Description The Company Code is a fundamental organizational unit in SAP Financials, representing an independent legal entity for which financial statements are generated. Every journal entry is assigned to a specific company code. This attribute is critical for segmenting and comparing process performance across different parts of the organization. Analysts can use it to filter the process view for a specific legal entity, compare rejection rates between company codes, or identify region-specific process variations. Why it matters Allows for filtering and comparing the journal entry process across different legal entities or business units within the organization. Where to get SAP S/4HANA table BKPF, field BUKRS (Company Code). Examples 10001710US01 | |||
| Created By User CreatedByUser | The user ID of the person who created the journal entry. | ||
| Description This attribute stores the unique identifier of the user who initiated the journal entry process by creating the initial document. This could be an accountant, a business user, or a system ID for automated entries. Analyzing the process by the creator helps identify patterns related to specific users or teams. It can reveal training needs if certain users have higher rejection rates, or highlight high-performing individuals. It is essential for the 'User Activity and Throughput' dashboard. Why it matters Attributes process activities to specific users, enabling performance analysis, workload balancing, and identification of training opportunities. Where to get SAP S/4HANA table BKPF, field USNAM (User Name). Examples ABROWNCJONESBATCH_USER | |||
| Journal Entry Type JournalEntryType | Classifies the journal entry based on its business purpose, such as an asset posting, vendor invoice, or general ledger entry. | ||
| Description The Journal Entry Type, or Document Type in SAP terminology, is a key that categorizes accounting documents. It controls aspects like the number range assigned to the document and which account types are permitted for posting. Analyzing the process by journal entry type is crucial for understanding context-specific behaviors. For example, the approval process for a simple accrual (type SA) may be much simpler than for a complex asset acquisition (type AA). This dimension is key for the 'Compliance by Entry Type' dashboard. Why it matters Categorizes entries by business context, enabling analysis of process variations and performance for different types of financial transactions. Where to get SAP S/4HANA table BKPF, field BLART (Document Type). Examples SAKRAA | |||
| Posting Date PostingDate | The date on which the journal entry is recorded in the General Ledger, affecting the financial period. | ||
| Description The Posting Date determines the fiscal period in which the transaction will appear on financial statements. It is a critical date for accounting and may differ from the date the document was created or entered into the system. In process mining, the posting date is used for time-based cohort analysis, such as comparing month-end closing processes or analyzing performance trends over different financial periods. It is also used to measure delays between entry creation and actual financial posting. Why it matters Crucial for financial context, it allows for analyzing process performance within specific accounting periods, like month-end or year-end. Where to get SAP S/4HANA table BKPF, field BUDAT (Posting Date in the Document). Examples 2023-10-312023-11-012024-02-29 | |||
| Approval Cycle Time ApprovalCycleTime | The time elapsed from when a journal entry was submitted for approval until it was either approved or rejected. | ||
| Description This calculated metric focuses specifically on the duration of the approval stage. It measures the time between the 'Journal Submitted For Review' activity and the subsequent 'Journal Entry Approved' or 'Journal Entry Rejected' activity. This KPI is critical for identifying bottlenecks within the approval workflow. High approval cycle times can significantly delay the overall process. Analyzing this metric by approver, company code, or journal entry type can reveal specific areas for improvement. Why it matters Isolates the duration of the approval step, helping to pinpoint and address bottlenecks in the review and approval workflow. Where to get Calculated by finding the time difference between the 'Journal Submitted For Review' event and the 'Journal Entry Approved' or 'Journal Entry Rejected' event. Examples 1 day 2 hours4 hours 25 minutes5 days 0 hours | |||
| Approver User ApproverUser | The user ID of the person who approved or rejected the journal entry. | ||
| Description This attribute identifies the user responsible for reviewing and making a decision on a submitted journal entry. In a multi-level approval workflow, there may be several approvers for a single journal entry. This information is essential for analyzing the approval process in detail. It helps measure the workload of different approvers, calculate individual approval times, and identify bottlenecks in the approval chain. It directly supports the 'User Activity and Throughput' dashboard. Why it matters Identifies the individual responsible for approval, enabling analysis of approval workloads, performance, and bottlenecks. Where to get Sourced from workflow logs (e.g., SWW_WI2OBJ, SWWLOG) or change document tables (CDHDR/CDPOS) by tracking who executed the approval step. Examples DMILLERFWHITEKCHEN | |||
| Document Status DocumentStatus | The current processing status of the journal entry, such as Parked, Posted, or Cleared. | ||
| Description The Document Status indicates the state of the journal entry within its lifecycle. For example, a 'parked' document is saved but not yet posted to the General Ledger, while a 'posted' document is finalized. Analyzing the status helps to understand the flow of work and identify bottlenecks. A high volume of documents remaining in a 'parked' or 'pending approval' state for long periods can signal inefficiencies in the process. It is also a key source for deriving process activities. Why it matters Provides a snapshot of where a journal entry is in its lifecycle, helping to identify queues and bottlenecks. Where to get SAP S/4HANA table BKPF, field BSTAT (Document status). Examples VAB | |||
| End Time EndTime | The timestamp indicating when the activity was completed. | ||
| Description The End Time marks the completion of an activity. In many event logs, the Start Time and End Time of an activity are the same, representing an instantaneous event. However, for activities that have a measurable duration, like a user actively reviewing a document, this attribute can capture that duration. Having a distinct End Time allows for more precise calculation of activity processing times versus waiting times. It helps differentiate the time a task was actively being worked on from the time it was idle in a queue. Why it matters Enables the calculation of precise activity processing times, separating active work time from idle waiting time. Where to get Typically the same as StartTime for atomic events. For durational activities, it may be sourced from workflow logs or calculated based on subsequent events. Examples 2023-10-26T10:05:00Z2023-11-15T14:45:20Z2024-01-20T09:10:30Z | |||
| Fiscal Year FiscalYear | The fiscal year to which the journal entry belongs. | ||
| Description The Fiscal Year is part of the unique key for a journal entry, alongside the company code and document number. It represents the financial year in which the document is relevant. In analysis, the fiscal year is used for long-term trend analysis and for ensuring the uniqueness of the case identifier. Comparing process metrics across different fiscal years can reveal improvements or degradations in performance over time. Why it matters Provides a critical component for uniquely identifying documents and enables year-over-year process performance analysis. Where to get SAP S/4HANA table BKPF, field GJAHR (Fiscal Year). Examples 202320242022 | |||
| Is Manual Posting IsManualPosting | A boolean flag that indicates whether the journal entry was posted manually by a user. | ||
| Description This attribute identifies journal entries that were posted through manual user intervention, as opposed to being posted automatically by a system job or interface. It is typically derived from the Transaction Code used to post the document. This flag is used to calculate the Manual Posting Rate KPI and helps organizations track their progress in automating the Record to Report process. By filtering for manually posted entries, analysts can identify the specific scenarios that still require human touch and evaluate them for automation potential. Why it matters Differentiates between human and system-driven postings, which is critical for measuring automation levels and identifying automation opportunities. Where to get This is a calculated attribute derived from the TransactionCode. A predefined list of manual transaction codes (e.g., 'FB01', 'F-02') is used to set the flag to 'true'. Examples truefalse | |||
| Is Rework IsRework | A boolean flag indicating if the journal entry has undergone rework, such as being corrected after a rejection. | ||
| Description This calculated attribute flags journal entries that have deviated from the ideal 'happy path' process. It is typically set to true if an activity like 'Journal Entry Rejected' or 'Journal Entry Corrected' occurs within the case. This flag simplifies the analysis of process efficiency. It allows for quick calculation of the Rework Rate KPI and enables direct comparison of cycle times and costs between cases with and without rework. Identifying the drivers of rework is a primary goal of many process improvement initiatives. Why it matters Flags cases that required correction or extra loops, allowing for easy quantification and root cause analysis of process inefficiencies. Where to get This is a calculated attribute derived from the sequence of activities in a case. It is flagged as 'true' if an activity like 'Journal Entry Rejected' is present. Examples truefalse | |||
| Last Data Update LastDataUpdate | Timestamp indicating the last time the data for this record was refreshed from the source system. | ||
| Description This attribute records the date and time of the most recent data extraction or update from the source system. It provides transparency on the freshness of the data being analyzed. Knowing the last update time is important for understanding the currency of the process analysis. It helps users interpret the dashboards and KPIs correctly, knowing if they are looking at near real-time data or a snapshot from a previous period. Why it matters Indicates the freshness of the data, ensuring users are aware of how current the process analysis is. Where to get This is a metadata attribute, typically generated and stamped on each record during the data ingestion pipeline. Examples 2024-03-10T02:00:00Z2024-03-11T02:00:00Z2024-03-12T02:00:00Z | |||
| Reversal Reason ReversalReason | A code indicating the reason why a posted journal entry was reversed. | ||
| Description When a posted journal entry is incorrect, it cannot be deleted but must be reversed with a new document. The Reversal Reason code explains why this action was taken, for example, due to an incorrect posting date or amount. Analyzing reversal reasons helps to identify the root causes of errors in the Record to Report process. A high frequency of a particular reason can point to systemic issues, such as inadequate training or control failures, that need to be addressed to improve first-time quality. Why it matters Helps diagnose the root cause of errors leading to reversals, providing insights needed to reduce rework and improve process quality. Where to get SAP S/4HANA table BKPF, field STGRD (Reason for reversal). Examples 010205 | |||
| Source System SourceSystem | Identifies the source system from which the journal entry data was extracted. | ||
| Description This attribute specifies the system of record where the journal entry data originated. For companies with multiple ERP instances or a mix of legacy and modern systems, this helps differentiate data sources. In analysis, it can be used to compare process performance across different systems or to filter data for a specific source. It is important for data governance and ensuring the context of the data is understood. Why it matters Provides context about data origin, which is crucial in multi-system landscapes for accurate process analysis and comparison. Where to get This is typically a static value added during data extraction, identifying the specific SAP S/4HANA instance (e.g., SID or logical system name). Examples S4H_PROD_100ECC_FIN_200S4C_US_EAST | |||
| Total Cycle Time TotalCycleTime | The total duration from the creation of the first activity to the completion of the last activity for a journal entry. | ||
| Description This calculated metric measures the end-to-end duration of the journal entry process for each case. It is the difference between the timestamp of the very last observed activity and the timestamp of the very first one. Total Cycle Time is a primary KPI for measuring overall process efficiency. It provides a high-level view of process performance and is used in dashboards to track trends over time. Analyzing the drivers of long cycle times is a common starting point for process improvement. Why it matters Measures the end-to-end process duration, providing a key performance indicator for overall process efficiency and speed. Where to get Calculated by subtracting the minimum EventTime from the maximum EventTime for each unique JournalEntryId. Examples 2 days 4 hours 30 minutes8 hours 15 minutes15 days 2 hours | |||
| Transaction Code TransactionCode | The SAP transaction code used to create or change the journal entry. | ||
| Description The Transaction Code (T-Code) is a shortcut that identifies a specific function or program in SAP. For journal entries, different T-Codes can indicate how the entry was created, for example, FB01 for manual GL posting, FV50 for parking, or an automated code for system-generated entries. This attribute is a strong indicator of whether an activity was performed manually by a user or automatically by the system. It is key for calculating the Manual Posting Rate KPI and for identifying opportunities for automation. Why it matters Indicates how an entry was processed (e.g., manually vs. automatically), which is key for automation analysis and understanding process variations. Where to get SAP S/4HANA table BKPF, field TCODE (Transaction Code). Examples FB01FV50F-02 | |||
Record to Report - Journal Entry Activities
| Activity | Description | ||
|---|---|---|---|
| Journal Entry Approved | The journal entry receives final approval from an authorized manager, confirming its validity and accuracy. This activity is the final gate before the document can be posted to the general ledger. | ||
| Why it matters This is a critical milestone that concludes the approval cycle. The time taken to reach this step is a major component of the overall process duration and a key indicator of approver efficiency. Where to get This event is inferred from a workflow log showing the final approval step or a status change on the document. The approver's user ID and timestamp can be sourced from workflow data or change logs. Capture Identify timestamp of final approval step in workflow logs or status change to 'Approved' in change documents. Event type inferred | |||
| Journal Entry Cleared | An open item line within a journal entry is offset by another posting, such as a payment clearing an invoice. This activity marks the reconciliation of specific line items, effectively closing them out. | ||
| Why it matters This activity represents the final reconciliation step for many journal entries, especially those involving suspense or open-item managed accounts. Analyzing the time from posting to clearing helps measure reconciliation efficiency. Where to get This event is inferred from the line item table (BSEG or the ACDOCA view). When an item is cleared, the Clearing Date (AUGDT) and Clearing Document (AUGBL) fields are populated for that line. Capture Use the clearing date (BSEG-AUGDT) for the line item as the timestamp for the event. Event type inferred | |||
| Journal Entry Created | This activity marks the initial creation of a journal entry document in the system. The record is created in the header table (BKPF), but it has not yet been posted to the general ledger. This is the starting point for the journal entry lifecycle. | ||
| Why it matters This is the primary start event for the process. Analyzing the time from this event to posting is crucial for measuring overall cycle time and identifying initial data entry delays. Where to get This event can be explicitly captured from the SAP table BKPF using the creation date (CPUDT) and creation time (CPUTM) fields for a given document number (BELNR). Capture Use BKPF-CPUDT and BKPF-CPUTM for the event timestamp. Event type explicit | |||
| Journal Entry Posted | The journal entry is officially recorded in the general ledger, impacting the company's financial statements. This is the point at which the document becomes a permanent financial record. | ||
| Why it matters This is the primary success milestone, marking the end of the core processing cycle. Analyzing the throughput of posted entries and the time to reach this stage are fundamental process mining metrics. Where to get This is an explicit event marked by the Posting Date (BUDAT) in the BKPF table. A posted document has a blank document status (BSTAT), distinguishing it from parked ('V') or held ('D') documents. Capture Use the Posting Date (BKPF-BUDAT) and Entry Date (BKPF-CPUDT) to timestamp the event. A blank BKPF-BSTAT indicates a posted document. Event type explicit | |||
| Journal Entry Reversal Processed | A previously posted journal entry is reversed by creating a new document with inverse postings. This action is taken to correct errors in posted documents and is an explicit, auditable transaction. | ||
| Why it matters Reversals indicate that an error was made in a posted document. A high reversal rate suggests underlying issues in the approval process or data entry quality, and tracking this helps improve first-time accuracy. Where to get The reversal is an explicit event. The new reversing document's header (BKPF) contains a reference to the original document in the Reversed Document No. (STBLG) field. The posting date of the new document is the event time. Capture Identify documents where BKPF-STBLG is populated. The event timestamp is the posting date of the reversing document. Event type explicit | |||
| Journal Submitted For Review | The creator of the journal entry formally submits the document for the review and approval workflow. This activity represents the handoff from data entry to the formal control process, initiating the approval cycle. | ||
| Why it matters This marks the beginning of the approval cycle time. Measuring from this point to the final approval or rejection helps isolate bottlenecks specifically within the review and approval stages. Where to get This is often captured from workflow logs (tables SWW_WIHEAD, SWWLOG) linked to the business object. It can also be inferred from a status change in a custom field on the document header (BKPF). Capture Timestamp of workflow item creation or a status field changing to 'Submitted' or 'In Review'. Event type inferred | |||
| Journal Entry Changed After Posting | A user modifies a limited set of fields on a journal entry after it has already been posted to the general ledger. While most financial data is immutable post-posting, some fields like text or assignments can be changed. | ||
| Why it matters This activity is a critical compliance flag. Changes after posting can indicate attempts to alter records and should be closely monitored to prevent fraud and ensure data integrity. Where to get This can be reliably inferred from change document tables (CDHDR and CDPOS). An entry in CDHDR for the document number with a change date after the posting date signifies a post-posting change. Capture Find records in CDHDR where the change timestamp (UDATE/UTIME) is after the document's posting date (BKPF-BUDAT). Event type inferred | |||
| Journal Entry Corrected | The user modifies a journal entry after it was rejected or sent back for changes. This represents the rework effort required to address issues identified during the review process before resubmission. | ||
| Why it matters This activity quantifies rework loops. Analyzing the frequency and duration of corrections helps pinpoint sources of inefficiency and highlights opportunities for training and process clarification. Where to get This can be inferred by tracking the 'Last Changed On' date (AEDAT) in the BKPF table for a document that was previously in a 'Rejected' state. Change documents provide more specific details on what was changed. Capture Use the timestamp from change document headers (CDHDR-UDATE) for changes made after a rejection event. Event type inferred | |||
| Journal Entry Parked | A user saves an incomplete journal entry without posting it, allowing for later completion or review. This is an explicit action that creates a document header record with a 'parked' status, holding it in a non-posted state. | ||
| Why it matters Parking is a common step before submission. Tracking the duration of the parked state helps identify delays in data completion and preparation before the formal review and approval process begins. Where to get In the BKPF table, a parked document is identified by the document status field (BSTAT) having a value of 'V'. The event timestamp is the creation date (CPUDT). Capture Filter for documents where BKPF-BSTAT = 'V' at time of creation. Event type explicit | |||
| Journal Entry Rejected | A reviewer or approver denies the journal entry, preventing it from being posted. The document is typically sent back to the creator for correction, initiating a rework loop. | ||
| Why it matters Tracking rejections is key to understanding process quality and identifying common errors. High rejection rates indicate problems with data accuracy, policy understanding, or inadequate supporting documentation. Where to get This event is inferred from a status change in a workflow log or a custom status field on the journal entry document. Change document logs (CDHDR/CDPOS) on the relevant status field can provide the timestamp. Capture Identify status field change to 'Rejected' via change documents (CDHDR/CDPOS) or workflow logs. Event type inferred | |||
| Manual Posting Identified | The journal entry was posted using a manual transaction code rather than through an automated interface or batch job. This is not a temporal event but a classification of the posting activity. | ||
| Why it matters Identifying manual postings is crucial for automation initiatives. A high rate of manual postings suggests opportunities to streamline processes by integrating subsystems or using automated posting programs. Where to get This is calculated by analyzing the transaction code (TCODE) field in the document header table (BKPF). A list of known manual T-Codes (e.g., FB01, F-02, FB50) is used to classify the entry. Capture Classify event based on BKPF-TCODE against a predefined list of manual transaction codes at the time of posting. Event type calculated | |||
| Supporting Documentation Attached | A user attaches one or more supporting documents, such as invoices or spreadsheets, to the journal entry. This is typically done to provide evidence and context for the financial transaction during the review and audit process. | ||
| Why it matters Ensuring documentation is attached before review is critical for compliance and approval efficiency. This activity helps measure adherence to documentation policies and its impact on approval cycle times. Where to get This is typically inferred by checking the creation timestamp of attachments linked via Generic Object Services (GOS). The SRGBTBREL table links the business object (e.g., BKPF document) to the attachment. Capture Query GOS attachment tables (e.g., SRGBTBREL) for links to the BKPF object and use the attachment creation timestamp. Event type inferred | |||
Extraction Guides
Steps
- Prerequisites and Access: Ensure you have a user with the necessary authorizations to query SAP S/4HANA's underlying database or execute ABAP reports. You will need read access to the CDS views I_JournalEntry, I_JournalEntryItem, and the tables CDHDR, CDPOS, SRGBREL, SOOD, SWW_WI2OBJ, and SWWLOGHIST. Access is typically granted through a database client like SAP HANA Studio, DBeaver, or using SAP's ABAP Development Tools (ADT) for Eclipse.
- Identify System-Specific Configurations: Before running the query, you must identify the specific task codes used in your journal entry approval workflow. Consult with your SAP workflow administrator to find the task IDs (e.g., TS12345678) that correspond to submission, rejection, and approval events. These are required for the placeholders in the final query.
- Prepare the SQL Query: Copy the complete SQL query provided in the
querysection into your chosen SQL client or development tool. - Set Query Parameters: Locate the placeholders within the query and replace them with your specific values. This includes setting the
[YourCompanyCode],[StartDate], and[EndDate]parameters. You must also replace the placeholder workflow task IDs ([Workflow Submitted Task ID],[Workflow Rejected Task ID],[Workflow Approved Task ID]) with the values you identified in the previous step. - Execute the Extraction Query: Run the modified SQL query against the SAP S/4HANA database. Depending on the date range and data volume, the query may take a significant amount of time to complete. It is recommended to run this during off-peak hours.
- Review the Initial Results: Once the query finishes, examine the first few rows of the output to ensure that all columns, such as JournalEntryId, ActivityName, and EventTime, are populated as expected. The result set should contain one row for each distinct business event in the journal entry lifecycle.
- Export Data to CSV: Export the entire result set from your SQL tool into a single CSV file. Ensure the file uses UTF-8 encoding to prevent issues with special characters.
- Prepare for Upload: Before uploading to a process mining tool, confirm the CSV file has the required headers. The data is already structured as an event log, so no further transformation or pivoting should be necessary.
Configuration
- Core Data Services (CDS) Views: The extraction primarily uses
I_JournalEntryfor header data andI_JournalEntryItemfor line item and amount details. These views provide a simplified and semantically rich interface to the universal journal (ACDOCA). - Supporting Tables: To capture a complete process view, the query also joins several standard SAP tables:
CDHDRandCDPOSfor tracking changes to documents.SRGBRELandSOODfor identifying when attachments are linked via Generic Object Services (GOS).SWW_WI2OBJandSWWLOGHISTfor extracting key events from the approval workflow.
- Date Range Filtering: It is critical to filter the data by a specific date range to manage performance. Use the
I_JournalEntry.CreationDateTimefield in theWHEREclause. A range of 3 to 6 months is recommended for an initial analysis. - Organizational Filtering: Always filter by
CompanyCodeto limit the extraction to relevant legal entities. Querying for all company codes at once in a large system can lead to extremely long execution times. - Workflow Task IDs: The query contains placeholders for workflow task IDs (e.g.,
[Workflow Approved Task ID]). These are unique to every SAP installation and must be configured correctly for workflow activities to be extracted. Without them, no submission, approval, or rejection events will be captured. - Prerequisites: The executing user requires extensive read authorizations to financial, system, and workflow tables. These permissions are not standard and must be specifically assigned.
a Sample Query sql
WITH JournalEntryAmountCTE AS (
SELECT
CompanyCode,
AccountingDocument,
FiscalYear,
SUM(AmountInCompanyCodeCurrency) AS AmountInLocalCurrency
FROM I_JournalEntryItem
GROUP BY CompanyCode, AccountingDocument, FiscalYear
),
JournalEntryBaseCTE AS (
SELECT
JE.CompanyCode,
JE.AccountingDocument,
JE.FiscalYear,
JE.CreatedByUser,
JE.CreationDateTime,
JE.PostingDateTime,
JE.PostingDate,
JE.AccountingDocumentType,
JE.DocumentIsParked,
JE.ReversedJournalEntry,
JE.TransactionCode,
JEA.AmountInLocalCurrency
FROM I_JournalEntry AS JE
LEFT JOIN JournalEntryAmountCTE AS JEA
ON JE.CompanyCode = JEA.CompanyCode
AND JE.AccountingDocument = JEA.AccountingDocument
AND JE.FiscalYear = JEA.FiscalYear
WHERE JE.CompanyCode IN ('[YourCompanyCode]')
AND JE.CreationDateTime BETWEEN '[StartDate]' AND '[EndDate]'
)
-- 1. Journal Entry Created
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Created' AS "ActivityName",
BJE.CreationDateTime AS "EventTime",
BJE.CreatedByUser AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
UNION ALL
-- 2. Journal Entry Parked
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Parked' AS "ActivityName",
BJE.CreationDateTime AS "EventTime",
BJE.CreatedByUser AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
WHERE BJE.DocumentIsParked = 'X'
UNION ALL
-- 3. Supporting Documentation Attached
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Supporting Documentation Attached' AS "ActivityName",
TO_TIMESTAMP(SOOD.CREDAT || ' ' || SOOD.CRETIM, 'YYYYMMDD HH24MISS') AS "EventTime",
SOOD.OWNER AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SRGBREL ON SRGBREL.INSTID_A = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
AND SRGBREL.TYPEID_A = 'BKPF'
AND SRGBREL.CATID_A = 'BO'
JOIN SOOD ON SOOD.OBJTP = SRGBREL.TYPEID_B
AND SOOD.OBJYR = SRGBREL.INSTID_B(3)
AND SOOD.OBJNO = SRGBREL.INSTID_B(5)
UNION ALL
-- 4. Journal Submitted For Review
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Submitted For Review' AS "ActivityName",
LOG.END_TS AS "EventTime",
LOG.EXEC_USER AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SWW_WI2OBJ AS WF_LINK ON WF_LINK.INSTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
AND WF_LINK.TYPEID = 'BKPF'
JOIN SWWLOGHIST AS LOG ON LOG.WI_ID = WF_LINK.WI_ID
WHERE LOG.METHOD = '[Workflow Submitted Task ID]'
UNION ALL
-- 5. Journal Entry Rejected
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Rejected' AS "ActivityName",
LOG.END_TS AS "EventTime",
LOG.EXEC_USER AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SWW_WI2OBJ AS WF_LINK ON WF_LINK.INSTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
AND WF_LINK.TYPEID = 'BKPF'
JOIN SWWLOGHIST AS LOG ON LOG.WI_ID = WF_LINK.WI_ID
WHERE LOG.METHOD = '[Workflow Rejected Task ID]'
UNION ALL
-- 6. Journal Entry Corrected (changed while parked)
SELECT DISTINCT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Corrected' AS "ActivityName",
TO_TIMESTAMP(CH.UDATE || ' ' || CH.UTIME, 'YYYYMMDD HH24MISS') AS "EventTime",
CH.USERNAME AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN CDHDR AS CH ON CH.OBJECTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
AND CH.OBJECTCLASS = 'BELEG'
WHERE BJE.DocumentIsParked = 'X'
UNION ALL
-- 7. Journal Entry Approved
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Approved' AS "ActivityName",
LOG.END_TS AS "EventTime",
LOG.EXEC_USER AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SWW_WI2OBJ AS WF_LINK ON WF_LINK.INSTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
AND WF_LINK.TYPEID = 'BKPF'
JOIN SWWLOGHIST AS LOG ON LOG.WI_ID = WF_LINK.WI_ID
WHERE LOG.METHOD = '[Workflow Approved Task ID]'
UNION ALL
-- 8. Manual Posting Identified
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Manual Posting Identified' AS "ActivityName",
BJE.PostingDateTime AS "EventTime",
BJE.CreatedByUser AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
WHERE BJE.PostingDateTime IS NOT NULL AND BJE.TransactionCode IN ('FB01', 'F-02', 'FB50', 'FV50', 'FBB1', 'FBV1')
UNION ALL
-- 9. Journal Entry Posted
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Posted' AS "ActivityName",
BJE.PostingDateTime AS "EventTime",
BJE.CreatedByUser AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
WHERE BJE.PostingDateTime IS NOT NULL
UNION ALL
-- 10. Journal Entry Changed After Posting
SELECT DISTINCT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Changed After Posting' AS "ActivityName",
TO_TIMESTAMP(CH.UDATE || ' ' || CH.UTIME, 'YYYYMMDD HH24MISS') AS "EventTime",
CH.USERNAME AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN CDHDR AS CH ON CH.OBJECTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
AND CH.OBJECTCLASS = 'BELEG'
WHERE BJE.PostingDateTime IS NOT NULL AND TO_TIMESTAMP(CH.UDATE || ' ' || CH.UTIME, 'YYYYMMDD HH24MISS') > BJE.PostingDateTime
UNION ALL
-- 11. Journal Entry Cleared
SELECT
JEI.AccountingDocument AS "JournalEntryId",
'Journal Entry Cleared' AS "ActivityName",
MIN(JEI.ClearingDateTime) AS "EventTime",
BJE.CreatedByUser AS "CreatedByUser", -- Note: Clearing user is not directly available here
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM I_JournalEntryItem AS JEI
JOIN JournalEntryBaseCTE AS BJE ON JEI.AccountingDocument = BJE.AccountingDocument
AND JEI.CompanyCode = BJE.CompanyCode
AND JEI.FiscalYear = BJE.FiscalYear
WHERE JEI.ClearingDateTime IS NOT NULL
GROUP BY JEI.AccountingDocument, BJE.CreatedByUser, BJE.CompanyCode, BJE.AccountingDocumentType, BJE.PostingDate, BJE.AmountInLocalCurrency
UNION ALL
-- 12. Journal Entry Reversal Processed
SELECT
OriginalDoc.AccountingDocument AS "JournalEntryId",
'Journal Entry Reversal Processed' AS "ActivityName",
ReversalDoc.PostingDateTime AS "EventTime",
ReversalDoc.CreatedByUser AS "CreatedByUser",
OriginalDoc.CompanyCode AS "CompanyCode",
OriginalDoc.AccountingDocumentType AS "JournalEntryType",
OriginalDoc.PostingDate AS "PostingDate",
OriginalDoc.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS ReversalDoc
JOIN JournalEntryBaseCTE AS OriginalDoc
ON ReversalDoc.ReversedJournalEntry = OriginalDoc.AccountingDocument
AND ReversalDoc.CompanyCode = OriginalDoc.CompanyCode
AND ReversalDoc.ReversalFiscalYear = OriginalDoc.FiscalYear
WHERE ReversalDoc.ReversedJournalEntry IS NOT NULL;Steps
- Prerequisites and Access: Ensure you have a user with the necessary authorizations to query SAP S/4HANA's underlying database or execute ABAP reports. You will need read access to the CDS views I_JournalEntry, I_JournalEntryItem, and the tables CDHDR, CDPOS, SRGBREL, SOOD, SWW_WI2OBJ, and SWWLOGHIST. Access is typically granted through a database client like SAP HANA Studio, DBeaver, or using SAP's ABAP Development Tools (ADT) for Eclipse.
- Identify System-Specific Configurations: Before running the query, you must identify the specific task codes used in your journal entry approval workflow. Consult with your SAP workflow administrator to find the task IDs (e.g., TS12345678) that correspond to submission, rejection, and approval events. These are required for the placeholders in the final query.
- Prepare the SQL Query: Copy the complete SQL query provided in the
querysection into your chosen SQL client or development tool. - Set Query Parameters: Locate the placeholders within the query and replace them with your specific values. This includes setting the
[YourCompanyCode],[StartDate], and[EndDate]parameters. You must also replace the placeholder workflow task IDs ([Workflow Submitted Task ID],[Workflow Rejected Task ID],[Workflow Approved Task ID]) with the values you identified in the previous step. - Execute the Extraction Query: Run the modified SQL query against the SAP S/4HANA database. Depending on the date range and data volume, the query may take a significant amount of time to complete. It is recommended to run this during off-peak hours.
- Review the Initial Results: Once the query finishes, examine the first few rows of the output to ensure that all columns, such as JournalEntryId, ActivityName, and EventTime, are populated as expected. The result set should contain one row for each distinct business event in the journal entry lifecycle.
- Export Data to CSV: Export the entire result set from your SQL tool into a single CSV file. Ensure the file uses UTF-8 encoding to prevent issues with special characters.
- Prepare for Upload: Before uploading to a process mining tool, confirm the CSV file has the required headers. The data is already structured as an event log, so no further transformation or pivoting should be necessary.
Configuration
- Core Data Services (CDS) Views: The extraction primarily uses
I_JournalEntryfor header data andI_JournalEntryItemfor line item and amount details. These views provide a simplified and semantically rich interface to the universal journal (ACDOCA). - Supporting Tables: To capture a complete process view, the query also joins several standard SAP tables:
CDHDRandCDPOSfor tracking changes to documents.SRGBRELandSOODfor identifying when attachments are linked via Generic Object Services (GOS).SWW_WI2OBJandSWWLOGHISTfor extracting key events from the approval workflow.
- Date Range Filtering: It is critical to filter the data by a specific date range to manage performance. Use the
I_JournalEntry.CreationDateTimefield in theWHEREclause. A range of 3 to 6 months is recommended for an initial analysis. - Organizational Filtering: Always filter by
CompanyCodeto limit the extraction to relevant legal entities. Querying for all company codes at once in a large system can lead to extremely long execution times. - Workflow Task IDs: The query contains placeholders for workflow task IDs (e.g.,
[Workflow Approved Task ID]). These are unique to every SAP installation and must be configured correctly for workflow activities to be extracted. Without them, no submission, approval, or rejection events will be captured. - Prerequisites: The executing user requires extensive read authorizations to financial, system, and workflow tables. These permissions are not standard and must be specifically assigned.
a Sample Query sql
WITH JournalEntryAmountCTE AS (
SELECT
CompanyCode,
AccountingDocument,
FiscalYear,
SUM(AmountInCompanyCodeCurrency) AS AmountInLocalCurrency
FROM I_JournalEntryItem
GROUP BY CompanyCode, AccountingDocument, FiscalYear
),
JournalEntryBaseCTE AS (
SELECT
JE.CompanyCode,
JE.AccountingDocument,
JE.FiscalYear,
JE.CreatedByUser,
JE.CreationDateTime,
JE.PostingDateTime,
JE.PostingDate,
JE.AccountingDocumentType,
JE.DocumentIsParked,
JE.ReversedJournalEntry,
JE.TransactionCode,
JEA.AmountInLocalCurrency
FROM I_JournalEntry AS JE
LEFT JOIN JournalEntryAmountCTE AS JEA
ON JE.CompanyCode = JEA.CompanyCode
AND JE.AccountingDocument = JEA.AccountingDocument
AND JE.FiscalYear = JEA.FiscalYear
WHERE JE.CompanyCode IN ('[YourCompanyCode]')
AND JE.CreationDateTime BETWEEN '[StartDate]' AND '[EndDate]'
)
-- 1. Journal Entry Created
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Created' AS "ActivityName",
BJE.CreationDateTime AS "EventTime",
BJE.CreatedByUser AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
UNION ALL
-- 2. Journal Entry Parked
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Parked' AS "ActivityName",
BJE.CreationDateTime AS "EventTime",
BJE.CreatedByUser AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
WHERE BJE.DocumentIsParked = 'X'
UNION ALL
-- 3. Supporting Documentation Attached
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Supporting Documentation Attached' AS "ActivityName",
TO_TIMESTAMP(SOOD.CREDAT || ' ' || SOOD.CRETIM, 'YYYYMMDD HH24MISS') AS "EventTime",
SOOD.OWNER AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SRGBREL ON SRGBREL.INSTID_A = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
AND SRGBREL.TYPEID_A = 'BKPF'
AND SRGBREL.CATID_A = 'BO'
JOIN SOOD ON SOOD.OBJTP = SRGBREL.TYPEID_B
AND SOOD.OBJYR = SRGBREL.INSTID_B(3)
AND SOOD.OBJNO = SRGBREL.INSTID_B(5)
UNION ALL
-- 4. Journal Submitted For Review
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Submitted For Review' AS "ActivityName",
LOG.END_TS AS "EventTime",
LOG.EXEC_USER AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SWW_WI2OBJ AS WF_LINK ON WF_LINK.INSTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
AND WF_LINK.TYPEID = 'BKPF'
JOIN SWWLOGHIST AS LOG ON LOG.WI_ID = WF_LINK.WI_ID
WHERE LOG.METHOD = '[Workflow Submitted Task ID]'
UNION ALL
-- 5. Journal Entry Rejected
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Rejected' AS "ActivityName",
LOG.END_TS AS "EventTime",
LOG.EXEC_USER AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SWW_WI2OBJ AS WF_LINK ON WF_LINK.INSTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
AND WF_LINK.TYPEID = 'BKPF'
JOIN SWWLOGHIST AS LOG ON LOG.WI_ID = WF_LINK.WI_ID
WHERE LOG.METHOD = '[Workflow Rejected Task ID]'
UNION ALL
-- 6. Journal Entry Corrected (changed while parked)
SELECT DISTINCT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Corrected' AS "ActivityName",
TO_TIMESTAMP(CH.UDATE || ' ' || CH.UTIME, 'YYYYMMDD HH24MISS') AS "EventTime",
CH.USERNAME AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN CDHDR AS CH ON CH.OBJECTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
AND CH.OBJECTCLASS = 'BELEG'
WHERE BJE.DocumentIsParked = 'X'
UNION ALL
-- 7. Journal Entry Approved
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Approved' AS "ActivityName",
LOG.END_TS AS "EventTime",
LOG.EXEC_USER AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN SWW_WI2OBJ AS WF_LINK ON WF_LINK.INSTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
AND WF_LINK.TYPEID = 'BKPF'
JOIN SWWLOGHIST AS LOG ON LOG.WI_ID = WF_LINK.WI_ID
WHERE LOG.METHOD = '[Workflow Approved Task ID]'
UNION ALL
-- 8. Manual Posting Identified
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Manual Posting Identified' AS "ActivityName",
BJE.PostingDateTime AS "EventTime",
BJE.CreatedByUser AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
WHERE BJE.PostingDateTime IS NOT NULL AND BJE.TransactionCode IN ('FB01', 'F-02', 'FB50', 'FV50', 'FBB1', 'FBV1')
UNION ALL
-- 9. Journal Entry Posted
SELECT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Posted' AS "ActivityName",
BJE.PostingDateTime AS "EventTime",
BJE.CreatedByUser AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
WHERE BJE.PostingDateTime IS NOT NULL
UNION ALL
-- 10. Journal Entry Changed After Posting
SELECT DISTINCT
BJE.AccountingDocument AS "JournalEntryId",
'Journal Entry Changed After Posting' AS "ActivityName",
TO_TIMESTAMP(CH.UDATE || ' ' || CH.UTIME, 'YYYYMMDD HH24MISS') AS "EventTime",
CH.USERNAME AS "CreatedByUser",
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS BJE
JOIN CDHDR AS CH ON CH.OBJECTID = CONCAT(BJE.CompanyCode, BJE.AccountingDocument, BJE.FiscalYear)
AND CH.OBJECTCLASS = 'BELEG'
WHERE BJE.PostingDateTime IS NOT NULL AND TO_TIMESTAMP(CH.UDATE || ' ' || CH.UTIME, 'YYYYMMDD HH24MISS') > BJE.PostingDateTime
UNION ALL
-- 11. Journal Entry Cleared
SELECT
JEI.AccountingDocument AS "JournalEntryId",
'Journal Entry Cleared' AS "ActivityName",
MIN(JEI.ClearingDateTime) AS "EventTime",
BJE.CreatedByUser AS "CreatedByUser", -- Note: Clearing user is not directly available here
BJE.CompanyCode AS "CompanyCode",
BJE.AccountingDocumentType AS "JournalEntryType",
BJE.PostingDate AS "PostingDate",
BJE.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM I_JournalEntryItem AS JEI
JOIN JournalEntryBaseCTE AS BJE ON JEI.AccountingDocument = BJE.AccountingDocument
AND JEI.CompanyCode = BJE.CompanyCode
AND JEI.FiscalYear = BJE.FiscalYear
WHERE JEI.ClearingDateTime IS NOT NULL
GROUP BY JEI.AccountingDocument, BJE.CreatedByUser, BJE.CompanyCode, BJE.AccountingDocumentType, BJE.PostingDate, BJE.AmountInLocalCurrency
UNION ALL
-- 12. Journal Entry Reversal Processed
SELECT
OriginalDoc.AccountingDocument AS "JournalEntryId",
'Journal Entry Reversal Processed' AS "ActivityName",
ReversalDoc.PostingDateTime AS "EventTime",
ReversalDoc.CreatedByUser AS "CreatedByUser",
OriginalDoc.CompanyCode AS "CompanyCode",
OriginalDoc.AccountingDocumentType AS "JournalEntryType",
OriginalDoc.PostingDate AS "PostingDate",
OriginalDoc.AmountInLocalCurrency AS "AmountInLocalCurrency"
FROM JournalEntryBaseCTE AS ReversalDoc
JOIN JournalEntryBaseCTE AS OriginalDoc
ON ReversalDoc.ReversedJournalEntry = OriginalDoc.AccountingDocument
AND ReversalDoc.CompanyCode = OriginalDoc.CompanyCode
AND ReversalDoc.ReversalFiscalYear = OriginalDoc.FiscalYear
WHERE ReversalDoc.ReversedJournalEntry IS NOT NULL;Steps
- Create the ABAP Program: Access the ABAP Editor using transaction code
SE38. Enter a name for the new program, for example,Z_PM_JE_EXTRACT, and click 'Create'. Provide a suitable title, set the 'Type' to 'Executable Program', and save it as a local object or within a package. - Define the Selection Screen: In the program, define parameters and select-options that will allow users to filter the data. This should include a date range for the journal entry creation date (
P_CPUDT_FR,P_CPUDT_TO), a select-option for Company Code (SO_BUKRS), and a file path for the output on the application server (P_FPATH). - Declare Data Structures: Define an internal table structure that matches the required event log format. This structure will hold the final output. Also, declare internal tables and work areas for the SAP tables you will be selecting from, such as BKPF, ACDOCA, CDHDR, CDPOS, and various workflow tables.
- Implement Data Selection Logic: Write the core ABAP logic to retrieve data for each of the 12 required activities. Create separate subroutines (FORMs) for each activity to keep the code organized. For example, create a FORM for
get_created_events,get_parked_events,get_workflow_events, etc. - Select 'Created' and 'Posted' Events: Read from table BKPF based on the user's selection screen criteria. An entry in BKPF signifies creation. A document with status
BSTAT = ' 'is considered posted. Use the creation timestamp (CPUDT,CPUTM) as the event time. - Select 'Parked' Events: Read from table VBKPF, which stores parked document headers. The creation timestamp in this table represents the parking event.
- Select 'Workflow' Events (Submitted, Approved, Rejected): Query workflow tables such as SWW_WI2OBJ (to link a journal entry object to a workflow instance) and SWWLOGHIST or SWWIHEAD (to get the details and timing of specific steps). You will need to identify the specific workflow task IDs for submission, approval, and rejection in your system.
- Select 'Change' and 'Correction' Events: Query the change document tables CDHDR (header) and CDPOS (item) for
OBJECTCLAS = 'BELEG'. For 'Changed After Posting', filter for changes where the change timestamp is after the document's posting date. For 'Corrected', filter for changes to parked or rejected documents. - Select 'Reversal' and 'Cleared' Events: Identify reversals by finding documents where the
STBLGfield (Reversed Document No.) in BKPF is populated. The reversal event time is the creation time of the reversing document. Identify clearing events by selecting the latest clearing date (AUGDT) from table ACDOCA for the line items of a given journal entry. - Combine and Sort Data: As each activity's data is selected, append the results into the final master internal table. After all selections are complete, sort the master table by
JournalEntryIdandEventTimeto ensure chronological order for each case. - Generate the Output File: Use the
OPEN DATASET,LOOP AT... TRANSFER, andCLOSE DATASETstatements to write the contents of the sorted final internal table to the specified file path on the SAP application server. The file should be in a CSV format with a header row. - Schedule Execution: For regular extractions, use transaction code
SM36to create a background job that runs theZ_PM_JE_EXTRACTprogram on a defined schedule, for example, weekly or monthly. This automates the data export process.
Configuration
- Date Range: The selection screen should have a mandatory date range for the journal entry creation date (
CPUDT). It is recommended to extract data in manageable chunks, such as 3-6 months at a time, to ensure good performance. - Company Code (
BUKRS): This is a critical filter to limit the extraction to the specific legal entities relevant to the process mining analysis. Extracting for all company codes at once is not recommended. - Document Type (
BLART): You can add this optional filter to focus on specific types of journal entries, such as 'SA' for G/L Account Posting or 'KR' for Vendor Invoices. This can help reduce data volume and increase the relevance of the dataset. - File Path: The program requires a logical file path on the SAP application server where the output file will be written. Ensure the path is valid and that the SAP system user has the necessary write authorizations for that directory. Use transaction
AL11to manage and view server directories. - Workflow Task IDs: The logic for extracting workflow events (Submitted, Approved, Rejected) must be configured with the specific task IDs used in your organization's journal entry approval workflow. These are often custom and must be identified by a workflow consultant or developer.
- Prerequisites: The user or system account executing the program requires developer authorizations to create and run ABAP programs (
S_DEVELOP) and broad read access to financial tables (BKPF, ACDOCA), change log tables (CDHDR, CDPOS), and workflow tables (SWW*).
a Sample Query abap
REPORT Z_PM_JE_EXTRACT.
*&---------------------------------------------------------------------*
*&-- Data Structures for Event Log --*
*&---------------------------------------------------------------------*
TYPES: BEGIN OF ty_event_log,
journalentryid TYPE string,
activityname TYPE string,
eventtime TYPE string,
createdbyuser TYPE uname,
companycode TYPE bukrs,
journalentrytype TYPE blart,
postingdate TYPE budat,
amountinlocalcurrency TYPE wrbtr,
END OF ty_event_log.
*&---------------------------------------------------------------------*
*&-- Selection Screen Definition --*
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
PARAMETERS: p_erdat_fr TYPE dats OBLIGATORY DEFAULT sy-datum-30,
p_erdat_to TYPE dats OBLIGATORY DEFAULT sy-datum.
SELECT-OPTIONS: so_bukrs FOR bkpf-bukrs OBLIGATORY.
PARAMETERS: p_fpath TYPE string OBLIGATORY DEFAULT '/usr/sap/trans/tmp/je_event_log.csv'.
SELECTION-SCREEN END OF BLOCK b1.
*&---------------------------------------------------------------------*
*&-- Internal Tables --*
*&---------------------------------------------------------------------*
DATA: gt_event_log TYPE TABLE OF ty_event_log.
*&---------------------------------------------------------------------*
*&-- Main Processing Block --*
*&---------------------------------------------------------------------*
START-OF-SELECTION.
PERFORM get_created_posted_events.
PERFORM get_parked_events.
PERFORM get_attachment_events.
PERFORM get_workflow_events.
PERFORM get_change_events.
PERFORM get_cleared_events.
PERFORM get_reversal_events.
SORT gt_event_log BY journalentryid eventtime.
PERFORM write_output_file.
*&---------------------------------------------------------------------*
*&-- Subroutines for Extracting Individual Activities --*
*&---------------------------------------------------------------------*
FORM get_created_posted_events.
DATA: lt_bkpf TYPE TABLE OF bkpf,
ls_event_log TYPE ty_event_log,
lv_timestamp TYPE string.
SELECT * FROM bkpf INTO TABLE lt_bkpf
WHERE bukrs IN so_bukrs
AND cpudt BETWEEN p_erdat_fr AND p_erdat_to.
LOOP AT lt_bkpf ASSIGNING FIELD-SYMBOL(<fs_bkpf>).
CLEAR ls_event_log.
CONCATENATE <fs_bkpf>-bukrs <fs_bkpf>-belnr <fs_bkpf>-gjahr INTO ls_event_log-journalentryid.
ls_event_log-companycode = <fs_bkpf>-bukrs.
ls_event_log-journalentrytype = <fs_bkpf>-blart.
ls_event_log-postingdate = <fs_bkpf>-budat.
ls_event_log-createdbyuser = <fs_bkpf>-usnam.
" Timestamp format YYYY-MM-DDTHH:MI:SS
CONCATENATE <fs_bkpf>-cpudt(4) '-' <fs_bkpf>-cpudt+4(2) '-' <fs_bkpf>-cpudt+6(2) 'T' <fs_bkpf>-cputm(2) ':' <fs_bkpf>-cputm+2(2) ':' <fs_bkpf>-cputm+4(2) INTO lv_timestamp.
ls_event_log-eventtime = lv_timestamp.
" Activity: Journal Entry Created
ls_event_log-activityname = 'Journal Entry Created'.
SELECT SUM( hsl ) INTO ls_event_log-amountinlocalcurrency FROM acdoca WHERE belnr = <fs_bkpf>-belnr AND gjahr = <fs_bkpf>-gjahr AND bukrs = <fs_bkpf>-bukrs.
APPEND ls_event_log TO gt_event_log.
" Activity: Journal Entry Posted (if not parked)
IF <fs_bkpf>-bstat = ' '.
ls_event_log-activityname = 'Journal Entry Posted'.
APPEND ls_event_log TO gt_event_log.
" Activity: Manual Posting Identified (based on T-Code)
CASE <fs_bkpf>-tcode.
WHEN 'FB01' OR 'F-02' OR 'FB50' OR 'F-22' OR 'F-43'.
ls_event_log-activityname = 'Manual Posting Identified'.
APPEND ls_event_log TO gt_event_log.
ENDCASE.
ENDIF.
ENDLOOP.
ENDFORM.
FORM get_parked_events.
DATA: ls_event_log TYPE ty_event_log, lv_timestamp TYPE string.
SELECT * FROM vbkpf
WHERE bukrs IN so_bukrs
AND cpudt BETWEEN p_erdat_fr AND p_erdat_to.
CLEAR ls_event_log.
CONCATENATE vbkpf-bukrs vbkpf-belnr vbkpf-gjahr INTO ls_event_log-journalentryid.
CONCATENATE vbkpf-cpudt(4) '-' vbkpf-cpudt+4(2) '-' vbkpf-cpudt+6(2) 'T' vbkpf-cputm(2) ':' vbkpf-cputm+2(2) ':' vbkpf-cputm+4(2) INTO lv_timestamp.
ls_event_log-activityname = 'Journal Entry Parked'.
ls_event_log-eventtime = lv_timestamp.
ls_event_log-createdbyuser = vbkpf-usnam.
ls_event_log-companycode = vbkpf-bukrs.
ls_event_log-journalentrytype = vbkpf-blart.
ls_event_log-postingdate = vbkpf-budat.
APPEND ls_event_log TO gt_event_log.
ENDSELECT.
ENDFORM.
FORM get_attachment_events.
DATA: lt_bdocs TYPE TABLE OF srgbtbrel, ls_event_log TYPE ty_event_log, lv_timestamp TYPE string.
SELECT * FROM srgbtbrel INTO TABLE lt_bdocs
WHERE typeid_a = 'BUS2081' " Object type for Accounting Document
AND catid_a = 'BO'.
LOOP AT lt_bdocs ASSIGNING FIELD-SYMBOL(<fs_bdocs>).
CHECK <fs_bdocs>-instid_a(4) IN so_bukrs.
DATA(lv_bukrs) = <fs_bdocs>-instid_a(4).
DATA(lv_belnr) = <fs_bdocs>-instid_a+4(10).
DATA(lv_gjahr) = <fs_bdocs>-instid_a+14(4).
SELECT SINGLE cpudt, cputm, usnam, blart, budat FROM bkpf
INTO (DATA(lv_cpudt), DATA(lv_cputm), DATA(lv_usnam), DATA(lv_blart), DATA(lv_budat))
WHERE bukrs = lv_bukrs AND belnr = lv_belnr AND gjahr = lv_gjahr.
IF sy-subrc = 0 AND lv_cpudt BETWEEN p_erdat_fr AND p_erdat_to.
CLEAR ls_event_log.
CONCATENATE lv_bukrs lv_belnr lv_gjahr INTO ls_event_log-journalentryid.
" Note: Using document creation time as a proxy for attachment time.
CONCATENATE lv_cpudt(4) '-' lv_cpudt+4(2) '-' lv_cpudt+6(2) 'T' lv_cputm(2) ':' lv_cputm+2(2) ':' lv_cputm+4(2) INTO lv_timestamp.
ls_event_log-activityname = 'Supporting Documentation Attached'.
ls_event_log-eventtime = lv_timestamp.
ls_event_log-createdbyuser = lv_usnam.
ls_event_log-companycode = lv_bukrs.
ls_event_log-journalentrytype = lv_blart.
ls_event_log-postingdate = lv_budat.
APPEND ls_event_log TO gt_event_log.
ENDIF.
ENDLOOP.
ENDFORM.
FORM get_workflow_events.
" This is a simplified example. Real workflow logic can be complex.
" You must identify your specific Task IDs for these events.
DATA: ls_event_log TYPE ty_event_log, lv_timestamp TYPE string.
DATA: BEGIN OF ls_wi, wi_id TYPE sww_wiid, cr_date TYPE sww_cd, cr_time TYPE sww_ct, task TYPE sww_task, instid TYPE swo_typeid, END OF ls_wi.
SELECT h~wi_id h~cr_date h~cr_time h~wi_rh_task o~instid
FROM swwwihead AS h
JOIN sww_wi2obj AS o ON h~wi_id = o~wi_id
INTO @ls_wi
WHERE o~typeid = 'BUS2081' AND o~catid = 'BO'
AND h~cr_date BETWEEN @p_erdat_fr AND @p_erdat_to.
DATA(lv_bukrs) = ls_wi-instid(4).
DATA(lv_belnr) = ls_wi-instid+4(10).
DATA(lv_gjahr) = ls_wi-instid+14(4).
IF lv_bukrs IN so_bukrs.
CLEAR ls_event_log.
CONCATENATE lv_bukrs lv_belnr lv_gjahr INTO ls_event_log-journalentryid.
CONCATENATE ls_wi-cr_date(4) '-' ls_wi-cr_date+4(2) '-' ls_wi-cr_date+6(2) 'T' ls_wi-cr_time(2) ':' ls_wi-cr_time+2(2) ':' ls_wi-cr_time+4(2) INTO lv_timestamp.
ls_event_log-eventtime = lv_timestamp.
ls_event_log-companycode = lv_bukrs.
CASE ls_wi-task.
WHEN '[Your Submit Task ID]'. " e.g., TS20000139
ls_event_log-activityname = 'Journal Submitted For Review'.
APPEND ls_event_log TO gt_event_log.
WHEN '[Your Approve Task ID]'. " e.g., TS20000142
ls_event_log-activityname = 'Journal Entry Approved'.
APPEND ls_event_log TO gt_event_log.
WHEN '[Your Reject Task ID]'. " e.g., TS20000141
ls_event_log-activityname = 'Journal Entry Rejected'.
APPEND ls_event_log TO gt_event_log.
ENDCASE.
ENDIF.
ENDSELECT.
ENDFORM.
FORM get_change_events.
DATA: lt_cdhdr TYPE TABLE OF cdhdr, ls_event_log TYPE ty_event_log, lv_timestamp TYPE string.
SELECT * FROM cdhdr INTO TABLE lt_cdhdr
WHERE objectclas = 'BELEG'
AND udate BETWEEN p_erdat_fr AND p_erdat_to.
LOOP AT lt_cdhdr ASSIGNING FIELD-SYMBOL(<fs_cdhdr>).
DATA(lv_bukrs) = <fs_cdhdr>-objectid(4).
DATA(lv_belnr) = <fs_cdhdr>-objectid+4(10).
DATA(lv_gjahr) = <fs_cdhdr>-objectid+14(4).
IF lv_bukrs IN so_bukrs.
SELECT SINGLE bstat, budat, blart FROM bkpf
INTO (DATA(lv_bstat), DATA(lv_budat), DATA(lv_blart))
WHERE bukrs = lv_bukrs AND belnr = lv_belnr AND gjahr = lv_gjahr.
IF sy-subrc = 0.
CLEAR ls_event_log.
CONCATENATE lv_bukrs lv_belnr lv_gjahr INTO ls_event_log-journalentryid.
CONCATENATE <fs_cdhdr>-udate(4) '-' <fs_cdhdr>-udate+4(2) '-' <fs_cdhdr>-udate+6(2) 'T' <fs_cdhdr>-utime(2) ':' <fs_cdhdr>-utime+2(2) ':' <fs_cdhdr>-utime+4(2) INTO lv_timestamp.
ls_event_log-eventtime = lv_timestamp.
ls_event_log-createdbyuser = <fs_cdhdr>-username.
ls_event_log-companycode = lv_bukrs.
ls_event_log-journalentrytype = lv_blart.
ls_event_log-postingdate = lv_budat.
IF lv_bstat = ' ' AND <fs_cdhdr>-udate > lv_budat.
ls_event_log-activityname = 'Journal Entry Changed After Posting'.
APPEND ls_event_log TO gt_event_log.
ELSEIF lv_bstat <> ' '.
ls_event_log-activityname = 'Journal Entry Corrected'.
APPEND ls_event_log TO gt_event_log.
ENDIF.
ENDIF.
ENDIF.
ENDLOOP.
ENDFORM.
FORM get_cleared_events.
DATA: ls_event_log TYPE ty_event_log, lv_timestamp TYPE string.
DATA: BEGIN OF ls_clear, belnr TYPE belnr_d, gjahr TYPE gjahr, bukrs TYPE bukrs, augdt TYPE augdt, END OF ls_clear, lt_clear LIKE TABLE OF ls_clear.
SELECT belnr, gjahr, bukrs, MAX( augdt ) AS augdt FROM acdoca
INTO TABLE @lt_clear
WHERE bukrs IN @so_bukrs
AND augdt NE '00000000'
AND augdt BETWEEN @p_erdat_fr AND @p_erdat_to
GROUP BY belnr, gjahr, bukrs.
LOOP AT lt_clear INTO ls_clear.
SELECT SINGLE usnam, blart, budat FROM bkpf
INTO (DATA(lv_usnam), DATA(lv_blart), DATA(lv_budat))
WHERE bukrs = ls_clear-bukrs AND belnr = ls_clear-belnr AND gjahr = ls_clear-gjahr.
IF sy-subrc = 0.
CLEAR ls_event_log.
CONCATENATE ls_clear-bukrs ls_clear-belnr ls_clear-gjahr INTO ls_event_log-journalentryid.
CONCATENATE ls_clear-augdt(4) '-' ls_clear-augdt+4(2) '-' ls_clear-augdt+6(2) 'T12:00:00' INTO lv_timestamp. " Clearing date has no time, use midday
ls_event_log-activityname = 'Journal Entry Cleared'.
ls_event_log-eventtime = lv_timestamp.
ls_event_log-createdbyuser = lv_usnam.
ls_event_log-companycode = ls_clear-bukrs.
ls_event_log-journalentrytype = lv_blart.
ls_event_log-postingdate = lv_budat.
APPEND ls_event_log TO gt_event_log.
ENDIF.
ENDLOOP.
ENDFORM.
FORM get_reversal_events.
DATA: lt_reversals TYPE TABLE OF bkpf, ls_event_log TYPE ty_event_log, lv_timestamp TYPE string.
SELECT * FROM bkpf INTO TABLE lt_reversals
WHERE bukrs IN so_bukrs
AND cpudt BETWEEN p_erdat_fr AND p_erdat_to
AND stblg IS NOT NULL.
LOOP AT lt_reversals ASSIGNING FIELD-SYMBOL(<fs_rev>).
SELECT SINGLE usnam, blart, budat FROM bkpf
INTO (DATA(lv_usnam), DATA(lv_blart), DATA(lv_budat))
WHERE bukrs = <fs_rev>-bukrs AND belnr = <fs_rev>-stblg AND gjahr = <fs_rev>-gjahr.
IF sy-subrc = 0.
CLEAR ls_event_log.
CONCATENATE <fs_rev>-bukrs <fs_rev>-stblg <fs_rev>-gjahr INTO ls_event_log-journalentryid.
CONCATENATE <fs_rev>-cpudt(4) '-' <fs_rev>-cpudt+4(2) '-' <fs_rev>-cpudt+6(2) 'T' <fs_rev>-cputm(2) ':' <fs_rev>-cputm+2(2) ':' <fs_rev>-cputm+4(2) INTO lv_timestamp.
ls_event_log-activityname = 'Journal Entry Reversal Processed'.
ls_event_log-eventtime = lv_timestamp.
ls_event_log-createdbyuser = lv_usnam.
ls_event_log-companycode = <fs_rev>-bukrs.
ls_event_log-journalentrytype = lv_blart.
ls_event_log-postingdate = lv_budat.
APPEND ls_event_log TO gt_event_log.
ENDIF.
ENDLOOP.
ENDFORM.
FORM write_output_file.
DATA: lv_line TYPE string.
FIELD-SYMBOLS: <fs_event_log> TYPE ty_event_log.
OPEN DATASET p_fpath FOR OUTPUT IN TEXT MODE ENCODING UTF-8.
IF sy-subrc NE 0.
MESSAGE 'Error opening file.' TYPE 'E'.
RETURN.
ENDIF.
" Write Header
lv_line = 'JournalEntryId,ActivityName,EventTime,CreatedByUser,CompanyCode,JournalEntryType,PostingDate,AmountInLocalCurrency'.
TRANSFER lv_line TO p_fpath.
LOOP AT gt_event_log ASSIGNING <fs_event_log>.
CONCATENATE <fs_event_log>-journalentryid <fs_event_log>-activityname <fs_event_log>-eventtime <fs_event_log>-createdbyuser <fs_event_log>-companycode <fs_event_log>-journalentrytype <fs_event_log>-postingdate <fs_event_log>-amountinlocalcurrency
INTO lv_line SEPARATED BY ','.
TRANSFER lv_line TO p_fpath.
ENDLOOP.
CLOSE DATASET p_fpath.
WRITE: / 'File successfully written to', p_fpath.
ENDFORM.