> Continuous Process Improvement > Record to Report Journal Entry > Oracle Fusion Financials > Data Template

Your Record to Report - Journal Entry Data Template

Oracle Fusion Financials

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

Your Record to Report - Journal Entry Data Template

This data template provides a comprehensive guide for extracting and structuring your Oracle Fusion Financials Journal Entry data. It outlines the essential attributes to collect and the key activities to track, ensuring you capture all necessary information for robust process analysis. You will also find practical guidance on how to extract this data directly from your system.

Recommended attributes to collect
Key activities to track
Extraction guidance for Oracle Fusion Financials

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

Download Template

Record to Report - Journal Entry Attributes

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

5 Required 6 Recommended 11 Optional

	Name	Description
	Activity ActivityName	The name of the specific business event or task that occurred at a point in time in the journal entry process.
Description The Activity Name describes a single step within the journal entry lifecycle, such as 'Journal Entry Created' or 'Journal Entry Approved'. This data is crucial for building the process map and understanding the sequence of events. Analyzing activities allows for the identification of the process flow, including standard paths, deviations, and rework loops. By tracking different activities, we can measure the time spent in specific stages, such as approval, and identify which steps are most time-consuming or error-prone. Why it matters It defines the steps of the process, forming the backbone of the process map and enabling analysis of flow, bottlenecks, and variations. Where to get This attribute is typically derived from status changes, event logs, or audit trail tables associated with journal entry objects in the General Ledger module. Examples Journal Entry CreatedJournal Submitted For ApprovalJournal Entry ApprovedJournal Entry Posted
	Journal Entry ID JournalEntryId	The unique identifier for a single journal entry, linking all related financial transaction activities.
Description The Journal Entry ID serves as the primary case identifier, uniquely linking all activities related to a specific set of financial transactions. This allows for the tracking of the complete lifecycle of a single journal entry, from initiation to final posting, ensuring all debits and credits are accounted for. In process mining, this ID is essential for reconstructing the end-to-end journey of each journal entry. It connects disparate events like 'Journal Entry Created', 'Journal Submitted For Approval', and 'Journal Entry Posted' into a coherent process flow, enabling analysis of cycle times, bottlenecks, and process variations. Why it matters This is the fundamental key for tracking a journal entry from beginning to end, making it possible to analyze the entire process flow for each unique case. Where to get This is a primary key found in core General Ledger tables such as GL_JE_HEADERS and GL_JE_LINES. Examples JE100523JE202311001882019
	Start Time EventTime	The timestamp indicating when a specific activity or event occurred.
Description This timestamp marks the exact date and time that an activity was executed. It is the primary temporal element used in process mining to determine the sequence of events and calculate durations between them. The accuracy of the Event Time is critical for all time-based analyses, including calculating cycle times, identifying bottlenecks, and monitoring performance against service level agreements. It provides the chronological order needed to reconstruct the process flow as it happened. Why it matters This timestamp is essential for ordering events, calculating all process durations, and performing any time-based analysis. Where to get This information is typically stored in audit trail tables or as a 'Last Update Date' or 'Creation Date' on transaction tables like GL_JE_HEADERS and GL_JE_LINES for specific events. Examples 2023-10-26T10:00:00Z2023-11-15T14:35:10Z2024-01-05T09:12:00Z
	Last Data Update LastDataUpdate	The timestamp of the most recent data refresh or extraction from the source system.
Description This attribute records when the dataset was last updated. It provides context about the freshness of the data being analyzed, which is important for understanding the relevance of the insights derived. In any analysis, especially in operational dashboards, knowing the Last Data Update time is critical for users to trust the data and make informed decisions. It clearly communicates the cutoff point for the data included in the process mining model. Why it matters Indicates the freshness of the data, ensuring users understand how current the process analysis is and can trust the insights. Where to get This value is generated and stored during the data extraction and transformation process. It is typically the timestamp when the ETL/ELT job was completed. Examples 2023-11-20T08:00:00Z2023-11-21T08:00:00Z2023-11-22T08:00:00Z
	Source System SourceSystem	The system or module where the data originated.
Description This attribute identifies the source system from which the process data was extracted. In a complex IT landscape, data may come from multiple integrated systems, and this field helps differentiate them. In process analysis, knowing the source system is crucial for understanding process variations that may be driven by different system behaviors. It also helps in data validation and troubleshooting by tracing data back to its origin. Why it matters Identifies the origin of the data, which is crucial for data governance, validation, and understanding process variations across different systems. Where to get This is often a static value configured during data extraction, or it could be a field in the source tables identifying the system of entry. Examples Oracle Fusion Financials CloudOracle EBS R12Fusion GL
	Journal Amount JournalAmount	The total monetary value of the journal entry, typically the sum of debits.
Description This attribute represents the total financial value being transacted in the journal entry. The amount can be a significant factor influencing the process. For example, high-value entries might require additional approval steps or more thorough reviews. Analyzing Journal Amount allows for the segmentation of cases by financial impact. It helps answer questions like 'Do high-value entries take longer to approve?' or 'Are rejections more common for entries above a certain threshold?'. This provides crucial business context to the process flow. Why it matters Provides critical business context, allowing analysis based on financial impact and helping to identify if high-value entries follow a different process. Where to get This value is typically calculated as the sum of debits or credits from the GL_JE_LINES table for a given journal entry. Examples 5000.00125000.75750.50
	Journal Category JournalCategory	The category of the journal entry, such as 'Accrual', 'Adjustment', or 'Reclassification'.
Description Journal Category classifies entries based on their business purpose. This classification allows for more granular analysis of the process, as different categories may have distinct process flows, approval rules, or cycle times. For example, month-end adjustment journals might have a more complex and urgent process than routine accruals. Analyzing the process by category helps to uncover these variations and tailor improvements to specific types of journal entries. Why it matters Allows for segmenting the process by the business purpose of the journal, revealing different behaviors and performance for different entry types. Where to get Available in the GL_JE_HEADERS table, typically in a field named JE_CATEGORY. Examples AccrualManualAdjustmentRevaluation
	Journal Source JournalSource	The subledger or source that generated the journal entry, such as 'Payables', 'Receivables', or 'Manual'.
Description This attribute indicates the origin of the journal entry within the ERP system. Entries can be created manually in the General Ledger or generated automatically from subledgers like Accounts Payable, Accounts Receivable, or Fixed Assets. Journal Source is a key attribute for automation analysis. It helps differentiate between manual and system-generated entries, supporting the 'Automated Journal Entry Rate' KPI. Processes for entries from different sources often vary significantly in terms of complexity and efficiency. Why it matters Distinguishes between manual and automated entries, which is critical for measuring automation rates and analyzing process differences. Where to get Available in the GL_JE_HEADERS table, typically in a field named JE_SOURCE. Examples ManualPayablesAssetsReceivables
	Rejection Reason RejectionReason	A textual description or code explaining why a journal entry was rejected.
Description When a journal entry is rejected during the approval process, this attribute captures the reason for the rejection. This could be a predefined code or a free-text comment from the approver. This is one of the most important attributes for root cause analysis of process inefficiencies. By analyzing the most common rejection reasons, organizations can identify areas for improvement, such as better training for preparers, clearer guidelines, or system control enhancements. It directly supports the 'Journal Entry Rejection Rate Analysis' dashboard. Why it matters Provides direct insight into the root causes of rework and process delays, enabling targeted improvements to reduce rejection rates. Where to get This information may be stored in workflow or audit trail tables associated with the approval process, or in a notes field on the journal header. Examples Incorrect Account CombinationInsufficient Supporting DocumentationExceeds budget
	User UserName	The user who performed the activity, such as creating, approving, or posting the journal entry.
Description This attribute identifies the specific employee or system user responsible for executing an activity. It is crucial for understanding workload distribution, performance, and identifying individual bottlenecks. In analysis, UserName allows for filtering processes by user, comparing user efficiency, and investigating reasons for delays or errors. It directly supports dashboards like 'User Workload & Efficiency Across Teams' and KPIs like the 'User Journal Bottleneck Index'. Why it matters Attributes responsibility for process steps, enabling analysis of user workload, individual performance, and training needs. Where to get Found in transaction tables like GL_JE_HEADERS (e.g., CREATED_BY, LAST_UPDATED_BY) and related audit trail tables. Examples john.doesusan.smithautoprocess_user
	User Department UserDepartment	The department or team to which the user who performed the activity belongs.
Description This attribute provides organizational context by linking an activity to a specific department, such as 'Finance', 'Accounting Operations', or 'Internal Audit'. It is typically derived from user master data. Analyzing by User Department helps identify cross-departmental bottlenecks, compare team performance, and understand how process execution differs across the organization. It is valuable for resource allocation and targeted process improvement initiatives. Why it matters Provides organizational context, allowing for performance analysis by team or department and highlighting cross-functional inefficiencies. Where to get This is typically not in the transaction tables. It must be sourced by joining the UserName with a user master data table or HR system data. Examples General AccountingFinancial ReportingAccounts Payable
	Accounting Period AccountingPeriod	The fiscal period to which the journal entry is posted, such as 'Jan-24'.
