Script to check and update Profile value in ebs R12

SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value
FROM
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
upper(pro1.user_profile_option_name) like upper('%&profile%')
and  pro.profile_option_name = pro1.profile_option_name
and  pro.profile_option_id = pov.profile_option_id
--and  resp.responsibility_name like '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */
and  pov.level_value = resp.responsibility_id (+)
and  pov.level_value = appl.application_id (+)
and  pov.level_value = u.user_id (+)
order by 1,2;

************************************

1. Run this sql query to check the value of the profile option at all levels:

set serveroutput on
set echo on
set timing on
set feedback on
set long 10000
set linesize 120
set pagesize 132
column SHORT_NAME format A30
column NAME format A40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format A60 wrap

select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10007, 'SERVRESP',
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE
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(p.profile_option_name) like '%FND_INIT_SQL%'
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 short_name, level_set;



2. One can also run this sql statement if one is sure to have updated the profile option at Site level only:

SELECT val.level_id, val.level_value, val.profile_option_id, val.profile_option_value
FROM fnd_profile_options opt, fnd_profile_option_values val
WHERE opt.profile_option_name = 'FND_INIT_SQL'
AND opt.profile_option_id = val.profile_option_id;



3. Update the profile option to a NULL value.
For instance if one just updated the same at site level one should run (as APPS user):

UPDATE fnd_profile_option_values
SET profile_option_value = NULL
WHERE profile_option_id = 3157 AND
level_id = 10001 AND
level_value = 0;

COMMIT;

Instead of that use this PROCEDURE below:

set serveroutput on;
DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('GUEST_USER_PWD', 'GUEST/ORACLE', 'SITE');
IF stat THEN
    dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
    dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
commit;
END;

How to Change Profile Option Value Without Forms? (Doc ID 943710.1)

How To Check If a Profile Option Is Set In Oracle Applications? (Doc ID 470102.1)