R12 PAAPIMPB: PRC: Interface Supplier Invoices From Payables Performance Issue after Upgrade to 12.2.5 (Doc ID 2201793.1)

Last updated on MARCH 08, 2017

Applies to:

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

Symptoms

On running PRC:Interface Supplier Invoices from Payables, the request runs for nearly 10 hours to complete for a single OU. The problem statement is:

 UPDATE AP_INVOICE_DISTRIBUTIONS_ALL APDIST SET APDIST.PA_ADDITION_FLAG =
 DECODE(NVL(APDIST.AMOUNT_VARIANCE,0),0,'G',
 PAAPIMP_PKG.INCREMENT_W_COUNT('W')), 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
ROWID IN ( SELECT /*+ index(dist AP_INVOICE_DISTRIBUTIONS_N7)*/ DIST.ROWID
 FROM AP_INVOICES INV, AP_INVOICE_DISTRIBUTIONS_ALL DIST WHERE
 INV.INVOICE_ID = DIST.INVOICE_ID AND ( DIST.LINE_TYPE_LOOKUP_CODE IN
 ('ITEM','ACCRUAL','RETROACCRUAL','NONREC_TAX') OR (
 PA_NL_INSTALLED.IS_NL_INSTALLED = 'Y' AND EXISTS (SELECT 'X' FROM
 MTL_SYSTEM_ITEMS SI, PO_LINES_ALL POL, PO_DISTRIBUTIONS_ALL PO_DIST1 WHERE
 PO_DIST1.PO_LINE_ID = POL.PO_LINE_ID AND PO_DIST1.PO_DISTRIBUTION_ID =
 DIST.PO_DISTRIBUTION_ID AND SI.INVENTORY_ITEM_ID = POL.ITEM_ID AND
 PO_DIST1.PROJECT_ID IS NOT NULL AND SI.COMMS_NL_TRACKABLE_FLAG = 'Y' AND
 (SI.ORGANIZATION_ID = PO_DIST1.ORG_ID OR SI.ORGANIZATION_ID IN (SELECT
 NVL(SHIP_TO_ORGANIZATION_ID,-9999) FROM PO_LINE_LOCATIONS_ALL PLL WHERE
 PLL.PO_HEADER_ID = PO_DIST1.PO_HEADER_ID AND PLL.PO_LINE_ID =
 PO_DIST1.PO_LINE_ID) )) ) ) AND NOT EXISTS (SELECT NULL FROM
 AP_INVOICE_DISTRIBUTIONS_ALL APDIST2 WHERE APDIST2.PA_ADDITION_FLAG = 'Y'
 AND APDIST2.PO_DISTRIBUTION_ID = DIST.PO_DISTRIBUTION_ID AND
 (APDIST2.LINE_TYPE_LOOKUP_CODE = DIST.LINE_TYPE_LOOKUP_CODE OR
 (APDIST2.HISTORICAL_FLAG = 'Y' AND DIST.LINE_TYPE_LOOKUP_CODE = 'ACCRUAL'))
 AND APDIST2.LINE_TYPE_LOOKUP_CODE IN ('ITEM','ACCRUAL','RETROACCRUAL',
 'NONREC_TAX') ) AND ( EXISTS (SELECT NULL FROM AP_INVOICE_DISTRIBUTIONS_ALL
 APDIST1, AP_INVOICES_ALL AP1 WHERE APDIST1.PA_ADDITION_FLAG IN ('F', 'G')
 AND AP1.INVOICE_ID = APDIST1.INVOICE_ID AND APDIST1.PO_DISTRIBUTION_ID =
 DIST.PO_DISTRIBUTION_ID AND AP1.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
 AND APDIST1.LINE_TYPE_LOOKUP_CODE <> 'PREPAY' ) OR EXISTS ( SELECT
 RCV_TXN.PO_DISTRIBUTION_ID FROM RCV_TRANSACTIONS RCV_TXN ,
 RCV_RECEIVING_SUB_LEDGER RCV_SUB WHERE RCV_TXN.PO_DISTRIBUTION_ID =
 DIST.PO_DISTRIBUTION_ID AND RCV_SUB.PA_ADDITION_FLAG IN ('Y','I') AND
 RCV_SUB.RCV_TRANSACTION_ID = RCV_TXN.TRANSACTION_ID ) OR EXISTS ( SELECT
 PO.PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS PO WHERE PO.PO_DISTRIBUTION_ID =
  DIST.PO_DISTRIBUTION_ID AND NVL(PO.DISTRIBUTION_TYPE,'XXX') <>
 'PREPAYMENT' AND PO.PROJECT_ID > 0 AND NVL(PO.DESTINATION_TYPE_CODE,
 'EXPENSE') = 'EXPENSE' AND PO.ACCRUE_ON_RECEIPT_FLAG= 'Y' ) ) AND
 NVL(INV.SOURCE, 'xx' ) NOT IN ('Oracle Project Accounting','PA_IC_INVOICES',
 'PA_COST_ADJUSTMENTS') AND DIST.PA_ADDITION_FLAG = 'N' AND DIST.POSTED_FLAG
 = 'Y' AND TRUNC(DIST.ACCOUNTING_DATE) <= TRUNC(NVL(:B7 ,
 DIST.ACCOUNTING_DATE)) AND TRUNC(DIST.EXPENDITURE_ITEM_DATE) <=
 TRUNC(NVL(:B6 ,DIST.EXPENDITURE_ITEM_DATE)) AND DIST.PROJECT_ID > 0 AND
 (:B5 = 'A' OR (:B5 = 'C' AND DIST.HISTORICAL_FLAG = 'Y')) AND
 DIST.PO_DISTRIBUTION_ID > 0 )

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