View: AMW_WF_HIERARCHY_ENTITIES_V

View Text

SELECT AMWP.PROCESS_REV_ID AS PROCESS_REV_ID
, WA_TL.DISPLAY_NAME AS PROCESS_DISPLAY_NAME
, AMWP.PROCESS_ID AS PROCESS_ID
, WA.NAME AS NAME
, WA.ITEM_TYPE AS ITEM_TYPE
, AMWP.SIGNIFICANT_PROCESS_FLAG AS SIGNIFICANT_PROCESS_FLAG
, AMWP.STANDARD_PROCESS_FLAG AS STANDARD_PROCESS_FLAG
, AMWP.APPROVAL_STATUS AS APPROVAL_STATUS
, DECODE (AMWP.PROCESS_ID
, -1
, TO_NUMBER(NULL)
, AMWP.RISK_COUNT) AS RISK_COUNT
, DECODE (AMWP.PROCESS_ID
, -1
, TO_NUMBER(NULL)
, AMWP.CONTROL_COUNT) AS CONTROL_COUNT
, DECODE (AMWP.PROCESS_ID
, -1
, TO_NUMBER(NULL)
, AMWP.ORG_COUNT) AS ORG_COUNT
, AMWP.PROCESS_OWNER_ID AS PROCESS_OWNER_ID
, HZP1.PARTY_NAME AS PROCESS_OWNER_NAME
, AMWP.CERTIFICATION_STATUS AS CERTIFICATION_STATUS_CODE
, AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_CERTIFICATION_STATUS'
, AMWP.CERTIFICATION_STATUS) AS CERTIFICATION_STATUS
, AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_PROCESS_APPROVAL_STATUS'
, AMWP.APPROVAL_STATUS) AS APPROVAL_STATUS_MEANING
, AMWP.APPLICATION_OWNER_ID AS APPLICATION_OWNER_ID
, HZP2.PARTY_NAME AS APPLICATION_OWNER_NAME
, AMWP.PROCESS_CATEGORY AS PROCESS_CATEGORY
, AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_PROCESS_CATEGORY'
, AMWP.PROCESS_CATEGORY) AS PROCESS_CATEGORY_MEANING
, DECODE (AMWP.PROCESS_ID
, -1
, NULL
, 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=') AS URLSTRING
, AMWP.FINANCE_OWNER_ID AS FINANCE_OWNER_ID
, HZP3.PARTY_NAME AS FINANCE_OWNER_NAME
, DECODE (AMWP.PROCESS_ID
, -1
, NULL
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMWP.PROCESS_ID||'&PROCESSREVID='||AMWP.PROCESS_REV_ID||'&POBJECTCONTEXT=PROCESS&GOTOTAB=R&ADDBREADCRUMB=Y&RETAINAM=Y') AS RISKCNTURL
, DECODE (AMWP.PROCESS_ID
, -1
, NULL
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMWP.PROCESS_ID||'&PROCESSREVID='||AMWP.PROCESS_REV_ID||'&POBJECTCONTEXT=PROCESS&GOTOTAB=C&ADDBREADCRUMB=Y&RETAINAM=Y') AS CTRLCNTURL
, DECODE (AMWP.PROCESS_ID
, -1
, NULL
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||AMWP.PROCESS_ID||'&PROCESSREVID='||AMWP.PROCESS_REV_ID||'&POBJECTCONTEXT=PROCESS&GOTOTAB=O&ADDBREADCRUMB=Y&RETAINAM=Y') AS ORGCNTURL
, DECODE (AMWP.PROCESS_ID
, -1
, NULL
, WA.NAME) AS NAMEFORURL
FROM AMW_PROCESS AMWP
, WF_ACTIVITIES WA
, WF_ACTIVITIES_TL WA_TL
, HZ_PARTIES HZP1
, HZ_PARTIES HZP2
, HZ_PARTIES HZP3
WHERE 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')
AND AMWP.NAME (+) = WA.NAME
AND AMWP.ITEM_TYPE (+) = WA.ITEM_TYPE
AND WA.END_DATE IS NULL
AND HZP1.PARTY_ID (+) = AMWP.PROCESS_OWNER_ID
AND HZP2.PARTY_ID (+) = AMWP.APPLICATION_OWNER_ID
AND HZP3.PARTY_ID (+) = AMWP.FINANCE_OWNER_ID

Columns