FND User Roles

select
x.*
from
(
select
xxen_util.user_name(wlur.user_name) user_name,
decode(wlur.role_orig_system,'FND_RESP',frv.responsibility_name,nvl(wlrt.display_name,wlr.display_name)) role_name,
wlr.name role_code,
decode(wlur.role_orig_system,'FND_RESP',fnd_message.get_string('FND','W3H_RESPONSIBILITY_ROLETYPE'),fnd_message.get_string('FND','W3H_ROLE_ROLETYPE')) role_type,
decode(wlur.assignment_type,'D',fnd_message.get_string('FND','W3H_DIRECT_ASSIGNMENTTYPE'),'I',fnd_message.get_string('FND', 'W3H_INDIRECT_ASSIGNMENTTYPE'),'B',fnd_message.get_string('FND','W3H_BOTH_ASSIGNMENTTYPE')) assignment_type,
decode(umx_w3h_utl.isfunctionaccessible(wlur.user_name,wlur.role_name),'true',fnd_message.get_string('FND','W3H_SHOW_ACTIVE_FUNCTIONS'),fnd_message.get_string('FND','W3H_SHOW_INACTIVE_FUNCTIONS')) assignment_status,
(
select
decode(wlr.orig_system,'FND_RESP',frv.responsibility_name,nvl(wlrt.display_name,wlr.display_name)) assigning_role_name
from
wf_local_roles wlr0,
wf_local_roles_tl wlrt0,
fnd_responsibility_vl frv0
where
nullif(wura.assigning_role,wlr.name)=wlr0.name and
wlr0.orig_system in ('UMX','FND_RESP') and
wlr0.name=wlrt0.name(+) and
wlr0.orig_system=wlrt0.orig_system(+) and
wlr0.orig_system_id=wlrt0.orig_system_id(+) and
wlrt0.language(+)=userenv('lang') and
wlr0.partition_id=wlrt0.partition_id(+) and
wlr0.orig_system_id=frv0.responsibility_id(+)
) assigning_role_name,
nullif(wura.assigning_role,wlr.name) assigning_role_code,
wura.effective_start_date,
wura.effective_end_date,
wura.role_start_date,
wura.role_end_date,
xxen_util.user_name(wura.created_by) assigned_by,
wura.assignment_reason
from
wf_local_user_roles wlur,
wf_user_role_assignments wura,
fnd_responsibility_vl frv,
wf_local_roles wlr,
wf_local_roles_tl wlrt
where
1=1 and
wlur.role_orig_system in ('UMX','FND_RESP') and
wlur.role_orig_system_id=frv.responsibility_id(+) and
wlur.role_name=wlr.name and
wlr.orig_system in ('UMX','FND_RESP') and
wlur.user_name=wura.user_name and
wlur.role_name=wura.role_name and
wlr.name=wlrt.name(+) and
wlr.orig_system=wlrt.orig_system(+) and
wlr.orig_system_id=wlrt.orig_system_id(+) and
wlrt.language(+)=userenv('lang') and
wlr.partition_id=wlrt.partition_id(+)
) x
where
2=2
order by
x.user_name,
x.role_name

select x.* from ( select xxen_util.user_name(wlur.user_name) user_name, decode(wlur.role_orig_system,'FND_RESP',frv.responsibility_name,nvl(wlrt.display_name,wlr.display_name)) role_name, wlr.name role_code, decode(wlur.role_orig_system,'FND_RESP',fnd_message.get_string('FND','W3H_RESPONSIBILITY_ROLETYPE'),fnd_message.get_string('FND','W3H_ROLE_ROLETYPE')) role_type, decode(wlur.assignment_type,'D',fnd_message.get_string('FND','W3H_DIRECT_ASSIGNMENTTYPE'),'I',fnd_message.get_string('FND', 'W3H_INDIRECT_ASSIGNMENTTYPE'),'B',fnd_message.get_string('FND','W3H_BOTH_ASSIGNMENTTYPE')) assignment_type, decode(umx_w3h_utl.isfunctionaccessible(wlur.user_name,wlur.role_name),'true',fnd_message.get_string('FND','W3H_SHOW_ACTIVE_FUNCTIONS'),fnd_message.get_string('FND','W3H_SHOW_INACTIVE_FUNCTIONS')) assignment_status, ( select decode(wlr.orig_system,'FND_RESP',frv.responsibility_name,nvl(wlrt.display_name,wlr.display_name)) assigning_role_name from wf_local_roles wlr0, wf_local_roles_tl wlrt0, fnd_responsibility_vl frv0 where nullif(wura.assigning_role,wlr.name)=wlr0.name and wlr0.orig_system in ('UMX','FND_RESP') and wlr0.name=wlrt0.name(+) and wlr0.orig_system=wlrt0.orig_system(+) and wlr0.orig_system_id=wlrt0.orig_system_id(+) and wlrt0.language(+)=userenv('lang') and wlr0.partition_id=wlrt0.partition_id(+) and wlr0.orig_system_id=frv0.responsibility_id(+) ) assigning_role_name, nullif(wura.assigning_role,wlr.name) assigning_role_code, wura.effective_start_date, wura.effective_end_date, wura.role_start_date, wura.role_end_date, xxen_util.user_name(wura.created_by) assigned_by, wura.assignment_reason from wf_local_user_roles wlur, wf_user_role_assignments wura, fnd_responsibility_vl frv, wf_local_roles wlr, wf_local_roles_tl wlrt where 1=1 and wlur.role_orig_system in ('UMX','FND_RESP') and wlur.role_orig_system_id=frv.responsibility_id(+) and wlur.role_name=wlr.name and wlr.orig_system in ('UMX','FND_RESP') and wlur.user_name=wura.user_name and wlur.role_name=wura.role_name and wlr.name=wlrt.name(+) and wlr.orig_system=wlrt.orig_system(+) and wlr.orig_system_id=wlrt.orig_system_id(+) and wlrt.language(+)=userenv('lang') and wlr.partition_id=wlrt.partition_id(+) ) x where 2=2 order by x.user_name, x.role_name