VIEW: APPS.PA_STRUCT_TASK_LOV_V

Source

View Text - Preformatted

SELECT ppev.element_version_id, ppe.name, ppe.element_number, ppe.project_id, -1, pst.structure_type FROM pa_proj_elements ppe, pa_proj_element_versions ppev, pa_proj_elem_ver_structure str, pa_proj_elements ppe2, pa_proj_structure_types ppst, pa_structure_types pst WHERE ppe.project_id = ppev.project_id and ppev.object_type = 'PA_TASKS' and ppe.proj_element_id = ppev.proj_element_id and ppe.link_task_flag <> 'Y' and ppev.parent_structure_version_id = str.element_version_id and ppev.project_id = str.project_id and str.CURRENT_WORKING_FLAG = 'Y' and ppe2.project_id = str.project_id and ppe2.proj_element_id = str.proj_element_id and ppe2.proj_element_id = ppst.proj_element_id and ppst.structure_type_id = pst.structure_type_id and not exists ( select 1 from pa_proj_elem_ver_structure str2 where str2.project_id = str.project_id and str2.proj_element_id = str.proj_element_id and str2.status_code = 'STRUCTURE_PUBLISHED' ) UNION select ppev.element_version_id, ppe.name, ppe.element_number, ppe.project_id, -1, pst.structure_type FROM pa_proj_elements ppe, pa_proj_element_versions ppev, pa_proj_elem_ver_structure str, pa_proj_elements ppe2, pa_proj_structure_types ppst, pa_structure_types pst WHERE ppe.project_id = ppev.project_id and ppev.object_type = 'PA_TASKS' and ppe.proj_element_id = ppev.proj_element_id and ppe.link_task_flag <> 'Y' and ppev.parent_structure_version_id = str.element_version_id and ppev.project_id = str.project_id and str.status_code = 'STRUCTURE_PUBLISHED' and str.latest_eff_published_flag = 'Y' and ppe2.project_id = str.project_id and ppe2.proj_element_id = str.proj_element_id and ppe2.proj_element_id = ppst.proj_element_id and ppst.structure_type_id = pst.structure_type_id UNION select ppev.element_version_id, ppe.name, ppe.element_number, ppe.project_id, ppev.parent_structure_version_id, pst.structure_type FROM pa_proj_elements ppe, pa_proj_element_versions ppev, pa_proj_elements ppe2, pa_proj_element_versions ppev2, pa_proj_structure_types ppst, pa_structure_types pst WHERE ppe.project_id = ppev.project_id and ppev.object_type = 'PA_TASKS' and ppe.proj_element_id = ppev.proj_element_id and ppe.link_task_flag <> 'Y' and ppev.parent_structure_version_id = ppev2.element_version_id and ppev2.project_id = ppe2.project_id and ppev2.proj_element_id = ppe2.proj_element_id and ppe2.proj_element_id = ppst.proj_element_id and ppst.structure_type_id = pst.structure_type_id

View Text - HTML Formatted

SELECT PPEV.ELEMENT_VERSION_ID
, PPE.NAME
, PPE.ELEMENT_NUMBER
, PPE.PROJECT_ID
, -1
, PST.STRUCTURE_TYPE
FROM PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_STRUCTURE STR
, PA_PROJ_ELEMENTS PPE2
, PA_PROJ_STRUCTURE_TYPES PPST
, PA_STRUCTURE_TYPES PST
WHERE PPE.PROJECT_ID = PPEV.PROJECT_ID
AND PPEV.OBJECT_TYPE = 'PA_TASKS'
AND PPE.PROJ_ELEMENT_ID = PPEV.PROJ_ELEMENT_ID
AND PPE.LINK_TASK_FLAG <> 'Y'
AND PPEV.PARENT_STRUCTURE_VERSION_ID = STR.ELEMENT_VERSION_ID
AND PPEV.PROJECT_ID = STR.PROJECT_ID
AND STR.CURRENT_WORKING_FLAG = 'Y'
AND PPE2.PROJECT_ID = STR.PROJECT_ID
AND PPE2.PROJ_ELEMENT_ID = STR.PROJ_ELEMENT_ID
AND PPE2.PROJ_ELEMENT_ID = PPST.PROJ_ELEMENT_ID
AND PPST.STRUCTURE_TYPE_ID = PST.STRUCTURE_TYPE_ID
AND NOT EXISTS ( SELECT 1
FROM PA_PROJ_ELEM_VER_STRUCTURE STR2
WHERE STR2.PROJECT_ID = STR.PROJECT_ID
AND STR2.PROJ_ELEMENT_ID = STR.PROJ_ELEMENT_ID
AND STR2.STATUS_CODE = 'STRUCTURE_PUBLISHED' ) UNION SELECT PPEV.ELEMENT_VERSION_ID
, PPE.NAME
, PPE.ELEMENT_NUMBER
, PPE.PROJECT_ID
, -1
, PST.STRUCTURE_TYPE
FROM PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_STRUCTURE STR
, PA_PROJ_ELEMENTS PPE2
, PA_PROJ_STRUCTURE_TYPES PPST
, PA_STRUCTURE_TYPES PST
WHERE PPE.PROJECT_ID = PPEV.PROJECT_ID
AND PPEV.OBJECT_TYPE = 'PA_TASKS'
AND PPE.PROJ_ELEMENT_ID = PPEV.PROJ_ELEMENT_ID
AND PPE.LINK_TASK_FLAG <> 'Y'
AND PPEV.PARENT_STRUCTURE_VERSION_ID = STR.ELEMENT_VERSION_ID
AND PPEV.PROJECT_ID = STR.PROJECT_ID
AND STR.STATUS_CODE = 'STRUCTURE_PUBLISHED'
AND STR.LATEST_EFF_PUBLISHED_FLAG = 'Y'
AND PPE2.PROJECT_ID = STR.PROJECT_ID
AND PPE2.PROJ_ELEMENT_ID = STR.PROJ_ELEMENT_ID
AND PPE2.PROJ_ELEMENT_ID = PPST.PROJ_ELEMENT_ID
AND PPST.STRUCTURE_TYPE_ID = PST.STRUCTURE_TYPE_ID UNION SELECT PPEV.ELEMENT_VERSION_ID
, PPE.NAME
, PPE.ELEMENT_NUMBER
, PPE.PROJECT_ID
, PPEV.PARENT_STRUCTURE_VERSION_ID
, PST.STRUCTURE_TYPE
FROM PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEMENTS PPE2
, PA_PROJ_ELEMENT_VERSIONS PPEV2
, PA_PROJ_STRUCTURE_TYPES PPST
, PA_STRUCTURE_TYPES PST
WHERE PPE.PROJECT_ID = PPEV.PROJECT_ID
AND PPEV.OBJECT_TYPE = 'PA_TASKS'
AND PPE.PROJ_ELEMENT_ID = PPEV.PROJ_ELEMENT_ID
AND PPE.LINK_TASK_FLAG <> 'Y'
AND PPEV.PARENT_STRUCTURE_VERSION_ID = PPEV2.ELEMENT_VERSION_ID
AND PPEV2.PROJECT_ID = PPE2.PROJECT_ID
AND PPEV2.PROJ_ELEMENT_ID = PPE2.PROJ_ELEMENT_ID
AND PPE2.PROJ_ELEMENT_ID = PPST.PROJ_ELEMENT_ID
AND PPST.STRUCTURE_TYPE_ID = PST.STRUCTURE_TYPE_ID