Description The Accounting Period specifies the financial period in which the transaction is recognized. This is fundamental for all financial reporting and analysis. In process mining, this attribute is crucial for trend analysis. It allows for comparing process performance, such as cycle times or rejection rates, across different months or quarters. This helps in identifying seasonality effects, like increased workload during month-end close, and measuring the impact of process improvements over time. Why it matters Enables trend analysis of KPIs over time, helping to measure process improvement and identify seasonal patterns like month-end pressures. Where to get Available in the GL_JE_HEADERS table, typically in a field named PERIOD_NAME. Examples Jan-24Feb-24Mar-24
	Currency CurrencyCode	The currency code for the journal entry amount, such as USD, EUR, or GBP.
Description The Currency Code specifies the currency of the financial amounts in the journal entry. This is essential for multinational organizations that transact in multiple currencies. This attribute ensures that financial values are interpreted correctly. It allows for filtering by currency and is a necessary field when performing any analysis that involves comparing or aggregating monetary amounts across different regions. Why it matters Provides necessary context for any financial amounts, ensuring accurate interpretation and enabling analysis specific to different currencies. Where to get Available in the GL_JE_HEADERS table, typically in a field named CURRENCY_CODE. Examples USDEURGBPJPY
	End Time EndTime	The timestamp indicating when a specific activity was completed.
Description The End Time marks the completion of an activity. While Start Time logs the beginning, End Time provides the closing point, allowing for precise calculation of the processing time for that specific step. This is essential for calculating activity processing times, which is a core metric for identifying bottlenecks and measuring resource efficiency. For example, the difference between the End Time and Start Time of the 'Journal Entry Reviewed' activity gives the actual time a reviewer spent on the task. Why it matters It enables the calculation of the actual duration or processing time of individual activities, which is key to identifying efficiency issues. Where to get Similar to Start Time, this is often found in audit trail tables or can be inferred from the Start Time of the subsequent activity in the process. Examples 2023-10-26T10:15:00Z2023-11-15T14:55:10Z2024-01-05T09:22:00Z
	End-to-End Cycle Time EndToEndCycleTime	The total calculated duration from the creation of a journal entry to its final reconciliation.
Description This metric represents the total time a journal entry spends in the entire process, from the very first 'Journal Entry Created' event to the final 'Journal Entry Reconciled' event. It provides a holistic view of process performance. This is a primary KPI for measuring overall process efficiency. It is used in trend dashboards to track the impact of improvement initiatives over time and provides a baseline against which all process changes can be measured. Why it matters Provides a high-level measure of the entire process's health and efficiency, making it a key metric for executive reporting. Where to get This metric is calculated by the process mining platform as the time difference between the first and last event timestamps for each case. Examples P10DT5HP4DT12HP22D
	Is On-Time Posting IsOnTimePosting	A calculated flag that is true if the journal was posted on or before its target date.
Description This boolean attribute is derived by comparing the timestamp of the 'Journal Entry Posted' activity with the 'Target Posting Date'. If the posting occurs on or before the target, the value is 'true'; otherwise, it is 'false'. This attribute directly supports the 'On-Time Journal Posting Rate' KPI by simplifying the calculation. It allows for easy filtering and analysis of late entries to identify common causes for delays, such as specific journal categories or approvers. Why it matters Provides a clear, binary outcome for posting performance, simplifying the analysis of on-time rates and the root causes of delays. Where to get This attribute is calculated by the process mining tool. It requires the 'TargetPostingDate' attribute and the timestamp of the 'Journal Entry Posted' activity. Examples truefalse
	Is Rework IsRework	A calculated flag that is true if the journal entry has undergone a rejection and correction cycle.
Description This boolean attribute is calculated to identify cases that have experienced rework. It is typically flagged as 'true' for any activity that follows a 'Journal Entry Rejected' event, such as 'Journal Corrected and Resubmitted'. Is Rework is a powerful attribute for analysis, as it allows for easy quantification of the volume and impact of rework loops. It directly supports the 'Average Journal Rework Rate' KPI and helps in comparing the process flow and duration of reworked cases versus those that are processed correctly the first time. Why it matters Directly flags cases that have gone through inefficient rework loops, making it easy to quantify the frequency and impact of process failures. Where to get This is not available in the source system. It is calculated within the process mining tool by analyzing the sequence of activities for each case. Examples truefalse
	Journal Approval Time JournalApprovalTime	The calculated duration between journal submission and the final approval or rejection decision.
Description This metric measures the time elapsed from when a journal is submitted for approval until it is either approved or rejected. It is a critical measure of the efficiency of the approval workflow. Journal Approval Time is a direct input for the 'Average Journal Approval Time' KPI and the 'Journal Entry Approval Bottlenecks' dashboard. Analyzing this duration helps pinpoint delays in the approval chain, whether they are caused by specific approvers, departments, or journal types. Why it matters Directly measures the efficiency of the approval workflow, which is often a major source of process delays. Where to get This is a calculated metric within the process mining tool. It is the time difference between the 'Journal Submitted For Approval' and 'Journal Entry Approved' or 'Journal Entry Rejected' events. Examples P2DT4H30MPT8HP5D
	Ledger Name LedgerName	The name of the general ledger where the journal entry is recorded.
Description The Ledger Name identifies the specific ledger to which the journal entry belongs. In organizations with multiple legal entities or reporting requirements, there can be multiple ledgers, such as a primary ledger for corporate accounting and secondary ledgers for local statutory reporting. This attribute is essential for filtering and comparing processes across different legal entities or business units. It ensures that analyses are conducted within the correct organizational context, which is particularly important for financial compliance and reporting. Why it matters Enables process analysis to be filtered and compared across different legal entities or accounting frameworks, which is vital for large organizations. Where to get Found in the GL_JE_HEADERS table, typically linked via a LEDGER_ID which can be joined with GL_LEDGERS to get the name. Examples US Primary LedgerUK Statutory LedgerGlobal Consolidation Ledger
	Posting Status PostingStatus	The current posting status of the journal entry, for example, 'Unposted' or 'Posted'.
Description This attribute reflects the current state of the journal entry with respect to its posting in the general ledger. It is a key indicator of where a journal entry is in its lifecycle, especially for in-flight cases. Posting Status is crucial for the 'Real-Time Journal Entry Status Tracker' dashboard, providing a snapshot of all active entries. It helps in monitoring backlogs of unposted journals and identifying delays in the final stages of the process. Why it matters Indicates the current state of a journal, which is essential for monitoring backlogs and tracking the status of in-progress entries. Where to get Available in the GL_JE_HEADERS table, typically in a column like STATUS or POSTING_STATUS. Examples PostedUnpostedError
	Reversal Indicator ReversalIndicator	A flag that indicates if the journal entry is a reversal of another entry.
Description This boolean attribute identifies journal entries that are created to reverse a previously posted entry. Reversals are a specific type of journal entry that often follow a distinct, and sometimes problematic, process. Analyzing this indicator is key to supporting the 'Journal Reversal Process Performance' dashboard. It allows for isolating reversal processes to measure their frequency, causes, and cycle times, helping to identify underlying issues in the original entries that necessitate reversal. Why it matters Helps isolate and analyze the reversal process, which is often an indicator of errors or issues in the original transaction. Where to get This can be a specific flag on the GL_JE_HEADERS table, such as ACCRUAL_REV_FLAG, or identified through links to the original journal being reversed. Examples truefalse
	Target Posting Date TargetPostingDate	The predetermined date by which the journal entry is expected to be posted.
Description The Target Posting Date represents the deadline for posting a journal entry to the general ledger, often determined by service level agreements (SLAs) or month-end close schedules. It serves as a benchmark for measuring performance. This attribute is essential for calculating the 'On-Time Journal Posting Rate' KPI. By comparing the actual posting date to this target, the system can automatically flag entries as on-time or late, providing a clear measure of process adherence to schedules. Why it matters Defines the service level agreement or deadline for posting, enabling the calculation of on-time performance KPIs. Where to get This may not be a standard field. It might be derived from business rules based on the creation date, accounting period, or journal category. Examples 2023-10-312023-11-302024-01-05

Required Recommended Optional

Record to Report - Journal Entry Activities

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

5 Recommended 7 Optional

	Activity	Description
	Journal Entry Approved	The designated approver has formally approved the journal entry, confirming its accuracy and validity. This is the final step in the approval workflow, clearing the way for posting.
Why it matters This milestone marks the end of the approval process. The time between submission and approval is a key KPI for measuring workflow efficiency and identifying approval bottlenecks. Where to get This event is inferred from a status change in the GL_JE_HEADERS table, where the APPROVAL_STATUS_CODE is updated to 'APPROVED'. Workflow tables contain the approver's identity and timestamp. Capture Track the timestamp when APPROVAL_STATUS_CODE in GL_JE_HEADERS changes to 'APPROVED'. Event type inferred
	Journal Entry Created	This activity marks the initiation of the journal entry process. It represents the moment a user creates a new journal header and begins entering data, but before it is submitted for any review or approval.
