eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AMW_WF_ORG_HIERARCHY_MAIN_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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