R12 AP: APPRVL: Payables Approval Process Performance Issue (Doc ID 1342657.1)

Last updated on DECEMBER 09, 2015

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.
Payables Approval (APPRVL) Process Performance Issue

$Header apetxsrs.pls 120.10.12010000.7 2010/08/27 10:24:36 hchaudha ship $

***Checked for relevance on 09-Dec-2015***
$Header apetxsrb.pls 120.115.12010000.73 2010/08/27 10:27:06 hchaudha ship $
$Header apsin.odf 120.49.12010000.18 2010/07/19 14:23:11 ppodhiya ship $


Symptoms

 

Join our growing Oracle Payables Community and learn from your peers and Oracle on how to address your unique issues in AP.



Payables Approval (APPRVL) process takes very long time to complete. Performance problem is isolated in the following SQL statement:


SELECT /*+ dynamic_sampling(2) cardinality(ai,10) */ invoice_id from AP_INVOICES AI -- 7461423
WHERE AI.VALIDATION_REQUEST_ID IS NULL
AND AI.APPROVAL_READY_FLAG <> 'S'
AND AI.CANCELLED_DATE IS NULL /* Bug 9777752 [http://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=9777752] */
AND NOT ( NVL(AI.PAYMENT_STATUS_FLAG,'N') = 'Y' AND
NVL(AI.HISTORICAL_FLAG,'N') = 'Y' )
AND EXISTS (
SELECT /*+ PUSH_SUBQ */ 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 DUAL
WHERE AI.FORCE_REVALIDATION_FLAG = 'Y'
UNION ALL
SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL D,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE D.INVOICE_ID = AI.INVOICE_ID
AND FSP.ORG_ID = AI.ORG_ID
AND FSP.SET_OF_BOOKS_ID = AI.SET_OF_BOOKS_ID
AND (NVL(FSP.PURCH_ENCUMBRANCE_FLAG,'N') = 'Y' AND NVL(D.MATCH_STATUS_FLAG,'N') <> 'A' OR
(NVL(FSP.PURCH_ENCUMBRANCE_FLAG,'N') = 'N' AND NVL(D.MATCH_STATUS_FLAG,'N') NOT IN ('A','T')))
UNION ALL
SELECT 1
FROM AP_SELF_ASSESSED_TAX_DIST_ALL D,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE D.INVOICE_ID = AI.INVOICE_ID
AND FSP.ORG_ID = AI.ORG_ID
AND FSP.SET_OF_BOOKS_ID = AI.SET_OF_BOOKS_ID
AND (NVL(FSP.PURCH_ENCUMBRANCE_FLAG,'N') = 'Y' AND NVL(D.MATCH_STATUS_FLAG,'N') <> 'A' OR
(NVL(FSP.PURCH_ENCUMBRANCE_FLAG,'N') = 'N' AND NVL(D.MATCH_STATUS_FLAG,'N') NOT IN ('A','T')))
AND NOT EXISTS
( SELECT 'Cancelled distributions'
FROM AP_SELF_ASSESSED_TAX_DIST_ALL D2
WHERE D2.INVOICE_ID = D.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','Expired Registration','Amount Funded','Quantity Funded')
AND H.RELEASE_LOOKUP_CODE IS NULL
AND EXISTS
( SELECT 'Lines'
FROM AP_INVOICE_LINES_ALL L2
WHERE L2.INVOICE_ID = H.INVOICE_ID ) --8580790,9112369
UNION ALL
SELECT 1
FROM AP_INVOICE_LINES_ALL AIL
WHERE AIL.INVOICE_ID = AI.INVOICE_ID
/* Bug 9777752 [http://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=9777752] AND AI.CANCELLED_DATE IS NULL */
AND NVL(AIL.DISCARDED_FLAG, 'N') <> 'Y'
AND NVL(AIL.CANCELLED_FLAG, 'N') <> 'Y'
AND (AIL.AMOUNT <> 0 OR
(AIL.AMOUNT = 0 AND AIL.GENERATE_DISTS = 'Y')) --8580790
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 NOT EXISTS
( SELECT /*+ no_push_subq */ 'Cancelled distributions'
FROM AP_INVOICE_DISTRIBUTIONS_ALL D3
WHERE D3.INVOICE_ID = AI.INVOICE_ID
AND D3.CANCELLATION_FLAG = 'Y'
) FOR UPDATE SKIP LOCKED


Statistics show high resource usage in the fetch phase:

call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.05       0.03          0          2          0          0
Execute      1     0.00       0.00          0          0          0          0
Fetch        1    24.10     261.86      14633     131458          7          5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3    24.15     261.90      14633     131460          7          5

Changes

None

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