Why it matters This is the primary start event for the process. Analyzing the time from this activity to subsequent steps helps measure initial data entry efficiency and overall process cycle time. Where to get This event is typically inferred from the creation date timestamp in the GL_JE_HEADERS table for a specific Journal Entry ID. The user who created the record is also available in this table. Capture Use the CREATION_DATE from the GL_JE_HEADERS table. Event type inferred
	Journal Entry Posted	The journal entry's financial data has been successfully recorded in the General Ledger. The debits and credits are now reflected in the account balances.
Why it matters This is a critical milestone representing the journal's entry into the official financial record. It is essential for measuring the on-time posting rate and the total time from creation to posting. Where to get This is inferred from a status change in the GL_JE_HEADERS table, where the STATUS field changes to 'P' (Posted). The GL_JE_BATCHES table also has a posting status. Capture Track the timestamp when STATUS in GL_JE_HEADERS changes to 'P'. Event type inferred
	Journal Entry Reconciled	The journal entry has been matched and cleared during a period-end account reconciliation process. This confirms that the transaction aligns with other financial data, such as bank statements.
Why it matters This activity serves as a true end-point for the journal's lifecycle. Measuring the time from posting to reconciliation is a key KPI for assessing the efficiency of the financial close process. Where to get This event is typically captured in the Oracle Financial Consolidation and Close Cloud Service (FCCS) or Account Reconciliation Cloud Service (ARCS), not the General Ledger itself. It's inferred from a status change on the reconciliation record linked to the journal. Capture Correlate journal data with reconciliation status updates from ARCS or FCCS tables. Event type inferred
	Journal Submitted For Approval	This activity occurs when the user formally submits the completed journal entry into the approval workflow. It transitions the journal from a draft or incomplete state to pending approval.
Why it matters This is a critical milestone that starts the clock for measuring approval cycle times and identifying bottlenecks. It separates the data entry phase from the review and approval phase. Where to get This event is inferred from a status change in the GL_JE_HEADERS table, specifically when the APPROVAL_STATUS_CODE changes to a value like 'REQUIRED' or 'INITIATED'. The submission date timestamp may also be recorded. Capture Track the timestamp when APPROVAL_STATUS_CODE in GL_JE_HEADERS changes to indicate submission. Event type inferred
	Journal Corrected and Resubmitted	After a journal entry is rejected, the creator makes the necessary corrections and resubmits it for approval. This activity represents the start of a new approval cycle for the same journal.
Why it matters Tracking rework is essential for understanding process inefficiency. This activity, combined with 'Journal Entry Rejected', allows for the measurement of rework time and frequency. Where to get This is a subsequent 'Journal Submitted For Approval' event for a journal that was previously in a 'REJECTED' state. It is identified by analyzing the sequence of status changes for a single Journal Entry ID. Capture Identify a submission event timestamp that occurs after a rejection event for the same case ID. Event type inferred
	Journal Entry Rejected	An approver has reviewed the journal entry and rejected it due to errors, lack of documentation, or policy violations. This action sends the journal back to the creator for correction.
Why it matters This activity is crucial for analyzing rework loops, rejection rates, and first-time-right metrics. High frequency of rejections points to issues with data quality or training. Where to get This is inferred from a status change in the GL_JE_HEADERS table when the APPROVAL_STATUS_CODE is updated to 'REJECTED'. The workflow history will log the user and timestamp for this action. Capture Track the timestamp when APPROVAL_STATUS_CODE in GL_JE_HEADERS is set to 'REJECTED'. Event type inferred
	Journal Entry Reviewed	A review step that may occur before or as part of the formal approval process. This represents a check by a peer or manager to ensure accuracy and compliance before it moves to the final approver.
Why it matters Isolating this activity helps to differentiate between preliminary review times and final approval times. It can reveal hidden bottlenecks if the review stage is informal but time consuming. Where to get This may be an explicit step in a multi-stage approval workflow, captured in workflow history tables. If informal, it is not captured. It can be inferred if a specific user action is logged before the final approval decision. Capture Analyze workflow history tables for interim approval or review steps before the final 'Approved' status. Event type inferred
	Journal Posting Initiated	The process of posting the approved journal to the General Ledger has been started. This can be an automated or manual step that queues the journal for processing by the posting program.
Why it matters This activity separates the approval from the technical posting process. Delays between approval and posting initiation can indicate scheduling issues or resource constraints in the posting engine. Where to get This can be inferred from status changes in the GL_JE_BATCHES table, or by the submission time of the posting concurrent request associated with the journal batch. Capture Identify the request submission time for the General Ledger Posting program for the specific journal batch. Event type inferred
	Journal Reversal Processed	A reversing journal entry has been created and posted to negate the financial impact of the original journal in a subsequent period. This is a common action for accruals.
Why it matters Tracking reversals helps identify types of entries that are frequently reversed and analyze the efficiency of the reversal process itself. This can indicate issues with accrual management. Where to get This is inferred by identifying a new journal entry that is explicitly linked to the original as its reversal. The GL_JE_HEADERS table has fields like REVERSAL_PERIOD and REVERSAL_FLAG to identify and link these entries. Capture Identify the creation and posting date of the new journal whose header references the original journal as being reversed. Event type inferred
	Posting Verified	A post-posting verification step where a user or system confirms that the journal was posted correctly and balances are as expected. This is often a manual control step.
Why it matters Analyzing this activity helps understand the time spent on manual controls and quality assurance after posting. It can highlight opportunities for automating verification processes. Where to get This is unlikely to be an explicit event in Oracle Fusion. It would need to be inferred from other actions, such as a user running a specific report or a custom status field being updated, which is not standard. Capture Requires custom logic, such as tracking the run time of verification reports or updates to a descriptive flexfield. Event type inferred
	Supporting Documentation Attached	Represents the action of attaching supporting documents, such as invoices or spreadsheets, to the journal entry. This is often done to provide context and evidence for auditors and approvers.
Why it matters Tracking this activity helps understand if delays are caused by missing documentation. It also provides insights into compliance and the completeness of journal entries before they enter the approval workflow. Where to get This can be difficult to track as a discrete event. It might be inferred from timestamps in attachment tables like FND_ATTACHED_DOCUMENTS, linked to the journal entry record in GL_JE_HEADERS. Capture Infer from the creation date of records in FND_ATTACHED_DOCUMENTS linked to the journal. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Oracle Fusion Financials

Steps

Navigate to the Reports and Analytics Work Area: Log in to Oracle Fusion Financials. Use the Navigator menu to go to Tools, then select Reports and Analytics. This is the central hub for creating and managing all analyses and reports.
Create a BI Publisher Data Model: Instead of starting with an OTBI Analysis, it is more effective for this type of event log extraction to create a BI Publisher Data Model directly. This allows you to use a custom SQL query with UNION ALL statements. Click the 'Browse Catalog' button, navigate to a suitable folder, and choose Create > Data Model.
Define the SQL Query Dataset: In the Data Model editor, click the '+' icon to add a new Data Set and select 'SQL Query'. Give the dataset a descriptive name, for example, 'JournalEventLog'.
Enter the SQL Query: Select 'Oracle BI EE' as the Data Source. Copy the complete SQL script from the Query section below and paste it into the SQL Query text area. The query is designed to pull all required activities and attributes into a single unified structure.
Define Parameters: The provided query uses parameters like :start_date, :end_date, and :ledger_name. The data model interface will automatically detect these. You must configure them as parameters, setting appropriate data types (Date or Text) and default values if needed. This allows for flexible report execution.
Create a Report from the Data Model: After saving the data model, navigate back to the catalog. Click Create > Report. In the report creation wizard, select 'Use Existing Data Model' and locate the data model you just created.
Configure the Report Layout: In the layout editor, choose the 'Table' layout. It is crucial to drag and drop all columns from the data model into the table to ensure they are included in the export. There is no need for complex formatting.
Set Default Output to CSV: Go to the 'View a List' link in the report editor. In the 'Output Formats' section, ensure 'CSV' is selected. It is recommended to uncheck all other formats to simplify scheduling. Save the report.
Schedule the Report for Automated Extraction: Find your saved report in the catalog and click 'More', then 'Schedule'. Create a new schedule job. Configure the frequency, for example, daily or weekly.
Configure Export and Delivery: In the 'Output' tab of the scheduling job, specify the output details. Select your report and choose 'CSV' as the format. In the 'Destination' tab, you can configure the delivery of the file to a content server location (UCM) or an external SFTP server for automated retrieval.
Retrieve and Prepare the Data: Once the scheduled job runs, retrieve the generated CSV file from your configured destination. Ensure the column headers match the requirements for ProcessMind ingestion: JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, etc. The file is now ready for upload.

Configuration

