VIEW: APPS.PA_TASK_ASSIGNMENTS_AMG_V

Source

View Text - Preformatted

SELECT ppr.actual_finish_date - ptav.assignment_end_date act_sch_finish_var, ppr.actual_start_date - ptav.assignment_start_date act_sch_start_var, (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_cost_this_period (ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id ,ppr.proj_element_id),0)) Actual_Cost, (decode(ptav.resource_class_code, 'PEOPLE', ppr.PPL_ACT_EFFORT_TO_DATE, 'EQUIPMENT', ppr.EQPMT_ACT_EFFORT_TO_DATE) - nvl(pa_progress_utils.get_act_effort_this_period (ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id ,ppr.proj_element_id),0)) Actual_Effort, ppr.actual_finish_date, ppr.actual_start_date, ppr.cumulative_work_quantity Actual_Work_Quantity, ppr.as_of_date as_of_date, ptav.bl_planned_bur_cost_txn_cur Baseline_Cost, ptav.baselined_asgmt_end_date, ptav.baselined_asgmt_start_date, ptav.baselined_planned_qty Baselined_Effort, (PA_PROGRESS_UTILS.get_next_progress_cycle(ppa.project_id,ppe.proj_element_id, ptav.resource_list_member_id, 'PA_ASSIGNMENTS')-trunc(sysdate)) Days_Until_Progress_Due, trunc(ptav.assignment_end_date) - trunc(sysdate), (trunc(ptav.assignment_start_date)-trunc(sysdate)) Days_Until_Start, ppr.earned_value Earned_Value, (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) +pa_progress_utils.sum_etc_values(ptav.planned_quantity ,ppr.estimated_remaining_effort ,ppr.eqpmt_etc_effort ,null ,null ,null ,null ,null ,(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, (ppr.estimated_finish_date - ptav.baselined_asgmt_end_date) Est_bas_Finish_Var, (ppr.estimated_start_date - ptav.baselined_asgmt_start_date) Est_Bas_Start_Var, (NVL(ppr.EQPMT_ETC_EFFORT,0) + NVL(ESTIMATED_REMAINING_EFFORT,0)) estimated_remaining_effort, ppr.estimated_finish_date - ptav.assignment_end_date est_sch_finish_var, 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 ,null ,(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, (NVL(ppvsch.wq_planned_quantity,0) - NVL(CUMULATIVE_WORK_QUANTITY,0) ) ETC_Work_Quantity, 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( (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)), ( NVL(ppr.oth_etc_cost_tc,0)+NVL(ppr.ppl_etc_cost_tc,0)+NVL(ppr.eqpmt_etc_cost_tc,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.estimated_remaining_effort,0)+NVL(ppr.eqpmt_etc_effort,0)) ) Percent_Complete_Effort, ppc.percent_complete_id, PA_PROGRESS_UTILS.Percent_Spent_Value( (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)), (nvl(ptav.planned_bur_cost_txn_cur,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(ptav.planned_quantity,0)) ) Percent_Spent_Effort, nvl(ptav.planned_bur_cost_txn_cur,0) Planned_Cost, (nvl(ptav.planned_bur_cost_txn_cur,0)/decode(nvl(ppr.cumulative_work_quantity,0),0,1,nvl(ppr.cumulative_work_quantity,0))) Planned_Cost_Per_Unit, nvl(ptav.planned_quantity,0), ppvsch.wq_planned_quantity, PA_RELATIONSHIP_UTILS.display_predecessors(ppe.proj_element_id) Predecessors, ppe.priority_code, ptav.assignment_end_date - ptav.baselined_asgmt_end_date sch_base_finish_var, ptav.assignment_start_date -ptav.baselined_asgmt_start_date sch_base_start_var, round((nvl(ppr.earned_value,0)/decode(nvl(pa_progress_utils.get_bcws(ppa.project_id,ppr.object_id, ppe.proj_element_id, ppr.as_of_date, ppev.parent_structure_version_id, pppa.task_weight_basis_code, ppe.baseline_start_date, ppe.baseline_finish_date),0),0,1,nvl(pa_progress_utils.get_bcws(ppa.project_id, ppr.object_id, ppe.proj_element_id, ppr.as_of_date, ppev.parent_structure_version_id, pppa.task_weight_basis_code, ppe.baseline_start_date, ppe.baseline_finish_date),0))),2) Sch_Performance_Index, ptav.assignment_end_date, ptav.assignment_start_date, ppe.proj_element_id, null Task_Manager_Name, ppe.manager_person_id, ppe.name, ppe.element_number, ptt.task_type, round(decode(ppr.task_wt_basis_code,'EFFORT',((nvl(ptav.baselined_planned_qty,0) -nvl(ppr.earned_value,0))/(decode((nvl(ptav.baselined_planned_qty,0)-(nvl(ppr.ppl_act_effort_to_date,0) +nvl(ppr.eqpmt_act_effort_to_date,0))),0,1,(nvl(ptav.bl_planned_bur_cost_txn_cur,0)-(nvl(ppr.ppl_act_effort_to_date,0)+nvl(ppr.eqpmt_act_effort_to_date,0)))))) ,((nvl(ptav.bl_planned_bur_cost_txn_cur,0)-nvl(ppr.earned_value,0)) /(decode((nvl(ptav.bl_planned_bur_cost_txn_cur,0)-(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))),0,1,(nvl(ptav.bl_planned_bur_cost_txn_cur,0)-(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))))))),2) To_Complete_Performance_Index, to_char(null) Unit_of_Measure, (nvl(ptav.bl_planned_bur_cost_txn_cur,0)-(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.bl_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))))) Var_at_Completion_Cost, (nvl(ptav.baselined_planned_qty,0)-(nvl(ppr.ppl_act_effort_to_date,0) +nvl(ppr.eqpmt_act_effort_to_date,0) +pa_progress_utils.sum_etc_values(ptav.baselined_planned_qty ,ppr.estimated_remaining_effort ,ppr.eqpmt_etc_effort ,null ,null ,null ,null ,null ,(nvl(ppr.ppl_act_effort_to_date,0)+nvl(ppr.eqpmt_act_effort_to_date,0)+nvl(ppr.oth_quantity_to_date,0))))) Var_at_Completion_Effort, ppev.wbs_number, ppe.wq_item_code, to_number(null) Work_Type, ptav.resource_assignment_id Resource_Assignment_Id, ppc.current_flag, ppev.Display_Sequence, ppev.element_version_id, ppa.long_name, ppev.object_type, ppe.proj_element_id, ppa.project_id, ppa.name, ppa.segment1, null published_by_name, ppc.published_by_party_id, ppc.published_flag, ppc.record_version_number, ptav.unit_of_measure_code, ptav.unit_of_measure, ppev.wbs_level, flv3.meaning, 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.resource_class, ptav.rbs_element_id, ptav.person_id, ppc.progress_comment, ppc.description, to_char(null) priority_description, 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, to_char(null) resources, ptav.rate_based_flag rate_based_flag, pa_progress_utils.get_act_effort_this_period (ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id ,ppr.proj_element_id) actual_effort_this_period, pa_progress_utils.get_act_cost_this_period (ppr.as_of_date,ppr.project_id,ppr.object_id,ppr.object_version_id ,ppr.proj_element_id) actual_cost_this_period, pa_progress_utils.get_latest_ass_prog_date(ppa.project_id,ppr.structure_type,ppr.object_id,ppr.object_type ,ppr.proj_element_id) latest_progress_date, ppev.parent_structure_version_id structure_version_id, ptav.spread_curve_id, ptav.spread_curve_name, ptav.assignment_description, ptav.planned_bur_cost_projfunc, ptav.planned_quantity, ptav.role_name, ptav.pm_res_assignment_reference, ptav.pm_project_reference, ptav.pm_product_code, ptav.pm_task_reference, ptav.resource_class_flag, ptav.unplanned_flag, ptav.resource_name, ptav.res_type_code, ptav.bom_resource_id, ptav.job_id, ptav.person_type_code, ptav.non_labor_resource, ptav.inventory_item_id, ptav.resource_class_id, ptav.item_category_id, ptav.financial_category_name, ptav.fc_res_type_code, ptav.expenditure_type, ptav.expenditure_category, ptav.event_type, ptav.revenue_category_code, ptav.organization_name, ptav.organization_id, ptav.supplier_name, ptav.supplier_id, ptav.team_role, ptav.incurred_by_resource_name, ptav.incur_by_role_id, ptav.incur_by_res_class_code, ptav.incurred_by_res_flag, ptav.procure_resource_flag, ptav.role_id, ptav.planning_start_date, ptav.planning_end_date, ptav.use_task_schedule_flag, ptav.delay, ptav.duration, ptav.manufacturing_cost_type, ptav.mfc_cost_type_id, ptav.etc_method_name, ptav.etc_method_code, ptav.billable_work_percent, ptav.planned_bur_cost_proj_cur, ptav.planned_raw_cost_txn_cur, ptav.planned_raw_cost_proj_cur, ptav.planned_raw_cost_projfunc, ptav.act_bur_cost_txn_cur, ptav.act_bur_cost_proj_cur, ptav.act_bur_cost_projfunc, ptav.act_raw_cost_txn_cur, ptav.act_raw_cost_proj_cur, ptav.act_raw_cost_projfunc, ptav.avg_raw_cost_rate, ptav.avg_bur_cost_rate, ptav.scheduled_delay From pa_projects_all ppa, pa_proj_elements ppe, pa_proj_element_versions ppev, pa_proj_elem_ver_schedule ppvsch, pa_proj_elem_ver_structure ppvs, pa_proj_structure_types ppst, pa_percent_completes ppc, pa_progress_rollup ppr, pa_proj_progress_attr pppa, pa_project_statuses pps, pa_project_statuses pps1, pa_task_types ptt, pa_lookups flv3, pa_lookups flv6, pa_task_assignments_v ptav WHERE ppe.project_id = ppvs.project_id AND ppev.project_id = ppvs.project_id AND ppst.proj_element_id = ppvs.proj_element_id AND ppst.structure_type_id = 1 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.project_id = ppvsch.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 ptav.project_id = ppr.project_id(+) and ptav.resource_list_member_id = ppr.object_id(+) and ptav.task_id = ppr.proj_element_id(+) and 'PA_ASSIGNMENTS' = ppr.object_type(+) and ppc.project_id(+) = ppr.project_id and ppc.object_id(+) = ppr.object_id and ppc.object_type(+) = ppr.object_type and ppc.structure_type(+) = ppr.structure_type and ppc.task_id(+) = ppr.proj_element_id and trunc(ppc.date_computed(+)) = trunc(ppr.as_of_date) and pppa.structure_type = 'WORKPLAN' and ppr.structure_type(+) = 'WORKPLAN' and ppr.object_version_id (+) = ptav.task_version_id and ppc.percent_complete_id(+) = ppr.percent_complete_id AND NVL(ppr.PROGRESS_STATUS_CODE, ppr.eff_rollup_prog_stat_code) = pps.PROJECT_STATUS_CODE(+) and nvl(ppc.status_code,nvl(ppe.status_code,'124')) = pps1.project_status_code and flv6.lookup_type(+) = 'PA_TASK_PRIORITY_CODE' and ppe.priority_code = flv6.lookup_code(+) and flv3.lookup_type(+) = 'PA_WQ_WORK_ITEMS' and ppe.wq_item_code = flv3.lookup_code(+) 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.structure_version_id =ppev.parent_structure_version_id and ppvs.project_id = pppa.project_id(+) and ppvs.proj_element_id = pppa.object_id(+) and ptav.ta_display_flag = 'Y'

