Find Profile Option values using SQL queries

Posted by Apurva at 3:24 AM Read our previous post

Many times we want to find a value for a particular profile option, but cannot find it as we may not have the System Administrator Responsibility.
In such cases i find this below query very useful.
Here we can give a where clause with Profile option name like for Eg. OS%
We can also add an additional where clause a.last_updated_date to get the recently updated profile values.

The SQL is


SELECT e.profile_option_name Profile,
        f.user_profile_option_name User_Profile_Name, c.application_short_name,
        decode(a.level_id,10001,'Site',10002,'Application',10003,'Resp',10004,'User') LevelSet_At,
        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
   FROM  fnd_profile_option_values a, fnd_responsibility_tl b,
        fnd_application c, fnd_user d, fnd_profile_options e, fnd_profile_options_vl f
      WHERE  f.user_profile_option_name like 'OS%'
             AND  e.profile_option_id = a.profile_option_id
          AND  e.profile_option_id = f.profile_option_id
       AND  a.level_value = b.responsibility_id (+)
       AND  a.level_value = c.application_id (+)
    AND  a.level_value = d.user_id (+)
   ORDER BY 1,2;


You may also like to read      Process to Debug a Concurrent Request
                                             Checking the File Versions with SQL Query
                                             Sending Email using PL/SQL Procedure
                                             Find Trace File location using SQL