Thursday, September 09, 2010

Oracle EBS mass create users

When using Oracle e-Business suite in a production environment you most likely will have besides a production instance some other instances. For example you might have an instance for developers and a Oracle eBS instance for your support organization. In most cases developers and possibly people from the support organization do not have an account on eBS.

In those cases you would like to have those accounts generated every time a instance is refreshed from a production instance. You can decide to have your developers do this manually as they most likely will have a high level of database access however it is more convenient to incorporate the generation of accounts into your cloning scripting.

The below script can be used to do this. It can also be used to generate for example user accounts on a production environment in case you need to do a mass creation of users. It is however advisable to change the script a bit if you intend to use it for this purpose. For cloning it is very useful.


/*********************************************************************************/
/*    after_clone_user_create_ebs.sql                                            */
/*                                                                               */
/*    PL/SQL script to create mass users for Oracle EBS non production instances */
/*    after a refresh from production. Developers and such can be added by this  */
/*    script.This code is tested on Oracle EBS 11.5.10.2 systems                 */
/*                                                                               */
/*    Copyright (C) 2010  Johan Louwers                                          */
/*                                                                               */
/*    This program is free software: you can redistribute it and/or modify       */
/*    it under the terms of the GNU General Public License as published by       */
/*    the Free Software Foundation, either version 3 of the License              */
/*                                                                               */
/*    This program is distributed in the hope that it will be useful,            */
/*    but WITHOUT ANY WARRANTY; without even the implied warranty of             */
/*    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the              */
/*    GNU General Public License for more details.                               */
/*                                                                               */
/*    You should have received a copy of the GNU General Public License          */
/*    along with this program.  If not, see http://www.gnu.org/licenses.         */
/*                                                                               */
/*    CONTRIBUTIONS                                                              */
/*    JLO          Johan.Louwers{at}capgemini.com                                */
/*                                                                               */
/*                                                                               */
/*    VERISON    DATE         DEVELOPER   DESC                                   */
/*    1.0        28-AUG-2010  JLO         Initial creation                       */
/*    1.1        29-AUG-2010  JLO         Added logging to the output.           */
/*                                                                               */
/*                                                                               */
/*********************************************************************************/

Set serveroutput ON;

DECLARE
/*--------------------------------------------*/
/* declare all the variables used in the      */
/* script.                                    */
/*                                            */
/*                                            */
/*--------------------------------------------*/
createdUser varchar2(200);
TYPE t_users IS VARRAY(60) OF VARCHAR2(20);
TYPE t_resp IS VARRAY(60) OF VARCHAR2(50);   


/*--------------------------------------------*/
/* State the usernames which have to be       */
/* created by the program. If the name is     */
/* already in the system it will not be       */
/* created again.                             */
/*--------------------------------------------*/
t_list_users t_users := t_users('SOMEUSERNAME'
,'ANOTHERUSERNAME'
,'SOMEUSER'
);


/*--------------------------------------------*/
/* list the resps that will be added to each  */
/* new user. The values are based upon the    */
/* value of RESPONSIBILITY_KEY as stated in   */
/* FND_RESPONSIBILITY. If the KEY is not      */
/* valid a error will be thrown               */
/*--------------------------------------------*/                                      
t_list_resp t_resp := t_resp('SYSTEM_ADMINISTRATOR'
,'APPLICATION_DEVELOPER'
);    


/*--------------------------------------------*/
/* cursor c_user_exists will be used to check */
/* if the user is already in the system. This */
/* is to prevent a error from happening by    */
/* entering a user for the second time which  */
/* is not allowed by fnd_user_pkg.CreateUser  */
/*--------------------------------------------*/ 
CURSOR c_user_exists (p_username IN VARCHAR2) IS
SELECT 
COUNT (us.user_name) AS usercounter
FROM 
apps.fnd_user us
WHERE 
us.user_name = (p_username);
r_user_exists c_user_exists%rowtype; 


/*--------------------------------------------*/
/* cursor c_resp_details will be query all    */ 
/* details for a resp needed when adding it to*/
/* a new user.                                */
/*                                            */  
/*--------------------------------------------*/
CURSOR c_resp_details (p_respkey IN VARCHAR2) IS
SELECT   
fav.application_short_name
,fav.application_name
,frv.responsibility_key
,frv.responsibility_name
FROM 
fnd_application_vl fav
,fnd_responsibility_vl frv
WHERE 
frv.application_id = fav.application_id
AND frv.responsibility_key = (p_respkey);
r_resp_details c_resp_details%rowtype; 




BEGIN
FOR i IN t_list_users.FIRST .. t_list_users.LAST
LOOP

DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('+ Checking for user '||t_list_users(i));

/*--------------------------------------------*/
/* Check if the user is already in the system */ 
/* if this is the case we will not create we  */
/* will only print a warning to the screen    */
/*                                            */  
/*--------------------------------------------*/
open c_user_exists(t_list_users(i));
fetch c_user_exists into r_user_exists;

IF r_user_exists.usercounter > 0
THEN
DBMS_OUTPUT.PUT_LINE(' - User '||t_list_users(i)||' was found, we will not create it. ');

/*--------------------------------------------*/
/* If the user in not the system we will have */ 
/* to create the user and print this to the   */
/* output. Creation will be done by using the */
/* standard fnd_user_pkg.CreateUser option    */  
/*--------------------------------------------*/
ELSE
DBMS_OUTPUT.PUT_LINE(' - User '||t_list_users(i)||' was NOT found, we will create it again');

createdUser := t_list_users(i);

fnd_user_pkg.CreateUser(x_user_name            => t_list_users(i)
,x_owner                => 'CUST'
,x_unencrypted_password => 'welcome'
,x_description => 'External Consultant'
);

/*--------------------------------------------*/
/* For all new users we will add all the resps*/ 
/* which have been stated in t_list_resp. To  */
/* be able to get all the details needed by   */
/* fnd_user_pkg.AddResp we will query it via  */
/* cursor c_resp_details.                     */
/*--------------------------------------------*/       
FOR i IN t_list_resp.FIRST .. t_list_resp.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(' - Adding to user '||createdUser||' the resp '||t_list_resp(i)||'.');
open c_resp_details(t_list_resp(i));
fetch c_resp_details into r_resp_details;

fnd_user_pkg.AddResp(username       => createdUser
,resp_app       => r_resp_details.application_short_name
,resp_key       => t_list_resp(i)
,security_group => 'STANDARD'
,description    => r_resp_details.responsibility_name
,start_date     => SYSDATE
,end_date       => NULL
);
close c_resp_details;
END LOOP;
END IF;
close c_user_exists;     


END LOOP;  

END;

COMMIT;
Post a Comment