R12 : Performance Problem When Unreserving Purchase Orders (POs) (Doc ID 1993824.1)

Last updated on FEBRUARY 18, 2016

Applies to:

Oracle Purchasing - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

PO unreserve action takes very log time to complete.

Steps to Reproduce :
------------------------
1. Create and submit a PO for approval
2. Check the reserve box and approve the PO
3. Try to unreserve the PO, it takes long time to complete

 

Trace shows expensive queries :

 

UPDATE FV_EXTRACT_DETAIL_GT FED SET ENT_UNANTICIPATED_BUD_AMOUNT = 0,
 ACC_UNANTICIPATED_BUD_AMOUNT = 0, ENT_ANTICIPATED_BUDGET_AMOUNT = 0,
 ACC_ANTICIPATED_BUDGET_AMOUNT = 0
WHERE
FED.EVENT_ID = :B1 AND EXISTS (SELECT 1 FROM PO_BC_DISTRIBUTIONS PBD WHERE
 FED.EVENT_ID = PBD.AE_EVENT_ID AND FED.LINE_NUMBER = PBD.LINE_NUMBER AND
 FED.EVENT_ID = :B1 AND NVL(FED.OLD_CCID, PBD.CODE_COMBINATION_ID) = :B3 AND
 PBD.APPLIED_TO_DIST_ID_2 = :B2 AND PBD.MAIN_OR_BACKING_CODE <> 'M')


SELECT SUM(NVL(FED.ENT_UNANTICIPATED_BUD_AMOUNT, 0)),
 SUM(NVL(FED.ACC_UNANTICIPATED_BUD_AMOUNT, 0)),
 SUM(NVL(FED.ENT_ANTICIPATED_BUDGET_AMOUNT, 0)),
 SUM(NVL(FED.ACC_ANTICIPATED_BUDGET_AMOUNT, 0))
FROM
FV_EXTRACT_DETAIL_GT FED, PO_BC_DISTRIBUTIONS PBD WHERE FED.EVENT_ID =
 PBD.AE_EVENT_ID AND FED.EVENT_ID = :B3 AND FED.LINE_NUMBER =
 PBD.LINE_NUMBER AND NVL(FED.OLD_CCID, PBD.CODE_COMBINATION_ID) = :B2 AND
 PBD.APPLIED_TO_DIST_ID_2 = :B1 AND PBD.MAIN_OR_BACKING_CODE <> 'M'

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