Adding new user in Oracle Apps (EBS) from back end

Application users in Oracle Apps (EBS) can be created from the front end as well as back end. The back end method for user creation can be used when you don’t have sysadmin access to the applciation or also it can be used to speed up the process when there are many userids to be created.

You will require apps access to connect to database  to run the package fnd_user_pkg.

OR

you need to connect by those database userids having execute permission on fnd_user_pkg. Also if you are using some other user, you need to prefix schema name (i.e. use apps.fnd_user_pkg.createuser)

We can create user, disable/enable user, add/delete responsibility to the user through back end using this seeded oracle API.

fnd_user_pkg is the seeded generic API provided by Oracle.

In this post we will see how to use below two functionality of this API
Step 1: createuser : the procedure to create the user
Step 2: addresp : the procedure to add responsibility to a userid[/stextbox]

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

• Author
• Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Latest posts by Brijesh Gogia (see all)

• Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2021
• Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2021
• Oracle Multitenant DB 2 : Benefits of the Multitenant Architecture - March 19, 2021