EGL92: FS_ALLC App Engine Performance Issue In SQL In FS_ALLC_CLOGuAdjAP uAcctPerD Section (Doc ID 2281097.1)

Last updated on SEPTEMBER 25, 2017

Applies to:

PeopleSoft Enterprise FIN General Ledger - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

ACTUAL BEHAVIOR
---------------
FS_ALLC app engine running for long duration in the SQL: FS_ALLC_CLOGuAdjAP uAcctPerD app engine section.

There is a "Do Select" step in the "FS_ALLC_CLOG.uAdjAP.uAcctPerD" app engine section that pulls ACCOUNTING_PERIOD values for each row in the PS_ALC_CLOG_TAO4 temp table for a specific PROCESS_INSTANCE value. In the SQL action of that same app engine section it then does an update for each row setting the ACCOUNTING_PERIOD value to 996 or some other value where the PROCESS_INSTANCE equals the current process_instance and the ACCOUNTING_PERIOD equals the accounting_period from the "Do Select". In a customer scenario where there are 892,438 rows in the PS_ALC_CLOG_TAO4 temp table for a particular process isntance that ALL have the same accounting_period value of 12. This is causing the update SQL action of this app engine section to be executed 892,438 times(once for each row) where if the 'Do Select" step used "SELECT DISTINCT ACCOUNTING_PERIOD ..." it would only loop 1 time and execute the update SQL action once as well. ** This is delivered code in this app engine section.

EXPECTED BEHAVIOR
-----------------------
FS_ALLC program should not run for over 20+ hours

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1.set up Allocation Step(s)
2.set up Allocation Group
3.run the FS_ALLC process using the above Allocation Group

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms