GL Balances query in oracle apps

GL Balances query in oracle apps

In this post , we will discuss about GL Balances query in oracle apps. This query helps to show the GL accounts balances as per the GL Period. The Query helps to provides GL account Opening Balances and the Closing Balances based on the Entered Periods. This query is totally based on the GL. All the transactions should be accounted and Posted to GL to show in this SQL Query. Here below is the Complete GL Balances query in oracle apps.

 

 

Important Tables of GL Balances query in oracle apps

1.GL_BALANCES

2.GL_CODE_COMBINATIONS

GL Balances query in oracle apps

SELECT gcc.concatenated_segments "Chart_Of_Accounts",
SUM(NVL(gb.begin_balance_dr,0)-NVL(gb.begin_balance_cr,0)) Opening_balance,
SUM(NVL(gb.begin_balance_dr,0)-NVL(gb.begin_balance_cr,0) +
(NVL(gb.period_net_Dr,0) - NVL(gb.period_net_cr,0))) Closing_bal
FROM gl_balances gb,
gl_code_combinations_kfv gcc
WHERE gb.code_combination_id = gcc.code_combination_id
AND gcc.CONCATENATED_SEGMENTS = '01-000-233230-0000-000-00'
AND gb.ledger_id = :P_LEDGER_ID
AND gb.Actual_flag = 'A'
AND gb.period_name = 'Dec-13'
AND gb.currency_code = (SELECT currency_code FROM gl_ledgers WHERE ledger_id = gb.ledger_id)
GROUP BY gcc.concatenated_segments;