Oracle ERP에서 해당 유저의 responsibility 확인 하는 방법

Oracle ERP에서 해당 유저의 responsibility 확인 하는 방법

 

ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN' ;




select fu.user_name,
       fr.responsibility_name,
       furg.START_DATE,
       furg.END_DATE
from   fnd_user_resp_groups_direct furg,
       fnd_user fu,
       fnd_responsibility_tl fr
where  fu.user_name in('IRC_EXT_GUEST', 'IRC_EMP_GUEST', 'IEXADMIN','MESINTUSER','PHUSER','BSUSER','M20USER','MOBADM','MOBDEV')
and    furg.user_id = fu.user_id
and    furg.responsibility_id = fr.responsibility_id
and    fr.language = userenv('LANG');
 

select
  urg.SECURITY_GROUP_ID SecGID,
  urg.RESPONSIBILITY_ID RespID,
  urg.RESPONSIBILITY_APPLICATION_ID RespAppID,
  resp.RESPONSIBILITY_NAME "Responsibility Name"
from
  fnd_user_resp_groups urg, fnd_responsibility_vl resp
where
  urg.responsibility_id = resp.responsibility_id
  and urg.responsibility_application_id = resp.application_id
  and urg.user_id = (select user_id from fnd_user where user_name = 'SYSADMIN')
  and urg.security_group_id = 0
union
select
  urg.SECURITY_GROUP_ID SecGID,
  urg.RESPONSIBILITY_ID RespID,
  urg.RESPONSIBILITY_APPLICATION_ID RespAppID,
  resp.RESPONSIBILITY_NAME || sec.security_group_name name
from fnd_user_resp_groups urg, fnd_responsibility_vl resp,
  fnd_security_groups_vl sec
where
  urg.responsibility_id = resp.responsibility_id
  and urg.responsibility_application_id = resp.application_id
  and urg.security_group_id = sec.security_group_id
  and urg.user_id = (select user_id from fnd_user where user_name = 'SYSADMIN')
  and urg.security_group_id != 0;
  
  
  

select
  p.user_profile_option_name "Profile Name",
  decode(v.profile_option_values,
    1, 'Admin',
    2, 'Normal',
    3, 'External',
    'Unknown') Value,
  decode(v.level_id,
    10001,'SITE',
    10002, (select 'App:'||a.application_short_name from fnd_application a
            where a.application_id = v.level_value),
    10003, (select 'Resp:'||f.RESPONSIBILITY_name||' ('||responsibility_key||')' from fnd_responsibility_vl f
            where f.responsibility_id = v.level_value),
    10004, (select 'User:'||u.user_name from fnd_user u
            where u.user_id = v.level_value),
    10005, (select 'Server:'||n.node_name from fnd_nodes n
            where n.node_id = v.level_value),
    10006, (select 'Org:'||org.name from hr_operating_units org
            where org.name = v.level_value),
    'NOT SET') "Level Where Option Is Set"
from
  APPS.FND_PROFILE_OPTIONS_VL p,
  APPS.fnd_profile_option_values v
where 1=1
  and p.profile_option_id = v.profile_option_id (+)
  and p.application_id = v.application_id (+)
  --and p.profile_option_name like upper('%TRUST%')
order by 1,2 desc,3;