R12/AP: Performance Issue with Invoice Validation (APPRVL) Concurrent Program (Doc ID 2004567.1)

Last updated on JUNE 09, 2023

Applies to:

Symptoms

Performance problem with the Invoice Validation (APPRVL).

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run the Invoice Validation

The following SQL statements are taking too long:

1)

INSERT INTO AP_TEMP_APPROVAL_GT(HOLD_LOOKUP_CODE, NUMBER_HOLDS_PLACED, NUMBER_HOLDS_RELEASED, ORG_ID)
SELECT HOLD_LOOKUP_CODE,
COUNT(1) - COUNT(RELEASE_LOOKUP_CODE),
COUNT(RELEASE_LOOKUP_CODE),
ORG_ID
FROM AP_HOLDS_ALL AHA
WHERE (AHA.VALIDATION_REQUEST_ID IN
(SELECT FCR.REQUEST_ID
FROM FND_CONCURRENT_REQUESTS FCR
WHERE FCR.PARENT_REQUEST_ID = :B1 ))
OR AHA.VALIDATION_REQUEST_ID = :B1
GROUP BY ORG_ID,
HOLD_LOOKUP_CODE

2)

UPDATE AP_HOLDS_ALL AHA
SET AHA.VALIDATION_REQUEST_ID = NULL
WHERE AHA.VALIDATION_REQUEST_ID = :B1
OR (AHA.VALIDATION_REQUEST_ID IN
(SELECT FCR.REQUEST_ID
FROM FND_CONCURRENT_REQUESTS FCR
WHERE FCR.PARENT_REQUEST_ID = :B1 ))

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.