GL Journal Entry Header Information (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle General Ledger (GL-Fusion-SQL's)
1

#oracle fusion sql

GL Journal Entry Header Information — Oracle Fusion SQL Query

This post provides a ready-to-use SQL query to retrieve general ledger (GL) journal entry header information from your Oracle Fusion environment. It helps you extract key journal header fields for reporting, audit trails, accounting reviews, and reconciliation checks.

What the query returns

  • Journal entry header ID, batch name or batch number
  • Journal name / reference number, journal date / accounting date
  • Creation status (draft, posted, reversed, adjusted)
  • Period name, ledger name / ledger ID, currency, conversion type / rate (if applicable)
  • Description or memo attached to the journal
  • Created by user, creation date/time, last updated by & date/time

Useful for these scenarios

  • Preparing GL ledger summary reports
  • Auditing journal entries before posting or after posting
  • Reviewing or exporting journal header metadata for downstream systems (reporting tools, data warehouses)
  • Cross-referencing journal headers with line-level details for reconciliation or analysis

How to use & customize

  • Run the SQL against your Fusion GL schema — adjust table/alias names as per your instance
  • You can extend the query to:
    • include journal line details (accounts, amounts, cost centres)
    • filter by period, currency, status, or ledger
    • join with additional tables if you need user-detail metadata or audit columns
  • If your setup uses multiple ledgers or balancing segments, adjust filters accordingly

#sqlquery

SELECT 
-- SQL4Fusion (An Orbit Analytics Project)  
-- Summary of GL journal entries with batch and account details
  GL.NAME SET_OF_BOOKS_NAME,
  GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5 || '.' || GCC.SEGMENT6 || '.' || GCC.SEGMENT7 || '.' || GCC.SEGMENT8 ACCOUNT_NAME,
  GJB.NAME JE_BATCH_NAME,
  GJB.DESCRIPTION JE_BATCH_DESCRIPTION,
  GJH.NAME JOURNAL_ENTRY_NAME,
  GJH.JE_CATEGORY JOURNAL_ENTRY_CATEGORY,
  GJB.POSTED_DATE JE_BATCH_POSTED_DATE,
  GJH.POSTED_DATE,
  GJST.USER_JE_SOURCE_NAME SOURCE,
  NVL(GJH.RUNNING_TOTAL_ACCOUNTED_DR, 0) - NVL(GJH.RUNNING_TOTAL_ACCOUNTED_CR, 0) RUNNING_TOTAL_ACCOUNTED,
  NVL(GJH.RUNNING_TOTAL_ACCOUNTED_CR, 0) RUNNING_TOTAL_ACCOUNTED_CR,
  NVL(GJH.RUNNING_TOTAL_ACCOUNTED_DR, 0) RUNNING_TOTAL_ACCOUNTED_DR,
  GL.CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE,
  GJH.EXTERNAL_REFERENCE EXTERNAL_REFERENCE,
  NVL(GJH.RUNNING_TOTAL_CR, 0) ENTERED_RUNNING_TOTAL_CR,
  NVL(GJH.RUNNING_TOTAL_DR, 0) ENTERED_RUNNING_TOTAL_DR,
  NVL(GJH.RUNNING_TOTAL_DR, 0) - NVL(GJH.RUNNING_TOTAL_CR, 0) ENTERED_RUNNING_TOTAL,
  GJH.EARLIEST_POSTABLE_DATE EARLIEST_POSTABLE_DATE,
  GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE,
  GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE,
  GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE,
  GJH.CURRENCY_CODE ,
  GJH.CREATION_DATE ,
  GJB.STATUS,
  GJH.PERIOD_NAME ,
  NVL(GJH.CONTROL_TOTAL, 0) CONTROL_TOTAL,
  NVL(GJH.ACCRUAL_REV_FLAG, 'N') ACCRUAL_REV_FLAG,
  DECODE(
    GJH.ACCRUAL_REV_STATUS,
    'M', 'Marked for Reversal',
    'N', 'Not Reversed',
    'R', 'Reversed',
    'U', 'Unreversable',
    GJH.ACCRUAL_REV_STATUS
  ) ACCRUAL_REV_STATUS,
  NVL(GJH.BALANCED_JE_FLAG, 'N') BALANCED_JE_FLAG,
  GJH.DESCRIPTION DESCRIPTION,
  GJH.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE,
  GJCT.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME,
  DECODE(
    GJB.ACTUAL_FLAG,
    'A', 'Actual',
    'B', 'Budget',
    'E', 'Encumbrance',
    GJB.ACTUAL_FLAG
  ) JE_BTCH_ACTUAL_FLAG,
  DECODE(
    GJH.ACTUAL_FLAG,
    'A', 'Actual',
    'B', 'Budget',
    'E', 'Encumbrance',
    GJH.ACTUAL_FLAG
  ) JE_HDRS_ACTUAL_FLAG,
  DECODE(
    GJB.APPROVAL_STATUS_CODE,
    'A', 'Approved',
    'I', 'In Process',
    'Z', 'N/A',
    'J', 'Rejected',
    'R', 'Required',
    'V', 'Validation Failed',
    GJB.APPROVAL_STATUS_CODE
  ) APPROVAL_STATUS_CODE,
  DECODE(
    GJB.AVERAGE_JOURNAL_FLAG,
    'N', 'Standard',
    'Y', 'Average',
    GJB.AVERAGE_JOURNAL_FLAG
  ) AVERAGE_JOURNAL_FLAG,
  DECODE(
    GJB.BUDGETARY_CONTROL_STATUS,
    'F', 'Failed',
    'I', 'In Process',
    'N', 'N/A',
    'P', 'Passed',
    'R', 'Required',
    GJB.BUDGETARY_CONTROL_STATUS
  ) AS BUDGETARY_CONTROL_STATUS,
  DECODE(
    GJH.TAX_STATUS_CODE,
    'N', 'Not Required',
    'R', 'Required',
    'T', 'Taxed',
    GJH.TAX_STATUS_CODE
  ) TAX_STATUS_CODE
FROM 
  GL_JE_SOURCES_TL GJST,
  GL_JE_CATEGORIES_TL GJCT,
  GL_JE_HEADERS GJH,
  GL_JE_BATCHES GJB,
  GL_CODE_COMBINATIONS GCC,
  GL_LEDGERS GL
WHERE 
  1 = 1
  AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID
  AND GJH.ACTUAL_FLAG = 'A'
  AND GJB.ACTUAL_FLAG = 'A'
  AND GJCT.JE_CATEGORY_NAME = GJH.JE_CATEGORY
  AND GJH.LEDGER_ID = GL.LEDGER_ID
  AND GCC.CHART_OF_ACCOUNTS_ID = GL.CHART_OF_ACCOUNTS_ID
  AND GJH.JE_SOURCE = GJST.JE_SOURCE_NAME(+)