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 e-mail 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;
/
Subscribe to this comment's feedwritten by Francesco , October 24, 2008
written by Mukunthan Lakshmanan , October 24, 2008
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.createuser. So, can you please tell me, what values will be defaulted there??
Thanks,
Mukunthan L
written by Anil Passi , October 24, 2008
You can extend this script to add further responsibilities and user level profile option values.
Thanks,
Anil Passi
written by Robert , November 15, 2008
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_foundation_password, encrypted_user_password
FROM FND_USER
WHERE user_name LIKE upper('&username')
OR user_name LIKE upper(
(SELECT
substr(fnd_profile.value('GUEST_USER_PWD'),1
,instr(fnd_profile.value('GUEST_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.decrypt((
SELECT
fnd_web_sec.decrypt(UPPER(
(SELECT upper(fnd_profile.value('GUEST_USER_PWD')) FROM dual)
), :GUEST_found_pass)
FROM dual), :user_pass)
FROM dual) AS encrypted_user_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE upper('&username');
If we want to get the pass for apps user, can you please guide me how do it?
Thanks a lot.
Robert.
written by chiku , November 17, 2008
I needed some help with the fnd_user_pkg.delresp.
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 responsibilities.
Could you please throw some light on this...
Thanks,
Mona
written by Anil Passi- , November 17, 2008
You should use API, as per below to end-date responsibility.
This will take care of everything, see sample below
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 ;
Thanks
Anil Passi
written by Ram , January 07, 2009
I am trying to create user from other than APPS user using fnd_user_pkg.createuser 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.
Thanks,
Ram
written by Anil Passi-- , January 08, 2009
written by Ram , January 08, 2009
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
written by Saurabh Sonkusare , March 22, 2009
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.
Regards,
Saurabh
written by Anil Passi- , March 22, 2009
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
written by Vyaghresh , October 21, 2009
I wnat to list the users having more than one OU Responsibilities attached.
E.g Assume there are three Users A, B and C, User A have two responsibilities belongs to OU1 and User B have two responsibilities belongs to OU2 but user C have two responsibilities 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
written by Prakash Sharma , January 24, 2011
FNDLOAD User miration doesn't migrate personal information, Only user name and assigned responsibilities.
Is this possible to migrate associate person with the user through FND_USER_PKG API.
If yes.. could you plz explain me.
written by Anika Ranjan , January 24, 2011
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
written by Nikhilmistry , April 20, 2011
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
written by SD , July 27, 2011
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.
written by SB , April 21, 2012
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?
| Next > |
|---|
