PAAPIMPB: Performance Issue in PRC: Interface Supplier Cost Process when run with the Parameter AP Discount = 'Y' (Doc ID 2703090.1)

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') ) )


UPDATE /*+ 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 /*+ push_subq no_unnest */NULL FROM AP_PAYMENT_HISTORY_ALL
  HIST WHERE HIST.PAYMENT_HISTORY_ID = DIST.PAYMENT_HISTORY_ID AND
  HIST.POSTED_FLAG = 'Y') AND EXISTS(SELECT /*+ push_subq no_unnest */
  INV.INVOICE_ID FROM AP_INVOICES_ALL INV, 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 = :B7 AND
  INV.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' AND AID.PROJECT_ID > 0 AND
  AID.INVOICE_ID = AIP.INVOICE_ID AND TRUNC(AIP.ACCOUNTING_DATE) <=
  TRUNC(NVL(:B6 ,AIP.ACCOUNTING_DATE)) AND TRUNC(AID.EXPENDITURE_ITEM_DATE)
<=
   TRUNC(NVL(:B5 ,AID.EXPENDITURE_ITEM_DATE)))