Data Source: The query should be run against the 'Oracle BI EE' data source within BI Publisher, which connects to the underlying transactional database views that support OTBI.
Key Subject Areas (for reference): The query logic is based on data primarily found in the 'General Ledger - Journals Real Time' subject area. It also logically joins data that would be found in 'HCM - Workforce Management - Worker Assignment Real Time' for user details and potentially workflow subject areas for approval history.
Date Range Filtering: It is critical to filter the data to a manageable period. The query includes :start_date and :end_date parameters. We recommend extracting 3 to 6 months of data for an initial analysis. The filter is applied to the journal creation date.
Ledger Filtering: Use the :ledger_name parameter to limit the extraction to a specific primary ledger. Extracting for all ledgers at once can result in extremely large files and performance issues.
Prerequisites: The user creating and scheduling the report must have appropriate BI roles, such as 'BI Administrator' or 'BI Author'. Additionally, the user's data security profile must grant access to the specific ledgers and business units being queried.
Scheduling: For optimal performance and to avoid impacting system users, schedule the extraction job to run during off-peak hours, such as overnight.

a Sample Query config

WITH JournalHeaders AS (
    SELECT
        gjh.je_header_id,
        gjh.ledger_id,
        gl.name AS ledger_name,
        gjb.je_batch_id,
        gjh.doc_sequence_value,
        gjh.name AS journal_name,
        gjc.user_je_category_name AS journal_category,
        gjs.user_je_source_name AS journal_source,
        gjh.default_effective_date,
        gjh.creation_date,
        gjh.created_by,
        gjh.last_update_date,
        gjh.last_updated_by,
        gjh.posted_date,
        gjb.approval_status_code AS batch_approval_status,
        gjh.accrual_rev_period_name,
        gjh.accrual_rev_je_header_id,
        (SELECT SUM(accounted_dr) FROM gl_je_lines WHERE je_header_id = gjh.je_header_id) AS journal_amount
    FROM
        gl_je_headers gjh
    JOIN
        gl_je_batches gjb ON gjh.je_batch_id = gjb.je_batch_id
    JOIN
        gl_ledgers gl ON gjh.ledger_id = gl.ledger_id
    JOIN
        gl_je_categories_tl gjc ON gjh.je_category = gjc.je_category_name AND gjc.language = USERENV('LANG')
    JOIN
        gl_je_sources_tl gjs ON gjh.je_source = gjs.je_source_name AND gjs.language = USERENV('LANG')
    WHERE
        gjh.creation_date BETWEEN TO_DATE(:start_date, 'YYYY-MM-DD') AND TO_DATE(:end_date, 'YYYY-MM-DD')
        AND gl.name = :ledger_name
)
-- 1. Journal Entry Created
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Entry Created' AS "ActivityName",
    jh.creation_date AS "EventTime",
    u.username AS "UserName",
    papf.person_number AS "UserDepartment", -- Placeholder: Configure based on your department tracking
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
LEFT JOIN
    per_users u ON jh.created_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 2. Supporting Documentation Attached
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Supporting Documentation Attached' AS "ActivityName",
    fad.creation_date AS "EventTime",
    u.username AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    fnd_attached_documents fad
JOIN
    JournalHeaders jh ON fad.pk1_value = TO_CHAR(jh.je_header_id) AND fad.entity_name = 'GL_JE_HEADERS'
