R12.2 Performance Issue with PA_RES_MANAGEMENT_AMG_PUB.DELETE_REQUIREMENTS

(Doc ID 2166312.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Project Foundation - Version 12.2.4 and later
Information in this document applies to any platform.

Symptoms

Performance issue with pa_res_management_amg_pub.delete_requirements. The below is the costly query:

SELECT RESOURCE_ASSIGNMENT_ID, WBS_ELEMENT_VERSION_ID, BUDGET_VERSION_ID,
  PROJECT_STRUCTURE_VERSION_ID
FROM
 ( (SELECT RA.RESOURCE_ASSIGNMENT_ID, RA.WBS_ELEMENT_VERSION_ID,
  BV.BUDGET_VERSION_ID, BV.PROJECT_STRUCTURE_VERSION_ID FROM
  PA_RESOURCE_ASSIGNMENTS RA ,PA_BUDGET_VERSIONS BV ,
  PA_PROJ_ELEM_VER_STRUCTURE EVS WHERE RA.PROJECT_ID = BV.PROJECT_ID AND
  BV.PROJECT_ID = EVS.PROJECT_ID AND BV.BUDGET_TYPE_CODE IS NULL AND
  RA.BUDGET_VERSION_ID = BV.BUDGET_VERSION_ID AND
  BV.PROJECT_STRUCTURE_VERSION_ID = EVS.ELEMENT_VERSION_ID AND
  RA.PROJECT_ASSIGNMENT_ID = :B1 AND EVS.STATUS_CODE = 'STRUCTURE_WORKING')
  UNION ALL (SELECT RA.RESOURCE_ASSIGNMENT_ID, RA.WBS_ELEMENT_VERSION_ID,
  BV.BUDGET_VERSION_ID, BV.PROJECT_STRUCTURE_VERSION_ID FROM
  PA_RESOURCE_ASSIGNMENTS RA ,PA_BUDGET_VERSIONS BV ,
  PA_PROJ_ELEM_VER_STRUCTURE EVS ,PA_PROJ_WORKPLAN_ATTR PWA WHERE
  PWA.WP_ENABLE_VERSION_FLAG = 'N' AND PWA.PROJECT_ID = RA.PROJECT_ID AND
  PWA.PROJ_ELEMENT_ID = EVS.PROJ_ELEMENT_ID AND RA.PROJECT_ID = BV.PROJECT_ID
  AND BV.PROJECT_ID = EVS.PROJECT_ID AND BV.BUDGET_TYPE_CODE IS NULL AND
  RA.BUDGET_VERSION_ID = BV.BUDGET_VERSION_ID AND
  BV.PROJECT_STRUCTURE_VERSION_ID = EVS.ELEMENT_VERSION_ID AND
  RA.PROJECT_ASSIGNMENT_ID = :B1 ) ) ORDER BY BUDGET_VERSION_ID,
  PROJECT_STRUCTURE_VERSION_ID

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