PAAPIMP: PRC: Interface Supplier Costs Slow Performance With AP Discount = 'Y'
(Doc ID 2584508.1)
Last updated on NOVEMBER 16, 2022
Applies to:
Oracle Grants Accounting - Version 12.2.7 and laterOracle 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=
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') ) )
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 |