Performance Issue When Downloading Budget To Excel For Financial Plan Using WebAdi

(Doc ID 2021019.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Project Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

Unable to edit a budget in Excel using Web ADI.
Screen freezes and then times out.

Query with performance issue
SELECT PRA.RESOURCE_ASSIGNMENT_ID RESOURCE_ASSIGNMENT_ID, NVL(PE.NAME,
PPA.LONG_NAME) TASK_NAME, NVL(PE.ELEMENT_NUMBER,PPA.SEGMENT1) TASK_NUMBER,
NVL(PEV.DISPLAY_SEQUENCE,-1) TASK_DISPLAY_SEQUENCE, PRLM.ALIAS
RESOURCE_NAME, PRA.RESOURCE_LIST_MEMBER_ID, UOM.MEANING UNIT_OF_MEASURE,
PRA.SPREAD_CURVE_ID SPREAD_CURVE_ID, PSC.NAME SPREAD_CURVE,
PRA.PLANNING_START_DATE, PRA.PLANNING_END_DATE, PRA.MFC_COST_TYPE_ID
MFC_COST_TYPE_ID, CCT.COST_TYPE MFC_COST_TYPE, PRA.ETC_METHOD_CODE
ETC_METHOD_CODE, ETC.MEANING ETC_METHOD, PEV.PROJ_ELEMENT_ID
PROJECT_ELEMENT_ID, DECODE(PRA.TRANSACTION_SOURCE_CODE,NULL,NULL, (SELECT
MEANING
FROM
PA_LOOKUPS WHERE LOOKUP_TYPE='PA_FP_FCST_GEN_SRC_ALL' AND LOOKUP_CODE=
NVL(PRA.TRANSACTION_SOURCE_CODE, ( SELECT LOOKUP_CODE FROM PA_LOOKUPS WHERE
LOOKUP_TYPE='PA_FP_FCST_GEN_SRC_ALL' AND ROWNUM=1)))) ETC_SOURCE,
PFTC.TXN_CURRENCY_CODE TXN_CURRENCY_CODE, PFTC.PROJECT_COST_EXCHANGE_RATE
PROJECT_COST_EXCHANGE_RATE, PFTC.PROJECT_REV_EXCHANGE_RATE
PROJECT_REV_EXCHANGE_RATE, PFTC.PROJFUNC_COST_EXCHANGE_RATE
PROJFUNC_COST_EXCHANGE_RATE, PFTC.PROJFUNC_REV_EXCHANGE_RATE
PROJFUNC_REV_EXCHANGE_RATE FROM PA_RESOURCE_ASSIGNMENTS PRA,
PA_RESOURCE_ASGN_CURR PRAC, PA_FP_TXN_CURRENCIES PFTC, PA_PROJ_ELEMENTS PE,
PA_PROJ_ELEMENT_VERSIONS PEV, PA_RESOURCE_LIST_MEMBERS PRLM, PA_LOOKUPS UOM,
 PA_SPREAD_CURVES_TL PSC, CST_COST_TYPES CCT, PA_LOOKUPS ETC,
PA_PROJECTS_ALL PPA WHERE PRA.BUDGET_VERSION_ID = :B4 AND
PRAC.BUDGET_VERSION_ID = PRA.BUDGET_VERSION_ID AND
PRAC.RESOURCE_ASSIGNMENT_ID = PRA.RESOURCE_ASSIGNMENT_ID AND
PFTC.FIN_PLAN_VERSION_ID = PRAC.BUDGET_VERSION_ID AND
PFTC.TXN_CURRENCY_CODE = PRAC.TXN_CURRENCY_CODE AND PFTC.PROJ_FP_OPTIONS_ID
= :B3 AND PRA.RESOURCE_LIST_MEMBER_ID = PRLM.RESOURCE_LIST_MEMBER_ID AND
DECODE(PRA.TASK_ID,0,PEV.PROJ_ELEMENT_ID,PRA.TASK_ID)=PEV.PROJ_ELEMENT_ID
AND DECODE(PRA.TASK_ID,0,PEV.PARENT_STRUCTURE_VERSION_ID,
PEV.ELEMENT_VERSION_ID)=PEV.ELEMENT_VERSION_ID AND PEV.PROJ_ELEMENT_ID =
PE.PROJ_ELEMENT_ID(+) AND PE.OBJECT_TYPE(+)='PA_TASKS' AND
PEV.PARENT_STRUCTURE_VERSION_ID = :B2 AND NVL(PRA.SPREAD_CURVE_ID,1) =
PSC.SPREAD_CURVE_ID AND ETC.LOOKUP_TYPE = 'PA_FP_ETC_METHOD' AND
ETC.LOOKUP_CODE(+) = PRA.ETC_METHOD_CODE AND CCT.COST_TYPE_ID(+) =
PRA.MFC_COST_TYPE_ID AND UOM.LOOKUP_TYPE = 'UNIT' AND UOM.LOOKUP_CODE =
NVL(PRLM.UNIT_OF_MEASURE,'HOURS') AND PSC.LANGUAGE = USERENV('LANG') AND
PPA.PROJECT_ID = :B1 ORDER BY TASK_DISPLAY_SEQUENCE

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