eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_TASK_DET_EFFORT_COST_V

Source

View Text - Preformatted


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 ,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, 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')+ ( (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')+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 ,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, 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')+ ( (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')+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

View Text - HTML Formatted

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
, 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
, 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')+ ( (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')+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
, 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
, 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')+ ( (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')+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