My Oracle Support Banner

PAAPIMP: PRC: Interface Supplier Costs Slow Performance With AP Discount = 'Y' (Doc ID 2584508.1)

Last updated on JULY 14, 2021

Applies to:

Oracle Grants Accounting - Version 12.2.7 and later
Oracle Project Costing - Version 12.2.7 and later
Information in this document applies to any platform.

Symptoms

After upgrading to R12.2.7 from 12.1.3, PRC: Interface Supplier Costs has slow performance. The program runs for 24+ hrs., and needs to be terminated.

Expensive statement:

SQL ID: 21jk5s185mwyd Plan Hash: 4181510110

UPDATE /*+ INDEX(DIST AP_PAYMENT_HIST_DISTS_N4)*/ 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','MANUAL PAYMENT ADJUSTED') AND HIST.POSTED_FLAG = 'Y') AND
 EXISTS(SELECT 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') ) )

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


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