VIEW: APPS.PA_DEPENDENCIES_V

Source

View Text - Preformatted

SELECT PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DEPEND_DISP_TYPE','PRE'), ppa.project_id, ppa.segment1, ppa.name, ppe.element_number, ppe.name, ppe.proj_element_id, pev.element_version_id, ppe2.proj_element_id, pev3.element_version_id, ppe2.project_id, rel.comments, rel.lag_day, sch.scheduled_start_date, sch.scheduled_finish_date, sch.actual_start_date, sch.actual_finish_date, ppe.manager_person_id, papf.full_name, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_OBJECT_DEPENDENCY_TYPES', rel.relationship_subtype), rel.relationship_subtype, 'PRE', rel.object_relationship_id, rel.record_version_number, pev.parent_structure_version_id FROM pa_projects_all ppa, pa_proj_elements ppe, pa_proj_element_versions pev, pa_object_relationships rel, pa_proj_elements ppe2, pa_proj_element_versions pev3, pa_proj_elem_ver_schedule sch, per_all_people_f papf WHERE nvl(rel.status_code,'PUBLISHED') = 'PUBLISHED' and rel.relationship_type = 'D' and rel.object_id_to1 = pev.element_version_id and rel.object_type_to = 'PA_TASKS' and rel.object_id_from1 = pev3.element_version_id and rel.object_type_from = 'PA_TASKS' and pev3.proj_element_id = ppe2.proj_element_id and pev3.project_id = ppe2.project_id and pev.proj_element_id = ppe.proj_element_id and pev.project_id = ppe.project_id and sch.project_id = pev.project_id and sch.element_version_id = pev.element_version_id and pev.project_id = ppa.project_id and ppe.manager_person_id = papf.person_id(+) and SYSDATE between papf.effective_start_date(+) AND papf.effective_end_date (+) and pev.element_version_id IN ( select max(pev2.element_version_id) from pa_proj_element_versions pev2 where pev2.project_id = pev.project_id and pev2.proJ_element_id = pev.proj_element_id) UNION SELECT PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DEPEND_DISP_TYPE','PRE'), ppa.project_id, ppa.segment1, ppa.name, ppe.element_number, ppe.name, ppe.proj_element_id, pev.element_version_id, ppe2.proj_element_id, pev3.element_version_id, ppe2.project_id, rel.comments, rel.lag_day, sch.scheduled_start_date, sch.scheduled_finish_date, sch.actual_start_date, sch.actual_finish_date, ppe.manager_person_id, papf.full_name, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_OBJECT_DEPENDENCY_TYPES', rel.relationship_subtype), rel.relationship_subtype, 'PRE', rel.object_relationship_id, rel.record_version_number, pev.parent_structure_version_id FROM pa_projects_all ppa, pa_proj_elements ppe, pa_proj_element_versions pev, pa_object_relationships rel, pa_proj_elements ppe2, pa_proj_element_versions pev3, pa_proj_elem_ver_schedule sch, per_all_people_f papf WHERE nvl(rel.status_code,'INTRA') = 'INTRA' and rel.relationship_type = 'D' and rel.object_id_to1 = pev.element_version_id and rel.object_type_to = 'PA_TASKS' and rel.object_id_from1 = pev3.element_version_id and rel.object_type_from = 'PA_TASKS' and pev3.proj_element_id = ppe2.proj_element_id and pev3.project_id = ppe2.project_id and pev.proj_element_id = ppe.proj_element_id and pev.project_id = ppe.project_id and sch.project_id = pev.project_id and sch.element_version_id = pev.element_version_id and pev.project_id = ppa.project_id and ppe.manager_person_id = papf.person_id(+) and SYSDATE between papf.effective_start_date(+) AND papf.effective_end_date (+) UNION SELECT PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DEPEND_DISP_TYPE','PRE'), ppa.project_id, ppa.segment1, ppa.name, ppe.element_number, ppe.name, ppe.proj_element_id, pev.element_version_id, ppe2.proj_element_id, pev3.element_version_id, ppe2.project_id, rel.comments, rel.lag_day, sch.scheduled_start_date, sch.scheduled_finish_date, sch.actual_start_date, sch.actual_finish_date, ppe.manager_person_id, papf.full_name, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_OBJECT_DEPENDENCY_TYPES', rel.relationship_subtype), rel.relationship_subtype, 'PRE', rel.object_relationship_id, rel.record_version_number, pev.parent_structure_version_id FROM pa_projects_all ppa, pa_proj_elements ppe, pa_proj_element_versions pev, pa_object_relationships rel, pa_proj_elements ppe2, pa_proj_element_versions pev3, pa_proj_elem_ver_schedule sch, per_all_people_f papf WHERE rel.status_code = 'UNPUBLISHED' and rel.relationship_type = 'D' and rel.object_id_to1 = pev.element_version_id and rel.object_type_to = 'PA_TASKS' and rel.object_id_from1 = pev3.element_version_id and rel.object_type_from = 'PA_TASKS' and pev3.proj_element_id = ppe2.proj_element_id and pev3.project_id = ppe2.project_id and pev.proj_element_id = ppe.proj_element_id and pev.project_id = ppe.project_id and sch.project_id = pev.project_id and sch.element_version_id = pev.element_version_id and pev.project_id = ppa.project_id and ppe.manager_person_id = papf.person_id(+) and SYSDATE between papf.effective_start_date(+) AND papf.effective_end_date (+) and ppe.proj_element_id NOT IN ( select pev2.proj_element_id from pa_proj_element_versions pev2, pa_object_relationships rel2 where pev2.project_id = pev.project_id and pev2.proJ_element_id = pev.proj_element_id and pev2.element_version_id = rel2.object_id_to1 and rel2.relationship_type = 'D' and rel2.status_code = 'PUBLISHED' ) UNION SELECT PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DEPEND_DISP_TYPE','SUC'), ppa.project_id, ppa.segment1, ppa.name, ppe.element_number, ppe.name, ppe.proj_element_id, pev.element_version_id, ppe2.proj_element_id, pev3.element_version_id, ppe2.project_id, rel.comments, rel.lag_day, sch.scheduled_start_date, sch.scheduled_finish_date, sch.actual_start_date, sch.actual_finish_date, ppe.manager_person_id, papf.full_name, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_OBJECT_DEPENDENCY_TYPES', rel.relationship_subtype), rel.relationship_subtype, 'SUC', rel.object_relationship_id, rel.record_version_number, pev.parent_structure_version_id FROM pa_projects_all ppa, pa_proj_elements ppe, pa_proj_element_versions pev, pa_object_relationships rel, pa_proj_elements ppe2, pa_proj_element_versions pev3, pa_proj_elem_ver_schedule sch, per_all_people_f papf WHERE nvl(rel.status_code,'PUBLISHED') = 'PUBLISHED' and rel.relationship_type = 'D' and rel.object_id_from1 = pev.element_version_id and rel.object_type_from = 'PA_TASKS' and rel.object_id_to1 = pev3.element_version_id and rel.object_type_to = 'PA_TASKS' and pev3.proj_element_id = ppe2.proj_element_id and pev3.project_id = ppe2.project_id and pev.proj_element_id = ppe.proj_element_id and pev.project_id = ppe.project_id and sch.project_id = pev.project_id and sch.element_version_id = pev.element_version_id and ppe.manager_person_id = papf.person_id(+) and SYSDATE between papf.effective_start_date(+) AND papf.effective_end_date (+) and pev.project_id = ppa.project_id and pev.element_version_id IN ( select max(pev2.element_version_id) from pa_proj_element_versions pev2 where pev2.project_id = pev.project_id and pev2.proJ_element_id = pev.proj_element_id) UNION SELECT PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DEPEND_DISP_TYPE','SUC'), ppa.project_id, ppa.segment1, ppa.name, ppe.element_number, ppe.name, ppe.proj_element_id, pev.element_version_id, ppe2.proj_element_id, pev3.element_version_id, ppe2.project_id, rel.comments, rel.lag_day, sch.scheduled_start_date, sch.scheduled_finish_date, sch.actual_start_date, sch.actual_finish_date, ppe.manager_person_id, papf.full_name, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_OBJECT_DEPENDENCY_TYPES', rel.relationship_subtype), rel.relationship_subtype, 'SUC', rel.object_relationship_id, rel.record_version_number, pev.parent_structure_version_id FROM pa_projects_all ppa, pa_proj_elements ppe, pa_proj_element_versions pev, pa_object_relationships rel, pa_proj_elements ppe2, pa_proj_element_versions pev3, pa_proj_elem_ver_schedule sch, per_all_people_f papf WHERE nvl(rel.status_code,'INTRA') = 'INTRA' and rel.relationship_type = 'D' and rel.object_id_from1 = pev.element_version_id and rel.object_type_from = 'PA_TASKS' and rel.object_id_to1 = pev3.element_version_id and rel.object_type_to = 'PA_TASKS' and pev3.proj_element_id = ppe2.proj_element_id and pev3.project_id = ppe2.project_id and pev.proj_element_id = ppe.proj_element_id and pev.project_id = ppe.project_id and sch.project_id = pev.project_id and sch.element_version_id = pev.element_version_id and ppe.manager_person_id = papf.person_id(+) and SYSDATE between papf.effective_start_date(+) AND papf.effective_end_date (+) and pev.project_id = ppa.project_id UNION SELECT PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DEPEND_DISP_TYPE','SUC'), ppa.project_id, ppa.segment1, ppa.name, ppe.element_number, ppe.name, ppe.proj_element_id, pev.element_version_id, ppe2.proj_element_id, pev3.element_version_id, ppe2.project_id, rel.comments, rel.lag_day, sch.scheduled_start_date, sch.scheduled_finish_date, sch.actual_start_date, sch.actual_finish_date, ppe.manager_person_id, papf.full_name, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_OBJECT_DEPENDENCY_TYPES', rel.relationship_subtype), rel.relationship_subtype, 'SUC', rel.object_relationship_id, rel.record_version_number, pev.parent_structure_version_id FROM pa_projects_all ppa, pa_proj_elements ppe, pa_proj_element_versions pev, pa_object_relationships rel, pa_proj_elements ppe2, pa_proj_element_versions pev3, pa_proj_elem_ver_schedule sch, per_all_people_f papf WHERE rel.status_code = 'UNPUBLISHED' and rel.relationship_type = 'D' and rel.object_id_from1 = pev.element_version_id and rel.object_type_from = 'PA_TASKS' and rel.object_id_to1 = pev3.element_version_id and rel.object_type_to = 'PA_TASKS' and pev3.proj_element_id = ppe2.proj_element_id and pev3.project_id = ppe2.project_id and pev.proj_element_id = ppe.proj_element_id and pev.project_id = ppe.project_id and sch.project_id = pev.project_id and sch.element_version_id = pev.element_version_id and pev.project_id = ppa.project_id and ppe.manager_person_id = papf.person_id(+) and SYSDATE between papf.effective_start_date(+) AND papf.effective_end_date (+) and ppe.proj_element_id NOT IN ( select pev2.proj_element_id from pa_proj_element_versions pev2, pa_object_relationships rel2 where pev2.project_id = pev.project_id and pev2.proJ_element_id = pev.proj_element_id and pev2.element_version_id = rel2.object_id_from1 and rel2.relationship_type = 'D' and rel2.status_code = 'PUBLISHED' )

