C# 如何在 PL/SQL 中列出 Oracle Apps 配置文件选项？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/70742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I list Oracle Apps profile options in PL/SQL?
提问by Jonathan
I administrate several Oracle Apps environment, and currently check profile options in lots of environments by loading up forms in each environment, and manually checking each variable, which requires a lot of time.
我管理了几个 Oracle Apps 环境，目前通过在每个环境中加载表单并手动检查每个变量来检查许多环境中的配置文件选项，这需要大量时间。
Is there a snippet of code which will list profile options and at what level and who they are applied to?
是否有一段代码可以列出配置文件选项以及它们适用于什么级别和对象？
采纳答案by Sten Vesterli
You'll want to query APPLSYS.FND_PROFILE_OPTIONSand FND_PROFILE_OPTION_VALUES.
For a comprehensive script that you can pick up the SQL from, look here:
http://tipsnscripts.com/?p=16
您将要查询APPLSYS.FND_PROFILE_OPTIONS和FND_PROFILE_OPTION_VALUES。有关可以从中获取 SQL 的综合脚本，请查看此处：http:
//tipsnscripts.com/?p=16
回答by Jonathan
Armed with the knowledge of which tables to get (thanks Sten) and a bit of judicious editing, I have come up with a query which serves my needs:
掌握了要获取哪些表的知识（感谢 Sten）并进行了一些明智的编辑，我提出了一个满足我需求的查询：
SELECT SUBSTR(e.profile_option_name,1,30) PROFILE,
DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Responsibility',10004,'User') L,
DECODE(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue,
NVL(a.profile_option_value,'Is Null') Value,
SUBSTR(a.last_update_date,1,25) UPDATED_DATE
FROM fnd_profile_option_values a
INNER JOIN fnd_profile_options e ON a.profile_option_id = e.profile_option_id
LEFT OUTER JOIN fnd_responsibility_tl b ON a.level_value = b.responsibility_id
LEFT OUTER JOIN fnd_application c ON a.level_value = c.application_id
LEFT OUTER JOIN fnd_user d ON a.level_value = d.user_id
WHERE e.profile_option_name LIKE '%&1%'
ORDER BY profile_option_name;
回答by caodan740
SELECT SUBSTR(e.profile_option_name,1,30) PROFILE,
DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Responsibility',10004,'User') L,
DECODE(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue,
NVL(a.profile_option_value,'Is Null') Value,
SUBSTR(a.last_update_date,1,25) UPDATED_DATE
FROM fnd_profile_option_values a
INNER JOIN fnd_profile_options e ON a.profile_option_id = e.profile_option_id
LEFT OUTER JOIN fnd_responsibility_tl b ON a.level_value = b.responsibility_id
LEFT OUTER JOIN fnd_application c ON a.level_value = c.application_id
LEFT OUTER JOIN fnd_user d ON a.level_value = d.user_id
WHERE e.profile_option_name LIKE '%&1%'
ORDER BY profile_option_name;
回答by SJ-Admin
I hope this will help you get more granular information when you try to track down changes by users.
我希望这将帮助您在尝试跟踪用户更改时获得更详细的信息。
SELECT FP.LEVEL_ID "Level ID",
FPO.PROFILE_OPTION_NAME "PROFILE NAME",
FP.LEVEL_VALUE "LEVEL VALUE",
DECODE (FP.LEVEL_ID,
10001,
'SITE',
10002,
'APPLICATION',
10003,
'RESPONSIBILITY',
10004,
'USER')
"LEVEL",
DECODE (FP.LEVEL_ID,
10001,
'SITE',
10002,
APPLICATION_SHORT_NAME,
10003,
RESPONSIBILITY_NAME,
10004,
FL.USER_NAME)
LVALUE,
FPO.USER_PROFILE_OPTION_NAME "PROFILE DESCRIPTION",
FP.PROFILE_OPTION_VALUE "PROFILE VALUE",
FU.USER_NAME "USER NAME",
FU.LAST_UPDATE_DATE
FROM FND_PROFILE_OPTIONS_VL FPO,
FND_PROFILE_OPTION_VALUES FP,
FND_RESPONSIBILITY_TL,
FND_APPLICATION FA,
FND_USER FL,
FND_USER FU
WHERE FPO.APPLICATION_ID = FP.APPLICATION_ID
AND FPO.PROFILE_OPTION_ID = FP.PROFILE_OPTION_ID
AND FP.LEVEL_VALUE = FL.USER_ID(+)
AND FP.LEVEL_VALUE = RESPONSIBILITY_ID(+)
AND FP.LEVEL_VALUE = FA.APPLICATION_ID(+)
AND FU.USER_ID = FP.LAST_UPDATED_BY
AND FP.PROFILE_OPTION_VALUE IS NOT NULL
AND (UPPER (FP.Profile_Option_Value) LIKE UPPER ('%&1%')
OR UPPER (FP.Profile_Option_Value) LIKE UPPER ('%&2%'))
