PAAPIMP_SI PRC: Interface Supplier Costs Has Performance Issue For Single Project (Doc ID 2692706.1)

Last updated on NOVEMBER 17, 2022

Applies to:

Symptoms

 12.2.8 version, Purchase to Pay Flow

ACTUAL BEHAVIOR
---------------
Upgraded instance from 12.1.3 to 12.2.8 instance.
In 12.1.3 PRC: Interface Supplier Costs process completes in less than 30-40 seconds for single project and in 12.2.8 instance the program is taking 20 minutes for single project

Following SQL is performing poor

SQL ID: 0yx4rvnhkbf8x Plan Hash: 219264287

UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL DIST SET DIST.PA_ADDITION_FLAG = 'N' ,
  DIST.REQUEST_ID = :B3
WHERE
 DIST.PA_ADDITION_FLAG IN ('O','W') AND DIST.POSTED_FLAG||''= 'Y' AND
  DIST.PROJECT_ID >0 AND NOT EXISTS ( SELECT 'X' FROM
  PA_EXPENDITURE_ITEMS_ALL EI WHERE EI.DOCUMENT_HEADER_ID = DIST.INVOICE_ID
  AND EI.DOCUMENT_DISTRIBUTION_ID = DIST.INVOICE_DISTRIBUTION_ID AND
  EI.DOCUMENT_DISTRIBUTION_TYPE = 'SELF_ASSESSED_TAX' AND
  EI.TRANSACTION_SOURCE IN ('AP INVOICE','AP VARIANCE','AP NRTAX','AP
  EXPENSE')) AND TRUNC(DIST.EXPENDITURE_ITEM_DATE) <= TRUNC(NVL(:B2 ,
  DIST.EXPENDITURE_ITEM_DATE)) AND TRUNC(DIST.ACCOUNTING_DATE) <=
  TRUNC(NVL(:B1 ,DIST.ACCOUNTING_DATE))

EXPECTED BEHAVIOR
-----------------------
Expected to complete in less than a minute.

Changes

Cause

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