EBS: 系统配置文件查询

EBS R12.1 系统配置文件查询 

路径： SYSTEM ADMINISTRATOR>> 配置文件>>系统。

SYSTEM ADMINISTRATOR>> PROFILE>>SYSTEM.  

-- FND-配置文件查询.sql 
-- 地点层，共 4175 笔
 SELECT --fpo.application_id,
        --fpo.PROFILE_OPTION_ID,
        fpo.profile_option_name , 
        fpo.USER_PROFILE_OPTION_NAME,
        fpv.profile_option_value,
        decode(fpv.level_id,'10001','地点') as level_code,
        fpv.level_id,
        fpv.level_value,
        '' AS "应用产品",
        '' AS "责任",
        '' as "用户",
        '' AS "服务器",
        '' AS "组织",
        fpv.level_value2,
        fpv.level_value_application_id,
        fpv.creation_date,
        fpv.last_update_date
      --  ,fpv.* 
  FROM FND_PROFILE_OPTIONS_VL fpo,
       fnd_profile_option_values fpv
  WHERE 1=1
  -- AND fpv.APPLICATION_ID = 0 
   and fpo.APPLICATION_ID= fpv.application_id
   and fpo.PROFILE_OPTION_ID = fpv.profile_option_id
  AND fpv.LEVEL_ID = 10001  -- 10001: 地点层 
 -- and fpo.PROFILE_OPTION_NAME = 'FND_SOA_AUDIT_ENABLED' 
UNION ALL 
-- 应用产品
 SELECT --fpo.application_id,
        --fpo.PROFILE_OPTION_ID,
        fpo.profile_option_name , 
        fpo.USER_PROFILE_OPTION_NAME,
        fpv.profile_option_value,
        decode(fpv.level_id,'10002','应用产品') as level_code,
        fpv.level_id,
        fpv.level_value,
        (SELECT FA.APPLICATION_NAME FROM FND_APPLICATION_VL FA
           WHERE FA.APPLICATION_ID = FPV.LEVEL_VALUE 
           AND fpv.LEVEL_ID = 10002
           AND ROWNUM = 1
         ) AS "应用产品",
        ''  AS "责任",
        '' as "用户",
        '' AS "服务器",
        '' AS "组织",
        fpv.level_value2,
        fpv.level_value_application_id,        
        fpv.creation_date,
        fpv.last_update_date
      --  ,fpv.* 
  FROM FND_PROFILE_OPTIONS_VL fpo,
       fnd_profile_option_values fpv       
  WHERE 1=1
  -- AND fpv.APPLICATION_ID = 0 
   and fpo.APPLICATION_ID= fpv.application_id
   and fpo.PROFILE_OPTION_ID = fpv.profile_option_id
  AND fpv.LEVEL_ID = 10002  -- 10002: 应用产品   
 -- and fpo.PROFILE_OPTION_NAME =  'HELP_TREE_ROOT='
  UNION ALL 
-- 责任 ,共 1138笔 
 SELECT --fpo.application_id,
        --fpo.PROFILE_OPTION_ID,
        fpo.profile_option_name , 
        fpo.USER_PROFILE_OPTION_NAME,
        fpv.profile_option_value,
        decode(fpv.level_id,'10003','责任') as level_code,
        fpv.level_id,
        fpv.level_value,
        (SELECT FA.APPLICATION_NAME FROM FND_APPLICATION_VL FA
           WHERE FA.APPLICATION_ID = FPV.LEVEL_VALUE
           AND fpv.LEVEL_ID = 10002
           AND ROWNUM = 1
         ) AS "应用产品",
        (SELECT fr.RESPONSIBILITY_NAME
         from fnd_responsibility_vl FR 
         where fr.APPLICATION_ID = fpv.level_value_application_id
          and to_char(fr.RESPONSIBILITY_ID) = fpv.level_value 
          AND fpv.LEVEL_ID = 10003
        AND ROWNUM = 1) AS "责任", 
        ''  as "用户",
        '' AS "服务器",
        '' AS "组织",
        fpv.level_value2,
        fpv.level_value_application_id,        
        fpv.creation_date,
        fpv.last_update_date
      --  ,fpv.* 
  FROM FND_PROFILE_OPTIONS_VL fpo,
       fnd_profile_option_values fpv       
  WHERE 1=1
  -- AND fpv.APPLICATION_ID = 0 
   and fpo.APPLICATION_ID= fpv.application_id
   and fpo.PROFILE_OPTION_ID = fpv.profile_option_id
   AND fpv.LEVEL_ID = 10003  -- 10003: 责任     
  -- AND fpo.profile_option_name = 'DIAGNOSTICS'
UNION ALL 
-- 用户 ,共 294 笔 
 SELECT --fpo.application_id,
        --fpo.PROFILE_OPTION_ID,
        fpo.profile_option_name , 
        fpo.USER_PROFILE_OPTION_NAME,
        fpv.profile_option_value,
        decode(fpv.level_id,'10004','用户') as level_code,
        fpv.level_id,
        fpv.level_value,
        (SELECT FA.APPLICATION_NAME FROM FND_APPLICATION_VL FA
           WHERE FA.APPLICATION_ID = FPV.LEVEL_VALUE
           AND fpv.LEVEL_ID = 10002
           AND ROWNUM = 1
         ) AS "应用产品",
        (SELECT fr.RESPONSIBILITY_NAME
         from fnd_responsibility_vl FR 
         where fr.APPLICATION_ID = fpv.level_value_application_id
          and to_char(fr.RESPONSIBILITY_ID) = fpv.level_value 
          AND fpv.LEVEL_ID = 10003
        AND ROWNUM = 1) AS "责任", 
        (SELECT FU.USER_NAME FROM FND_USER FU WHERE TO_CHAR(FU.USER_ID) = FPV.LEVEL_VALUE
         AND fpv.LEVEL_ID = 10004
         AND ROWNUM = 1 ) as "用户",
        '' AS "服务器",
        '' AS "组织",
        fpv.level_value2,
        fpv.level_value_application_id,        
        fpv.creation_date,
        fpv.last_update_date
      --  ,fpv.* 
  FROM FND_PROFILE_OPTIONS_VL fpo,
       fnd_profile_option_values fpv       
  WHERE 1=1
 --  AND fpv.APPLICATION_ID = 0 
   and fpo.APPLICATION_ID= fpv.application_id
   and fpo.PROFILE_OPTION_ID = fpv.profile_option_id
   AND fpv.LEVEL_ID = 10004  -- 10004: 用户
  -- and fpv.level_value = 21084 
  -- AND fpo.profile_option_name = 'DIAGNOSTICS'   
