Performance issue with Invoice Validation (APPRVL) Concurrent Program on R12 (Doc ID 2004567.1)

Last updated on APRIL 26, 2017

Applies to:

Oracle Payables - Version 12.1.1 to 12.2.6 [Release 12.1 to 12.2]
Information in this document applies to any platform.

Symptoms

On : R12.x to 12.2.x version


ACTUAL BEHAVIOR
---------------
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 statement takes 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 ))

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