VIEW: APPS.AMW_CURR_APP_HIERARCHY_RL_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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