FND W3H_User_Roles

select * from (
select decode(aur.role_orig_system,'FND_RESP', t.responsibility_name, nvl(WRT.DISPLAY_NAME, WR.DISPLAY_NAME)) as display_Name 
, decode(aur.role_orig_system,'FND_RESP',fnd_message.get_STRING('FND','W3H_RESPONSIBILITY_ROLETYPE'),
  fnd_message.get_STRING('FND','W3H_ROLE_ROLETYPE')) role_type
, decode(aur.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(aur.user_name,aur.role_name),'true',
  fnd_message.get_STRING('FND','W3H_SHOW_ACTIVE_FUNCTIONS'),
  fnd_message.get_STRING('FND','W3H_SHOW_INACTIVE_FUNCTIONS') )Assignment_Status,
wura.assigning_role  AS AssigningRole,
wura.effective_start_date AS EffectiveStartDate,
wura.effective_end_date AS EffectiveEndDate,
wura.role_start_date  AS RoleStartDate,
wura.role_end_date AS RoleEndDate ,
(select user_name from fnd_user where user_id=wura.created_by) AS AssignedBy,
wura.assignment_reason AS Justification
from wf_ALL_user_roles aur
, WF_LOCAL_ROLES wr 
, WF_LOCAL_ROLES_TL wrt 
, FND_RESPONSIBILITY_TL T
,wf_user_role_assignments wura
where aur.USER_name = :PARAM1
 and (aur.ROLE_ORIG_SYSTEM = 'UMX' or aur.ROLE_ORIG_SYSTEM = 'FND_RESP')
 and (aur.role_orig_system_id=t.responsibility_id(+) AND T.LANGUAGE(+) =
USERENV('LANG'))
 and (aur.role_name=wr.name(+)	and wr.orig_system = wrt.orig_system (+)
 and (WR.orig_system = 'UMX' or WR.ORIG_SYSTEM = 'FND_RESP')
 and wr.orig_system_id = wrt.orig_system_id (+)
 and wr.name = wrt.name (+)
 and wr.partition_id = wrt.partition_id (+)
 and wrt.language (+) = userenv('LANG'))
 and wura.user_name= aur.USER_name
 and  wura.role_name = aur.ROLE_NAME
) &PARAM2