R12: PRC: INTERFACE SUPPLIER COSTS PERFORMANCE ISSUE (Doc ID 2101269.1)

Last updated on NOVEMBER 18, 2022

Applies to:

Symptoms

Performance issue with PRC: Interface Supplier Costs process.

Problem SQLS

1.
UPDATE AP_PAYMENT_HIST_DISTS DIST SET DIST.PA_ADDITION_FLAG = 'G', REQUEST_ID
 = :B4 , LAST_UPDATE_DATE=SYSDATE, LAST_UPDATED_BY= :B3 , LAST_UPDATE_LOGIN=
 :B3 , PROGRAM_ID= :B2 , PROGRAM_APPLICATION_ID= :B1 , PROGRAM_UPDATE_DATE=
 SYSDATE
WHERE
DIST.PA_ADDITION_FLAG = 'N' AND DIST.PAY_DIST_LOOKUP_CODE = 'DISCOUNT' AND
 EXISTS (SELECT /*+ NO_UNNEST */ NULL FROM AP_PAYMENT_HISTORY_ALL HIST WHERE
 HIST.PAYMENT_HISTORY_ID = DIST.PAYMENT_HISTORY_ID AND
 HIST.ACCOUNTING_EVENT_ID = DIST.ACCOUNTING_EVENT_ID AND
 HIST.TRANSACTION_TYPE NOT IN ( 'PAYMENT CREATED','PAYMENT CANCELLED',
 'PAYMENT ADJUSTED') AND HIST.POSTED_FLAG = 'Y') AND EXISTS(SELECT /*+
 leading ( INV,AID ) use_nl(AID) */ INV.INVOICE_ID FROM AP_INVOICES_ALL INV,
 PO_DISTRIBUTIONS_ALL PO, AP_INVOICE_DISTRIBUTIONS_ALL AID,
 AP_INVOICE_PAYMENTS_ALL AIP WHERE INV.INVOICE_ID = AIP.INVOICE_ID AND
 AID.INVOICE_ID = INV.INVOICE_ID AND AIP.INVOICE_PAYMENT_ID =
 DIST.INVOICE_PAYMENT_ID AND AID.INVOICE_DISTRIBUTION_ID =
 DIST.INVOICE_DISTRIBUTION_ID AND INV.ORG_ID = :B10 AND
 AID.PO_DISTRIBUTION_ID = PO.PO_DISTRIBUTION_ID (+) AND
 NVL(PO.DISTRIBUTION_TYPE,'XXX') <> 'PREPAYMENT' AND
 INV.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL AND NVL(PO.DESTINATION_TYPE_CODE,
 'EXPENSE') = 'EXPENSE' AND INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
 AND NVL(INV.SOURCE, 'xx' ) NOT IN ('Oracle Project Accounting',
 'PA_IC_INVOICES') AND AID.PROJECT_ID > 0 AND AID.LINE_TYPE_LOOKUP_CODE NOT
 IN ('TERV', 'REC_TAX') AND TRUNC(AIP.ACCOUNTING_DATE) <= TRUNC(NVL(:B9 ,
 AIP.ACCOUNTING_DATE)) AND TRUNC(AID.EXPENDITURE_ITEM_DATE) <= TRUNC(NVL(:B8
 ,AID.EXPENDITURE_ITEM_DATE)) AND ( (AID.EXPENDITURE_ITEM_DATE >=:B7 AND :B5
 = 'A' AND :B6 IN ('TAX','EXPENSE')) OR (( (:B6 IN ('TAX','EXPENSE') AND
 AID.EXPENDITURE_ITEM_DATE < :B7 AND NVL(AID.PA_ADDITION_FLAG,'N') <> 'Y')
 OR (:B6 IN ('TAX','EXPENSE') AND AID.EXPENDITURE_ITEM_DATE >= :B7 AND
 AID.PA_ADDITION_FLAG = 'Y') OR (:B6 = 'TAX' AND AID.LINE_TYPE_LOOKUP_CODE
 <> 'NONREC_TAX' AND AID.EXPENDITURE_ITEM_DATE >=:B7 ) OR :B6 = 'SYSTEM')
 AND :B5 = 'C') ) )


