Many times, you might need to update a profile for multiple users. Changing through the form will be a tedious job, we can change using the plsql procedure from the backend. Here is an example to set the Apps Local Login Type to LOCAL for Multiple users
SET SERVEROUTPUT ON SIZE 1000000
DECLARE cursor user_cur is
select USER_ID from apps.fnd_user where user_name in ('username1','username2','username3');
v_success BOOLEAN;
l_user_name VARCHAR2(100);
BEGIN
FOR v_user_id IN user_cur
LOOP
select user_name into l_user_name from apps.fnd_user where user_id = v_user_id.user_id;
v_success := apps.FND_PROFILE.SAVE
( x_name => 'APPS_SSO_LOCAL_LOGIN',
x_value => 'LOCAL' ,
x_level_name => 'USER' ,
x_level_value => v_user_id.user_id ,
x_level_value_app_id => NULL ) ;
if v_success then
DBMS_OUTPUT.PUT_LINE('Employee with user name ' ||l_user_name ||' updated');
else
DBMS_OUTPUT.PUT_LINE('Failed to update Profile Option. Error:'||sqlerrm);
end if;
Commit;
END LOOP;
END;
/
The above plsql procedure needs to be run as apps database user. You can put your username in the cursor and then run it
x_name is the PROFILE_OPTION_NAME in the plsql block. So you can find out this if you know user profile option name.
column PROFILE_OPTION_NAME format A40
column USER_PROFILE_OPTION_NAME format A60 wrap
select o.PROFILE_OPTION_ID,o.PROFILE_OPTION_NAME,v.USER_PROFILE_OPTION_namE
from apps.FND_PROFILE_OPTIONs o , apps.FND_PROFILE_OPTIONS_TL v
where v.PROFILE_OPTION_NAME = o.PROFILE_OPTION_NAME
and upper(v.USER_PROFILE_OPTION_namE) like upper('%&profname%')
and v.LANGUAGE='US';
I hope you like this post on Script to Update a profile for Multiple Users in EBS. Please do provide the feedback
Related Articles
APPLSYSPUB schema : applsyspub schema is the public schema in Oracle EBS which is used first while connecting to Oracle Forms and Oracle OAF pages.
oracle apps queries : This page contains the very useful and practical Top 30 Useful oracle apps queries for APPS DBA to help in day to day administration activities
FNDLOAD Command: FNDLOAD command / loader is a general-purpose utility that moves structured data between a text file and a database in EBS environment.