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_TASKS_ASSIGNS_PROGRESS_V

Source

View Text - Preformatted


SELECT pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','B','T') actual_cost, pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'E',null,null) actual_effort, ppr.actual_finish_date, ppr.actual_start_date, ppr.as_of_date as_of_date, ptav.baselined_asgmt_end_date, ptav.baselined_asgmt_start_date, nvl(ppr.oth_act_cost_to_date_tc,0)+nvl(ppr.ppl_act_cost_to_date_tc,0)+nvl(ppr.eqpmt_act_cost_to_date_tc,0) +pa_progress_utils.sum_etc_values(ptav.planned_bur_cost_txn_cur,ppr.ppl_etc_cost_tc,ppr.eqpmt_etc_cost_tc,ppr.oth_etc_cost_tc,null,null,null,null ,(nvl(ppr.oth_act_cost_to_date_tc,0)+nvl(ppr.ppl_act_cost_to_date_tc,0)+nvl(ppr.eqpmt_act_cost_to_date_tc,0))) Est_at_Completion_Cost, nvl(ppr.ppl_act_effort_to_date,0)+nvl(ppr.eqpmt_act_effort_to_date,0)+nvl(ppr.oth_quantity_to_date,0) +pa_progress_utils.sum_etc_values(ptav.planned_quantity,ppr.estimated_remaining_effort,ppr.eqpmt_etc_effort,null,null,null,null,ppr.oth_etc_quantity ,(nvl(ppr.ppl_act_effort_to_date,0)+nvl(ppr.eqpmt_act_effort_to_date,0)+nvl(ppr.oth_quantity_to_date,0))) Est_at_Completion_Effort, pa_progress_utils.sum_etc_values(ptav.planned_quantity,ppr.estimated_remaining_effort,ppr.eqpmt_etc_effort,null,null,null,null,ppr.oth_etc_quantity ,(nvl(ppr.ppl_act_effort_to_date,0)+nvl(ppr.eqpmt_act_effort_to_date,0)+nvl(ppr.oth_quantity_to_date,0))) estimated_remaining_effort, ppr.estimated_finish_date, ppr.estimated_start_date, pa_progress_utils.sum_etc_values(ptav.planned_bur_cost_txn_cur,ppr.ppl_etc_cost_tc,ppr.eqpmt_etc_cost_tc,ppr.oth_etc_cost_tc,null,null,null,null ,(nvl(ppr.oth_act_cost_to_date_tc,0)+nvl(ppr.ppl_act_cost_to_date_tc,0)+nvl(ppr.eqpmt_act_cost_to_date_tc,0))) ETC_COST_TC, pa_progress_utils.sum_etc_values(ptav.planned_quantity,ppr.estimated_remaining_effort,ppr.eqpmt_etc_effort,null,null,null,null,ppr.oth_etc_quantity ,(nvl(ppr.ppl_act_effort_to_date,0)+nvl(ppr.eqpmt_act_effort_to_date,0)+nvl(ppr.oth_quantity_to_date,0))) ETC_EFFORT, pa_progress_utils.GET_NEXT_PROGRESS_CYCLE(ppe.project_id, ppe.proj_element_id, ptav.resource_list_member_id, 'PA_ASSIGNMENTS'), pa_progress_utils.GET_PROG_DT_CLOSEST_TO_SYS_DT(ppe.project_id, ppe.proj_element_id, ptav.resource_list_member_id, 'PA_ASSIGNMENTS'), PA_PROGRESS_UTILS.Percent_Complete_Value(decode(ptav.resource_class_code, 'PEOPLE', NVL(ppr.ppl_act_effort_to_date,0), 'EQUIPMENT', NVL(ppr.eqpmt_act_effort_to_date,0),NVL(ppr.oth_quantity_to_date,0)) ,decode(ptav.resource_class_code, 'PEOPLE', NVL(ppr.estimated_remaining_effort , decode( sign(nvl(ptav.planned_quantity,0)-nvl(ppr.PPL_ACT_EFFORT_TO_DATE,0)), -1, 0, nvl( ptav.planned_quantity-ppr.PPL_ACT_EFFORT_TO_DATE,0))), 'EQUIPMENT', NVL(ppr.eqpmt_etc_effort, decode( sign(nvl(ptav.planned_quantity,0)-nvl(ppr.EQPMT_ACT_EFFORT_TO_DATE,0)), -1, 0, nvl( ptav.planned_quantity-ppr.EQPMT_ACT_EFFORT_TO_DATE,0))) , NVL(ppr.oth_etc_quantity, decode( sign(nvl(ptav.planned_quantity,0)-nvl(ppr.oth_quantity_to_date,0)), -1, 0, nvl( ptav.planned_quantity-ppr.oth_quantity_to_date,0)))))Percent_Complete_Effort, ppc.percent_complete_id, PA_PROGRESS_UTILS.Percent_Spent_Value( (NVL(ppr.ppl_act_effort_to_date,0)+NVL(ppr.eqpmt_act_effort_to_date,0) +NVL(ppr.oth_quantity_to_date,0)), (NVL(ptav.planned_quantity,0)) ) percent_Spent_Effort, nvl(ptav.planned_bur_cost_txn_cur,0) Planned_Cost, ptav.planned_quantity planned_effort, ptav.assignment_end_date, ptav.assignment_start_date, ppe.proj_element_id, ppe.name, ppe.element_number, to_char(null) Unit_of_Measure, ppev.wbs_number, ptav.resource_assignment_id Resource_Assignment_Id, ppc.current_flag, ppev.element_version_id, ppev.object_type, ppe.proj_element_id, ppa.project_id, ppa.name, ppa.segment1, ppc.published_flag, ppc.record_version_number, ptav.unit_of_measure_code, ptav.unit_of_measure, ptav.txn_currency_code, 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')), decode(ppe.object_type,'PA_TASKS',ptt.PROG_ENTRY_ENABLE_FLAG,'Y'), ptav.resource_alias, ptav.resource_list_member_id, ptav.resource_class_code, ptav.rbs_element_id, ptav.rate_based_flag rate_based_flag, decode(ptav.resource_class_code, 'PEOPLE', ppr.PPL_ACT_EFFORT_TO_DATE, 'EQUIPMENT', ppr.EQPMT_ACT_EFFORT_TO_DATE, ppr.oth_quantity_to_date)- nvl(pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'E',null,null),0) actual_effort_this_period, decode(ptav.resource_class_code, 'PEOPLE', ppr.PPL_ACT_COST_TO_DATE_TC, 'EQUIPMENT', ppr.EQPMT_ACT_COST_TO_DATE_TC, ppr.OTH_ACT_COST_TO_DATE_TC) -nvl(pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','B','T'),0) actual_cost_this_period, pa_progress_utils.get_latest_progress_entry_date(ppa.project_id,ppr.object_id,ppr.object_type,ppr.structure_type,ppe.proj_element_id) latest_progress_date, ppev.parent_structure_version_id structure_version_id, pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','R','T') actual_rawcost ,nvl(ptav.PLANNED_RAW_COST_TXN_CUR,0) planned_rawcost ,ptav.BL_PLANNED_RAW_COST_TXN_CUR baselined_rawcost ,nvl(ppr.oth_act_rawcost_to_date_tc,0)+nvl(ppr.ppl_act_rawcost_to_date_tc,0)+nvl(ppr.eqpmt_act_rawcost_to_date_tc,0) +pa_progress_utils.sum_etc_values(ptav.PLANNED_RAW_COST_TXN_CUR,ppr.ppl_etc_rawcost_tc,ppr.eqpmt_etc_rawcost_tc,ppr.oth_etc_rawcost_tc,null,null,null,null ,(nvl(ppr.oth_act_rawcost_to_date_tc,0)+nvl(ppr.ppl_act_rawcost_to_date_tc,0)+nvl(ppr.eqpmt_act_rawcost_to_date_tc,0))) est_at_completion_rawcost ,pa_progress_utils.sum_etc_values(ptav.PLANNED_RAW_COST_TXN_CUR,ppr.ppl_etc_rawcost_tc,ppr.eqpmt_etc_rawcost_tc,ppr.oth_etc_rawcost_tc,null,null,null,null ,(nvl(ppr.oth_act_rawcost_to_date_tc,0)+nvl(ppr.ppl_act_rawcost_to_date_tc,0)+nvl(ppr.eqpmt_act_rawcost_to_date_tc,0))) etc_rawcost_tc ,PA_PROGRESS_UTILS.Percent_Complete_Value( (NVL(ppr.oth_act_rawcost_to_date_tc,0)+NVL(ppr.ppl_act_rawcost_to_date_tc,0) +NVL(ppr.eqpmt_act_rawcost_to_date_tc,0)), ( NVL(ppr.oth_etc_rawcost_tc,0)+NVL(ppr.ppl_etc_rawcost_tc,0)+NVL(ppr.eqpmt_etc_rawcost_tc,0)) ) Percent_Complete_Rawcost ,PA_PROGRESS_UTILS.Percent_Spent_Value( (NVL(ppr.OTH_ACT_COST_TO_DATE_PC,0)+NVL(ppr.ppl_act_rawcost_to_date_tc,0) +NVL(ppr.eqpmt_act_rawcost_to_date_tc,0)), (nvl(ptav.PLANNED_RAW_COST_TXN_CUR,0)) ) Percent_Spent_Rawcost ,decode(ptav.resource_class_code, 'PEOPLE', ppr.PPL_ACT_rawcost_TO_DATE_TC, 'EQUIPMENT', ppr.EQPMT_ACT_rawcost_TO_DATE_TC,ppr.OTH_ACT_rawcost_TO_DATE_TC) - nvl(pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','R','T'),0) actual_rawcost_this_period ,ptav.unplanned_flag unplanned_assignments_flag ,nvl(ptav.PLANNED_BUR_COST_PROJ_CUR,0) planned_cost_bur_pc ,pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','B','P') actual_cost_bur_pc ,decode(ptav.resource_class_code, 'PEOPLE', ppr.PPL_ACT_COST_TO_DATE_PC, 'EQUIPMENT', ppr.EQPMT_ACT_COST_TO_DATE_PC, ppr.OTH_ACT_COST_TO_DATE_PC) - nvl(pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','B','P'),0) actual_cost_this_bur_pc ,pa_progress_utils.sum_etc_values(ptav.PLANNED_BUR_COST_PROJ_CUR,ppr.ppl_etc_cost_pc,ppr.eqpmt_etc_cost_pc,ppr.oth_etc_cost_pc,null, null,null,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))) ETC_cost_bur_pc ,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) +pa_progress_utils.sum_etc_values(ptav.PLANNED_BUR_COST_PROJ_CUR,ppr.ppl_etc_cost_pc,ppr.eqpmt_etc_cost_pc,ppr.oth_etc_cost_pc, null,null,null,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))) Est_at_Completion_cost_bur_pc ,ppr.progress_rollup_id ,ppa.project_currency_code ,ppr.current_flag ppr_current_flag ,PA_PROGRESS_UTILS.Percent_Complete_Value(decode(ptav.resource_class_code, 'PEOPLE', NVL(ppr.PPL_ACT_COST_TO_DATE_PC,0), 'EQUIPMENT', NVL(ppr.EQPMT_ACT_COST_TO_DATE_PC,0),NVL(ppr.OTH_ACT_COST_TO_DATE_PC,0)) ,decode(ptav.resource_class_code, 'PEOPLE', NVL(ppr.PPL_ETC_COST_PC , decode( sign(nvl(ptav.planned_bur_cost_proj_cur,0)-nvl(ppr.PPL_ACT_COST_TO_DATE_PC,0)), -1, 0, nvl( ptav.planned_bur_cost_proj_cur-ppr.PPL_ACT_COST_TO_DATE_PC,0))), 'EQUIPMENT', NVL(ppr.EQPMT_ETC_COST_PC, decode( sign(nvl(ptav.planned_bur_cost_proj_cur,0)-nvl(ppr.EQPMT_ACT_COST_TO_DATE_PC,0)), -1, 0, nvl( ptav.planned_bur_cost_proj_cur-ppr.EQPMT_ACT_COST_TO_DATE_PC,0))) , NVL(ppr.OTH_ETC_COST_PC, decode( sign(nvl(ptav.planned_bur_cost_proj_cur,0)-nvl(ppr.OTH_ACT_COST_TO_DATE_PC,0)), -1, 0, nvl( ptav.planned_bur_cost_proj_cur-ppr.OTH_ACT_COST_TO_DATE_PC,0)))))Percent_Complete_bur_cost_pc ,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(ptav.planned_bur_cost_proj_cur,0)) ) Percent_Spent_bur_cost_pc, ppc.progress_comment, ppc.description , PA_PROGRESS_UTILS.Percent_Spent_Value( (NVL(ppr.oth_act_rawcost_to_date_tc,0)+NVL(ppr.ppl_act_rawcost_to_date_tc,0) +NVL(ppr.eqpmt_act_rawcost_to_date_tc,0)),(nvl(ptav.PLANNED_RAW_COST_TXN_CUR,0)) ) pct_spent_rawcost_cbs , PA_PROGRESS_UTILS.Percent_Complete_Value(( NVL(ppr.PPL_ACT_COST_TO_DATE_PC,0) + NVL(ppr.EQPMT_ACT_COST_TO_DATE_PC,0) + NVL(ppr.OTH_ACT_COST_TO_DATE_PC,0)) , Decode(ptav.resource_class_code, 'PEOPLE',NVL(ppr.PPL_ETC_COST_PC ,decode( sign(nvl(ptav.planned_bur_cost_proj_cur,0)-nvl(ppr.PPL_ACT_COST_TO_DATE_PC,0)),-1,0,nvl( ptav.planned_bur_cost_proj_cur-ppr.PPL_ACT_COST_TO_DATE_PC,0))) ,'EQUIPMENT',NVL(ppr.EQPMT_ETC_COST_PC,decode( sign(nvl(ptav.planned_bur_cost_proj_cur,0)-nvl(ppr.EQPMT_ACT_COST_TO_DATE_PC,0)),-1,0,nvl( ptav.planned_bur_cost_proj_cur-ppr.EQPMT_ACT_COST_TO_DATE_PC,0))) ,NVL(ppr.OTH_ETC_COST_PC,decode( sign(nvl(ptav.planned_bur_cost_proj_cur,0)-nvl(ppr.OTH_ACT_COST_TO_DATE_PC,0)),-1,0,nvl( ptav.planned_bur_cost_proj_cur-ppr.OTH_ACT_COST_TO_DATE_PC,0))) ) )Percent_Complete_bur_cost_cbs , (Nvl(ppr.PPL_ACT_rawcost_TO_DATE_TC,0)+ Nvl(ppr.EQPMT_ACT_rawcost_TO_DATE_TC,0) + Nvl(ppr.OTH_ACT_rawcost_TO_DATE_TC,0)) - nvl(pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','R','T'),0) actual_rawcost_this_period_cbs From pa_projects_all ppa, pa_proj_elements ppe, pa_proj_element_versions ppev, pa_proj_elem_ver_structure ppvs, pa_percent_completes ppc, pa_progress_rollup ppr, pa_proj_progress_attr pppa, pa_task_types ptt, pa_task_asgmts_v ptav WHERE ppe.project_id = ppvs.project_id and ppvs.latest_eff_published_flag = 'Y' and ppe.link_task_flag = 'N' and ppe.object_Type = 'PA_TASKS' and ppe.proj_element_id = ppev.proj_element_id and ppev.parent_structure_version_id = ppvs.element_version_id and ppa.project_id = ppvs.project_id and ppe.type_id = ptt.task_type_id(+) and ptt.object_type(+) = 'PA_TASKS' and ptav.project_id = ppe.project_id and ptav.task_id = ppe.proj_element_id and ptav.task_version_id = ppev.element_version_id and ptav.project_id = ppr.project_id(+) and ptav.resource_list_member_id = ppr.object_id(+) and ptav.task_id = ppr.proj_element_id(+) and ppc.project_id(+) = ppr.project_id and ppc.object_id(+) = ppr.object_id and ppc.object_type(+) = 'PA_ASSIGNMENTS' and ppc.task_id(+) = ppr.proj_element_id and 'PA_ASSIGNMENTS' = ppr.object_type(+) and ppr.structure_version_id(+) IS NULL and ppc.structure_type(+) = 'WORKPLAN' and ppr.structure_type(+) = 'WORKPLAN' and ppr.structure_version_id(+) IS NULL and ppr.percent_complete_id = ppc.percent_complete_id(+) and ppr.progress_rollup_id(+) = pa_progress_utils.get_w_pub_prupid_asofdate(ptav.project_id,ptav.resource_list_member_id,'PA_ASSIGNMENTS',ptav.task_id,pa_progress_utils.get_prog_asofdate(),'Y') and ppvs.project_id = pppa.project_id(+) and ppvs.proj_element_id = pppa.object_id(+) and pppa.structure_type(+) = 'WORKPLAN' and ptav.ta_display_flag = 'Y' and PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(ppa.project_id) = 'N' union Select pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','B','T',ppr.cbs_element_id) actual_cost, pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'E',null,null,ppr.cbs_element_id) actual_effort, ppr.actual_finish_date, ppr.actual_start_date, ppr.as_of_date as_of_date, ptav.baselined_asgmt_end_date, ptav.baselined_asgmt_start_date, nvl(ppr.oth_act_cost_to_date_tc,0)+nvl(ppr.ppl_act_cost_to_date_tc,0)+nvl(ppr.eqpmt_act_cost_to_date_tc,0) +pa_progress_utils.sum_etc_values(ptav.planned_bur_cost_txn_cur,ppr.ppl_etc_cost_tc,ppr.eqpmt_etc_cost_tc,ppr.oth_etc_cost_tc,null,null,null,null ,(nvl(ppr.oth_act_cost_to_date_tc,0)+nvl(ppr.ppl_act_cost_to_date_tc,0)+nvl(ppr.eqpmt_act_cost_to_date_tc,0))) Est_at_Completion_Cost, nvl(ppr.ppl_act_effort_to_date,0)+nvl(ppr.eqpmt_act_effort_to_date,0)+nvl(ppr.oth_quantity_to_date,0) +pa_progress_utils.sum_etc_values(ptav.planned_quantity,ppr.estimated_remaining_effort,ppr.eqpmt_etc_effort,null,null,null,null,ppr.oth_etc_quantity ,(nvl(ppr.ppl_act_effort_to_date,0)+nvl(ppr.eqpmt_act_effort_to_date,0)+nvl(ppr.oth_quantity_to_date,0))) Est_at_Completion_Effort, pa_progress_utils.sum_etc_values(ptav.planned_quantity,ppr.estimated_remaining_effort,ppr.eqpmt_etc_effort,null,null,null,null,ppr.oth_etc_quantity ,(nvl(ppr.ppl_act_effort_to_date,0)+nvl(ppr.eqpmt_act_effort_to_date,0)+nvl(ppr.oth_quantity_to_date,0))) estimated_remaining_effort, ppr.estimated_finish_date, ppr.estimated_start_date, pa_progress_utils.sum_etc_values(ptav.planned_bur_cost_txn_cur,ppr.ppl_etc_cost_tc,ppr.eqpmt_etc_cost_tc,ppr.oth_etc_cost_tc,null,null,null,null ,(nvl(ppr.oth_act_cost_to_date_tc,0)+nvl(ppr.ppl_act_cost_to_date_tc,0)+nvl(ppr.eqpmt_act_cost_to_date_tc,0))) ETC_COST_TC, pa_progress_utils.sum_etc_values(ptav.planned_quantity,ppr.estimated_remaining_effort,ppr.eqpmt_etc_effort,null,null,null,null,ppr.oth_etc_quantity ,(nvl(ppr.ppl_act_effort_to_date,0)+nvl(ppr.eqpmt_act_effort_to_date,0)+nvl(ppr.oth_quantity_to_date,0))) ETC_EFFORT, pa_progress_utils.GET_NEXT_PROGRESS_CYCLE(ppe.project_id, ppe.proj_element_id, ptav.resource_list_member_id, 'PA_ASSIGNMENTS'), pa_progress_utils.GET_PROG_DT_CLOSEST_TO_SYS_DT(ppe.project_id, ppe.proj_element_id, ptav.resource_list_member_id, 'PA_ASSIGNMENTS'), PA_PROGRESS_UTILS.Percent_Complete_Value(decode(ptav.resource_class_code, 'PEOPLE', NVL(ppr.ppl_act_effort_to_date,0), 'EQUIPMENT', NVL(ppr.eqpmt_act_effort_to_date,0),NVL(ppr.oth_quantity_to_date,0)) ,decode(ptav.resource_class_code, 'PEOPLE', NVL(ppr.estimated_remaining_effort , decode( sign(nvl(ptav.planned_quantity,0)-nvl(ppr.PPL_ACT_EFFORT_TO_DATE,0)), -1, 0, nvl( ptav.planned_quantity-ppr.PPL_ACT_EFFORT_TO_DATE,0))), 'EQUIPMENT', NVL(ppr.eqpmt_etc_effort, decode( sign(nvl(ptav.planned_quantity,0)-nvl(ppr.EQPMT_ACT_EFFORT_TO_DATE,0)), -1, 0, nvl( ptav.planned_quantity-ppr.EQPMT_ACT_EFFORT_TO_DATE,0))) , NVL(ppr.oth_etc_quantity, decode( sign(nvl(ptav.planned_quantity,0)-nvl(ppr.oth_quantity_to_date,0)), -1, 0, nvl( ptav.planned_quantity-ppr.oth_quantity_to_date,0)))))Percent_Complete_Effort, ppc.percent_complete_id, PA_PROGRESS_UTILS.Percent_Spent_Value( (NVL(ppr.ppl_act_effort_to_date,0)+NVL(ppr.eqpmt_act_effort_to_date,0) +NVL(ppr.oth_quantity_to_date,0)), (NVL(ptav.planned_quantity,0)) ) percent_Spent_Effort, nvl(ptav.planned_bur_cost_txn_cur,0) Planned_Cost, ptav.planned_quantity planned_effort, ptav.assignment_end_date, ptav.assignment_start_date, ppe.proj_element_id, ppe.name, ppe.element_number, to_char(null) Unit_of_Measure, ppev.wbs_number, ptav.resource_assignment_id Resource_Assignment_Id, ppc.current_flag, ppev.element_version_id, ppev.object_type, ppe.proj_element_id, ppa.project_id, ppa.name, ppa.segment1, ppc.published_flag, ppc.record_version_number, ptav.unit_of_measure_code, ptav.unit_of_measure, ptav.txn_currency_code, 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')), decode(ppe.object_type,'PA_TASKS',ptt.PROG_ENTRY_ENABLE_FLAG,'Y'), ptav.resource_alias, ptav.resource_list_member_id, ptav.resource_class_code, ptav.rbs_element_id, ptav.rate_based_flag rate_based_flag, decode(ptav.resource_class_code, 'PEOPLE', ppr.PPL_ACT_EFFORT_TO_DATE, 'EQUIPMENT', ppr.EQPMT_ACT_EFFORT_TO_DATE, ppr.oth_quantity_to_date)- nvl(pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'E',null,null,ppr.cbs_element_id),0) actual_effort_this_period, decode(ptav.resource_class_code, 'PEOPLE', ppr.PPL_ACT_COST_TO_DATE_TC, 'EQUIPMENT', ppr.EQPMT_ACT_COST_TO_DATE_TC, ppr.OTH_ACT_COST_TO_DATE_TC) -nvl(pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','B','T',ppr.cbs_element_id),0) actual_cost_this_period, pa_progress_utils.get_latest_progress_entry_date(ppa.project_id,ppr.object_id,ppr.object_type,ppr.structure_type,ppe.proj_element_id) latest_progress_date, ppev.parent_structure_version_id structure_version_id, pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','R','T',ppr.cbs_element_id) actual_rawcost ,nvl(ptav.PLANNED_RAW_COST_TXN_CUR,0) planned_rawcost ,ptav.BL_PLANNED_RAW_COST_TXN_CUR baselined_rawcost ,nvl(ppr.oth_act_rawcost_to_date_tc,0)+nvl(ppr.ppl_act_rawcost_to_date_tc,0)+nvl(ppr.eqpmt_act_rawcost_to_date_tc,0) +pa_progress_utils.sum_etc_values(ptav.PLANNED_RAW_COST_TXN_CUR,ppr.ppl_etc_rawcost_tc,ppr.eqpmt_etc_rawcost_tc,ppr.oth_etc_rawcost_tc,null,null,null,null ,(nvl(ppr.oth_act_rawcost_to_date_tc,0)+nvl(ppr.ppl_act_rawcost_to_date_tc,0)+nvl(ppr.eqpmt_act_rawcost_to_date_tc,0))) est_at_completion_rawcost ,pa_progress_utils.sum_etc_values(ptav.PLANNED_RAW_COST_TXN_CUR,ppr.ppl_etc_rawcost_tc,ppr.eqpmt_etc_rawcost_tc,ppr.oth_etc_rawcost_tc,null,null,null,null ,(nvl(ppr.oth_act_rawcost_to_date_tc,0)+nvl(ppr.ppl_act_rawcost_to_date_tc,0)+nvl(ppr.eqpmt_act_rawcost_to_date_tc,0))) etc_rawcost_tc ,PA_PROGRESS_UTILS.Percent_Complete_Value( (NVL(ppr.oth_act_rawcost_to_date_tc,0)+NVL(ppr.ppl_act_rawcost_to_date_tc,0) +NVL(ppr.eqpmt_act_rawcost_to_date_tc,0)), ( NVL(ppr.oth_etc_rawcost_tc,0)+NVL(ppr.ppl_etc_rawcost_tc,0)+NVL(ppr.eqpmt_etc_rawcost_tc,0)) ) Percent_Complete_Rawcost ,PA_PROGRESS_UTILS.Percent_Spent_Value( (NVL(ppr.OTH_ACT_COST_TO_DATE_PC,0)+NVL(ppr.ppl_act_rawcost_to_date_tc,0) +NVL(ppr.eqpmt_act_rawcost_to_date_tc,0)), (nvl(ptav.PLANNED_RAW_COST_TXN_CUR,0)) ) Percent_Spent_Rawcost ,decode(ptav.resource_class_code, 'PEOPLE', ppr.PPL_ACT_rawcost_TO_DATE_TC, 'EQUIPMENT', ppr.EQPMT_ACT_rawcost_TO_DATE_TC,ppr.OTH_ACT_rawcost_TO_DATE_TC) - nvl(pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','R','T',ppr.cbs_element_id),0) actual_rawcost_this_period ,ptav.unplanned_flag unplanned_assignments_flag ,nvl(ptav.PLANNED_BUR_COST_PROJ_CUR,0) planned_cost_bur_pc ,pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','B','P',ppr.cbs_element_id) actual_cost_bur_pc ,decode(ptav.resource_class_code, 'PEOPLE', ppr.PPL_ACT_COST_TO_DATE_PC, 'EQUIPMENT', ppr.EQPMT_ACT_COST_TO_DATE_PC, ppr.OTH_ACT_COST_TO_DATE_PC) - nvl(pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','B','P',ppr.cbs_element_id),0) actual_cost_this_bur_pc ,pa_progress_utils.sum_etc_values(ptav.PLANNED_BUR_COST_PROJ_CUR,ppr.ppl_etc_cost_pc,ppr.eqpmt_etc_cost_pc,ppr.oth_etc_cost_pc,null, null,null,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))) ETC_cost_bur_pc ,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) +pa_progress_utils.sum_etc_values(ptav.PLANNED_BUR_COST_PROJ_CUR,ppr.ppl_etc_cost_pc,ppr.eqpmt_etc_cost_pc,ppr.oth_etc_cost_pc, null,null,null,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))) Est_at_Completion_cost_bur_pc ,ppr.progress_rollup_id ,ppa.project_currency_code ,ppr.current_flag ppr_current_flag ,PA_PROGRESS_UTILS.Percent_Complete_Value(decode(ptav.resource_class_code, 'PEOPLE', NVL(ppr.PPL_ACT_COST_TO_DATE_PC,0), 'EQUIPMENT', NVL(ppr.EQPMT_ACT_COST_TO_DATE_PC,0),NVL(ppr.OTH_ACT_COST_TO_DATE_PC,0)) ,decode(ptav.resource_class_code, 'PEOPLE', NVL(ppr.PPL_ETC_COST_PC , decode( sign(nvl(ptav.planned_bur_cost_proj_cur,0)-nvl(ppr.PPL_ACT_COST_TO_DATE_PC,0)), -1, 0, nvl( ptav.planned_bur_cost_proj_cur-ppr.PPL_ACT_COST_TO_DATE_PC,0))), 'EQUIPMENT', NVL(ppr.EQPMT_ETC_COST_PC, decode( sign(nvl(ptav.planned_bur_cost_proj_cur,0)-nvl(ppr.EQPMT_ACT_COST_TO_DATE_PC,0)), -1, 0, nvl( ptav.planned_bur_cost_proj_cur-ppr.EQPMT_ACT_COST_TO_DATE_PC,0))) , NVL(ppr.OTH_ETC_COST_PC, decode( sign(nvl(ptav.planned_bur_cost_proj_cur,0)-nvl(ppr.OTH_ACT_COST_TO_DATE_PC,0)), -1, 0, nvl( ptav.planned_bur_cost_proj_cur-ppr.OTH_ACT_COST_TO_DATE_PC,0)))))Percent_Complete_bur_cost_pc ,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(ptav.planned_bur_cost_proj_cur,0)) ) Percent_Spent_bur_cost_pc, ppc.progress_comment, ppc.description , PA_PROGRESS_UTILS.Percent_Spent_Value( (NVL(ppr.oth_act_rawcost_to_date_tc,0)+NVL(ppr.ppl_act_rawcost_to_date_tc,0) +NVL(ppr.eqpmt_act_rawcost_to_date_tc,0)),(nvl(ptav.PLANNED_RAW_COST_TXN_CUR,0)) ) pct_spent_rawcost_cbs , PA_PROGRESS_UTILS.Percent_Complete_Value(( NVL(ppr.PPL_ACT_COST_TO_DATE_PC,0) + NVL(ppr.EQPMT_ACT_COST_TO_DATE_PC,0) + NVL(ppr.OTH_ACT_COST_TO_DATE_PC,0)) , Decode(ptav.resource_class_code, 'PEOPLE',NVL(ppr.PPL_ETC_COST_PC ,decode( sign(nvl(ptav.planned_bur_cost_proj_cur,0)-nvl(ppr.PPL_ACT_COST_TO_DATE_PC,0)),-1,0,nvl( ptav.planned_bur_cost_proj_cur-ppr.PPL_ACT_COST_TO_DATE_PC,0))) ,'EQUIPMENT',NVL(ppr.EQPMT_ETC_COST_PC,decode( sign(nvl(ptav.planned_bur_cost_proj_cur,0)-nvl(ppr.EQPMT_ACT_COST_TO_DATE_PC,0)),-1,0,nvl( ptav.planned_bur_cost_proj_cur-ppr.EQPMT_ACT_COST_TO_DATE_PC,0))) ,NVL(ppr.OTH_ETC_COST_PC,decode( sign(nvl(ptav.planned_bur_cost_proj_cur,0)-nvl(ppr.OTH_ACT_COST_TO_DATE_PC,0)),-1,0,nvl( ptav.planned_bur_cost_proj_cur-ppr.OTH_ACT_COST_TO_DATE_PC,0))) ) )Percent_Complete_bur_cost_cbs , (Nvl(ppr.PPL_ACT_rawcost_TO_DATE_TC,0)+ Nvl(ppr.EQPMT_ACT_rawcost_TO_DATE_TC,0) + Nvl(ppr.OTH_ACT_rawcost_TO_DATE_TC,0)) - nvl(pa_progress_utils.get_act_for_prev_asofdate(ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id,ppr.proj_element_id,'C','R','T',ppr.cbs_element_id),0) actual_rawcost_this_period_cbs From pa_projects_all ppa, pa_proj_elements ppe, pa_proj_element_versions ppev, pa_proj_elem_ver_structure ppvs, pa_percent_completes ppc, pa_progress_rollup ppr, pa_proj_progress_attr pppa, pa_task_types ptt, pa_task_asgmts_v ptav Where ppe.project_id = ppvs.project_id and ppvs.latest_eff_published_flag = 'Y' and ppe.link_task_flag = 'N' and ppe.object_Type = 'PA_TASKS' and ppe.proj_element_id = ppev.proj_element_id and ppev.parent_structure_version_id = ppvs.element_version_id and ppa.project_id = ppvs.project_id and ppe.type_id = ptt.task_type_id(+) and ptt.object_type(+) = 'PA_TASKS' and ptav.project_id = ppe.project_id and ptav.task_id = ppe.proj_element_id and ptav.task_version_id = ppev.element_version_id and ptav.project_id = ppr.project_id(+) and ptav.resource_list_member_id = ppr.object_id(+) and ptav.task_id = ppr.proj_element_id(+) and ppc.project_id(+) = ppr.project_id and ppc.object_id(+) = ppr.object_id and ppc.object_type(+) = 'PA_ASSIGNMENTS' and ppc.task_id(+) = ppr.proj_element_id and 'PA_ASSIGNMENTS' = ppr.object_type(+) and ppr.structure_version_id(+) IS NULL and ppc.structure_type(+) = 'WORKPLAN' and ppr.structure_type(+) = 'WORKPLAN' and ppr.structure_version_id(+) IS NULL and ppr.percent_complete_id = ppc.percent_complete_id(+) and ppr.progress_rollup_id(+) = pa_progress_utils.get_w_pub_prupid_asofdate(ptav.project_id,ptav.resource_list_member_id,'PA_ASSIGNMENTS',ptav.task_id,pa_progress_utils.get_prog_asofdate(),'Y',ptav.cbs_element_id) and ppvs.project_id = pppa.project_id(+) and ppvs.proj_element_id = pppa.object_id(+) and pppa.structure_type(+) = 'WORKPLAN' and ptav.ta_display_flag = 'Y' AND PTAV.CBS_ELEMENT_ID = PPR.CBS_ELEMENT_ID(+) and PA_ALTERNATE_TASK_PVT.Is_Cbs_Enabled(ppa.project_id) = 'Y'

View Text - HTML Formatted

SELECT PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'B'
, 'T') ACTUAL_COST
, PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'E'
, NULL
, NULL) ACTUAL_EFFORT
, PPR.ACTUAL_FINISH_DATE
, PPR.ACTUAL_START_DATE
, PPR.AS_OF_DATE AS_OF_DATE
, PTAV.BASELINED_ASGMT_END_DATE
, PTAV.BASELINED_ASGMT_START_DATE
, NVL(PPR.OTH_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_TC
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_BUR_COST_TXN_CUR
, PPR.PPL_ETC_COST_TC
, PPR.EQPMT_ETC_COST_TC
, PPR.OTH_ETC_COST_TC
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_TC
, 0))) EST_AT_COMPLETION_COST
, NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.OTH_QUANTITY_TO_DATE
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_QUANTITY
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, NULL
, NULL
, NULL
, PPR.OTH_ETC_QUANTITY
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))) EST_AT_COMPLETION_EFFORT
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_QUANTITY
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, NULL
, NULL
, NULL
, PPR.OTH_ETC_QUANTITY
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))) ESTIMATED_REMAINING_EFFORT
, PPR.ESTIMATED_FINISH_DATE
, PPR.ESTIMATED_START_DATE
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_BUR_COST_TXN_CUR
, PPR.PPL_ETC_COST_TC
, PPR.EQPMT_ETC_COST_TC
, PPR.OTH_ETC_COST_TC
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_TC
, 0))) ETC_COST_TC
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_QUANTITY
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, NULL
, NULL
, NULL
, PPR.OTH_ETC_QUANTITY
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))) ETC_EFFORT
, PA_PROGRESS_UTILS.GET_NEXT_PROGRESS_CYCLE(PPE.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, PTAV.RESOURCE_LIST_MEMBER_ID
, 'PA_ASSIGNMENTS')
, PA_PROGRESS_UTILS.GET_PROG_DT_CLOSEST_TO_SYS_DT(PPE.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, PTAV.RESOURCE_LIST_MEMBER_ID
, 'PA_ASSIGNMENTS')
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE(DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)
, 'EQUIPMENT'
, NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)
, NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, NVL(PPR.ESTIMATED_REMAINING_EFFORT
, DECODE( SIGN(NVL(PTAV.PLANNED_QUANTITY
, 0)-NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_QUANTITY-PPR.PPL_ACT_EFFORT_TO_DATE
, 0)))
, 'EQUIPMENT'
, NVL(PPR.EQPMT_ETC_EFFORT
, DECODE( SIGN(NVL(PTAV.PLANNED_QUANTITY
, 0)-NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_QUANTITY-PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)))
, NVL(PPR.OTH_ETC_QUANTITY
, DECODE( SIGN(NVL(PTAV.PLANNED_QUANTITY
, 0)-NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_QUANTITY-PPR.OTH_QUANTITY_TO_DATE
, 0)))))PERCENT_COMPLETE_EFFORT
, PPC.PERCENT_COMPLETE_ID
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))
, (NVL(PTAV.PLANNED_QUANTITY
, 0)) ) PERCENT_SPENT_EFFORT
, NVL(PTAV.PLANNED_BUR_COST_TXN_CUR
, 0) PLANNED_COST
, PTAV.PLANNED_QUANTITY PLANNED_EFFORT
, PTAV.ASSIGNMENT_END_DATE
, PTAV.ASSIGNMENT_START_DATE
, PPE.PROJ_ELEMENT_ID
, PPE.NAME
, PPE.ELEMENT_NUMBER
, TO_CHAR(NULL) UNIT_OF_MEASURE
, PPEV.WBS_NUMBER
, PTAV.RESOURCE_ASSIGNMENT_ID RESOURCE_ASSIGNMENT_ID
, PPC.CURRENT_FLAG
, PPEV.ELEMENT_VERSION_ID
, PPEV.OBJECT_TYPE
, PPE.PROJ_ELEMENT_ID
, PPA.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PPC.PUBLISHED_FLAG
, PPC.RECORD_VERSION_NUMBER
, PTAV.UNIT_OF_MEASURE_CODE
, PTAV.UNIT_OF_MEASURE
, PTAV.TXN_CURRENCY_CODE
, 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'))
, DECODE(PPE.OBJECT_TYPE
, 'PA_TASKS'
, PTT.PROG_ENTRY_ENABLE_FLAG
, 'Y')
, PTAV.RESOURCE_ALIAS
, PTAV.RESOURCE_LIST_MEMBER_ID
, PTAV.RESOURCE_CLASS_CODE
, PTAV.RBS_ELEMENT_ID
, PTAV.RATE_BASED_FLAG RATE_BASED_FLAG
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_EFFORT_TO_DATE
, 'EQUIPMENT'
, PPR.EQPMT_ACT_EFFORT_TO_DATE
, PPR.OTH_QUANTITY_TO_DATE)- NVL(PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'E'
, NULL
, NULL)
, 0) ACTUAL_EFFORT_THIS_PERIOD
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_COST_TO_DATE_TC
, 'EQUIPMENT'
, PPR.EQPMT_ACT_COST_TO_DATE_TC
, PPR.OTH_ACT_COST_TO_DATE_TC) -NVL(PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'B'
, 'T')
, 0) ACTUAL_COST_THIS_PERIOD
, PA_PROGRESS_UTILS.GET_LATEST_PROGRESS_ENTRY_DATE(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_TYPE
, PPR.STRUCTURE_TYPE
, PPE.PROJ_ELEMENT_ID) LATEST_PROGRESS_DATE
, PPEV.PARENT_STRUCTURE_VERSION_ID STRUCTURE_VERSION_ID
, PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'R'
, 'T') ACTUAL_RAWCOST
, NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0) PLANNED_RAWCOST
, PTAV.BL_PLANNED_RAW_COST_TXN_CUR BASELINED_RAWCOST
, NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_RAW_COST_TXN_CUR
, PPR.PPL_ETC_RAWCOST_TC
, PPR.EQPMT_ETC_RAWCOST_TC
, PPR.OTH_ETC_RAWCOST_TC
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0))) EST_AT_COMPLETION_RAWCOST
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_RAW_COST_TXN_CUR
, PPR.PPL_ETC_RAWCOST_TC
, PPR.EQPMT_ETC_RAWCOST_TC
, PPR.OTH_ETC_RAWCOST_TC
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0))) ETC_RAWCOST_TC
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE( (NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0) +NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0))
, ( NVL(PPR.OTH_ETC_RAWCOST_TC
, 0)+NVL(PPR.PPL_ETC_RAWCOST_TC
, 0)+NVL(PPR.EQPMT_ETC_RAWCOST_TC
, 0)) ) PERCENT_COMPLETE_RAWCOST
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0) +NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0))
, (NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0)) ) PERCENT_SPENT_RAWCOST
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 'EQUIPMENT'
, PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, PPR.OTH_ACT_RAWCOST_TO_DATE_TC) - NVL(PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'R'
, 'T')
, 0) ACTUAL_RAWCOST_THIS_PERIOD
, PTAV.UNPLANNED_FLAG UNPLANNED_ASSIGNMENTS_FLAG
, NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0) PLANNED_COST_BUR_PC
, PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'B'
, 'P') ACTUAL_COST_BUR_PC
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_COST_TO_DATE_PC
, 'EQUIPMENT'
, PPR.EQPMT_ACT_COST_TO_DATE_PC
, PPR.OTH_ACT_COST_TO_DATE_PC) - NVL(PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'B'
, 'P')
, 0) ACTUAL_COST_THIS_BUR_PC
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_BUR_COST_PROJ_CUR
, PPR.PPL_ETC_COST_PC
, PPR.EQPMT_ETC_COST_PC
, PPR.OTH_ETC_COST_PC
, NULL
, NULL
, NULL
, 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))) ETC_COST_BUR_PC
, 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) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_BUR_COST_PROJ_CUR
, PPR.PPL_ETC_COST_PC
, PPR.EQPMT_ETC_COST_PC
, PPR.OTH_ETC_COST_PC
, NULL
, NULL
, NULL
, 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))) EST_AT_COMPLETION_COST_BUR_PC
, PPR.PROGRESS_ROLLUP_ID
, PPA.PROJECT_CURRENCY_CODE
, PPR.CURRENT_FLAG PPR_CURRENT_FLAG
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE(DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)
, 'EQUIPMENT'
, NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)
, NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0))
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, NVL(PPR.PPL_ETC_COST_PC
, DECODE( SIGN(NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)-NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_BUR_COST_PROJ_CUR-PPR.PPL_ACT_COST_TO_DATE_PC
, 0)))
, 'EQUIPMENT'
, NVL(PPR.EQPMT_ETC_COST_PC
, DECODE( SIGN(NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)-NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_BUR_COST_PROJ_CUR-PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)))
, NVL(PPR.OTH_ETC_COST_PC
, DECODE( SIGN(NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)-NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_BUR_COST_PROJ_CUR-PPR.OTH_ACT_COST_TO_DATE_PC
, 0)))))PERCENT_COMPLETE_BUR_COST_PC
, 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(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)) ) PERCENT_SPENT_BUR_COST_PC
, PPC.PROGRESS_COMMENT
, PPC.DESCRIPTION
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( (NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0) +NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0))
, (NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0)) ) PCT_SPENT_RAWCOST_CBS
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE(( NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0) + NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) + NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0))
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, NVL(PPR.PPL_ETC_COST_PC
, DECODE( SIGN(NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)-NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_BUR_COST_PROJ_CUR-PPR.PPL_ACT_COST_TO_DATE_PC
, 0)))
, 'EQUIPMENT'
, NVL(PPR.EQPMT_ETC_COST_PC
, DECODE( SIGN(NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)-NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_BUR_COST_PROJ_CUR-PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)))
, NVL(PPR.OTH_ETC_COST_PC
, DECODE( SIGN(NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)-NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_BUR_COST_PROJ_CUR-PPR.OTH_ACT_COST_TO_DATE_PC
, 0))) ) )PERCENT_COMPLETE_BUR_COST_CBS
, (NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0)+ NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0) + NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)) - NVL(PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'R'
, 'T')
, 0) ACTUAL_RAWCOST_THIS_PERIOD_CBS
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_STRUCTURE PPVS
, PA_PERCENT_COMPLETES PPC
, PA_PROGRESS_ROLLUP PPR
, PA_PROJ_PROGRESS_ATTR PPPA
, PA_TASK_TYPES PTT
, PA_TASK_ASGMTS_V PTAV
WHERE PPE.PROJECT_ID = PPVS.PROJECT_ID
AND PPVS.LATEST_EFF_PUBLISHED_FLAG = 'Y'
AND PPE.LINK_TASK_FLAG = 'N'
AND PPE.OBJECT_TYPE = 'PA_TASKS'
AND PPE.PROJ_ELEMENT_ID = PPEV.PROJ_ELEMENT_ID
AND PPEV.PARENT_STRUCTURE_VERSION_ID = PPVS.ELEMENT_VERSION_ID
AND PPA.PROJECT_ID = PPVS.PROJECT_ID
AND PPE.TYPE_ID = PTT.TASK_TYPE_ID(+)
AND PTT.OBJECT_TYPE(+) = 'PA_TASKS'
AND PTAV.PROJECT_ID = PPE.PROJECT_ID
AND PTAV.TASK_ID = PPE.PROJ_ELEMENT_ID
AND PTAV.TASK_VERSION_ID = PPEV.ELEMENT_VERSION_ID
AND PTAV.PROJECT_ID = PPR.PROJECT_ID(+)
AND PTAV.RESOURCE_LIST_MEMBER_ID = PPR.OBJECT_ID(+)
AND PTAV.TASK_ID = PPR.PROJ_ELEMENT_ID(+)
AND PPC.PROJECT_ID(+) = PPR.PROJECT_ID
AND PPC.OBJECT_ID(+) = PPR.OBJECT_ID
AND PPC.OBJECT_TYPE(+) = 'PA_ASSIGNMENTS'
AND PPC.TASK_ID(+) = PPR.PROJ_ELEMENT_ID
AND 'PA_ASSIGNMENTS' = PPR.OBJECT_TYPE(+)
AND PPR.STRUCTURE_VERSION_ID(+) IS NULL
AND PPC.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PPR.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PPR.STRUCTURE_VERSION_ID(+) IS NULL
AND PPR.PERCENT_COMPLETE_ID = PPC.PERCENT_COMPLETE_ID(+)
AND PPR.PROGRESS_ROLLUP_ID(+) = PA_PROGRESS_UTILS.GET_W_PUB_PRUPID_ASOFDATE(PTAV.PROJECT_ID
, PTAV.RESOURCE_LIST_MEMBER_ID
, 'PA_ASSIGNMENTS'
, PTAV.TASK_ID
, PA_PROGRESS_UTILS.GET_PROG_ASOFDATE()
, 'Y')
AND PPVS.PROJECT_ID = PPPA.PROJECT_ID(+)
AND PPVS.PROJ_ELEMENT_ID = PPPA.OBJECT_ID(+)
AND PPPA.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PTAV.TA_DISPLAY_FLAG = 'Y'
AND PA_ALTERNATE_TASK_PVT.IS_CBS_ENABLED(PPA.PROJECT_ID) = 'N' UNION SELECT PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'B'
, 'T'
, PPR.CBS_ELEMENT_ID) ACTUAL_COST
, PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'E'
, NULL
, NULL
, PPR.CBS_ELEMENT_ID) ACTUAL_EFFORT
, PPR.ACTUAL_FINISH_DATE
, PPR.ACTUAL_START_DATE
, PPR.AS_OF_DATE AS_OF_DATE
, PTAV.BASELINED_ASGMT_END_DATE
, PTAV.BASELINED_ASGMT_START_DATE
, NVL(PPR.OTH_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_TC
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_BUR_COST_TXN_CUR
, PPR.PPL_ETC_COST_TC
, PPR.EQPMT_ETC_COST_TC
, PPR.OTH_ETC_COST_TC
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_TC
, 0))) EST_AT_COMPLETION_COST
, NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.OTH_QUANTITY_TO_DATE
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_QUANTITY
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, NULL
, NULL
, NULL
, PPR.OTH_ETC_QUANTITY
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))) EST_AT_COMPLETION_EFFORT
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_QUANTITY
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, NULL
, NULL
, NULL
, PPR.OTH_ETC_QUANTITY
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))) ESTIMATED_REMAINING_EFFORT
, PPR.ESTIMATED_FINISH_DATE
, PPR.ESTIMATED_START_DATE
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_BUR_COST_TXN_CUR
, PPR.PPL_ETC_COST_TC
, PPR.EQPMT_ETC_COST_TC
, PPR.OTH_ETC_COST_TC
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_TC
, 0))) ETC_COST_TC
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_QUANTITY
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, NULL
, NULL
, NULL
, PPR.OTH_ETC_QUANTITY
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))) ETC_EFFORT
, PA_PROGRESS_UTILS.GET_NEXT_PROGRESS_CYCLE(PPE.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, PTAV.RESOURCE_LIST_MEMBER_ID
, 'PA_ASSIGNMENTS')
, PA_PROGRESS_UTILS.GET_PROG_DT_CLOSEST_TO_SYS_DT(PPE.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, PTAV.RESOURCE_LIST_MEMBER_ID
, 'PA_ASSIGNMENTS')
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE(DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)
, 'EQUIPMENT'
, NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)
, NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, NVL(PPR.ESTIMATED_REMAINING_EFFORT
, DECODE( SIGN(NVL(PTAV.PLANNED_QUANTITY
, 0)-NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_QUANTITY-PPR.PPL_ACT_EFFORT_TO_DATE
, 0)))
, 'EQUIPMENT'
, NVL(PPR.EQPMT_ETC_EFFORT
, DECODE( SIGN(NVL(PTAV.PLANNED_QUANTITY
, 0)-NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_QUANTITY-PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)))
, NVL(PPR.OTH_ETC_QUANTITY
, DECODE( SIGN(NVL(PTAV.PLANNED_QUANTITY
, 0)-NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_QUANTITY-PPR.OTH_QUANTITY_TO_DATE
, 0)))))PERCENT_COMPLETE_EFFORT
, PPC.PERCENT_COMPLETE_ID
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))
, (NVL(PTAV.PLANNED_QUANTITY
, 0)) ) PERCENT_SPENT_EFFORT
, NVL(PTAV.PLANNED_BUR_COST_TXN_CUR
, 0) PLANNED_COST
, PTAV.PLANNED_QUANTITY PLANNED_EFFORT
, PTAV.ASSIGNMENT_END_DATE
, PTAV.ASSIGNMENT_START_DATE
, PPE.PROJ_ELEMENT_ID
, PPE.NAME
, PPE.ELEMENT_NUMBER
, TO_CHAR(NULL) UNIT_OF_MEASURE
, PPEV.WBS_NUMBER
, PTAV.RESOURCE_ASSIGNMENT_ID RESOURCE_ASSIGNMENT_ID
, PPC.CURRENT_FLAG
, PPEV.ELEMENT_VERSION_ID
, PPEV.OBJECT_TYPE
, PPE.PROJ_ELEMENT_ID
, PPA.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PPC.PUBLISHED_FLAG
, PPC.RECORD_VERSION_NUMBER
, PTAV.UNIT_OF_MEASURE_CODE
, PTAV.UNIT_OF_MEASURE
, PTAV.TXN_CURRENCY_CODE
, 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'))
, DECODE(PPE.OBJECT_TYPE
, 'PA_TASKS'
, PTT.PROG_ENTRY_ENABLE_FLAG
, 'Y')
, PTAV.RESOURCE_ALIAS
, PTAV.RESOURCE_LIST_MEMBER_ID
, PTAV.RESOURCE_CLASS_CODE
, PTAV.RBS_ELEMENT_ID
, PTAV.RATE_BASED_FLAG RATE_BASED_FLAG
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_EFFORT_TO_DATE
, 'EQUIPMENT'
, PPR.EQPMT_ACT_EFFORT_TO_DATE
, PPR.OTH_QUANTITY_TO_DATE)- NVL(PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'E'
, NULL
, NULL
, PPR.CBS_ELEMENT_ID)
, 0) ACTUAL_EFFORT_THIS_PERIOD
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_COST_TO_DATE_TC
, 'EQUIPMENT'
, PPR.EQPMT_ACT_COST_TO_DATE_TC
, PPR.OTH_ACT_COST_TO_DATE_TC) -NVL(PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'B'
, 'T'
, PPR.CBS_ELEMENT_ID)
, 0) ACTUAL_COST_THIS_PERIOD
, PA_PROGRESS_UTILS.GET_LATEST_PROGRESS_ENTRY_DATE(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_TYPE
, PPR.STRUCTURE_TYPE
, PPE.PROJ_ELEMENT_ID) LATEST_PROGRESS_DATE
, PPEV.PARENT_STRUCTURE_VERSION_ID STRUCTURE_VERSION_ID
, PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'R'
, 'T'
, PPR.CBS_ELEMENT_ID) ACTUAL_RAWCOST
, NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0) PLANNED_RAWCOST
, PTAV.BL_PLANNED_RAW_COST_TXN_CUR BASELINED_RAWCOST
, NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_RAW_COST_TXN_CUR
, PPR.PPL_ETC_RAWCOST_TC
, PPR.EQPMT_ETC_RAWCOST_TC
, PPR.OTH_ETC_RAWCOST_TC
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0))) EST_AT_COMPLETION_RAWCOST
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_RAW_COST_TXN_CUR
, PPR.PPL_ETC_RAWCOST_TC
, PPR.EQPMT_ETC_RAWCOST_TC
, PPR.OTH_ETC_RAWCOST_TC
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0))) ETC_RAWCOST_TC
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE( (NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0) +NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0))
, ( NVL(PPR.OTH_ETC_RAWCOST_TC
, 0)+NVL(PPR.PPL_ETC_RAWCOST_TC
, 0)+NVL(PPR.EQPMT_ETC_RAWCOST_TC
, 0)) ) PERCENT_COMPLETE_RAWCOST
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( (NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0) +NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0))
, (NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0)) ) PERCENT_SPENT_RAWCOST
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 'EQUIPMENT'
, PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, PPR.OTH_ACT_RAWCOST_TO_DATE_TC) - NVL(PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'R'
, 'T'
, PPR.CBS_ELEMENT_ID)
, 0) ACTUAL_RAWCOST_THIS_PERIOD
, PTAV.UNPLANNED_FLAG UNPLANNED_ASSIGNMENTS_FLAG
, NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0) PLANNED_COST_BUR_PC
, PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'B'
, 'P'
, PPR.CBS_ELEMENT_ID) ACTUAL_COST_BUR_PC
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_COST_TO_DATE_PC
, 'EQUIPMENT'
, PPR.EQPMT_ACT_COST_TO_DATE_PC
, PPR.OTH_ACT_COST_TO_DATE_PC) - NVL(PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'B'
, 'P'
, PPR.CBS_ELEMENT_ID)
, 0) ACTUAL_COST_THIS_BUR_PC
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_BUR_COST_PROJ_CUR
, PPR.PPL_ETC_COST_PC
, PPR.EQPMT_ETC_COST_PC
, PPR.OTH_ETC_COST_PC
, NULL
, NULL
, NULL
, 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))) ETC_COST_BUR_PC
, 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) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_BUR_COST_PROJ_CUR
, PPR.PPL_ETC_COST_PC
, PPR.EQPMT_ETC_COST_PC
, PPR.OTH_ETC_COST_PC
, NULL
, NULL
, NULL
, 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))) EST_AT_COMPLETION_COST_BUR_PC
, PPR.PROGRESS_ROLLUP_ID
, PPA.PROJECT_CURRENCY_CODE
, PPR.CURRENT_FLAG PPR_CURRENT_FLAG
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE(DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0)
, 'EQUIPMENT'
, NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)
, NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0))
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, NVL(PPR.PPL_ETC_COST_PC
, DECODE( SIGN(NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)-NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_BUR_COST_PROJ_CUR-PPR.PPL_ACT_COST_TO_DATE_PC
, 0)))
, 'EQUIPMENT'
, NVL(PPR.EQPMT_ETC_COST_PC
, DECODE( SIGN(NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)-NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_BUR_COST_PROJ_CUR-PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)))
, NVL(PPR.OTH_ETC_COST_PC
, DECODE( SIGN(NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)-NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_BUR_COST_PROJ_CUR-PPR.OTH_ACT_COST_TO_DATE_PC
, 0)))))PERCENT_COMPLETE_BUR_COST_PC
, 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(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)) ) PERCENT_SPENT_BUR_COST_PC
, PPC.PROGRESS_COMMENT
, PPC.DESCRIPTION
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( (NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0) +NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0))
, (NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0)) ) PCT_SPENT_RAWCOST_CBS
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE(( NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0) + NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0) + NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0))
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, NVL(PPR.PPL_ETC_COST_PC
, DECODE( SIGN(NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)-NVL(PPR.PPL_ACT_COST_TO_DATE_PC
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_BUR_COST_PROJ_CUR-PPR.PPL_ACT_COST_TO_DATE_PC
, 0)))
, 'EQUIPMENT'
, NVL(PPR.EQPMT_ETC_COST_PC
, DECODE( SIGN(NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)-NVL(PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_BUR_COST_PROJ_CUR-PPR.EQPMT_ACT_COST_TO_DATE_PC
, 0)))
, NVL(PPR.OTH_ETC_COST_PC
, DECODE( SIGN(NVL(PTAV.PLANNED_BUR_COST_PROJ_CUR
, 0)-NVL(PPR.OTH_ACT_COST_TO_DATE_PC
, 0))
, -1
, 0
, NVL( PTAV.PLANNED_BUR_COST_PROJ_CUR-PPR.OTH_ACT_COST_TO_DATE_PC
, 0))) ) )PERCENT_COMPLETE_BUR_COST_CBS
, (NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0)+ NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0) + NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)) - NVL(PA_PROGRESS_UTILS.GET_ACT_FOR_PREV_ASOFDATE(PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID
, 'C'
, 'R'
, 'T'
, PPR.CBS_ELEMENT_ID)
, 0) ACTUAL_RAWCOST_THIS_PERIOD_CBS
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_STRUCTURE PPVS
, PA_PERCENT_COMPLETES PPC
, PA_PROGRESS_ROLLUP PPR
, PA_PROJ_PROGRESS_ATTR PPPA
, PA_TASK_TYPES PTT
, PA_TASK_ASGMTS_V PTAV
WHERE PPE.PROJECT_ID = PPVS.PROJECT_ID
AND PPVS.LATEST_EFF_PUBLISHED_FLAG = 'Y'
AND PPE.LINK_TASK_FLAG = 'N'
AND PPE.OBJECT_TYPE = 'PA_TASKS'
AND PPE.PROJ_ELEMENT_ID = PPEV.PROJ_ELEMENT_ID
AND PPEV.PARENT_STRUCTURE_VERSION_ID = PPVS.ELEMENT_VERSION_ID
AND PPA.PROJECT_ID = PPVS.PROJECT_ID
AND PPE.TYPE_ID = PTT.TASK_TYPE_ID(+)
AND PTT.OBJECT_TYPE(+) = 'PA_TASKS'
AND PTAV.PROJECT_ID = PPE.PROJECT_ID
AND PTAV.TASK_ID = PPE.PROJ_ELEMENT_ID
AND PTAV.TASK_VERSION_ID = PPEV.ELEMENT_VERSION_ID
AND PTAV.PROJECT_ID = PPR.PROJECT_ID(+)
AND PTAV.RESOURCE_LIST_MEMBER_ID = PPR.OBJECT_ID(+)
AND PTAV.TASK_ID = PPR.PROJ_ELEMENT_ID(+)
AND PPC.PROJECT_ID(+) = PPR.PROJECT_ID
AND PPC.OBJECT_ID(+) = PPR.OBJECT_ID
AND PPC.OBJECT_TYPE(+) = 'PA_ASSIGNMENTS'
AND PPC.TASK_ID(+) = PPR.PROJ_ELEMENT_ID
AND 'PA_ASSIGNMENTS' = PPR.OBJECT_TYPE(+)
AND PPR.STRUCTURE_VERSION_ID(+) IS NULL
AND PPC.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PPR.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PPR.STRUCTURE_VERSION_ID(+) IS NULL
AND PPR.PERCENT_COMPLETE_ID = PPC.PERCENT_COMPLETE_ID(+)
AND PPR.PROGRESS_ROLLUP_ID(+) = PA_PROGRESS_UTILS.GET_W_PUB_PRUPID_ASOFDATE(PTAV.PROJECT_ID
, PTAV.RESOURCE_LIST_MEMBER_ID
, 'PA_ASSIGNMENTS'
, PTAV.TASK_ID
, PA_PROGRESS_UTILS.GET_PROG_ASOFDATE()
, 'Y'
, PTAV.CBS_ELEMENT_ID)
AND PPVS.PROJECT_ID = PPPA.PROJECT_ID(+)
AND PPVS.PROJ_ELEMENT_ID = PPPA.OBJECT_ID(+)
AND PPPA.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PTAV.TA_DISPLAY_FLAG = 'Y'
AND PTAV.CBS_ELEMENT_ID = PPR.CBS_ELEMENT_ID(+)
AND PA_ALTERNATE_TASK_PVT.IS_CBS_ENABLED(PPA.PROJECT_ID) = 'Y'