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
| Next > |
|---|
