View: PA_TASK_DET_EARNED_VALUES_V

View Text

SELECT PPV.PROJECT_ID
, PPV.ELEMENT_VERSION_ID
, PPV.PROJ_ELEMENT_ID
, PPV.OBJECT_TYPE
, PPV.PARENT_STRUCTURE_VERSION_ID
, PPR.PERCENT_COMPLETE_ID PERCENT_COMPLETE_ID
, 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
, PPVSCH.SCHEDULED_START_DATE
, PPVSCH.SCHEDULED_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
, PPVSCH.SCHEDULED_START_DATE
, PPVSCH.SCHEDULED_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)) BUDGETED_COST_OF_WORK_SCHEDULE
, 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
, PPVSCH.SCHEDULED_START_DATE
, PPVSCH.SCHEDULED_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
, PPVSCH.SCHEDULED_START_DATE
, PPVSCH.SCHEDULED_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)))
, 2) SCHEDULE_PERFORMANCE_INDEX
, 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_DATE_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_DATE_PC
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))))
, '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))))) ESTIMATE_AT_COMPLETION
, NVL(PPR.EFF_ROLLUP_PERCENT_COMP
, PPR.BASE_PERCENT_COMPLETE) PHYSICAL_PERCENT_COMPLETE
, PPR.EARNED_VALUE EARNED_VALUE
, ROUND(DECODE(PPR.TASK_WT_BASIS_CODE
, 'COST'
, (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) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_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)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))))
, 'EFFORT'
, (NVL(PPR.EARNED_VALUE
, 0)/DECODE((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))
, 0
, 1
, (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)))))
, 2) COST_PERFORMANCE_INDEX
, PPVSCH.SCHEDULED_START_DATE+((PPVSCH.SCHEDULED_FINISH_DATE-PPVSCH.SCHEDULED_START_DATE)/DECODE((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
, PPVSCH.SCHEDULED_START_DATE
, PPVSCH.SCHEDULED_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
, PPVSCH.SCHEDULED_START_DATE
, PPVSCH.SCHEDULED_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)))
, 0
, 1
, (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
, PPVSCH.SCHEDULED_START_DATE
, PPVSCH.SCHEDULED_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
, PPVSCH.SCHEDULED_START_DATE
, PPVSCH.SCHEDULED_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0))))) SCHEDULE_AT_COMPLETION
, PL.MEANING HOURS
, 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
, PPVSCH.SCHEDULED_START_DATE
, PPVSCH.SCHEDULED_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0) SCHEDULE_VARIANCE
, 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)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0)) COST_VARIANCE
, PPA.PROJECT_CURRENCY_CODE
, NVL( PPR.TASK_WT_BASIS_CODE
, PPPA.TASK_WEIGHT_BASIS_CODE ) PROGRESS_ROLLUP_METHOD
, PPA.PROJFUNC_CURRENCY_CODE PROJFUNC_CURRENCY_CODE
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENT_VERSIONS PPV
, PA_LOOKUPS PL
, PA_PROGRESS_ROLLUP PPR
, PA_PROJ_PROGRESS_ATTR PPPA
, PJI_FM_XBS_ACCUM_TMP1 PFXAT
, PA_PROJ_ELEM_VER_SCHEDULE PPVSCH
WHERE PPA.PROJECT_ID = PPV.PROJECT_ID
AND PPV.OBJECT_TYPE IN( 'PA_STRUCTURES'
, 'PA_TASKS' )
AND PL.LOOKUP_TYPE = 'UNIT'
AND PL.LOOKUP_CODE = 'HOURS'
AND PPR.PROJECT_ID(+) = PPV.PROJECT_ID
AND PPR.OBJECT_ID(+) = PPV.PROJ_ELEMENT_ID
AND PPR.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PPPA.PROJECT_ID(+) = PPV.PROJECT_ID
AND PPPA.OBJECT_ID(+) = PPV.PROJ_ELEMENT_ID
AND PPPA.STRUCTURE_TYPE(+) = 'WORKPLAN'
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 PPVSCH.PROJECT_ID = PPA.PROJECT_ID
AND PPVSCH.ELEMENT_VERSION_ID = PPV.ELEMENT_VERSION_ID

Columns