ORACLEPROFILE(配置文件)查询

ORACLEPROFILE(配置文件)查询

SELECT profile_option_name            配置文件名,
       user_profile_option_name       用户配置文件名,
       description                    说明,
       hierarchy_type                 层次结构类型,
       site_enabled_flag              地点可见,
       site_update_allowed_flag       地点可更新,
       app_enabled_flag               应用产品可见,
       app_update_allowed_flag        应用产品可更新,
       resp_enabled_flag              责任可见,
       resp_update_allowed_flag       责任可更新,
       server_enabled_flag            服务器可见,
       server_update_allowed_flag     服务器可更新,
       serverresp_enabled_flag        服务器职责可见,
       serverresp_update_allowed_flag 服务器职责可更新,
       org_enabled_flag               组织可见,
       org_update_allowed_flag        组织可更新,
       user_enabled_flag              用户可见,
       user_update_allowed_flag       用户可更新,
       start_date_active              有效起始日期,
       end_date_active                有效截止日期,
       user_visible_flag              用户访问可查看,
       user_changeable_flag           用户访问可更新,
       read_allowed_flag              可读,
       write_allowed_flag             可写,
       sql_validation                 sql验证,
       profile_option_id              配置文件配置情况id
  FROM fnd_profile_options_vl
 WHERE (user_profile_option_name = 'CUX_用户机台编号')

SELECT op.profile_option_id,
       tl.profile_option_name,
       tl.user_profile_option_name,
       lv.level_id,
       lv.文件安全性,
       va.level_value,
       CASE
         WHEN va.level_id = 10001 THEN
          '地点'
         WHEN va.level_id = 10002 THEN
          (SELECT fav.application_name
             FROM fnd_application_vl fav
            WHERE fav.application_id = va.level_value)
         WHEN va.level_id = 10003 THEN
          (SELECT /* $HEADER$ */
            t.responsibility_name
             FROM fnd_responsibility_tl t, fnd_responsibility b
            WHERE t.responsibility_id = va.level_value
              AND t.responsibility_id = b.responsibility_id
              AND b.application_id = t.application_id
              AND nvl(b.end_date, SYSDATE + 1) > SYSDATE
              AND nvl(b.start_date, SYSDATE - 1) < SYSDATE
              AND t.language = 'ZHS')
         WHEN va.level_id = 10004 THEN
          (SELECT user_name
             FROM fnd_user
            WHERE user_name NOT IN
                  ('*ANONYMOS*',
                   'CONVERSION',
                   'INITIAL SETUP',
                   'FEEDER SYSTEM',
                   'CONCURRENT MANAGER',
                   'STANDALONE BATCH PROCESS')
              AND user_id = va.level_value
              AND nvl(end_date, SYSDATE + 1) > SYSDATE
              AND nvl(start_date, SYSDATE - 1) < SYSDATE)
         WHEN va.level_id = 10005 THEN
          (SELECT node_name FROM fnd_nodes WHERE node_id = va.level_value)
         WHEN va.level_id = 10006 THEN
          (SELECT NAME
             FROM hr_operating_units
            WHERE organization_id = va.level_value)
         ELSE
          ''
       END AS profile_level_value,
       va.profile_option_value
  FROM fnd_profile_options_tl tl,
       fnd_profile_options op,
       fnd_profile_option_values va,
       (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 tl.language = 'ZHS'
   AND tl.profile_option_name = op.profile_option_name
   AND va.profile_option_id = op.profile_option_id
   AND va.level_id = lv.level_id
      --AND TL.PROFILE_OPTION_NAME like '%'
   AND tl.user_profile_option_name = 'CUX_用户机台编号'