VIEW: APPS.PA_DELIVERABLES_AMG_V

Source

View Text - Preformatted

SELECT PPA.PROJECT_ID ,PPA.NAME ,PPA.SEGMENT1 ,PPE.PROJ_ELEMENT_ID ,PPE.NAME ,PPE.ELEMENT_NUMBER ,PPS.PROJECT_STATUS_NAME ,PPE.STATUS_CODE ,to_char(null) PROJECT_STATUS_NAME ,to_char(null) PROGRESS_STATUS_CODE ,PPF.FULL_NAME ,PPE.MANAGER_PERSON_ID ,PTT.TASK_TYPE ,PPE.TYPE_ID ,PPE.PROGRESS_WEIGHT ,PPE.DESCRIPTION ,PSC.SCHEDULED_FINISH_DATE ,PSC.ACTUAL_FINISH_DATE ,PPE.PM_SOURCE_REFERENCE ,PPE.PM_SOURCE_CODE ,PPE.RECORD_VERSION_NUMBER ,to_date(null) DATE_COMPUTED ,to_number(null) COMPLETED_PERCENTAGE ,to_number(null) PUBLISHED_BY_PARTY_ID ,to_char(null) PROGRESS_COMMENT ,to_char(null) DESCRIPTION ,to_char(null) PROG_ENTRY_ENABLE_FLAG FROM PA_PROJ_ELEMENTS PPE ,PA_PROJ_ELEMENT_VERSIONS PPV ,PA_PROJ_ELEM_VER_SCHEDULE PSC ,PA_PROJECT_STATUSES PPS ,PA_TASK_TYPES PTT ,PER_ALL_PEOPLE_F PPF ,PA_PROJECTS_ALL PPA ,PA_LOOKUPS PLL WHERE PPE.OBJECT_TYPE = 'PA_DELIVERABLES' AND PPE.PROJ_ELEMENT_ID = PPV.PROJ_ELEMENT_ID AND PPE.PROJECT_ID=PPV.PROJECT_ID AND PPE.PROJECT_ID = PSC.PROJECT_ID AND PPE.PROJECT_ID = PPA.PROJECT_ID AND PPV.ELEMENT_VERSION_ID = PSC.ELEMENT_VERSION_ID AND PTT.OBJECT_TYPE = 'PA_DLVR_TYPES' AND PTT.TASK_TYPE_ID = PPE.TYPE_ID AND PPE.STATUS_CODE = PPS.PROJECT_STATUS_CODE(+) AND PPE.MANAGER_PERSON_ID = PPF.PERSON_ID(+) AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE(+) AND PPF.EFFECTIVE_END_DATE(+) AND PLL.LOOKUP_TYPE = 'PA_DLVR_TYPE' AND PLL.LOOKUP_CODE = PTT.TASK_TYPE_CLASS_CODE AND NOT EXISTS (SELECT 1 FROM PA_PROGRESS_ROLLUP WHERE PROJECT_ID = PPE.PROJECT_ID AND OBJECT_ID = PPE.PROJ_ELEMENT_ID AND OBJECT_TYPE = PPE.OBJECT_TYPE AND STRUCTURE_TYPE = 'WORKPLAN' AND STRUCTURE_VERSION_ID IS NULL) UNION ALL SELECT PPA.PROJECT_ID ,PPA.NAME ,PPA.SEGMENT1 ,PPE.PROJ_ELEMENT_ID ,PPE.NAME ,PPE.ELEMENT_NUMBER ,PPS.PROJECT_STATUS_NAME ,PPE.STATUS_CODE ,STA.PROJECT_STATUS_NAME ,PPR.PROGRESS_STATUS_CODE ,PPF.FULL_NAME ,PPE.MANAGER_PERSON_ID ,PTT.TASK_TYPE ,PPE.TYPE_ID ,PPE.PROGRESS_WEIGHT ,PPE.DESCRIPTION ,PSC.SCHEDULED_FINISH_DATE ,PSC.ACTUAL_FINISH_DATE ,PPE.PM_SOURCE_REFERENCE ,PPE.PM_SOURCE_CODE ,PPE.RECORD_VERSION_NUMBER ,PPC.DATE_COMPUTED ,PPC.COMPLETED_PERCENTAGE ,PPC.PUBLISHED_BY_PARTY_ID ,PPC.PROGRESS_COMMENT ,PPC.DESCRIPTION ,PTT.PROG_ENTRY_ENABLE_FLAG FROM PA_PROJ_ELEMENTS PPE ,PA_PROJ_ELEMENT_VERSIONS PPV ,PA_PROJ_ELEM_VER_SCHEDULE PSC ,PA_PROJECT_STATUSES PPS ,PA_TASK_TYPES PTT ,PER_ALL_PEOPLE_F PPF ,PA_PROJECTS_ALL PPA ,PA_LOOKUPS PLL ,PA_PROGRESS_ROLLUP PPR ,PA_PERCENT_COMPLETES PPC ,PA_PROJECT_STATUSES STA WHERE PPE.OBJECT_TYPE = 'PA_DELIVERABLES' AND PPE.PROJ_ELEMENT_ID = PPV.PROJ_ELEMENT_ID AND PPE.PROJECT_ID=PPV.PROJECT_ID AND PPE.PROJECT_ID = PSC.PROJECT_ID AND PPE.PROJECT_ID = PPA.PROJECT_ID AND PPV.ELEMENT_VERSION_ID = PSC.ELEMENT_VERSION_ID AND PTT.OBJECT_TYPE = 'PA_DLVR_TYPES' AND PTT.TASK_TYPE_ID = PPE.TYPE_ID AND PPE.STATUS_CODE = PPS.PROJECT_STATUS_CODE(+) AND PPE.MANAGER_PERSON_ID = PPF.PERSON_ID(+) AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE(+) AND PPF.EFFECTIVE_END_DATE(+) AND PLL.LOOKUP_TYPE = 'PA_DLVR_TYPE' AND PLL.LOOKUP_CODE = PTT.TASK_TYPE_CLASS_CODE AND PPE.PROJECT_ID = PPR.PROJECT_ID AND PPE.PROJ_ELEMENT_ID = PPR.OBJECT_ID AND PPE.OBJECT_TYPE = PPR.OBJECT_TYPE AND PPR.STRUCTURE_TYPE = 'WORKPLAN' AND PPR.STRUCTURE_VERSION_ID IS NULL AND (PPC.PUBLISHED_FLAG = 'N' OR (PPC.CURRENT_FLAG = 'Y' AND NOT EXISTS (SELECT 1 FROM PA_PERCENT_COMPLETES WHERE PROJECT_ID = PPE.PROJECT_ID AND OBJECT_ID = PPE.PROJ_ELEMENT_ID AND OBJECT_TYPE = PPE.OBJECT_TYPE AND STRUCTURE_TYPE = 'WORKPLAN' AND PUBLISHED_FLAG = 'N'))) AND PPR.PROGRESS_STATUS_CODE = STA.PROJECT_STATUS_CODE(+) 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 PPR.STRUCTURE_TYPE = PPC.STRUCTURE_TYPE

