VIEW: APPS.AMW_LATEST_HIERARCHY_RL_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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