View: AMW_CURR_APP_HIERARCHY_ORG_V

Product:	AMW - Internal Controls Manager
Description:	View for approved organizations process hierarchy
Implementation/DBA Data:	APPS.AMW_CURR_APP_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
, 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 CHILD_CONTROL_COUNT
, CP.RISK_COUNT CHILD_RISK_COUNT
, 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_APPROVED_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 AH.START_DATE <= SYSDATE
AND (AH.END_DATE IS NULL OR AH.END_DATE > SYSDATE)
AND PP.APPROVAL_DATE <= SYSDATE
AND (PP.APPROVAL_END_DATE IS NULL OR PP.APPROVAL_END_DATE > SYSDATE)
AND PP.DELETION_DATE IS NULL
AND CP.APPROVAL_DATE <= SYSDATE
AND (CP.APPROVAL_END_DATE IS NULL OR CP.APPROVAL_END_DATE > SYSDATE)
AND CP.DELETION_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
, 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
, 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 CHILD_CONTROL_COUNT
, CP.RISK_COUNT CHILD_RISK_COUNT
, 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