R12: PAXPREPR, Performance Issue Creating Capital Projects (Doc ID 2491245.1)

Last updated on NOVEMBER 15, 2022

Applies to:

Symptoms

The following statement has been identified as causing poor performance in the PAXPREPR form:

INSERT INTO PA_PROJECT_ASSET_ASSIGNMENTS ( PROJECT_ASSET_ID, TASK_ID,
PROJECT_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
LAST_UPDATE_LOGIN) SELECT NEW_ASSET.PROJECT_ASSET_ID, NEW_TSK.TASK_ID,
NEW_ASSET.PROJECT_ID, SYSDATE, FND_GLOBAL.USER_ID, SYSDATE,
FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID FROM PA_PROJECT_ASSET_ASSIGNMENTS
ASSIGN, PA_PROJECT_ASSETS OLD_ASSET, PA_PROJECT_ASSETS NEW_ASSET, PA_TASKS
OLD_TSK, PA_TASKS NEW_TSK WHERE OLD_ASSET.PROJECT_ID = :B2 AND
OLD_ASSET.PROJECT_ASSET_ID = ASSIGN.PROJECT_ASSET_ID AND ASSIGN.TASK_ID =
OLD_TSK.TASK_ID AND ASSIGN.PROJECT_ID = OLD_TSK.PROJECT_ID AND
OLD_TSK.PROJECT_ID = OLD_ASSET.PROJECT_ID AND OLD_TSK.TASK_NUMBER =
NEW_TSK.TASK_NUMBER AND NEW_TSK.PROJECT_ID = NEW_ASSET.PROJECT_ID AND
OLD_ASSET.ASSET_NAME = NEW_ASSET.ASSET_NAME AND NEW_ASSET.PROJECT_ID = :B1
UNION SELECT ASSIGN.PROJECT_ASSET_ID, NEW_TSK.TASK_ID, :B1 , SYSDATE,
FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID, FND_GLOBAL.LOGIN_ID FROM
PA_PROJECT_ASSET_ASSIGNMENTS ASSIGN, PA_TASKS OLD_TSK, PA_TASKS NEW_TSK
WHERE ASSIGN.PROJECT_ID = :B2 AND ASSIGN.TASK_ID = OLD_TSK.TASK_ID AND
OLD_TSK.TASK_NUMBER = NEW_TSK.TASK_NUMBER AND NEW_TSK.PROJECT_ID = :B1 AND
ASSIGN.PROJECT_ASSET_ID = 0

Cause

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