View: AMW_CURR_APP_HIERARCHY_RL_V

Product:	AMW - Internal Controls Manager
Description:	View for risk library approved process hierarchy
Implementation/DBA Data:	APPS.AMW_CURR_APP_HIERARCHY_RL_V

View Text

SELECT PR.PROCESS_ID PARENT_PROCESS_ID
, PR.REVISION_NUMBER PARENT_REVISION_NUMBER
, PR.PROCESS_CODE PARTENT_PROCESS_CODE
, PR.DISPLAY_NAME PARENT_DISPLAY_NAME
, PR.PROCESS_REV_ID PARENT_PROCESS_REV_ID
, CR.PROCESS_ID CHILD_PROCESS_ID
, CR.REVISION_NUMBER CHILD_REVISION_NUMBER
, CR.PROCESS_CODE CHILD_PROCESS_CODE
, CR.DISPLAY_NAME CHILD_DISPLAY_NAME
, CR.ITEM_TYPE CHILD_ITEM_TYPE
, CR.NAME CHILD_NAME
, CR.PROCESS_REV_ID CHILD_PROCESS_REV_ID
, CR.APPROVAL_STATUS CHILD_APPROVAL_STATUS
, CR.START_DATE CHILD_START_DATE
, CR.APPROVAL_DATE CHILD_APPROVAL_DATE
, CR.APPROVAL_END_DATE CHILD_APPROVAL_END_DATE
, CR.END_DATE CHILD_END_DATE
, CR.CONTROL_COUNT CHILD_CONTROL_COUNT
, CR.RISK_COUNT CHILD_RISK_COUNT
, CR.ORG_COUNT CHILD_ORG_COUNT
, CR.SIGNIFICANT_PROCESS_FLAG CHILD_SIGNIFICANT_PROCESS_FLAG
, CR.STANDARD_PROCESS_FLAG CHILD_STANDARD_PROCESS_FLAG
, CR.CERTIFICATION_STATUS CHILD_CERTIFICATION_STATUS
, CR.PROCESS_CATEGORY CHILD_PROCESS_CATEGORY
, CR.PROCESS_OWNER_ID CHILD_PROCESS_OWNER_ID
, CR.FINANCE_OWNER_ID CHILD_FINANCE_OWNER_ID
, CR.APPLICATION_OWNER_ID CHILD_APPLICATION_OWNER_ID
, CR.STANDARD_VARIATION CHILD_STANDARD_VARIATION
, AH.CHILD_ORDER_NUMBER CHILD_ORDER_NUMBER
FROM AMW_PROCESS_VL PR
, AMW_PROCESS_VL CR
, AMW_APPROVED_HIERARCHIES AH
WHERE AH.PARENT_ID = PR.PROCESS_ID
AND AH.CHILD_ID = CR.PROCESS_ID
AND AH.START_DATE <= SYSDATE
AND (AH.END_DATE IS NULL OR AH.END_DATE > SYSDATE)
AND PR.APPROVAL_DATE <= SYSDATE
AND (PR.APPROVAL_END_DATE IS NULL OR PR.APPROVAL_END_DATE > SYSDATE)
AND CR.APPROVAL_DATE <= SYSDATE
AND (CR.APPROVAL_END_DATE IS NULL OR CR.APPROVAL_END_DATE > SYSDATE)
AND AH.ORGANIZATION_ID = -1
AND PR.DELETION_DATE IS NULL
AND CR.DELETION_DATE IS NULL UNION SELECT TO_NUMBER(NULL) PARENT_PROCESS_ID
, TO_NUMBER(NULL) PARENT_REVISION_NUMBER
, NULL PARTENT_PROCESS_CODE
, NULL PARENT_DISPLAY_NAME
, NULL PARENT_PROCESS_REV_ID
, CR.PROCESS_ID CHILD_PROCESS_ID
, CR.REVISION_NUMBER CHILD_REVISION_NUMBER
, CR.PROCESS_CODE CHILD_PROCESS_CODE
, CR.DISPLAY_NAME CHILD_DISPLAY_NAME
, CR.ITEM_TYPE CHILD_ITEM_TYPE
, CR.NAME CHILD_NAME
, CR.PROCESS_REV_ID CHILD_PROCESS_REV_ID
, CR.APPROVAL_STATUS CHILD_APPROVAL_STATUS
, CR.START_DATE CHILD_START_DATE
, CR.APPROVAL_DATE CHILD_APPROVAL_DATE
, CR.APPROVAL_END_DATE CHILD_APPROVAL_END_DATE
, CR.END_DATE CHILD_END_DATE
, CR.CONTROL_COUNT CHILD_CONTROL_COUNT
, CR.RISK_COUNT CHILD_RISK_COUNT
, CR.ORG_COUNT CHILD_ORG_COUNT
, CR.SIGNIFICANT_PROCESS_FLAG CHILD_SIGNIFICANT_PROCESS_FLAG
, CR.STANDARD_PROCESS_FLAG CHILD_STANDARD_PROCESS_FLAG
, CR.CERTIFICATION_STATUS CHILD_CERTIFICATION_STATUS
, CR.PROCESS_CATEGORY CHILD_PROCESS_CATEGORY
, CR.PROCESS_OWNER_ID CHILD_PROCESS_OWNER_ID
, CR.FINANCE_OWNER_ID CHILD_FINANCE_OWNER_ID
, CR.APPLICATION_OWNER_ID CHILD_APPLICATION_OWNER_ID
, CR.STANDARD_VARIATION CHILD_STANDARD_VARIATION
, TO_NUMBER(NULL) CHILD_ORDER_NUMBER
FROM AMW_PROCESS_VL CR
WHERE CR.PROCESS_ID = -1

Columns