R12: PA: PRC: Interface Supplier Costs Is Having Performance Issue (Doc ID 2049512.1)

Last updated on OCTOBER 28, 2022

Applies to:

Symptoms

Customer upgraded from from 11i to R12.2.4. The concurrent Program PRC: Interface Supplier Costs is having performance issue

tkprof trace file shows the queries below caused the most performance:

SQL ID: 3w3qtp559svm2 Plan Hash: 708680730

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 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 2357.01 43167.06 5861866 18148833 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2357.01 43167.06 5861866 18148833 0 0

SQL ID: 91ymx6tgdxjpf Plan Hash: 2603236020

UPDATE /*+ use_nl(rcv_sub) index(rcv_sub RCV_RECEIVING_SUB_LEDGER_N7) */
  RCV_RECEIVING_SUB_LEDGER RCV_SUB SET RCV_SUB.PA_ADDITION_FLAG =
  DECODE(RCV_SUB.PA_ADDITION_FLAG,'N','O','I','J'), RCV_SUB.REQUEST_ID = :B4 ,
  RCV_SUB.LAST_UPDATE_DATE = SYSDATE, RCV_SUB.LAST_UPDATED_BY = :B3 ,
  RCV_SUB.LAST_UPDATE_LOGIN = :B3 , RCV_SUB.PROGRAM_ID = :B2 ,
  RCV_SUB.PROGRAM_APPLICATION_ID = :B1 , RCV_SUB.PROGRAM_UPDATE_DATE =
  SYSDATE
WHERE
 EXISTS (SELECT /*+ no_unnest */ 1 FROM RCV_TRANSACTIONS RCV_TXN,
  PO_DISTRIBUTIONS PO_DIST, PO_LINES_ALL POL WHERE
  ((RCV_TXN.DESTINATION_TYPE_CODE ='EXPENSE' ) OR
  (RCV_TXN.DESTINATION_TYPE_CODE = 'RECEIVING' AND (RCV_TXN.TRANSACTION_TYPE
  IN ('RETURN TO VENDOR','RETURN TO RECEIVING')))) AND
  TRUNC(RCV_TXN.TRANSACTION_DATE) <= TRUNC(NVL(:B6 ,RCV_TXN.TRANSACTION_DATE))
  AND RCV_TXN.PO_DISTRIBUTION_ID = PO_DIST.PO_DISTRIBUTION_ID AND
  PO_DIST.PO_LINE_ID = POL.PO_LINE_ID AND RCV_SUB.CODE_COMBINATION_ID =
  PO_DIST.CODE_COMBINATION_ID AND NVL(PO_DIST.DISTRIBUTION_TYPE,'XXX') <>
  'PREPAYMENT' AND RCV_SUB.RCV_TRANSACTION_ID = RCV_TXN.TRANSACTION_ID AND
  TRUNC(PO_DIST.EXPENDITURE_ITEM_DATE) <= TRUNC(NVL(:B5 ,
  PO_DIST.EXPENDITURE_ITEM_DATE)) AND PO_DIST.PROJECT_ID > 0 AND
  PO_DIST.ACCRUE_ON_RECEIPT_FLAG= 'Y' AND ( (RCV_TXN.DESTINATION_TYPE_CODE =
  'EXPENSE' AND (( RCV_TXN.TRANSACTION_TYPE = 'DELIVER' AND
  RCV_SUB.ENTERED_DR IS NOT NULL ) OR ( RCV_TXN.TRANSACTION_TYPE = 'RETURN TO
  RECEIVING' AND RCV_SUB.ENTERED_CR IS NOT NULL ) OR (
  RCV_TXN.TRANSACTION_TYPE = 'CORRECT' AND (RCV_SUB.ENTERED_CR IS NOT NULL OR
  RCV_SUB.ENTERED_DR IS NOT NULL) AND RCV_SUB.ACCOUNTING_LINE_TYPE='Charge'
  AND DECODE(POL.ORDER_TYPE_LOOKUP_CODE, 'RATE' , RCV_TXN.AMOUNT, 'FIXED
  PRICE', RCV_TXN.AMOUNT, RCV_TXN.PRIMARY_QUANTITY) < 0 ) OR (
  RCV_TXN.TRANSACTION_TYPE = 'CORRECT' AND RCV_SUB.ENTERED_DR IS NOT NULL AND
  DECODE(POL.ORDER_TYPE_LOOKUP_CODE, 'RATE' , RCV_TXN.AMOUNT, 'FIXED PRICE',
  RCV_TXN.AMOUNT, RCV_TXN.PRIMARY_QUANTITY) >= 0 ) ) ) ) AND (
  PA_PJC_CWK_UTILS.IS_RATE_BASED_LINE(NULL, PO_DIST.PO_DISTRIBUTION_ID) = 'N'
  OR PA_PJC_CWK_UTILS.IS_CWK_TC_XFACE_ALLOWED(PO_DIST.PROJECT_ID) = 'N' OR
  (PA_PJC_CWK_UTILS.IS_RATE_BASED_LINE(NULL, PO_DIST.PO_DISTRIBUTION_ID) =
  'Y' AND NVL(RCV_SUB.ENTERED_NR_TAX,0) <> 0 ) ) ) AND
  RCV_SUB.PA_ADDITION_FLAG IN ('N','I') AND RCV_SUB.ACTUAL_FLAG = 'A' AND
  RCV_SUB.CODE_COMBINATION_ID IN ( SELECT /*+ leading ( po_dist ) index (
  po_dist , PO_DISTRIBUTIONS_N15 ) cardinality(po_dist,1) */
  PO_DIST.CODE_COMBINATION_ID FROM PO_DISTRIBUTIONS PO_DIST WHERE
  NVL(PO_DIST.DISTRIBUTION_TYPE,'XXX') <> 'PREPAYMENT' AND PO_DIST.PROJECT_ID
  > 0 AND PO_DIST.ACCRUE_ON_RECEIPT_FLAG= 'Y' )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 40.63 822.45 93542 297362 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 40.63 822.45 93542 297362 0 0

Total elapsed time: 43990.21 seconds ~ 12 hours & 22 minutes


 ### Business Impact ###
As we are in the upgrade phase from 11i to R12.
Milestone Date : Aug-31-2015

Cause

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