VIEW: APPS.AMW_LATEST_HIERARCHY_ORG_V

Source

View Text - Preformatted

SELECT pp.PROCESS_ID PARENT_PROCESS_ID, pp.REVISION_NUMBER PARENT_REVISION_NUMBER, pp.PROCESS_CODE PARENT_PROCESS_CODE, pp.DISPLAY_NAME PARENT_DISPLAY_NAME, pp.PROCESS_ORG_REV_ID PARENT_PROCESS_ORG_REV_ID, pp.approval_status PARENT_APPROVAL_STATUS, cp.DISPLAY_NAME CHILD_DISPLAY_NAME, cp.DESCRIPTION CHILD_DESCRIPTION, cp.PROCESS_ID CHILD_PROCESS_ID, cp.ORGANIZATION_ID CHILD_ORGANIZATION_ID, cp.PROCESS_CODE CHILD_PROCESS_CODE, cp.REVISION_NUMBER CHILD_REVISION_NUMBER, cp.PROCESS_ORG_REV_ID CHILD_PROCESS_ORG_REV_ID, cp.APPROVAL_STATUS CHILD_APPROVAL_STATUS, cp.START_DATE CHILD_START_DATE, cp.APPROVAL_DATE CHILD_APPROVAL_DATE, cp.APPROVAL_END_DATE CHILD_APPROVAL_END_DATE, cp.END_DATE CHILD_END_DATE, cp.CONTROL_COUNT_LATEST CHILD_CONTROL_COUNT_LATEST, cp.RISK_COUNT_LATEST CHILD_RISK_COUNT_LATEST, cp.SIGNIFICANT_PROCESS_FLAG CHILD_SIGNIFICANT_PROCESS_FLAG, cp.STANDARD_PROCESS_FLAG CHILD_STANDARD_PROCESS_FLAG, cp.CERTIFICATION_STATUS CHILD_CERTIFICATION_STATUS, cp.PROCESS_CATEGORY CHILD_PROCESS_CATEGORY, cp.PROCESS_OWNER_ID CHILD_PROCESS_OWNER_ID, cp.FINANCE_OWNER_ID CHILD_FINANCE_OWNER_ID, cp.APPLICATION_OWNER_ID CHILD_APPLICATION_OWNER_ID, cp.STANDARD_VARIATION CHILD_STANDARD_VARIATION, cp.PROCESS_TYPE CHILD_PROCESS_TYPE, cp.CONTROL_ACTIVITY_TYPE CHILD_CONTROL_ACTIVITY_TYPE, ah.child_order_number CHILD_ORDER_NUMBER from amw_process_organization_vl pp, amw_process_organization_vl cp, amw_latest_hierarchies ah WHERE ah.parent_id = pp.process_id and ah.child_id = cp.process_id and ah.organization_id = cp.organization_id and ah.organization_id = pp.organization_id and pp.end_date is null and cp.end_date is null union select to_number(null) PARENT_PROCESS_ID, to_number(null) PARENT_REVISION_NUMBER, null PARENT_PROCESS_CODE, null PARENT_DISPLAY_NAME, null PARENT_PROCESS_ORG_REV_ID, null PARENT_APPROVAL_STATUS, cp.DISPLAY_NAME CHILD_DISPLAY_NAME, cp.DESCRIPTION CHILD_DESCRIPTION, cp.PROCESS_ID CHILD_PROCESS_ID, cp.ORGANIZATION_ID CHILD_ORGANIZATION_ID, null CHILD_PROCESS_CODE, cp.REVISION_NUMBER CHILD_REVISION_NUMBER, cp.PROCESS_ORG_REV_ID CHILD_PROCESS_ORG_REV_ID, null CHILD_APPROVAL_STATUS, cp.START_DATE CHILD_START_DATE, cp.APPROVAL_DATE CHILD_APPROVAL_DATE, cp.APPROVAL_END_DATE CHILD_APPROVAL_END_DATE, cp.END_DATE CHILD_END_DATE, cp.CONTROL_COUNT_LATEST CHILD_CONTROL_COUNT_LATEST, cp.RISK_COUNT_LATEST CHILD_RISK_COUNT_LATEST, cp.SIGNIFICANT_PROCESS_FLAG CHILD_SIGNIFICANT_PROCESS_FLAG, cp.STANDARD_PROCESS_FLAG CHILD_STANDARD_PROCESS_FLAG, cp.CERTIFICATION_STATUS CHILD_CERTIFICATION_STATUS, cp.PROCESS_CATEGORY CHILD_PROCESS_CATEGORY, cp.PROCESS_OWNER_ID CHILD_PROCESS_OWNER_ID, cp.FINANCE_OWNER_ID CHILD_FINANCE_OWNER_ID, cp.APPLICATION_OWNER_ID CHILD_APPLICATION_OWNER_ID, cp.STANDARD_VARIATION CHILD_STANDARD_VARIATION, cp.PROCESS_TYPE CHILD_PROCESS_TYPE, cp.CONTROL_ACTIVITY_TYPE CHILD_CONTROL_ACTIVITY_TYPE, to_number(null) CHILD_ORDER_NUMBER from amw_process_organization_vl cp where cp.process_id = -2

View Text - HTML Formatted

