How do I list Oracle Apps profile options in PL/SQL?

Question

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.

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

Answer 1

You'll want to query APPLSYS.FND_PROFILE_OPTIONS and FND_PROFILE_OPTION_VALUES. For a comprehensive script that you can pick up the SQL from, look here: http://tipsnscripts.com/?p=16

Answer 2

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:

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;

Answer 3

Thanks for this wonderful answer. Better try this later.