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 changed 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).
NOTES: If you have to change a Profile option but the EBS webpage is not available, try FND_PROFILE package (for example):
declare value Boolean;
begin
value := FND_PROFILE.SAVE('SIGNON_PASSWORD_CASE','1','SITE');
end;
/