UNION ALL 
-- 服务器 ,共 1 笔 
 SELECT --fpo.application_id,
        --fpo.PROFILE_OPTION_ID,
        fpo.profile_option_name , 
        fpo.USER_PROFILE_OPTION_NAME,
        fpv.profile_option_value,
        decode(fpv.level_id,'10005','服务器') as level_code,
        fpv.level_id,
        fpv.level_value,
        (SELECT FA.APPLICATION_NAME FROM FND_APPLICATION_VL FA
           WHERE FA.APPLICATION_ID = FPV.LEVEL_VALUE AND ROWNUM = 1
         ) AS "应用产品",
        (SELECT fr.RESPONSIBILITY_NAME
         from fnd_responsibility_vl FR 
         where fr.APPLICATION_ID = fpv.level_value_application_id
          and to_char(fr.RESPONSIBILITY_ID) = fpv.level_value 
        AND ROWNUM = 1) AS "责任", 
        (SELECT FU.USER_NAME FROM FND_USER FU WHERE TO_CHAR(FU.USER_ID) = FPV.LEVEL_VALUE
         AND fpv.LEVEL_ID = 10004
        AND ROWNUM = 1 ) as "用户",
        (SELECT NODE_NAME FROM FND_NODES FN
         WHERE TO_CHAR(FN.NODE_ID) = FPV.LEVEL_VALUE
         AND fpv.LEVEL_ID = 10005
         AND ROWNUM = 1
         ) AS "服务器",
        '' AS "组织",
        fpv.level_value2,
        fpv.level_value_application_id,        
        fpv.creation_date,
        fpv.last_update_date
      --  ,fpv.* 
  FROM FND_PROFILE_OPTIONS_VL fpo,
       fnd_profile_option_values fpv       
  WHERE 1=1 
  -- AND fpv.APPLICATION_ID = 0 
   and fpo.APPLICATION_ID= fpv.application_id
   and fpo.PROFILE_OPTION_ID = fpv.profile_option_id
   AND fpv.LEVEL_ID = 10005  -- 10005: 服务器
  -- and fpv.level_value = 21084 
  -- AND fpo.profile_option_name = 'DIAGNOSTICS'     
UNION ALL 
-- 组织 ,共 4 笔 
 SELECT --fpo.application_id,
        --fpo.PROFILE_OPTION_ID,
        fpo.profile_option_name , 
        fpo.USER_PROFILE_OPTION_NAME,
        fpv.profile_option_value,
        decode(fpv.level_id,'10006','组织') as level_code,
        fpv.level_id,
        fpv.level_value,
        (SELECT FA.APPLICATION_NAME FROM FND_APPLICATION_VL FA
           WHERE FA.APPLICATION_ID = FPV.LEVEL_VALUE AND ROWNUM = 1
         ) AS "应用产品",
        (SELECT fr.RESPONSIBILITY_NAME
         from fnd_responsibility_vl FR 
         where fr.APPLICATION_ID = fpv.level_value_application_id
          and to_char(fr.RESPONSIBILITY_ID) = fpv.level_value 
        AND ROWNUM = 1) AS "责任", 
        (SELECT FU.USER_NAME FROM FND_USER FU WHERE TO_CHAR(FU.USER_ID) = FPV.LEVEL_VALUE
         AND fpv.LEVEL_ID = 10004
        AND ROWNUM = 1 ) as "用户",
        (SELECT NODE_NAME FROM FND_NODES FN
         WHERE TO_CHAR(FN.NODE_ID) = FPV.LEVEL_VALUE
         AND fpv.LEVEL_ID = 10005
         AND ROWNUM = 1
         ) AS "服务器",
         (SELECT HOU.NAME FROM HR_ORGANIZATION_UNITS HOU
         WHERE TO_CHAR(HOU.ORGANIZATION_ID) = FPV.LEVEL_VALUE
         AND fpv.LEVEL_ID = 10006
         AND ROWNUM = 1
         ) AS "组织",
        fpv.level_value2,
        fpv.level_value_application_id,        
        fpv.creation_date,
        fpv.last_update_date
      --  ,fpv.* 
  FROM FND_PROFILE_OPTIONS_VL fpo,
       fnd_profile_option_values fpv       
  WHERE 1=1 
  -- AND fpv.APPLICATION_ID = 0 
   and fpo.APPLICATION_ID= fpv.application_id
   and fpo.PROFILE_OPTION_ID = fpv.profile_option_id
   AND fpv.LEVEL_ID = 10006  -- 10006: 组织
  -- and fpv.level_value = 21084 
  -- AND fpo.profile_option_name = 'DIAGNOSTICS'       

　　全部查询大约1.1万行记录。