LEFT JOIN
    per_users u ON fad.created_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(fad.creation_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 3. Journal Submitted For Approval
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Submitted For Approval' AS "ActivityName",
    wfa.created_date AS "EventTime",
    wfa.updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    fun_all_wf_transactions wft ON wft.transaction_id = jh.je_batch_id AND wft.application_id = 101 AND wft.transaction_class = 'Journal Batch'
JOIN
    fun_all_wf_approvals wfa ON wft.transaction_id = wfa.transaction_id AND wfa.response = 'SUBMITTED'
LEFT JOIN
    per_users u ON wfa.updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(wfa.created_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 4. Journal Entry Reviewed & 7. Journal Entry Approved
SELECT
    jh.je_header_id AS "JournalEntryId",
    CASE
        WHEN wfa.approval_level < (SELECT MAX(approval_level) FROM fun_all_wf_approvals WHERE transaction_id = wfa.transaction_id) THEN 'Journal Entry Reviewed'
        ELSE 'Journal Entry Approved'
    END AS "ActivityName",
    wfa.created_date AS "EventTime",
    wfa.updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    fun_all_wf_approvals wfa ON wfa.transaction_id = jh.je_batch_id AND wfa.response = 'APPROVED'
LEFT JOIN
    per_users u ON wfa.updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(wfa.created_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 5. Journal Entry Rejected
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Entry Rejected' AS "ActivityName",
    wfa.created_date AS "EventTime",
    wfa.updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    wfa.reason AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    fun_all_wf_approvals wfa ON wfa.transaction_id = jh.je_batch_id AND wfa.response = 'REJECTED'
LEFT JOIN
    per_users u ON wfa.updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(wfa.created_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 6. Journal Corrected and Resubmitted
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Corrected and Resubmitted' AS "ActivityName",
    resubmit_events.created_date AS "EventTime",
    resubmit_events.updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN (
    SELECT wfa.transaction_id, wfa.created_date, wfa.updated_by
    FROM fun_all_wf_approvals wfa
    WHERE wfa.response = 'SUBMITTED'
    AND EXISTS (
        SELECT 1
        FROM fun_all_wf_approvals prev_wfa
        WHERE prev_wfa.transaction_id = wfa.transaction_id
        AND prev_wfa.response = 'REJECTED'
        AND prev_wfa.created_date < wfa.created_date
    )
) resubmit_events ON jh.je_batch_id = resubmit_events.transaction_id
LEFT JOIN
    per_users u ON resubmit_events.updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(resubmit_events.created_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 8. Journal Posting Initiated
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Posting Initiated' AS "ActivityName",
    gjb.last_update_date AS "EventTime",
    gjb.last_updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    gl_je_batches gjb ON jh.je_batch_id = gjb.je_batch_id
WHERE
    gjb.status = 'I' -- 'I' typically means Selected for Posting
    AND jh.posted_date IS NOT NULL
LEFT JOIN
    per_users u ON gjb.last_updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(gjb.last_update_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 9. Journal Entry Posted
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Entry Posted' AS "ActivityName",
    jh.posted_date AS "EventTime",
    jh.last_updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
LEFT JOIN
    per_users u ON jh.last_updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(jh.posted_date) BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
    jh.posted_date IS NOT NULL

UNION ALL

-- 10. Posting Verified - This is a placeholder as it is typically a manual control step not logged in a standard table.
-- This part of the query will return no rows unless you have a custom solution, such as a Descriptive Flexfield (DFF), to track this.
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Posting Verified' AS "ActivityName",
    jh.last_update_date AS "EventTime", -- Placeholder timestamp
    jh.last_updated_by AS "UserName", -- Placeholder user
    NULL AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
WHERE
    1 = 2 -- This condition ensures no rows are returned. Replace with logic for your custom DFF or tracking field.

UNION ALL

-- 11. Journal Entry Reconciled - This depends on integration with Oracle Account Reconciliation (ARCS).
-- This query assumes reconciliation status is written back to GL_RECONCILIATION_REFERENCES.
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Entry Reconciled' AS "ActivityName",
    grr.last_update_date AS "EventTime",
    grr.last_updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    gl_je_lines gjl ON jh.je_header_id = gjl.je_header_id
JOIN
    gl_reconciliation_references grr ON gjl.je_header_id = grr.je_header_id AND gjl.je_line_num = grr.je_line_num
WHERE
    grr.status = 'R' -- Assuming 'R' means Reconciled. Verify based on your ARCS configuration.
LEFT JOIN
    per_users u ON grr.last_updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(grr.last_update_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 12. Journal Reversal Processed
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Reversal Processed' AS "ActivityName",
    rev_jh.creation_date AS "EventTime",
    rev_jh.created_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    gl_je_headers rev_jh ON jh.je_header_id = rev_jh.accrual_rev_je_header_id
LEFT JOIN
    per_users u ON rev_jh.created_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(rev_jh.creation_date) BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
    jh.accrual_rev_je_header_id IS NOT NULL

Steps

Navigate to BI Publisher: Log in to Oracle Fusion Financials. Use the Navigator menu to go to Tools, then select Reports and Analytics. This will open the Business Intelligence catalog.
Create a New Data Model: In the BI catalog, click the Create button and select Data Model. This will open the data model editor.
Define the SQL Query Dataset: In the data model editor, click the plus icon on the Diagram tab and select SQL Query. In the new dialog box, give the dataset a name, for example, ProcessMind_JE_EventLog. Select the appropriate Data Source, which is typically FUSIONDB. Paste the complete SQL query provided into the SQL Query text area.
Define Parameters: The query uses parameters for the date range (p_start_date and p_end_date). Oracle BI Publisher will automatically detect these. You should configure them as Text type parameters and set user-friendly labels like 'Start Date (YYYY-MM-DD)' and 'End Date (YYYY-MM-DD)'.
Save the Data Model: After pasting the query and defining parameters, click OK and save the data model in a suitable folder, for example, Shared Folders/Custom/ProcessMining.
Create the Report: Click the Create Report button in the upper right corner of the data model editor. The report creation wizard will launch.
Configure the Report Layout: In the wizard, select the option to use the current data model. Choose the 'Table' layout. Drag and drop all columns from the data source into the table layout area. Uncheck the option 'Show Grand Totals Row'.
Save the Report: Save the report in the same folder as the data model. Give it a descriptive name like 'Journal Entry Event Log Report'.
Set Default Output Format: Edit the report and navigate to the 'Viewing' tab. Under 'Output Formats', ensure CSV is selected and preferably set it as the default format. This ensures the output is compatible for direct use.
Run and Export: Open the report and you will be prompted to enter the start and end date parameters. Enter the desired date range and click Apply. Once the report runs, click the Export button and choose the CSV format to download the event log file.
Prepare for Upload: Open the downloaded CSV file. Verify that the column headers match the expected attribute names (JournalEntryId, ActivityName, EventTime, etc.) and that the data is formatted correctly before uploading to the ProcessMind platform.

Configuration

Prerequisites: The user running the report must have a role with the necessary data security privileges to access General Ledger data, such as General Accountant or a custom BI role. Access to the BI Publisher tool is also required.
Data Source: The BI Publisher Data Model should be configured to use the FUSIONDB application database connection.
Date Range Parameters: The query is designed with p_start_date and p_end_date parameters to control the data extraction window. It is recommended to start with a period of 3 to 6 months of data to ensure performance.
Report Filters: For large organizations, it is highly recommended to add filters directly into the WHERE clause of the query's JournalHeaders common table expression. Filtering by LEDGER_ID or LEGAL_ENTITY_ID is essential to manage data volume.
Performance Considerations: Executing this query against a large production database can consume significant resources. Schedule the report to run during off-peak hours to minimize impact on system performance.
Output Format: The report must be configured to output as a CSV file. This provides a clean, structured format for ingestion into process mining tools.

a Sample Query sql

WITH JournalHeaders AS (
    SELECT
        gjh.JE_HEADER_ID,
        gjh.NAME AS JOURNAL_NAME,
        gjh.LEDGER_ID,
        gjh.JE_CATEGORY,
        gjh.JE_SOURCE,
        gjh.STATUS,
        gjh.APPROVAL_STATUS_CODE,
        gjh.CREATED_BY,
        gjh.CREATION_DATE,
        gjh.LAST_UPDATED_BY,
        gjh.LAST_UPDATE_DATE,
        gjh.POSTED_DATE,
        gjh.ACCRUAL_REV_JE_HEADER_ID,
        gjh.ACCRUAL_REV_PERIOD_NAME,
        gjs.USER_JE_SOURCE_NAME,
        gjc.USER_JE_CATEGORY_NAME,
        (SELECT SUM(NVL(gjl.ACCOUNTED_DR, 0)) FROM GL_JE_LINES gjl WHERE gjl.JE_HEADER_ID = gjh.JE_HEADER_ID) AS JOURNAL_AMOUNT
    FROM
        GL_JE_HEADERS gjh
    JOIN
        GL_JE_SOURCES_TL gjs ON gjh.JE_SOURCE = gjs.JE_SOURCE_NAME AND gjs.LANGUAGE = USERENV('LANG')
    JOIN
        GL_JE_CATEGORIES_TL gjc ON gjh.JE_CATEGORY = gjc.JE_CATEGORY_NAME AND gjc.LANGUAGE = USERENV('LANG')
    WHERE
        gjh.CREATION_DATE BETWEEN TO_TIMESTAMP(:p_start_date || ' 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF') AND TO_TIMESTAMP(:p_end_date || ' 23:59:59.999', 'YYYY-MM-DD HH24:MI:SS.FF')
        AND gjh.ACTUAL_FLAG = 'A'
        -- Recommended: Add a filter for your specific ledger(s) to improve performance
        -- AND gjh.LEDGER_ID IN ([Your Ledger ID])
),
UserDepartment AS (
    -- This subquery is an example. The join logic to find a user's department may need to be adjusted
    -- based on your specific Oracle HCM configuration (e.g., using different assignment types or tables).
    SELECT 
        pu.USERNAME, 
        org.NAME as Department
    FROM PER_USERS pu
    LEFT JOIN PER_ALL_ASSIGNMENTS_F asg ON pu.PERSON_ID = asg.PERSON_ID 
        AND asg.ASSIGNMENT_TYPE IN ('E', 'C') 
        AND asg.PRIMARY_FLAG = 'Y'
        AND SYSDATE BETWEEN asg.EFFECTIVE_START_DATE AND asg.EFFECTIVE_END_DATE
    LEFT JOIN HR_ALL_ORGANIZATION_UNITS_F_VL org ON asg.ORGANIZATION_ID = org.ORGANIZATION_ID
        AND SYSDATE BETWEEN org.EFFECTIVE_START_DATE AND org.EFFECTIVE_END_DATE
    WHERE pu.USERNAME IS NOT NULL
)
-- 1. Journal Entry Created
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Entry Created' AS "ActivityName",
    jh.CREATION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    jh.CREATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.CREATED_BY = ud.USERNAME

UNION ALL

-- 2. Supporting Documentation Attached
SELECT
    TO_NUMBER(fad.PK1_VALUE) AS "JournalEntryId",
    'Supporting Documentation Attached' AS "ActivityName",
    fad.CREATION_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    fad.CREATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    FND_ATTACHED_DOCUMENTS fad
JOIN
    JournalHeaders jh ON fad.PK1_VALUE = TO_CHAR(jh.JE_HEADER_ID)
LEFT JOIN UserDepartment ud ON fad.CREATED_BY = ud.USERNAME
WHERE
    fad.ENTITY_NAME = 'GL_JE_HEADERS'

UNION ALL

-- 3. Journal Submitted For Approval
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Submitted For Approval' AS "ActivityName",
    jh.LAST_UPDATE_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    jh.LAST_UPDATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.LAST_UPDATED_BY = ud.USERNAME
WHERE
    jh.APPROVAL_STATUS_CODE IN ('REQUIRED', 'INITIATED')

UNION ALL

-- 4. Journal Entry Reviewed (Placeholder)
-- This activity requires custom logic based on your approval workflow configuration.
-- The query below is a placeholder and may not return any data without customization.
-- It assumes a review happens if the approval status is updated but not yet approved or rejected.
SELECT
    NULL AS "JournalEntryId",
    NULL AS "ActivityName",
    NULL AS "EventTime",
    NULL AS "SourceSystem",
    NULL AS "LastDataUpdate",
    NULL AS "UserName",
    NULL AS "UserDepartment",
    NULL AS "JournalCategory",
    NULL AS "JournalSource",
    NULL AS "JournalAmount",
    NULL AS "RejectionReason"
FROM DUAL WHERE 1=0

UNION ALL

-- 5. Journal Entry Rejected
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Entry Rejected' AS "ActivityName",
    jh.LAST_UPDATE_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    jh.LAST_UPDATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    -- Rejection Reason is not a standard field. This might be found in workflow tables.
    '[Configure based on your system]' AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.LAST_UPDATED_BY = ud.USERNAME
WHERE
    jh.APPROVAL_STATUS_CODE = 'REJECTED'

UNION ALL

-- 6. Journal Corrected and Resubmitted
-- This logic assumes a resubmission is an update that occurs after a rejection.
-- A more precise method would involve analyzing workflow history, which is complex.
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Corrected and Resubmitted' AS "ActivityName",
    jh.LAST_UPDATE_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    jh.LAST_UPDATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.LAST_UPDATED_BY = ud.USERNAME
WHERE
    jh.APPROVAL_STATUS_CODE IN ('REQUIRED', 'INITIATED')
    AND jh.JE_HEADER_ID IN (SELECT h.JE_HEADER_ID FROM GL_JE_HEADERS h WHERE h.APPROVAL_STATUS_CODE = 'REJECTED')

UNION ALL

-- 7. Journal Entry Approved
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Entry Approved' AS "ActivityName",
    jh.LAST_UPDATE_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    jh.LAST_UPDATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.LAST_UPDATED_BY = ud.USERNAME
WHERE
    jh.APPROVAL_STATUS_CODE = 'APPROVED'

UNION ALL

-- 8. Journal Posting Initiated
-- The exact initiation time is not stored. We infer it as 5 minutes before the final posted date.
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Posting Initiated' AS "ActivityName",
    (jh.POSTED_DATE - INTERVAL '5' MINUTE) AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    -- Posting is often a system process, so user might not be available.
    jh.LAST_UPDATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.LAST_UPDATED_BY = ud.USERNAME
WHERE
    jh.STATUS = 'P' AND jh.POSTED_DATE IS NOT NULL

UNION ALL

-- 9. Journal Entry Posted
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Entry Posted' AS "ActivityName",
    jh.POSTED_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    jh.LAST_UPDATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
LEFT JOIN UserDepartment ud ON jh.LAST_UPDATED_BY = ud.USERNAME
WHERE
    jh.STATUS = 'P' AND jh.POSTED_DATE IS NOT NULL

UNION ALL

-- 10. Posting Verified (Placeholder)
-- This is a manual control activity not tracked by default. This query must be customized.
-- It assumes a descriptive flexfield named 'POSTING_VERIFIED_DATE' on GL_JE_HEADERS exists.
SELECT
    NULL AS "JournalEntryId",
    NULL AS "ActivityName",
    NULL AS "EventTime",
    NULL AS "SourceSystem",
    NULL AS "LastDataUpdate",
    NULL AS "UserName",
    NULL AS "UserDepartment",
    NULL AS "JournalCategory",
    NULL AS "JournalSource",
    NULL AS "JournalAmount",
    NULL AS "RejectionReason"
FROM DUAL WHERE 1=0

UNION ALL

-- 11. Journal Entry Reconciled (Placeholder)
-- Reconciliation is typically done in other modules. This query must be customized.
-- It assumes a custom table or field indicates when a journal is reconciled.
SELECT
    NULL AS "JournalEntryId",
    NULL AS "ActivityName",
    NULL AS "EventTime",
    NULL AS "SourceSystem",
    NULL AS "LastDataUpdate",
    NULL AS "UserName",
    NULL AS "UserDepartment",
    NULL AS "JournalCategory",
    NULL AS "JournalSource",
    NULL AS "JournalAmount",
    NULL AS "RejectionReason"
FROM DUAL WHERE 1=0

UNION ALL

-- 12. Journal Reversal Processed
SELECT
    jh.JE_HEADER_ID AS "JournalEntryId",
    'Journal Reversal Processed' AS "ActivityName",
    rev_jh.POSTED_DATE AS "EventTime",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate",
    rev_jh.CREATED_BY AS "UserName",
    ud.Department AS "UserDepartment",
    jh.USER_JE_CATEGORY_NAME AS "JournalCategory",
    jh.USER_JE_SOURCE_NAME AS "JournalSource",
    jh.JOURNAL_AMOUNT AS "JournalAmount",
    NULL AS "RejectionReason"
FROM
    JournalHeaders jh
JOIN
    GL_JE_HEADERS rev_jh ON jh.ACCRUAL_REV_JE_HEADER_ID = rev_jh.JE_HEADER_ID
LEFT JOIN UserDepartment ud ON rev_jh.CREATED_BY = ud.USERNAME
WHERE
    jh.ACCRUAL_REV_JE_HEADER_ID IS NOT NULL
    AND rev_jh.STATUS = 'P'
ORDER BY
    "JournalEntryId", "EventTime"

Steps

Prerequisites: Ensure you have Python 3 installed along with the requests and pandas libraries. You will need a user account for Oracle Fusion Financials with the necessary permissions to access the General Ledger Journals REST API. Obtain your Oracle Fusion instance URL, username, and password.
Script Setup: Create a new Python script file. Begin by importing the necessary libraries: requests, json, pandas, and datetime.
Configuration: In the script, define variables for your Oracle Fusion credentials and the base URL. Also, set parameters for the data extraction, such as the start and end dates for the query.
Authentication: The script uses Basic Authentication. The requests library handles the encoding of the username and password in the request headers.
Define API Call Function: Create a function to handle the API requests. This function should manage pagination by using the offset and limit parameters. It will loop, making successive API calls, until all journal records within the specified date range have been retrieved.
Construct API Query: Use the q parameter in the API request to filter journals based on the LastUpdateDate. This allows for targeted extraction of data within a specific timeframe, for example LastUpdateDate between '2023-01-01' and '2023-03-31'.
Initiate Data Retrieval: Call the function to start fetching data from the .../fscmRestApi/resources/11.13.18.05/journals endpoint. The function will return a complete list of journal entries.
Process and Transform Data: Iterate through each journal entry returned by the API. For each journal, generate multiple event rows corresponding to the different business activities (Journal Entry Created, Journal Entry Approved, etc.). Use the date fields and status codes from the API response (CreationDate, PostedDate, ApprovalStatus, etc.) to create these distinct events.
Structure Event Log: As each event row is generated, store it as a dictionary in a list. This list will become the raw data for your event log.
Create DataFrame: Once all journals have been processed, convert the list of event dictionaries into a Pandas DataFrame for easier manipulation.
Finalize Columns: Select and rename the DataFrame columns to match the required schema for ProcessMind: JournalEntryId, ActivityName, EventTime, UserName, etc.
Export to CSV: Save the final DataFrame to a CSV file. This file can then be uploaded directly to ProcessMind for analysis.

Configuration

Credentials: A valid Oracle Fusion username and password are required for Basic Authentication.
Instance URL: The base URL of your Oracle Fusion Financials instance is needed to construct the API endpoint (e.g., https://[your-instance].fa.us2.oraclecloud.com).
API Endpoint: The script targets the fscmRestApi/resources/11.13.18.05/journals endpoint for fetching General Ledger data.
Date Range Filter: The script should be configured with a start_date and end_date to limit the scope of the extraction. For an initial analysis, a period of 3 to 6 months is recommended. For ongoing use, configure it for daily or weekly incremental loads.
Pagination Limit: The limit parameter controls how many records are fetched per API call. A value between 200 and 500 is typically efficient, balancing the number of API calls with the size of each response.
Required Oracle Roles: The user account must have a role with the necessary privileges to read journal data via the REST API, such as General Accountant or a custom role with equivalent permissions.

a Sample Query python

import requests
import pandas as pd
from datetime import datetime, timezone

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># Replace with your Oracle Fusion instance details
FUSION_URL = class="hl-string">"https://[your-instance].fa.us2.oraclecloud.com"
USERNAME = class="hl-string">"[your-username]"
PASSWORD = class="hl-string">"[your-password]"

class=class="hl-string">"hl-comment"># --- Parameters ---
class=class="hl-string">"hl-comment"># Set the date range for the extraction (YYYY-MM-DD format)
START_DATE = class="hl-string">"2023-01-01"
END_DATE = class="hl-string">"2023-06-30"
PAGE_LIMIT = 500 class=class="hl-string">"hl-comment"># Number of records per API call

def get_all_journals(base_url, user, password, start_date, end_date):
    class="hl-string">"""Fetches all journals from the Oracle Fusion REST API within a date range, handling pagination."""
    all_journals = []
    offset = 0
    has_more = True
    
    api_url = fclass="hl-string">"{base_url}/fscmRestApi/resources/11.13.18.05/journals"
    
    headers = {
        class="hl-string">'Content-Type': class="hl-string">'application/vnd.oracle.adf.resourcecollection+json'
    }
    
    query_filter = fclass="hl-string">"LastUpdateDate between '{start_date}' and '{end_date}'"
    
    while has_more:
        params = {
            class="hl-string">'q': query_filter,
            class="hl-string">'onlyData': class="hl-string">'true',
            class="hl-string">'limit': PAGE_LIMIT,
            class="hl-string">'offset': offset,
            class="hl-string">'fields': class="hl-string">'JeHeaderId,CreatedBy,CreationDate,LastUpdatedBy,LastUpdateDate,PostedDate,ApprovalStatus,ActualFlag,DefaultEffectiveDate,Description,JeCategory,JeSource,PeriodName,TotalDebit,ReversalDate,ReversalPeriodName,ExternalReference,attachments'
        }
        
        print(fclass="hl-string">"Fetching records from offset {offset}...")
        try:
            response = requests.get(api_url, auth=(user, password), headers=headers, params=params)
            response.raise_for_status() class=class="hl-string">"hl-comment"># Raises an HTTPError for bad responses (4xx or 5xx)
            data = response.json()
            
            journals = data.get(class="hl-string">'items', [])
            all_journals.extend(journals)
            
            has_more = data.get(class="hl-string">'hasMore', False)
            offset += PAGE_LIMIT

        except requests.exceptions.RequestException as e:
            print(fclass="hl-string">"An error occurred: {e}")
            has_more = False

    return all_journals

def transform_to_event_log(journals):
    class="hl-string">"""Transforms the raw journal data into a structured event log."""
    event_log = []
    extraction_timestamp = datetime.now(timezone.utc).isoformat()

    for jrnl in journals:
        journal_id = jrnl.get(class="hl-string">'JeHeaderId')
        created_by = jrnl.get(class="hl-string">'CreatedBy')
        last_updated_by = jrnl.get(class="hl-string">'LastUpdatedBy')
        approval_status = jrnl.get(class="hl-string">'ApprovalStatus')
        rejection_reason_text = class="hl-string">"Rejected per workflow comments" if approval_status == class="hl-string">'REJECTED' else None

        class=class="hl-string">"hl-comment"># 1. Journal Entry Created
        if jrnl.get(class="hl-string">'CreationDate'):
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id,
                class="hl-string">'ActivityName': class="hl-string">'Journal Entry Created',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'CreationDate'),
                class="hl-string">'UserName': created_by,
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]',
                class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'),
                class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'),
                class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials',
                class="hl-string">'LastDataUpdate': extraction_timestamp
            })

        class=class="hl-string">"hl-comment"># 2. Supporting Documentation Attached (Approximation)
        if jrnl.get(class="hl-string">'attachments') and len(jrnl[class="hl-string">'attachments']) > 0:
             event_log.append({
                class="hl-string">'JournalEntryId': journal_id,
                class="hl-string">'ActivityName': class="hl-string">'Supporting Documentation Attached',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'LastUpdateDate'), class=class="hl-string">"hl-comment"># Approximation: uses last update date
                class="hl-string">'UserName': last_updated_by, 
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]',
                class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'),
                class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'),
                class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials',
                class="hl-string">'LastDataUpdate': extraction_timestamp
            })

        class=class="hl-string">"hl-comment"># 3, 4, 5, 6, 7: Approval Workflow Events (Inferred from final status)
        class=class="hl-string">"hl-comment"># Note: This block infers events. For a more detailed history, workflow-specific APIs would be needed.
        if approval_status in [class="hl-string">'IN_PROCESS', class="hl-string">'REQUIRED']:
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Submitted For Approval',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'LastUpdateDate'), class="hl-string">'UserName': last_updated_by,
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })
        elif approval_status == class="hl-string">'REJECTED':
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Entry Rejected',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'LastUpdateDate'), class="hl-string">'UserName': last_updated_by, class=class="hl-string">"hl-comment"># Approver is often the last updater
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': rejection_reason_text,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })
            class=class="hl-string">"hl-comment"># A resubmission is assumed if a rejected journal is updated again
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Corrected and Resubmitted',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'LastUpdateDate'), class="hl-string">'UserName': created_by, class=class="hl-string">"hl-comment"># Typically the creator corrects it
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })
        elif approval_status == class="hl-string">'APPROVED':
            class=class="hl-string">"hl-comment"># class="hl-string">'Journal Entry Reviewed' is often an implicit part of approval
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Entry Reviewed',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'LastUpdateDate'), class="hl-string">'UserName': last_updated_by,
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Entry Approved',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'LastUpdateDate'), class="hl-string">'UserName': last_updated_by,
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })

        class=class="hl-string">"hl-comment"># 8, 9: Posting Events
        if jrnl.get(class="hl-string">'PostedDate'):
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Posting Initiated',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'PostedDate'), class=class="hl-string">"hl-comment"># Assuming initiation is same as posted time
                class="hl-string">'UserName': class="hl-string">'System', class=class="hl-string">"hl-comment"># Posting is often a system process
                class="hl-string">'UserDepartment': class="hl-string">'Finance Operations', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id, class="hl-string">'ActivityName': class="hl-string">'Journal Entry Posted',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'PostedDate'),
                class="hl-string">'UserName': class="hl-string">'System',
                class="hl-string">'UserDepartment': class="hl-string">'Finance Operations', class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'), class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'), class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials', class="hl-string">'LastDataUpdate': extraction_timestamp
            })
        
        class=class="hl-string">"hl-comment"># 12. Journal Reversal Processed
        if jrnl.get(class="hl-string">'ReversalDate'):
            event_log.append({
                class="hl-string">'JournalEntryId': journal_id,
                class="hl-string">'ActivityName': class="hl-string">'Journal Reversal Processed',
                class="hl-string">'EventTime': jrnl.get(class="hl-string">'ReversalDate'),
                class="hl-string">'UserName': last_updated_by,
                class="hl-string">'UserDepartment': class="hl-string">'[Configure based on your system]',
                class="hl-string">'JournalCategory': jrnl.get(class="hl-string">'JeCategory'),
                class="hl-string">'JournalSource': jrnl.get(class="hl-string">'JeSource'),
                class="hl-string">'JournalAmount': jrnl.get(class="hl-string">'TotalDebit'),
                class="hl-string">'RejectionReason': None,
                class="hl-string">'SourceSystem': class="hl-string">'Oracle Fusion Financials',
                class="hl-string">'LastDataUpdate': extraction_timestamp
            })

        class=class="hl-string">"hl-comment"># Activities not directly available from this API, added as placeholders for potential enrichment
        class=class="hl-string">"hl-comment"># Posting Verified, Journal Entry Reconciled

    return pd.DataFrame(event_log)

