View: PA_TASK_DET_EFFORT_COST_V

Product:	PA - Projects
Description:	PA_LOOKUPS is a view of system defined lookup codes and their corresponding meaning for Oracle Projects. This view will display the meani ng in the language of the session.
Implementation/DBA Data:	APPS.PA_TASK_DET_EFFORT_COST_V

View Text

SELECT /*+ INDEX(PJI_FM_XBS_ACCUM_TMP1 PJI_FM_XBS_ACCUM_TMP1_N1)*/ PPV.PROJECT_ID
, PPV.ELEMENT_VERSION_ID
, PPV.PROJ_ELEMENT_ID
, PPV.OBJECT_TYPE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.LABOR_HOURS
, PFXAT.EQUIPMENT_HOURS) PLANNED_EFFORT
, NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0) ACTUAL_EFFORT_TO_DATE
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE( ( NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0) +NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0) )
, PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0))
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, PPR.SUBPRJ_PPL_ETC_EFFORT
, PPR.SUBPRJ_EQPMT_ETC_EFFORT
, NULL
, NULL
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, DECODE(PPWA.WP_ENABLE_VERSION_FLAG
, 'Y'
, 'PUBLISH'
, 'WORKING')) ) PERCENT_COMPLETE_EFFORT
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( ( NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0) +NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0) )
, ( NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0)) ) PERCENT_SPENT_EFFORT
, PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0))
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, PPR.SUBPRJ_PPL_ETC_EFFORT
, PPR.SUBPRJ_EQPMT_ETC_EFFORT
, NULL
, NULL
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, DECODE(PPWA.WP_ENABLE_VERSION_FLAG
, 'Y'
, 'PUBLISH'
, 'WORKING')) ETC_EFFORT
, NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0) +NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0))
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, PPR.SUBPRJ_PPL_ETC_EFFORT
, PPR.SUBPRJ_EQPMT_ETC_EFFORT
, NULL
, NULL
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, DECODE(PPWA.WP_ENABLE_VERSION_FLAG
, 'Y'
, 'PUBLISH'
, 'WORKING')) EAC_EFFORT
, PFXAT.PRJ_BRDN_COST PLANNED_COST
, NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0) ACTUAL_COST_TO_DATE
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE( (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PFXAT.PRJ_BRDN_COST
, PPR.PPL_ETC_COST_PC
, PPR.EQPMT_ETC_COST_PC
, PPR.OTH_ETC_COST_PC
, PPR.SUBPRJ_PPL_ETC_COST_PC
, PPR.SUBPRJ_EQPMT_ETC_COST_PC
, PPR.SUBPRJ_OTH_ETC_COST_PC
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, DECODE(PPWA.WP_ENABLE_VERSION_FLAG
, 'Y'
, 'PUBLISH'
, 'WORKING')) ) PERCENT_COMPLETE_COST
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, (NVL(PFXAT.PRJ_BRDN_COST
, 0)) ) PERCENT_SPENT_COST
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PFXAT.PRJ_BRDN_COST
, PPR.PPL_ETC_COST_PC
, PPR.EQPMT_ETC_COST_PC
, PPR.OTH_ETC_COST_PC
, PPR.SUBPRJ_PPL_ETC_COST_PC
, PPR.SUBPRJ_EQPMT_ETC_COST_PC
, PPR.SUBPRJ_OTH_ETC_COST_PC
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, DECODE(PPWA.WP_ENABLE_VERSION_FLAG
, 'Y'
, 'PUBLISH'
, 'WORKING')) ETC_COST
, NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PFXAT.PRJ_BRDN_COST
, PPR.PPL_ETC_COST_PC
, PPR.EQPMT_ETC_COST_PC
, PPR.OTH_ETC_COST_PC
, PPR.SUBPRJ_PPL_ETC_COST_PC
, PPR.SUBPRJ_EQPMT_ETC_COST_PC
, PPR.SUBPRJ_OTH_ETC_COST_PC
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, DECODE(PPWA.WP_ENABLE_VERSION_FLAG
, 'Y'
, 'PUBLISH'
, 'WORKING')) EAC_COST
, PL.MEANING HOURS
, PPA.PROJECT_CURRENCY_CODE
, DECODE(PPR.AS_OF_DATE
, NULL
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPV.PROJ_ELEMENT_ID
, PA_PROGRESS_UTILS.GET_PROG_DT_CLOSEST_TO_SYS_DT(PPV.PROJECT_ID
, PPV.PROJ_ELEMENT_ID)
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)) BUDGETED_COST_OF_WORK_SCHEDULE
, ROUND(DECODE(PPR.TASK_WT_BASIS_CODE
, 'EFFORT'
, (NVL(PPR.EARNED_VALUE
, 0)/DECODE((NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0))
, 0
, 1
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0))))
, (NVL(PPR.EARNED_VALUE
, 0)/DECODE((NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0))
, 0
, 1
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)))))
, 2) COST_PERFORMANCE_INDEX
, DECODE(PPR.TASK_WT_BASIS_CODE
, 'EFFORT'
, ROUND(NVL(PPR.EARNED_VALUE
, 0)-(NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0))
, 2)
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(NVL(PPR.EARNED_VALUE
, 0)-(NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0))
, PPA.PROJECT_CURRENCY_CODE)) COST_VARIANCE
, PPR.EARNED_VALUE EARNED_VALUE
, DECODE(PPR.TASK_WT_BASIS_CODE
, 'COST'
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PFXAT.PRJ_BRDN_COST
, PPR.PPL_ETC_COST_PC
, PPR.EQPMT_ETC_COST_PC
, PPR.OTH_ETC_COST_PC
, PPR.SUBPRJ_PPL_ETC_COST_PC
, PPR.SUBPRJ_EQPMT_ETC_COST_PC
, PPR.SUBPRJ_OTH_ETC_COST_PC
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, DECODE(PPWA.WP_ENABLE_VERSION_FLAG
, 'Y'
, 'PUBLISH'
, 'WORKING')))
, 'EFFORT'
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0))
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, PPR.SUBPRJ_PPL_ETC_EFFORT
, PPR.SUBPRJ_EQPMT_ETC_EFFORT
, NULL
, NULL
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, DECODE(PPWA.WP_ENABLE_VERSION_FLAG
, 'Y'
, 'PUBLISH'
, 'WORKING')))) ESTIMATE_AT_COMPLETION
, PPR.PERCENT_COMPLETE_ID PERCENT_COMPLETE_ID
, NVL(PPR.COMPLETED_PERCENTAGE
, PPR.EFF_ROLLUP_PERCENT_COMP) PHYSICAL_PERCENT_COMPLETE
, NVL( PPR.TASK_WT_BASIS_CODE
, PPPA.TASK_WEIGHT_BASIS_CODE ) PROGRESS_ROLLUP_METHOD
, PA_PROGRESS_UTILS.RETURN_START_END_DATE(PPVSCH.SCHEDULED_START_DATE
, PPE.BASELINE_START_DATE
, PPV.PROJECT_ID
, PPV.PROJ_ELEMENT_ID
, 'PA_TASKS'
, 'S')+ ((PA_PROGRESS_UTILS.RETURN_START_END_DATE(PPVSCH.SCHEDULED_FINISH_DATE
, PPE.BASELINE_FINISH_DATE
, PPV.PROJECT_ID
, PPV.PROJ_ELEMENT_ID
, 'PA_TASKS'
, 'E')- PA_PROGRESS_UTILS.RETURN_START_END_DATE(PPVSCH.SCHEDULED_START_DATE
, PPE.BASELINE_START_DATE
, PPV.PROJECT_ID
, PPV.PROJ_ELEMENT_ID
, 'PA_TASKS'
, 'S')) /DECODE(ROUND((NVL(PPR.EARNED_VALUE
, 0)/DECODE(NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)
, 0
, 1
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)))
, 2)
, 0
, 1
, ROUND((NVL(PPR.EARNED_VALUE
, 0)/DECODE(NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)
, 0
, 1
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)))
, 2))) SCHEDULE_AT_COMPLETION
, ROUND((NVL(PPR.EARNED_VALUE
, 0)/DECODE(NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)
, 0
, 1
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)))
, 2) SCHEDULE_PERFORMANCE_INDEX
, NVL(PPR.EARNED_VALUE
, 0)-NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0) SCHEDULE_VARIANCE
, PPA.PROJFUNC_CURRENCY_CODE PROJFUNC_CURRENCY_CODE
, PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.BASE_LABOR_HOURS
, PFXAT.BASE_EQUIP_HOURS) BASELINE_EFFORT
, PFXAT.PRJ_BASE_BRDN_COST BASELINE_COST
, PPR.EFF_ROLLUP_PERCENT_COMP ROLLUP_PERCENT_COMPLETE
, DECODE(PPE.OBJECT_TYPE
, 'PA_STRUCTURES'
, PPPA.PERCENT_COMP_ENABLE_FLAG
, DECODE(PTT.PERCENT_COMP_ENABLE_FLAG
, 'Y'
, DECODE(PPPA.PERCENT_COMP_ENABLE_FLAG
, 'Y'
, 'Y'
, 'N')
, 'N')) PERCENT_COMP_ENABLED_FLAG
, DECODE(PPE.OBJECT_TYPE
, 'PA_TASKS'
, PTT.PROG_ENTRY_ENABLE_FLAG
, 'Y') PROGRESS_ENTRY_ENABLED_FLAG
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_LOOKUPS PL
, PJI_FM_XBS_ACCUM_TMP1 PFXAT
, PA_PROJ_PROGRESS_ATTR PPPA
, PA_PROJ_WORKPLAN_ATTR PPWA
, PA_TASK_TYPES PTT
, PA_PROGRESS_ROLLUP PPR
, PA_PROJ_ELEM_VER_STRUCTURE PPVS
, PA_PROJ_ELEM_VER_SCHEDULE PPVSCH
, PA_PROJ_ELEMENT_VERSIONS PPV
WHERE PPV.PROJECT_ID = PPA.PROJECT_ID
AND PPV.OBJECT_TYPE IN( 'PA_STRUCTURES'
, 'PA_TASKS' )
AND PL.LOOKUP_TYPE = 'UNIT'
AND PL.LOOKUP_CODE = 'HOURS'
AND PFXAT.PROJECT_ID(+) = PPV.PROJECT_ID
AND PFXAT.STRUCT_VERSION_ID(+) = PPV.PARENT_STRUCTURE_VERSION_ID
AND PFXAT.PROJECT_ELEMENT_ID(+) = PPV.PROJ_ELEMENT_ID
AND PFXAT.CALENDAR_TYPE(+) = 'A'
AND PFXAT.PLAN_VERSION_ID(+) > 0
AND PFXAT.TXN_CURRENCY_CODE(+) IS NULL
AND PPV.PROJECT_ID = PPVSCH.PROJECT_ID
AND PPV.ELEMENT_VERSION_ID = PPVSCH.ELEMENT_VERSION_ID
AND PPPA.PROJECT_ID = PPE.PROJECT_ID
AND PPPA.OBJECT_ID = DECODE(PPE.OBJECT_TYPE
, 'PA_TASKS'
, PPE.PARENT_STRUCTURE_ID
, 'PA_STRUCTURES'
, PPE.PROJ_ELEMENT_ID)
AND PPPA.STRUCTURE_TYPE = 'WORKPLAN'
AND PPV.PARENT_STRUCTURE_VERSION_ID = PPVS.ELEMENT_VERSION_ID
AND PPV.PROJECT_ID = PPVS.PROJECT_ID
AND PPVS.STATUS_CODE = 'STRUCTURE_PUBLISHED'
AND PPVS.PROJ_ELEMENT_ID = PPWA.PROJ_ELEMENT_ID
AND PPVS.PROJECT_ID = PPWA.PROJECT_ID
AND PPV.PROJECT_ID = PPE.PROJECT_ID
AND PPV.PROJ_ELEMENT_ID = PPE.PROJ_ELEMENT_ID
AND PPV.OBJECT_TYPE = PPE.OBJECT_TYPE
AND PPE.TYPE_ID = PTT.TASK_TYPE_ID(+)
AND PTT.OBJECT_TYPE (+) = 'PA_TASKS'
AND PPR.CURRENT_FLAG (+) <> 'W'
AND PPR.PROJECT_ID (+) = PPV.PROJECT_ID
AND PPR.OBJECT_ID (+) = PPV.PROJ_ELEMENT_ID
AND PPR.STRUCTURE_TYPE (+) = 'WORKPLAN'
AND PPR.STRUCTURE_VERSION_ID (+) IS NULL
AND PPR.OBJECT_VERSION_ID (+) <= PPV.ELEMENT_VERSION_ID
AND NVL(PPR.AS_OF_DATE
, TRUNC(SYSDATE)) = (SELECT NVL(MAX(PPR2.AS_OF_DATE)
, TRUNC(SYSDATE))
FROM PA_PROGRESS_ROLLUP PPR2
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_STRUCTURE PPEVS
WHERE PPR2.OBJECT_ID = PPV.PROJ_ELEMENT_ID
AND PPR2.PROJ_ELEMENT_ID = PPV.PROJ_ELEMENT_ID
AND PPR2.PROJECT_ID = PPV.PROJECT_ID
AND PPR2.OBJECT_TYPE = PPV.OBJECT_TYPE
AND PPR2.STRUCTURE_TYPE = 'WORKPLAN'
AND PPR2.STRUCTURE_VERSION_ID IS NULL
AND PPR2.CURRENT_FLAG <> 'W'
AND PPR2.OBJECT_VERSION_ID = PPEV.ELEMENT_VERSION_ID
AND PPEVS.PROJECT_ID = PPEV.PROJECT_ID
AND PPEVS.ELEMENT_VERSION_ID = PPEV.PARENT_STRUCTURE_VERSION_ID
AND PPEVS.STATUS_CODE = 'STRUCTURE_PUBLISHED'
AND PPEVS.PUBLISHED_DATE <= PPVS.PUBLISHED_DATE) UNION ALL SELECT /*+ INDEX(PJI_FM_XBS_ACCUM_TMP1 PJI_FM_XBS_ACCUM_TMP1_N1)*/ PPV.PROJECT_ID
, PPV.ELEMENT_VERSION_ID
, PPV.PROJ_ELEMENT_ID
, PPV.OBJECT_TYPE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.LABOR_HOURS
, PFXAT.EQUIPMENT_HOURS) PLANNED_EFFORT
, NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0) ACTUAL_EFFORT_TO_DATE
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE( ( NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0) +NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0) )
, PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0))
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, PPR.SUBPRJ_PPL_ETC_EFFORT
, PPR.SUBPRJ_EQPMT_ETC_EFFORT
, NULL
, NULL
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, 'WORKING') ) PERCENT_COMPLETE_EFFORT
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( ( NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0) +NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0) )
, ( NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0)) ) PERCENT_SPENT_EFFORT
, PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0))
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, PPR.SUBPRJ_PPL_ETC_EFFORT
, PPR.SUBPRJ_EQPMT_ETC_EFFORT
, NULL
, NULL
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, 'WORKING') ETC_EFFORT
, NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0) +NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0))
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, PPR.SUBPRJ_PPL_ETC_EFFORT
, PPR.SUBPRJ_EQPMT_ETC_EFFORT
, NULL
, NULL
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, 'WORKING') EAC_EFFORT
, PFXAT.PRJ_BRDN_COST PLANNED_COST
, NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0) ACTUAL_COST_TO_DATE
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE( (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PFXAT.PRJ_BRDN_COST
, PPR.PPL_ETC_COST_PC
, PPR.EQPMT_ETC_COST_PC
, PPR.OTH_ETC_COST_PC
, PPR.SUBPRJ_PPL_ETC_COST_PC
, PPR.SUBPRJ_EQPMT_ETC_COST_PC
, PPR.SUBPRJ_OTH_ETC_COST_PC
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, 'WORKING') ) PERCENT_COMPLETE_COST
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, (NVL(PFXAT.PRJ_BRDN_COST
, 0)) ) PERCENT_SPENT_COST
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PFXAT.PRJ_BRDN_COST
, PPR.PPL_ETC_COST_PC
, PPR.EQPMT_ETC_COST_PC
, PPR.OTH_ETC_COST_PC
, PPR.SUBPRJ_PPL_ETC_COST_PC
, PPR.SUBPRJ_EQPMT_ETC_COST_PC
, PPR.SUBPRJ_OTH_ETC_COST_PC
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, 'WORKING') ETC_COST
, NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PFXAT.PRJ_BRDN_COST
, PPR.PPL_ETC_COST_PC
, PPR.EQPMT_ETC_COST_PC
, PPR.OTH_ETC_COST_PC
, PPR.SUBPRJ_PPL_ETC_COST_PC
, PPR.SUBPRJ_EQPMT_ETC_COST_PC
, PPR.SUBPRJ_OTH_ETC_COST_PC
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, 'WORKING') EAC_COST
, PL.MEANING HOURS
, PPA.PROJECT_CURRENCY_CODE
, DECODE(PPR.AS_OF_DATE
, NULL
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPV.PROJ_ELEMENT_ID
, PA_PROGRESS_UTILS.GET_PROG_DT_CLOSEST_TO_SYS_DT(PPV.PROJECT_ID
, PPV.PROJ_ELEMENT_ID)
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)) BUDGETED_COST_OF_WORK_SCHEDULE
, ROUND(DECODE(PPR.TASK_WT_BASIS_CODE
, 'EFFORT'
, (NVL(PPR.EARNED_VALUE
, 0)/DECODE((NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0))
, 0
, 1
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0))))
, (NVL(PPR.EARNED_VALUE
, 0)/DECODE((NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0))
, 0
, 1
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)))))
, 2) COST_PERFORMANCE_INDEX
, DECODE(PPR.TASK_WT_BASIS_CODE
, 'EFFORT'
, ROUND(NVL(PPR.EARNED_VALUE
, 0)-(NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0))
, 2)
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1(NVL(PPR.EARNED_VALUE
, 0)-(NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0))
, PPA.PROJECT_CURRENCY_CODE)) COST_VARIANCE
, PPR.EARNED_VALUE EARNED_VALUE
, DECODE(PPR.TASK_WT_BASIS_CODE
, 'COST'
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PFXAT.PRJ_BRDN_COST
, PPR.PPL_ETC_COST_PC
, PPR.EQPMT_ETC_COST_PC
, PPR.OTH_ETC_COST_PC
, PPR.SUBPRJ_PPL_ETC_COST_PC
, PPR.SUBPRJ_EQPMT_ETC_COST_PC
, PPR.SUBPRJ_OTH_ETC_COST_PC
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, 'WORKING'))
, 'EFFORT'
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0))
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, PPR.SUBPRJ_PPL_ETC_EFFORT
, PPR.SUBPRJ_EQPMT_ETC_EFFORT
, NULL
, NULL
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, 'WORKING'))) ESTIMATE_AT_COMPLETION
, PPR.PERCENT_COMPLETE_ID PERCENT_COMPLETE_ID
, NVL(PPR.COMPLETED_PERCENTAGE
, PPR.EFF_ROLLUP_PERCENT_COMP) PHYSICAL_PERCENT_COMPLETE
, NVL( PPR.TASK_WT_BASIS_CODE
, PPPA.TASK_WEIGHT_BASIS_CODE ) PROGRESS_ROLLUP_METHOD
, PA_PROGRESS_UTILS.RETURN_START_END_DATE(PPVSCH.SCHEDULED_START_DATE
, PPE.BASELINE_START_DATE
, PPV.PROJECT_ID
, PPV.PROJ_ELEMENT_ID
, 'PA_TASKS'
, 'S')+ ((PA_PROGRESS_UTILS.RETURN_START_END_DATE(PPVSCH.SCHEDULED_FINISH_DATE
, PPE.BASELINE_FINISH_DATE
, PPV.PROJECT_ID
, PPV.PROJ_ELEMENT_ID
, 'PA_TASKS'
, 'E')- PA_PROGRESS_UTILS.RETURN_START_END_DATE(PPVSCH.SCHEDULED_START_DATE
, PPE.BASELINE_START_DATE
, PPV.PROJECT_ID
, PPV.PROJ_ELEMENT_ID
, 'PA_TASKS'
, 'S')) /DECODE(ROUND((NVL(PPR.EARNED_VALUE
, 0)/DECODE(NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)
, 0
, 1
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)))
, 2)
, 0
, 1
, ROUND((NVL(PPR.EARNED_VALUE
, 0)/DECODE(NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)
, 0
, 1
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)))
, 2))) SCHEDULE_AT_COMPLETION
, ROUND((NVL(PPR.EARNED_VALUE
, 0)/DECODE(NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)
, 0
, 1
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)))
, 2) SCHEDULE_PERFORMANCE_INDEX
, NVL(PPR.EARNED_VALUE
, 0)-NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPV.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0) SCHEDULE_VARIANCE
, PPA.PROJFUNC_CURRENCY_CODE PROJFUNC_CURRENCY_CODE
, PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.BASE_LABOR_HOURS
, PFXAT.BASE_EQUIP_HOURS) BASELINE_EFFORT
, PFXAT.PRJ_BASE_BRDN_COST BASELINE_COST
, PPR.EFF_ROLLUP_PERCENT_COMP ROLLUP_PERCENT_COMPLETE
, DECODE(PPE.OBJECT_TYPE
, 'PA_STRUCTURES'
, PPPA.PERCENT_COMP_ENABLE_FLAG
, DECODE(PTT.PERCENT_COMP_ENABLE_FLAG
, 'Y'
, DECODE(PPPA.PERCENT_COMP_ENABLE_FLAG
, 'Y'
, 'Y'
, 'N')
, 'N')) PERCENT_COMP_ENABLED_FLAG
, DECODE(PPE.OBJECT_TYPE
, 'PA_TASKS'
, PTT.PROG_ENTRY_ENABLE_FLAG
, 'Y') PROGRESS_ENTRY_ENABLED_FLAG
FROM PA_PROJECTS_ALL PPA
, PA_LOOKUPS PL
, PJI_FM_XBS_ACCUM_TMP1 PFXAT
, PA_PROGRESS_ROLLUP PPR
, PA_TASK_TYPES PTT
, PA_PROJ_PROGRESS_ATTR PPPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEM_VER_STRUCTURE PPVS
, PA_PROJ_ELEM_VER_SCHEDULE PPVSCH
, PA_PROJ_ELEMENT_VERSIONS PPV
WHERE PPV.PROJECT_ID = PPA.PROJECT_ID
AND PPV.OBJECT_TYPE IN( 'PA_STRUCTURES'
, 'PA_TASKS' )
AND PL.LOOKUP_TYPE = 'UNIT'
AND PL.LOOKUP_CODE = 'HOURS'
AND PFXAT.PROJECT_ID(+) = PPV.PROJECT_ID
AND PFXAT.STRUCT_VERSION_ID(+) = PPV.PARENT_STRUCTURE_VERSION_ID
AND PFXAT.PROJECT_ELEMENT_ID(+) = PPV.PROJ_ELEMENT_ID
AND PFXAT.CALENDAR_TYPE(+) = 'A'
AND PFXAT.PLAN_VERSION_ID(+) > 0
AND PFXAT.TXN_CURRENCY_CODE(+) IS NULL
AND PPV.PROJECT_ID = PPVSCH.PROJECT_ID
AND PPV.ELEMENT_VERSION_ID = PPVSCH.ELEMENT_VERSION_ID
AND PPPA.PROJECT_ID = PPE.PROJECT_ID
AND PPPA.OBJECT_ID = DECODE(PPE.OBJECT_TYPE
, 'PA_TASKS'
, PPE.PARENT_STRUCTURE_ID
, 'PA_STRUCTURES'
, PPE.PROJ_ELEMENT_ID)
AND PPPA.STRUCTURE_TYPE = 'WORKPLAN'
AND PPV.PARENT_STRUCTURE_VERSION_ID = PPVS.ELEMENT_VERSION_ID
AND PPV.PROJECT_ID = PPVS.PROJECT_ID
AND PPVS.STATUS_CODE <> 'STRUCTURE_PUBLISHED'
AND PPV.PROJECT_ID = PPE.PROJECT_ID
AND PPV.PROJ_ELEMENT_ID = PPE.PROJ_ELEMENT_ID
AND PPV.OBJECT_TYPE = PPE.OBJECT_TYPE
AND PPE.TYPE_ID = PTT.TASK_TYPE_ID(+)
AND PTT.OBJECT_TYPE (+) = 'PA_TASKS'
AND PPR.PROJECT_ID(+) = PPV.PROJECT_ID
AND PPR.OBJECT_ID(+) = PPV.PROJ_ELEMENT_ID
AND PPR.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PPR.CURRENT_FLAG(+) = 'Y'
AND PPR.STRUCTURE_VERSION_ID(+) = PPV.PARENT_STRUCTURE_VERSION_ID
AND PPR.OBJECT_VERSION_ID(+) = PPV.ELEMENT_VERSION_ID

Columns