View: AMW_WF_ORG_HIERARCHY_MAIN_V

Product:	AMW - Internal Controls Manager
Description:	This view returns the Details about Parent Child Process Hierarchy in Organization Context
Implementation/DBA Data:	APPS.AMW_WF_ORG_HIERARCHY_MAIN_V

View Text

SELECT WPA.PROCESS_NAME PARENT_PROCESS_NAME
, WPA.PROCESS_ITEM_TYPE PARENT_ITEM_TYPE
, WPA.ACTIVITY_NAME CHILD_PROCESS_NAME
, WPA.ACTIVITY_ITEM_TYPE CHILD_ITEM_TYPE
, AMWP1.PROCESS_ID PARENT_PROCESS_ID
, AMWP2.PROCESS_ID CHILD_PROCESS_ID
, INSTANCE_ID INSTANCE_ID
, AMW_WF_HIERARCHY_PKG.FIND_TRANSITION_ORDER(INSTANCE_ID) TRANSITION_REVERSE_ORDER
, AMPO.PROCESS_ORGANIZATION_ID PROCESS_ORG_ID
, AMPO.ORGANIZATION_ID ORG_ID
, AMPO.AUDIT_UNIT ORG_NAME
, AMPO.DISPLAY_NAME PROCESS_DISPLAY_NAME
, AMPO.PROCESS_ID PROCESS_ID
, AMPO.NAME NAME
, AMPO.ITEM_TYPE ITEM_TYPE
, AMPO.SIGNIFICANT_PROCESS_FLAG SIGNIFICANT_PROCESS_FLAG
, AMPO.STANDARD_PROCESS_FLAG STANDARD_PROCESS_FLAG
, AMPO.APPROVAL_STATUS APPROVAL_STATUS
, DECODE (AMPO.PROCESS_ID
, -1
, TO_NUMBER(NULL)
, AMPO.RISK_COUNT) RISK_COUNT
, DECODE (AMPO.PROCESS_ID
, -1
, TO_NUMBER(NULL)
, AMPO.CONTROL_COUNT) CONTROL_COUNT
, AMPO.PROCESS_OWNER_ID PROCESS_OWNER_ID
, AMPO.PARTY_NAME PROCESS_OWNER_NAME
, AMPO.CERTIFICATION_STATUS CERTIFICATION_STATUS_CODE
, AMWLKUP.MEANING CERTIFICATION_STATUS
, DECODE (AMPO.PROCESS_ID
, -1
, NULL
, 'OA.JSP?OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMPO.PROCESS_ID||'&PROCESSORGID='||AMPO.PROCESS_ORGANIZATION_ID||'&ORGID='||AMPO.ORGANIZATION_ID||'&POBJECTCONTEXT=PROCESS_ORG&GOTOTAB=B&ADDBREADCRUMB=Y&RETAINAM=Y') URLSTRING
, DECODE (AMPO.PROCESS_ID
, -1
, NULL
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMPO.PROCESS_ID||'&PROCESSORGID='||AMPO.PROCESS_ORGANIZATION_ID||'&ORGID='||AMPO.ORGANIZATION_ID||'&POBJECTCONTEXT=PROCESS_ORG&GOTOTAB=R&ADDBREADCRUMB=Y&RETAINAM=Y') RISKCNTURL
, DECODE (AMPO.PROCESS_ID
, -1
, NULL
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMPO.PROCESS_ID||'&PROCESSORGID='||AMPO.PROCESS_ORGANIZATION_ID||'&ORGID='||AMPO.ORGANIZATION_ID||'&POBJECTCONTEXT=PROCESS_ORG&GOTOTAB=C&ADDBREADCRUMB=Y&RETAINAM=Y') CTRLCNTURL
FROM WF_PROCESS_ACTIVITIES WPA
, WF_ACTIVITIES WAPR
, AMW_PROCESS AMWP1
, AMW_PROCESS AMWP2
, AMW_PROCESS_ORG_BASICINFO_V AMPO
, AMW_LOOKUPS AMWLKUP
WHERE WPA.PROCESS_NAME = WAPR.NAME
AND WPA.PROCESS_VERSION = WAPR.VERSION
AND WPA.PROCESS_ITEM_TYPE = WAPR.ITEM_TYPE
AND WAPR.END_DATE IS NULL
AND WPA.PROCESS_ITEM_TYPE = 'AUDITMGR'
AND WPA.ACTIVITY_NAME NOT IN ('START'
, 'END')
AND WPA.PROCESS_NAME <> 'ROOT'
AND AMWP1.NAME = WPA.PROCESS_NAME
AND AMWP2.NAME = WPA.ACTIVITY_NAME
AND AMPO.PROCESS_ID = AMWP2.PROCESS_ID
AND AMWLKUP.LOOKUP_TYPE (+)= 'AMW_CERTIFICATION_STATUS'
AND AMWLKUP.LOOKUP_CODE (+)= AMPO.CERTIFICATION_STATUS
AND AMPO.END_DATE IS NULL UNION ALL SELECT NULL PARENT_PROCESS_NAME
, NULL PARENT_ITEM_TYPE
, WA.NAME CHILD_PROCESS_NAME
, WA.ITEM_TYPE CHILD_ITEM_TYPE
, TO_NUMBER(NULL) PARENT_PROCESS_ID
, -1 CHILD_PROCESS_ID
, TO_NUMBER(NULL) INSTANCE_ID
, TO_NUMBER(NULL) TRANSITION_REVERSE_ORDER
, TO_NUMBER(NULL) PROCESS_ORG_ID
, AMPO.ORGANIZATION_ID ORG_ID
, NULL ORG_NAME
, WA_TL.DISPLAY_NAME PROCESS_DISPLAY_NAME
, -1 PROCESS_ID
, WA.NAME NAME
, WA.ITEM_TYPE ITEM_TYPE
, NULL SIGNIFICANT_PROCESS_FLAG
, NULL STANDARD_PROCESS_FLAG
, NULL APPROVAL_STATUS
, TO_NUMBER(NULL) RISK_COUNT
, TO_NUMBER(NULL) CONTROL_COUNT
, TO_NUMBER(NULL) PROCESS_OWNER_ID
, NULL PROCESS_OWNER_NAME
, NULL CERTIFICATION_STATUS_CODE
, NULL CERTIFICATION_STATUS
, NULL URLSTRING
, NULL RISKCNTURL
, NULL CTRLCNTURL
FROM WF_ACTIVITIES WA
, WF_ACTIVITIES_TL WA_TL
, (SELECT DISTINCT ORGANIZATION_ID
FROM AMW_PROCESS_ORGANIZATION) AMPO
WHERE WA.NAME = 'PROCESS'
AND WA.ITEM_TYPE ='AUDITMGR'
AND WA.END_DATE IS NULL
AND WA_TL.NAME = WA.NAME
AND WA_TL.VERSION = WA.VERSION
AND WA_TL.ITEM_TYPE = WA.ITEM_TYPE
AND WA_TL.LANGUAGE = USERENV('LANG') UNION SELECT 'PROCESS' PARENT_PROCESS_NAME
, 'AUDITMGR' PARENT_ITEM_TYPE
, AMPO.NAME CHILD_PROCESS_NAME
, AMPO.ITEM_TYPE CHILD_ITEM_TYPE
, -1 PARENT_PROCESS_ID
, AMPO.TOP_PROCESS_ID CHILD_PROCESS_ID
, TO_NUMBER(NULL) INSTANCE_ID
, TO_NUMBER(NULL) TRANSITION_REVERSE_ORDER
, AMPO.PROCESS_ORGANIZATION_ID PROCESS_ORG_ID
, AMPO.ORGANIZATION_ID ORG_ID
, AMPO.AUDIT_UNIT ORG_NAME
, AMPO.DISPLAY_NAME PROCESS_DISPLAY_NAME
, AMPO.TOP_PROCESS_ID PROCESS_ID
, AMPO.NAME NAME
, AMPO.ITEM_TYPE ITEM_TYPE
, AMPO.STANDARD_PROCESS_FLAG SIGNIFICANT_PROCESS_FLAG
, AMPO.STANDARD_PROCESS_FLAG STANDARD_PROCESS_FLAG
, AMPO.APPROVAL_STATUS APPROVAL_STATUS
, AMPO.RISK_COUNT RISK_COUNT
, AMPO.CONTROL_COUNT CONTROL_COUNT
, AMPO.PROCESS_OWNER_ID PROCESS_OWNER_ID
, AMPO.PARTY_NAME PROCESS_OWNER_NAME
, AMPO.CERTIFICATION_STATUS CERTIFICATION_STATUS_CODE
, AMWLKUP.MEANING CERTIFICATION_STATUS
, 'OA.JSP?OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMPO.PROCESS_ID||'&PROCESSORGID='||AMPO.PROCESS_ORGANIZATION_ID||'&ORGID='||AMPO.ORGANIZATION_ID||'&POBJECTCONTEXT=PROCESS_ORG&GOTOTAB=B&ADDBREADCRUMB=Y&RETAINAM=Y' URLSTRING
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMPO.PROCESS_ID||'&PROCESSORGID='||AMPO.PROCESS_ORGANIZATION_ID||'&ORGID='||AMPO.ORGANIZATION_ID||'&POBJECTCONTEXT=PROCESS_ORG&GOTOTAB=R&ADDBREADCRUMB=Y&RETAINAM=Y' RISKCNTURL
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMPO.PROCESS_ID||'&PROCESSORGID='||AMPO.PROCESS_ORGANIZATION_ID||'&ORGID='||AMPO.ORGANIZATION_ID||'&POBJECTCONTEXT=PROCESS_ORG&GOTOTAB=C&ADDBREADCRUMB=Y&RETAINAM=Y' CTRLCNTURL
FROM AMW_PROCESS_ORG_BASICINFO_V AMPO
, AMW_LOOKUPS AMWLKUP
WHERE AMPO.TOP_PROCESS_ID IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM AMW_PROCESS_HIERARCHY_V
WHERE PARENT_PROCESS_ID = -1
AND CHILD_PROCESS_ID = AMPO.TOP_PROCESS_ID)
AND AMWLKUP.LOOKUP_TYPE (+)= 'AMW_CERTIFICATION_STATUS'
AND AMWLKUP.LOOKUP_CODE (+)= AMPO.CERTIFICATION_STATUS
AND AMPO.END_DATE IS NULL

Columns