VIEW: APPS.PA_DELIVERABLE_PROGRESS_V

Source

View Text - Preformatted

SELECT ppa.project_id, ppa.name, ppa.segment1, ppa.long_name, to_char(null) structure_version_name, to_char(null) structure_version_number, ppe.name task_name, ppe.element_number task_number, ppe.proj_element_id str_task_proj_element_id, ppe.object_type str_task_object_type, delppe.object_type del_object_type, ppvsch.element_version_id del_element_version_id, delppe.proj_element_id del_id, delppe.name del_name, delppe.element_number del_number, delppe.manager_person_id del_manager_id, papf.full_name del_manager_name, nvl(ppc.status_code,delppe.status_code), pps3.project_status_name del_status_name, pps3.project_system_status_code del_system_status_code, ppr.as_of_date, ppc.percent_complete_id, ppr.completed_percentage, pps.project_status_code progress_status_code, pps.project_status_name progress_status_name, pps.PROJECT_SYSTEM_STATUS_CODE progress_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.base_percent_complete, ppr.eff_rollup_percent_comp, ppc.published_by_party_id, ppc.progress_comment, ppc.description, ppc.last_update_date, ppc.published_flag, ppc.current_flag, ppc.record_version_number percomp_record_version_number, ppr.record_version_number rollup_record_version_number, ppr.BASE_PROGRESS_STATUS_CODE, pps1.project_status_name base_progress_status_name, delppe.last_update_date del_last_update_date, ppvsch.last_update_date del_sch_last_update_date, pppa.percent_comp_enable_flag, ppr.EFF_ROLLUP_PROG_STAT_CODE, pps2.project_status_name eff_rollup_status_name, pps2.status_icon_ind eff_rollup_status_icon_ind, delppe.PROGRESS_OUTDATED_FLAG, 'N' prog_entry_req_flag, ppr.STRUCTURE_TYPE, ppr.PROJ_ELEMENT_ID, ppr.STRUCTURE_VERSION_ID, ppr.TASK_WT_BASIS_CODE, pa_progress_utils.GET_NEXT_PROGRESS_CYCLE(delppe.project_id, null, delppe.proj_element_id, delppe.object_type), pa_progress_utils.GET_PROG_DT_CLOSEST_TO_SYS_DT(delppe.project_id, null, delppe.proj_element_id, delppe.object_type), pppa.allow_collab_prog_entry, decode(ppc.published_flag, 'Y', to_number(null), ppr.completed_percentage ) this_period_percentage, decode(ppc.published_flag, 'Y',ppr.completed_percentage, PA_PROGRESS_UTILS.get_last_published_perc_comp (ppr.project_id, ppr.object_id, ppr.as_of_date,'PA_DELIVERABLES')) last_submitted_percentage, ppr.progress_rollup_id, ppa.org_id, null, delppev.display_sequence From pa_projects_all ppa, pa_proj_elements delppe, pa_proj_element_versions delppev, pa_proj_elem_ver_schedule ppvsch, pa_task_types delptt, pa_percent_completes ppc, pa_progress_rollup ppr, pa_project_statuses pps, pa_project_statuses pps1, pa_project_statuses pps2, pa_project_statuses pps3, per_all_people_f papf, pa_proj_elements ppe, pa_object_relationships por, pa_proj_progress_attr pppa WHERE ppa.project_id = delppe.project_id and delppe.object_type = 'PA_DELIVERABLES' and delppe.proj_element_id = por.object_id_to2(+) and delppe.type_id = delptt.task_type_id and delptt.object_type = 'PA_DLVR_TYPES' and delptt.PROG_ENTRY_ENABLE_FLAG = 'Y' and nvl(ppc.status_code,delppe.status_code) = pps3.project_status_code and ppa.project_id = ppvsch.project_id and ppvsch.proj_element_id = delppe.proj_element_id and delppe.project_id = delppev.project_id and delppe.proj_element_id = delppev.proj_element_id and delppe.object_type = delppev.object_type and ppe.proj_element_id(+) = por.object_id_from2 AND ppe.object_type(+) ='PA_TASKS' and por.object_type_to(+) = 'PA_DELIVERABLES' and por.relationship_type(+) = 'A' and por.relationship_subtype(+) = 'TASK_TO_DELIVERABLE' and delppe.project_id = ppr.project_id(+) and delppe.proj_element_id = ppr.object_id(+) and ppr.object_type(+) = 'PA_DELIVERABLES' and ppr.percent_complete_id = ppc.percent_complete_id(+) and ppr.project_id = ppc.project_id(+) and ppr.object_id = ppc.object_id(+) and ppr.object_type = ppc.object_type(+) and trunc(ppr.as_of_date) = trunc(ppc.date_computed(+)) and pps.project_status_code (+)= ppr.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 ppr.structure_type(+) = 'WORKPLAN' and ppc.structure_type(+) = 'WORKPLAN' and ppr.structure_version_id(+) is null and papf.person_id(+) = delppe.manager_person_id and trunc(sysdate) between papf.effective_start_date(+) and papf.effective_end_date (+) and ppa.project_id = pppa.project_id and pppa.structure_type = 'WORKPLAN'

