EBS:查询系统配置文件

环境：EBS R12.1.X 

EBS:查询系统配置文件

-- 3796：MO：安全性配置文件  查询  
SELECT FPO.APPLICATION_ID,
       FPO.PROFILE_OPTION_ID,
       FPO.PROFILE_OPTION_NAME AS "配置文件-名称",
       FPO.USER_PROFILE_OPTION_NAME AS "用户配置文件名",  -- 例如： MO：默认业务实体
     --  FPO.DESCRIPTION AS "配置文件-说明",
       /*(SELECT FR.RESPONSIBILITY_NAME FROM APPS.FND_RESPONSIBILITY_VL  FR
        WHERE FR.RESPONSIBILITY_ID = FPOV.LEVEL_VALUE
         AND FR.APPLICATION_ID = FPOV.LEVEL_VALUE_APPLICATION_ID 
        AND ROWNUM =1) AS "责任-名称", */
      --  FR.RESPONSIBILITY_NAME  AS "责任-名称", 
      /* NVL((SELECT PSP2.SECURITY_PROFILE_NAME  FROM APPS.PER_SECURITY_PROFILES PSP2
        WHERE PSP2.SECURITY_PROFILE_ID = FPOV.PROFILE_OPTION_VALUE 
        AND FPOV.PROFILE_OPTION_ID = 3796  -- 3796：MO：安全性配置文件   
        AND ROWNUM=1 ),FPOV.PROFILE_OPTION_VALUE ) AS  "配置文件-值" , */
        FPOV.PROFILE_OPTION_VALUE  AS  "配置文件-值" ,
        FPOV.LEVEL_ID AS 层次级别ID,
        ( SELECT /*FLV.LOOKUP_CODE, TAG,FLV.MEANING,*/FLV.DESCRIPTION
        FROM APPS.FND_LOOKUP_VALUES_VL FLV
       WHERE  LOOKUP_TYPE = 'ITA_PROFILE_LEVEL_ID' AND FLV.LOOKUP_CODE= FPOV.LEVEL_ID ) AS "层次级别",
       -- FR.RESPONSIBILITY_NAME AS "职责名称"
        VAL.levle_value_desc ,
        FPOV.LAST_UPDATE_DATE
      -- ,FPOV.*
     -- , FPO.*
  FROM -- APPS.PER_SECURITY_PROFILES PSP,  -- 定义配置文件选项
        APPS.fnd_profile_option_values  FPOV ,  -- 配置文件值
       APPS.FND_PROFILE_OPTIONS_VL FPO,  -- 配置文件选项 
      -- APPS.FND_RESPONSIBILITY_VL  FR,  -- 职责 
       ( -- 10001:地点
       SELECT 10001 AS LEVEL_ID,
             NULL AS LEVEL_VALUE_APPLICATION_ID,
             NULL AS level_value,
             NULL AS levle_value_desc
        FROM DUAL 
        UNION ALL 
        -- 10002:应用产品
       SELECT 10002 AS LEVEL_ID,
             NULL AS LEVEL_VALUE_APPLICATION_ID,
             FA.APPLICATION_ID AS level_value,
             FA.APPLICATION_SHORT_NAME ||'-'|| FA.APPLICATION_NAME AS levle_value_desc
        FROM APPS.FND_APPLICATION_VL FA 
        UNION ALL  
       -- 10003：责任层
       SELECT 10003 AS LEVEL_ID,
              fr.APPLICATION_ID AS LEVEL_VALUE_APPLICATION_ID,
              FR.RESPONSIBILITY_ID AS level_value,
              FR.RESPONSIBILITY_NAME AS levle_value_desc
        FROM APPS.FND_RESPONSIBILITY_VL  FR
       UNION 
       -- 10004：用户层
       SELECT 10004 AS level_id ,
              NULL LEVEL_VALUE_APPLICATION_ID ,
               fu.user_id AS level_value,
               fu.user_name ||'-'|| fu.description AS levle_value_desc
       FROM applsys.fnd_user fu
       UNION ALL
       -- 10005:服务器 (N:系统管理员>>ORACLE APPLICATIONS 管理器>>主机。)
       SELECT 10005 AS LEVEL_ID,
             NULL AS LEVEL_VALUE_APPLICATION_ID,
             SVR.NODE_ID AS level_value,
             SVR.NODE_NAME AS levle_value_desc
        FROM APPS.fnd_nodes  svr
        UNION ALL 
        -- 10006:组织
       SELECT 10006 AS LEVEL_ID,
             NULL AS LEVEL_VALUE_APPLICATION_ID,
             HOU.ORGANIZATION_ID AS level_value,
             HOU.SHORT_CODE ||'-'|| HOU.NAME AS levle_value_desc
        FROM APPS.HR_OPERATING_UNITS  HOU 
       ) val
 WHERE 1=1
  --AND PSP.SECURITY_PROFILE_ID = FPOV.PROFILE_OPTION_VALUE
 -- AND PSP.SECURITY_PROFILE_NAME = 'CUX采购组织安全性'
 -- AND FPOV.LAST_UPDATE_DATE>  TO_DATE('2023-02-16','YYYY-MM-DD')
  AND FPOV.APPLICATION_ID = FPO.APPLICATION_ID 
  AND FPOV.PROFILE_OPTION_ID = FPO.PROFILE_OPTION_ID
 -- AND FPO.PROFILE_OPTION_ID = 1261 -- 3796：MO：安全性配置文件   ,9933:GL：数据访问权限集:
  AND FPOV.LEVEL_ID = '10003' -- 10003 :责任层,10004:用户层
  AND FPOV.LEVEL_ID = VAL.LEVEL_ID(+) 
  AND FPOV.LEVEL_VALUE = VAL.LEVEL_VALUE(+)
  AND NVL(FPOV.LEVEL_VALUE_APPLICATION_ID,'-1') = NVL( VAL.LEVEL_VALUE_APPLICATION_ID(+) ,'-1')
 -- AND FR.RESPONSIBILITY_ID(+) = FPOV.LEVEL_VALUE
 -- AND FR.APPLICATION_ID(+) = FPOV.LEVEL_VALUE_APPLICATION_ID 
 -- AND  FPO.PROFILE_OPTION_NAME IN ('ORG_ID','DEFAULT_ORG_ID','XLA_MO_SECURITY_PROFILE_LEVEL')
 -- AND FR.RESPONSIBILITY_NAME LIKE 'CUX_PO_采购员'  --职责名称  
  -- AND FPOV.LEVEL_VALUE = 201
ORDER BY -- FR.RESPONSIBILITY_NAME, 
   FPO.PROFILE_OPTION_ID,FPOV.LEVEL_VALUE

　　

 

查询 EBS 系统配置文件的脚本不好写呀