Performance Issue with PO Roll View (PO_POROLLVW) Process (Doc ID 1544853.1)

Last updated on SEPTEMBER 28, 2016

Applies to:

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

Symptoms

Running PO Roll View and experiencing a performance issue. Specifically with PPR6000.UPDEXC2


PO_POROLLVW.PPR6000.UPDEXC2 step is taking very long

PPR6000.UPDEXC2.S 1 0.0 1 14143.2 0 0.0 14143.2

The SQL is executing well on the initial select (1 min 2 sec)

SELECT DISTINCT A.BUSINESS_UNIT ,'XPQA_GRP1' ,'IVC' , A.PO_ID , ' ' , ' ' ,
V.BUSINESS_UNIT , V.VOUCHER_ID ,0 ,0
FROM PS_PO_HDR A , PS_VOUCHER V , PS_VOUCHER_LINE L
WHERE A.BUSINESS_UNIT = 'UCF01' AND L.BUSINESS_UNIT_PO =
'UCF01' AND L.BUSINESS_UNIT_PO = A.BUSINESS_UNIT AND L.PO_ID = A.PO_ID AND
V.BUSINESS_UNIT = L.BUSINESS_UNIT AND V.VOUCHER_ID = L.VOUCHER_ID AND
V.ENTRY_STATUS <> 'X' AND ((V.BUDGET_HDR_STATUS <> 'V' AND
V.BUDGET_MISC_STATUS <> 'V') OR (V.BUDGET_HDR_STATUS = 'V' AND
V.BUDGET_MISC_STATUS <> 'V') OR (V.BUDGET_HDR_STATUS <> 'V' AND
V.BUDGET_MISC_STATUS = 'V')) AND A.BUDGET_HDR_STATUS='V' AND
A.BUDGET_HDR_STS_NP='V'

but when you add

NOT EXISTS( SELECT 'X' FROM PS_PO_ROLL_EXCEP
WHERE PS_PO_ROLL_EXCEP.BUSINESS_UNIT='UCF01' AND
PS_PO_ROLL_EXCEP.SEARCH_ID='XPQA_GRP1' AND PS_PO_ROLL_EXCEP.PO_ID=A.PO_ID AND
PS_PO_ROLL_EXCEP.VOUCHER_BU=V.BUSINESS_UNIT AND
PS_PO_ROLL_EXCEP.VOUCHER_ID=V.VOUCHER_ID)

it experiences performance lag.



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