Home » Applications » Oracle Fusion Apps & E-Business Suite » GL posting error after opening next GL period
GL posting error after opening next GL period [message #95750] Fri, 30 April 2004 07:05 Go to next message
David Correia
Messages: 8
Registered: April 2004
Junior Member
We opened the next GL period and closed the previous purchasing period without any problems. They we tried to post journal entries and got this error message in the log file:

PPOS0225: glpubr() : Number of updated GL_BALANCES rows does not equal the number of rows in GL_POSTING_INTERIM table.

I checked the GL_BALANCES table and it was empty. The GL_POSTING_INTERIM table had rows for journal entries for the opened GL period.

I checked the log files for the GL Open Period process and it dropped the GL_POSTING_INTERIM table, recreated it, and inserted records into it. The log makes no mention of the GL_BALANCES table.

First, I would like to know what happened to the GL_BALANCE table and what information is kept in it. Does any Oracle process, such as the GL Open Period process affect this table? We are using Oracle Finanicals 11 or a Oracle 8.0.5 database running WinNT. Any help will be appreciated.
Re: GL posting error after opening next GL period [message #95783 is a reply to message #95750] Tue, 11 May 2004 09:42 Go to previous messageGo to next message
user
Messages: 3
Registered: July 2000
Junior Member
I found this guide:

The following guidelines will help you with troubleshooting possible
balance problems in General Ledger.

Step 1. Is this a new install of Oracle General Ledger?

Step 2. Have any SQL updates been done to any of the tables?

If yes, what tables have been updated, and what was done?

Step 3. Do you have any customizations?

What are they?

Step 4. If a concurrent process failed, do you have your logfile?
It is important to have the most recent concurrent process logfile
in front of you.

Step 5. What makes you think that you are having a problem?

Is there an error on the log?

Does a form show results you are not expecting?

What is the exact error message you are receiving?

Step 6. If there is an 'ORA' error on the log file, have you showed it to
your DBA?
In most cases, ORA errors are database level errors that need to be
resolved by your DBA.


Possible Balances Problems
----------------------------------

1. You see the following message in the output of the General Ledger
report: "CONFLICTS WITH PERIOD NET BALANCE".

This message means that the sum of the amounts, for posted journals,
in GL_JE_LINES for the set of books, account, currency and period,
do not tie to the amount in GL_BALANCES.

If you are running this report for Budget balances, this is ok. Since
Budget amounts are typically not entered with journal entries, it is not
expected that these 2 will tie.

If you are running this report for Actual balances, this is a problem.
Please contact Oracle Support for assistance in resolving this. Things
to have ready when you open the tar are:
- What period is this problem in? How far back does it go? Run the
report for earlier periods also.
- How many accounts are affected? Run the report for all accounts.
- Have any SQL updates been done to any of the tables?
- Have there been any posting processes that ended in error? Gather
those log files. Run the Journals General (unposted/error/posted) on those
batches.
- If you are on Release 11.0.X, has posting patch 937591 been applied?
- Run the following script to check for discrepancies between gl_balances
and posted lines in gl_je_lines.

SELECT a.period_name AS period,
a.code_combination_id AS ccid,
TO_CHAR(a.period_net_dr,'999G999G999G999D99') AS net_dr,
TO_CHAR(a.period_net_cr,'999G999G999G999D99') AS net_cr,
TO_CHAR(b.sum_accounted_dr,'999G999G999G999D99') AS sum_dr,
TO_CHAR(b.sum_accounted_cr,'999G999G999G999D99') AS sum_cr,
a.template_id AS tid
FROM GL_BALANCES a,
(SELECT jel.period_name AS period_name,
jel.code_combination_id AS code_combination_id,
SUM(jel.accounted_dr) AS sum_accounted_dr,
SUM(jel.accounted_cr) AS sum_accounted_cr
FROM gl_je_lines jel,
gl_je_headers jeh,
gl_je_batches jeb
WHERE jel.status = 'P'
AND jeh.je_header_id = jel.je_header_id
AND jeh.actual_flag = 'A'
AND jeh.status = 'P'
AND jeh.currency_code != 'STAT'
AND jeb.je_batch_id = jeh.je_batch_id
AND jeb.set_of_books_id = 1
AND jeb.average_journal_flag = 'N'
GROUP BY jel.period_name, jel.code_combination_id) b
WHERE a.code_combination_id = b.code_combination_id
AND a.period_name = b.period_name
AND a.set_of_books_id = 1
AND a.actual_flag = 'A'
AND a.currency_code = '&functional_currency'
AND a.translated_flag IS NULL
AND (nvl(a.period_net_dr,0) != nvl(b.sum_accounted_dr,0)
OR
nvl(a.period_net_cr,0) != nvl(b.sum_accounted_cr,0))


2. You see the following in your posting log file:
<< glpubr.concurrency() 29-DEC-1999 08:49:56
PPOS0225: glpubr(): Number of updated GL_BALANCES rows does not equal the
number of rows in GL_POSTING_INTERIM_24697 table.
PPOS0226: Balances update will be ROLLED BACK. Posting is stopping here.

<x glpubr() 29-DEC-1999 08:53:24
SHRD0042: Error in ROUTINE: glpubr, status: 0
SHRD0043: ERROR:
glpubr:updated balances rows != gl_interim_posting rows. Posting stops.
CALL YOUR CUSTOMER SUPPORT REPRESENTATIVE!

This message means there are probably duplicate rows in the GL_BALANCES table.
This can be caused by manually updating (SQL) the period statuses, and
subsequently opening periods.

The following script can be used to identify the duplicates rows in GL_BALANCES:

SELECT set_of_books_id, code_combination_id, currency_code, period_name,
actual_flag, budget_version_id, encumbrance_type_id, template_id,
decode(translated_flag, 'Y', 'Y', 'N', 'Y', 'R', 'R', 'X'), count(*)
FROM gl_balances
GROUP BY set_of_books_id, code_combination_id, currency_code, period_name,
actual_flag, budget_version_id, encumbrance_type_id, template_id,
decode(translated_flag, 'Y', 'Y', 'N', 'Y', 'R', 'R', 'X')
HAVING count(*) > 1

If the duplicates have a summary template_id then drop the summary templates
as they have become corrupt.
If the duplicate rows are not for summary accounts (template_id is not
populated for summary accounts), the balances will have to be rolled back to
the period prior to the earliest bad period, and the journals reposted.

Please contact Oracle Support for instructions on how to do this.

3: Posting is incomplete

You are posting a journal in Oracle General Ledger, and the posting has
failed for one reason or another. What you find is that the journal is not
posted, but the balances for the accounts that show on the journal have the
amounts committed.


Possible Symptoms:
------------------------

1. There is a discrepancy between the sum of posted lines in GL_JE_LINES
and GL_BALANCES.

The General Ledger 132 report may have a difference between the beginning
balance and ending balance which is greater than the period activity.

The GL_BALANCES table has been updated by posting, but the statuses in
the batch have not been updated to 'P'. The batch has not been resubmitted
for posting.

2. Same as the first scenario, except the batch has been resubmitted for
posting. This results in a 'double posting' of the amounts in the batch.

The General Ledger 132 report may have a difference between the beginning
balance and ending balance which is greater than the period activity.

On Drilldown, the first amount will be double that on the journal or
subledger.

The statuses on the batch are 'P.'


Solution 3

For 11.0 systems you may need to apply Patch 937591 or Minipack 11.0.GL.E Patch
1178837 to prevent this posting problem from happening again.

You will need to fix the data that has been committed to the GL_BALANCES,
and/or update the journal batches tables.


To Fix the Data:
--------------------

If you can identify the problem batch, and it has not been resubmitted for
posting again, you should contact support.
If you cannot identify the problem batch, or the batch has been resubmitted
for posting, resulting in a 'double post,' rollback the balances and repost
the journals.

You will need to ask for support's help to rectify this problem.
Re: GL posting error after opening next GL period [message #95787 is a reply to message #95783] Wed, 12 May 2004 05:26 Go to previous message
David Correia
Messages: 8
Registered: April 2004
Junior Member
Thanks for your information. I found that I had duplicate records in the GL.GL_BALANCES table for the period that was just opened. When I removed these duplicates, the posting issue was resolved.

I am still trying to find out what caused the problem. Could this be done when two people open the same GL period? I am going to continue to monitor the GL.GL_BALANCES table to see if the problem reoccurs.
Previous Topic: Career advise
Next Topic: Oracle Application Responsibilities
Goto Forum:
  


Current Time: Sun Nov 28 01:44:23 CST 2021