select fpv.profile_option_value, fpv.level_value, fst.responsibility_name
from fnd_profile_options fpo,
fnd_profile_option_values fpv,
fnd_profile_options_tl tl,
fnd_responsibility_tl fst,
(SELECT 10001 level_id, '地点' 文件安全性
FROM dual
UNION
SELECT 10002 level_id, '应用产品' 文件安全性
FROM dual
UNION
SELECT 10003 level_id, '责任' 文件安全性
FROM dual
UNION
SELECT 10004 level_id, '用户' 文件安全性
FROM dual
UNION
SELECT 10005 level_id, '服务器' 文件安全性
FROM dual
UNION
SELECT 10006 level_id, '组织' 文件安全性 FROM dual) lv
where 1 = 1
and fpv.profile_option_id = fpo.profile_option_id
and tl.language = 'ZHS'
and fst.language = 'ZHS'
and tl.profile_option_name = fpo.profile_option_name
–and tl.user_profile_option_name like '%MO%业务实体%'
–and fpo.profile_option_name = 'DEFAULT_ORG_ID'
and fpo.profile_option_name = 'CUX_INV_ACCESS_SINV_CQC'
and fpv.level_value = fst.responsibility_id
and lv.level_id = fpv.level_id
–and fpv.level_value = fnd_global.resp_id
–and fpv.level_id = 10003 –职责层
–初始化环境
BEGIN
fnd_global.apps_initialize(user_id => 0,
resp_id => 50658,
resp_appl_id => 20003);
mo_global.set_policy_context(p_access_mode => 'S', p_org_id => 82);
mo_global.init('SQLAP');
END;
–以下SQL查询当前配置的MO：安全性配置文件
SELECT *
FROM per_security_profiles psp
WHERE psp.security_profile_id =
to_number(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'));
和fnd_profile.value('ORG_ID') 来配置
–以下SQL用来获取当前安全性配置文件和当前用户可访问的OU
SELECT hou.organization_id, hou.name
FROM hr_operating_units hou
WHERE hou.usable_flag IS NULL
AND EXISTS
(SELECT 1
FROM per_organization_list per
WHERE per.organization_id = hou.organization_id
AND per.security_profile_id =
to_number(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))
UNION
SELECT 1
FROM dual
WHERE to_number(fnd_profile.value('ORG_ID')) = hou.organization_id)
表名：per_organization_list per, hr_operating_units hr
条件：WHERE per.security_profile_id(+) = to_number(fnd_profile.VALUE('XLA_MO_SECURITY_PROFILE_LEVEL'))
AND hr.organization_id = per.organization_id(+)
AND hr.usable_flag is null
AND (EXISTS (SELECT 1
FROM per_organization_list per1
WHERE hr.organization_id = per1.organization_id
AND per1.security_profile_id =
to_number(fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL'))) OR
fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL') = 0)
