View: AMW_OWNER_ROLES_V

Product:	AMW - Internal Controls Manager
Description:	View for all the owners of process, risk controls.
Implementation/DBA Data:	APPS.AMW_OWNER_ROLES_V

View Text

SELECT 'A' GRANTEE_TYPE
, 'A1' NAME_LINK
, GRANTS.GRANT_GUID GRANT_GUID
, GRANTS.START_DATE START_DATE
, GRANTS.END_DATE END_DATE
, GRANTS.INSTANCE_TYPE OBJECT_KEY_TYPE
, GRANTS.INSTANCE_PK1_VALUE OBJECT_KEY
, HZ_FORMAT_PUB.FORMAT_NAME(GRANTEE_GLOBAL.PARTY_ID) PARTY_NAME_ORIG
, (SELECT DISTINCT FULL_NAME
FROM AMW_EMPLOYEES_CURRENT_V
WHERE PARTY_ID=GRANTEE_GLOBAL.PARTY_ID) PARTY_NAME
, NULL COMPANY_NAME
, -1 COMPANY_ID
, GRANTEE_GLOBAL.PARTY_ID PARTY_ID
, GRANTED_MENU.MENU_NAME ROLE_NAME
, GRANTED_MENU.MENU_NAME ROLE_DESCRIPTION
, OBJ.OBJ_NAME OBJECT_NAME
, GRANTED_MENU.MENU_ID MENU_ID
, 'EGOROLEGRANTTABLEVIEWROLENAME' SWITCHERCOL
, MENUTL.USER_MENU_NAME ROLENAMELINK
, GRANTS.INSTANCE_PK1_VALUE PK1_VALUE
, GRANTS.INSTANCE_PK2_VALUE PK2_VALUE
, GRANTS.INSTANCE_PK3_VALUE PK3_VALUE
, GRANTS.INSTANCE_PK4_VALUE PK4_VALUE
, GRANTS.INSTANCE_PK5_VALUE PK5_VALUE
, GRANTS.INSTANCE_SET_ID INSTANCE_SET_ID
, GRANTS.ROWID AS ROW_ID
, FU.USER_NAME USER_NAME
FROM FND_GRANTS GRANTS
, HZ_PARTIES GRANTEE_GLOBAL
, FND_MENUS GRANTED_MENU
, FND_OBJECTS OBJ
, FND_MENUS_TL MENUTL
, FND_USER FU
WHERE GRANTED_MENU.MENU_NAME IN ('AMW_CTRL_OWNER_ROLE'
, 'AMW_AP_OWNER_ROLE'
, 'AMW_RL_PROC_OWNER_ROLE'
, 'AMW_RISK_OWNER_ROLE'
, 'AMW_RL_PROC_APPL_OWNER_ROLE'
, 'AMW_RL_PROC_FINANCE_OWNER_ROLE')
AND GRANTS.OBJECT_ID = OBJ.OBJECT_ID
AND GRANTS.GRANTEE_TYPE = 'GLOBAL'
AND NVL(GRANTS.END_DATE
, SYSDATE+1) >= TRUNC(SYSDATE)
AND GRANTS.MENU_ID = GRANTED_MENU.MENU_ID
AND GRANTS.MENU_ID = MENUTL.MENU_ID
AND MENUTL.LANGUAGE=USERENV('LANG')
AND GRANTEE_GLOBAL.PARTY_ID = -1000
AND GRANTS.INSTANCE_TYPE = 'INSTANCE'
AND GRANTEE_GLOBAL.PARTY_ID=FU.PERSON_PARTY_ID UNION ALL SELECT 'A' GRANTEE_TYPE
, 'A1' NAME_LINK
, GRANTS.GRANT_GUID GRANT_GUID
, GRANTS.START_DATE START_DATE
, GRANTS.END_DATE END_DATE
, GRANTS.INSTANCE_TYPE OBJECT_KEY_TYPE
, GRANTS.INSTANCE_PK1_VALUE OBJECT_KEY
, HZ_FORMAT_PUB.FORMAT_NAME(GRANTEE_GLOBAL.PARTY_ID) PARTY_NAME_ORIG
, (SELECT DISTINCT FULL_NAME
FROM AMW_EMPLOYEES_CURRENT_V
WHERE PARTY_ID=GRANTEE_GLOBAL.PARTY_ID) PARTY_NAME
, NULL COMPANY_NAME
, -1 COMPANY_ID
, GRANTEE_GLOBAL.PARTY_ID PARTY_ID
, GRANTED_MENU.MENU_NAME ROLE_NAME
, GRANTED_MENU.MENU_NAME ROLE_DESCRIPTION
, OBJ.OBJ_NAME OBJECT_NAME
, GRANTED_MENU.MENU_ID MENU_ID
, 'EGOROLEGRANTTABLEVIEWROLENAME' SWITCHERCOL
, MENUTL.USER_MENU_NAME ROLENAMELINK
, GRANTS.INSTANCE_PK1_VALUE PK1_VALUE
, GRANTS.INSTANCE_PK2_VALUE PK2_VALUE
, GRANTS.INSTANCE_PK3_VALUE PK3_VALUE
, GRANTS.INSTANCE_PK4_VALUE PK4_VALUE
, GRANTS.INSTANCE_PK5_VALUE PK5_VALUE
, GRANTS.INSTANCE_SET_ID INSTANCE_SET_ID
, GRANTS.ROWID AS ROW_ID
, FU.USER_NAME
FROM FND_GRANTS GRANTS
, HZ_PARTIES GRANTEE_GLOBAL
, FND_MENUS GRANTED_MENU
, FND_OBJECTS OBJ
, FND_MENUS_TL MENUTL
, FND_USER FU
WHERE GRANTED_MENU.MENU_NAME IN ('AMW_CTRL_OWNER_ROLE'
, 'AMW_AP_OWNER_ROLE'
, 'AMW_RL_PROC_OWNER_ROLE'
, 'AMW_RISK_OWNER_ROLE'
, 'AMW_RL_PROC_APPL_OWNER_ROLE'
, 'AMW_RL_PROC_FINANCE_OWNER_ROLE')
AND GRANTS.OBJECT_ID = OBJ.OBJECT_ID
AND GRANTS.GRANTEE_TYPE = 'GLOBAL'
AND NVL(GRANTS.END_DATE
, SYSDATE+1) >= TRUNC(SYSDATE)
AND GRANTS.MENU_ID = GRANTED_MENU.MENU_ID
AND GRANTS.MENU_ID = MENUTL.MENU_ID
AND MENUTL.LANGUAGE=USERENV('LANG')
AND GRANTEE_GLOBAL.PARTY_ID = -1000
AND GRANTS.INSTANCE_TYPE = 'SET'
AND GRANTS.INSTANCE_SET_ID = -1
AND GRANTEE_GLOBAL.PARTY_ID=FU.PERSON_PARTY_ID UNION ALL SELECT 'P' GRANTEE_TYPE
, 'P1' NAME_LINK
, GRANTS.GRANT_GUID GRANT_GUID
, GRANTS.START_DATE START_DATE
, GRANTS.END_DATE END_DATE
, GRANTS.INSTANCE_TYPE OBJECT_KEY_TYPE
, GRANTS.INSTANCE_PK1_VALUE OBJECT_KEY
, GRANTEE_PERSON.PERSON_NAME PARTY_NAME_ORIG
, (SELECT DISTINCT FULL_NAME
FROM AMW_EMPLOYEES_CURRENT_V
WHERE PARTY_ID=GRANTEE_PERSON.PERSON_ID) PARTY_NAME
, GRANTEE_PERSON.COMPANY_NAME COMPANY_NAME
, GRANTEE_PERSON.COMPANY_ID COMPANY_ID
, GRANTEE_PERSON.PERSON_ID PARTY_ID
, GRANTED_MENU.MENU_NAME ROLE_NAME
, GRANTED_MENU.MENU_NAME ROLE_DESCRIPTION
, OBJ.OBJ_NAME OBJECT_NAME
, GRANTED_MENU.MENU_ID MENU_ID
, 'EGOROLEGRANTTABLEVIEWROLENAME' SWITCHERCOL
, MENUTL.USER_MENU_NAME ROLENAMELINK
, GRANTS.INSTANCE_PK1_VALUE PK1_VALUE
, GRANTS.INSTANCE_PK2_VALUE PK2_VALUE
, GRANTS.INSTANCE_PK3_VALUE PK3_VALUE
, GRANTS.INSTANCE_PK4_VALUE PK4_VALUE
, GRANTS.INSTANCE_PK5_VALUE PK5_VALUE
, GRANTS.INSTANCE_SET_ID INSTANCE_SET_ID
, GRANTS.ROWID AS ROW_ID
, FU.USER_NAME
FROM FND_GRANTS GRANTS
, AMW_PERSON_COMPANY_V GRANTEE_PERSON
, FND_MENUS GRANTED_MENU
, FND_OBJECTS OBJ
, FND_MENUS_TL MENUTL
, FND_USER FU
WHERE GRANTED_MENU.MENU_NAME IN ('AMW_CTRL_OWNER_ROLE'
, 'AMW_AP_OWNER_ROLE'
, 'AMW_RL_PROC_OWNER_ROLE'
, 'AMW_RISK_OWNER_ROLE'
, 'AMW_RL_PROC_APPL_OWNER_ROLE'
, 'AMW_RL_PROC_FINANCE_OWNER_ROLE')
AND GRANTS.OBJECT_ID = OBJ.OBJECT_ID
AND GRANTS.GRANTEE_TYPE ='USER'
AND TO_NUMBER(REPLACE(GRANTS.GRANTEE_KEY
, 'HZ_PARTY:'
, '')) = GRANTEE_PERSON.PERSON_ID
AND GRANTEE_KEY LIKE 'HZ_PARTY%'
AND NVL(GRANTS.END_DATE
, SYSDATE+1) >= TRUNC(SYSDATE)
AND GRANTS.MENU_ID = GRANTED_MENU.MENU_ID
AND GRANTS.MENU_ID = MENUTL.MENU_ID
AND MENUTL.LANGUAGE=USERENV('LANG')
AND GRANTS.INSTANCE_TYPE = 'INSTANCE'
AND GRANTEE_PERSON.PERSON_ID=FU.PERSON_PARTY_ID UNION ALL SELECT 'P' GRANTEE_TYPE
, 'P1' NAME_LINK
, GRANTS.GRANT_GUID GRANT_GUID
, GRANTS.START_DATE START_DATE
, GRANTS.END_DATE END_DATE
, GRANTS.INSTANCE_TYPE OBJECT_KEY_TYPE
, GRANTS.INSTANCE_PK1_VALUE OBJECT_KEY
, GRANTEE_PERSON.PERSON_NAME PARTY_NAME_ORIG
, (SELECT DISTINCT FULL_NAME
FROM AMW_EMPLOYEES_CURRENT_V
WHERE PARTY_ID=GRANTEE_PERSON.PERSON_ID) PARTY_NAME
, GRANTEE_PERSON.COMPANY_NAME COMPANY_NAME
, GRANTEE_PERSON.COMPANY_ID COMPANY_ID
, GRANTEE_PERSON.PERSON_ID PARTY_ID
, GRANTED_MENU.MENU_NAME ROLE_NAME
, GRANTED_MENU.MENU_NAME ROLE_DESCRIPTION
, OBJ.OBJ_NAME OBJECT_NAME
, GRANTED_MENU.MENU_ID MENU_ID
, 'EGOROLEGRANTTABLEVIEWROLENAME' SWITCHERCOL
, MENUTL.USER_MENU_NAME ROLENAMELINK
, GRANTS.INSTANCE_PK1_VALUE PK1_VALUE
, GRANTS.INSTANCE_PK2_VALUE PK2_VALUE
, GRANTS.INSTANCE_PK3_VALUE PK3_VALUE
, GRANTS.INSTANCE_PK4_VALUE PK4_VALUE
, GRANTS.INSTANCE_PK5_VALUE PK5_VALUE
, GRANTS.INSTANCE_SET_ID INSTANCE_SET_ID
, GRANTS.ROWID AS ROW_ID
, FU.USER_NAME
FROM FND_GRANTS GRANTS
, AMW_PERSON_COMPANY_V GRANTEE_PERSON
, FND_MENUS GRANTED_MENU
, FND_OBJECTS OBJ
, FND_MENUS_TL MENUTL
, FND_USER FU
WHERE GRANTED_MENU.MENU_NAME IN ('AMW_CTRL_OWNER_ROLE'
, 'AMW_AP_OWNER_ROLE'
, 'AMW_RL_PROC_OWNER_ROLE'
, 'AMW_RISK_OWNER_ROLE'
, 'AMW_RL_PROC_APPL_OWNER_ROLE'
, 'AMW_RL_PROC_FINANCE_OWNER_ROLE')
AND GRANTS.OBJECT_ID = OBJ.OBJECT_ID
AND GRANTS.GRANTEE_TYPE ='USER'
AND TO_NUMBER(REPLACE(GRANTS.GRANTEE_KEY
, 'HZ_PARTY:'
, '')) = GRANTEE_PERSON.PERSON_ID
AND GRANTEE_KEY LIKE 'HZ_PARTY%'
AND NVL(GRANTS.END_DATE
, SYSDATE+1) >= TRUNC(SYSDATE)
AND GRANTS.MENU_ID = GRANTED_MENU.MENU_ID
AND GRANTS.MENU_ID = MENUTL.MENU_ID
AND MENUTL.LANGUAGE=USERENV('LANG')
AND GRANTS.INSTANCE_TYPE = 'SET'
AND GRANTS.INSTANCE_SET_ID = -1
AND GRANTEE_PERSON.PERSON_ID=FU.PERSON_PARTY_ID

Columns