View Text - HTML Formatted

SELECT PPR.ACTUAL_FINISH_DATE - PTAV.ASSIGNMENT_END_DATE ACT_SCH_FINISH_VAR
, PPR.ACTUAL_START_DATE - PTAV.ASSIGNMENT_START_DATE ACT_SCH_START_VAR
, (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_COST_THIS_PERIOD (PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, 0)) ACTUAL_COST
, (DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_EFFORT_TO_DATE
, 'EQUIPMENT'
, PPR.EQPMT_ACT_EFFORT_TO_DATE) - NVL(PA_PROGRESS_UTILS.GET_ACT_EFFORT_THIS_PERIOD (PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID)
, 0)) ACTUAL_EFFORT
, PPR.ACTUAL_FINISH_DATE
, PPR.ACTUAL_START_DATE
, PPR.CUMULATIVE_WORK_QUANTITY ACTUAL_WORK_QUANTITY
, PPR.AS_OF_DATE AS_OF_DATE
, PTAV.BL_PLANNED_BUR_COST_TXN_CUR BASELINE_COST
, PTAV.BASELINED_ASGMT_END_DATE
, PTAV.BASELINED_ASGMT_START_DATE
, PTAV.BASELINED_PLANNED_QTY BASELINED_EFFORT
, (PA_PROGRESS_UTILS.GET_NEXT_PROGRESS_CYCLE(PPA.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, PTAV.RESOURCE_LIST_MEMBER_ID
, 'PA_ASSIGNMENTS')-TRUNC(SYSDATE)) DAYS_UNTIL_PROGRESS_DUE
, TRUNC(PTAV.ASSIGNMENT_END_DATE) - TRUNC(SYSDATE)
, (TRUNC(PTAV.ASSIGNMENT_START_DATE)-TRUNC(SYSDATE)) DAYS_UNTIL_START
, PPR.EARNED_VALUE EARNED_VALUE
, (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) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_QUANTITY
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, NULL
, NULL
, NULL
, NULL
, (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
, (PPR.ESTIMATED_FINISH_DATE - PTAV.BASELINED_ASGMT_END_DATE) EST_BAS_FINISH_VAR
, (PPR.ESTIMATED_START_DATE - PTAV.BASELINED_ASGMT_START_DATE) EST_BAS_START_VAR
, (NVL(PPR.EQPMT_ETC_EFFORT
, 0) + NVL(ESTIMATED_REMAINING_EFFORT
, 0)) ESTIMATED_REMAINING_EFFORT
, PPR.ESTIMATED_FINISH_DATE - PTAV.ASSIGNMENT_END_DATE EST_SCH_FINISH_VAR
, 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
, NULL
, (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
, (NVL(PPVSCH.WQ_PLANNED_QUANTITY
, 0) - NVL(CUMULATIVE_WORK_QUANTITY
, 0) ) ETC_WORK_QUANTITY
, 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( (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))
, ( NVL(PPR.OTH_ETC_COST_TC
, 0)+NVL(PPR.PPL_ETC_COST_TC
, 0)+NVL(PPR.EQPMT_ETC_COST_TC
, 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.ESTIMATED_REMAINING_EFFORT
, 0)+NVL(PPR.EQPMT_ETC_EFFORT
, 0)) ) PERCENT_COMPLETE_EFFORT
, PPC.PERCENT_COMPLETE_ID
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( (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))
, (NVL(PTAV.PLANNED_BUR_COST_TXN_CUR
, 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(PTAV.PLANNED_QUANTITY
, 0)) ) PERCENT_SPENT_EFFORT
, NVL(PTAV.PLANNED_BUR_COST_TXN_CUR
, 0) PLANNED_COST
, (NVL(PTAV.PLANNED_BUR_COST_TXN_CUR
, 0)/DECODE(NVL(PPR.CUMULATIVE_WORK_QUANTITY
, 0)
, 0
, 1
, NVL(PPR.CUMULATIVE_WORK_QUANTITY
, 0))) PLANNED_COST_PER_UNIT
, NVL(PTAV.PLANNED_QUANTITY
, 0)
, PPVSCH.WQ_PLANNED_QUANTITY
, PA_RELATIONSHIP_UTILS.DISPLAY_PREDECESSORS(PPE.PROJ_ELEMENT_ID) PREDECESSORS
, PPE.PRIORITY_CODE
, PTAV.ASSIGNMENT_END_DATE - PTAV.BASELINED_ASGMT_END_DATE SCH_BASE_FINISH_VAR
, PTAV.ASSIGNMENT_START_DATE -PTAV.BASELINED_ASGMT_START_DATE SCH_BASE_START_VAR
, ROUND((NVL(PPR.EARNED_VALUE
, 0)/DECODE(NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPE.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPEV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE)
, 0)
, 0
, 1
, NVL(PA_PROGRESS_UTILS.GET_BCWS(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPE.PROJ_ELEMENT_ID
, PPR.AS_OF_DATE
, PPEV.PARENT_STRUCTURE_VERSION_ID
, PPPA.TASK_WEIGHT_BASIS_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE)
, 0)))
, 2) SCH_PERFORMANCE_INDEX
, PTAV.ASSIGNMENT_END_DATE
, PTAV.ASSIGNMENT_START_DATE
, PPE.PROJ_ELEMENT_ID
, NULL TASK_MANAGER_NAME
, PPE.MANAGER_PERSON_ID
, PPE.NAME
, PPE.ELEMENT_NUMBER
, PTT.TASK_TYPE
, ROUND(DECODE(PPR.TASK_WT_BASIS_CODE
, 'EFFORT'
, ((NVL(PTAV.BASELINED_PLANNED_QTY
, 0) -NVL(PPR.EARNED_VALUE
, 0))/(DECODE((NVL(PTAV.BASELINED_PLANNED_QTY
, 0)-(NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)))
, 0
, 1
, (NVL(PTAV.BL_PLANNED_BUR_COST_TXN_CUR
, 0)-(NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0))))))
, ((NVL(PTAV.BL_PLANNED_BUR_COST_TXN_CUR
, 0)-NVL(PPR.EARNED_VALUE
, 0)) /(DECODE((NVL(PTAV.BL_PLANNED_BUR_COST_TXN_CUR
, 0)-(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)))
, 0
, 1
, (NVL(PTAV.BL_PLANNED_BUR_COST_TXN_CUR
, 0)-(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)))))))
, 2) TO_COMPLETE_PERFORMANCE_INDEX
, TO_CHAR(NULL) UNIT_OF_MEASURE
, (NVL(PTAV.BL_PLANNED_BUR_COST_TXN_CUR
, 0)-(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.BL_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))))) VAR_AT_COMPLETION_COST
, (NVL(PTAV.BASELINED_PLANNED_QTY
, 0)-(NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.BASELINED_PLANNED_QTY
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))))) VAR_AT_COMPLETION_EFFORT
, PPEV.WBS_NUMBER
, PPE.WQ_ITEM_CODE
, TO_NUMBER(NULL) WORK_TYPE
, PTAV.RESOURCE_ASSIGNMENT_ID RESOURCE_ASSIGNMENT_ID
, PPC.CURRENT_FLAG
, PPEV.DISPLAY_SEQUENCE
, PPEV.ELEMENT_VERSION_ID
, PPA.LONG_NAME
, PPEV.OBJECT_TYPE
, PPE.PROJ_ELEMENT_ID
, PPA.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, NULL PUBLISHED_BY_NAME
, PPC.PUBLISHED_BY_PARTY_ID
, PPC.PUBLISHED_FLAG
, PPC.RECORD_VERSION_NUMBER
, PTAV.UNIT_OF_MEASURE_CODE
, PTAV.UNIT_OF_MEASURE
, PPEV.WBS_LEVEL
, FLV3.MEANING
, 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.RESOURCE_CLASS
, PTAV.RBS_ELEMENT_ID
, PTAV.PERSON_ID
, PPC.PROGRESS_COMMENT
, PPC.DESCRIPTION
, TO_CHAR(NULL) PRIORITY_DESCRIPTION
, 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
, TO_CHAR(NULL) RESOURCES
, PTAV.RATE_BASED_FLAG RATE_BASED_FLAG
, PA_PROGRESS_UTILS.GET_ACT_EFFORT_THIS_PERIOD (PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID) ACTUAL_EFFORT_THIS_PERIOD
, PA_PROGRESS_UTILS.GET_ACT_COST_THIS_PERIOD (PPR.AS_OF_DATE
, PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PPR.PROJ_ELEMENT_ID) ACTUAL_COST_THIS_PERIOD
, PA_PROGRESS_UTILS.GET_LATEST_ASS_PROG_DATE(PPA.PROJECT_ID
, PPR.STRUCTURE_TYPE
, PPR.OBJECT_ID
, PPR.OBJECT_TYPE
, PPR.PROJ_ELEMENT_ID) LATEST_PROGRESS_DATE
, PPEV.PARENT_STRUCTURE_VERSION_ID STRUCTURE_VERSION_ID
, PTAV.SPREAD_CURVE_ID
, PTAV.SPREAD_CURVE_NAME
, PTAV.ASSIGNMENT_DESCRIPTION
, PTAV.PLANNED_BUR_COST_PROJFUNC
, PTAV.PLANNED_QUANTITY
, PTAV.ROLE_NAME
, PTAV.PM_RES_ASSIGNMENT_REFERENCE
, PTAV.PM_PROJECT_REFERENCE
, PTAV.PM_PRODUCT_CODE
, PTAV.PM_TASK_REFERENCE
, PTAV.RESOURCE_CLASS_FLAG
, PTAV.UNPLANNED_FLAG
, PTAV.RESOURCE_NAME
, PTAV.RES_TYPE_CODE
, PTAV.BOM_RESOURCE_ID
, PTAV.JOB_ID
, PTAV.PERSON_TYPE_CODE
, PTAV.NON_LABOR_RESOURCE
, PTAV.INVENTORY_ITEM_ID
, PTAV.RESOURCE_CLASS_ID
, PTAV.ITEM_CATEGORY_ID
, PTAV.FINANCIAL_CATEGORY_NAME
, PTAV.FC_RES_TYPE_CODE
, PTAV.EXPENDITURE_TYPE
, PTAV.EXPENDITURE_CATEGORY
, PTAV.EVENT_TYPE
, PTAV.REVENUE_CATEGORY_CODE
, PTAV.ORGANIZATION_NAME
, PTAV.ORGANIZATION_ID
, PTAV.SUPPLIER_NAME
, PTAV.SUPPLIER_ID
, PTAV.TEAM_ROLE
, PTAV.INCURRED_BY_RESOURCE_NAME
, PTAV.INCUR_BY_ROLE_ID
, PTAV.INCUR_BY_RES_CLASS_CODE
, PTAV.INCURRED_BY_RES_FLAG
, PTAV.PROCURE_RESOURCE_FLAG
, PTAV.ROLE_ID
, PTAV.PLANNING_START_DATE
, PTAV.PLANNING_END_DATE
, PTAV.USE_TASK_SCHEDULE_FLAG
, PTAV.DELAY
, PTAV.DURATION
, PTAV.MANUFACTURING_COST_TYPE
, PTAV.MFC_COST_TYPE_ID
, PTAV.ETC_METHOD_NAME
, PTAV.ETC_METHOD_CODE
, PTAV.BILLABLE_WORK_PERCENT
, PTAV.PLANNED_BUR_COST_PROJ_CUR
, PTAV.PLANNED_RAW_COST_TXN_CUR
, PTAV.PLANNED_RAW_COST_PROJ_CUR
, PTAV.PLANNED_RAW_COST_PROJFUNC
, PTAV.ACT_BUR_COST_TXN_CUR
, PTAV.ACT_BUR_COST_PROJ_CUR
, PTAV.ACT_BUR_COST_PROJFUNC
, PTAV.ACT_RAW_COST_TXN_CUR
, PTAV.ACT_RAW_COST_PROJ_CUR
, PTAV.ACT_RAW_COST_PROJFUNC
, PTAV.AVG_RAW_COST_RATE
, PTAV.AVG_BUR_COST_RATE
, PTAV.SCHEDULED_DELAY
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_SCHEDULE PPVSCH
, PA_PROJ_ELEM_VER_STRUCTURE PPVS
, PA_PROJ_STRUCTURE_TYPES PPST
, PA_PERCENT_COMPLETES PPC
, PA_PROGRESS_ROLLUP PPR
, PA_PROJ_PROGRESS_ATTR PPPA
, PA_PROJECT_STATUSES PPS
, PA_PROJECT_STATUSES PPS1
, PA_TASK_TYPES PTT
, PA_LOOKUPS FLV3
, PA_LOOKUPS FLV6
, PA_TASK_ASSIGNMENTS_V PTAV
WHERE PPE.PROJECT_ID = PPVS.PROJECT_ID
AND PPEV.PROJECT_ID = PPVS.PROJECT_ID
AND PPST.PROJ_ELEMENT_ID = PPVS.PROJ_ELEMENT_ID
AND PPST.STRUCTURE_TYPE_ID = 1
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.PROJECT_ID = PPVSCH.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 PTAV.PROJECT_ID = PPR.PROJECT_ID(+)
AND PTAV.RESOURCE_LIST_MEMBER_ID = PPR.OBJECT_ID(+)
AND PTAV.TASK_ID = PPR.PROJ_ELEMENT_ID(+)
AND 'PA_ASSIGNMENTS' = PPR.OBJECT_TYPE(+)
AND PPC.PROJECT_ID(+) = PPR.PROJECT_ID
AND PPC.OBJECT_ID(+) = PPR.OBJECT_ID
AND PPC.OBJECT_TYPE(+) = PPR.OBJECT_TYPE
AND PPC.STRUCTURE_TYPE(+) = PPR.STRUCTURE_TYPE
AND PPC.TASK_ID(+) = PPR.PROJ_ELEMENT_ID
AND TRUNC(PPC.DATE_COMPUTED(+)) = TRUNC(PPR.AS_OF_DATE)
AND PPPA.STRUCTURE_TYPE = 'WORKPLAN'
AND PPR.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PPR.OBJECT_VERSION_ID (+) = PTAV.TASK_VERSION_ID
AND PPC.PERCENT_COMPLETE_ID(+) = PPR.PERCENT_COMPLETE_ID
AND NVL(PPR.PROGRESS_STATUS_CODE
, PPR.EFF_ROLLUP_PROG_STAT_CODE) = PPS.PROJECT_STATUS_CODE(+)
AND NVL(PPC.STATUS_CODE
, NVL(PPE.STATUS_CODE
, '124')) = PPS1.PROJECT_STATUS_CODE
AND FLV6.LOOKUP_TYPE(+) = 'PA_TASK_PRIORITY_CODE'
AND PPE.PRIORITY_CODE = FLV6.LOOKUP_CODE(+)
AND FLV3.LOOKUP_TYPE(+) = 'PA_WQ_WORK_ITEMS'
AND PPE.WQ_ITEM_CODE = FLV3.LOOKUP_CODE(+)
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.STRUCTURE_VERSION_ID =PPEV.PARENT_STRUCTURE_VERSION_ID
AND PPVS.PROJECT_ID = PPPA.PROJECT_ID(+)
AND PPVS.PROJ_ELEMENT_ID = PPPA.OBJECT_ID(+)
AND PTAV.TA_DISPLAY_FLAG = 'Y'