VIEW: APPS.PA_STRUCTURE_PUB_VERS_V

Source

View Text - Preformatted

SELECT /*+ INDEX(pji_fm_xbs_accum_tmp1 pji_fm_xbs_accum_tmp1_n1)*/ ppa.name, ppa.segment1, ele.proj_element_id, evr.element_version_id, stc.pev_structure_id, evr.project_id, ele.element_number, ele.name, stc.version_number, stc.name, stc.description, sch.scheduled_start_date, sch.scheduled_finish_date, sch.ESTIMATED_START_DATE, sch.ESTIMATED_FINISH_DATE, sch.ACTUAL_START_DATE, sch.ACTUAL_FINISH_DATE, stc.effective_date, stc.status_code, st1.project_status_name, stc.lock_status_code, st2.meaning, stc.locked_date, stc.locked_by_person_id, pp1.full_name, stc.published_date, stc.published_by_person_id, pp2.full_name, stc.original_flag, lk1.meaning, stc.current_flag, lk2.meaning, stc.latest_eff_published_flag, lk3.meaning, pst.STRUCTURE_TYPE, ele.record_version_number, evr.record_version_number, stc.record_version_number, sch.record_version_number, stc.wbs_record_version_number, NVL( ppru.PROGRESS_STATUS_CODE, ppru.eff_rollup_prog_stat_code ), PPS.PROJECT_STATUS_NAME, ppru.percent_complete_id, pps.STATUS_ICON_ACTIVE_IND, pps.STATUS_ICON_IND, ppru.estimated_start_date prog_estimated_start_date, ppru.estimated_finish_date prog_estimated_finish_date, ppru.actual_start_date prog_actual_start_date, ppru.actual_finish_date prog_actual_finish_date, NVL( ppru.COMPLETED_PERCENTAGE, ppru.eff_rollup_percent_comp ), ppru.as_of_date, ele.manager_person_id, papf.FULL_NAME, to_number(NULL) report_version_id, ele.baseline_start_date, ele.baseline_finish_date, sch.scheduled_start_date - ele.baseline_start_date, sch.scheduled_finish_date - ele.baseline_finish_date, sch.ESTIMATED_START_DATE - sch.scheduled_start_date, sch.ESTIMATED_FINISH_DATE - sch.scheduled_finish_date, sch.ACTUAL_START_DATE - sch.scheduled_start_date, sch.ACTUAL_FINISH_DATE - sch.scheduled_finish_date, stc.change_reason_code, lu.meaning, PA_PROGRESS_UTILS.GET_PRIOR_PERCENT_COMPLETE(ppa.project_id,ele.proj_element_id,ppru.as_of_date) Prior_percent_complete, sch.last_update_date, ppa.BASELINE_AS_OF_DATE, ppru.LAST_UPDATE_DATE, ppru.LAST_UPDATE_DATE, pa_progress_utils.calc_plan(pfxat.labor_hours, pfxat.equipment_hours) planned_effort, PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Structure(ele.proj_element_id,'FINANCIAL'), sch.calendar_id, jc.calendar_name, pa_progress_utils.calc_etc(pa_progress_utils.calc_plan(pfxat.labor_hours, pfxat.equipment_hours, null) , ppru.estimated_remaining_effort , ppru.eqpmt_etc_effort , null , ppru.subprj_ppl_etc_effort , ppru.subprj_eqpmt_etc_effort , null , null , pa_progress_utils.calc_act(ppru.ppl_act_effort_to_date , ppru.eqpmt_act_effort_to_date , null , ppru.subprj_ppl_act_effort , ppru.subprj_eqpmt_act_effort , null)) estimated_remaining_effort, NULL initial_progress_status_code, pppa.wq_enable_flag, pppa.percent_comp_enable_flag, pppa.remain_effort_enable_flag, pa_workplan_attr_utils.check_wp_versioning_enabled(ele.project_id), sch.duration, pa_proj_elements_utils.convert_hr_to_days(ele.baseline_duration), pa_proj_elements_utils.convert_hr_to_days(sch.estimated_duration), pa_proj_elements_utils.convert_hr_to_days(sch.actual_duration), ppwa.lifecycle_version_id, ppe2.name, ppe2.element_number, ppwa.current_phase_version_id, ppe3.name, ppe3.element_number, PA_PROJ_ELEMENTS_UTILS.check_child_element_exist(evr.element_version_id), stc.CURRENT_WORKING_FLAG, stc.SCHEDULE_DIRTY_FLAG, pfxat.equipment_hours planned_equip_effort, pfxat.prj_brdn_cost Planned_Cost, NVL(sch.SCHEDULED_START_DATE_ROLLUP,0) + sch.SCHEDULED_START_DATE SCHEDULED_START_ROLLUP_DATE, NVL(sch.SCHEDULED_FINISH_DATE_ROLLUP,0) + sch.SCHEDULED_FINISH_DATE SCHEDULED_FINISH_ROLLUP_DATE, NVL(sch.ESTIMATED_START_DATE_ROLLUP,0) + sch.ESTIMATED_START_DATE ESTIMATED_START_ROLLUP_DATE, NVL(sch.ESTIMATED_FINISH_DATE_ROLLUP,0) + sch.ESTIMATED_FINISH_DATE ESTIMATED_FINISH_ROLLUP_DATE, NVL(sch.ACTUAL_START_DATE_ROLLUP,0) + sch.ACTUAL_START_DATE ACTUAL_START_ROLLUP_DATE, NVL(sch.ACTUAL_FINISH_DATE_ROLLUP,0) + sch.ACTUAL_FINISH_DATE ACTUAL_FINISH_ROLLUP_DATE, pfxat.prj_raw_cost RAW_COST, pfxat.prj_brdn_cost Burdened_cost, pa_progress_utils.calc_act(ppru.ppl_act_effort_to_date, ppru.eqpmt_act_effort_to_date, null , ppru.subprj_ppl_act_effort, ppru.subprj_eqpmt_act_effort, null) Actual_Effort, pa_progress_utils.calc_act(null, ppru.eqpmt_act_effort_to_date, null , null, ppru.subprj_eqpmt_act_effort, null) Actual_equip_effort, ppa.project_currency_code, PA_RELATIONSHIP_UTILS.DISPLAY_PREDECESSORS(evr.element_version_id) predecessors, PA_PROGRESS_UTILS.Percent_Spent_Value((nvl(ppru.ppl_act_effort_to_date,0)+nvl(ppru.eqpmt_act_effort_to_date,0) +nvl(ppru.subprj_ppl_act_effort,0)+nvl(ppru.subprj_eqpmt_act_effort,0)), nvl(pfxat.labor_hours,0)+nvl(pfxat.equipment_hours,0) ) percent_Spent_Effort, PA_PROGRESS_UTILS.Percent_Spent_Value ((nvl(ppru.oth_act_cost_to_date_pc,0)+nvl(ppru.ppl_act_cost_to_date_pc,0) +nvl(ppru.eqpmt_act_cost_to_date_pc,0)+nvl(ppru.subprj_oth_act_cost_to_date_pc,0) +nvl(ppru.subprj_ppl_act_cost_pc,0)+nvl(ppru.subprj_eqpmt_act_cost_pc,0)), nvl(pfxat.prj_brdn_cost,0) ) percent_Spent_Cost, PA_PROGRESS_UTILS.Percent_Complete_Value((nvl(ppru.ppl_act_effort_to_date,0)+nvl(ppru.eqpmt_act_effort_to_date,0) +nvl(ppru.subprj_ppl_act_effort,0)+nvl(ppru.subprj_eqpmt_act_effort,0)), (nvl(ppru.estimated_remaining_effort,0)+nvl(ppru.eqpmt_etc_effort,0) +nvl(ppru.subprj_ppl_etc_effort,0)+nvl(ppru.subprj_eqpmt_etc_effort,0))) Percent_Complete_Effort, PA_PROGRESS_UTILS.Percent_Complete_Value((nvl(ppru.oth_act_cost_to_date_pc,0)+nvl(ppru.ppl_act_cost_to_date_pc,0) +nvl(ppru.eqpmt_act_cost_to_date_pc,0)+nvl(ppru.subprj_oth_act_cost_to_date_pc,0) +nvl(ppru.subprj_ppl_act_cost_pc,0)+nvl(ppru.subprj_eqpmt_act_cost_pc,0)), (nvl(ppru.oth_etc_cost_pc,0)+nvl(ppru.ppl_etc_cost_pc,0)+nvl(ppru.eqpmt_etc_cost_pc,0) +nvl(ppru.subprj_oth_etc_cost_pc,0)+nvl(ppru.subprj_ppl_etc_cost_pc,0) +nvl(ppru.subprj_eqpmt_etc_cost_pc,0))) Percent_Complete_Cost, trunc(ppru.actual_finish_date) - trunc(ppru.actual_start_date) Actual_Duration, trunc(sch.SCHEDULED_FINISH_DATE) - trunc(sysdate) Remaining_Duration, PA_PROJ_ELEMENTS_UTILS.GET_FND_LOOKUP_MEANING ( 'PA_SCHEDULE_CONSTRAINT_TYPE',sch.constraint_type_code ) Constraint_Type, sch.constraint_type_code, sch.Constraint_Date, sch.Early_Start_Date, sch.Early_Finish_Date, sch.Late_Start_Date, sch.Late_Finish_Date, sch.Free_Slack, sch.Total_Slack, DECODE(PA_PROJ_ELEMENTS_UTILS.is_summary_task_or_structure(evr.element_version_id),'Y','N','N',' ') Lowest_Task, /* Bug Fix 5406261 to_number ( null ) Estimated_Baseline_Start, to_number ( null ) Estimated_Baseline_Finish, */ (sch.ESTIMATED_START_DATE - ele.BASELINE_START_DATE) Estimated_Baseline_Start, (sch.ESTIMATED_FINISH_DATE - ele.BASELINE_FINISH_DATE) Estimated_Baseline_Finish, /* End of Bug Fix 5406261 */ to_number ( null ) Planned_Baseline_Start, to_number ( null ) Planned_Baseline_Finish, pa_progress_utils.calc_plan(pfxat.base_equip_hours, pfxat.base_labor_hours, null) Baseline_Effort, pa_progress_utils.calc_etc(pa_progress_utils.calc_plan(pfxat.labor_hours, pfxat.equipment_hours, null) , ppru.estimated_remaining_effort , ppru.eqpmt_etc_effort , null , ppru.subprj_ppl_etc_effort , ppru.subprj_eqpmt_etc_effort , null , null , pa_progress_utils.calc_act(ppru.ppl_act_effort_to_date , ppru.eqpmt_act_effort_to_date , null , ppru.subprj_ppl_act_effort , ppru.subprj_eqpmt_act_effort , null)) ETC_EFFORT, (nvl(ppru.ppl_act_effort_to_date,0) +nvl(ppru.eqpmt_act_effort_to_date,0) +nvl(ppru.subprj_ppl_act_effort,0) +nvl(ppru.subprj_eqpmt_act_effort,0) +pa_progress_utils.sum_etc_values((NVL(pfxat.labor_hours,0)+NVL(pfxat.equipment_hours,0)) ,ppru.estimated_remaining_effort ,ppru.eqpmt_etc_effort ,null ,ppru.subprj_ppl_etc_effort ,ppru.subprj_eqpmt_etc_effort ,null ,null ,(nvl(ppru.ppl_act_effort_to_date,0)+nvl(ppru.eqpmt_act_effort_to_date,0) +nvl(ppru.subprj_ppl_act_effort,0)+nvl(ppru.subprj_eqpmt_act_effort,0)) ,decode(nvl(ppwa.WP_ENABLE_VERSION_FLAG,'N'),'Y','PUBLISH','WORKING'))) Estimate_At_Completion_Effort, ((nvl(pfxat.base_labor_hours,0)+nvl(pfxat.base_equip_hours,0)) -(nvl(ppru.ppl_act_effort_to_date,0) +nvl(ppru.eqpmt_act_effort_to_date,0) +nvl(ppru.subprj_ppl_act_effort,0) +nvl(ppru.subprj_eqpmt_act_effort,0) +pa_progress_utils.sum_etc_values((NVL(pfxat.labor_hours,0)+NVL(pfxat.equipment_hours,0)) ,ppru.estimated_remaining_effort ,ppru.eqpmt_etc_effort ,null ,ppru.subprj_ppl_etc_effort ,ppru.subprj_eqpmt_etc_effort ,null ,null ,(nvl(ppru.ppl_act_effort_to_date,0)+nvl(ppru.eqpmt_act_effort_to_date,0) +nvl(ppru.subprj_ppl_act_effort,0)+nvl(ppru.subprj_eqpmt_act_effort,0)) ,decode(nvl(ppwa.WP_ENABLE_VERSION_FLAG,'N'),'Y','PUBLISH','WORKING')))) Variance_At_Completion_Effort, ((NVL(ppru.earned_value,0))-(nvl(ppru.ppl_act_effort_to_date,0)+ nvl(ppru.eqpmt_act_effort_to_date,0)+ nvl(ppru.subprj_ppl_act_effort,0)+ nvl(ppru.subprj_eqpmt_act_effort,0))) Effort_Variance, round((((NVL(ppru.earned_value,0))-(nvl(ppru.ppl_act_effort_to_date,0)+ nvl(ppru.eqpmt_act_effort_to_date,0)+ nvl(ppru.subprj_ppl_act_effort,0)+ nvl(ppru.subprj_eqpmt_act_effort,0)))/(DECODE(NVL(ppru.earned_value,0),0,1,NVL(ppru.earned_value,0)))),2) Effort_Variance_Percent, pa_progress_utils.calc_act(ppru.ppl_act_cost_to_date_pc , ppru.eqpmt_act_cost_to_date_pc , ppru.oth_act_cost_to_date_pc , ppru.subprj_ppl_act_cost_pc , ppru.subprj_eqpmt_act_cost_pc , ppru.subprj_oth_act_cost_to_date_pc) Actual_Cost, pfxat.prj_base_brdn_cost Baseline_Cost, (nvl(ppru.oth_act_cost_to_date_pc,0) +nvl(ppru.ppl_act_cost_to_date_pc,0) +nvl(ppru.eqpmt_act_cost_to_date_pc,0) +nvl(ppru.subprj_oth_act_cost_to_date_pc,0) +nvl(ppru.subprj_ppl_act_cost_pc,0) +nvl(ppru.subprj_eqpmt_act_cost_pc,0) +PA_PROGRESS_UTILS.sum_etc_values(pfxat.prj_brdn_cost ,ppru.ppl_etc_cost_pc ,ppru.eqpmt_etc_cost_pc ,ppru.oth_etc_cost_pc ,ppru.subprj_ppl_etc_cost_pc ,ppru.subprj_eqpmt_etc_cost_pc ,ppru.subprj_oth_etc_cost_pc ,null,(nvl(ppru.oth_act_cost_to_date_pc,0)+nvl(ppru.ppl_act_cost_to_date_pc,0) +nvl(ppru.eqpmt_act_cost_to_date_pc,0)+nvl(ppru.subprj_oth_act_cost_to_date_pc,0) +nvl(ppru.subprj_ppl_act_cost_pc,0)+nvl(ppru.subprj_eqpmt_act_cost_pc,0)) ,decode(nvl(ppwa.WP_ENABLE_VERSION_FLAG,'N'),'Y','PUBLISH','WORKING'))) Estimate_At_Completion_Cost, ((NVL(ppru.earned_value,0))-(nvl(ppru.oth_act_cost_to_date_pc,0)+ nvl(ppru.ppl_act_cost_to_date_pc,0)+ nvl(ppru.eqpmt_act_cost_to_date_pc,0)+ nvl(ppru.subprj_oth_act_cost_to_date_pc,0)+ nvl(ppru.subprj_ppl_act_cost_pc,0)+ nvl(ppru.subprj_eqpmt_act_cost_pc,0))) Cost_Variance, round((((NVL(ppru.earned_value,0))-(nvl(ppru.oth_act_cost_to_date_pc,0)+ nvl(ppru.ppl_act_cost_to_date_pc,0)+ nvl(ppru.eqpmt_act_cost_to_date_pc,0)+ nvl(ppru.subprj_oth_act_cost_to_date_pc,0)+ nvl(ppru.subprj_ppl_act_cost_pc,0)+ nvl(ppru.subprj_eqpmt_act_cost_pc,0)))/(DECODE(NVL(ppru.earned_value,0),0,1,NVL(ppru.earned_value,0)))),2) Cost_Variance_Percent, round((NVL(sch.wq_planned_quantity,0) - NVL(ppru.CUMULATIVE_WORK_QUANTITY,0)),2) ETC_Work_Quantity, pa_currency.round_trans_currency_amt((nvl(pfxat.prj_brdn_cost,0)/decode(nvl(ppru.cumulative_work_quantity,0),0,1,nvl(ppru.cumulative_work_quantity,0))),ppa.project_currency_code) Planned_Cost_Per_Unit, pa_currency.round_trans_currency_amt((NVL((NVL(ppru.oth_act_cost_to_date_pc,0)+ NVL(ppru.ppl_act_cost_to_date_pc,0)+ NVL(ppru.eqpmt_act_cost_to_date_pc,0)+ NVL(ppru.subprj_oth_act_cost_to_date_pc,0)+ NVL(ppru.subprj_ppl_act_cost_pc,0)+ NVL(ppru.subprj_eqpmt_act_cost_pc,0)),0)/DECODE(NVL(ppru.CUMULATIVE_WORK_QUANTITY,0),0,1,ppru.CUMULATIVE_WORK_QUANTITY)),ppa.project_currency_code) Actual_Cost_Per_Unit, round((NVL(NVL(ppru.CUMULATIVE_WORK_QUANTITY,0)-NVL(sch.WQ_PLANNED_QUANTITY,0),0)),2) Work_Quantity_Variance, round((((ppru.CUMULATIVE_WORK_QUANTITY-sch.WQ_PLANNED_QUANTITY)/DECODE(NVL(sch.WQ_PLANNED_QUANTITY,0),0,1,sch.WQ_PLANNED_QUANTITY))*100),2) Work_Quantity_Variance_Percent, ppru.earned_value Earned_Value, (nvl(ppru.earned_value,0)-nvl(pa_progress_utils.get_bcws(ppa.project_id, ppru.object_id, ele.proj_element_id, ppru.as_of_date, evr.parent_structure_version_id, pppa.task_weight_basis_code, ele.baseline_start_date, ele.baseline_finish_date,ppa.project_currency_code),0)) Schedule_Variance, (NVL(ppru.earned_value,0)-NVL((NVL(ppru.oth_act_cost_to_date_pc,0)+ NVL(ppru.ppl_act_cost_to_date_pc,0)+ NVL(ppru.eqpmt_act_cost_to_date_pc,0)+ NVL(ppru.subprj_oth_act_cost_to_date_pc,0)+ NVL(ppru.subprj_ppl_act_cost_pc,0)+ NVL(ppru.subprj_eqpmt_act_cost_pc,0)),0)) Earned_Value_Cost_Variance, (NVL(ppru.earned_value,0)-NVL(pa_progress_utils.get_bcws(ppa.project_id, ppru.object_id, ele.proj_element_id, ppru.as_of_date, evr.parent_structure_version_id, pppa.task_weight_basis_code, ele.baseline_start_date, ele.baseline_finish_date,ppa.project_currency_code),0)) Earned_Value_Schedule_Variance, ((nvl(pfxat.prj_base_brdn_cost,0)) -(nvl(ppru.oth_act_cost_to_date_pc,0) +nvl(ppru.ppl_act_cost_to_date_pc,0) +nvl(ppru.eqpmt_act_cost_to_date_pc,0) +nvl(ppru.subprj_oth_act_cost_to_date_pc,0) +nvl(ppru.subprj_ppl_act_cost_pc,0) +nvl(ppru.subprj_eqpmt_act_cost_pc,0) +PA_PROGRESS_UTILS.sum_etc_values(pfxat.prj_brdn_cost ,ppru.ppl_etc_cost_pc ,ppru.eqpmt_etc_cost_pc ,ppru.oth_etc_cost_pc ,ppru.subprj_ppl_etc_cost_pc ,ppru.subprj_eqpmt_etc_cost_pc ,ppru.subprj_oth_etc_cost_pc ,null,(nvl(ppru.oth_act_cost_to_date_pc,0)+nvl(ppru.ppl_act_cost_to_date_pc,0) +nvl(ppru.eqpmt_act_cost_to_date_pc,0)+nvl(ppru.subprj_oth_act_cost_to_date_pc,0) +nvl(ppru.subprj_ppl_act_cost_pc,0)+nvl(ppru.subprj_eqpmt_act_cost_pc,0)) ,decode(ppwa.wp_enable_version_flag,'Y','PUBLISH','WORKING')))) Variance_At_Completion_Cost, round((decode (ppru.task_wt_basis_code,'EFFORT' , (((nvl(pfxat.base_labor_hours,0)+nvl(pfxat.base_equip_hours,0))-ppru.earned_value)/decode(((nvl(pfxat.base_labor_hours,0) +nvl(pfxat.base_equip_hours,0))-(nvl(ppru.ppl_act_effort_to_date,0)+nvl(ppru.eqpmt_act_effort_to_date,0)+nvl(ppru.subprj_ppl_act_effort,0)+nvl(ppru.subprj_eqpmt_act_effort,0))), 0,1,((nvl(pfxat.base_labor_hours,0)+nvl(pfxat.base_equip_hours,0))-(nvl(ppru.ppl_act_effort_to_date,0)+nvl(ppru.eqpmt_act_effort_to_date,0)+nvl(ppru.subprj_ppl_act_effort,0) +nvl(ppru.subprj_eqpmt_act_effort,0))))) , ((nvl(pfxat.prj_base_brdn_cost,0)-ppru.earned_value)/decode((nvl(pfxat.prj_base_brdn_cost,0)-(nvl(ppru.oth_act_cost_to_date_pc,0) +nvl(ppru.ppl_act_cost_to_date_pc,0)+nvl(ppru.eqpmt_act_cost_to_date_pc,0)+nvl(ppru.subprj_oth_act_cost_to_date_pc,0)+nvl(ppru.subprj_ppl_act_cost_pc,0) +nvl(ppru.subprj_eqpmt_act_cost_pc,0))),0,1,(nvl(pfxat.prj_base_brdn_cost,0)-(nvl(ppru.oth_act_cost_to_date_pc,0) +nvl(ppru.ppl_act_cost_to_date_pc,0)+nvl(ppru.eqpmt_act_cost_to_date_pc,0)+nvl(ppru.subprj_oth_act_cost_to_date_pc,0)+nvl(ppru.subprj_ppl_act_cost_pc,0) +nvl(ppru.subprj_eqpmt_act_cost_pc,0))))) )),2) To_Complete_Performance_Index, nvl(pa_progress_utils.get_bcws(ppa.project_id, ppru.object_id, ele.proj_element_id, ppru.as_of_date, evr.parent_structure_version_id, pppa.task_weight_basis_code, ele.baseline_start_date, ele.baseline_finish_date,ppa.project_currency_code),0) Budgeted_Cost_Of_Work_Sch, round((nvl(ppru.earned_value,0)/decode(nvl(pa_progress_utils.get_bcws(ppa.project_id, ppru.object_id, ele.proj_element_id, ppru.as_of_date, evr.parent_structure_version_id, pppa.task_weight_basis_code, ele.baseline_start_date, ele.baseline_finish_date,ppa.project_currency_code),0),0,1,nvl(pa_progress_utils.get_bcws(ppa.project_id, ppru.object_id, ele.proj_element_id, ppru.as_of_date, evr.parent_structure_version_id, pppa.task_weight_basis_code, ele.baseline_start_date, ele.baseline_finish_date,ppa.project_currency_code),0))),2) Schedule_Performance_Index, round((decode(ppru.task_wt_basis_code,'EFFORT' ,(nvl(ppru.earned_value,0)/decode((nvl(ppru.ppl_act_effort_to_date,0)+nvl(ppru.eqpmt_act_effort_to_date,0)+nvl(ppru.subprj_ppl_act_effort,0)+nvl(ppru.subprj_eqpmt_act_effort,0)),0,1, (nvl(ppru.ppl_act_effort_to_date,0)+nvl(ppru.eqpmt_act_effort_to_date,0)+nvl(ppru.subprj_ppl_act_effort,0)+nvl(ppru.subprj_eqpmt_act_effort,0)))) ,(nvl(ppru.earned_value,0)/decode((nvl(ppru.oth_act_cost_to_date_pc,0)+nvl(ppru.ppl_act_cost_to_date_pc,0)+nvl(ppru.eqpmt_act_cost_to_date_pc,0) +nvl(ppru.subprj_oth_act_cost_to_date_pc,0)+nvl(ppru.subprj_ppl_act_cost_pc,0)+nvl(ppru.subprj_eqpmt_act_cost_pc,0)),0,1, (nvl(ppru.oth_act_cost_to_date_pc,0)+nvl(ppru.ppl_act_cost_to_date_pc,0) +nvl(ppru.eqpmt_act_cost_to_date_pc,0)+nvl(ppru.subprj_oth_act_cost_to_date_pc,0)+nvl(ppru.subprj_ppl_act_cost_pc,0)+nvl(ppru.subprj_eqpmt_act_cost_pc,0)))) )),2) Cost_Performance_Index, PA_PROJ_STRUC_MAPPING_UTILS.GET_MAPPED_FIN_TASK_ID(evr.element_version_id,ppa.structure_sharing_code) Mapped_Financial_Task, PA_DELIVERABLE_UTILS.GET_ASSOCIATED_DELIVERABLES (ele.proj_element_id) Deliverables, stc.date_prog_applied_on_wver, pa_progress_utils.latest_published_progress_date(evr.project_id,ppru.structure_type), PA_PROJ_STRUC_MAPPING_UTILS.GET_MAPPED_FIN_TASK_NAME(evr.element_version_id,ppa.structure_sharing_code), pa_progress_utils.calc_etc(pfxat.prj_brdn_cost , ppru.ppl_etc_cost_pc , ppru.eqpmt_etc_cost_pc , ppru.oth_etc_cost_pc , ppru.subprj_ppl_etc_cost_pc , ppru.subprj_eqpmt_etc_cost_pc , ppru.subprj_oth_etc_cost_pc , null , pa_progress_utils.calc_act(ppru.ppl_act_cost_to_date_pc , ppru.eqpmt_act_cost_to_date_pc , ppru.oth_act_cost_to_date_pc , ppru.subprj_ppl_act_cost_pc , ppru.subprj_eqpmt_act_cost_pc , ppru.subprj_oth_act_cost_to_date_pc)) etc_cost ,ppru.PROGRESS_ROLLUP_ID ,ppru.BASE_PERCENT_COMPLETE ,nvl(pfxat.labor_hours,0)+nvl(pfxat.equipment_hours,0) - (nvl(pfxat.base_labor_hours,0)+nvl(pfxat.base_equip_hours,0)) PLANNED_BASELINE_EFFORT_VAR ,nvl(pfxat.prj_brdn_cost,0) - nvl(pfxat.prj_base_brdn_cost,0) PLANNED_BASELINE_COST_VAR from pa_proj_elem_ver_schedule sch, pa_project_statuses st1, pa_lookups st2, pa_lookups lu, per_all_people_f pp1, per_all_people_f pp2, fnd_lookups lk1, fnd_lookups lk2, fnd_lookups lk3, pa_proj_structure_types ppst, pa_structure_types pst, per_all_people_f papf, jtf_calendars_tl jc, pa_project_statuses pps, pa_progress_rollup ppru, pa_proj_progress_attr pppa, pa_projects_all ppa, pa_proj_workplan_attr ppwa, pa_proj_element_versions ppev2, pa_proj_element_versions ppev3, pa_proj_elements ppe2, pa_proj_elements ppe3, pa_proj_elem_ver_structure stc, pa_proj_elements ele, pa_proj_element_versions evr, pji_fm_xbs_accum_tmp1 pfxat WHERE evr.proj_element_id = ele.proj_element_id and stc.element_version_id = evr.element_version_id and stc.project_id = evr.project_id and stc.status_code = 'STRUCTURE_PUBLISHED' and sch.element_version_id (+) = evr.element_version_id and sch.project_id (+) = evr.project_id and st1.project_status_code = stc.status_code and st2.lookup_code (+) = stc.lock_status_code and st2.lookup_type (+) = 'PA_PEV_LOCK_STATUS' and ele.proj_element_id = ppst.proj_element_id and ppst.STRUCTURE_TYPE_ID = pst.STRUCTURE_TYPE_ID and lk1.lookup_type (+) = 'YES_NO' and lk1.lookup_code (+) = stc.original_flag and lk2.lookup_type (+) = 'YES_NO' and lk2.lookup_code (+) = stc.current_flag and lk3.lookup_type (+) = 'YES_NO' and lk3.lookup_code (+) = stc.latest_eff_published_flag and pp1.person_id (+) = stc.locked_by_person_id and sysdate between pp1.effective_start_date(+) and pp1.effective_end_date(+) and pp2.person_id (+) = stc.published_by_person_id and sysdate between pp2.effective_start_date(+) and pp2.effective_end_date(+) and ele.manager_person_id = papf.person_id(+) and SYSDATE BETWEEN papf.effective_start_date(+) AND papf.effective_end_date (+) and stc.change_reason_code = lu.lookup_code(+) and lu.lookup_type(+) = 'PA_WORKPLAN_CHANGE_REASON' and sch.calendar_id = jc.calendar_id (+) and jc.language (+) = userenv('lang') and evr.project_id = ppru.project_id(+) and evr.proj_element_id = ppru.object_id(+) and evr.object_type = ppru.object_type(+) and ppru.structure_type (+) = 'WORKPLAN' and ppru.structure_version_id(+) is null and NVL( ppru.PROGRESS_STATUS_CODE, ppru.eff_rollup_prog_stat_code ) = pps.PROJECT_STATUS_CODE(+) AND pppa.project_id (+) = ele.project_id and pppa.object_type (+) = 'PA_STRUCTURES' and pppa.object_id (+) = ele.proj_element_id and pppa.structure_type(+) = 'WORKPLAN' and pst.structure_type_class_code IN ('WORKPLAN','FINANCIAL') and ele.project_id = ppa.project_id AND stc.project_id <> 0 AND ele.proj_element_id = ppwa.proj_element_id (+) AND ppwa.LIFECYCLE_VERSION_ID = ppev2.element_version_id (+) AND ppev2.proj_element_id = ppe2.proj_element_id (+) AND ppwa.CURRENT_PHASE_VERSION_ID = ppev3.element_version_id (+) AND ppev3.proj_element_id = ppe3.proj_element_id (+) AND pfxat.project_id (+)= evr.project_id AND pfxat.project_element_id (+)=evr.proj_element_id AND pfxat.struct_version_id (+)=evr.element_version_id AND pfxat.calendar_type(+) = 'A' AND pfxat.plan_version_id (+)> 0 AND pfxat.txn_currency_code(+) is null AND ppru.object_version_id(+) <= evr.element_version_id AND nvl(ppru.as_of_date, trunc(sysdate)) = (select nvl(max(ppr2.as_of_date),trunc(sysdate)) from pa_progress_rollup ppr2 ,pa_proj_element_versions ppev2 ,pa_proj_elem_ver_structure ppvs2 where ppr2.object_id = evr.proj_element_id and ppr2.proj_element_id = evr.proj_element_id and ppr2.project_id = evr.project_id and ppr2.object_type = 'PA_STRUCTURES' and ppr2.structure_type = 'WORKPLAN' and ppr2.structure_version_id is null and ppr2.current_flag <> 'W' and ppr2.project_id = ppev2.project_id and ppr2.proj_element_id = ppev2.proj_element_id and ppr2.object_version_id = ppev2.element_version_id and ppev2.parent_structure_version_id = ppvs2.element_version_id and ppvs2.project_id = ppr2.project_id and ppvs2.status_code = 'STRUCTURE_PUBLISHED' and ppvs2.published_date <= stc.published_date) AND ppru.current_flag (+) <> 'W'

