Inconsistent Balances Scripts--From Metalink

 PURPOSE
-------
   
To identify inconsistencies in gl_balances or between  gl_balances and
gl_je_lines.

SCOPE & APPLICATION
-------------------

This bulletin could be useful when there are inconsistencies in balances for
some accounts, customer can find inconsistency or corruption with information
between two or more modules of the applications. For example, Account Analysis
Report and Summary Trial Balance.

With this information it is possible to verify the inconsistency, the periods
and sometimes the list of accounts involved.

Keep in mind that if there is one account with inconsistency is necessary to do
a rollback or to use the Balances Corruption Datafix script(Release 11i). Log a service request
if these scripts show that there is an issue.

Balances Inconsistency Diagnostic Scripts
----------------------------------------
Versions Affected:
 
  Oracle Applications Rel 10.7
  Oracle Applications Rel 11
  Oracle Applications Rel 11i
 
Platforms Affected:
  
  GENERIC

Description:   
------------
When Process Post Journal Entries is run for each Journal, the transaction
amount is accumulated in a corresponding record of gl_balances.  If setup
includes the Average Balance feature the transaction amount is also stored in
gl_daily_balances.

The information in gl_balances must be consistent by itself and with the
journals posted in gl_je_lines.

Scripts that can help to verify whether there is an inconsistency.
------------------------------------------------------------------
1. Totals in Gl_balances.

GL_BALANCES must be balanced for each period. This query will show you
the begin balances, period movements and end balances. The balances
must be equal in credits and debits. The script requires the set of books
identification, the period name and the balance type.

set linesize 200
col begin_dr format 999,999,999,999,999.99
col begin_cr format 999,999,999,999,999.99
col dr format 999,999,999,999.99
col cr format 999,999,999,999.99
col end_dr format 999,999,999,999,999.99
col end_cr format 999,999,999,999,999.99

SELECT translated_flag
, currency_code
, sum(begin_balance_dr) begin_dr
, sum(begin_balance_cr) begin_cr
, sum(period_net_dr) dr
, sum(period_net_cr) cr
, sum(begin_balance_dr) + sum(period_net_dr) end_dr
, sum(begin_balance_cr) + sum(period_net_cr) end_cr
FROM gl_balances
WHERE set_of_books_id = &set_of_book_id
AND period_name = '&period_name'
AND actual_flag = '&Balance_type'
           -- A Actual, B Budget, E Encumbrance
AND template_id is null
group by  translated_flag
, currency_code
;


2. Gl_balances vs. Gl_balances

   Sometimes, the calculated end balance for some period is not equal to
   begin balance in the next period.

   If you know the CCID or CCIDs, you can obtain the balances during one
   or more fiscal years for some accounts.

col begin_dr format 99,999,999,999
col begin_cr format 99,999,999,999
col period_dr format 99,999,999,999
col period_cr format 99,999,999,999
col end_dr format 99,999,999,999
col end_cr format 99,999,999,999
col periodo_year format 9999
col periodo_num format 99
col periodo_name format a6
col currency format a4
set linesize 180
select code_combination_id ccid
, currency_code currency
, period_name
, actual_flag
, budget_version_id
, encumbrance_type_id
, translated_flag
, period_year
, period_num
, begin_balance_dr begin_dr
, begin_balance_cr begin_cr
, period_net_dr period_dr
, period_net_cr period_cr
, begin_balance_dr+period_net_dr end_dr
, begin_balance_cr+period_net_cr end_cr
from gl_balances
where set_of_books_id=&sob
       and code_combination_id in ( &list_of_ccid )
       and currency_code = upper ('¤cy')
       and actual_flag = '&balance_type'
           -- A Actual, B Budget, E Encumbrance
       and period_year in ( &list_of_years )
       and (translated_flag <> 'Y' or translated_flag is null)
order by code_combination_id, currency_code, budget_version_id
, encumbrance_type_id, translated_flag , period_year, period_num
/

3. Gl_balances vs gl_balances.

   If you know or suspect the periods in which the corruption exists, you can
   obtain all the accounts with this problem. This can only check periods within
   the same calendar year. It does not check translated balances.

col end_previous format 999,999,999,999.99
col begin_next format 999,999,999,999.99
Select a.code_combination_id ccid, a.template_id,
a.BEGIN_BALANCE_DR - a.BEGIN_BALANCE_CR
+ a.PERIOD_NET_DR - a.PERIOD_NET_CR end_previous,
b.BEGIN_BALANCE_DR - b.BEGIN_BALANCE_CR begin_next
from gl_balances a
, gl_balances b
where a.set_of_books_id = &set_of_books_id
and a.set_of_books_id = b.set_of_books_id
and a.code_combination_id = b.code_combination_id
and a.actual_flag = b.actual_flag
and a.actual_flag = '&Batch_balance'
           -- A Actual, B Budget, E Encumbrance
and nvl(a.ENCUMBRANCE_TYPE_ID,-1)
= nvl(b.ENCUMBRANCE_TYPE_ID,-1)
and nvl(a.budget_version_id,-1) = nvl(b.budget_version_id,-1)
and a.currency_code = b.currency_code
and ((a.translated_flag is null and b.translated_flag is null)
or (a.translated_flag = 'R' and b.translated_flag = 'R'))
and a.period_name = '&prior_period'
and b.period_name = '&problem_period'
and (a.BEGIN_BALANCE_DR + a.PERIOD_NET_DR != b.BEGIN_BALANCE_DR
or a.BEGIN_BALANCE_CR + a.PERIOD_NET_CR != b.BEGIN_BALANCE_CR)
order by 1;

In 11i the standard report Summary Trial balance also gives a warning if the trial balance is
out of balance

4. Gl_balances vs. gl_je_lines

   Sometimes the sum of posted transactions in gl_je_lines is not the same
   as the balance stored in gl_balances for a period.

Please use the Diagnostic Note 260031.1 Oracle General Ledger Period Closing Activity Test
or use the General 132 Report or the Account Analysis Report

In 11i please run the Account analysis report and General ledger 132 reports
which should provide warnings if the journals and lines do not match.
This is confirmation that you have a problem.

RELATED DOCUMENTS
-----------------
Bug 1178577