Performance Concerns on Category Lookup Creating Special Request Requisition (Doc ID 2301340.1)

Last updated on AUGUST 27, 2017

Applies to:

PeopleSoft Enterprise SCM eProcurement - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

Steps to Reproduce the Issue:
1) Navigation: PIA > eProcurement >Requisition
2) Select ‘Special Requests’…
3) Click the spy glass next to Category….
4) And it is taking the long time (~12 sec in prod) for the ‘Look Up Category’ page to load…

Actual Results: It takes over 12 seconds for Category Code Lookup


Special Request Category Lookup Results in Multiple Queries Execution
SELECT A.DESCR
 FROM PS_ITEM_CAT_TBL_VW A
 WHERE A.SETID='SHARE'
 AND A.CATEGORY_CD='ALL'
 AND A.EFFDT=(SELECT MAX(C.EFFDT)
 FROM PS_ITEM_CAT_TBL_VW C
 WHERE C.SETID=A.SETID
 AND C.CATEGORY_CD=A.CATEGORY_CD
 AND C.EFFDT<=TO_DATE('1900-01-01','YYYY-MM-DD'))
 

Changes

 

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