View Text - HTML Formatted

SELECT /*+ INDEX(PJI_FM_XBS_ACCUM_TMP1 PJI_FM_XBS_ACCUM_TMP1_N1)*/ PPA.NAME
, PPA.SEGMENT1
, ELE.PROJ_ELEMENT_ID
, EVR.ELEMENT_VERSION_ID
, STC.PEV_STRUCTURE_ID
, EVR.PROJECT_ID
, ELE.ELEMENT_NUMBER
, ELE.NAME
, STC.VERSION_NUMBER
, STC.NAME
, STC.DESCRIPTION
, SCH.SCHEDULED_START_DATE
, SCH.SCHEDULED_FINISH_DATE
, SCH.ESTIMATED_START_DATE
, SCH.ESTIMATED_FINISH_DATE
, SCH.ACTUAL_START_DATE
, SCH.ACTUAL_FINISH_DATE
, STC.EFFECTIVE_DATE
, STC.STATUS_CODE
, ST1.PROJECT_STATUS_NAME
, STC.LOCK_STATUS_CODE
, ST2.MEANING
, STC.LOCKED_DATE
, STC.LOCKED_BY_PERSON_ID
, PP1.FULL_NAME
, STC.PUBLISHED_DATE
, STC.PUBLISHED_BY_PERSON_ID
, PP2.FULL_NAME
, STC.ORIGINAL_FLAG
, LK1.MEANING
, STC.CURRENT_FLAG
, LK2.MEANING
, STC.LATEST_EFF_PUBLISHED_FLAG
, LK3.MEANING
, PST.STRUCTURE_TYPE
, ELE.RECORD_VERSION_NUMBER
, EVR.RECORD_VERSION_NUMBER
, STC.RECORD_VERSION_NUMBER
, SCH.RECORD_VERSION_NUMBER
, STC.WBS_RECORD_VERSION_NUMBER
, NVL( PPRU.PROGRESS_STATUS_CODE
, PPRU.EFF_ROLLUP_PROG_STAT_CODE )
, PPS.PROJECT_STATUS_NAME
, PPRU.PERCENT_COMPLETE_ID
, PPS.STATUS_ICON_ACTIVE_IND
, PPS.STATUS_ICON_IND
, PPRU.ESTIMATED_START_DATE PROG_ESTIMATED_START_DATE
, PPRU.ESTIMATED_FINISH_DATE PROG_ESTIMATED_FINISH_DATE
, PPRU.ACTUAL_START_DATE PROG_ACTUAL_START_DATE
, PPRU.ACTUAL_FINISH_DATE PROG_ACTUAL_FINISH_DATE
, NVL( PPRU.COMPLETED_PERCENTAGE
, PPRU.EFF_ROLLUP_PERCENT_COMP )
, PPRU.AS_OF_DATE
, ELE.MANAGER_PERSON_ID
, PAPF.FULL_NAME
, TO_NUMBER(NULL) REPORT_VERSION_ID
, ELE.BASELINE_START_DATE
, ELE.BASELINE_FINISH_DATE
, SCH.SCHEDULED_START_DATE - ELE.BASELINE_START_DATE
, SCH.SCHEDULED_FINISH_DATE - ELE.BASELINE_FINISH_DATE
, SCH.ESTIMATED_START_DATE - SCH.SCHEDULED_START_DATE
, SCH.ESTIMATED_FINISH_DATE - SCH.SCHEDULED_FINISH_DATE
, SCH.ACTUAL_START_DATE - SCH.SCHEDULED_START_DATE
, SCH.ACTUAL_FINISH_DATE - SCH.SCHEDULED_FINISH_DATE
, STC.CHANGE_REASON_CODE
, LU.MEANING
, PA_PROGRESS_UTILS.GET_PRIOR_PERCENT_COMPLETE(PPA.PROJECT_ID
, ELE.PROJ_ELEMENT_ID
, PPRU.AS_OF_DATE) PRIOR_PERCENT_COMPLETE
, SCH.LAST_UPDATE_DATE
, PPA.BASELINE_AS_OF_DATE
, PPRU.LAST_UPDATE_DATE
, PPRU.LAST_UPDATE_DATE
, PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.LABOR_HOURS
, PFXAT.EQUIPMENT_HOURS) PLANNED_EFFORT
, PA_PROJECT_STRUCTURE_UTILS.GET_STRUC_TYPE_FOR_STRUCTURE(ELE.PROJ_ELEMENT_ID
, 'FINANCIAL')
, SCH.CALENDAR_ID
, JC.CALENDAR_NAME
, PA_PROGRESS_UTILS.CALC_ETC(PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.LABOR_HOURS
, PFXAT.EQUIPMENT_HOURS
, NULL)
, PPRU.ESTIMATED_REMAINING_EFFORT
, PPRU.EQPMT_ETC_EFFORT
, NULL
, PPRU.SUBPRJ_PPL_ETC_EFFORT
, PPRU.SUBPRJ_EQPMT_ETC_EFFORT
, NULL
, NULL
, PA_PROGRESS_UTILS.CALC_ACT(PPRU.PPL_ACT_EFFORT_TO_DATE
, PPRU.EQPMT_ACT_EFFORT_TO_DATE
, NULL
, PPRU.SUBPRJ_PPL_ACT_EFFORT
, PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, NULL)) ESTIMATED_REMAINING_EFFORT
, NULL INITIAL_PROGRESS_STATUS_CODE
, PPPA.WQ_ENABLE_FLAG
, PPPA.PERCENT_COMP_ENABLE_FLAG
, PPPA.REMAIN_EFFORT_ENABLE_FLAG
, PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(ELE.PROJECT_ID)
, SCH.DURATION
, PA_PROJ_ELEMENTS_UTILS.CONVERT_HR_TO_DAYS(ELE.BASELINE_DURATION)
, PA_PROJ_ELEMENTS_UTILS.CONVERT_HR_TO_DAYS(SCH.ESTIMATED_DURATION)
, PA_PROJ_ELEMENTS_UTILS.CONVERT_HR_TO_DAYS(SCH.ACTUAL_DURATION)
, PPWA.LIFECYCLE_VERSION_ID
, PPE2.NAME
, PPE2.ELEMENT_NUMBER
, PPWA.CURRENT_PHASE_VERSION_ID
, PPE3.NAME
, PPE3.ELEMENT_NUMBER
, PA_PROJ_ELEMENTS_UTILS.CHECK_CHILD_ELEMENT_EXIST(EVR.ELEMENT_VERSION_ID)
, STC.CURRENT_WORKING_FLAG
, STC.SCHEDULE_DIRTY_FLAG
, PFXAT.EQUIPMENT_HOURS PLANNED_EQUIP_EFFORT
, PFXAT.PRJ_BRDN_COST PLANNED_COST
, NVL(SCH.SCHEDULED_START_DATE_ROLLUP
, 0) + SCH.SCHEDULED_START_DATE SCHEDULED_START_ROLLUP_DATE
, NVL(SCH.SCHEDULED_FINISH_DATE_ROLLUP
, 0) + SCH.SCHEDULED_FINISH_DATE SCHEDULED_FINISH_ROLLUP_DATE
, NVL(SCH.ESTIMATED_START_DATE_ROLLUP
, 0) + SCH.ESTIMATED_START_DATE ESTIMATED_START_ROLLUP_DATE
, NVL(SCH.ESTIMATED_FINISH_DATE_ROLLUP
, 0) + SCH.ESTIMATED_FINISH_DATE ESTIMATED_FINISH_ROLLUP_DATE
, NVL(SCH.ACTUAL_START_DATE_ROLLUP
, 0) + SCH.ACTUAL_START_DATE ACTUAL_START_ROLLUP_DATE
, NVL(SCH.ACTUAL_FINISH_DATE_ROLLUP
, 0) + SCH.ACTUAL_FINISH_DATE ACTUAL_FINISH_ROLLUP_DATE
, PFXAT.PRJ_RAW_COST RAW_COST
, PFXAT.PRJ_BRDN_COST BURDENED_COST
, PA_PROGRESS_UTILS.CALC_ACT(PPRU.PPL_ACT_EFFORT_TO_DATE
, PPRU.EQPMT_ACT_EFFORT_TO_DATE
, NULL
, PPRU.SUBPRJ_PPL_ACT_EFFORT
, PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, NULL) ACTUAL_EFFORT
, PA_PROGRESS_UTILS.CALC_ACT(NULL
, PPRU.EQPMT_ACT_EFFORT_TO_DATE
, NULL
, NULL
, PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, NULL) ACTUAL_EQUIP_EFFORT
, PPA.PROJECT_CURRENCY_CODE
, PA_RELATIONSHIP_UTILS.DISPLAY_PREDECESSORS(EVR.ELEMENT_VERSION_ID) PREDECESSORS
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE((NVL(PPRU.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0) ) PERCENT_SPENT_EFFORT
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE ((NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, NVL(PFXAT.PRJ_BRDN_COST
, 0) ) PERCENT_SPENT_COST
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE((NVL(PPRU.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, (NVL(PPRU.ESTIMATED_REMAINING_EFFORT
, 0)+NVL(PPRU.EQPMT_ETC_EFFORT
, 0) +NVL(PPRU.SUBPRJ_PPL_ETC_EFFORT
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ETC_EFFORT
, 0))) PERCENT_COMPLETE_EFFORT
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE((NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, (NVL(PPRU.OTH_ETC_COST_PC
, 0)+NVL(PPRU.PPL_ETC_COST_PC
, 0)+NVL(PPRU.EQPMT_ETC_COST_PC
, 0) +NVL(PPRU.SUBPRJ_OTH_ETC_COST_PC
, 0)+NVL(PPRU.SUBPRJ_PPL_ETC_COST_PC
, 0) +NVL(PPRU.SUBPRJ_EQPMT_ETC_COST_PC
, 0))) PERCENT_COMPLETE_COST
, TRUNC(PPRU.ACTUAL_FINISH_DATE) - TRUNC(PPRU.ACTUAL_START_DATE) ACTUAL_DURATION
, TRUNC(SCH.SCHEDULED_FINISH_DATE) - TRUNC(SYSDATE) REMAINING_DURATION
, PA_PROJ_ELEMENTS_UTILS.GET_FND_LOOKUP_MEANING ( 'PA_SCHEDULE_CONSTRAINT_TYPE'
, SCH.CONSTRAINT_TYPE_CODE ) CONSTRAINT_TYPE
, SCH.CONSTRAINT_TYPE_CODE
, SCH.CONSTRAINT_DATE
, SCH.EARLY_START_DATE
, SCH.EARLY_FINISH_DATE
, SCH.LATE_START_DATE
, SCH.LATE_FINISH_DATE
, SCH.FREE_SLACK
, SCH.TOTAL_SLACK
, DECODE(PA_PROJ_ELEMENTS_UTILS.IS_SUMMARY_TASK_OR_STRUCTURE(EVR.ELEMENT_VERSION_ID)
, 'Y'
, 'N'
, 'N'
, ' ') LOWEST_TASK
, /* BUG FIX 5406261 TO_NUMBER ( NULL ) ESTIMATED_BASELINE_START
, TO_NUMBER ( NULL ) ESTIMATED_BASELINE_FINISH
, */ (SCH.ESTIMATED_START_DATE - ELE.BASELINE_START_DATE) ESTIMATED_BASELINE_START
, (SCH.ESTIMATED_FINISH_DATE - ELE.BASELINE_FINISH_DATE) ESTIMATED_BASELINE_FINISH
, /* END OF BUG FIX 5406261 */ TO_NUMBER ( NULL ) PLANNED_BASELINE_START
, TO_NUMBER ( NULL ) PLANNED_BASELINE_FINISH
, PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.BASE_EQUIP_HOURS
, PFXAT.BASE_LABOR_HOURS
, NULL) BASELINE_EFFORT
, PA_PROGRESS_UTILS.CALC_ETC(PA_PROGRESS_UTILS.CALC_PLAN(PFXAT.LABOR_HOURS
, PFXAT.EQUIPMENT_HOURS
, NULL)
, PPRU.ESTIMATED_REMAINING_EFFORT
, PPRU.EQPMT_ETC_EFFORT
, NULL
, PPRU.SUBPRJ_PPL_ETC_EFFORT
, PPRU.SUBPRJ_EQPMT_ETC_EFFORT
, NULL
, NULL
, PA_PROGRESS_UTILS.CALC_ACT(PPRU.PPL_ACT_EFFORT_TO_DATE
, PPRU.EQPMT_ACT_EFFORT_TO_DATE
, NULL
, PPRU.SUBPRJ_PPL_ACT_EFFORT
, PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, NULL)) ETC_EFFORT
, (NVL(PPRU.PPL_ACT_EFFORT_TO_DATE
, 0) +NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT
, 0) +NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0))
, PPRU.ESTIMATED_REMAINING_EFFORT
, PPRU.EQPMT_ETC_EFFORT
, NULL
, PPRU.SUBPRJ_PPL_ETC_EFFORT
, PPRU.SUBPRJ_EQPMT_ETC_EFFORT
, NULL
, NULL
, (NVL(PPRU.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, DECODE(NVL(PPWA.WP_ENABLE_VERSION_FLAG
, 'N')
, 'Y'
, 'PUBLISH'
, 'WORKING'))) ESTIMATE_AT_COMPLETION_EFFORT
, ((NVL(PFXAT.BASE_LABOR_HOURS
, 0)+NVL(PFXAT.BASE_EQUIP_HOURS
, 0)) -(NVL(PPRU.PPL_ACT_EFFORT_TO_DATE
, 0) +NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT
, 0) +NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0))
, PPRU.ESTIMATED_REMAINING_EFFORT
, PPRU.EQPMT_ETC_EFFORT
, NULL
, PPRU.SUBPRJ_PPL_ETC_EFFORT
, PPRU.SUBPRJ_EQPMT_ETC_EFFORT
, NULL
, NULL
, (NVL(PPRU.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, DECODE(NVL(PPWA.WP_ENABLE_VERSION_FLAG
, 'N')
, 'Y'
, 'PUBLISH'
, 'WORKING')))) VARIANCE_AT_COMPLETION_EFFORT
, ((NVL(PPRU.EARNED_VALUE
, 0))-(NVL(PPRU.PPL_ACT_EFFORT_TO_DATE
, 0)+ NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE
, 0)+ NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT
, 0)+ NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, 0))) EFFORT_VARIANCE
, ROUND((((NVL(PPRU.EARNED_VALUE
, 0))-(NVL(PPRU.PPL_ACT_EFFORT_TO_DATE
, 0)+ NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE
, 0)+ NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT
, 0)+ NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, 0)))/(DECODE(NVL(PPRU.EARNED_VALUE
, 0)
, 0
, 1
, NVL(PPRU.EARNED_VALUE
, 0))))
, 2) EFFORT_VARIANCE_PERCENT
, PA_PROGRESS_UTILS.CALC_ACT(PPRU.PPL_ACT_COST_TO_DATE_PC
, PPRU.EQPMT_ACT_COST_TO_DATE_PC
, PPRU.OTH_ACT_COST_TO_DATE_PC
, PPRU.SUBPRJ_PPL_ACT_COST_PC
, PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC) ACTUAL_COST
, PFXAT.PRJ_BASE_BRDN_COST BASELINE_COST
, (NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0) +NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PFXAT.PRJ_BRDN_COST
, PPRU.PPL_ETC_COST_PC
, PPRU.EQPMT_ETC_COST_PC
, PPRU.OTH_ETC_COST_PC
, PPRU.SUBPRJ_PPL_ETC_COST_PC
, PPRU.SUBPRJ_EQPMT_ETC_COST_PC
, PPRU.SUBPRJ_OTH_ETC_COST_PC
, NULL
, (NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, DECODE(NVL(PPWA.WP_ENABLE_VERSION_FLAG
, 'N')
, 'Y'
, 'PUBLISH'
, 'WORKING'))) ESTIMATE_AT_COMPLETION_COST
, ((NVL(PPRU.EARNED_VALUE
, 0))-(NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0)+ NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0))) COST_VARIANCE
, ROUND((((NVL(PPRU.EARNED_VALUE
, 0))-(NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0)+ NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0)))/(DECODE(NVL(PPRU.EARNED_VALUE
, 0)
, 0
, 1
, NVL(PPRU.EARNED_VALUE
, 0))))
, 2) COST_VARIANCE_PERCENT
, ROUND((NVL(SCH.WQ_PLANNED_QUANTITY
, 0) - NVL(PPRU.CUMULATIVE_WORK_QUANTITY
, 0))
, 2) ETC_WORK_QUANTITY
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((NVL(PFXAT.PRJ_BRDN_COST
, 0)/DECODE(NVL(PPRU.CUMULATIVE_WORK_QUANTITY
, 0)
, 0
, 1
, NVL(PPRU.CUMULATIVE_WORK_QUANTITY
, 0)))
, PPA.PROJECT_CURRENCY_CODE) PLANNED_COST_PER_UNIT
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((NVL((NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0)+ NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, 0)/DECODE(NVL(PPRU.CUMULATIVE_WORK_QUANTITY
, 0)
, 0
, 1
, PPRU.CUMULATIVE_WORK_QUANTITY))
, PPA.PROJECT_CURRENCY_CODE) ACTUAL_COST_PER_UNIT
, ROUND((NVL(NVL(PPRU.CUMULATIVE_WORK_QUANTITY
, 0)-NVL(SCH.WQ_PLANNED_QUANTITY
, 0)
, 0))
, 2) WORK_QUANTITY_VARIANCE
, ROUND((((PPRU.CUMULATIVE_WORK_QUANTITY-SCH.WQ_PLANNED_QUANTITY)/DECODE(NVL(SCH.WQ_PLANNED_QUANTITY
, 0)
, 0
, 1
, SCH.WQ_PLANNED_QUANTITY))*100)
, 2) WORK_QUANTITY_VARIANCE_PERCENT
, PPRU.EARNED_VALUE EARNED_VALUE
, (NVL(PPRU.EARNED_VALUE
, 0)-NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPRU.OBJECT_ID
, ELE.PROJ_ELEMENT_ID
, PPRU.AS_OF_DATE
, EVR.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, ELE.BASELINE_START_DATE
, ELE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)) SCHEDULE_VARIANCE
, (NVL(PPRU.EARNED_VALUE
, 0)-NVL((NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0)+ NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0)+ NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, 0)) EARNED_VALUE_COST_VARIANCE
, (NVL(PPRU.EARNED_VALUE
, 0)-NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPRU.OBJECT_ID
, ELE.PROJ_ELEMENT_ID
, PPRU.AS_OF_DATE
, EVR.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, ELE.BASELINE_START_DATE
, ELE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)) EARNED_VALUE_SCHEDULE_VARIANCE
, ((NVL(PFXAT.PRJ_BASE_BRDN_COST
, 0)) -(NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0) +NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PFXAT.PRJ_BRDN_COST
, PPRU.PPL_ETC_COST_PC
, PPRU.EQPMT_ETC_COST_PC
, PPRU.OTH_ETC_COST_PC
, PPRU.SUBPRJ_PPL_ETC_COST_PC
, PPRU.SUBPRJ_EQPMT_ETC_COST_PC
, PPRU.SUBPRJ_OTH_ETC_COST_PC
, NULL
, (NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, DECODE(PPWA.WP_ENABLE_VERSION_FLAG
, 'Y'
, 'PUBLISH'
, 'WORKING')))) VARIANCE_AT_COMPLETION_COST
, ROUND((DECODE (PPRU.TASK_WT_BASIS_CODE
, 'EFFORT'
, (((NVL(PFXAT.BASE_LABOR_HOURS
, 0)+NVL(PFXAT.BASE_EQUIP_HOURS
, 0))-PPRU.EARNED_VALUE)/DECODE(((NVL(PFXAT.BASE_LABOR_HOURS
, 0) +NVL(PFXAT.BASE_EQUIP_HOURS
, 0))-(NVL(PPRU.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, 0)))
, 0
, 1
, ((NVL(PFXAT.BASE_LABOR_HOURS
, 0)+NVL(PFXAT.BASE_EQUIP_HOURS
, 0))-(NVL(PPRU.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT
, 0) +NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, 0)))))
, ((NVL(PFXAT.PRJ_BASE_BRDN_COST
, 0)-PPRU.EARNED_VALUE)/DECODE((NVL(PFXAT.PRJ_BASE_BRDN_COST
, 0)-(NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0) +NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0)))
, 0
, 1
, (NVL(PFXAT.PRJ_BASE_BRDN_COST
, 0)-(NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0) +NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0))))) ))
, 2) TO_COMPLETE_PERFORMANCE_INDEX
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPRU.OBJECT_ID
, ELE.PROJ_ELEMENT_ID
, PPRU.AS_OF_DATE
, EVR.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, ELE.BASELINE_START_DATE
, ELE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0) BUDGETED_COST_OF_WORK_SCH
, ROUND((NVL(PPRU.EARNED_VALUE
, 0)/DECODE(NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPRU.OBJECT_ID
, ELE.PROJ_ELEMENT_ID
, PPRU.AS_OF_DATE
, EVR.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, ELE.BASELINE_START_DATE
, ELE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)
, 0
, 1
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPRU.OBJECT_ID
, ELE.PROJ_ELEMENT_ID
, PPRU.AS_OF_DATE
, EVR.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, ELE.BASELINE_START_DATE
, ELE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)))
, 2) SCHEDULE_PERFORMANCE_INDEX
, ROUND((DECODE(PPRU.TASK_WT_BASIS_CODE
, 'EFFORT'
, (NVL(PPRU.EARNED_VALUE
, 0)/DECODE((NVL(PPRU.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, 0
, 1
, (NVL(PPRU.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPRU.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPRU.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_EFFORT
, 0))))
, (NVL(PPRU.EARNED_VALUE
, 0)/DECODE((NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, 0
, 1
, (NVL(PPRU.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPRU.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPRU.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, 0)))) ))
, 2) COST_PERFORMANCE_INDEX
, PA_PROJ_STRUC_MAPPING_UTILS.GET_MAPPED_FIN_TASK_ID(EVR.ELEMENT_VERSION_ID
, PPA.STRUCTURE_SHARING_CODE) MAPPED_FINANCIAL_TASK
, PA_DELIVERABLE_UTILS.GET_ASSOCIATED_DELIVERABLES (ELE.PROJ_ELEMENT_ID) DELIVERABLES
, STC.DATE_PROG_APPLIED_ON_WVER
, PA_PROGRESS_UTILS.LATEST_PUBLISHED_PROGRESS_DATE(EVR.PROJECT_ID
, PPRU.STRUCTURE_TYPE)
, PA_PROJ_STRUC_MAPPING_UTILS.GET_MAPPED_FIN_TASK_NAME(EVR.ELEMENT_VERSION_ID
, PPA.STRUCTURE_SHARING_CODE)
, PA_PROGRESS_UTILS.CALC_ETC(PFXAT.PRJ_BRDN_COST
, PPRU.PPL_ETC_COST_PC
, PPRU.EQPMT_ETC_COST_PC
, PPRU.OTH_ETC_COST_PC
, PPRU.SUBPRJ_PPL_ETC_COST_PC
, PPRU.SUBPRJ_EQPMT_ETC_COST_PC
, PPRU.SUBPRJ_OTH_ETC_COST_PC
, NULL
, PA_PROGRESS_UTILS.CALC_ACT(PPRU.PPL_ACT_COST_TO_DATE_PC
, PPRU.EQPMT_ACT_COST_TO_DATE_PC
, PPRU.OTH_ACT_COST_TO_DATE_PC
, PPRU.SUBPRJ_PPL_ACT_COST_PC
, PPRU.SUBPRJ_EQPMT_ACT_COST_PC
, PPRU.SUBPRJ_OTH_ACT_COST_TO_DATE_PC)) ETC_COST
, PPRU.PROGRESS_ROLLUP_ID
, PPRU.BASE_PERCENT_COMPLETE
, NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0) - (NVL(PFXAT.BASE_LABOR_HOURS
, 0)+NVL(PFXAT.BASE_EQUIP_HOURS
, 0)) PLANNED_BASELINE_EFFORT_VAR
, NVL(PFXAT.PRJ_BRDN_COST
, 0) - NVL(PFXAT.PRJ_BASE_BRDN_COST
, 0) PLANNED_BASELINE_COST_VAR
FROM PA_PROJ_ELEM_VER_SCHEDULE SCH
, PA_PROJECT_STATUSES ST1
, PA_LOOKUPS ST2
, PA_LOOKUPS LU
, PER_ALL_PEOPLE_F PP1
, PER_ALL_PEOPLE_F PP2
, FND_LOOKUPS LK1
, FND_LOOKUPS LK2
, FND_LOOKUPS LK3
, PA_PROJ_STRUCTURE_TYPES PPST
, PA_STRUCTURE_TYPES PST
, PER_ALL_PEOPLE_F PAPF
, JTF_CALENDARS_TL JC
, PA_PROJECT_STATUSES PPS
, PA_PROGRESS_ROLLUP PPRU
, PA_PROJ_PROGRESS_ATTR PPPA
, PA_PROJECTS_ALL PPA
, PA_PROJ_WORKPLAN_ATTR PPWA
, PA_PROJ_ELEMENT_VERSIONS PPEV2
, PA_PROJ_ELEMENT_VERSIONS PPEV3
, PA_PROJ_ELEMENTS PPE2
, PA_PROJ_ELEMENTS PPE3
, PA_PROJ_ELEM_VER_STRUCTURE STC
, PA_PROJ_ELEMENTS ELE
, PA_PROJ_ELEMENT_VERSIONS EVR
, PJI_FM_XBS_ACCUM_TMP1 PFXAT
WHERE EVR.PROJ_ELEMENT_ID = ELE.PROJ_ELEMENT_ID
AND STC.ELEMENT_VERSION_ID = EVR.ELEMENT_VERSION_ID
AND STC.PROJECT_ID = EVR.PROJECT_ID
AND STC.STATUS_CODE = 'STRUCTURE_PUBLISHED'
AND SCH.ELEMENT_VERSION_ID (+) = EVR.ELEMENT_VERSION_ID
AND SCH.PROJECT_ID (+) = EVR.PROJECT_ID
AND ST1.PROJECT_STATUS_CODE = STC.STATUS_CODE
AND ST2.LOOKUP_CODE (+) = STC.LOCK_STATUS_CODE
AND ST2.LOOKUP_TYPE (+) = 'PA_PEV_LOCK_STATUS'
AND ELE.PROJ_ELEMENT_ID = PPST.PROJ_ELEMENT_ID
AND PPST.STRUCTURE_TYPE_ID = PST.STRUCTURE_TYPE_ID
AND LK1.LOOKUP_TYPE (+) = 'YES_NO'
AND LK1.LOOKUP_CODE (+) = STC.ORIGINAL_FLAG
AND LK2.LOOKUP_TYPE (+) = 'YES_NO'
AND LK2.LOOKUP_CODE (+) = STC.CURRENT_FLAG
AND LK3.LOOKUP_TYPE (+) = 'YES_NO'
AND LK3.LOOKUP_CODE (+) = STC.LATEST_EFF_PUBLISHED_FLAG
AND PP1.PERSON_ID (+) = STC.LOCKED_BY_PERSON_ID
AND SYSDATE BETWEEN PP1.EFFECTIVE_START_DATE(+)
AND PP1.EFFECTIVE_END_DATE(+)
AND PP2.PERSON_ID (+) = STC.PUBLISHED_BY_PERSON_ID
AND SYSDATE BETWEEN PP2.EFFECTIVE_START_DATE(+)
AND PP2.EFFECTIVE_END_DATE(+)
AND ELE.MANAGER_PERSON_ID = PAPF.PERSON_ID(+)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE (+)
AND STC.CHANGE_REASON_CODE = LU.LOOKUP_CODE(+)
AND LU.LOOKUP_TYPE(+) = 'PA_WORKPLAN_CHANGE_REASON'
AND SCH.CALENDAR_ID = JC.CALENDAR_ID (+)
AND JC.LANGUAGE (+) = USERENV('LANG')
AND EVR.PROJECT_ID = PPRU.PROJECT_ID(+)
AND EVR.PROJ_ELEMENT_ID = PPRU.OBJECT_ID(+)
AND EVR.OBJECT_TYPE = PPRU.OBJECT_TYPE(+)
AND PPRU.STRUCTURE_TYPE (+) = 'WORKPLAN'
AND PPRU.STRUCTURE_VERSION_ID(+) IS NULL
AND NVL( PPRU.PROGRESS_STATUS_CODE
, PPRU.EFF_ROLLUP_PROG_STAT_CODE ) = PPS.PROJECT_STATUS_CODE(+)
AND PPPA.PROJECT_ID (+) = ELE.PROJECT_ID
AND PPPA.OBJECT_TYPE (+) = 'PA_STRUCTURES'
AND PPPA.OBJECT_ID (+) = ELE.PROJ_ELEMENT_ID
AND PPPA.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PST.STRUCTURE_TYPE_CLASS_CODE IN ('WORKPLAN'
, 'FINANCIAL')
AND ELE.PROJECT_ID = PPA.PROJECT_ID
AND STC.PROJECT_ID <> 0
AND ELE.PROJ_ELEMENT_ID = PPWA.PROJ_ELEMENT_ID (+)
AND PPWA.LIFECYCLE_VERSION_ID = PPEV2.ELEMENT_VERSION_ID (+)
AND PPEV2.PROJ_ELEMENT_ID = PPE2.PROJ_ELEMENT_ID (+)
AND PPWA.CURRENT_PHASE_VERSION_ID = PPEV3.ELEMENT_VERSION_ID (+)
AND PPEV3.PROJ_ELEMENT_ID = PPE3.PROJ_ELEMENT_ID (+)
AND PFXAT.PROJECT_ID (+)= EVR.PROJECT_ID
AND PFXAT.PROJECT_ELEMENT_ID (+)=EVR.PROJ_ELEMENT_ID
AND PFXAT.STRUCT_VERSION_ID (+)=EVR.ELEMENT_VERSION_ID
AND PFXAT.CALENDAR_TYPE(+) = 'A'
AND PFXAT.PLAN_VERSION_ID (+)> 0
AND PFXAT.TXN_CURRENCY_CODE(+) IS NULL
AND PPRU.OBJECT_VERSION_ID(+) <= EVR.ELEMENT_VERSION_ID
AND NVL(PPRU.AS_OF_DATE
, TRUNC(SYSDATE)) = (SELECT NVL(MAX(PPR2.AS_OF_DATE)
, TRUNC(SYSDATE))
FROM PA_PROGRESS_ROLLUP PPR2
, PA_PROJ_ELEMENT_VERSIONS PPEV2
, PA_PROJ_ELEM_VER_STRUCTURE PPVS2
WHERE PPR2.OBJECT_ID = EVR.PROJ_ELEMENT_ID
AND PPR2.PROJ_ELEMENT_ID = EVR.PROJ_ELEMENT_ID
AND PPR2.PROJECT_ID = EVR.PROJECT_ID
AND PPR2.OBJECT_TYPE = 'PA_STRUCTURES'
AND PPR2.STRUCTURE_TYPE = 'WORKPLAN'
AND PPR2.STRUCTURE_VERSION_ID IS NULL
AND PPR2.CURRENT_FLAG <> 'W'
AND PPR2.PROJECT_ID = PPEV2.PROJECT_ID
AND PPR2.PROJ_ELEMENT_ID = PPEV2.PROJ_ELEMENT_ID
AND PPR2.OBJECT_VERSION_ID = PPEV2.ELEMENT_VERSION_ID
AND PPEV2.PARENT_STRUCTURE_VERSION_ID = PPVS2.ELEMENT_VERSION_ID
AND PPVS2.PROJECT_ID = PPR2.PROJECT_ID
AND PPVS2.STATUS_CODE = 'STRUCTURE_PUBLISHED'
AND PPVS2.PUBLISHED_DATE <= STC.PUBLISHED_DATE)
AND PPRU.CURRENT_FLAG (+) <> 'W'