class=class="hl-string">"hl-comment"># --- Main Execution ---
if __name__ == class="hl-string">"__main__":
    print(class="hl-string">"Starting Oracle Fusion Journal Entry extraction...")
    raw_journals = get_all_journals(FUSION_URL, USERNAME, PASSWORD, START_DATE, END_DATE)
    
    if raw_journals:
        print(fclass="hl-string">"Successfully extracted {len(raw_journals)} journal records.")
        event_log_df = transform_to_event_log(raw_journals)
        
        if not event_log_df.empty:
            class=class="hl-string">"hl-comment"># Save to CSV
            output_filename = fclass="hl-string">"oracle_fusion_journal_events_{START_DATE}_to_{END_DATE}.csv"
            event_log_df.to_csv(output_filename, index=False)
            print(fclass="hl-string">"Event log saved to {output_filename}")
        else:
            print(class="hl-string">"No events were generated from the extracted data.")
    else:
        print(class="hl-string">"No data extracted. Please check configuration and API availability.")

Steps

Navigate to the Reports and Analytics Work Area: Log in to Oracle Fusion Financials. Use the Navigator menu to go to Tools, then select Reports and Analytics. This is the central hub for creating and managing all analyses and reports.
Create a BI Publisher Data Model: Instead of starting with an OTBI Analysis, it is more effective for this type of event log extraction to create a BI Publisher Data Model directly. This allows you to use a custom SQL query with UNION ALL statements. Click the 'Browse Catalog' button, navigate to a suitable folder, and choose Create > Data Model.
Define the SQL Query Dataset: In the Data Model editor, click the '+' icon to add a new Data Set and select 'SQL Query'. Give the dataset a descriptive name, for example, 'JournalEventLog'.
Enter the SQL Query: Select 'Oracle BI EE' as the Data Source. Copy the complete SQL script from the Query section below and paste it into the SQL Query text area. The query is designed to pull all required activities and attributes into a single unified structure.
Define Parameters: The provided query uses parameters like :start_date, :end_date, and :ledger_name. The data model interface will automatically detect these. You must configure them as parameters, setting appropriate data types (Date or Text) and default values if needed. This allows for flexible report execution.
Create a Report from the Data Model: After saving the data model, navigate back to the catalog. Click Create > Report. In the report creation wizard, select 'Use Existing Data Model' and locate the data model you just created.
Configure the Report Layout: In the layout editor, choose the 'Table' layout. It is crucial to drag and drop all columns from the data model into the table to ensure they are included in the export. There is no need for complex formatting.
Set Default Output to CSV: Go to the 'View a List' link in the report editor. In the 'Output Formats' section, ensure 'CSV' is selected. It is recommended to uncheck all other formats to simplify scheduling. Save the report.
Schedule the Report for Automated Extraction: Find your saved report in the catalog and click 'More', then 'Schedule'. Create a new schedule job. Configure the frequency, for example, daily or weekly.
Configure Export and Delivery: In the 'Output' tab of the scheduling job, specify the output details. Select your report and choose 'CSV' as the format. In the 'Destination' tab, you can configure the delivery of the file to a content server location (UCM) or an external SFTP server for automated retrieval.
Retrieve and Prepare the Data: Once the scheduled job runs, retrieve the generated CSV file from your configured destination. Ensure the column headers match the requirements for ProcessMind ingestion: JournalEntryId, ActivityName, EventTime, SourceSystem, LastDataUpdate, UserName, etc. The file is now ready for upload.

