在EBS中列出所有profile的属性值

  经常需要查询profile中的某个属性值，都要登录系统去找。这样很麻烦。可以把EBS的所有profile的属性值查找出来（包含空的属性）。SQL如下：

 

SELECT pro.profile_option_name,

       pro.user_profile_option_name,

       lev.level_type TYPE,

       lev.level_name,

       prv.profile_option_value

FROM apps.fnd_profile_options_vl pro,

       applsys.fnd_profile_option_values prv,

       (SELECT 10001 level_id,

               'Site' level_type,

               0 level_value,

               'Site' level_code,

               'Site' level_name

          FROM dual

        UNION ALL

        SELECT 10002 level_id,

               'App' level_type,

               app.application_id level_value,

               app.application_short_name level_code,

               app.application_name level_name

          FROM apps.fnd_application_vl app

        UNION ALL

        SELECT 10003 level_id,

               'Resp' level_type,

               resp.responsibility_id level_value,

               resp.responsibility_key level_code,

               resp.responsibility_name level_name

          FROM apps.fnd_responsibility_vl resp

        UNION ALL

        SELECT 10004 level_id,

               'User' level_type,

               usr.user_id level_value,

               usr.user_name level_code,

               usr.user_name level_name

          FROM applsys.fnd_user usr) lev

 

WHERE pro.profile_option_id = prv.profile_option_id(+)

   AND prv.level_id = lev.level_id(+)

   AND prv.level_value = lev.level_value(+)

            --参数Profile，下面连个条件用一个即可

   AND pro.user_profile_option_name LIKE '%BNE Allow No Security Rule%' --Profile名称

ORDER BY pro.profile_option_name, lev.level_type, lev.level_name;

 

--可以把查询结果直接导成CSV，用excel打开，然后直接进行搜索。EBS中以FND_PROFILE开头的都跟属性设置有关，可以好好研究。