View: AMW_LATEST_HIERARCHY_RL_V

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

View Text

SELECT PR.PROCESS_ID
, PR.REVISION_NUMBER
, PR.NAME
, PR.ITEM_TYPE
, PR.PROCESS_CODE
, PR.DISPLAY_NAME
, PR.PROCESS_REV_ID
, PR.APPROVAL_STATUS
, CR.PROCESS_ID
, CR.REVISION_NUMBER
, CR.PROCESS_CODE
, CR.DISPLAY_NAME
, CR.ITEM_TYPE
, CR.NAME
, CR.PROCESS_REV_ID
, CR.APPROVAL_STATUS
, CR.START_DATE
, CR.APPROVAL_DATE
, CR.APPROVAL_END_DATE
, CR.END_DATE
, CR.CONTROL_COUNT_LATEST
, CR.RISK_COUNT_LATEST
, CR.ORG_COUNT
, CR.SIGNIFICANT_PROCESS_FLAG
, CR.STANDARD_PROCESS_FLAG
, CR.CERTIFICATION_STATUS
, CR.PROCESS_CATEGORY
, CR.PROCESS_OWNER_ID
, CR.FINANCE_OWNER_ID
, CR.APPLICATION_OWNER_ID
, CR.STANDARD_VARIATION
, AH.CHILD_ORDER_NUMBER
, NVL((SELECT 0
FROM DUAL
WHERE EXISTS (SELECT 0
FROM AMW_PROCESS_LOCKS
WHERE LOCKED_PROCESS_ID=CR.PROCESS_ID
AND ORGANIZATION_ID = -1))
, 1) CHILD_ADD_SWITCHER
, NVL((SELECT 0
FROM DUAL
WHERE EXISTS (SELECT 0
FROM AMW_PROCESS_LOCKS
WHERE LOCKED_PROCESS_ID=CR.PROCESS_ID
AND LOCKING_PROCESS_ID <> LOCKED_PROCESS_ID
AND ORGANIZATION_ID = -1))
, 1) CHILD_DELETE_SWITCHER
FROM AMW_PROCESS_VL PR
, AMW_PROCESS_VL CR
, AMW_LATEST_HIERARCHIES AH
WHERE AH.PARENT_ID = PR.PROCESS_ID
AND AH.CHILD_ID = CR.PROCESS_ID
AND AH.ORGANIZATION_ID = -1
AND PR.END_DATE IS NULL
AND CR.END_DATE IS NULL
AND PR.DELETION_DATE IS NULL
AND CR.DELETION_DATE IS NULL UNION SELECT DISTINCT TO_NUMBER(NULL) PARENT_PROCESS_ID
, TO_NUMBER(NULL) PARENT_REVISION_NUMBER
, NULL PARENT_NAME
, NULL PARENT_ITEM_TYPE
, NULL PARENT_PROCESS_CODE
, NULL PARENT_DISPLAY_NAME
, NULL PARENT_PROCESS_REV_ID
, NULL PARENT_APPROVAL_STATUS
, 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_LATEST CHILD_CONTROL_COUNT
, CR.RISK_COUNT_LATEST 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
, 1 CHILD_ADD_SWITCHER
, 0 CHILD_DELETE_SWITCHER
FROM AMW_PROCESS_VL CR
, (SELECT DISTINCT PARENT_ID
FROM AMW_LATEST_HIERARCHIES
WHERE PARENT_ID NOT IN (SELECT CHILD_ID
FROM AMW_LATEST_HIERARCHIES
WHERE ORGANIZATION_ID = -1)
AND ORGANIZATION_ID = -1) AH
WHERE AH.PARENT_ID = CR.PROCESS_ID
AND CR.END_DATE IS NULL
AND CR.DELETION_DATE IS NULL

Columns