Configuration

Data Source: The query should be run against the 'Oracle BI EE' data source within BI Publisher, which connects to the underlying transactional database views that support OTBI.
Key Subject Areas (for reference): The query logic is based on data primarily found in the 'General Ledger - Journals Real Time' subject area. It also logically joins data that would be found in 'HCM - Workforce Management - Worker Assignment Real Time' for user details and potentially workflow subject areas for approval history.
Date Range Filtering: It is critical to filter the data to a manageable period. The query includes :start_date and :end_date parameters. We recommend extracting 3 to 6 months of data for an initial analysis. The filter is applied to the journal creation date.
Ledger Filtering: Use the :ledger_name parameter to limit the extraction to a specific primary ledger. Extracting for all ledgers at once can result in extremely large files and performance issues.
Prerequisites: The user creating and scheduling the report must have appropriate BI roles, such as 'BI Administrator' or 'BI Author'. Additionally, the user's data security profile must grant access to the specific ledgers and business units being queried.
Scheduling: For optimal performance and to avoid impacting system users, schedule the extraction job to run during off-peak hours, such as overnight.

a Sample Query config

WITH JournalHeaders AS (
    SELECT
        gjh.je_header_id,
        gjh.ledger_id,
        gl.name AS ledger_name,
        gjb.je_batch_id,
        gjh.doc_sequence_value,
        gjh.name AS journal_name,
        gjc.user_je_category_name AS journal_category,
        gjs.user_je_source_name AS journal_source,
        gjh.default_effective_date,
        gjh.creation_date,
        gjh.created_by,
        gjh.last_update_date,
        gjh.last_updated_by,
        gjh.posted_date,
        gjb.approval_status_code AS batch_approval_status,
        gjh.accrual_rev_period_name,
        gjh.accrual_rev_je_header_id,
        (SELECT SUM(accounted_dr) FROM gl_je_lines WHERE je_header_id = gjh.je_header_id) AS journal_amount
    FROM
        gl_je_headers gjh
    JOIN
        gl_je_batches gjb ON gjh.je_batch_id = gjb.je_batch_id
    JOIN
        gl_ledgers gl ON gjh.ledger_id = gl.ledger_id
    JOIN
        gl_je_categories_tl gjc ON gjh.je_category = gjc.je_category_name AND gjc.language = USERENV('LANG')
    JOIN
        gl_je_sources_tl gjs ON gjh.je_source = gjs.je_source_name AND gjs.language = USERENV('LANG')
    WHERE
        gjh.creation_date BETWEEN TO_DATE(:start_date, 'YYYY-MM-DD') AND TO_DATE(:end_date, 'YYYY-MM-DD')
        AND gl.name = :ledger_name
)
-- 1. Journal Entry Created
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Entry Created' AS "ActivityName",
    jh.creation_date AS "EventTime",
    u.username AS "UserName",
    papf.person_number AS "UserDepartment", -- Placeholder: Configure based on your department tracking
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
LEFT JOIN
    per_users u ON jh.created_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 2. Supporting Documentation Attached
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Supporting Documentation Attached' AS "ActivityName",
    fad.creation_date AS "EventTime",
    u.username AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    fnd_attached_documents fad
