VIEW: APPS.PA_PROJ_TASK_PROG_HIST_V

Source

View Text - Preformatted

SELECT ppa.project_id, ppa.name, ppa.segment1, ppa.long_name, decode( ppe.object_type, 'PA_STRUCTURES', ppvs.name , null), decode( ppe.object_type, 'PA_STRUCTURES', to_char(ppvs.version_number ), null ), ppe.proj_element_id, ppev2.proj_element_id, ppe.proj_element_id, por.object_id_from1, ppev.element_version_id, ppe.object_type, 'WORKPLAN', decode( ppe.object_type, 'PA_TASKS', ppe.name , null), decode( ppe.object_type, 'PA_TASKS', ppe.element_number , null ), ppev.wbs_number, ppev.wbs_level, ppev.display_sequence, ppe.manager_person_id, '', ppvsch.milestone_flag, ppvsch.critical_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, ppe.status_code, pps3.project_status_name, pps3.project_system_status_code, ppvsch.planned_effort, ppvsch.wq_planned_quantity, ppe.wq_item_code, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_WQ_WORK_ITEMS',ppe.wq_item_code), ppe.wq_uom_code, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('UNIT',ppe.wq_uom_code), ppc.date_computed, ppc.percent_complete_id, nvl(ppc.completed_percentage,ppr.eff_rollup_percent_comp), pps.project_status_code, pps.project_status_name, pps.PROJECT_SYSTEM_STATUS_CODE, pps.status_icon_ind, pps.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, ppr.incremental_work_quantity, ppr.cumulative_work_quantity, ppr.base_percent_complete, ppr.eff_rollup_percent_comp, ppr.estimated_remaining_effort, ppc.published_by_party_id, null, ppc.progress_comment, ppc.description, ppc.last_update_date, ppc.published_flag, ppc.current_flag, ppc.record_version_number, ppr.BASE_PROGRESS_STATUS_CODE, pps1.project_status_name, 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')), ppe.last_update_date, ppvsch.last_update_date, 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')), ppr.EFF_ROLLUP_PROG_STAT_CODE, pps2.project_status_name, pps2.status_icon_ind, ppe.PROGRESS_OUTDATED_FLAG, ptt.BASE_PERCENT_COMP_DERIV_CODE, ppe.BASELINE_START_DATE, ppe.BASELINE_FINISH_DATE, decode(ppe.object_type,'PA_TASKS',ptt.PROG_ENTRY_ENABLE_FLAG,'Y'), ptt.prog_entry_req_flag, ppr.percent_complete_id, ppc.STRUCTURE_TYPE, ppr.PROJ_ELEMENT_ID, ppr.PPL_ACT_EFFORT_TO_DATE, ppr.EQPMT_ACT_EFFORT_TO_DATE, nvl(ppr.PPL_ACT_EFFORT_TO_DATE,0) + nvl(ppr.EQPMT_ACT_EFFORT_TO_DATE,0) total_act_effort_to_date, ppr.EQPMT_ETC_EFFORT, nvl(ppr.EQPMT_ETC_EFFORT,0) + nvl(ppr.estimated_remaining_effort,0) total_etc_effort, ppr.OTH_ACT_COST_TO_DATE_TC, ppr.OTH_ACT_COST_TO_DATE_PC, ppr.OTH_ACT_COST_TO_DATE_FC, ppr.OTH_ETC_COST_TC, ppr.OTH_ETC_COST_PC, ppr.OTH_ETC_COST_FC, ppr.PPL_ACT_COST_TO_DATE_TC, ppr.PPL_ACT_COST_TO_DATE_PC, ppr.PPL_ACT_COST_TO_DATE_FC, ppr.PPL_ETC_COST_TC, ppr.PPL_ETC_COST_PC, ppr.PPL_ETC_COST_FC, ppr.EQPMT_ACT_COST_TO_DATE_TC, ppr.EQPMT_ACT_COST_TO_DATE_PC, ppr.EQPMT_ACT_COST_TO_DATE_FC, 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) total_act_cost_to_date_tc, 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) total_act_cost_to_date_pc, nvl(ppr.OTH_ACT_COST_TO_DATE_FC,0) + nvl(ppr.PPL_ACT_COST_TO_DATE_FC,0) + nvl(ppr.EQPMT_ACT_COST_TO_DATE_FC,0) total_act_cost_to_date_fc, ppr.EQPMT_ETC_COST_TC, ppr.EQPMT_ETC_COST_PC, ppr.EQPMT_ETC_COST_FC, nvl(ppr.OTH_ETC_COST_TC,0) + nvl(ppr.PPL_ETC_COST_TC,0) + nvl(ppr.EQPMT_ETC_COST_TC,0) total_etc_cost_tc, nvl(ppr.OTH_ETC_COST_PC,0) + nvl(ppr.PPL_ETC_COST_PC,0) + nvl(ppr.EQPMT_ETC_COST_PC,0) total_etc_cost_pc, nvl(ppr.OTH_ETC_COST_FC,0) + nvl(ppr.PPL_ETC_COST_FC,0) + nvl(ppr.EQPMT_ETC_COST_FC,0) total_etc_cost_fc, ppr.EARNED_VALUE, ppr.CURRENT_FLAG, ppr.TXN_CURRENCY_CODE, ppr.PROG_PA_PERIOD_NAME, ppr.PROG_GL_PERIOD_NAME, ppev.parent_structure_version_id, nvl(ppr.OTH_ACT_RAWCOST_TO_DATE_PC,0) + nvl(ppr.PPL_ACT_RAWCOST_TO_DATE_PC,0) + nvl(ppr.EQPMT_ACT_RAWCOST_TO_DATE_PC,0) total_act_rawcost_to_date_pc, nvl(ppr.OTH_ETC_RAWCOST_PC,0) + nvl(ppr.PPL_ETC_RAWCOST_PC,0) + nvl(ppr.EQPMT_ETC_RAWCOST_PC,0) total_etc_rawcost_pc From pa_projects_all ppa, pa_proj_elements ppe, pa_proj_element_versions ppev, pa_proj_element_versions ppev2, 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_object_relationships por, pa_percent_completes ppc, pa_progress_rollup ppr, pa_project_statuses pps, pa_project_statuses pps1, pa_project_statuses pps2, pa_project_statuses pps3 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 ppa.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 ppvs.project_id = pppa.project_id(+) and ppvs.proj_element_id = pppa.object_id(+) and pppa.structure_type = 'WORKPLAN' and nvl(ppe.status_code,'X') = pps3.project_status_code(+) and ppev.element_version_id = por.object_id_to1(+) AND por.object_type_to(+) = 'PA_TASKS' AND por.relationship_type (+) = 'S' AND por.object_id_from1 = ppev2.element_version_id(+) and ppe.project_id = ppc.project_id and ppe.proj_element_id = ppc.object_id and ppe.object_type = ppc.object_type and ppc.project_id = ppr.project_id(+) and ppc.object_id = ppr.object_id(+) and ppc.object_type = ppr.object_type(+) and ppr.current_flag(+) <> 'W' and ppc.published_flag = 'Y' and trunc(ppc.date_computed) = trunc(ppr.as_of_date) and pps.project_status_code (+)= ppc.progress_status_code and pps1.project_status_code (+)= ppr.BASE_PROGRESS_STATUS_CODE and pps2.project_status_code (+)= ppr.EFF_ROLLUP_PROG_STAT_CODE and ppc.structure_type = 'WORKPLAN' and ppr.structure_type(+) = ppc.structure_type and ppr.structure_version_id(+) is null

