<p>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.</p>

<p>Is there a snippet of code which will list profile options and at what level and who they are applied to?</p>

[{'answer_id': 71141, 'author': 'Sten Vesterli', 'author_id': 9363, 'author_profile': 'https://Stackoverflow.com/users/9363', 'pm_score': 2, 'selected': True, 'text': '<p>You\'ll want to query <code>APPLSYS.FND_PROFILE_OPTIONS</code> and <code>FND_PROFILE_OPTION_VALUES</code>. \nFor a comprehensive script that you can pick up the SQL from, look here: \n<a href="http://tipsnscripts.com/?p=16" rel="nofollow noreferrer">http://tipsnscripts.com/?p=16</a></p>\n'}, {'answer_id': 71387, 'author': 'Jonathan', 'author_id': 6910, 'author_profile': 'https://Stackoverflow.com/users/6910', 'pm_score': 0, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>SELECT SUBSTR(e.profile_option_name,1,30) PROFILE,\n    DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Responsibility',10004,'User') L,\n    DECODE(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue,\n    NVL(a.profile_option_value,'Is Null') Value,\n    SUBSTR(a.last_update_date,1,25) UPDATED_DATE\nFROM fnd_profile_option_values a\nINNER JOIN fnd_profile_options e ON a.profile_option_id = e.profile_option_id \nLEFT OUTER JOIN fnd_responsibility_tl b ON a.level_value = b.responsibility_id\nLEFT OUTER JOIN fnd_application c ON a.level_value = c.application_id\nLEFT OUTER JOIN fnd_user d ON a.level_value = d.user_id\nWHERE e.profile_option_name LIKE '%&amp;1%'\nORDER BY profile_option_name;\n</code></pre>\n"}, {'answer_id': 6454817, 'author': 'caodan740', 'author_id': 812300, 'author_profile': 'https://Stackoverflow.com/users/812300', 'pm_score': 0, 'selected': False, 'text': "<pre><code>SELECT SUBSTR(e.profile_option_name,1,30) PROFILE,\n    DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Responsibility',10004,'User') L,\n    DECODE(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue,\n    NVL(a.profile_option_value,'Is Null') Value,\n    SUBSTR(a.last_update_date,1,25) UPDATED_DATE\nFROM fnd_profile_option_values a\nINNER JOIN fnd_profile_options e ON a.profile_option_id = e.profile_option_id \nLEFT OUTER JOIN fnd_responsibility_tl b ON a.level_value = b.responsibility_id\nLEFT OUTER JOIN fnd_application c ON a.level_value = c.application_id\nLEFT OUTER JOIN fnd_user d ON a.level_value = d.user_id\nWHERE e.profile_option_name LIKE '%&amp;1%'\nORDER BY profile_option_name;\n</code></pre>\n"}, {'answer_id': 7261709, 'author': 'SJ-Admin', 'author_id': 922224, 'author_profile': 'https://Stackoverflow.com/users/922224', 'pm_score': 1, 'selected': False, 'text': '<p>I hope this will help you get more granular information when you try to track down changes by users.</p>\n\n<pre><code>SELECT FP.LEVEL_ID "Level ID", \n     FPO.PROFILE_OPTION_NAME "PROFILE NAME",\n     FP.LEVEL_VALUE "LEVEL VALUE",\n     DECODE (FP.LEVEL_ID,\n             10001,\n             \'SITE\',\n             10002,\n             \'APPLICATION\',\n             10003,\n             \'RESPONSIBILITY\',\n             10004,\n             \'USER\')\n        "LEVEL",\n     DECODE (FP.LEVEL_ID,\n             10001,\n             \'SITE\',\n             10002,\n             APPLICATION_SHORT_NAME,\n             10003,\n             RESPONSIBILITY_NAME,\n             10004,\n             FL.USER_NAME)\n        LVALUE,\n     FPO.USER_PROFILE_OPTION_NAME "PROFILE DESCRIPTION",\n     FP.PROFILE_OPTION_VALUE "PROFILE VALUE",\n     FU.USER_NAME "USER NAME",\n     FU.LAST_UPDATE_DATE\nFROM FND_PROFILE_OPTIONS_VL FPO,\n     FND_PROFILE_OPTION_VALUES FP,\n     FND_RESPONSIBILITY_TL,\n     FND_APPLICATION FA,\n     FND_USER FL,\n     FND_USER FU\nWHERE FPO.APPLICATION_ID = FP.APPLICATION_ID\n     AND FPO.PROFILE_OPTION_ID = FP.PROFILE_OPTION_ID\n     AND FP.LEVEL_VALUE = FL.USER_ID(+)\n     AND FP.LEVEL_VALUE = RESPONSIBILITY_ID(+)\n     AND FP.LEVEL_VALUE = FA.APPLICATION_ID(+)\n     AND FU.USER_ID = FP.LAST_UPDATED_BY\n     AND FP.PROFILE_OPTION_VALUE IS NOT NULL\n     AND (UPPER (FP.Profile_Option_Value) LIKE UPPER (\'%&amp;1%\')\n          OR UPPER (FP.Profile_Option_Value) LIKE UPPER (\'%&amp;2%\'))\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/70742', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/6910/']