GL Balances Database Queries

GL Balances Database Queries


Troubleshooting and Understanding GL balances is a important part of any Oracle application Implementation, Support or Design Project.

In Oracle Application R12 The GL Balances are stored in the database table GL_BALANCES
Below are few queries which we can leverage to identify, Actual and Budget Balances.


Actual Balances


SELECT gl.name,cc.concatenated_segments,cc.segment4,bal.period_name,BAL.LEDGER_ID,gl.name,BAL.CURRENCY_CODE,
nvl(sum(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr),0) "Actual Balance"
FROM apps.gl_balances bal,
apps.gl_code_combinations_KFV cc,
apps.gl_ledgers gl
WHERE cc.code_combination_id = bal.code_combination_id
and bal.ledger_id = gl.ledger_id
--AND bal.Ledger_id = <'your SOB id'>
AND bal.period_name LIKE 'JAN%2015'
AND bal.actual_flag = 'A'
and gl.ledger_category_code = 'PRIMARY'
--AND bal.currency_code = <'you SOB currency code'>
--and cc.segment1 = '101'
and cc.segment4 = '113113'
--and cc.concatenated_segments in ('202.0000.000000000.251001.0000.000.000000','202.0000.000000000.299000.0000.101.000000')
GROUP by cc.concatenated_segments,cc.segment4,bal.period_name,BAL.LEDGER_ID,gl.name,BAL.CURRENCY_CODE
order by gl.name,cc.concatenated_segments,cc.segment4,bal.period_name,BAL.LEDGER_ID,gl.name,BAL.CURRENCY_CODE;


Actual And Budget Balances


SELECT cc.concatenated_segments,cc.segment2,
nvl(sum(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr),0) "Actual Balance",
nvl(sum(balb.begin_balance_dr + balb.period_net_dr - balb.begin_balance_cr - balb.period_net_cr),0) "Budget Balance"
FROM apps.gl_balances bal,
apps.gl_balances balb,
apps.gl_code_combinations_KFV cc,

WHERE cc.code_combination_id = bal.code_combination_id
AND balb.code_combination_id = cc.code_combination_id
AND
--AND bal.Ledger_id = <'your SOB id'>
--AND balb.Ledger_id = <'your SOB id'>
--AND bal.period_name = <'your actual period name'>
--AND balb.period_name = <'your budget period_name'>
AND bal.actual_flag = 'A'
AND balb.actual_flag = 'B'
--AND bal.currency_code = <'you SOB currency code'>
--AND balb.currency_code = <'your SOB currency code'>
and cc.segment1 = '101'
and cc.segment4 = '251011'
GROUP by cc.concatenated_segments,cc.segment2;