职责与配置文件的关系信息

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)

发表回复

您的电子邮箱地址不会被公开。