| Inconsistent Balances Scripts [ID 101579.1] |
|
| | Modified 06-JAN-2011 Type TROUBLESHOOTING Status PUBLISHED | |
Checked for relevance on 14-Apr-2009 Checked for relevance on 07-Jan-2011 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
 Related
|
Back to top
No comments:
Post a Comment