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_PROJ_TASK_PROG_DET_V

Source

View Text - Preformatted


SELECT /*+ INDEX(pfxat pji_fm_xbs_accum_tmp1_n1)*/ decode(ppc.published_flag,'Y',to_number(null),ppc.percent_complete_id), ppa.project_id, ppa.name, ppa.segment1, ppa.long_name, ppe.PROJ_ELEMENT_ID, ppe.PROJ_ELEMENT_ID, to_number(null), ppev.ELEMENT_VERSION_ID, to_number(null), ppe.OBJECT_TYPE, 'WORKPLAN', decode( ppe.object_type, 'PA_TASKS', ppe.name, 'PA_STRUCTURES', ppvs.name ), decode( ppe.object_type, 'PA_TASKS', ppe.element_number, 'PA_STRUCTURES', to_char( ppvs.version_number ) ), ppe.MANAGER_PERSON_ID, null, ppev.WBS_LEVEL, ppev.WBS_NUMBER, ppvsch.MILESTONE_FLAG, ppvsch.CRITICAL_FLAG, ppe.priority_code, null priority_description, 'Y', ppr.as_of_date, ppc.description, ppc.progress_comment, decode(ppe.object_type, 'PA_STRUCTURES', nvl(ppc.PROGRESS_STATUS_CODE,nvl(ppr.eff_rollup_prog_stat_code,'PROGRESS_STAT_ON_TRACK')),'PA_TASKS', nvl(ppc.PROGRESS_STATUS_CODE,nvl(ppr.eff_rollup_prog_stat_code,ptt.initial_progress_status_code))), pps.PROJECT_STATUS_name, pps.PROJECT_SYSTEM_STATUS_CODE, pps2.status_icon_ind, pps2.status_icon_active_ind, ppvsch.scheduled_start_date, ppvsch.scheduled_finish_date, ppr.actual_start_date, ppr.actual_finish_date, ppr.estimated_start_date, ppr.estimated_finish_date, nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage, ppc.last_update_date, ppc.published_by_party_id, null published_by_party_name, ppc.current_flag, ppc.published_flag, decode( ppev.object_type , 'PA_STRUCTURES', 'Y' , 'PA_TASKS', PA_PROJ_ELEMENTS_UTILS.is_summary_task_or_structure( ppev.element_version_id ) ) summary_element_flag, ppc.record_version_number, nvl(ppc.status_code,nvl(ppe.status_code,'124')), pps3.project_status_name, pps3.project_system_status_code, round(ppvsch.WQ_PLANNED_QUANTITY,2), ppr.INCREMENTAL_WORK_QUANTITY, ppr.CUMULATIVE_WORK_QUANTITY, NVL(pfxat.LABOR_HOURS,0) + NVL(pfxat.EQUIPMENT_HOURS,0), ppr.ESTIMATED_REMAINING_EFFORT, ppr.BASE_PERCENT_COMPLETE, decode(ppe.object_type,'PA_STRUCTURES',pppa.WQ_ENABLE_FLAG,decode(ptt.WQ_ENABLE_FLAG, 'Y', decode(pppa.WQ_ENABLE_FLAG, 'Y', 'Y', 'N'), 'N')), decode(ppe.object_type,'PA_STRUCTURES',pppa.REMAIN_EFFORT_ENABLE_FLAG,decode(ptt.REMAIN_EFFORT_ENABLE_FLAG, 'Y', decode(pppa.REMAIN_EFFORT_ENABLE_FLAG, 'Y', 'Y', 'N'), 'N')), ptt.base_percent_comp_deriv_code, nvl(ppe.WQ_ACTUAL_ENTRY_CODE,ptt.ACTUAL_WQ_ENTRY_CODE), ppe.Baseline_start_date, ppe.baseline_finish_date, ppe.last_update_date, ppvsch.last_update_date, ppr.EFF_ROLLUP_PERCENT_COMP, ppr.record_version_number, nvl(ppe.WQ_ITEM_CODE,ptt.work_item_code), PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_WQ_WORK_ITEMS',nvl(ppe.WQ_ITEM_CODE,ptt.work_item_code)), ppe.wq_uom_code, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('UNIT',ppe.wq_uom_code), ppr.BASE_PROGRESS_STATUS_CODE, null base_progress_status_name, ppr.EFF_ROLLUP_PROG_STAT_CODE, pps2.PROJECT_STATUS_NAME, pa_progress_utils.GET_NEXT_PROGRESS_CYCLE(ppe.project_id, ppe.proj_element_id), pa_progress_utils.GET_PROG_DT_CLOSEST_TO_SYS_DT(ppe.project_id, ppe.proj_element_id), 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')), decode(ppe.object_type,'PA_TASKS',ptt.PROG_ENTRY_ENABLE_FLAG,'Y'), ptt.PROG_ENTRY_REQ_FLAG, ppe.rowid, 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)), ( nvl(ppr.oth_etc_cost_pc,0)+nvl(ppr.ppl_etc_cost_pc,0)+nvl(ppr.eqpmt_etc_cost_pc,0)+nvl(ppr.subprj_oth_etc_cost_pc,0) +nvl(ppr.subprj_ppl_etc_cost_pc,0) +nvl(ppr.subprj_eqpmt_etc_cost_pc,0)) ) Percent_Complete_Cost, 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)), ( nvl(ppr.estimated_remaining_effort,0)+nvl(ppr.eqpmt_etc_effort,0)+nvl(ppr.subprj_ppl_etc_effort,0) +nvl(ppr.subprj_eqpmt_etc_effort,0)) ) Percent_Complete_Effort, 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.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, 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, 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, pfxat.prj_base_brdn_cost Baseline_Cost, nvl(pfxat.base_labor_hours,0)+nvl(pfxat.base_equip_hours,0) Baselined_Effort, (PA_PROGRESS_UTILS.get_next_progress_cycle(ppa.project_id,ppe.proj_element_id)-sysdate) Days_Until_Progress_Due, (trunc(ppvsch.scheduled_start_date)-trunc(sysdate)) Days_Until_Start, ppr.earned_value Earned_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))) Est_at_Completion_Cost, 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))) Est_at_Completion_Effort, (ppr.estimated_start_date - ppe.baseline_start_date) Est_Bas_Start_Var, (ppr.estimated_finish_date - ppe.baseline_finish_date) Est_bas_Finish_Var, 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))) ETC_COST, 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))) ETC_EFFORT, round(( NVL(ppvsch.wq_planned_quantity,0) - NVL(CUMULATIVE_WORK_QUANTITY,0)),2) ETC_Work_Quantity, nvl(pfxat.prj_brdn_cost,0) Planned_Cost, pa_currency.round_trans_currency_amt((nvl(pfxat.prj_brdn_cost,0)/decode(nvl(cumulative_work_quantity,0),0,1,nvl(cumulative_work_quantity,0))),ppa.project_currency_code) Planned_Cost_Per_Unit, PA_RELATIONSHIP_UTILS.display_predecessors(ppe.proj_element_id) Predecessors, to_char(null) Resources, round((nvl(ppr.earned_value,0)/decode(nvl(pa_progress_utils.get_bcws(ppa.project_id,ppr.object_id,ppe.proj_element_id,pa_progress_utils.get_prog_asofdate(),ppev.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,ppe.proj_element_id,pa_progress_utils.get_prog_asofdate(),ppev.parent_structure_version_id ,pppa.task_weight_basis_code,ppe.baseline_start_date,ppe.baseline_finish_date,ppa.project_currency_code),0))),2) Sch_Performance_Index, round(decode (ppr.task_wt_basis_code,'EFFORT',(((nvl(pfxat.base_labor_hours,0)+nvl(pfxat.base_equip_hours,0))-ppr.earned_value)/decode(((nvl(pfxat.base_labor_hours,0) +nvl(pfxat.base_equip_hours,0))-(nvl(ppr.ppl_act_effort_to_date,0)+nvl(ppr.eqpmt_act_effort_to_date,0)+nvl(ppr.subprj_ppl_act_effort,0)+nvl(ppr.subprj_eqpmt_act_effort,0))), 0,1,((nvl(pfxat.base_labor_hours,0)+nvl(pfxat.base_equip_hours,0))-(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.prj_base_brdn_cost,0)-ppr.earned_value)/decode((nvl(pfxat.prj_base_brdn_cost,0)-(nvl(ppr.oth_act_cost_to_date_pc,0) +nvl(ppr.ppl_act_cost_to_date_pc,0)+nvl(ppr.eqpmt_act_cost_to_date_pc,0)+nvl(ppr.SUBPRJ_OTH_ACT_COST_TO_DT_PC,0)+nvl(ppr.subprj_ppl_act_cost_pc,0) +nvl(ppr.subprj_eqpmt_act_cost_pc,0))),0,1,(nvl(pfxat.prj_base_brdn_cost,0)-(nvl(ppr.oth_act_cost_to_date_pc,0) +nvl(ppr.ppl_act_cost_to_date_pc,0)+nvl(ppr.eqpmt_act_cost_to_date_pc,0)+nvl(ppr.SUBPRJ_OTH_ACT_COST_TO_DT_PC,0)+nvl(ppr.subprj_ppl_act_cost_pc,0) +nvl(ppr.subprj_eqpmt_act_cost_pc,0)))))),2) To_Complete_Performance_Index, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('UNIT','HOURS') Unit_of_Measure, nvl(pfxat.prj_base_brdn_cost,0)-(nvl(ppr.oth_act_cost_to_date_pc,0)+nvl(ppr.ppl_act_cost_to_date_pc,0)+nvl(ppr.eqpmt_act_cost_to_date_pc,0) +nvl(ppr.SUBPRJ_OTH_ACT_COST_TO_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)))) Var_at_Completion_Cost, nvl(pfxat.base_labor_hours,0)+nvl(pfxat.base_equip_hours,0)-(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)))) Var_at_Completion_Effort, to_number(null) Work_Type, nvl(pa_progress_utils.get_bcws(ppa.project_id,ppr.object_id,ppe.proj_element_id,pa_progress_utils.get_def_as_of_date_prog_report(ppa.project_id, ppe.proj_element_id),ppev.parent_structure_version_id ,pppa.task_weight_basis_code,ppe.baseline_start_date,ppe.baseline_finish_date,ppa.project_currency_code),0) BCWS, ppa.Project_currency_code, 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)+nvl(ppr.subprj_ppl_act_effort,0) +nvl(ppr.subprj_eqpmt_act_effort,0)),0,1,(nvl(ppr.ppl_act_effort_to_date,0)+nvl(ppr.eqpmt_act_effort_to_date,0)+nvl(ppr.subprj_ppl_act_effort,0)+nvl(ppr.subprj_eqpmt_act_effort,0)))) ,(nvl(ppr.earned_value,0)/decode((nvl(ppr.oth_act_cost_to_date_pc,0)+nvl(ppr.ppl_act_cost_to_date_pc,0)+nvl(ppr.eqpmt_act_cost_to_date_pc,0) +nvl(ppr.SUBPRJ_OTH_ACT_COST_TO_DT_PC,0)+nvl(ppr.subprj_ppl_act_cost_pc,0)+nvl(ppr.subprj_eqpmt_act_cost_pc,0)),0,1, (nvl(ppr.oth_act_cost_to_date_pc,0)+nvl(ppr.ppl_act_cost_to_date_pc,0) +nvl(ppr.eqpmt_act_cost_to_date_pc,0)+nvl(ppr.SUBPRJ_OTH_ACT_COST_TO_DT_PC,0)+nvl(ppr.subprj_ppl_act_cost_pc,0)+nvl(ppr.subprj_eqpmt_act_cost_pc,0))))),2) Cost_performance_index, decode(round ( (nvl(ppr.earned_value,0)/decode(nvl(pa_progress_utils.get_bcws(ppa.project_id,ppr.object_id,ppe.proj_element_id, pa_progress_utils.get_prog_asofdate(),ppev.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,ppe.proj_element_id,pa_progress_utils.get_prog_asofdate(),ppev.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,ppa.project_id,ppe.proj_element_id,'PA_TASKS','E'), pa_progress_utils.return_start_end_date(ppvsch.scheduled_start_date,ppe.baseline_start_date,ppa.project_id,ppe.proj_element_id,'PA_TASKS','S')+ ((pa_progress_utils.return_start_end_date(ppvsch.scheduled_finish_date,ppe.baseline_finish_date,ppa.project_id,ppe.proj_element_id,'PA_TASKS','E')- pa_progress_utils.return_start_end_date(ppvsch.scheduled_start_date,ppe.baseline_start_date,ppa.project_id,ppe.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,ppe.proj_element_id, pa_progress_utils.get_prog_asofdate(),ppev.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,ppe.proj_element_id,pa_progress_utils.get_prog_asofdate(),ppev.parent_structure_version_id ,pppa.task_weight_basis_code,ppe.baseline_start_date,ppe.baseline_finish_date,ppa.project_currency_code),0))),2))) sch_at_completion, ppr.LAST_UPDATE_DATE, ppr.LAST_UPDATE_DATE, ppvs.element_version_id, (ppr.actual_finish_date - ppvsch.scheduled_finish_date) Actual_Sch_Finish_Var, (ppr.actual_start_date - ppvsch.scheduled_start_date) Actual_Sch_Start_Var, ppr.cumulative_work_quantity Actual_Work_Quantity, to_char(null) Column_Name, (ppvsch.scheduled_finish_date - sysdate) Days_Until_Finish, (ppvsch.estimated_finish_date - ppvsch.scheduled_finish_date) Estimated_Sch_Finish_Var, (ppvsch.scheduled_finish_date - ppe.baseline_finish_date) Sch_Baseline_Finish_Var, (ppvsch.scheduled_start_date - ppe.baseline_start_date) Sch_Baseline_Start_Var, ppvsch.late_start_date late_start_date, ppvsch.late_finish_date late_finish_date, 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, to_number(null) baseline_schedule_var, decode(ppvsch.actual_finish_date,null,(ppvsch.estimated_finish_date-ppvsch.scheduled_finish_date),(ppvsch.actual_finish_date-ppvsch.scheduled_finish_date)) variance_from_schedule, 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))) -(nvl(pfxat.labor_hours,0)+nvl(pfxat.equipment_hours,0)) eac_planned_effort_var, pa_progress_utils.get_act_effort_this_period(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id) actual_effort_this_period, pppa.allow_phy_prcnt_cmp_overrides ,ppa.projfunc_currency_code projfunc_currency_code ,ppr.task_wt_basis_code progress_rollup_method ,decode(ppev.object_type, 'PA_STRUCTURES', 'N' , decode(ppev.prg_group, null, 'N', decode(PA_PROGRESS_UTILS.check_assignment_exists(ppev.project_id, ppev.element_version_id, 'PA_TASKS') ,'Y', 'N', 'Y'))) linked_task_flag ,decode(ppev.object_type, 'PA_STRUCTURES', 'N',decode(ppa.structure_sharing_code, 'SHARE_FULL', 'N' , decode(ppev.prg_group, null, 'N', decode(PA_PROGRESS_UTILS.check_assignment_exists(ppev.project_id, ppev.element_version_id, 'PA_TASKS') ,'Y', 'N', 'Y')))) linked_task_flag_shared ,pa_progress_utils.get_self_amounts('ACT_COST', ppa.structure_sharing_code, ppev.prg_group, ppr.project_id, ppr.object_version_id, ppr.proj_element_id, ppr.as_of_date, ppr.current_flag, ppr.record_version_number) self_actual_cost ,pa_progress_utils.get_self_amounts('ACT_EFFORT', ppa.structure_sharing_code, ppev.prg_group, ppr.project_id, ppr.object_version_id, ppr.proj_element_id, ppr.as_of_date, ppr.current_flag, ppr.record_version_number) self_actual_effort ,pa_progress_utils.get_self_amounts('ETC_COST', ppa.structure_sharing_code, ppev.prg_group, ppr.project_id, ppr.object_version_id, ppr.proj_element_id, ppr.as_of_date, ppr.current_flag, ppr.record_version_number) self_etc_cost ,pa_progress_utils.get_self_amounts('ETC_EFFORT', ppa.structure_sharing_code, ppev.prg_group, ppr.project_id, ppr.object_version_id, ppr.proj_element_id, ppr.as_of_date, ppr.current_flag, ppr.record_version_number) self_etc_effort From pa_projects_all ppa, pa_proj_elements ppe, pa_proj_element_versions ppev, pa_proj_elem_ver_structure ppvs, pa_proj_elem_ver_schedule ppvsch, pa_proj_structure_types ppst, pa_task_types ptt, pa_proj_progress_attr pppa, pa_project_statuses pps, pa_project_statuses pps2, pa_project_statuses pps3, pa_percent_completes ppc, pa_progress_rollup ppr, pji_fm_xbs_accum_tmp1 pfxat Where ppa.project_id = ppvs.project_id AND ppvs.proj_element_id = ppst.proj_element_id AND ppst.structure_type_id = 1 and ppvs.latest_eff_published_flag = 'Y' and ppvs.project_id = ppe.project_id and ppe.link_task_flag = 'N' and ppe.proj_element_id = ppev.proj_element_id and ppev.parent_structure_version_id = ppvs.element_version_id and ppev.project_id = ppvs.project_id and ppvsch.project_id = ppa.project_id and ppvsch.element_version_id = ppev.element_version_id and ppe.type_id = ptt.task_type_id(+) and ptt.object_type (+) = 'PA_TASKS' and ppvs.project_id = pppa.project_id and ppvs.proj_element_id = pppa.object_id and ppe.project_id = ppr.project_id(+) and ppe.proj_element_id = ppr.object_id(+) and ppe.object_type = ppr.object_type(+) and ppr.structure_type(+) = 'WORKPLAN' and ppr.structure_version_id(+) is null and ppc.structure_type(+) = 'WORKPLAN' and ppr.percent_complete_id = ppc.percent_complete_id(+) and ppr.progress_rollup_id(+) = pa_progress_utils.get_w_pub_prupid_asofdate(ppe.project_id,ppe.proj_element_id,ppe.object_Type,ppe.proj_element_id,pa_progress_utils.get_prog_asofdate(),'N') and nvl(ppr.PROGRESS_STATUS_CODE,nvl(ppr.eff_rollup_prog_stat_code,nvl(ptt.initial_progress_status_code,'PROGRESS_STAT_ON_TRACK'))) = pps.project_status_code and ppr.eff_rollup_prog_stat_code = pps2.project_status_code(+) and nvl(ppc.status_code,nvl(ppe.status_code,'124')) = pps3.project_status_code AND pfxat.project_id(+) = ppev.project_id AND pfxat.struct_version_id(+) = ppev.parent_structure_version_id AND pfxat.project_element_id(+) = ppev.proj_element_id AND pfxat.calendar_type(+) = 'A' AND pfxat.plan_version_id(+) > 0 AND pfxat.txn_currency_code(+) is null AND pppa.structure_type = 'WORKPLAN'

