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
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020
This is an excellent and very easy to use scripts! wow! it works like magic.
Lovely !
how can I find what schema belongs to fnd_user_pkg.createuser ?? thanks
very helpful scripts thank you brother
Many thanks appreciated