VIEW: APPS.AMW_ALL_HIER_CHILDREN_V

Source

View Text - Preformatted

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(+)

View Text - HTML Formatted

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(+)