VIEW: APPS.PA_FIN_LATEST_PUB_TASKS_V

Source

View Text - Preformatted

SELECT ppe.name, ppe.element_number, ppe.proj_element_id, ppevs.project_id, ppst.structure_type_id, pst.structure_type_class_code, pl.meaning FROM pa_proj_elements ppe, pa_proj_element_versions ppev, pa_proj_elem_ver_structure ppevs, pa_proj_structure_types ppst, pa_structure_types pst, pa_lookups pl, pa_project_statuses pps WHERE ppevs.latest_eff_published_flag = 'Y' AND ppevs.proj_element_id = ppst.proj_element_id AND ppst.proj_element_id in ( select proj_element_id from pa_proj_structure_types group by proj_element_id having count(proj_element_id) = 2) AND ppst.structure_type_id = pst.structure_type_id AND pst.structure_type_class_code = 'WORKPLAN' AND ppev.parent_structure_version_id = ppevs.element_version_id AND ppev.project_id = ppevs.project_id AND ppev.proj_element_id = ppe.proj_element_id AND ppev.project_id = ppe.project_id AND ppe.object_type = 'PA_TASKS' AND pl.lookup_type = 'PA_STRUCTURE_TYPE_CLASS' AND pl.lookup_code = pst.structure_type AND ppe.STATUS_CODE = pps.PROJECT_STATUS_CODE AND pps.PROJECT_SYSTEM_STATUS_CODE <> 'CANCELLED' AND ppe.link_task_flag <> 'Y' UNION SELECT ppe.name, ppe.element_number, ppe.proj_element_id, ppevs.project_id, ppst.structure_type_id, pst.structure_type_class_code, pl.meaning FROM pa_proj_elements ppe, pa_proj_element_versions ppev, pa_proj_elem_ver_structure ppevs, pa_proj_structure_types ppst, pa_structure_types pst, pa_lookups pl, pa_project_statuses pps WHERE ppevs.latest_eff_published_flag = 'Y' AND ppevs.proj_element_id = ppst.proj_element_id AND ppst.proj_element_id in ( select proj_element_id from pa_proj_structure_types group by proj_element_id having count(proj_element_id) = 1) AND ppst.structure_type_id = pst.structure_type_id AND ppev.parent_structure_version_id = ppevs.element_version_id AND ppev.project_id = ppevs.project_id AND ppev.proj_element_id = ppe.proj_element_id AND ppev.project_id = ppe.project_id AND ppe.object_type = 'PA_TASKS' AND pl.lookup_type = 'PA_STRUCTURE_TYPE_CLASS' AND pl.lookup_code = pst.structure_type AND ppe.STATUS_CODE = pps.PROJECT_STATUS_CODE AND pps.PROJECT_SYSTEM_STATUS_CODE <> 'CANCELLED' AND ppe.link_task_flag <> 'Y'

View Text - HTML Formatted

SELECT PPE.NAME
, PPE.ELEMENT_NUMBER
, PPE.PROJ_ELEMENT_ID
, PPEVS.PROJECT_ID
, PPST.STRUCTURE_TYPE_ID
, PST.STRUCTURE_TYPE_CLASS_CODE
, PL.MEANING
FROM PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_STRUCTURE PPEVS
, PA_PROJ_STRUCTURE_TYPES PPST
, PA_STRUCTURE_TYPES PST
, PA_LOOKUPS PL
, PA_PROJECT_STATUSES PPS
WHERE PPEVS.LATEST_EFF_PUBLISHED_FLAG = 'Y'
AND PPEVS.PROJ_ELEMENT_ID = PPST.PROJ_ELEMENT_ID
AND PPST.PROJ_ELEMENT_ID IN ( SELECT PROJ_ELEMENT_ID
FROM PA_PROJ_STRUCTURE_TYPES GROUP BY PROJ_ELEMENT_ID HAVING COUNT(PROJ_ELEMENT_ID) = 2)
AND PPST.STRUCTURE_TYPE_ID = PST.STRUCTURE_TYPE_ID
AND PST.STRUCTURE_TYPE_CLASS_CODE = 'WORKPLAN'
AND PPEV.PARENT_STRUCTURE_VERSION_ID = PPEVS.ELEMENT_VERSION_ID
AND PPEV.PROJECT_ID = PPEVS.PROJECT_ID
AND PPEV.PROJ_ELEMENT_ID = PPE.PROJ_ELEMENT_ID
AND PPEV.PROJECT_ID = PPE.PROJECT_ID
AND PPE.OBJECT_TYPE = 'PA_TASKS'
AND PL.LOOKUP_TYPE = 'PA_STRUCTURE_TYPE_CLASS'
AND PL.LOOKUP_CODE = PST.STRUCTURE_TYPE
AND PPE.STATUS_CODE = PPS.PROJECT_STATUS_CODE
AND PPS.PROJECT_SYSTEM_STATUS_CODE <> 'CANCELLED'
AND PPE.LINK_TASK_FLAG <> 'Y' UNION SELECT PPE.NAME
, PPE.ELEMENT_NUMBER
, PPE.PROJ_ELEMENT_ID
, PPEVS.PROJECT_ID
, PPST.STRUCTURE_TYPE_ID
, PST.STRUCTURE_TYPE_CLASS_CODE
, PL.MEANING
FROM PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_STRUCTURE PPEVS
, PA_PROJ_STRUCTURE_TYPES PPST
, PA_STRUCTURE_TYPES PST
, PA_LOOKUPS PL
, PA_PROJECT_STATUSES PPS
WHERE PPEVS.LATEST_EFF_PUBLISHED_FLAG = 'Y'
AND PPEVS.PROJ_ELEMENT_ID = PPST.PROJ_ELEMENT_ID
AND PPST.PROJ_ELEMENT_ID IN ( SELECT PROJ_ELEMENT_ID
FROM PA_PROJ_STRUCTURE_TYPES GROUP BY PROJ_ELEMENT_ID HAVING COUNT(PROJ_ELEMENT_ID) = 1)
AND PPST.STRUCTURE_TYPE_ID = PST.STRUCTURE_TYPE_ID
AND PPEV.PARENT_STRUCTURE_VERSION_ID = PPEVS.ELEMENT_VERSION_ID
AND PPEV.PROJECT_ID = PPEVS.PROJECT_ID
AND PPEV.PROJ_ELEMENT_ID = PPE.PROJ_ELEMENT_ID
AND PPEV.PROJECT_ID = PPE.PROJECT_ID
AND PPE.OBJECT_TYPE = 'PA_TASKS'
AND PL.LOOKUP_TYPE = 'PA_STRUCTURE_TYPE_CLASS'
AND PL.LOOKUP_CODE = PST.STRUCTURE_TYPE
AND PPE.STATUS_CODE = PPS.PROJECT_STATUS_CODE
AND PPS.PROJECT_SYSTEM_STATUS_CODE <> 'CANCELLED'
AND PPE.LINK_TASK_FLAG <> 'Y'