View Text - HTML Formatted

SELECT PPA.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PPA.LONG_NAME
, DECODE( PPE.OBJECT_TYPE
, 'PA_STRUCTURES'
, PPVS.NAME
, NULL)
, DECODE( PPE.OBJECT_TYPE
, 'PA_STRUCTURES'
, TO_CHAR(PPVS.VERSION_NUMBER )
, NULL )
, PPE.PROJ_ELEMENT_ID
, PPEV2.PROJ_ELEMENT_ID
, PPE.PROJ_ELEMENT_ID
, POR.OBJECT_ID_FROM1
, PPEV.ELEMENT_VERSION_ID
, PPE.OBJECT_TYPE
, 'WORKPLAN'
, DECODE( PPE.OBJECT_TYPE
, 'PA_TASKS'
, PPE.NAME
, NULL)
, DECODE( PPE.OBJECT_TYPE
, 'PA_TASKS'
, PPE.ELEMENT_NUMBER
, NULL )
, PPEV.WBS_NUMBER
, PPEV.WBS_LEVEL
, PPEV.DISPLAY_SEQUENCE
, PPE.MANAGER_PERSON_ID
, ''
, PPVSCH.MILESTONE_FLAG
, PPVSCH.CRITICAL_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
, PPE.STATUS_CODE
, PPS3.PROJECT_STATUS_NAME
, PPS3.PROJECT_SYSTEM_STATUS_CODE
, PPVSCH.PLANNED_EFFORT
, PPVSCH.WQ_PLANNED_QUANTITY
, PPE.WQ_ITEM_CODE
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_WQ_WORK_ITEMS'
, PPE.WQ_ITEM_CODE)
, PPE.WQ_UOM_CODE
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('UNIT'
, PPE.WQ_UOM_CODE)
, PPC.DATE_COMPUTED
, PPC.PERCENT_COMPLETE_ID
, NVL(PPC.COMPLETED_PERCENTAGE
, PPR.EFF_ROLLUP_PERCENT_COMP)
, PPS.PROJECT_STATUS_CODE
, PPS.PROJECT_STATUS_NAME
, PPS.PROJECT_SYSTEM_STATUS_CODE
, PPS.STATUS_ICON_IND
, PPS.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
, PPR.INCREMENTAL_WORK_QUANTITY
, PPR.CUMULATIVE_WORK_QUANTITY
, PPR.BASE_PERCENT_COMPLETE
, PPR.EFF_ROLLUP_PERCENT_COMP
, PPR.ESTIMATED_REMAINING_EFFORT
, PPC.PUBLISHED_BY_PARTY_ID
, NULL
, PPC.PROGRESS_COMMENT
, PPC.DESCRIPTION
, PPC.LAST_UPDATE_DATE
, PPC.PUBLISHED_FLAG
, PPC.CURRENT_FLAG
, PPC.RECORD_VERSION_NUMBER
, PPR.BASE_PROGRESS_STATUS_CODE
, PPS1.PROJECT_STATUS_NAME
, 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'))
, PPE.LAST_UPDATE_DATE
, PPVSCH.LAST_UPDATE_DATE
, 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'))
, PPR.EFF_ROLLUP_PROG_STAT_CODE
, PPS2.PROJECT_STATUS_NAME
, PPS2.STATUS_ICON_IND
, PPE.PROGRESS_OUTDATED_FLAG
, PTT.BASE_PERCENT_COMP_DERIV_CODE
, PPE.BASELINE_START_DATE
, PPE.BASELINE_FINISH_DATE
, DECODE(PPE.OBJECT_TYPE
, 'PA_TASKS'
, PTT.PROG_ENTRY_ENABLE_FLAG
, 'Y')
, PTT.PROG_ENTRY_REQ_FLAG
, PPR.PERCENT_COMPLETE_ID
, PPC.STRUCTURE_TYPE
, PPR.PROJ_ELEMENT_ID
, PPR.PPL_ACT_EFFORT_TO_DATE
, PPR.EQPMT_ACT_EFFORT_TO_DATE
, NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0) + NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) TOTAL_ACT_EFFORT_TO_DATE
, PPR.EQPMT_ETC_EFFORT
, NVL(PPR.EQPMT_ETC_EFFORT
, 0) + NVL(PPR.ESTIMATED_REMAINING_EFFORT
, 0) TOTAL_ETC_EFFORT
, PPR.OTH_ACT_COST_TO_DATE_TC
, PPR.OTH_ACT_COST_TO_DATE_PC
, PPR.OTH_ACT_COST_TO_DATE_FC
, PPR.OTH_ETC_COST_TC
, PPR.OTH_ETC_COST_PC
, PPR.OTH_ETC_COST_FC
, PPR.PPL_ACT_COST_TO_DATE_TC
, PPR.PPL_ACT_COST_TO_DATE_PC
, PPR.PPL_ACT_COST_TO_DATE_FC
, PPR.PPL_ETC_COST_TC
, PPR.PPL_ETC_COST_PC
, PPR.PPL_ETC_COST_FC
, PPR.EQPMT_ACT_COST_TO_DATE_TC
, PPR.EQPMT_ACT_COST_TO_DATE_PC
, PPR.EQPMT_ACT_COST_TO_DATE_FC
, 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) TOTAL_ACT_COST_TO_DATE_TC
, 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) TOTAL_ACT_COST_TO_DATE_PC
, NVL(PPR.OTH_ACT_COST_TO_DATE_FC
, 0) + NVL(PPR.PPL_ACT_COST_TO_DATE_FC
, 0) + NVL(PPR.EQPMT_ACT_COST_TO_DATE_FC
, 0) TOTAL_ACT_COST_TO_DATE_FC
, PPR.EQPMT_ETC_COST_TC
, PPR.EQPMT_ETC_COST_PC
, PPR.EQPMT_ETC_COST_FC
, NVL(PPR.OTH_ETC_COST_TC
, 0) + NVL(PPR.PPL_ETC_COST_TC
, 0) + NVL(PPR.EQPMT_ETC_COST_TC
, 0) TOTAL_ETC_COST_TC
, NVL(PPR.OTH_ETC_COST_PC
, 0) + NVL(PPR.PPL_ETC_COST_PC
, 0) + NVL(PPR.EQPMT_ETC_COST_PC
, 0) TOTAL_ETC_COST_PC
, NVL(PPR.OTH_ETC_COST_FC
, 0) + NVL(PPR.PPL_ETC_COST_FC
, 0) + NVL(PPR.EQPMT_ETC_COST_FC
, 0) TOTAL_ETC_COST_FC
, PPR.EARNED_VALUE
, PPR.CURRENT_FLAG
, PPR.TXN_CURRENCY_CODE
, PPR.PROG_PA_PERIOD_NAME
, PPR.PROG_GL_PERIOD_NAME
, PPEV.PARENT_STRUCTURE_VERSION_ID
, NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_PC
, 0) + NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_PC
, 0) + NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_PC
, 0) TOTAL_ACT_RAWCOST_TO_DATE_PC
, NVL(PPR.OTH_ETC_RAWCOST_PC
, 0) + NVL(PPR.PPL_ETC_RAWCOST_PC
, 0) + NVL(PPR.EQPMT_ETC_RAWCOST_PC
, 0) TOTAL_ETC_RAWCOST_PC
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEMENT_VERSIONS PPEV2
, 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_OBJECT_RELATIONSHIPS POR
, PA_PERCENT_COMPLETES PPC
, PA_PROGRESS_ROLLUP PPR
, PA_PROJECT_STATUSES PPS
, PA_PROJECT_STATUSES PPS1
, PA_PROJECT_STATUSES PPS2
, PA_PROJECT_STATUSES PPS3
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 PPA.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 PPVS.PROJECT_ID = PPPA.PROJECT_ID(+)
AND PPVS.PROJ_ELEMENT_ID = PPPA.OBJECT_ID(+)
AND PPPA.STRUCTURE_TYPE = 'WORKPLAN'
AND NVL(PPE.STATUS_CODE
, 'X') = PPS3.PROJECT_STATUS_CODE(+)
AND PPEV.ELEMENT_VERSION_ID = POR.OBJECT_ID_TO1(+)
AND POR.OBJECT_TYPE_TO(+) = 'PA_TASKS'
AND POR.RELATIONSHIP_TYPE (+) = 'S'
AND POR.OBJECT_ID_FROM1 = PPEV2.ELEMENT_VERSION_ID(+)
AND PPE.PROJECT_ID = PPC.PROJECT_ID
AND PPE.PROJ_ELEMENT_ID = PPC.OBJECT_ID
AND PPE.OBJECT_TYPE = PPC.OBJECT_TYPE
AND PPC.PROJECT_ID = PPR.PROJECT_ID(+)
AND PPC.OBJECT_ID = PPR.OBJECT_ID(+)
AND PPC.OBJECT_TYPE = PPR.OBJECT_TYPE(+)
AND PPR.CURRENT_FLAG(+) <> 'W'
AND PPC.PUBLISHED_FLAG = 'Y'
AND TRUNC(PPC.DATE_COMPUTED) = TRUNC(PPR.AS_OF_DATE)
AND PPS.PROJECT_STATUS_CODE (+)= PPC.PROGRESS_STATUS_CODE
AND PPS1.PROJECT_STATUS_CODE (+)= PPR.BASE_PROGRESS_STATUS_CODE
AND PPS2.PROJECT_STATUS_CODE (+)= PPR.EFF_ROLLUP_PROG_STAT_CODE
AND PPC.STRUCTURE_TYPE = 'WORKPLAN'
AND PPR.STRUCTURE_TYPE(+) = PPC.STRUCTURE_TYPE
AND PPR.STRUCTURE_VERSION_ID(+) IS NULL