Post Batch program R09801 not updating F0902

[Harry Chen]

There have been a few incidents we found that some batches were not posted correctly. Table F0902 (Account Balances) was not updated.
The PDF of job R09801 shows "No F0911 records exist in this Batch." but in fact there are F0911 records there.

We found this issue is quite serious as it may affect our balance for our year end.

I found one post related to the similar issue in 2009:
--------------
Post program not updating F0902 for Batch Type Z
#152728 - 11/09/09 05:01 PM
--------------

This issue occurred sporadically so it is hard to duplicate or capture logs. As our Post Batch job is executed by scheduler every 10 minutes we do not have chances to check all PDF output. It is possible it happened more often then we thought.
The suggestions from Oracle support is to run integrity reports: e.g. R09705, R097001, R007031.

We know the fix is to run Re-post program R099102 (please carefully choose the right data selection!!) but would like to ask if someone has experience running into the same issue or have it solved?

 

[IanSewell]

Harry,

Do you have your posting program in a single thread queue? If not you may be getting clashes between two programs trying to post the same data. Change it if it is.

Is there a reason why you are posting every 10 minutes? Seems a bit excessive, all the sites I have worked have it daily or half daily at best.

The integrity reports should highlight where the problem occurs and you can then see when the records where being updated. As you say running the re-post will fix the problem but running the interity reports will show you when it occurs.

Ian

 

[Harry Chen]

Thanks Ian for your reply. Yes, we do have a single-thread job queue QGLPOST (Maximum Batch Jobs=1) for Post program R09801.

One thing I want to throw out for ideas is that I found some 'strange' records in F0902, 1 record has not Ledger Type, 782 records has century=0 (normal century should 19 or 20). Not sure if those 'bad' data may cause unexpected problems.
Here are the SQL statements I used:
SELECT TOP 100 GBMCU,GBOBJ,GBSUB, * FROM PRODDTA.F0902 WHERE GBLT <'1' -- List records with empty Ledger Type
SELECT TOP 1000 GBMCU,GBOBJ,GBSUB, * FROM PRODDTA.F0902 WHERE GBCTRY <19 ---List records with strange century


I will ask business process owner why we need that frequent running of R09801 on schedule.
Thanks,

 

[Harry Chen]

Suggestions from the vendor's Support Community:
1. Delete the records in the F0902 where there is no ledger type or with a century =0. Please note that Manual Data changes are not supported by Oracle and it is strongly recommended to backup data before making any changes and to always test the changes in a Test environment prior to making any changes elsewhere.
2. Run Repost (R099102) to get the F0911 and F0902 in sync. For more information on running the Repost, please see Note 1387474.1. As you stated, it is very important to be careful on your data selection.
3. Run the R09705 to ensure that there are no F0902s without F0911s. For more information on this report, please see Note 1383948.1.

It looks hard to find the root cause and the suggestion is to delete those 'invalid' records in F0902 and run integrity reports regularly. It is hard to imagine the issue happened as there is already Transaction Processing for R09801 to allow transaction reversed if the tables-writing is not successful.

Here is my plan:
- Develop a BI Publisher version to run re-post program R099102 (proof mode) as one of the integrity report to compare F0911 and F0902.
- Run R099102 (proof mode) and other integrity reports every night in scheduler
- If the issue occurs again too often, let us say once a month, we need to delete those invalid records.

 

[Domcal22]

We are having a very similar issue where some R09801 post programs are not posting to the F0902 and the ledger is out of balance. Where you able to resolved the issues you had and would you be willing to share your fix.

Thanks,