View: AMW_WF_HIERARCHY_MAIN_V

Product:	AMW - Internal Controls Manager
Description:	This view returns the Details about Parent Child Process Hierarchy
Implementation/DBA Data:	APPS.AMW_WF_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
, (SELECT MAX(INSTANCE_ID)
FROM WF_PROCESS_ACTIVITIES
WHERE PROCESS_NAME = WPA.PROCESS_NAME
AND PROCESS_ITEM_TYPE = WPA.PROCESS_ITEM_TYPE
AND ACTIVITY_NAME = WPA.ACTIVITY_NAME
AND ACTIVITY_ITEM_TYPE = WPA.ACTIVITY_ITEM_TYPE) INSTANCE_ID
, AMW_WF_HIERARCHY_PKG.FIND_TRANSITION_ORDER((SELECT MAX(INSTANCE_ID)
FROM WF_PROCESS_ACTIVITIES
WHERE PROCESS_NAME = WPA.PROCESS_NAME
AND PROCESS_ITEM_TYPE = WPA.PROCESS_ITEM_TYPE
AND ACTIVITY_NAME = WPA.ACTIVITY_NAME
AND ACTIVITY_ITEM_TYPE = WPA.ACTIVITY_ITEM_TYPE)) TRANSITION_REVERSE_ORDER
, AMWP.PROCESS_REV_ID PROCESS_REV_ID
, WA_TL.DISPLAY_NAME PROCESS_DISPLAY_NAME
, AMWP.PROCESS_ID PROCESS_ID
, AMWP.SIGNIFICANT_PROCESS_FLAG SIGNIFICANT_PROCESS_FLAG
, AMWP.STANDARD_PROCESS_FLAG STANDARD_PROCESS_FLAG
, AMWP.APPROVAL_STATUS APPROVAL_STATUS
, AMWP.RISK_COUNT RISK_COUNT
, AMWP.CONTROL_COUNT CONTROL_COUNT
, AMWP.ORG_COUNT ORG_COUNT
, AMWP.PROCESS_OWNER_ID PROCESS_OWNER_ID
, HZP1.PARTY_NAME PROCESS_OWNER_NAME
, AMWP.CERTIFICATION_STATUS CERTIFICATION_STATUS_CODE
, AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_CERTIFICATION_STATUS'
, AMWP.CERTIFICATION_STATUS) CERTIFICATION_STATUS
, AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_PROCESS_APPROVAL_STATUS'
, AMWP.APPROVAL_STATUS) APPROVAL_STATUS_MEANING
, AMWP.APPLICATION_OWNER_ID APPLICATION_OWNER_ID
, HZP2.PARTY_NAME APPLICATION_OWNER_NAME
, AMWP.PROCESS_CATEGORY PROCESS_CATEGORY
, AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_PROCESS_CATEGORY'
, AMWP.PROCESS_CATEGORY) PROCESS_CATEGORY_MEANING
, DECODE (AMWP.PROCESS_ID
, NULL
, 'OA.JSP?OAFUNC=AMW_RISKLIB_EDIT_PROCESS&PCALLINGPAGE=AMW_RISKLIBRARY_PROCESSES&ADDBREADCRUMB=Y&RETAINAM=Y&PNAME='
, 'OA.JSP?OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMWP.PROCESS_ID||'&PROCESSREVID='||AMWP.PROCESS_REV_ID||'&POBJECTCONTEXT=PROCESS&ADDBREADCRUMB=Y&RETAINAM=Y&PNAME=') URLSTRING
, AMWP.FINANCE_OWNER_ID FINANCE_OWNER_ID
, HZP3.PARTY_NAME FINANCE_OWNER_NAME
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMWP.PROCESS_ID||'&PROCESSREVID='||AMWP.PROCESS_REV_ID||'&POBJECTCONTEXT=PROCESS&OA_SUBTABIDX=2>AB=2&ADDBREADCRUMB=Y&RETAINAM=Y' RISKCNTURL
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMWP.PROCESS_ID||'&PROCESSREVID='||AMWP.PROCESS_REV_ID||'&POBJECTCONTEXT=PROCESS&OA_SUBTABIDX=3>AB=3&ADDBREADCRUMB=Y&RETAINAM=Y' CTRLCNTURL
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMWP.PROCESS_ID||'&PROCESSREVID='||AMWP.PROCESS_REV_ID||'&POBJECTCONTEXT=PROCESS&OA_SUBTABIDX=1&ADDBREADCRUMB=Y&RETAINAM=Y' ORGCNTURL
, WACH.NAME NAMEFORURL
FROM (SELECT DISTINCT PROCESS_NAME
, PROCESS_ITEM_TYPE
, ACTIVITY_NAME
, ACTIVITY_ITEM_TYPE
FROM WF_PROCESS_ACTIVITIES A
, WF_ACTIVITIES B
WHERE A.PROCESS_NAME = B.NAME
AND A.PROCESS_VERSION = B.VERSION
AND A.PROCESS_ITEM_TYPE = B.ITEM_TYPE
AND B.END_DATE IS NULL
AND A.PROCESS_ITEM_TYPE = 'AUDITMGR'
AND B.TYPE = 'PROCESS') WPA
, AMW_PROCESS AMWP
, WF_ACTIVITIES WACH
, WF_ACTIVITIES_TL WA_TL
, HZ_PARTIES HZP1
, HZ_PARTIES HZP2
, HZ_PARTIES HZP3
WHERE WACH.NAME = WPA.ACTIVITY_NAME
AND WACH.ITEM_TYPE = WPA.ACTIVITY_ITEM_TYPE
AND WACH.END_DATE IS NULL
AND WACH.TYPE = 'PROCESS'
AND WA_TL.NAME = WACH.NAME
AND WA_TL.VERSION = WACH.VERSION
AND WA_TL.ITEM_TYPE = WACH.ITEM_TYPE
AND WA_TL.LANGUAGE = USERENV('LANG')
AND AMWP.NAME (+) = WACH.NAME
AND AMWP.ITEM_TYPE (+) = WACH.ITEM_TYPE
AND HZP1.PARTY_ID (+) = AMWP.PROCESS_OWNER_ID
AND HZP2.PARTY_ID (+) = AMWP.APPLICATION_OWNER_ID
AND HZP3.PARTY_ID (+) = AMWP.FINANCE_OWNER_ID UNION SELECT NULL PARENT_PROCESS_NAME
, NULL PARENT_ITEM_TYPE
, WA.NAME CHILD_PROCESS_NAME
, WA.ITEM_TYPE CHILD_ITEM_TYPE
, TO_NUMBER(NULL) INSTANCE_ID
, TO_NUMBER(NULL) TRANSITION_REVERSE_ORDER
, -1 PROCESS_REV_ID
, WA_TL.DISPLAY_NAME PROCESS_DISPLAY_NAME
, -1 PROCESS_ID
, NULL SIGNIFICANT_PROCESS_FLAG
, NULL STANDARD_PROCESS_FLAG
, NULL APPROVAL_STATUS
, TO_NUMBER(NULL) RISK_COUNT
, TO_NUMBER(NULL) CONTROL_COUNT
, TO_NUMBER(NULL) ORG_COUNT
, TO_NUMBER(NULL) PROCESS_OWNER_ID
, NULL PROCESS_OWNER_NAME
, NULL CERTIFICATION_STATUS_CODE
, NULL CERTIFICATION_STATUS
, NULL APPROVAL_STATUS_MEANING
, TO_NUMBER(NULL) APPLICATION_OWNER_ID
, NULL APPLICATION_OWNER_NAME
, NULL PROCESS_CATEGORY
, NULL PROCESS_CATEGORY_MEANING
, NULL URLSTRING
, TO_NUMBER(NULL) FINANCE_OWNER_ID
, NULL FINANCE_OWNER_NAME
, NULL RISKCNTURL
, NULL CTRLCNTURL
, NULL ORGCNTURL
, NULL NAMEFORURL
FROM WF_ACTIVITIES WA
, WF_ACTIVITIES_TL WA_TL
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')

Columns