My Oracle Support Banner

PAAPIMPB: Slow Performance with PRC: Interface Supplier Costs in 'UPDATE /*+ LEADING(DIST) INDEX(DIST AP_PAYMENT_HIST_DISTS_N4)' (Doc ID 2662106.1)

Last updated on APRIL 23, 2020

Applies to:

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

Symptoms

Slow performance with PAAPIMPB PRC:  Interface Supplier Costs.  Problem statement is:

UPDATE /*+ LEADING(DIST) INDEX(DIST AP_PAYMENT_HIST_DISTS_N4)
*/ 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','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') ) )

Patches from the following were applied, yet the issue persists:

'12.2.7 PAAPIMP: PRC: Interface Supplier Costs Slow Performance With AP Discount = 'Y' (Doc ID 2584508.1)' and Bug 29725203: PERF ISSUE W/PRC: INTERFACE SUPPLIER INVOICES FROM PAYABLES - SAME AS 27073325?

File versions are now:

$Header: pav0830.odf 120.6.12020000.4
$Header: PAAPIMPB.pls 120.133.12020000.72
$Header: PAAPIMPS.pls 120.51.12020000.16


Changes

 

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
Changes
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.