SELECT PP.PROCESS_ID PARENT_PROCESS_ID
, PP.REVISION_NUMBER PARENT_REVISION_NUMBER
, PP.PROCESS_CODE PARENT_PROCESS_CODE
, PP.DISPLAY_NAME PARENT_DISPLAY_NAME
, PP.PROCESS_ORG_REV_ID PARENT_PROCESS_ORG_REV_ID
, PP.APPROVAL_STATUS PARENT_APPROVAL_STATUS
, CP.DISPLAY_NAME CHILD_DISPLAY_NAME
, CP.DESCRIPTION CHILD_DESCRIPTION
, CP.PROCESS_ID CHILD_PROCESS_ID
, CP.ORGANIZATION_ID CHILD_ORGANIZATION_ID
, CP.PROCESS_CODE CHILD_PROCESS_CODE
, CP.REVISION_NUMBER CHILD_REVISION_NUMBER
, CP.PROCESS_ORG_REV_ID CHILD_PROCESS_ORG_REV_ID
, CP.APPROVAL_STATUS CHILD_APPROVAL_STATUS
, CP.START_DATE CHILD_START_DATE
, CP.APPROVAL_DATE CHILD_APPROVAL_DATE
, CP.APPROVAL_END_DATE CHILD_APPROVAL_END_DATE
, CP.END_DATE CHILD_END_DATE
, CP.CONTROL_COUNT_LATEST CHILD_CONTROL_COUNT_LATEST
, CP.RISK_COUNT_LATEST CHILD_RISK_COUNT_LATEST
, CP.SIGNIFICANT_PROCESS_FLAG CHILD_SIGNIFICANT_PROCESS_FLAG
, CP.STANDARD_PROCESS_FLAG CHILD_STANDARD_PROCESS_FLAG
, CP.CERTIFICATION_STATUS CHILD_CERTIFICATION_STATUS
, CP.PROCESS_CATEGORY CHILD_PROCESS_CATEGORY
, CP.PROCESS_OWNER_ID CHILD_PROCESS_OWNER_ID
, CP.FINANCE_OWNER_ID CHILD_FINANCE_OWNER_ID
, CP.APPLICATION_OWNER_ID CHILD_APPLICATION_OWNER_ID
, CP.STANDARD_VARIATION CHILD_STANDARD_VARIATION
, CP.PROCESS_TYPE CHILD_PROCESS_TYPE
, CP.CONTROL_ACTIVITY_TYPE CHILD_CONTROL_ACTIVITY_TYPE
, AH.CHILD_ORDER_NUMBER CHILD_ORDER_NUMBER
FROM AMW_PROCESS_ORGANIZATION_VL PP
, AMW_PROCESS_ORGANIZATION_VL CP
, AMW_LATEST_HIERARCHIES AH
WHERE AH.PARENT_ID = PP.PROCESS_ID
AND AH.CHILD_ID = CP.PROCESS_ID
AND AH.ORGANIZATION_ID = CP.ORGANIZATION_ID
AND AH.ORGANIZATION_ID = PP.ORGANIZATION_ID
AND PP.END_DATE IS NULL
AND CP.END_DATE IS NULL UNION SELECT TO_NUMBER(NULL) PARENT_PROCESS_ID
, TO_NUMBER(NULL) PARENT_REVISION_NUMBER
, NULL PARENT_PROCESS_CODE
, NULL PARENT_DISPLAY_NAME
, NULL PARENT_PROCESS_ORG_REV_ID
, NULL PARENT_APPROVAL_STATUS
, CP.DISPLAY_NAME CHILD_DISPLAY_NAME
, CP.DESCRIPTION CHILD_DESCRIPTION
, CP.PROCESS_ID CHILD_PROCESS_ID
, CP.ORGANIZATION_ID CHILD_ORGANIZATION_ID
, NULL CHILD_PROCESS_CODE
, CP.REVISION_NUMBER CHILD_REVISION_NUMBER
, CP.PROCESS_ORG_REV_ID CHILD_PROCESS_ORG_REV_ID
, NULL CHILD_APPROVAL_STATUS
, CP.START_DATE CHILD_START_DATE
, CP.APPROVAL_DATE CHILD_APPROVAL_DATE
, CP.APPROVAL_END_DATE CHILD_APPROVAL_END_DATE
, CP.END_DATE CHILD_END_DATE
, CP.CONTROL_COUNT_LATEST CHILD_CONTROL_COUNT_LATEST
, CP.RISK_COUNT_LATEST CHILD_RISK_COUNT_LATEST
, CP.SIGNIFICANT_PROCESS_FLAG CHILD_SIGNIFICANT_PROCESS_FLAG
, CP.STANDARD_PROCESS_FLAG CHILD_STANDARD_PROCESS_FLAG
, CP.CERTIFICATION_STATUS CHILD_CERTIFICATION_STATUS
, CP.PROCESS_CATEGORY CHILD_PROCESS_CATEGORY
, CP.PROCESS_OWNER_ID CHILD_PROCESS_OWNER_ID
, CP.FINANCE_OWNER_ID CHILD_FINANCE_OWNER_ID
, CP.APPLICATION_OWNER_ID CHILD_APPLICATION_OWNER_ID
, CP.STANDARD_VARIATION CHILD_STANDARD_VARIATION
, CP.PROCESS_TYPE CHILD_PROCESS_TYPE
, CP.CONTROL_ACTIVITY_TYPE CHILD_CONTROL_ACTIVITY_TYPE
, TO_NUMBER(NULL) CHILD_ORDER_NUMBER
FROM AMW_PROCESS_ORGANIZATION_VL CP
WHERE CP.PROCESS_ID = -2