2.
UPDATE AP_PAYMENT_HIST_DISTS DIST SET DIST.PA_ADDITION_FLAG = 'O', REQUEST_ID
 = :B4 , LAST_UPDATE_DATE=SYSDATE, LAST_UPDATED_BY= :B3 , LAST_UPDATE_LOGIN=
 :B3 , PROGRAM_ID= :B2 , PROGRAM_APPLICATION_ID= :B1 , PROGRAM_UPDATE_DATE=
 SYSDATE
WHERE
DIST.PA_ADDITION_FLAG = 'N' AND DIST.PAY_DIST_LOOKUP_CODE = 'DISCOUNT' AND
 EXISTS (SELECT /*+ push_subq no_unnest */ NULL FROM AP_PAYMENT_HISTORY_ALL
 HIST WHERE HIST.PAYMENT_HISTORY_ID = DIST.PAYMENT_HISTORY_ID AND
 HIST.ACCOUNTING_EVENT_ID = DIST.ACCOUNTING_EVENT_ID AND
 HIST.TRANSACTION_TYPE IN ( 'PAYMENT CREATED','PAYMENT CANCELLED','PAYMENT
 ADJUSTED') AND HIST.POSTED_FLAG = 'Y') AND EXISTS(SELECT /*+ push_subq
 no_unnest */ INV.INVOICE_ID FROM AP_INVOICES_ALL INV, PO_DISTRIBUTIONS_ALL
 PO, AP_INVOICE_DISTRIBUTIONS_ALL AID, AP_INVOICE_PAYMENTS_ALL AIP WHERE
 INV.INVOICE_ID = AIP.INVOICE_ID AND AID.INVOICE_ID = INV.INVOICE_ID AND
 AIP.INVOICE_PAYMENT_ID = DIST.INVOICE_PAYMENT_ID AND
 AID.INVOICE_DISTRIBUTION_ID = DIST.INVOICE_DISTRIBUTION_ID AND INV.ORG_ID =
 :B10 AND AID.PO_DISTRIBUTION_ID = PO.PO_DISTRIBUTION_ID (+) AND
 NVL(PO.DISTRIBUTION_TYPE,'XXX') <> 'PREPAYMENT' AND
 INV.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL AND NVL(PO.DESTINATION_TYPE_CODE,
 'EXPENSE') = 'EXPENSE' AND INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
 AND NVL(INV.SOURCE, 'xx' ) NOT IN ('Oracle Project Accounting',
 'PA_IC_INVOICES') AND AID.PROJECT_ID > 0 AND AID.LINE_TYPE_LOOKUP_CODE NOT
 IN ('TERV', 'REC_TAX') AND TRUNC(AIP.ACCOUNTING_DATE) <= TRUNC(NVL(:B9 ,
 AIP.ACCOUNTING_DATE)) AND TRUNC(AID.EXPENDITURE_ITEM_DATE) <= TRUNC(NVL(:B8
 ,AID.EXPENDITURE_ITEM_DATE)) AND ( (AID.EXPENDITURE_ITEM_DATE >=:B7 AND :B5
 = 'A' AND :B6 IN ('TAX','EXPENSE')) OR (( (:B6 IN ('TAX','EXPENSE') AND
 AID.EXPENDITURE_ITEM_DATE < :B7 AND NVL(AID.PA_ADDITION_FLAG,'N') <> 'Y')
 OR (:B6 IN ('TAX','EXPENSE') AND AID.EXPENDITURE_ITEM_DATE >= :B7 AND
 AID.PA_ADDITION_FLAG = 'Y') OR (:B6 = 'TAX' AND AID.LINE_TYPE_LOOKUP_CODE
 <> 'NONREC_TAX' AND AID.EXPENDITURE_ITEM_DATE >=:B7 ) OR :B6 = 'SYSTEM')
 AND :B5 = 'C') ) )

Process takes all day to complete.

Cause

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