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;

Deep Dive in AP/AR Netting In R12

Deep Dive in AP/AR Netting In R12

Prior to Release 12, there were several netting solutions in the eBusiness Suite:

Contra-Charging

This Globalization feature was originally a solution for Germany in Release 11, but then it became a Regional Globalization feature in Release 11i.

US Federal Financials Netting

This solution specifically addresses the needs of US requirements. In Release 12, AP/AP Netting has become "core" functionality and is the only netting solution available, which means Contra-Charging and US Federal Financials Netting are all replaced by this solution. Each of those features has a migration path to the Release 12 Netting

How does it work in R12?

Here is the conceptual diagram behind the Netting Engine.

What is AP/AR Netting

What is AP/AR Netting

Oracle AP/AR Netting allows you to net your Payables invoice balances against your Receivables invoice balances for those Customers who are also your Suppliers.

For example, If you have a customer who is also your supplier, then rather than him paying you and then you paying him, AP/AR netting allows you to pay the net difference between how much you owe the supplier and how much he owes you. After establishing a netting agreement with such trading partners, you set up the agreement and the rules associated with it in eBusiness Suite and you can then start to net AP and AR transactions.

Below is a conceptual diagram of how this works.

AP AR Netting Business Process

Netting Business process