GL Journals – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: General Ledger · all General Ledger reports

Overview

GL batches and journals report, including amounts and attachments. One row per journal line.

The Knocked Off flag marks journal lines whose equal and opposite accounted amounts on the same account combination and batch fully cancel to zero; Exclude Knocked Off Journal Lines removes them from the output.

Exclude Reversed Journals removes a reversal and its reversed journal only when both fall within the selected period range, so a reversal whose original journal lies outside the range stays visible and the period totals remain complete.

Show Open/Close Balances adds a ledger currency opening balance row for the first selected period and a closing balance row for the last selected period per account combination, marked as Opening Balance or Closing Balance in the Source column. Balance rows follow the ledger, period and account filters, not journal level filters such as Source, Category or Posting Status. The chosen value controls whether balance rows appear only for accounts with journal lines in the range, also for accounts with a nonzero balance, or for every account with a balance record, while All Accounts Grouped shows a single opening and closing balance row summed over the selected accounts.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Ledger, Ledger Category, Period, Period From, Period To, Show Open/Close Balances, Posted Date From, Posted Date To, Creation Date From, Creation Date To, Created By, Journal Source, Journal Category, Posting Status, Funds Status, Exclude SLA Journals, Exclude Reversed Journals, Exclude Knocked Off Journal Lines, Batch, Journal, Currency, Journal Line DFF Display, Show Attachment Details, Balance Type, Budget Name, Encumbrance Type, Reference, Concatenated Segments, GL_SEGMENT1, GL_SEGMENT1 From, GL_SEGMENT1 To, GL_SEGMENT2, GL_SEGMENT2 From, GL_SEGMENT2 To, GL_SEGMENT3, GL_SEGMENT3 From, GL_SEGMENT3 To, GL_SEGMENT4, GL_SEGMENT4 From, GL_SEGMENT4 To, GL_SEGMENT5, GL_SEGMENT5 From, GL_SEGMENT5 To, GL_SEGMENT6, GL_SEGMENT6 From, GL_SEGMENT6 To, GL_SEGMENT7, GL_SEGMENT7 From, GL_SEGMENT7 To, GL_SEGMENT8, GL_SEGMENT8 From, GL_SEGMENT8 To, GL_SEGMENT9, GL_SEGMENT9 From, GL_SEGMENT9 To, GL_SEGMENT10, GL_SEGMENT10 From, GL_SEGMENT10 To

Oracle EBS Tables Used

gl_code_combinations_kfv, per_people_f, gl_je_sources_vl, gl_je_categories_vl, gl_budget_versions, gl_encumbrance_types, gl_daily_conversion_types, gl_je_headers, gl_system_usages, zx_rates_b, gl_ledgers, gl_periods, gl_je_batches, gl_je_lines, gl_je_lines_recon, fnd_attached_documents, fnd_documents, fnd_documents_tl, fnd_document_datatypes, fnd_document_categories_vl, fnd_lobs, fnd_documents_short_text, fnd_documents_long_text, gl_balances

Report Categories

Enginatics

GL Journals (Drilldown), GL Journals (Drilldown) 11g, GL Account Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Distribution Analysis, GL Account Analysis 11g, GL Account Analysis (Drilldown), GL Journals 11i, GL Setup Upload

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output GL Journals 30-Apr-2021 220634.xlsx
Blitz Report™ XML Import GL_Journals.xml
SQL Source GL Journals.sql
Full Report on Enginatics www.enginatics.com/reports/gl-journals/

Case Study & Technical Analysis: GL Journals

Executive Summary

The GL Journals report is the backbone of financial auditing and analysis in Oracle General Ledger. It provides a granular view of all journal entries—whether manual, imported, or generated by subledgers. This report is indispensable for Controllers and Auditors to verify the accuracy of financial statements, investigate account anomalies, and ensure strict adherence to internal controls.

Business Challenge

The General Ledger is the final repository for all financial transactions. However, analyzing this data can be challenging due to:

The Solution

This report solves these challenges by offering a powerful, parameter-driven “Operational View” of the General Ledger.

Technical Architecture (High Level)

The report queries the core GL transaction tables, optimized for the star-schema design of Oracle GL.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How can I find all manual journal entries created last month? A: Set the “Period” to last month and the “Journal Source” parameter to ‘Manual’.

Q: Does this report show the foreign currency amounts? A: Yes, the report displays both the “Entered” (foreign) currency amounts and the “Accounted” (functional) currency amounts, along with the conversion rate used.

Q: Why can’t I see the subledger transaction details (e.g., Invoice Number) here? A: This report shows the GL view. While some references are carried over to GL_JE_LINES.REFERENCE_1, detailed subledger data is best viewed in the “GL Account Analysis (Drilldown)” report or specific subledger reports.

© Enginatics GmbH