Skip to content

README.md

Latest commit

 

History

History
103 lines (77 loc) · 9.64 KB

README.md

File metadata and controls

103 lines (77 loc) · 9.64 KB
layout default
title GL Journals | Oracle EBS SQL Report
description GL batches and journals report, including amounts and attachments – Oracle E-Business Suite SQL report
keywords Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Enginatics, R12 only, Journals, gl_code_combinations_kfv, per_people_f, gl_je_sources_vl
permalink /GL%20Journals/

GL Journals – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

GL batches and journals report, including amounts and attachments

Report Parameters

Ledger, Ledger Category, Period, Period From, Period To, 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, Batch, Journal, Currency, Show Journal Line DFF Attributes, 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, gcck, 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

Report Categories

Enginatics, R12 only

Related Reports

GL Journals (Drilldown), GL Journals (Drilldown) 11g

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource Link
Excel Example Output GL Journals 30-Apr-2021 220634.xlsx
Blitz Report™ XML Import GL_Journals.xml
Full SQL 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:

  • Volume: Millions of journal lines make it difficult to find specific transactions using standard forms.
  • Source Traceability: Determining the origin of a journal (e.g., "Did this come from Payables or Inventory?") often requires navigating multiple screens.
  • Compliance Audits: Auditors frequently request lists of manual journals, unposted entries, or journals created by specific users to test for segregation of duties.
  • Reconciliation: Identifying the specific journal lines that make up a GL account balance is often a manual, error-prone process.

The Solution

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

  • Comprehensive Filtering: Users can slice data by Period, Source, Category, Status, and specific Account Segments to isolate exactly what they need.
  • Full Audit Trail: Displays the "Who, What, When, and Why" of every journal, including the Creator, Approval Status, and Posting Date.
  • Subledger Visibility: By including "Reference" and "Description" fields, it helps link GL entries back to their operational source.

Technical Architecture (High Level)

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

  • Primary Tables:

    • GL_JE_BATCHES: The container for journals, holding control totals and approval status.
    • GL_JE_HEADERS: The journal entry header, defining the Category, Source, and Currency.
    • GL_JE_LINES: The individual debit and credit lines.
    • GL_CODE_COMBINATIONS_KFV: The Chart of Accounts structure.
    • GL_LEDGERS & GL_PERIODS: Provides the accounting context.

  • Logical Relationships: The report follows the standard hierarchy: Batch -> Header -> Line. It joins to the Code Combinations table to resolve the accounting flexfield segments (Company, Department, Account, etc.) into readable values.

Parameters & Filtering

  • Posting Status: Critical for month-end close. Allows users to find 'Unposted' or 'Error' journals that are holding up the close process.
  • Source & Category: Enables targeted analysis (e.g., "Show me all 'Manual' journals" or "Show me all 'Accrual' entries").
  • Account Segments (GL_SEGMENT1...10): Allows for precise account analysis (e.g., "Show all journals hitting the 'Travel Expense' account in the 'Sales' department").
  • Exclude SLA Journals: A performance and usability feature. Since Subledger Accounting (SLA) generates massive volumes of detailed journals, excluding them allows for a cleaner view of manual and summary-level entries.

Performance & Optimization

  • Partition Pruning: By filtering on GL_LEDGERS and GL_PERIODS, the query takes advantage of Oracle's standard partitioning strategies for GL tables.
  • Selective Columns: The report allows users to toggle "Show Journal Line DFF Attributes" and "Show Attachment Details," preventing unnecessary data retrieval when not needed.

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.

Useful Links

© 2026 Enginatics