JOIN
    JournalHeaders jh ON fad.pk1_value = TO_CHAR(jh.je_header_id) AND fad.entity_name = 'GL_JE_HEADERS'
LEFT JOIN
    per_users u ON fad.created_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(fad.creation_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 3. Journal Submitted For Approval
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Submitted For Approval' AS "ActivityName",
    wfa.created_date AS "EventTime",
    wfa.updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    fun_all_wf_transactions wft ON wft.transaction_id = jh.je_batch_id AND wft.application_id = 101 AND wft.transaction_class = 'Journal Batch'
JOIN
    fun_all_wf_approvals wfa ON wft.transaction_id = wfa.transaction_id AND wfa.response = 'SUBMITTED'
LEFT JOIN
    per_users u ON wfa.updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(wfa.created_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 4. Journal Entry Reviewed & 7. Journal Entry Approved
SELECT
    jh.je_header_id AS "JournalEntryId",
    CASE
        WHEN wfa.approval_level < (SELECT MAX(approval_level) FROM fun_all_wf_approvals WHERE transaction_id = wfa.transaction_id) THEN 'Journal Entry Reviewed'
        ELSE 'Journal Entry Approved'
    END AS "ActivityName",
    wfa.created_date AS "EventTime",
    wfa.updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    fun_all_wf_approvals wfa ON wfa.transaction_id = jh.je_batch_id AND wfa.response = 'APPROVED'
LEFT JOIN
    per_users u ON wfa.updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(wfa.created_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 5. Journal Entry Rejected
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Entry Rejected' AS "ActivityName",
    wfa.created_date AS "EventTime",
    wfa.updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    wfa.reason AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    fun_all_wf_approvals wfa ON wfa.transaction_id = jh.je_batch_id AND wfa.response = 'REJECTED'
LEFT JOIN
    per_users u ON wfa.updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(wfa.created_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 6. Journal Corrected and Resubmitted
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Corrected and Resubmitted' AS "ActivityName",
    resubmit_events.created_date AS "EventTime",
    resubmit_events.updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN (
    SELECT wfa.transaction_id, wfa.created_date, wfa.updated_by
    FROM fun_all_wf_approvals wfa
    WHERE wfa.response = 'SUBMITTED'
    AND EXISTS (
        SELECT 1
        FROM fun_all_wf_approvals prev_wfa
        WHERE prev_wfa.transaction_id = wfa.transaction_id
        AND prev_wfa.response = 'REJECTED'
        AND prev_wfa.created_date < wfa.created_date
    )
) resubmit_events ON jh.je_batch_id = resubmit_events.transaction_id
LEFT JOIN
    per_users u ON resubmit_events.updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(resubmit_events.created_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 8. Journal Posting Initiated
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Posting Initiated' AS "ActivityName",
    gjb.last_update_date AS "EventTime",
    gjb.last_updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    gl_je_batches gjb ON jh.je_batch_id = gjb.je_batch_id
WHERE
    gjb.status = 'I' -- 'I' typically means Selected for Posting
    AND jh.posted_date IS NOT NULL
LEFT JOIN
    per_users u ON gjb.last_updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(gjb.last_update_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 9. Journal Entry Posted
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Entry Posted' AS "ActivityName",
    jh.posted_date AS "EventTime",
    jh.last_updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
LEFT JOIN
    per_users u ON jh.last_updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(jh.posted_date) BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
    jh.posted_date IS NOT NULL

UNION ALL

-- 10. Posting Verified - This is a placeholder as it is typically a manual control step not logged in a standard table.
-- This part of the query will return no rows unless you have a custom solution, such as a Descriptive Flexfield (DFF), to track this.
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Posting Verified' AS "ActivityName",
    jh.last_update_date AS "EventTime", -- Placeholder timestamp
    jh.last_updated_by AS "UserName", -- Placeholder user
    NULL AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
WHERE
    1 = 2 -- This condition ensures no rows are returned. Replace with logic for your custom DFF or tracking field.

UNION ALL

-- 11. Journal Entry Reconciled - This depends on integration with Oracle Account Reconciliation (ARCS).
-- This query assumes reconciliation status is written back to GL_RECONCILIATION_REFERENCES.
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Entry Reconciled' AS "ActivityName",
    grr.last_update_date AS "EventTime",
    grr.last_updated_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    gl_je_lines gjl ON jh.je_header_id = gjl.je_header_id
JOIN
    gl_reconciliation_references grr ON gjl.je_header_id = grr.je_header_id AND gjl.je_line_num = grr.je_line_num
WHERE
    grr.status = 'R' -- Assuming 'R' means Reconciled. Verify based on your ARCS configuration.
LEFT JOIN
    per_users u ON grr.last_updated_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(grr.last_update_date) BETWEEN papf.effective_start_date AND papf.effective_end_date

UNION ALL

-- 12. Journal Reversal Processed
SELECT
    jh.je_header_id AS "JournalEntryId",
    'Journal Reversal Processed' AS "ActivityName",
    rev_jh.creation_date AS "EventTime",
    rev_jh.created_by AS "UserName",
    papf.person_number AS "UserDepartment",
    jh.journal_category AS "JournalCategory",
    jh.journal_source AS "JournalSource",
    jh.journal_amount AS "JournalAmount",
    NULL AS "RejectionReason",
    'Oracle Fusion Financials' AS "SourceSystem",
    SYSTIMESTAMP AS "LastDataUpdate"
FROM
    JournalHeaders jh
JOIN
    gl_je_headers rev_jh ON jh.je_header_id = rev_jh.accrual_rev_je_header_id
LEFT JOIN
    per_users u ON rev_jh.created_by = u.username
LEFT JOIN
    per_all_people_f papf ON u.person_id = papf.person_id AND TRUNC(rev_jh.creation_date) BETWEEN papf.effective_start_date AND papf.effective_end_date
WHERE
    jh.accrual_rev_je_header_id IS NOT NULL

Your Record to Report - Journal Entry Data Template

Your Record to Report - Journal Entry Data Template

Record to Report - Journal Entry Attributes

Record to Report - Journal Entry Activities

Extraction Guides

Oracle OTBI and BI Publisher Report Export

Steps

Configuration

a Sample Query config

BI Publisher Report with Direct SQL Data Model Query

Steps

Configuration

a Sample Query sql

Python Script using Oracle Fusion Financials REST API

Steps

Configuration

a Sample Query python

Bulk Data Extraction using BI Cloud Connector (BICC)

Steps

Configuration

a Sample Query config

Ready to Get Started?

Accelerate Your Record to Report - Journal Entry Today

Your Record to Report - Journal Entry Data Template

Your Record to Report - Journal Entry Data Template

Record to Report - Journal Entry Attributes

Record to Report - Journal Entry Activities

Extraction Guides

Oracle OTBI and BI Publisher Report Export

Steps

Configuration

a Sample Query config

BI Publisher Report with Direct SQL Data Model Query

Steps

Configuration

a Sample Query sql

Python Script using Oracle Fusion Financials REST API

Steps

Configuration

a Sample Query python

Bulk Data Extraction using BI Cloud Connector (BICC)

Steps

Configuration

a Sample Query config

We value your privacy

Ready to Get Started?

Accelerate Your Record to Report - Journal Entry Today