R12 AP: Performance Issue running Invoice Validation Concurrent Request (Doc ID 1071708.1)

Last updated on FEBRUARY 01, 2023

Applies to:

Symptoms

SELECT /*+ dynamic_sampling(2) */ invoice_id
from
AP_INVOICES AI WHERE AI.VALIDATION_REQUEST_ID IS NULL AND
AI.APPROVAL_READY_FLAG <> 'S' AND NOT (NVL(AI.PAYMENT_STATUS_FLAG,'N') =
'Y' AND NVL(AI.HISTORICAL_FLAG,'N') = 'Y') AND EXISTS (
SELECT 1 FROM DUAL WHERE
UPPER(NVL(AI.SOURCE, 'X')) <> 'RECURRING INVOICE' UNION ALL
SELECT 1 FROM DUAL WHERE
UPPER(NVL(AI.SOURCE, 'X')) = 'RECURRING INVOICE' AND NOT
EXISTS ( SELECT NULL
FROM GL_PERIOD_STATUSES GLPS WHERE
GLPS.APPLICATION_ID = '200' AND
GLPS.SET_OF_BOOKS_ID = AI.SET_OF_BOOKS_ID AND
TRUNC(AI.GL_DATE) BETWEEN GLPS.START_DATE AND GLPS.END_DATE
AND NVL(GLPS.ADJUSTMENT_PERIOD_FLAG, 'N') = 'N'
AND GLPS.CLOSING_STATUS = 'N' )
) AND EXISTS ( SELECT 1 FROM
AP_INVOICE_DISTRIBUTIONS_ALL D WHERE D.INVOICE_ID =
AI.INVOICE_ID AND NVL(D.MATCH_STATUS_FLAG, 'N') <> 'A'
AND NOT EXISTS ( SELECT 'Cancelled
distributions' FROM AP_INVOICE_DISTRIBUTIONS_ALL
D2 WHERE D2.INVOICE_ID = AI.INVOICE_ID
AND D2.CANCELLATION_FLAG = 'Y' )
UNION ALL SELECT 1 FROM
AP_HOLDS_ALL H WHERE H.INVOICE_ID = AI.INVOICE_ID
AND H.HOLD_LOOKUP_CODE IN ('QTY ORD',
'QTY REC', 'AMT ORD', 'AMT REC', 'QUALITY',
'PRICE', 'TAX DIFFERENCE', 'CURRENCY DIFFERENCE',
'REC EXCEPTION', 'TAX VARIANCE', 'PO NOT APPROVED',
'PO REQUIRED', 'MAX SHIP AMOUNT', 'MAX RATE AMOUNT',
'MAX TOTAL AMOUNT', 'TAX AMOUNT RANGE', 'MAX QTY ORD',
'MAX QTY REC', 'MAX AMT ORD', 'MAX AMT REC',
'CANT CLOSE PO', 'CANT TRY PO CLOSE', 'LINE VARIANCE',
'CANT FUNDS CHECK') AND
H.RELEASE_LOOKUP_CODE IS NULL AND NOT EXISTS
( SELECT 'Cancelled distributions'
FROM AP_INVOICE_DISTRIBUTIONS_ALL D2 WHERE
D2.INVOICE_ID = AI.INVOICE_ID AND
D2.CANCELLATION_FLAG = 'Y' ) UNION
ALL SELECT 1 FROM DUAL
WHERE AI.FORCE_REVALIDATION_FLAG = 'Y' AND NOT EXISTS
( SELECT 'Cancelled distributions'
FROM AP_INVOICE_DISTRIBUTIONS_ALL D2 WHERE
D2.INVOICE_ID = AI.INVOICE_ID AND
D2.CANCELLATION_FLAG = 'Y' ) UNION
ALL SELECT 1 FROM AP_INVOICE_LINES_ALL
AIL WHERE AIL.INVOICE_ID = AI.INVOICE_ID
AND AI.CANCELLED_DATE IS NULL AND
NVL(AIL.DISCARDED_FLAG, 'N') <> 'Y' AND
NVL(AIL.CANCELLED_FLAG, 'N') <> 'Y' AND NOT EXISTS
( SELECT /*+ NO_UNNEST */
'distributed line' FROM
AP_INVOICE_DISTRIBUTIONS_ALL D5 WHERE
D5.INVOICE_ID = AIL.INVOICE_ID AND
D5.INVOICE_LINE_NUMBER = AIL.LINE_NUMBER )
) AND AI.org_id =xx FOR UPDATE SKIP LOCKED


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1222.23 2045.18 1363402 317503458 4 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1222.27 2045.21 1363402 317503458 4 2

Cause

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