EGL9.1: Slow Performance At AE Step (FS_JGEN_JRNL.uDrill.uDrill2) (SQL) For Subsystem Accounting Entry With Drilldown Option Selected (Doc ID 1515467.1)

Last updated on SEPTEMBER 14, 2016

Applies to:

PeopleSoft Enterprise FIN General Ledger - Version 9.1 to 9.1 [Release 9]
Information in this document applies to any platform.
****Checked for Currency on Jun-6-2014****

Symptoms

On : 9.1 version, Journal Generator

ENVIRONMENT
-----------------------------
- Peoplesoft 9.1 FSCM on bundle #9.
- Peopletools 8.5.11.
- Oracle 11.2.2.

ACTUAL BEHAVIOR
---------------
FS_JGEN takes much longer time at step (FS_JGEN_JRNL.uDrill.uDrill2) (SQL) if relevant Accounting Entry Definition has 'Cross Product Drill Down' option selected.


AE trace shows that (FS_JGEN_JRNL.uDrill.uDrill2) - looped 14102 times and took 1830.7 seconds( populate the journal data to accounting entry table for drill down purpose)

Below is one example of the SQL statement:
-- 16.18.36 ........................(FS_JGEN_JRNL.uDrill.uDrill2) (SQL)
UPDATE PS_VCHR_ACCTG_LINE SET JOURNAL_ID = 'AP00295627', JOURNAL_DATE =
TO_DATE('2012-01-04','YYYY-MM-DD'), FISCAL_YEAR = 2012, ACCOUNTING_PERIOD =
1, GL_DISTRIB_STATUS = 'D', JOURNAL_LINE = 1 WHERE PROCESS_INSTANCE = 4002632
AND GL_DISTRIB_STATUS = 'J' AND ACCOUNT='6340001' AND DEPTID='3700' AND
FOREIGN_CURRENCY='USD' AND CURRENCY_CD='USD' AND OPERATING_UNIT='XXXXX' AND
BUDGET_REF=' ' AND AFFILIATE=' ' AND CHARTFIELD1=' ' AND CHARTFIELD2=' ' AND
CHARTFIELD3=' ' AND PROJECT_ID=' ' AND ENTRY_EVENT=' '

EXPECTED BEHAVIOR
-----------------------
The same process used to take much less time in peoplesoft 8.8 (on DB2) hence similiar processing time is expected.

 

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Set up accounting entry definition with 'Cross Product Drill Down' option.
2. Set up Journal generator with summary option.
3. Run journal generator process.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot complete journal generator process within acceptable time.

 



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