R12 Role, Responsibilities and Assignments Queries

select pu.username, prdt.role_name, prd.role_common_name, pur.active_flag

from apps.per_users pu, apps.per_user_roles pur, apps.per_roles_dn prd, apps.per_roles_dn_tl prdt

where pu.user_id = pur.user_id

and pur.role_id = prd.role_id

and prd.role_id = prdt.role_id

and prdt.language = 'US'

and pur.role_guid = prd.role_guid

and pu.username =  'USER_NAME'

group by pu.username, pur.active_flag, prd.role_common_name, prdt.role_name;

select * from apps.wf_local_roles  where name like 'UMX%EBS_WF%' order by 1;;

select * from apps.wf_local_user_roles where role_name like 'UMX%EBS_WF%'order by 1;

select * from apps.wf_user_role_assignments where user_name  ='USERNAME' order by 1; --'role_name like 'UMX%EBS_WF%' order by 1;

select * from apps.wf_user_role_assignments where assigning_role  like 'UMX%EBS%FIN%' order by 1;

The following query I wrote in an effort to list all the responsibilities that are assigned to the Role I create under UMX. I could not find any simpler query that would define relationship directly

select distinct role_name, assigning_role from apps.wf_user_role_assignments where assigning_role  like 'UMX%' order by 1;

The following Query also works better

select WRH.SUPER_NAME, WRH.SUB_NAME,

--apps.FND_LOAD_UTIL.Owner_Name(WRH.LAST_UPDATED_BY) OWNER,

to_char(WRH.LAST_UPDATE_DATE, 'YYYY/MM/DD') LAST_UPDATE_DATE,

WRH.ENABLED_FLAG

from apps.WF_ROLE_HIERARCHIES WRH, apps.WF_ROLES WR

where WRH.SUB_NAME = WR.NAME

--and SUB_NAME like 'UMX%WF%'

and WR.ORIG_SYSTEM in ('FND_RESP', 'UMX')

connect by wrh.super_name = wrh.sub_name

--start with WRH.SUB_NAME = 'UMX|XXX AP INVOICE ENTRY';

start with WRH.SUB_NAME = 'UMX|XXX WF ADMIN';

select * from  apps.FND_RESPONSIBILITY_VL where responsibility_key like '%WF%';

select *  from  apps.FND_USER_RESP_GROUPS;

select * from apps.WF_ROLES where name like '%' order by orig_system_id;

select * from apps.WF_ROLES where name like 'UMX%XXX_WF%' order by orig_system_id;

select * from  apps.FND_RESPONSIBILITY;

select * from apps.umx_role_assignments_v where role_name like 'UMX%';

select * from apps.umx_current_roles_v;