View: AMW_PROJECT_LISTS_V

Product:	AMW - Internal Controls Manager
Description:
Implementation/DBA Data:	APPS.AMW_PROJECT_LISTS_V

View Text

SELECT AP.AUDIT_PROJECT_ID
, AP.PROJECT_ID
, AP.PROJECT_NUMBER
, APT.NAME
, APT.DESCRIPTION
, AP.AUDIT_PROJECT_STATUS
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE = 'AMW_ENGAGEMENT_STATUS'
AND LOOKUP_CODE = AP.AUDIT_PROJECT_STATUS)
, AP.AUDIT_MANAGER_PERSON_ID
, (SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = AP.AUDIT_MANAGER_PERSON_ID
AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE
AND EMPLOYEE_NUMBER IS NOT NULL )
, AP.CREATED_BY
, AP.CREATION_DATE
, AP.LAST_UPDATE_DATE
, AP.LAST_UPDATED_BY
, AP.AUDIT_PROJECT_STATUS
, AP.START_DATE
, AP.COMPLETION_DATE
, AP.TEMPLATE_FLAG
, AP.CREATED_FROM_PROJECT_ID
, AP.ENGAGEMENT_TYPE_ID
, 'ICM'
FROM AMW_AUDIT_PROJECTS AP
, AMW_AUDIT_PROJECTS_TL APT
, AMW_WORK_CATEGORIES_B CB
, AMW_WORK_TYPES_B TB
WHERE CB.CATEGORY_CODE='ENGAGEMENT'
AND TB.CATEGORY_ID = CB.CATEGORY_ID
AND AP.ENGAGEMENT_TYPE_ID = TB.WORK_TYPE_ID
AND AP.AUDIT_PROJECT_ID = APT.AUDIT_PROJECT_ID
AND APT.LANGUAGE=USERENV('LANG')
AND AP.PROJECT_ID IS NULL UNION SELECT AP.AUDIT_PROJECT_ID
, PPA.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, PPA.DESCRIPTION
, NVL(AP.AUDIT_PROJECT_STATUS
, 'ACTI')
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE = 'AMW_ENGAGEMENT_STATUS'
AND LOOKUP_CODE = NVL(AP.AUDIT_PROJECT_STATUS
, 'ACTI'))
, PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER(PPA.PROJECT_ID)
, PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER_NAME
, PPA.CREATED_BY
, PPA.CREATION_DATE
, PPA.LAST_UPDATE_DATE
, PPA.LAST_UPDATED_BY
, NVL(AP.AUDIT_PROJECT_STATUS
, 'ACTI')
, PPA.START_DATE
, PPA.COMPLETION_DATE
, PPA.TEMPLATE_FLAG
, PPA.CREATED_FROM_PROJECT_ID
, TB.WORK_TYPE_ID
, 'PA'
FROM PA_PROJECTS_ALL PPA
, AMW_AUDIT_PROJECTS AP
, PA_PROJECT_TYPES_ALL PT
, AMW_WORK_CATEGORIES_B CB
, AMW_WORK_TYPES_B TB
WHERE CB.CATEGORY_CODE='ENGAGEMENT'
AND TB.CATEGORY_ID = CB.CATEGORY_ID
AND TB.PROJECT_TYPE_ID = PT.PROJECT_TYPE_ID
AND PPA.PROJECT_TYPE = PT.PROJECT_TYPE
AND PPA.PROJECT_ID = AP.PROJECT_ID(+)

Columns