GL General Ledger Balances (Oracle Fusion SQL)

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

This GL General Ledger Balances query shows detailed ledger balances with account types and period summaries, helping users analyze financial performance across ledgers.

#sqlquery

SELECT
  -- SQL4Fusion (An Orbit Analytics Project)
  -- Shows detailed ledger balances with account types and period summaries
  GL.NAME LEDGER,
  DECODE(
    GCC.ACCOUNT_TYPE,
    'A', 'Asset',
    'C', 'Budgetary (CR)',
    'D', 'Budgetary (DR)',
    'E', 'Expense',
    'L', 'Liability',
    'O', 'Owners Equity',
    'R', 'Revenue',
    NULL
  ) ACCOUNT_NAME,
  GCC.ACCOUNT_TYPE,
  NVL(GB.QUARTER_TO_DATE_DR, 0) - NVL(GB.QUARTER_TO_DATE_CR, 0) + NVL(GB.PERIOD_NET_DR, 0) - NVL(GB.PERIOD_NET_CR, 0) QUARTER_TO_DATE,
  NVL(GB.BEGIN_BALANCE_DR, 0) - NVL(GB.BEGIN_BALANCE_CR, 0) + NVL(GB.PERIOD_NET_DR, 0) - NVL(GB.PERIOD_NET_CR, 0) BALANCE,
  NVL(GB.PERIOD_NET_DR, 0) - NVL(GB.PERIOD_NET_CR, 0) PERIOD_NET,
  NVL(GB.PROJECT_TO_DATE_DR, 0) - NVL(GB.PROJECT_TO_DATE_CR, 0) PROJECT_TO_DATE,
  GB.CURRENCY_CODE CURRENCY_CODE,
  GL.CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE,
  GB.PERIOD_NAME,
  GB.PERIOD_TYPE PERIOD_TYPE,
  GB.PERIOD_YEAR PERIOD_YEAR,
  GB.ACTUAL_FLAG,
  GCC.SUMMARY_FLAG
FROM
  GL_BALANCES GB,
  GL_CODE_COMBINATIONS GCC,
  GL_LEDGERS GL
WHERE
  1 = 1
  AND GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
  AND GCC.CHART_OF_ACCOUNTS_ID = GL.CHART_OF_ACCOUNTS_ID
  AND GB.ACTUAL_FLAG = 'A'
  AND GB.LEDGER_ID = GL.LEDGER_ID
  AND GB.CURRENCY_CODE = GL.CURRENCY_CODE;