View: AMW_ALL_HIER_CHILDREN_V

Product:	AMW - Internal Controls Manager
Description:	View for all the process hierarchy descendents in approved and latest hierarchies.
Implementation/DBA Data:	APPS.AMW_ALL_HIER_CHILDREN_V

View Text

SELECT PR.PROCESS_ID PARENT_ID
, PR.REVISION_NUMBER PARENT_REV_NUM
, CH.PROCESS_CODE CHILD_PROCESS_CODE
, CH.PROCESS_ID CHILD_ID
, CH.REVISION_NUMBER CHILD_REV_NUM
, CH.DISPLAY_NAME CHILD_NAME
, CH.STANDARD_PROCESS_FLAG CHILD_STANDARD_PROCESS_FLAG
, ACH.PROCESS_ID CHILD_STD_VAR_PROCESS_ID
, 'A' HIERARCHY_TYPE
FROM AMW_PROCESS_VL PR
, AMW_PROCESS_VL CH
, AMW_PROCESS ACH
, AMW_APPROVED_HIERARCHIES AH
WHERE (AH.ORGANIZATION_ID IS NULL OR AH.ORGANIZATION_ID = -1)
AND AH.END_DATE IS NULL
AND AH.PARENT_ID = PR.PROCESS_ID
AND PR.APPROVAL_DATE <= AH.START_DATE
AND (PR.APPROVAL_END_DATE IS NULL OR PR.APPROVAL_END_DATE > AH.START_DATE)
AND AH.CHILD_ID = CH.PROCESS_ID
AND CH.APPROVAL_DATE <= AH.START_DATE
AND (CH.APPROVAL_END_DATE IS NULL OR CH.APPROVAL_END_DATE > AH.START_DATE)
AND CH.STANDARD_VARIATION = ACH.PROCESS_REV_ID(+) UNION SELECT PR.PROCESS_ID PARENT_ID
, PR.REVISION_NUMBER PARENT_REV_NUM
, CH.PROCESS_CODE CHILD_PROCESS_CODE
, CH.PROCESS_ID CHILD_ID
, CH.REVISION_NUMBER CHILD_REV_NUM
, CH.DISPLAY_NAME CHILD_NAME
, CH.STANDARD_PROCESS_FLAG CHILD_STANDARD_PROCESS_FLAG
, ACH.PROCESS_ID CHILD_STD_VAR_PROCESS_ID
, 'L' HIERARCHY_TYPE
FROM AMW_PROCESS_VL PR
, AMW_PROCESS_VL CH
, AMW_PROCESS ACH
, AMW_LATEST_HIERARCHIES LH
WHERE (LH.ORGANIZATION_ID IS NULL OR LH.ORGANIZATION_ID = -1)
AND LH.PARENT_ID = PR.PROCESS_ID
AND PR.END_DATE IS NULL
AND LH.CHILD_ID = CH.PROCESS_ID
AND CH.END_DATE IS NULL
AND CH.STANDARD_VARIATION = ACH.PROCESS_REV_ID(+)

Columns