This Gl Balances Report query displays beginning balances, period activity, and cumulative totals by account and period for each ledger, helping users monitor financial performance across time.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Ledger balance report by period and account
GL.NAME LEDGER_NAME,
GL.DESCRIPTION LEDGER_DESCRIPTION,
FND_FLEX_EXT.GET_SEGS(
'GL',
'GL#',
GCC.CHART_OF_ACCOUNTS_ID,
GCC.CODE_COMBINATION_ID
) ACCOUNT,
GL.CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE,
GB.CURRENCY_CODE,
GB.PERIOD_NAME,
GB.PERIOD_TYPE,
GB.BEGIN_BALANCE_DR,
GB.BEGIN_BALANCE_CR,
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.BEGIN_BALANCE_DR_BEQ, 0) - NVL(GB.BEGIN_BALANCE_CR_BEQ, 0) + NVL(GB.PERIOD_NET_DR_BEQ, 0) - NVL(GB.PERIOD_NET_CR_BEQ, 0) BALANCE_ACCOUNTED,
NVL(GB.BEGIN_BALANCE_DR, 0) - NVL(GB.BEGIN_BALANCE_CR, 0) BEGIN_BALANCE,
NVL(GB.BEGIN_BALANCE_DR_BEQ, 0) - NVL(GB.BEGIN_BALANCE_CR_BEQ, 0) BEGIN_BALANCE_ACCOUNTED,
NVL(GB.PERIOD_NET_DR, 0) - NVL(GB.PERIOD_NET_CR, 0) PERIOD_NET,
NVL(GB.PERIOD_NET_DR_BEQ, 0) - NVL(GB.PERIOD_NET_CR_BEQ, 0) PERIOD_NET_ACCOUNTED,
NVL(GB.PROJECT_TO_DATE_DR, 0) - NVL(GB.PROJECT_TO_DATE_CR, 0) PROJECT_TO_DATE,
NVL(GB.QUARTER_TO_DATE_DR, 0) - NVL(GB.QUARTER_TO_DATE_CR, 0) QUARTER_TO_DATE,
GB.LEDGER_ID,
GB.ACTUAL_FLAG,
GB.CODE_COMBINATION_ID
FROM
GL_BALANCES GB,
GL_CODE_COMBINATIONS GCC,
GL_LEDGERS GL
WHERE
GB.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GB.ACTUAL_FLAG = 'A'
AND GCC.CHART_OF_ACCOUNTS_ID = GL.CHART_OF_ACCOUNTS_ID
AND GB.LEDGER_ID = GL.LEDGER_ID
AND GB.CURRENCY_CODE = GL.CURRENCY_CODE