This GL Statistical Balances query displays detailed statistical account balances for ledgers, filtered by statistical currency and excluding summary accounts, helping users perform accurate financial analysis.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Detailed statistical account balances for GL ledgers, filtered by currency and excluding summary accounts
GL.NAME SOB_NAME,
GL.PERIOD_SET_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,
GCC.ACCOUNT_TYPE ACCOUNT_TYPE,
GL.CURRENCY_CODE FUNCTIONAL_CURRENCY_CODE,
GB.CURRENCY_CODE CURRENCY_CODE,
GB.PERIOD_NAME,
GB.PERIOD_YEAR PERIOD_YEAR,
GB.PERIOD_TYPE PERIOD_TYPE,
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) + NVL(GB.PERIOD_NET_DR, 0) - NVL(GB.PERIOD_NET_CR, 0) QUARTER_TO_DATE,
NVL(GB.PERIOD_NET_DR, 0) - NVL(GB.PERIOD_NET_CR, 0) PERIOD_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)) YEAR_TO_DATE
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 = 'STAT' -- Statistical Currency
AND GCC.SUMMARY_FLAG = 'N';