View Text - HTML Formatted

SELECT /*+ INDEX(PFXAT PJI_FM_XBS_ACCUM_TMP1_N1)*/ DECODE(PPC.PUBLISHED_FLAG
, 'Y'
, TO_NUMBER(NULL)
, PPC.PERCENT_COMPLETE_ID)
, PPA.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PPA.LONG_NAME
, PPE.PROJ_ELEMENT_ID
, PPE.PROJ_ELEMENT_ID
, TO_NUMBER(NULL)
, PPEV.ELEMENT_VERSION_ID
, TO_NUMBER(NULL)
, PPE.OBJECT_TYPE
, 'WORKPLAN'
, DECODE( PPE.OBJECT_TYPE
, 'PA_TASKS'
, PPE.NAME
, 'PA_STRUCTURES'
, PPVS.NAME )
, DECODE( PPE.OBJECT_TYPE
, 'PA_TASKS'
, PPE.ELEMENT_NUMBER
, 'PA_STRUCTURES'
, TO_CHAR( PPVS.VERSION_NUMBER ) )
, PPE.MANAGER_PERSON_ID
, NULL
, PPEV.WBS_LEVEL
, PPEV.WBS_NUMBER
, PPVSCH.MILESTONE_FLAG
, PPVSCH.CRITICAL_FLAG
, PPE.PRIORITY_CODE
, NULL PRIORITY_DESCRIPTION
, 'Y'
, PPR.AS_OF_DATE
, PPC.DESCRIPTION
, PPC.PROGRESS_COMMENT
, DECODE(PPE.OBJECT_TYPE
, 'PA_STRUCTURES'
, NVL(PPC.PROGRESS_STATUS_CODE
, NVL(PPR.EFF_ROLLUP_PROG_STAT_CODE
, 'PROGRESS_STAT_ON_TRACK'))
, 'PA_TASKS'
, NVL(PPC.PROGRESS_STATUS_CODE
, NVL(PPR.EFF_ROLLUP_PROG_STAT_CODE
, PTT.INITIAL_PROGRESS_STATUS_CODE)))
, PPS.PROJECT_STATUS_NAME
, PPS.PROJECT_SYSTEM_STATUS_CODE
, PPS2.STATUS_ICON_IND
, PPS2.STATUS_ICON_ACTIVE_IND
, PPVSCH.SCHEDULED_START_DATE
, PPVSCH.SCHEDULED_FINISH_DATE
, PPR.ACTUAL_START_DATE
, PPR.ACTUAL_FINISH_DATE
, PPR.ESTIMATED_START_DATE
, PPR.ESTIMATED_FINISH_DATE
, NVL(PPR.COMPLETED_PERCENTAGE
, PPR.EFF_ROLLUP_PERCENT_COMP) COMPLETED_PERCENTAGE
, PPC.LAST_UPDATE_DATE
, PPC.PUBLISHED_BY_PARTY_ID
, NULL PUBLISHED_BY_PARTY_NAME
, PPC.CURRENT_FLAG
, PPC.PUBLISHED_FLAG
, DECODE( PPEV.OBJECT_TYPE
, 'PA_STRUCTURES'
, 'Y'
, 'PA_TASKS'
, PA_PROJ_ELEMENTS_UTILS.IS_SUMMARY_TASK_OR_STRUCTURE( PPEV.ELEMENT_VERSION_ID ) ) SUMMARY_ELEMENT_FLAG
, PPC.RECORD_VERSION_NUMBER
, NVL(PPC.STATUS_CODE
, NVL(PPE.STATUS_CODE
, '124'))
, PPS3.PROJECT_STATUS_NAME
, PPS3.PROJECT_SYSTEM_STATUS_CODE
, ROUND(PPVSCH.WQ_PLANNED_QUANTITY
, 2)
, PPR.INCREMENTAL_WORK_QUANTITY
, PPR.CUMULATIVE_WORK_QUANTITY
, NVL(PFXAT.LABOR_HOURS
, 0) + NVL(PFXAT.EQUIPMENT_HOURS
, 0)
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.BASE_PERCENT_COMPLETE
, DECODE(PPE.OBJECT_TYPE
, 'PA_STRUCTURES'
, PPPA.WQ_ENABLE_FLAG
, DECODE(PTT.WQ_ENABLE_FLAG
, 'Y'
, DECODE(PPPA.WQ_ENABLE_FLAG
, 'Y'
, 'Y'
, 'N')
, 'N'))
, DECODE(PPE.OBJECT_TYPE
, 'PA_STRUCTURES'
, PPPA.REMAIN_EFFORT_ENABLE_FLAG
, DECODE(PTT.REMAIN_EFFORT_ENABLE_FLAG
, 'Y'
, DECODE(PPPA.REMAIN_EFFORT_ENABLE_FLAG
, 'Y'
, 'Y'
, 'N')
, 'N'))
, PTT.BASE_PERCENT_COMP_DERIV_CODE
, NVL(PPE.WQ_ACTUAL_ENTRY_CODE
, PTT.ACTUAL_WQ_ENTRY_CODE)
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPE.LAST_UPDATE_DATE
, PPVSCH.LAST_UPDATE_DATE
, PPR.EFF_ROLLUP_PERCENT_COMP
, PPR.RECORD_VERSION_NUMBER
, NVL(PPE.WQ_ITEM_CODE
, PTT.WORK_ITEM_CODE)
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_WQ_WORK_ITEMS'
, NVL(PPE.WQ_ITEM_CODE
, PTT.WORK_ITEM_CODE))
, PPE.WQ_UOM_CODE
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('UNIT'
, PPE.WQ_UOM_CODE)
, PPR.BASE_PROGRESS_STATUS_CODE
, NULL BASE_PROGRESS_STATUS_NAME
, PPR.EFF_ROLLUP_PROG_STAT_CODE
, PPS2.PROJECT_STATUS_NAME
, PA_PROGRESS_UTILS.GET_NEXT_PROGRESS_CYCLE(PPE.PROJECT_ID
, PPE.PROJ_ELEMENT_ID)
, PA_PROGRESS_UTILS.GET_PROG_DT_CLOSEST_TO_SYS_DT(PPE.PROJECT_ID
, PPE.PROJ_ELEMENT_ID)
, 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'))
, DECODE(PPE.OBJECT_TYPE
, 'PA_TASKS'
, PTT.PROG_ENTRY_ENABLE_FLAG
, 'Y')
, PTT.PROG_ENTRY_REQ_FLAG
, PPE.ROWID
, 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))
, ( NVL(PPR.OTH_ETC_COST_PC
, 0)+NVL(PPR.PPL_ETC_COST_PC
, 0)+NVL(PPR.EQPMT_ETC_COST_PC
, 0)+NVL(PPR.SUBPRJ_OTH_ETC_COST_PC
, 0) +NVL(PPR.SUBPRJ_PPL_ETC_COST_PC
, 0) +NVL(PPR.SUBPRJ_EQPMT_ETC_COST_PC
, 0)) ) PERCENT_COMPLETE_COST
, 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))
, ( NVL(PPR.ESTIMATED_REMAINING_EFFORT
, 0)+NVL(PPR.EQPMT_ETC_EFFORT
, 0)+NVL(PPR.SUBPRJ_PPL_ETC_EFFORT
, 0) +NVL(PPR.SUBPRJ_EQPMT_ETC_EFFORT
, 0)) ) PERCENT_COMPLETE_EFFORT
, 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.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
, 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
, 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
, PFXAT.PRJ_BASE_BRDN_COST BASELINE_COST
, NVL(PFXAT.BASE_LABOR_HOURS
, 0)+NVL(PFXAT.BASE_EQUIP_HOURS
, 0) BASELINED_EFFORT
, (PA_PROGRESS_UTILS.GET_NEXT_PROGRESS_CYCLE(PPA.PROJECT_ID
, PPE.PROJ_ELEMENT_ID)-SYSDATE) DAYS_UNTIL_PROGRESS_DUE
, (TRUNC(PPVSCH.SCHEDULED_START_DATE)-TRUNC(SYSDATE)) DAYS_UNTIL_START
, PPR.EARNED_VALUE EARNED_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))) EST_AT_COMPLETION_COST
, 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))) EST_AT_COMPLETION_EFFORT
, (PPR.ESTIMATED_START_DATE - PPE.BASELINE_START_DATE) EST_BAS_START_VAR
, (PPR.ESTIMATED_FINISH_DATE - PPE.BASELINE_FINISH_DATE) EST_BAS_FINISH_VAR
, 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))) ETC_COST
, 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))) ETC_EFFORT
, ROUND(( NVL(PPVSCH.WQ_PLANNED_QUANTITY
, 0) - NVL(CUMULATIVE_WORK_QUANTITY
, 0))
, 2) ETC_WORK_QUANTITY
, NVL(PFXAT.PRJ_BRDN_COST
, 0) PLANNED_COST
, PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((NVL(PFXAT.PRJ_BRDN_COST
, 0)/DECODE(NVL(CUMULATIVE_WORK_QUANTITY
, 0)
, 0
, 1
, NVL(CUMULATIVE_WORK_QUANTITY
, 0)))
, PPA.PROJECT_CURRENCY_CODE) PLANNED_COST_PER_UNIT
, PA_RELATIONSHIP_UTILS.DISPLAY_PREDECESSORS(PPE.PROJ_ELEMENT_ID) PREDECESSORS
, TO_CHAR(NULL) RESOURCES
, ROUND((NVL(PPR.EARNED_VALUE
, 0)/DECODE(NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPE.PROJ_ELEMENT_ID
, PA_PROGRESS_UTILS.GET_PROG_ASOFDATE()
, PPEV.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
, PPE.PROJ_ELEMENT_ID
, PA_PROGRESS_UTILS.GET_PROG_ASOFDATE()
, PPEV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)))
, 2) SCH_PERFORMANCE_INDEX
, ROUND(DECODE (PPR.TASK_WT_BASIS_CODE
, 'EFFORT'
, (((NVL(PFXAT.BASE_LABOR_HOURS
, 0)+NVL(PFXAT.BASE_EQUIP_HOURS
, 0))-PPR.EARNED_VALUE)/DECODE(((NVL(PFXAT.BASE_LABOR_HOURS
, 0) +NVL(PFXAT.BASE_EQUIP_HOURS
, 0))-(NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0)))
, 0
, 1
, ((NVL(PFXAT.BASE_LABOR_HOURS
, 0)+NVL(PFXAT.BASE_EQUIP_HOURS
, 0))-(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.PRJ_BASE_BRDN_COST
, 0)-PPR.EARNED_VALUE)/DECODE((NVL(PFXAT.PRJ_BASE_BRDN_COST
, 0)-(NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0) +NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0)))
, 0
, 1
, (NVL(PFXAT.PRJ_BASE_BRDN_COST
, 0)-(NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0) +NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))))))
, 2) TO_COMPLETE_PERFORMANCE_INDEX
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('UNIT'
, 'HOURS') UNIT_OF_MEASURE
, NVL(PFXAT.PRJ_BASE_BRDN_COST
, 0)-(NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_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)))) VAR_AT_COMPLETION_COST
, NVL(PFXAT.BASE_LABOR_HOURS
, 0)+NVL(PFXAT.BASE_EQUIP_HOURS
, 0)-(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)))) VAR_AT_COMPLETION_EFFORT
, TO_NUMBER(NULL) WORK_TYPE
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPE.PROJ_ELEMENT_ID
, PA_PROGRESS_UTILS.GET_DEF_AS_OF_DATE_PROG_REPORT(PPA.PROJECT_ID
, PPE.PROJ_ELEMENT_ID)
, PPEV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0) BCWS
, PPA.PROJECT_CURRENCY_CODE
, 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)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0) +NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0))
, 0
, 1
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_EFFORT
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_EFFORT
, 0))))
, (NVL(PPR.EARNED_VALUE
, 0)/DECODE((NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0))
, 0
, 1
, (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0) +NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.SUBPRJ_OTH_ACT_COST_TO_DT_PC
, 0)+NVL(PPR.SUBPRJ_PPL_ACT_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ACT_COST_PC
, 0)))))
, 2) COST_PERFORMANCE_INDEX
, DECODE(ROUND ( (NVL(PPR.EARNED_VALUE
, 0)/DECODE(NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPE.PROJ_ELEMENT_ID
, PA_PROGRESS_UTILS.GET_PROG_ASOFDATE()
, PPEV.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
, PPE.PROJ_ELEMENT_ID
, PA_PROGRESS_UTILS.GET_PROG_ASOFDATE()
, PPEV.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
, PPA.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, 'PA_TASKS'
, 'E')
, PA_PROGRESS_UTILS.RETURN_START_END_DATE(PPVSCH.SCHEDULED_START_DATE
, PPE.BASELINE_START_DATE
, PPA.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, 'PA_TASKS'
, 'S')+ ((PA_PROGRESS_UTILS.RETURN_START_END_DATE(PPVSCH.SCHEDULED_FINISH_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, 'PA_TASKS'
, 'E')- PA_PROGRESS_UTILS.RETURN_START_END_DATE(PPVSCH.SCHEDULED_START_DATE
, PPE.BASELINE_START_DATE
, PPA.PROJECT_ID
, PPE.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
, PPE.PROJ_ELEMENT_ID
, PA_PROGRESS_UTILS.GET_PROG_ASOFDATE()
, PPEV.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
, PPE.PROJ_ELEMENT_ID
, PA_PROGRESS_UTILS.GET_PROG_ASOFDATE()
, PPEV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, PPA.PROJECT_CURRENCY_CODE)
, 0)))
, 2))) SCH_AT_COMPLETION
, PPR.LAST_UPDATE_DATE
, PPR.LAST_UPDATE_DATE
, PPVS.ELEMENT_VERSION_ID
, (PPR.ACTUAL_FINISH_DATE - PPVSCH.SCHEDULED_FINISH_DATE) ACTUAL_SCH_FINISH_VAR
, (PPR.ACTUAL_START_DATE - PPVSCH.SCHEDULED_START_DATE) ACTUAL_SCH_START_VAR
, PPR.CUMULATIVE_WORK_QUANTITY ACTUAL_WORK_QUANTITY
, TO_CHAR(NULL) COLUMN_NAME
, (PPVSCH.SCHEDULED_FINISH_DATE - SYSDATE) DAYS_UNTIL_FINISH
, (PPVSCH.ESTIMATED_FINISH_DATE - PPVSCH.SCHEDULED_FINISH_DATE) ESTIMATED_SCH_FINISH_VAR
, (PPVSCH.SCHEDULED_FINISH_DATE - PPE.BASELINE_FINISH_DATE) SCH_BASELINE_FINISH_VAR
, (PPVSCH.SCHEDULED_START_DATE - PPE.BASELINE_START_DATE) SCH_BASELINE_START_VAR
, PPVSCH.LATE_START_DATE LATE_START_DATE
, PPVSCH.LATE_FINISH_DATE LATE_FINISH_DATE
, 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
, TO_NUMBER(NULL) BASELINE_SCHEDULE_VAR
, DECODE(PPVSCH.ACTUAL_FINISH_DATE
, NULL
, (PPVSCH.ESTIMATED_FINISH_DATE-PPVSCH.SCHEDULED_FINISH_DATE)
, (PPVSCH.ACTUAL_FINISH_DATE-PPVSCH.SCHEDULED_FINISH_DATE)) VARIANCE_FROM_SCHEDULE
, 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))) -(NVL(PFXAT.LABOR_HOURS
, 0)+NVL(PFXAT.EQUIPMENT_HOURS
, 0)) EAC_PLANNED_EFFORT_VAR
, PA_PROGRESS_UTILS.GET_ACT_EFFORT_THIS_PERIOD(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID) ACTUAL_EFFORT_THIS_PERIOD
, PPPA.ALLOW_PHY_PRCNT_CMP_OVERRIDES
, PPA.PROJFUNC_CURRENCY_CODE PROJFUNC_CURRENCY_CODE
, PPR.TASK_WT_BASIS_CODE PROGRESS_ROLLUP_METHOD
, DECODE(PPEV.OBJECT_TYPE
, 'PA_STRUCTURES'
, 'N'
, DECODE(PPEV.PRG_GROUP
, NULL
, 'N'
, DECODE(PA_PROGRESS_UTILS.CHECK_ASSIGNMENT_EXISTS(PPEV.PROJECT_ID
, PPEV.ELEMENT_VERSION_ID
, 'PA_TASKS')
, 'Y'
, 'N'
, 'Y'))) LINKED_TASK_FLAG
, DECODE(PPEV.OBJECT_TYPE
, 'PA_STRUCTURES'
, 'N'
, DECODE(PPA.STRUCTURE_SHARING_CODE
, 'SHARE_FULL'
, 'N'
, DECODE(PPEV.PRG_GROUP
, NULL
, 'N'
, DECODE(PA_PROGRESS_UTILS.CHECK_ASSIGNMENT_EXISTS(PPEV.PROJECT_ID
, PPEV.ELEMENT_VERSION_ID
, 'PA_TASKS')
, 'Y'
, 'N'
, 'Y')))) LINKED_TASK_FLAG_SHARED
, PA_PROGRESS_UTILS.GET_SELF_AMOUNTS('ACT_COST'
, PPA.STRUCTURE_SHARING_CODE
, PPEV.PRG_GROUP
, PPR.PROJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPR.CURRENT_FLAG
, PPR.RECORD_VERSION_NUMBER) SELF_ACTUAL_COST
, PA_PROGRESS_UTILS.GET_SELF_AMOUNTS('ACT_EFFORT'
, PPA.STRUCTURE_SHARING_CODE
, PPEV.PRG_GROUP
, PPR.PROJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPR.CURRENT_FLAG
, PPR.RECORD_VERSION_NUMBER) SELF_ACTUAL_EFFORT
, PA_PROGRESS_UTILS.GET_SELF_AMOUNTS('ETC_COST'
, PPA.STRUCTURE_SHARING_CODE
, PPEV.PRG_GROUP
, PPR.PROJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPR.CURRENT_FLAG
, PPR.RECORD_VERSION_NUMBER) SELF_ETC_COST
, PA_PROGRESS_UTILS.GET_SELF_AMOUNTS('ETC_EFFORT'
, PPA.STRUCTURE_SHARING_CODE
, PPEV.PRG_GROUP
, PPR.PROJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPR.CURRENT_FLAG
, PPR.RECORD_VERSION_NUMBER) SELF_ETC_EFFORT
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_STRUCTURE PPVS
, PA_PROJ_ELEM_VER_SCHEDULE PPVSCH
, PA_PROJ_STRUCTURE_TYPES PPST
, PA_TASK_TYPES PTT
, PA_PROJ_PROGRESS_ATTR PPPA
, PA_PROJECT_STATUSES PPS
, PA_PROJECT_STATUSES PPS2
, PA_PROJECT_STATUSES PPS3
, PA_PERCENT_COMPLETES PPC
, PA_PROGRESS_ROLLUP PPR
, PJI_FM_XBS_ACCUM_TMP1 PFXAT
WHERE PPA.PROJECT_ID = PPVS.PROJECT_ID
AND PPVS.PROJ_ELEMENT_ID = PPST.PROJ_ELEMENT_ID
AND PPST.STRUCTURE_TYPE_ID = 1
AND PPVS.LATEST_EFF_PUBLISHED_FLAG = 'Y'
AND PPVS.PROJECT_ID = PPE.PROJECT_ID
AND PPE.LINK_TASK_FLAG = 'N'
AND PPE.PROJ_ELEMENT_ID = PPEV.PROJ_ELEMENT_ID
AND PPEV.PARENT_STRUCTURE_VERSION_ID = PPVS.ELEMENT_VERSION_ID
AND PPEV.PROJECT_ID = PPVS.PROJECT_ID
AND PPVSCH.PROJECT_ID = PPA.PROJECT_ID
AND PPVSCH.ELEMENT_VERSION_ID = PPEV.ELEMENT_VERSION_ID
AND PPE.TYPE_ID = PTT.TASK_TYPE_ID(+)
AND PTT.OBJECT_TYPE (+) = 'PA_TASKS'
AND PPVS.PROJECT_ID = PPPA.PROJECT_ID
AND PPVS.PROJ_ELEMENT_ID = PPPA.OBJECT_ID
AND PPE.PROJECT_ID = PPR.PROJECT_ID(+)
AND PPE.PROJ_ELEMENT_ID = PPR.OBJECT_ID(+)
AND PPE.OBJECT_TYPE = PPR.OBJECT_TYPE(+)
AND PPR.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PPR.STRUCTURE_VERSION_ID(+) IS NULL
AND PPC.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PPR.PERCENT_COMPLETE_ID = PPC.PERCENT_COMPLETE_ID(+)
AND PPR.PROGRESS_ROLLUP_ID(+) = PA_PROGRESS_UTILS.GET_W_PUB_PRUPID_ASOFDATE(PPE.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, PPE.OBJECT_TYPE
, PPE.PROJ_ELEMENT_ID
, PA_PROGRESS_UTILS.GET_PROG_ASOFDATE()
, 'N')
AND NVL(PPR.PROGRESS_STATUS_CODE
, NVL(PPR.EFF_ROLLUP_PROG_STAT_CODE
, NVL(PTT.INITIAL_PROGRESS_STATUS_CODE
, 'PROGRESS_STAT_ON_TRACK'))) = PPS.PROJECT_STATUS_CODE
AND PPR.EFF_ROLLUP_PROG_STAT_CODE = PPS2.PROJECT_STATUS_CODE(+)
AND NVL(PPC.STATUS_CODE
, NVL(PPE.STATUS_CODE
, '124')) = PPS3.PROJECT_STATUS_CODE
AND PFXAT.PROJECT_ID(+) = PPEV.PROJECT_ID
AND PFXAT.STRUCT_VERSION_ID(+) = PPEV.PARENT_STRUCTURE_VERSION_ID
AND PFXAT.PROJECT_ELEMENT_ID(+) = PPEV.PROJ_ELEMENT_ID
AND PFXAT.CALENDAR_TYPE(+) = 'A'
AND PFXAT.PLAN_VERSION_ID(+) > 0
AND PFXAT.TXN_CURRENCY_CODE(+) IS NULL
AND PPPA.STRUCTURE_TYPE = 'WORKPLAN'