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;
1 comment:
Hi All
We have a scenario
we are handling Non Production servers where we need to create lot of users daily.
So the thing is after refreshing the Prod fix environment from Production we are end dating all the produciton users and we are creating the new users through sql script using FND create package where we have two scripts one for creating the user and 2nd for adding the responsibilites corresponding to the user in other script which are ran as a part of post refresh scripts.
So below are the few steps we are doing
1) login to server
2) pbrun
3) then going to the specific location where those two files are placed
4) taking the backup of those two files
5) then adding the new user creation script at the end of the file
6) Adding the name of the user in the non end dated command
7) Adding the responsibilites assinging commands to the second file
Now I want the following things
1) Need to create a unix script which when i will run first login to database using apps and asks for the number of usernames to be created newly
2) To check if those users exist already if yes then asking for other name
3) Creating the users at runtime and adding the same in the file after taking the current backup.
4) Asking for the list of responsibilities (can be provided in a flat file)
5) Need to add those responsibilities once at that time and adding the script to the second file after taking its backup.
Can you please assist in this
Post a Comment