GL Journal Entry Line Details (Oracle Fusion SQL)

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

This GL Journal Entry Line Details query displays detailed line-level journal entry information for each ledger and transaction, helping users to perform in-depth financial reconciliation and reporting.

#sqlquery

SELECT 
-- SQL4Fusion (An Orbit Analytics Project)  
-- GL journal line-level details with amounts and posting info
  GL.NAME SOB_NAME,
  DECODE(
    GCC.ACCOUNT_TYPE,
    'A', 'Asset',
    'C', 'Budgetary (CR)',
    'D', 'Budgetary (DR)',
    'E', 'Expense',
    'L', 'Liability',
    'O', 'Owners Equity',
    'R', 'Revenue',
    NULL
  ) ACCOUNT_NAME,
  GJB.NAME JE_BATCH_NAME,
  GJB.DESCRIPTION JE_BATCH_DESCRIPTION,
  GJB.STATUS JE_BATCH_STATUS,
  GJH.NAME JE_NAME,
  GJH.DESCRIPTION JE_DESCRIPTION,
  GJCT.DESCRIPTION JE_CATEGORY,
  GJST.USER_JE_SOURCE_NAME JE_SOURCE,
  GJH.EXTERNAL_REFERENCE JE_EXTERNAL_REFERENCE,
  GJL.JE_LINE_NUM LINE_NUMBER,
  GJL.DESCRIPTION LINE_DESCRIPTION,
  NVL(GJH.ACCRUAL_REV_FLAG, 'N') ACCRUAL_REVERSED_FLAG,
  DECODE(
    GJH.CURRENCY_CODE,
    'STAT', 0,
    NVL(GJL.ACCOUNTED_DR, 0) - NVL(GJL.ACCOUNTED_CR, 0)
  ) AMOUNT,
  DECODE(
    GJH.CURRENCY_CODE,
    'STAT', 0,
    NVL(GJL.ACCOUNTED_CR, 0)
  ) AMOUNT_CR,
  DECODE(
    GJH.CURRENCY_CODE,
    'STAT', 0,
    NVL(GJL.ACCOUNTED_DR, 0)
  ) AMOUNT_DR,
  GJL.CREATION_DATE CREATION_DATE,
  GJH.CURRENCY_CODE CURRENCY_CODE,
  GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE,
  GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE,
  GJH.PERIOD_NAME PERIOD_NAME,
  GJL.EFFECTIVE_DATE EFFECTIVE_DATE,
  DECODE(
    GJH.CURRENCY_CODE,
    'STAT', 0,
    NVL(GJL.ENTERED_DR, 0) - NVL(GJL.ENTERED_CR, 0)
  ) FOREIGN_AMOUNT,
  DECODE(
    GJH.CURRENCY_CODE,
    'STAT', 0,
    NVL(GJL.ENTERED_CR, 0)
  ) FOREIGN_AMOUNT_CR,
  DECODE(
    GJH.CURRENCY_CODE,
    'STAT', 0,
    NVL(GJL.ENTERED_DR, 0)
  ) FOREIGN_AMOUNT_DR,
  GJB.POSTED_DATE POSTED_DATE,
  DECODE(
    GJH.CURRENCY_CODE,
    'STAT', NVL(GJL.ACCOUNTED_DR, 0) - NVL(GJL.ACCOUNTED_CR, 0),
    NVL(GJL.STAT_AMOUNT, 0)
  ) STAT_AMOUNT,
  GL.CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE,
  GJH.BALANCED_JE_FLAG JE_BALANCED_JE_FLAG
FROM 
  GL_JE_CATEGORIES_TL GJCT,
  GL_JE_SOURCES_TL GJST,
  GL_JE_LINES GJL,
  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 GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
  AND GJH.ACTUAL_FLAG = 'A'
  AND GJB.ACTUAL_FLAG = 'A'
  AND GJH.LEDGER_ID = GL.LEDGER_ID
  AND GJL.LEDGER_ID = GL.LEDGER_ID
  AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
  AND GJH.JE_SOURCE = GJST.JE_SOURCE_NAME(+)
  AND GJH.JE_CATEGORY = GJCT.JE_CATEGORY_NAME(+)
  AND GJST.LANGUAGE = USERENV('LANG')
  AND GJCT.LANGUAGE = USERENV('LANG')