View Text - HTML Formatted

SELECT PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DEPEND_DISP_TYPE'
, 'PRE')
, PPA.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, PPE.ELEMENT_NUMBER
, PPE.NAME
, PPE.PROJ_ELEMENT_ID
, PEV.ELEMENT_VERSION_ID
, PPE2.PROJ_ELEMENT_ID
, PEV3.ELEMENT_VERSION_ID
, PPE2.PROJECT_ID
, REL.COMMENTS
, REL.LAG_DAY
, SCH.SCHEDULED_START_DATE
, SCH.SCHEDULED_FINISH_DATE
, SCH.ACTUAL_START_DATE
, SCH.ACTUAL_FINISH_DATE
, PPE.MANAGER_PERSON_ID
, PAPF.FULL_NAME
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_OBJECT_DEPENDENCY_TYPES'
, REL.RELATIONSHIP_SUBTYPE)
, REL.RELATIONSHIP_SUBTYPE
, 'PRE'
, REL.OBJECT_RELATIONSHIP_ID
, REL.RECORD_VERSION_NUMBER
, PEV.PARENT_STRUCTURE_VERSION_ID
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PEV
, PA_OBJECT_RELATIONSHIPS REL
, PA_PROJ_ELEMENTS PPE2
, PA_PROJ_ELEMENT_VERSIONS PEV3
, PA_PROJ_ELEM_VER_SCHEDULE SCH
, PER_ALL_PEOPLE_F PAPF
WHERE NVL(REL.STATUS_CODE
, 'PUBLISHED') = 'PUBLISHED'
AND REL.RELATIONSHIP_TYPE = 'D'
AND REL.OBJECT_ID_TO1 = PEV.ELEMENT_VERSION_ID
AND REL.OBJECT_TYPE_TO = 'PA_TASKS'
AND REL.OBJECT_ID_FROM1 = PEV3.ELEMENT_VERSION_ID
AND REL.OBJECT_TYPE_FROM = 'PA_TASKS'
AND PEV3.PROJ_ELEMENT_ID = PPE2.PROJ_ELEMENT_ID
AND PEV3.PROJECT_ID = PPE2.PROJECT_ID
AND PEV.PROJ_ELEMENT_ID = PPE.PROJ_ELEMENT_ID
AND PEV.PROJECT_ID = PPE.PROJECT_ID
AND SCH.PROJECT_ID = PEV.PROJECT_ID
AND SCH.ELEMENT_VERSION_ID = PEV.ELEMENT_VERSION_ID
AND PEV.PROJECT_ID = PPA.PROJECT_ID
AND PPE.MANAGER_PERSON_ID = PAPF.PERSON_ID(+)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE (+)
AND PEV.ELEMENT_VERSION_ID IN ( SELECT MAX(PEV2.ELEMENT_VERSION_ID)
FROM PA_PROJ_ELEMENT_VERSIONS PEV2
WHERE PEV2.PROJECT_ID = PEV.PROJECT_ID
AND PEV2.PROJ_ELEMENT_ID = PEV.PROJ_ELEMENT_ID) UNION SELECT PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DEPEND_DISP_TYPE'
, 'PRE')
, PPA.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, PPE.ELEMENT_NUMBER
, PPE.NAME
, PPE.PROJ_ELEMENT_ID
, PEV.ELEMENT_VERSION_ID
, PPE2.PROJ_ELEMENT_ID
, PEV3.ELEMENT_VERSION_ID
, PPE2.PROJECT_ID
, REL.COMMENTS
, REL.LAG_DAY
, SCH.SCHEDULED_START_DATE
, SCH.SCHEDULED_FINISH_DATE
, SCH.ACTUAL_START_DATE
, SCH.ACTUAL_FINISH_DATE
, PPE.MANAGER_PERSON_ID
, PAPF.FULL_NAME
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_OBJECT_DEPENDENCY_TYPES'
, REL.RELATIONSHIP_SUBTYPE)
, REL.RELATIONSHIP_SUBTYPE
, 'PRE'
, REL.OBJECT_RELATIONSHIP_ID
, REL.RECORD_VERSION_NUMBER
, PEV.PARENT_STRUCTURE_VERSION_ID
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PEV
, PA_OBJECT_RELATIONSHIPS REL
, PA_PROJ_ELEMENTS PPE2
, PA_PROJ_ELEMENT_VERSIONS PEV3
, PA_PROJ_ELEM_VER_SCHEDULE SCH
, PER_ALL_PEOPLE_F PAPF
WHERE NVL(REL.STATUS_CODE
, 'INTRA') = 'INTRA'
AND REL.RELATIONSHIP_TYPE = 'D'
AND REL.OBJECT_ID_TO1 = PEV.ELEMENT_VERSION_ID
AND REL.OBJECT_TYPE_TO = 'PA_TASKS'
AND REL.OBJECT_ID_FROM1 = PEV3.ELEMENT_VERSION_ID
AND REL.OBJECT_TYPE_FROM = 'PA_TASKS'
AND PEV3.PROJ_ELEMENT_ID = PPE2.PROJ_ELEMENT_ID
AND PEV3.PROJECT_ID = PPE2.PROJECT_ID
AND PEV.PROJ_ELEMENT_ID = PPE.PROJ_ELEMENT_ID
AND PEV.PROJECT_ID = PPE.PROJECT_ID
AND SCH.PROJECT_ID = PEV.PROJECT_ID
AND SCH.ELEMENT_VERSION_ID = PEV.ELEMENT_VERSION_ID
AND PEV.PROJECT_ID = PPA.PROJECT_ID
AND PPE.MANAGER_PERSON_ID = PAPF.PERSON_ID(+)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE (+) UNION SELECT PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DEPEND_DISP_TYPE'
, 'PRE')
, PPA.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, PPE.ELEMENT_NUMBER
, PPE.NAME
, PPE.PROJ_ELEMENT_ID
, PEV.ELEMENT_VERSION_ID
, PPE2.PROJ_ELEMENT_ID
, PEV3.ELEMENT_VERSION_ID
, PPE2.PROJECT_ID
, REL.COMMENTS
, REL.LAG_DAY
, SCH.SCHEDULED_START_DATE
, SCH.SCHEDULED_FINISH_DATE
, SCH.ACTUAL_START_DATE
, SCH.ACTUAL_FINISH_DATE
, PPE.MANAGER_PERSON_ID
, PAPF.FULL_NAME
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_OBJECT_DEPENDENCY_TYPES'
, REL.RELATIONSHIP_SUBTYPE)
, REL.RELATIONSHIP_SUBTYPE
, 'PRE'
, REL.OBJECT_RELATIONSHIP_ID
, REL.RECORD_VERSION_NUMBER
, PEV.PARENT_STRUCTURE_VERSION_ID
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PEV
, PA_OBJECT_RELATIONSHIPS REL
, PA_PROJ_ELEMENTS PPE2
, PA_PROJ_ELEMENT_VERSIONS PEV3
, PA_PROJ_ELEM_VER_SCHEDULE SCH
, PER_ALL_PEOPLE_F PAPF
WHERE REL.STATUS_CODE = 'UNPUBLISHED'
AND REL.RELATIONSHIP_TYPE = 'D'
AND REL.OBJECT_ID_TO1 = PEV.ELEMENT_VERSION_ID
AND REL.OBJECT_TYPE_TO = 'PA_TASKS'
AND REL.OBJECT_ID_FROM1 = PEV3.ELEMENT_VERSION_ID
AND REL.OBJECT_TYPE_FROM = 'PA_TASKS'
AND PEV3.PROJ_ELEMENT_ID = PPE2.PROJ_ELEMENT_ID
AND PEV3.PROJECT_ID = PPE2.PROJECT_ID
AND PEV.PROJ_ELEMENT_ID = PPE.PROJ_ELEMENT_ID
AND PEV.PROJECT_ID = PPE.PROJECT_ID
AND SCH.PROJECT_ID = PEV.PROJECT_ID
AND SCH.ELEMENT_VERSION_ID = PEV.ELEMENT_VERSION_ID
AND PEV.PROJECT_ID = PPA.PROJECT_ID
AND PPE.MANAGER_PERSON_ID = PAPF.PERSON_ID(+)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE (+)
AND PPE.PROJ_ELEMENT_ID NOT IN ( SELECT PEV2.PROJ_ELEMENT_ID
FROM PA_PROJ_ELEMENT_VERSIONS PEV2
, PA_OBJECT_RELATIONSHIPS REL2
WHERE PEV2.PROJECT_ID = PEV.PROJECT_ID
AND PEV2.PROJ_ELEMENT_ID = PEV.PROJ_ELEMENT_ID
AND PEV2.ELEMENT_VERSION_ID = REL2.OBJECT_ID_TO1
AND REL2.RELATIONSHIP_TYPE = 'D'
AND REL2.STATUS_CODE = 'PUBLISHED' ) UNION SELECT PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DEPEND_DISP_TYPE'
, 'SUC')
, PPA.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, PPE.ELEMENT_NUMBER
, PPE.NAME
, PPE.PROJ_ELEMENT_ID
, PEV.ELEMENT_VERSION_ID
, PPE2.PROJ_ELEMENT_ID
, PEV3.ELEMENT_VERSION_ID
, PPE2.PROJECT_ID
, REL.COMMENTS
, REL.LAG_DAY
, SCH.SCHEDULED_START_DATE
, SCH.SCHEDULED_FINISH_DATE
, SCH.ACTUAL_START_DATE
, SCH.ACTUAL_FINISH_DATE
, PPE.MANAGER_PERSON_ID
, PAPF.FULL_NAME
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_OBJECT_DEPENDENCY_TYPES'
, REL.RELATIONSHIP_SUBTYPE)
, REL.RELATIONSHIP_SUBTYPE
, 'SUC'
, REL.OBJECT_RELATIONSHIP_ID
, REL.RECORD_VERSION_NUMBER
, PEV.PARENT_STRUCTURE_VERSION_ID
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PEV
, PA_OBJECT_RELATIONSHIPS REL
, PA_PROJ_ELEMENTS PPE2
, PA_PROJ_ELEMENT_VERSIONS PEV3
, PA_PROJ_ELEM_VER_SCHEDULE SCH
, PER_ALL_PEOPLE_F PAPF
WHERE NVL(REL.STATUS_CODE
, 'PUBLISHED') = 'PUBLISHED'
AND REL.RELATIONSHIP_TYPE = 'D'
AND REL.OBJECT_ID_FROM1 = PEV.ELEMENT_VERSION_ID
AND REL.OBJECT_TYPE_FROM = 'PA_TASKS'
AND REL.OBJECT_ID_TO1 = PEV3.ELEMENT_VERSION_ID
AND REL.OBJECT_TYPE_TO = 'PA_TASKS'
AND PEV3.PROJ_ELEMENT_ID = PPE2.PROJ_ELEMENT_ID
AND PEV3.PROJECT_ID = PPE2.PROJECT_ID
AND PEV.PROJ_ELEMENT_ID = PPE.PROJ_ELEMENT_ID
AND PEV.PROJECT_ID = PPE.PROJECT_ID
AND SCH.PROJECT_ID = PEV.PROJECT_ID
AND SCH.ELEMENT_VERSION_ID = PEV.ELEMENT_VERSION_ID
AND PPE.MANAGER_PERSON_ID = PAPF.PERSON_ID(+)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE (+)
AND PEV.PROJECT_ID = PPA.PROJECT_ID
AND PEV.ELEMENT_VERSION_ID IN ( SELECT MAX(PEV2.ELEMENT_VERSION_ID)
FROM PA_PROJ_ELEMENT_VERSIONS PEV2
WHERE PEV2.PROJECT_ID = PEV.PROJECT_ID
AND PEV2.PROJ_ELEMENT_ID = PEV.PROJ_ELEMENT_ID) UNION SELECT PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DEPEND_DISP_TYPE'
, 'SUC')
, PPA.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, PPE.ELEMENT_NUMBER
, PPE.NAME
, PPE.PROJ_ELEMENT_ID
, PEV.ELEMENT_VERSION_ID
, PPE2.PROJ_ELEMENT_ID
, PEV3.ELEMENT_VERSION_ID
, PPE2.PROJECT_ID
, REL.COMMENTS
, REL.LAG_DAY
, SCH.SCHEDULED_START_DATE
, SCH.SCHEDULED_FINISH_DATE
, SCH.ACTUAL_START_DATE
, SCH.ACTUAL_FINISH_DATE
, PPE.MANAGER_PERSON_ID
, PAPF.FULL_NAME
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_OBJECT_DEPENDENCY_TYPES'
, REL.RELATIONSHIP_SUBTYPE)
, REL.RELATIONSHIP_SUBTYPE
, 'SUC'
, REL.OBJECT_RELATIONSHIP_ID
, REL.RECORD_VERSION_NUMBER
, PEV.PARENT_STRUCTURE_VERSION_ID
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PEV
, PA_OBJECT_RELATIONSHIPS REL
, PA_PROJ_ELEMENTS PPE2
, PA_PROJ_ELEMENT_VERSIONS PEV3
, PA_PROJ_ELEM_VER_SCHEDULE SCH
, PER_ALL_PEOPLE_F PAPF
WHERE NVL(REL.STATUS_CODE
, 'INTRA') = 'INTRA'
AND REL.RELATIONSHIP_TYPE = 'D'
AND REL.OBJECT_ID_FROM1 = PEV.ELEMENT_VERSION_ID
AND REL.OBJECT_TYPE_FROM = 'PA_TASKS'
AND REL.OBJECT_ID_TO1 = PEV3.ELEMENT_VERSION_ID
AND REL.OBJECT_TYPE_TO = 'PA_TASKS'
AND PEV3.PROJ_ELEMENT_ID = PPE2.PROJ_ELEMENT_ID
AND PEV3.PROJECT_ID = PPE2.PROJECT_ID
AND PEV.PROJ_ELEMENT_ID = PPE.PROJ_ELEMENT_ID
AND PEV.PROJECT_ID = PPE.PROJECT_ID
AND SCH.PROJECT_ID = PEV.PROJECT_ID
AND SCH.ELEMENT_VERSION_ID = PEV.ELEMENT_VERSION_ID
AND PPE.MANAGER_PERSON_ID = PAPF.PERSON_ID(+)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE (+)
AND PEV.PROJECT_ID = PPA.PROJECT_ID UNION SELECT PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_DEPEND_DISP_TYPE'
, 'SUC')
, PPA.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, PPE.ELEMENT_NUMBER
, PPE.NAME
, PPE.PROJ_ELEMENT_ID
, PEV.ELEMENT_VERSION_ID
, PPE2.PROJ_ELEMENT_ID
, PEV3.ELEMENT_VERSION_ID
, PPE2.PROJECT_ID
, REL.COMMENTS
, REL.LAG_DAY
, SCH.SCHEDULED_START_DATE
, SCH.SCHEDULED_FINISH_DATE
, SCH.ACTUAL_START_DATE
, SCH.ACTUAL_FINISH_DATE
, PPE.MANAGER_PERSON_ID
, PAPF.FULL_NAME
, PA_PROJ_ELEMENTS_UTILS.GET_PA_LOOKUP_MEANING('PA_OBJECT_DEPENDENCY_TYPES'
, REL.RELATIONSHIP_SUBTYPE)
, REL.RELATIONSHIP_SUBTYPE
, 'SUC'
, REL.OBJECT_RELATIONSHIP_ID
, REL.RECORD_VERSION_NUMBER
, PEV.PARENT_STRUCTURE_VERSION_ID
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PEV
, PA_OBJECT_RELATIONSHIPS REL
, PA_PROJ_ELEMENTS PPE2
, PA_PROJ_ELEMENT_VERSIONS PEV3
, PA_PROJ_ELEM_VER_SCHEDULE SCH
, PER_ALL_PEOPLE_F PAPF
WHERE REL.STATUS_CODE = 'UNPUBLISHED'
AND REL.RELATIONSHIP_TYPE = 'D'
AND REL.OBJECT_ID_FROM1 = PEV.ELEMENT_VERSION_ID
AND REL.OBJECT_TYPE_FROM = 'PA_TASKS'
AND REL.OBJECT_ID_TO1 = PEV3.ELEMENT_VERSION_ID
AND REL.OBJECT_TYPE_TO = 'PA_TASKS'
AND PEV3.PROJ_ELEMENT_ID = PPE2.PROJ_ELEMENT_ID
AND PEV3.PROJECT_ID = PPE2.PROJECT_ID
AND PEV.PROJ_ELEMENT_ID = PPE.PROJ_ELEMENT_ID
AND PEV.PROJECT_ID = PPE.PROJECT_ID
AND SCH.PROJECT_ID = PEV.PROJECT_ID
AND SCH.ELEMENT_VERSION_ID = PEV.ELEMENT_VERSION_ID
AND PEV.PROJECT_ID = PPA.PROJECT_ID
AND PPE.MANAGER_PERSON_ID = PAPF.PERSON_ID(+)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE (+)
AND PPE.PROJ_ELEMENT_ID NOT IN ( SELECT PEV2.PROJ_ELEMENT_ID
FROM PA_PROJ_ELEMENT_VERSIONS PEV2
, PA_OBJECT_RELATIONSHIPS REL2
WHERE PEV2.PROJECT_ID = PEV.PROJECT_ID
AND PEV2.PROJ_ELEMENT_ID = PEV.PROJ_ELEMENT_ID
AND PEV2.ELEMENT_VERSION_ID = REL2.OBJECT_ID_FROM1
AND REL2.RELATIONSHIP_TYPE = 'D'
AND REL2.STATUS_CODE = 'PUBLISHED' )