View: AMW_LATEST_HIERARCHY_ORG_V

Product:	AMW - Internal Controls Manager
Description:	View for organizations process latest hierarchy
Implementation/DBA Data:	APPS.AMW_LATEST_HIERARCHY_ORG_V

View Text

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

Columns