VIEW: APPS.AMW_OWNER_ROLES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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