View Text - HTML Formatted

SELECT PPA.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PPE.PROJ_ELEMENT_ID
, PPE.NAME
, PPE.ELEMENT_NUMBER
, PPS.PROJECT_STATUS_NAME
, PPE.STATUS_CODE
, TO_CHAR(NULL) PROJECT_STATUS_NAME
, TO_CHAR(NULL) PROGRESS_STATUS_CODE
, PPF.FULL_NAME
, PPE.MANAGER_PERSON_ID
, PTT.TASK_TYPE
, PPE.TYPE_ID
, PPE.PROGRESS_WEIGHT
, PPE.DESCRIPTION
, PSC.SCHEDULED_FINISH_DATE
, PSC.ACTUAL_FINISH_DATE
, PPE.PM_SOURCE_REFERENCE
, PPE.PM_SOURCE_CODE
, PPE.RECORD_VERSION_NUMBER
, TO_DATE(NULL) DATE_COMPUTED
, TO_NUMBER(NULL) COMPLETED_PERCENTAGE
, TO_NUMBER(NULL) PUBLISHED_BY_PARTY_ID
, TO_CHAR(NULL) PROGRESS_COMMENT
, TO_CHAR(NULL) DESCRIPTION
, TO_CHAR(NULL) PROG_ENTRY_ENABLE_FLAG
FROM PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPV
, PA_PROJ_ELEM_VER_SCHEDULE PSC
, PA_PROJECT_STATUSES PPS
, PA_TASK_TYPES PTT
, PER_ALL_PEOPLE_F PPF
, PA_PROJECTS_ALL PPA
, PA_LOOKUPS PLL
WHERE PPE.OBJECT_TYPE = 'PA_DELIVERABLES'
AND PPE.PROJ_ELEMENT_ID = PPV.PROJ_ELEMENT_ID
AND PPE.PROJECT_ID=PPV.PROJECT_ID
AND PPE.PROJECT_ID = PSC.PROJECT_ID
AND PPE.PROJECT_ID = PPA.PROJECT_ID
AND PPV.ELEMENT_VERSION_ID = PSC.ELEMENT_VERSION_ID
AND PTT.OBJECT_TYPE = 'PA_DLVR_TYPES'
AND PTT.TASK_TYPE_ID = PPE.TYPE_ID
AND PPE.STATUS_CODE = PPS.PROJECT_STATUS_CODE(+)
AND PPE.MANAGER_PERSON_ID = PPF.PERSON_ID(+)
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE(+)
AND PPF.EFFECTIVE_END_DATE(+)
AND PLL.LOOKUP_TYPE = 'PA_DLVR_TYPE'
AND PLL.LOOKUP_CODE = PTT.TASK_TYPE_CLASS_CODE
AND NOT EXISTS (SELECT 1
FROM PA_PROGRESS_ROLLUP
WHERE PROJECT_ID = PPE.PROJECT_ID
AND OBJECT_ID = PPE.PROJ_ELEMENT_ID
AND OBJECT_TYPE = PPE.OBJECT_TYPE
AND STRUCTURE_TYPE = 'WORKPLAN'
AND STRUCTURE_VERSION_ID IS NULL) UNION ALL SELECT PPA.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, PPE.PROJ_ELEMENT_ID
, PPE.NAME
, PPE.ELEMENT_NUMBER
, PPS.PROJECT_STATUS_NAME
, PPE.STATUS_CODE
, STA.PROJECT_STATUS_NAME
, PPR.PROGRESS_STATUS_CODE
, PPF.FULL_NAME
, PPE.MANAGER_PERSON_ID
, PTT.TASK_TYPE
, PPE.TYPE_ID
, PPE.PROGRESS_WEIGHT
, PPE.DESCRIPTION
, PSC.SCHEDULED_FINISH_DATE
, PSC.ACTUAL_FINISH_DATE
, PPE.PM_SOURCE_REFERENCE
, PPE.PM_SOURCE_CODE
, PPE.RECORD_VERSION_NUMBER
, PPC.DATE_COMPUTED
, PPC.COMPLETED_PERCENTAGE
, PPC.PUBLISHED_BY_PARTY_ID
, PPC.PROGRESS_COMMENT
, PPC.DESCRIPTION
, PTT.PROG_ENTRY_ENABLE_FLAG
FROM PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPV
, PA_PROJ_ELEM_VER_SCHEDULE PSC
, PA_PROJECT_STATUSES PPS
, PA_TASK_TYPES PTT
, PER_ALL_PEOPLE_F PPF
, PA_PROJECTS_ALL PPA
, PA_LOOKUPS PLL
, PA_PROGRESS_ROLLUP PPR
, PA_PERCENT_COMPLETES PPC
, PA_PROJECT_STATUSES STA
WHERE PPE.OBJECT_TYPE = 'PA_DELIVERABLES'
AND PPE.PROJ_ELEMENT_ID = PPV.PROJ_ELEMENT_ID
AND PPE.PROJECT_ID=PPV.PROJECT_ID
AND PPE.PROJECT_ID = PSC.PROJECT_ID
AND PPE.PROJECT_ID = PPA.PROJECT_ID
AND PPV.ELEMENT_VERSION_ID = PSC.ELEMENT_VERSION_ID
AND PTT.OBJECT_TYPE = 'PA_DLVR_TYPES'
AND PTT.TASK_TYPE_ID = PPE.TYPE_ID
AND PPE.STATUS_CODE = PPS.PROJECT_STATUS_CODE(+)
AND PPE.MANAGER_PERSON_ID = PPF.PERSON_ID(+)
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE(+)
AND PPF.EFFECTIVE_END_DATE(+)
AND PLL.LOOKUP_TYPE = 'PA_DLVR_TYPE'
AND PLL.LOOKUP_CODE = PTT.TASK_TYPE_CLASS_CODE
AND PPE.PROJECT_ID = PPR.PROJECT_ID
AND PPE.PROJ_ELEMENT_ID = PPR.OBJECT_ID
AND PPE.OBJECT_TYPE = PPR.OBJECT_TYPE
AND PPR.STRUCTURE_TYPE = 'WORKPLAN'
AND PPR.STRUCTURE_VERSION_ID IS NULL
AND (PPC.PUBLISHED_FLAG = 'N' OR (PPC.CURRENT_FLAG = 'Y'
AND NOT EXISTS (SELECT 1
FROM PA_PERCENT_COMPLETES
WHERE PROJECT_ID = PPE.PROJECT_ID
AND OBJECT_ID = PPE.PROJ_ELEMENT_ID
AND OBJECT_TYPE = PPE.OBJECT_TYPE
AND STRUCTURE_TYPE = 'WORKPLAN'
AND PUBLISHED_FLAG = 'N')))
AND PPR.PROGRESS_STATUS_CODE = STA.PROJECT_STATUS_CODE(+)
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 PPR.STRUCTURE_TYPE = PPC.STRUCTURE_TYPE