REL11i AP: Performance Problem In Mass Additions Create Program

(Doc ID 317518.1)

Last updated on AUGUST 25, 2017

Applies to:

Oracle Payables - Version 11.5.10.0 to 11.5.10.0 [Release 11.5]
Information in this document applies to any platform.
Performance problem in Mass Additions Create program apmassab.pls
***Checked for relevance on 10-Dec-2015***

Symptoms

Mass additions create program is running for long.

The SQL creating the performance issue is given below.

UPDATE AP_INVOICE_DISTRIBUTIONS APID SET
APID.ASSETS_ADDITION_FLAG='N',APID.LAST_UPDATE_DATE=SYSDATE,APID.LAST_UPDATED_BY
=:b1,APID.LAST_UPDATE_LOGIN=FND_GLOBAL.LOGIN_ID,APID.PROGRAM_UPDATE_DATE=SYSDATE
,APID.PROGRAM_APPLICATION_ID=FND_GLOBAL.PROG_APPL_ID,APID.PROGRAM_ID=FND_GLOBAL.
CONC_PROGRAM_ID,APID.REQUEST_ID=:b2 WHERE ASSETS_ADDITION_FLAG = 'U'  AND
SET_OF_BOOKS_ID = :b3  AND POSTED_FLAG = 'Y'  AND
DECODE(NVL(APID.ACCRUAL_POSTED_FLAG,'N'),'N',APID.CASH_POSTED_FLAG,APID.ACCRUAL_
POSTED_FLAG) = 'Y'  AND ASSETS_TRACKING_FLAG = 'Y'  AND 'Y' =  (SELECT
AEH.GL_TRANSFER_FLAG  FROM AP_AE_HEADERS AEH  WHERE AEH.ACCOUNTING_EVENT_ID =
APID.ACCOUNTING_EVENT_ID  AND AEH.SET_OF_BOOKS_ID = APID.SET_OF_BOOKS_ID )  AND
ACCOUNTING_DATE <= :b4  AND DECODE(APID.PO_DISTRIBUTION_ID, NULL
,AP_MASS_ADDITIONS_CREATE_PKG.GET_ACCOUNT_SEGMENT(APID.DIST_CODE_COMBINATION_ID,
:b5,:b6),AP_MASS_ADDITIONS_CREATE_PKG.GET_PO_ACCOUNT_SEGMENT(APID.DIST_CODE_COMB
INATION_ID,APID.PO_DISTRIBUTION_ID,:b5,:b6)) NOT IN (SELECT ASSET_CLEARING_ACCT
  FROM FA_CATEGORY_BOOKS FCB,FA_BOOK_CONTROLS FAC  WHERE FCB.BOOK_TYPE_CODE =
FAC.BOOK_TYPE_CODE  AND FAC.BOOK_CLASS = 'CORPORATE'  AND FCB.BOOK_TYPE_CODE !=
:b9  UNION SELECT CIP_CLEARING_ACCT  FROM FA_CATEGORY_BOOKS
FCB,FA_BOOK_CONTROLS FAC  WHERE FCB.BOOK_TYPE_CODE = FAC.BOOK_TYPE_CODE  AND FAC.BOOK_CLASS = 'CORPORATE'  AND CIP_CLEARING_ACCT IS NOT NULL  AND
FCB.BOOK_TYPE_CODE != :b9 );

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms