VIEW: APPS.AMW_WF_HIERARCHY_ENTITIES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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