环境：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 FRWHERE FR.RESPONSIBILITY_ID = FPOV.LEVEL_VALUEAND 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 PSP2WHERE 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.DESCRIPTIONFROM APPS.FND_LOOKUP_VALUES_VL FLVWHERE 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_descFROM 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_descFROM 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_descFROM APPS.FND_RESPONSIBILITY_VL FRUNION -- 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_descFROM applsys.fnd_user fuUNION 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_descFROM APPS.fnd_nodes svrUNION 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_descFROM APPS.HR_OPERATING_UNITS HOU ) valWHERE 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 系统配置文件的脚本不好写呀