VIEW: APPS.AMW_AUDIT_PROJECTS_V

Source

View Text - Preformatted

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, ap.scope_changed_flag, 'ICM', ap.sign_off_status, (select meaning from amw_lookups where lookup_type = 'AMW_ENGAGEMENT_SIGN_OFF_STATUS' and lookup_code = ap.sign_off_status), NVL(ap.sign_off_required_flag,'N'), TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',null,SUBSTR(USERENV('CLIENT_INFO'),1,10))) 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, ap.project_id, ppa.segment1, ppa.name, ppa.description, ap.audit_project_status, (select meaning from amw_lookups where lookup_type = 'AMW_ENGAGEMENT_STATUS' and lookup_code = ap.audit_project_status), 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, ap.audit_project_status, ppa.start_date, ppa.completion_date, ppa.template_flag, ppa.created_from_project_id, ap.engagement_type_id, ap.scope_changed_flag, 'PA', ap.sign_off_status, (select meaning from amw_lookups where lookup_type = 'AMW_ENGAGEMENT_SIGN_OFF_STATUS' and lookup_code = ap.sign_off_status), NVL(ap.sign_off_required_flag,'N'), ppa.org_id from amw_audit_projects ap, pa_projects_all ppa, 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.project_id = ppa.project_id

View Text - HTML Formatted

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
, AP.SCOPE_CHANGED_FLAG
, 'ICM'
, AP.SIGN_OFF_STATUS
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE = 'AMW_ENGAGEMENT_SIGN_OFF_STATUS'
AND LOOKUP_CODE = AP.SIGN_OFF_STATUS)
, NVL(AP.SIGN_OFF_REQUIRED_FLAG
, 'N')
, TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTR(USERENV('CLIENT_INFO')
, 1
, 10)))
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
, AP.PROJECT_ID
, PPA.SEGMENT1
, PPA.NAME
, PPA.DESCRIPTION
, AP.AUDIT_PROJECT_STATUS
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE = 'AMW_ENGAGEMENT_STATUS'
AND LOOKUP_CODE = AP.AUDIT_PROJECT_STATUS)
, 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
, AP.AUDIT_PROJECT_STATUS
, PPA.START_DATE
, PPA.COMPLETION_DATE
, PPA.TEMPLATE_FLAG
, PPA.CREATED_FROM_PROJECT_ID
, AP.ENGAGEMENT_TYPE_ID
, AP.SCOPE_CHANGED_FLAG
, 'PA'
, AP.SIGN_OFF_STATUS
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE = 'AMW_ENGAGEMENT_SIGN_OFF_STATUS'
AND LOOKUP_CODE = AP.SIGN_OFF_STATUS)
, NVL(AP.SIGN_OFF_REQUIRED_FLAG
, 'N')
, PPA.ORG_ID
FROM AMW_AUDIT_PROJECTS AP
, PA_PROJECTS_ALL PPA
, 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.PROJECT_ID = PPA.PROJECT_ID