View: AMW_ORG_RELATIONS_V

Product:	AMW - Internal Controls Manager
Description:	Stores Information of organization relations
Implementation/DBA Data:	APPS.AMW_ORG_RELATIONS_V

View Text

SELECT APOBV1.NAME PARENT_PROCESS_NAME
, 'AUDITMGR' PARENT_ITEM_TYPE
, APOBV2.NAME CHILD_PROCESS_NAME
, 'AUDITMGR' CHILD_ITEM_TYPE
, APOR.PARENT_PROCESS_ID PARENT_PROCESS_ID
, APOR.CHILD_PROCESS_ID CHILD_PROCESS_ID
, APOR.INSTANCE_ID INSTANCE_ID
, AMW_WF_HIERARCHY_PKG.FIND_TRANSITION_ORDER(INSTANCE_ID) TRANSITION_REVERSE_ORDER
, APOBV2.PROCESS_ORGANIZATION_ID PROCESS_ORG_ID
, APOBV2.ORGANIZATION_ID ORG_ID
, APOBV2.AUDIT_UNIT ORG_NAME
, APOBV2.DISPLAY_NAME PROCESS_DISPLAY_NAME
, APOBV2.PROCESS_ID PROCESS_ID
, APOBV2.NAME NAME
, APOBV2.ITEM_TYPE ITEM_TYPE
, APOBV2.SIGNIFICANT_PROCESS_FLAG SIGNIFICANT_PROCESS_FLAG
, APOBV2.STANDARD_PROCESS_FLAG STANDARD_PROCESS_FLAG
, APOBV2.APPROVAL_STATUS APPROVAL_STATUS
, APOBV2.RISK_COUNT RISK_COUNT
, APOBV2.CONTROL_COUNT CONTROL_COUNT
, APOBV2.PROCESS_OWNER_ID PROCESS_OWNER_ID
, APOBV2.PARTY_NAME PROCESS_OWNER_NAME
, APOBV2.CERTIFICATION_STATUS CERTIFICATION_STATUS_CODE
, AMW_UTILITY_PVT.GET_PROC_ORG_OPINION_STATUS(APOR.CHILD_PROCESS_ID
, APOBV2.ORGANIZATION_ID
, 'CERTIFICATION') CERTIFICATION_STATUS
, 'OA.JSP?OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||APOBV2.PROCESS_ID||'&PROCESSORGID='||APOBV2.PROCESS_ORGANIZATION_ID||'&ORGID='||APOBV2.ORGANIZATION_ID||'&POBJECTCONTEXT=PROCESS_ORG&OA_SUBTABIDX=0&ADDBREADCRUMB=Y&RETAINAM=Y' URLSTRING
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||APOBV2.PROCESS_ID||'&PROCESSORGID='||APOBV2.PROCESS_ORGANIZATION_ID||'&ORGID='||APOBV2.ORGANIZATION_ID||'&POBJECTCONTEXT=PROCESS_ORG&OA_SUBTABIDX=2>AB=2&ADDBREADCRUMB=Y&RETAINAM=Y' RISKCNTURL
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||APOBV2.PROCESS_ID||'&PROCESSORGID='||APOBV2.PROCESS_ORGANIZATION_ID||'&ORGID='||APOBV2.ORGANIZATION_ID||'&POBJECTCONTEXT=PROCESS_ORG&OA_SUBTABIDX=3>AB=3&ADDBREADCRUMB=Y&RETAINAM=Y' CTRLCNTURL
FROM AMW_PROCESS_ORG_RELATIONS APOR
, AMW_PROCESS_ORG_BASICINFO_V APOBV1
, AMW_PROCESS_ORG_BASICINFO_V APOBV2
WHERE APOBV1.ORGANIZATION_ID = APOR.ORGANIZATION_ID
AND APOBV1.PROCESS_ID = APOR.PARENT_PROCESS_ID
AND APOBV2.ORGANIZATION_ID = APOR.ORGANIZATION_ID
AND APOBV2.PROCESS_ID = APOR.CHILD_PROCESS_ID
AND APOBV1.END_DATE IS NULL
AND APOBV2.END_DATE IS NULL UNION SELECT 'PROCESS' PARENT_PROCESS_NAME
, 'AUDITMGR' PARENT_ITEM_TYPE
, APOBV2.NAME CHILD_PROCESS_NAME
, 'AUDITMGR' CHILD_ITEM_TYPE
, APOR.PARENT_PROCESS_ID PARENT_PROCESS_ID
, APOR.CHILD_PROCESS_ID CHILD_PROCESS_ID
, APOR.INSTANCE_ID INSTANCE_ID
, AMW_WF_HIERARCHY_PKG.FIND_TRANSITION_ORDER(INSTANCE_ID) TRANSITION_REVERSE_ORDER
, APOBV2.PROCESS_ORGANIZATION_ID PROCESS_ORG_ID
, APOBV2.ORGANIZATION_ID ORG_ID
, APOBV2.AUDIT_UNIT ORG_NAME
, APOBV2.DISPLAY_NAME PROCESS_DISPLAY_NAME
, APOBV2.PROCESS_ID PROCESS_ID
, APOBV2.NAME NAME
, APOBV2.ITEM_TYPE ITEM_TYPE
, APOBV2.SIGNIFICANT_PROCESS_FLAG SIGNIFICANT_PROCESS_FLAG
, APOBV2.STANDARD_PROCESS_FLAG STANDARD_PROCESS_FLAG
, APOBV2.APPROVAL_STATUS APPROVAL_STATUS
, APOBV2.RISK_COUNT RISK_COUNT
, APOBV2.CONTROL_COUNT CONTROL_COUNT
, APOBV2.PROCESS_OWNER_ID PROCESS_OWNER_ID
, APOBV2.PARTY_NAME PROCESS_OWNER_NAME
, APOBV2.CERTIFICATION_STATUS CERTIFICATION_STATUS_CODE
, AMW_UTILITY_PVT.GET_PROC_ORG_OPINION_STATUS(APOR.CHILD_PROCESS_ID
, APOBV2.ORGANIZATION_ID
, 'CERTIFICATION') CERTIFICATION_STATUS
, 'OA.JSP?OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||APOBV2.PROCESS_ID||'&PROCESSORGID='||APOBV2.PROCESS_ORGANIZATION_ID||'&ORGID='||APOBV2.ORGANIZATION_ID||'&POBJECTCONTEXT=PROCESS_ORG&OA_SUBTABIDX=0&ADDBREADCRUMB=Y&RETAINAM=Y' URLSTRING
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||APOBV2.PROCESS_ID||'&PROCESSORGID='||APOBV2.PROCESS_ORGANIZATION_ID||'&ORGID='||APOBV2.ORGANIZATION_ID||'&POBJECTCONTEXT=PROCESS_ORG&OA_SUBTABIDX=2>AB=2&ADDBREADCRUMB=Y&RETAINAM=Y' RISKCNTURL
, 'OAFUNC=AMW_PROCESS_DETAILS&PROCESSID='||APOBV2.PROCESS_ID||'&PROCESSORGID='||APOBV2.PROCESS_ORGANIZATION_ID||'&ORGID='||APOBV2.ORGANIZATION_ID||'&POBJECTCONTEXT=PROCESS_ORG&OA_SUBTABIDX=3>AB=3&ADDBREADCRUMB=Y&RETAINAM=Y' CTRLCNTURL
FROM AMW_PROCESS_ORG_RELATIONS APOR
, AMW_PROCESS_ORG_BASICINFO_V APOBV2
WHERE APOR.PARENT_PROCESS_ID = -1
AND APOBV2.ORGANIZATION_ID = APOR.ORGANIZATION_ID
AND APOBV2.PROCESS_ID = APOR.CHILD_PROCESS_ID
AND APOBV2.END_DATE IS NULL UNION SELECT NULL PARENT_PROCESS_NAME
, NULL PARENT_ITEM_TYPE
, 'PROCESS' CHILD_PROCESS_NAME
, 'AUDITMGR' 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
, AMW_UTILITY_PVT.GET_MESSAGE_TEXT('AMW_ASSIGNED_PROCESSES') PROCESS_DISPLAY_NAME
, -1 PROCESS_ID
, 'PROCESS' NAME
, 'AUDITMGR' 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')

Columns