You can use this SQL Script to create a FND_USER for any Technical Developer that joins your team.
This simple script will set all the desired admin responsibilities and all common profile options for a given FND_USER.
Salient features of this simple script are
1. If a user is inactive, that FND_USER will be re-activated
2. If any of their Sysadmin or WF Admin or Application Developer or Functional Admin responsibilities are inactive, those responsibilities will be re-activated
3. Add commonly used responsibilities will be assigned to the user
3. Personalization, Diagnostic and Utilities profile options will be assigned to this user.
4. Password expiration will be removed from the FND_USER
In a nutshell, use this script to enable a user and to enable all their administrator responsibilities.
This script can be run as many times for any given user.
In case a user does not exist, then new user will be created with default password being oracle123
Please find the script below
Save this as a SQL File, and it will prompt for parameter User Name
DECLARE
--By: Anil Passi
--When Jun-2007
v_session_id INTEGER := userenv('sessionid');
v_user_name VARCHAR2(30) := upper('&Enter_User_Name');
result BOOLEAN;
v_user_id INTEGER;
FUNCTION check_fu_name(p_user_name IN VARCHAR2) RETURN BOOLEAN IS
CURSOR c_check IS
SELECT 'x' FROM fnd_user WHERE user_name = p_user_name;
p_check c_check%ROWTYPE;
BEGIN
OPEN c_check;
FETCH c_check
INTO p_check;
IF c_check%FOUND
THEN
/*Yes, it exists*/
CLOSE c_check;
RETURN TRUE;
END IF;
CLOSE c_check;
RETURN FALSE;
END check_fu_name;
BEGIN
IF NOT (check_fu_name(p_user_name => v_user_name))
THEN
fnd_user_pkg.createuser(x_user_name => v_user_name
,x_owner => ''
,x_unencrypted_password => 'oracle123'
,x_session_number => v_session_id
,x_start_date => SYSDATE - 10
,x_end_date => SYSDATE + 100
,x_last_logon_date => SYSDATE - 10
,x_description => 'Anil Passi'
,x_password_date => SYSDATE - 10
,x_password_accesses_left => 10000
,x_password_lifespan_accesses => 10000
,x_password_lifespan_days => 10000
,x_employee_id => NULL /*Change this id by running below SQL*/
/*
SELECT person_id
,full_name
FROM per_all_people_f
WHERE upper(full_name) LIKE '%' || upper('<ampersand>full_name') || '%'
GROUP BY person_id
,full_name
*/
,x_email_address => This email address is being protected from spambots. You need JavaScript enabled to view it.'
,x_fax => ''
,x_customer_id => ''
,x_supplier_id => '');
dbms_output.put_line ( 'FND_USER Created' ) ;
ELSE
fnd_user_pkg.updateuser(x_user_name => v_user_name
,x_owner => 'CUST'
,x_end_date => fnd_user_pkg.null_date
,x_password_date => SYSDATE - 10
,x_password_accesses_left => 10000
,x_password_lifespan_accesses => 10000
,x_password_lifespan_days => 10000);
dbms_output.put_line ( 'End Date removed from FND_USER ' ) ;
END IF;
SELECT user_id
INTO v_user_id
FROM fnd_user
WHERE user_name = v_user_name;
fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'FND'
,resp_key => 'FND_FUNC_ADMIN'
,security_group => 'STANDARD'
,description => 'Anil Passi apps2fusion.com'
,start_date => SYSDATE - 1
,end_date => SYSDATE + 10000);
fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Anil Passi apps2fusion.com'
,start_date => SYSDATE - 1
,end_date => SYSDATE + 10000);
fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'FND'
,resp_key => 'FNDWF_ADMIN_WEB'
,security_group => 'STANDARD'
,description => 'Anil Passi apps2fusion.com'
,start_date => SYSDATE - 1
,end_date => SYSDATE + 10000);
fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'FND'
,resp_key => 'APPLICATION_DEVELOPER'
,security_group => 'STANDARD'
,description => 'Anil Passi apps2fusion.com'
,start_date => SYSDATE - 1
,end_date => SYSDATE + 10000);
fnd_user_pkg.addresp(username => v_user_name,
resp_app => 'ICX',
resp_key => 'PREFERENCES',
security_group => 'STANDARD',
description => 'Anil Passi apps2fusion.com',
start_date => sysdate - 1,
end_date => null);
result := fnd_profile.save(x_name => 'APPS_SSO_LOCAL_LOGIN'
,x_value => 'BOTH'
,x_level_name => 'USER'
,x_level_value => v_user_id);
result := fnd_profile.save(x_name => 'FND_CUSTOM_OA_DEFINTION'
,x_value => 'Y'
,x_level_name => 'USER'
,x_level_value => v_user_id);
result := fnd_profile.save(x_name => 'FND_DIAGNOSTICS'
,x_value => 'Y'
,x_level_name => 'USER'
,x_level_value => v_user_id);
result := fnd_profile.save(x_name => 'DIAGNOSTICS'
,x_value => 'Y'
,x_level_name => 'USER'
,x_level_value => v_user_id);
result := fnd_profile.save(x_name => 'FND_HIDE_DIAGNOSTICS'
,x_value => 'N'
,x_level_name => 'USER'
,x_level_value => v_user_id);
COMMIT;
END;
/
Apps2Fusion Articles 
Comments
This one is fantastic... but, i do have some small questions - kindly get them resolved!
In the FND_USERS table, the columns CREATED_BY and LAST_UPDATED_BY are NOT NULL columns - which I don't see being filled up, in the package above - fnd_user_pkg.cr eateuser. So, can you please tell me, what values will be defaulted there??
Thanks ,
Mukunthan L
You can set FND_GLOBAL variables by running the script below, or by running fnd_global.apps _initialize
Quote:
Thanks,
Anil Passi
You can extend this script to add further responsibilitie s and user level profile option values.
Thanks,
Anil Passi
Could you please guide me for to get the apps password?.
For recover the pass for any user, we run the following query in the instance
SELECT USER_NAME , encrypted_found ation_password, encrypted_user_ password
FROM FND_USER
WHERE user_name LIKE upper('&usernam e')
OR user_name LIKE upper(
(SELECT
substr(fnd_prof ile.value('GUES T_USER_PWD'),1
,instr(fnd_prof ile.value('GUES T_USER_PWD'),'/ ')-1)
FROM dual)
)
And then, in other instance, we public the function "decrypt" within package FND_WEB_SEC.
Then, we run the following query for to get the pass for any user.
SELECT usertable.user_ name
, (SELECT
fnd_web_sec.dec rypt((
SELECT
fnd_web_sec.dec rypt(UPPER(
(SELECT upper(fnd_profi le.value('GUEST _USER_PWD')) FROM dual)
), :GUEST_found_pa ss)
FROM dual), :user_pass)
FROM dual) AS encrypted_user_ password
FROM fnd_user usertable
WHERE usertable.user_ name LIKE upper('&usernam e');
If we want to get the pass for apps user, can you please guide me how do it?
Thanks a lot.
Robert.
I needed some help with the fnd_user_pkg.de lresp.
We have some users assigned to a responsibility which they are not supposed to have.
How do I delete or end date the assignment of the responsibility without affecting or breaking anything.
I went through this api but I see some workflow related stuff. I fear I might break something if I use this api to delete the assigned responsibilitie s.
Could you please throw some light on this...
Thanks ,
Mona
You should use API, as per below to end-date responsibility.
This will take care of everything, see sample below
fnd_user_pkg.ad dresp(username => v_user_name
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINIS TRATOR'
,security_group => 'STANDARD'
,description => 'Anil Passi apps2fusion.com '
,start_date => SYSDATE - 1
,end_date => SYSDATE ;
Thanks
Anil Passi
I am trying to create user from other than APPS user using fnd_user_pkg.cr eateuser API. I gave neccessary grant privileges and also created synonyms.
I am able to create new user without any problem in one instance but in another instance with the same approach following error is displayed "ORA-20001: Custom
user name validation in subscription to event oracle.fnd.user .name.validate failed". Please let me know what might the problem.
Thank s,
Ram
Thanks for the quick response.
Can you please tell me where i can look for this subscription. But when we are trying to create from Apps forntend we are not getting this error.
Thanks,
Ram
First of all, thanks for the script.
I have a question. How can we utilize the same script (with some modifications) to register Suppliers as Users. This is mainly required for iSupplier User Registration but we are unable to find any API related to that.
We have IBM AIX server, Oracle 11.5.10.
Regar ds,
Saurabh
Setup a user account in that responsibility and see the trace files for the API's used by Oracle.
I believe it is fnd_user_pkg itself and Oracle might be passing customer_id paramter when creating the username.
But you can doublecheck that using the trace file.
Thanks,
Anil Passi
I wnat to list the users having more than one OU Responsibilitie s attached.
E.g Assume there are three Users A, B and C, User A have two responsibilitie s belongs to OU1 and User B have two responsibilitie s belongs to OU2 but user C have two responsibilitie s one belongs to OU1 and another belongs to Ou2.
Now i need to list only the User C.
Please assist me how to proceed with this.
Oracle Release : 11510
FNDLOAD User miration doesn't migrate personal information, Only user name and assigned responsibilitie s.
Is this possible to migrate associate person with the user through FND_USER_PKG API.
If yes.. could you plz explain me.
I have migrated users and responsibility through FNDLOAD script, but the password for the users is not getting migrated.
I have checked the ldt files and they dont contain passwod information for the users.
I have also checked the fnd_user table and the end_date for the users is set to null.
I am not able to understand why FNDLOAD is not able to pick the passwod information. Please guide me in this regard.
Thanks ,
Anika
does oracle apps has any function which we can use when any of the FND_USER's password get changed DBA (management_ get alert?
Nikhil
I have a requirement where I have to end-date responsibility and then re-instate the same responsiblities . Can you please guide me how this can be done with above package.
I want to delete a user . Could you please let me know how I can do that. One quick question Other than Inactivate Cann't we delete a user?
Our test EBS sysadmin got locked out. How can I unlock it ??
Regards
Pas ha
I am going to recommend this blog!
and reporting! Keep up the very good works guys I've incorporated you guys to blogroll.
de herfst of lente plus de avondjes van de
zomers.
Stop by my blog :: Luttersville.Co m: http://Luttersville.com/trachycarpus-1358/
I'm kinda paranoid about losing everything I've worked hard on.
Any recommendations ?
more details.
circumspection
milk farm hentai
website!I suppose its ok to use a few of your ideas!!
guardianship
tickle central videos
as I found this post at this website.
you download it from somewhere? A design like yours with a few simple tweeks would really make my blog stand out.
Please let me know where you got your theme.
Thanks a lot
I might check things out. I like what I see so now i am following you.
Look forward to finding out about your web page for a second time.
and gave it to my 4 year old daughter and said "You can hear the ocean if you put this to your ear." She put the shell
to her ear and screamed. There was a hermit crab inside and it pinched her ear.
She never wants to go back! LoL I know this is totally off topic but I had to tell someone!
I'm sending it to some buddies ans additionally sharing in delicious.
And certainly, thanks to your sweat!
topic of this post, in my view its actually awesome for me.
and personally recommend to my friends. I am sure they will be benefited from this website.
one is getting more from this website, and your views are nice designed
for new visitors.
I will bookmark your web site and take the feeds additionally?
I'm glad to find a lot of helpful information right here in the
publish, we'd like work out extra techniques in this
regard, thanks for sharing. . . . . .
I needs to spend some time learning much more or understanding more.
Thanks for great information I was looking for this info for my
mission.
to be happy. I've read this post and if I could I want
to suggest you few interesting things or tips. Maybe you
could write next articles referring to this article. I desire to read even more
things about it!
new visitors.
I say to you, I definitely get irked even as folks think about concerns that they just do not recognize about.
You controlled to hit the nail upon the highest as smartly as defined out the whole thing
with no need side effect , other people could take a signal.
Will probably be again to get more. Thank you
I found this website as a finest site for latest updates.
to find out about this topic. You realize a whole lot its almost hard to argue with you
(not that I actually will need to…HaHa). You certainly put a fresh spin on a subject that's
been written about for a long time. Excellent stuff, just excellent!
upon your site and in accession capital to assert that I get in fact enjoyed account your weblog posts.
Any way I will be subscribing on your augment and even I fulfillment you get admission to consistently quickly.
I am impressed! Extremely useful information specially the remaining section :
) I handle such information a lot. I was seeking this certain info for a
very lengthy time. Thanks and best of luck.
It seems like some of the written text within your posts are running off the screen. Can somebody else please comment and let me know if this is happening
to them too? This may be a problem with my web browser because I've
had this happen before. Kudos
Where are your contact details though?
very good.
There's a lot of people that I think would really
enjoy your content. Please let me know. Thank you
you using for this website? I'm getting fed up of Wordpress because I've had problems with
hackers and I'm looking at options for another platform. I
would be great if you could point me in the direction of a good platform.
RSS feed for comments to this post