VIEW: APPS.PA_TASK_PROGRESS_AMG_V

Source

View Text - Preformatted

SELECT /*+ index(ppr,PA_PROGRESS_ROLLUP_U2) */ ppe.project_id, ppe.proj_element_id, ppev.element_version_id, ppe.name, ppe.element_number, ppr.as_of_date , nvl(ppr.completed_percentage, nvl(ppr.base_percent_complete,ppr.eff_rollup_percent_comp)) completed_percentage, decode(ppc.published_flag, 'N', ppc.status_code, ppe.status_code), pps1.project_status_name, pps.project_status_code, pps.project_status_name, ppr.actual_start_date, ppr.actual_finish_date, ppr.estimated_start_date, ppr.estimated_finish_date, ppr.cumulative_work_quantity, pa_progress_utils.sum_etc_values((nvl(pxpv.labor_effort,0)+nvl(pxpv.equipment_effort,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_remaining_effort, ppc.published_by_party_id, ppc.progress_comment, ppc.description, ptt.prog_entry_enable_flag, ppr.object_id, ppr.object_version_id, pst.structure_type_class_code, (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, (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.ppl_etc_cost_pc,0)+nvl(ppr.eqpmt_etc_cost_pc,0)+nvl(ppr.oth_etc_cost_pc,0) +nvl(ppr.subprj_ppl_etc_cost_pc,0)+nvl(ppr.subprj_eqpmt_etc_cost_pc,0) +nvl(ppr.subprj_oth_etc_cost_pc,0)) estimate_at_completion_cost, (nvl(pxpv.labor_effort,0)+ nvl(pxpv.equipment_effort,0)) planned_effort From pa_proj_elements ppe, pa_proj_element_versions ppev, pa_proj_elem_ver_structure ppvs, pa_percent_completes ppc, pa_progress_rollup ppr, pa_project_statuses pps, pa_project_statuses pps1, pa_task_types ptt, pa_proj_structure_types ppst, pa_structure_types pst, pji_xbs_plans_v pxpv WHERE ppe.link_task_flag <> 'Y' and ppe.object_Type = 'PA_TASKS' and ppe.project_id = ppev.project_id 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 ppvs.latest_eff_published_flag = 'Y' and ppe.type_id = ptt.task_type_id(+) and ptt.object_type = 'PA_TASKS' AND pst.structure_type_id = ppst.structure_type_id AND ppst.proj_element_id = ppvs.proj_element_id and (nvl(ppr.current_flag,'N') = 'W' or (ppr.current_flag = 'Y' and not exists (select 1 from pa_progress_rollup ppc1 where ppc1.project_id = ppev.project_id and ppc1.object_id = ppev.proj_element_id and ppc1.object_Type = ppev.object_type and ppc1.structure_type = 'WORKPLAN' and ppc1.structure_version_id is null and ppc1.current_flag = 'W'))) and ppr.percent_complete_id = ppc.percent_complete_id(+) and ppr.structure_version_id is null and ppr.progress_status_code = pps.project_status_code(+) and decode(ppc.published_flag,'N',ppc.status_code,nvl(ppe.status_code,'IN_PROGRESS')) = pps1.project_status_code and ppev.project_id = ppr.project_id and ppev.proj_element_id = ppr.object_id and ppev.object_type = ppr.object_type and trunc(nvl(ppc.date_computed(+),ppr.as_of_date)) = trunc(ppr.as_of_date) and ppr.structure_type(+) = 'WORKPLAN' and ppc.structure_type(+) = 'WORKPLAN' AND pxpv.project_id(+) = ppev.project_id AND pxpv.structure_version_id(+) = ppev.parent_structure_version_id AND pxpv.proj_element_id(+) = ppev.proj_element_id AND pxpv.structure_type(+) = 'WORKPLAN'

View Text - HTML Formatted

SELECT /*+ INDEX(PPR
, PA_PROGRESS_ROLLUP_U2) */ PPE.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, PPEV.ELEMENT_VERSION_ID
, PPE.NAME
, PPE.ELEMENT_NUMBER
, PPR.AS_OF_DATE
, NVL(PPR.COMPLETED_PERCENTAGE
, NVL(PPR.BASE_PERCENT_COMPLETE
, PPR.EFF_ROLLUP_PERCENT_COMP)) COMPLETED_PERCENTAGE
, DECODE(PPC.PUBLISHED_FLAG
, 'N'
, PPC.STATUS_CODE
, PPE.STATUS_CODE)
, PPS1.PROJECT_STATUS_NAME
, PPS.PROJECT_STATUS_CODE
, PPS.PROJECT_STATUS_NAME
, PPR.ACTUAL_START_DATE
, PPR.ACTUAL_FINISH_DATE
, PPR.ESTIMATED_START_DATE
, PPR.ESTIMATED_FINISH_DATE
, PPR.CUMULATIVE_WORK_QUANTITY
, PA_PROGRESS_UTILS.SUM_ETC_VALUES((NVL(PXPV.LABOR_EFFORT
, 0)+NVL(PXPV.EQUIPMENT_EFFORT
, 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_REMAINING_EFFORT
, PPC.PUBLISHED_BY_PARTY_ID
, PPC.PROGRESS_COMMENT
, PPC.DESCRIPTION
, PTT.PROG_ENTRY_ENABLE_FLAG
, PPR.OBJECT_ID
, PPR.OBJECT_VERSION_ID
, PST.STRUCTURE_TYPE_CLASS_CODE
, (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
, (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.PPL_ETC_COST_PC
, 0)+NVL(PPR.EQPMT_ETC_COST_PC
, 0)+NVL(PPR.OTH_ETC_COST_PC
, 0) +NVL(PPR.SUBPRJ_PPL_ETC_COST_PC
, 0)+NVL(PPR.SUBPRJ_EQPMT_ETC_COST_PC
, 0) +NVL(PPR.SUBPRJ_OTH_ETC_COST_PC
, 0)) ESTIMATE_AT_COMPLETION_COST
, (NVL(PXPV.LABOR_EFFORT
, 0)+ NVL(PXPV.EQUIPMENT_EFFORT
, 0)) PLANNED_EFFORT
FROM PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_STRUCTURE PPVS
, PA_PERCENT_COMPLETES PPC
, PA_PROGRESS_ROLLUP PPR
, PA_PROJECT_STATUSES PPS
, PA_PROJECT_STATUSES PPS1
, PA_TASK_TYPES PTT
, PA_PROJ_STRUCTURE_TYPES PPST
, PA_STRUCTURE_TYPES PST
, PJI_XBS_PLANS_V PXPV
WHERE PPE.LINK_TASK_FLAG <> 'Y'
AND PPE.OBJECT_TYPE = 'PA_TASKS'
AND PPE.PROJECT_ID = PPEV.PROJECT_ID
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 PPVS.LATEST_EFF_PUBLISHED_FLAG = 'Y'
AND PPE.TYPE_ID = PTT.TASK_TYPE_ID(+)
AND PTT.OBJECT_TYPE = 'PA_TASKS'
AND PST.STRUCTURE_TYPE_ID = PPST.STRUCTURE_TYPE_ID
AND PPST.PROJ_ELEMENT_ID = PPVS.PROJ_ELEMENT_ID
AND (NVL(PPR.CURRENT_FLAG
, 'N') = 'W' OR (PPR.CURRENT_FLAG = 'Y'
AND NOT EXISTS (SELECT 1
FROM PA_PROGRESS_ROLLUP PPC1
WHERE PPC1.PROJECT_ID = PPEV.PROJECT_ID
AND PPC1.OBJECT_ID = PPEV.PROJ_ELEMENT_ID
AND PPC1.OBJECT_TYPE = PPEV.OBJECT_TYPE
AND PPC1.STRUCTURE_TYPE = 'WORKPLAN'
AND PPC1.STRUCTURE_VERSION_ID IS NULL
AND PPC1.CURRENT_FLAG = 'W')))
AND PPR.PERCENT_COMPLETE_ID = PPC.PERCENT_COMPLETE_ID(+)
AND PPR.STRUCTURE_VERSION_ID IS NULL
AND PPR.PROGRESS_STATUS_CODE = PPS.PROJECT_STATUS_CODE(+)
AND DECODE(PPC.PUBLISHED_FLAG
, 'N'
, PPC.STATUS_CODE
, NVL(PPE.STATUS_CODE
, 'IN_PROGRESS')) = PPS1.PROJECT_STATUS_CODE
AND PPEV.PROJECT_ID = PPR.PROJECT_ID
AND PPEV.PROJ_ELEMENT_ID = PPR.OBJECT_ID
AND PPEV.OBJECT_TYPE = PPR.OBJECT_TYPE
AND TRUNC(NVL(PPC.DATE_COMPUTED(+)
, PPR.AS_OF_DATE)) = TRUNC(PPR.AS_OF_DATE)
AND PPR.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PPC.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PXPV.PROJECT_ID(+) = PPEV.PROJECT_ID
AND PXPV.STRUCTURE_VERSION_ID(+) = PPEV.PARENT_STRUCTURE_VERSION_ID
AND PXPV.PROJ_ELEMENT_ID(+) = PPEV.PROJ_ELEMENT_ID
AND PXPV.STRUCTURE_TYPE(+) = 'WORKPLAN'