Queries on GL_BALANCES [ID 14855.1]

Queries on GL_BALANCES [ID 14855.1]
		Modified 29-OCT-2010 Type HOWTO Status PUBLISHED

 Checked for relevance on 29-Oct-2010 Checked for relevance on 27-May-2009 Checked for relevance on 1-Oct-2007  The General Ledger entry reports can sometimes show errors like:      **CONFLICTS W/PER NET BAL**  which normally point to a discrepancy between an account's begin balance and  end balance - normally from the previous period.  To check out such an error  sometimes means querying on the gl_balances table. Here are some suggestions as to possible SIMPLE queries to be run.  If you do discover or suspect an imbalance, please log a service request, explaining the situation.   Note 101579.1 contains scripts that can help in identifying discrepancies. The Period Close Diagnostic can also be used to identify potential problems.   There is no end balance column in gl_balances - the following should highlight  it (this is the period end balance).  Select sum(begin_balance_dr + period_net_dr) - sum(begin_balance_cr +                                                period_net_cr) from gl_balances where period_name = and actual_flag = 'A' and currency_code = and set_of_books_id = and code_combination_id =  PLEASE NOTE:  When the general ledger entry reports work out the end balance  they work it out on a credit or debit basis - not a net of both.  That is, the  period activity total credits on gl_balances should match the journal line  credit total for the period (and CCID) number for number and visa versa with the debits. If it does not then the General Ledger Entry reports will still show  CONFLICT errors even though the account(s) actually have the correct net totals.  This is important when doing a correction on gl_balances or gl_je_lines.  To find period net activity (which forms part of the above)  Select sum(period_net_dr), sum(period_net_cr) from gl_balances where set_of_books_id = and actual_flag = 'A' and currency_code = and period_name =  At foreign currency sites you might want to include the translated_flag.  This will give the sum of every code combination.  However, if you just want to  check against one CCID then take out the sum and include the CCID in the where  clause.  This period net activity should match the amount of journals that have been put  through and for this you need to check mainly against gl_je_lines:  Select sum(j.entered_dr), sum(j.entered_cr) from gl_je_lines j, gl_je_headers h where j.je_header_id = h.je_header_id and j.period_name = and h.currency_code = and j.set_of_books_id = and j.status = 'P' and h.actual_flag =  Again, you might want to search on a particular code combination and this can be included in the where clause - along with other columns, say, from headers to  limit the data returned. The above highlighted one problem for me as the journal lines did not match the balances.  You might want to extend the select to include the sums of the headers as in one case headers for the period did not match the lines in that one journal for  November included some lines for November and some for October (which should be  impossible but happened because of a system problem - there was also a suspected problem in this area with running maintain batch periods -ie changing the period on the journal header and running posting before it completed).  Select h.je_header_id from gl_je_lines j, gl_je_headers h where j.je_header_id = h.je_header_id and j.period_name != and h.period_name = and j.status = 'P' and h.currency_code = and j.set_of_books_id = and h.actual_flag =   No update scripts are included as each case is different. Contact Oracle Support for  a solution if you suspect an imbalance in gl_balances.