Sysadmin user creation & attaching responsibility

Very simple procedures can be created which have this API used in it to create and add responsibility to the user.

Step 1 : Simple procedure (anonymous block) to create a new user :

DECLARE
v_user_name VARCHAR2 (100) := upper(‘&Enter_User_Name’);
 v_description VARCHAR2 (100) := ‘NEW Test User';
BEGIN
 fnd_user_pkg.createuser
(x_user_name => v_user_name,
 x_owner => NULL,
 x_unencrypted_password => ‘&input_password’,
 x_session_number => 0,
 x_start_date => SYSDATE,
 x_end_date => NULL,
 x_last_logon_date => NULL,
 x_description => v_description,
 x_password_date => NULL,
 x_password_accesses_left => NULL,
 x_password_lifespan_accesses => NULL,
 x_password_lifespan_days => NULL,
 x_employee_id => NULL,
 x_email_address => NULL,
 x_fax => NULL,
 x_customer_id => NULL,
 x_supplier_id => NULL,
 x_user_guid => NULL,
 x_change_source => NULL
 );
 COMMIT;
END;
 /
SHOW ERR;

• The above procedure will ask for ‘User_name’ to create and the password (x_unencrypted_password) that you want to keep. Also these  value can be hard-coded if required to. (example > If you want to keep same initial password for all the created userids )
• The fields mentioned above should be changed as per security policies governing account creation in your organization. For example x_password_lifespan_days should be chosen as per the total number of day you want  the password to be valid.
• Email address(x_email_address), if available, can be input also.
• When a new user will login to the EBS, they will be re-directed to the change password screen for changing the default password kept above.
• In case the user already exists in the applciation, the procedure will show Error: ORA-20001 and message  ‘This user name is already in use. Please enter a unique user name.. ” and no action will be taken.

Step 2: Simple anonymous block to add responsibility to the user :

DECLARE
 v_user_name VARCHAR2 (100) := upper(‘&Enter_User_Name’);
BEGIN
fnd_user_pkg.addresp(username => v_user_name
 ,resp_app => ‘&APPLICATION_SHORT_NAME’
 ,resp_key => ‘&RESPONSIBILITY_KEY’
 ,security_group => ‘STANDARD’
 ,description => NULL
 ,start_date => SYSDATE
 ,end_date => null);
 commit;
END;
 /
SHOW ERR;

• You need to key in the value of user_name(&Enter_User_Name) to whom you want to attach the responsibility.
• The value of APPLICATION_SHORT_NAME and RESPONSIBILITY_KEY can be easily found out if you know the responsibility name. The below script can be used for finding these details.

set lines 132
 col RESPONSIBILITY_NAME format a50
select a.application_short_name, r.responsibility_key
from fnd_responsibility_vl r, fnd_application_vl a where
r.application_id =a.application_id
and upper(r.responsibility_name) in upper (‘&RESPONSIBILITY_NAME’);

Script to create new user and  to add ALL  Active responsibilities from existing user :

DECLARE
v_user_name VARCHAR2 (100) := upper(‘&Enter_User_Name’);
v_description VARCHAR2 (100) := ‘New User';
 v_app_short_name VARCHAR2 (100);
 v_resp_key VARCHAR2 (100);
 CURSOR user_cur IS
 select a.application_short_name, r.responsibility_key
 from fnd_responsibility_vl r, fnd_application_vl a where
 r.application_id =a.application_id
 and R.responsibility_ID IN (SELECT RESPONSIBILITY_ID FROM fnd_user_resp_groups WHERE USER_ID=&from_userid AND END_dATE IS NULL);
user_rec user_cur%ROWTYPE;
BEGIN
 fnd_user_pkg.createuser
(x_user_name => v_user_name,
 x_owner => NULL,
 x_unencrypted_password => ‘&input_password’,
 x_session_number => 0,
 x_start_date => SYSDATE,
 x_end_date => NULL,
 x_last_logon_date => NULL,
 x_description => v_description,
 x_password_date => NULL,
 x_password_accesses_left => NULL,
 x_password_lifespan_accesses => NULL,
 x_password_lifespan_days => NULL,
 x_employee_id => NULL,
 x_email_address => NULL,
 x_fax => NULL,
 x_customer_id => NULL,
 x_supplier_id => NULL,
 x_user_guid => NULL,
 x_change_source => NULL
 );
 COMMIT;
 OPEN user_cur;
 LOOP
 FETCH user_cur INTO user_rec;
 EXIT WHEN user_cur%NOTFOUND;
 fnd_user_pkg.addresp(username => v_user_name
 ,resp_app => user_rec.application_short_name
 ,resp_key => user_rec.responsibility_key
 ,security_group => ‘STANDARD’
 ,description => NULL
 ,start_date => SYSDATE
 ,end_date => null);
 END LOOP;
 CLOSE user_cur;
commit;
END;
/

Input variables required :

&Enter_User_Name   > New username to create
&from_userid               > userid from where all responsibilities needs to be copied
&input_password       > password for the new user