R12.1 AP: Invoice Online Validation Poor Performance (Doc ID 1418226.1)

Symptoms

Invoice online validation performs poorly.
It takes 3 to 4 minutes to validate any invoice in the Invoice workbench.


The following SQL statements take most of the time:

1)
SELECT /*+ push_pred(AP_INVOICES_V.pv) */ CUST_REGISTRATION_NUMBER,
INVOICE_TYPE,QUICK_PO_NUMBER,VENDOR_NAME,VENDOR_NUMBER,VENDOR_SITE_CODE,
INVOICE_DATE,INVOICE_NUM,INVOICE_CURRENCY_CODE,INVOICE_AMOUNT,
...
...
...
RELEASE_AMOUNT_NET_OF_TAX,PARTY_ID,PARTY_SITE_ID,PAYMENT_FUNCTION,
PAY_PROC_TRXN_TYPE_CODE,DISC_IS_INV_LESS_TAX_FLAG,APPLICATION_ID
FROM
AP_INVOICES_V WHERE (INVOICE_AMOUNT=:1) order by INVOICE_TYPE DESC,
VENDOR_NAME DESC,INVOICE_DATE DESC


2)
SELECT /*+ push_pred(AP_INVOICES_V.pv) */ CUST_REGISTRATION_NUMBER,
INVOICE_TYPE,QUICK_PO_NUMBER,VENDOR_NAME,VENDOR_NUMBER,VENDOR_SITE_CODE,
INVOICE_DATE,INVOICE_NUM,INVOICE_CURRENCY_CODE,INVOICE_AMOUNT,
..
..
..
FROM
AP_INVOICES_V WHERE (INVOICE_NUM=:1) order by INVOICE_TYPE DESC,VENDOR_NAME
DESC,INVOICE_DATE DESC


3)
UPDATE ZX_REC_NREC_DIST ZD SET TAX_LINE_NUMBER = (SELECT TAX_LINE_NUMBER FROM
ZX_LINES
WHERE
TAX_LINE_ID = ZD.TAX_LINE_ID AND TAX_LINE_NUMBER <> ZD.TAX_LINE_NUMBER )
WHERE NVL(REVERSE_FLAG,'N') = 'Y' AND APPLICATION_ID = :B3 AND ENTITY_CODE =
:B2 AND EVENT_CLASS_CODE = :B1 AND (TRX_ID, TRX_LINE_ID, TRX_LEVEL_TYPE)
IN (SELECT /*+ use_hash(ZX_LINES_DET_FACTORS) */ TRX_ID, TRX_LINE_ID,
TRX_LEVEL_TYPE FROM ZX_LINES_DET_FACTORS WHERE APPLICATION_ID = :B3 AND
ENTITY_CODE = :B2 AND EVENT_CLASS_CODE = :B1 AND EVENT_ID = :B4 ) AND
EXISTS (SELECT TAX_LINE_NUMBER FROM ZX_LINES WHERE TAX_LINE_ID =
ZD.TAX_LINE_ID AND TAX_LINE_NUMBER <> ZD.TAX_LINE_NUMBER )


4)
This SQL also might be the slowest SQL statement:

SQL ID: fr7q58byvjtsu

UPDATE ZX_REC_NREC_DIST ZD SET TAX_LINE_NUMBER = (SELECT TAX_LINE_NUMBER FROM
ZX_LINES
WHERE
TAX_LINE_ID = ZD.TAX_LINE_ID AND TAX_LINE_NUMBER <> ZD.TAX_LINE_NUMBER )
WHERE NVL(REVERSE_FLAG,'N') = 'Y' AND APPLICATION_ID = :B3 AND ENTITY_CODE =
:B2 AND EVENT_CLASS_CODE = :B1 AND TRX_ID IN (SELECT /*+ unnest
cardinality(ZX_TRX_HEADERS_GT 1) */ TRX_ID FROM ZX_TRX_HEADERS_GT) AND
(TRX_LINE_ID, TRX_LEVEL_TYPE) IN (SELECT TRX_LINE_ID, TRX_LEVEL_TYPE FROM
ZX_LINES_DET_FACTORS WHERE APPLICATION_ID = ZD.APPLICATION_ID AND
ENTITY_CODE = ZD.ENTITY_CODE AND EVENT_CLASS_CODE = ZD.EVENT_CLASS_CODE AND
TRX_ID = ZD.TRX_ID AND EVENT_ID = :B4 ) AND EXISTS (SELECT TAX_LINE_NUMBER
FROM ZX_LINES WHERE TAX_LINE_ID = ZD.TAX_LINE_ID AND TAX_LINE_NUMBER <>
ZD.TAX_LINE_NUMBER )

Cause

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