View Text - HTML Formatted

SELECT PPA.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PPA.LONG_NAME
, TO_CHAR(NULL) STRUCTURE_VERSION_NAME
, TO_CHAR(NULL) STRUCTURE_VERSION_NUMBER
, PPE.NAME TASK_NAME
, PPE.ELEMENT_NUMBER TASK_NUMBER
, PPE.PROJ_ELEMENT_ID STR_TASK_PROJ_ELEMENT_ID
, PPE.OBJECT_TYPE STR_TASK_OBJECT_TYPE
, DELPPE.OBJECT_TYPE DEL_OBJECT_TYPE
, PPVSCH.ELEMENT_VERSION_ID DEL_ELEMENT_VERSION_ID
, DELPPE.PROJ_ELEMENT_ID DEL_ID
, DELPPE.NAME DEL_NAME
, DELPPE.ELEMENT_NUMBER DEL_NUMBER
, DELPPE.MANAGER_PERSON_ID DEL_MANAGER_ID
, PAPF.FULL_NAME DEL_MANAGER_NAME
, NVL(PPC.STATUS_CODE
, DELPPE.STATUS_CODE)
, PPS3.PROJECT_STATUS_NAME DEL_STATUS_NAME
, PPS3.PROJECT_SYSTEM_STATUS_CODE DEL_SYSTEM_STATUS_CODE
, PPR.AS_OF_DATE
, PPC.PERCENT_COMPLETE_ID
, PPR.COMPLETED_PERCENTAGE
, PPS.PROJECT_STATUS_CODE PROGRESS_STATUS_CODE
, PPS.PROJECT_STATUS_NAME PROGRESS_STATUS_NAME
, PPS.PROJECT_SYSTEM_STATUS_CODE PROGRESS_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.BASE_PERCENT_COMPLETE
, PPR.EFF_ROLLUP_PERCENT_COMP
, PPC.PUBLISHED_BY_PARTY_ID
, PPC.PROGRESS_COMMENT
, PPC.DESCRIPTION
, PPC.LAST_UPDATE_DATE
, PPC.PUBLISHED_FLAG
, PPC.CURRENT_FLAG
, PPC.RECORD_VERSION_NUMBER PERCOMP_RECORD_VERSION_NUMBER
, PPR.RECORD_VERSION_NUMBER ROLLUP_RECORD_VERSION_NUMBER
, PPR.BASE_PROGRESS_STATUS_CODE
, PPS1.PROJECT_STATUS_NAME BASE_PROGRESS_STATUS_NAME
, DELPPE.LAST_UPDATE_DATE DEL_LAST_UPDATE_DATE
, PPVSCH.LAST_UPDATE_DATE DEL_SCH_LAST_UPDATE_DATE
, PPPA.PERCENT_COMP_ENABLE_FLAG
, PPR.EFF_ROLLUP_PROG_STAT_CODE
, PPS2.PROJECT_STATUS_NAME EFF_ROLLUP_STATUS_NAME
, PPS2.STATUS_ICON_IND EFF_ROLLUP_STATUS_ICON_IND
, DELPPE.PROGRESS_OUTDATED_FLAG
, 'N' PROG_ENTRY_REQ_FLAG
, PPR.STRUCTURE_TYPE
, PPR.PROJ_ELEMENT_ID
, PPR.STRUCTURE_VERSION_ID
, PPR.TASK_WT_BASIS_CODE
, PA_PROGRESS_UTILS.GET_NEXT_PROGRESS_CYCLE(DELPPE.PROJECT_ID
, NULL
, DELPPE.PROJ_ELEMENT_ID
, DELPPE.OBJECT_TYPE)
, PA_PROGRESS_UTILS.GET_PROG_DT_CLOSEST_TO_SYS_DT(DELPPE.PROJECT_ID
, NULL
, DELPPE.PROJ_ELEMENT_ID
, DELPPE.OBJECT_TYPE)
, PPPA.ALLOW_COLLAB_PROG_ENTRY
, DECODE(PPC.PUBLISHED_FLAG
, 'Y'
, TO_NUMBER(NULL)
, PPR.COMPLETED_PERCENTAGE ) THIS_PERIOD_PERCENTAGE
, DECODE(PPC.PUBLISHED_FLAG
, 'Y'
, PPR.COMPLETED_PERCENTAGE
, PA_PROGRESS_UTILS.GET_LAST_PUBLISHED_PERC_COMP (PPR.PROJECT_ID
, PPR.OBJECT_ID
, PPR.AS_OF_DATE
, 'PA_DELIVERABLES')) LAST_SUBMITTED_PERCENTAGE
, PPR.PROGRESS_ROLLUP_ID
, PPA.ORG_ID
, NULL
, DELPPEV.DISPLAY_SEQUENCE
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS DELPPE
, PA_PROJ_ELEMENT_VERSIONS DELPPEV
, PA_PROJ_ELEM_VER_SCHEDULE PPVSCH
, PA_TASK_TYPES DELPTT
, PA_PERCENT_COMPLETES PPC
, PA_PROGRESS_ROLLUP PPR
, PA_PROJECT_STATUSES PPS
, PA_PROJECT_STATUSES PPS1
, PA_PROJECT_STATUSES PPS2
, PA_PROJECT_STATUSES PPS3
, PER_ALL_PEOPLE_F PAPF
, PA_PROJ_ELEMENTS PPE
, PA_OBJECT_RELATIONSHIPS POR
, PA_PROJ_PROGRESS_ATTR PPPA
WHERE PPA.PROJECT_ID = DELPPE.PROJECT_ID
AND DELPPE.OBJECT_TYPE = 'PA_DELIVERABLES'
AND DELPPE.PROJ_ELEMENT_ID = POR.OBJECT_ID_TO2(+)
AND DELPPE.TYPE_ID = DELPTT.TASK_TYPE_ID
AND DELPTT.OBJECT_TYPE = 'PA_DLVR_TYPES'
AND DELPTT.PROG_ENTRY_ENABLE_FLAG = 'Y'
AND NVL(PPC.STATUS_CODE
, DELPPE.STATUS_CODE) = PPS3.PROJECT_STATUS_CODE
AND PPA.PROJECT_ID = PPVSCH.PROJECT_ID
AND PPVSCH.PROJ_ELEMENT_ID = DELPPE.PROJ_ELEMENT_ID
AND DELPPE.PROJECT_ID = DELPPEV.PROJECT_ID
AND DELPPE.PROJ_ELEMENT_ID = DELPPEV.PROJ_ELEMENT_ID
AND DELPPE.OBJECT_TYPE = DELPPEV.OBJECT_TYPE
AND PPE.PROJ_ELEMENT_ID(+) = POR.OBJECT_ID_FROM2
AND PPE.OBJECT_TYPE(+) ='PA_TASKS'
AND POR.OBJECT_TYPE_TO(+) = 'PA_DELIVERABLES'
AND POR.RELATIONSHIP_TYPE(+) = 'A'
AND POR.RELATIONSHIP_SUBTYPE(+) = 'TASK_TO_DELIVERABLE'
AND DELPPE.PROJECT_ID = PPR.PROJECT_ID(+)
AND DELPPE.PROJ_ELEMENT_ID = PPR.OBJECT_ID(+)
AND PPR.OBJECT_TYPE(+) = 'PA_DELIVERABLES'
AND PPR.PERCENT_COMPLETE_ID = PPC.PERCENT_COMPLETE_ID(+)
AND PPR.PROJECT_ID = PPC.PROJECT_ID(+)
AND PPR.OBJECT_ID = PPC.OBJECT_ID(+)
AND PPR.OBJECT_TYPE = PPC.OBJECT_TYPE(+)
AND TRUNC(PPR.AS_OF_DATE) = TRUNC(PPC.DATE_COMPUTED(+))
AND PPS.PROJECT_STATUS_CODE (+)= PPR.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 PPR.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PPC.STRUCTURE_TYPE(+) = 'WORKPLAN'
AND PPR.STRUCTURE_VERSION_ID(+) IS NULL
AND PAPF.PERSON_ID(+) = DELPPE.MANAGER_PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE (+)
AND PPA.PROJECT_ID = PPPA.PROJECT_ID
AND PPPA.STRUCTURE_TYPE = 'WORKPLAN'