ORACLE EBS 权限查询SQL

ORACLE EBS 权限查询SQL

1.查询拥有某个配置文件的用户**
SELECT t.level_id,
fu.user_name,
t.profile_option_value
FROM fnd_profile_option_values t,
fnd_user fu,
fnd_profile_options fpo
WHERE t.profile_option_id = fpo.profile_option_id
AND fpo.profile_option_name = ‘DIAGNOSTICS’ --查询拥有诊断功能的用户
AND t.level_value = fu.user_id(+);
*EBS诊断功能可以配置如下两个配置文件：
隐藏诊断菜单项
公用程序：诊断

2.查询拥有“系统管理员”的用户
SELECT fu.user_name,
hr.last_name,
t.responsibility_name
FROM fnd_user_resp_groups_direct fur,
fnd_user fu,
hr_employees hr,
fnd_responsibility_tl t
WHERE (fur.responsibility_id IN ( 20420)) --系统管理员责任ID
AND fur.user_id = fu.user_id
AND fur.end_date IS NULL
AND fu.end_date IS NULL
AND fu.employee_id = hr.employee_id(+)
AND fur.responsibility_id = t.responsibility_id
AND fur.responsibility_application_id = t.application_id
AND t.language = ‘ZHS’
ORDER BY fu.user_name;
**以上ID一般不用改，如果有问题自己去查询对应系统责任的ID

3.EAM模块当前系统使用数量统计
SELECT fu.user_name, hr.last_name, t.responsibility_name
FROM fnd_user_resp_groups_direct fur,
fnd_user fu,
hr_employees hr,
fnd_responsibility_tl t
WHERE (fur.responsibility_id IN
(SELECT DISTINCT T.RESPONSIBILITY_ID
FROM Fnd_Responsibility_Tl t --职责表
,
Fnd_Responsibility b,
Fnd_Menus_Vl f --菜单表
,
Fnd_Application_Vl a,
Fnd_Request_Groups Rg --请求组表
WHERE b.Responsibility_Id = t.Responsibility_Id
AND b.Application_Id = t.Application_Id
AND t.Language = Userenv(‘LANG’)
AND f.Menu_Id = b.Menu_Id
AND Rg.Application_Id = a.Application_Id
AND b.Request_Group_Id = Rg.Request_Group_Id
AND t.Responsibility_Name LIKE ‘%EAM%’ --职责名
)) --系统管理员责任ID
AND fur.user_id = fu.user_id
AND fur.end_date IS NULL
AND fu.end_date IS NULL
AND fu.employee_id = hr.employee_id(+)
AND fur.responsibility_id = t.responsibility_id
AND fur.responsibility_application_id = t.application_id
AND t.language = ‘ZHS’
ORDER BY fu.user_name;