Comon Causes of F0911 vs F0902 Out of balance?

[DBohner-(db)]

Looking for Input...

Recently saw several F0902 Balances that did not match against the F0911 GL Detail. Struggling to figure out how that happens.
- I found the batches - and there were no errors, WC Messages or Logs
- SQL the Sum for a Period, and the Detail did not match the period buckets
- The Offset accounts - balanced...

So, if some of the transactions in a batch happily occur and others don't - what the heck might be happening?

The 'simple' solution is to run R099102 - Right? And, that's just a band-aide - because we really don't know if something 'somewhere' was missed, right again??

Any thoughts / suggestions or long drawn out stories that have true meaning to life - appreciated.

(db)

 

[peterbruce]

Dan,

I seem to remember this happening a few times. It had happened when we were experiencing system problems and the F0911 rows were updated, but the F0902 rows were not. Also I think on occasion someone would post a batch who did not have access to all the accounts which caused a few problems.

 

[Chan Rana]

If your size of F0911 is much much larger then many times the async functions will keep running even after the user has started on new transaction or screen, file update can error due to network or timeout issue which can go unnoticed or without error appearing on screen.
Best way to catch earlier is to run GL Integrity reports overnight daily.

 

[DBohner-(db)]

Just putting this here - so we'll have it in the future. As always, Correct Me if You See An Error...

SQL Statement to Compare F0902 vs F0911 Balances:


Select SubQ.*, (F0911_BALANCE - F0902_BUCKET) AS BALANCE
FROM (
Select glaid, glco, glmcu, globj, glsub, glsbl, glfy, glpn, gllt, SUM(glaa/100) AS F0911_Balance,
CASE glpn
WHEN 1 then AVG(gban01/100)
WHEN 2 then AVG(gban02/100)
WHEN 3 then AVG(gban03/100)
WHEN 4 then AVG(gban04/100)
WHEN 5 then AVG(gban05/100)
WHEN 6 then AVG(gban06/100)
WHEN 7 then AVG(gban07/100)
WHEN 8 then AVG(gban08/100)
WHEN 9 then AVG(gban09/100)
WHEN 10 then AVG(gban10/100)
WHEN 11 then AVG(gban11/100)
WHEN 12 then AVG(gban12/100)
END F0902_BUCKET


FROM proddta.F0911, proddta.f0902
WHERE glaid=gbaid and glco=gbco and glmcu=gbmcu and globj=gbobj and glsub=gbsub and glsbl=gbsbl and glfy=gbfy and gllt=gblt
and glpost = 'P'
and GLFY =15

GROUP BY glco, glmcu, globj, glsub, glsbl, glfy, glpn, gllt, glaid

) SubQ
WHERE (F0911_BALANCE - F0902_BUCKET) != 0
ORDER BY glco, glmcu, globj, glsub, glsbl, glfy, glpn, gllt, glaid
;

 

[DBohner-(db)]

Thanks, Chan.

I believe these were all via R09801 - which "shouldn't" be having the typical "Application" issues... In the cases I've been able to define, It's been one user and within a very short timeframe.

Ugh!

(db)

 

[jemez842]

The most common scenario I've seen is related to row security, where they didn't have update access to a particular cost center. For whatever reason, on some releases the process to update the F0911 bypassed row security but the F0902 update honored it, leading to the F0911 showing as posted but no actual update to the F0902.

Another scenario was related to running the post jobs in a multi-threaded queue. I never had a problem with this for regular G/L posts, but for A/P & A/R posts where it's hitting the same account over and over again, it would often lose the F0902 integrity if you had multiple jobs running.

Other than that, sometimes stuff happens.

Terry

 

[peterbruce]

Dan,

See attached for the (Oracle Database) SQL I use for comparing the F0911 and the F0902.