SQL scripts show R12 Profile Options

Below query will find the value on Profile option(s). It is modified from a script in Doc ID 1159313.1 "Unable To Authenticate Session Error When Login to Different R12 Instance in Same IE Session". Exact profile name is in table fnd_profile_options_tl .

SELECT p.profile_option_name, fpot.user_profile_option_name NAME,
decode(v.level_id,
             10001,'SITE',
             10002, (select 'App:'||a.application_short_name
                            from fnd_application a
                          where a.application_id = v.level_value),
             10003, (select 'Resp:'||f.RESPONSIBILITY_KEY
                            from fnd_responsibility f
                          where f.responsibility_id = v.level_value),
             10004, (select 'User:'||u.user_name
                            from fnd_user u
                          where u.user_id = v.level_value),
             10005, (select 'Server:'||n.node_name
                            from fnd_nodes n
                          where n.node_id = v.level_value),
             10006, (select 'Org:'|| o.name
                            from hr_operating_units o
                          where o.organization_id = v.level_value),
             'NOT SET') PROF_LEVEL,
              nvl(v.profile_option_value,'NOT SET') profile_option_value, fpot.description
FROM
  fnd_profile_options p,
  fnd_profile_option_values v,
  fnd_profile_options_tl fpot
WHERE
p.profile_option_id = v.profile_option_id (+)
and p.application_id = v.application_id (+)
and p.profile_option_name = fpot.profile_option_name
-- and p.profile_option_name = 'FND_DIAGNOSTICS'
-- and fpot.user_profile_option_name like 'FND: D%'
and fpot.user_profile_option_name like 'Signon%'
order by 1, v.level_id;

A query to find ALL profile Option values (modified from a script in Doc ID 2026081.1):

select n.user_profile_option_name NAME,
       decode(v.level_id,
               10001, 'Site',
               10002, 'Application',
               10003, 'Responsibility',
               10004, 'User',
               10005, 'Server',
               10006, 'Organization',
               10007, 'ServResp',
               'UnDef') LEVEL_SET,
       decode(to_char(v.level_id),
              '10001', '',
              '10002', app.application_short_name,
              '10003', rsp.responsibility_key,
              '10004', usr.user_name,
              '10005', svr.node_name,
              '10006', org.name,
              '10007', 'depends=',
              v.level_id) "CONTEXT",
       v.profile_option_value VALUE,
       (select n.node_name
          from
               fnd_nodes n
         where
               n.node_id=level_value2) Server,
               decode(v.LEVEL_VALUE,
               -1, 'Default',
               rsp.responsibility_key) Resp,
               decode(LEVEL_VALUE_APPLICATION_ID,
               -1, 'Default',
               app.application_short_name) Application
  from fnd_profile_options p,
       fnd_profile_option_values v,
       fnd_profile_options_tl n,
       fnd_user usr,
       fnd_application app,
       fnd_responsibility rsp,
       fnd_nodes svr,
       hr_operating_units org
 where p.profile_option_id = v.profile_option_id (+)
   and p.profile_option_name = n.profile_option_name
  -- and (upper(v.profile_option_value) like '%HTTP%')   -- if you want to find the value, enable this line
  -- and p.profile_option_name like 'APPLICATIONS_HOME%'   -- if you want to find the name
   and    usr.user_id (+) = v.level_value
   and    rsp.application_id (+) = v.level_value_application_id
   and    rsp.responsibility_id (+) = v.level_value
   and    app.application_id (+) = v.level_value
   and    svr.node_id (+) = v.level_value
   and    org.organization_id (+) = v.level_value
 order by name, level_set;

A change in Profile option can make the EBS run very differently. Below script will find Profile options that were change within one day.

 SELECT '''' || a.user_profile_option_name
       || ''' Was Updated with value '
       || b.profile_option_value mesg,
       (SELECT user_name
             FROM apps.fnd_user u
            WHERE u.user_id = b.last_updated_by) who , b.last_update_date, b.level_value
  FROM apps.fnd_profile_options_vl a,
       apps.fnd_profile_option_values b,
       apps.fnd_user c
 WHERE a.profile_option_id = b.profile_option_id
   AND b.last_updated_by = c.user_id
   AND (   b.last_update_date > SYSDATE - 1   -- <- put a different number here if needed
        OR b.creation_date > SYSDATE - 1
       );
NOTE: Not sure why, but this query is not accurate sometimes (when the value was changed to null).