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;
When you run your sales in Oracle E-Business suite you want from time to time run promotions. For example if you sell an item in Oracle Order Management you want to “give” the customer a second item for free, a promotional item.
Inside the modifier we have created a line for item AS18947. This is a laptop and we want to give a promotion of a second item for every customer who buys this item. If you click the “Define Details” button you will be able to set the item that the customer will “get”. You have to make sure you define it as a “get”item.
You can see that we have stated here a get item for item CM11222 and we have stated the “Application Methode” is “New Price”. This will make sure that the price of the get item will be overwritten by the value defined in the “value” field which is in this case zero. Meaning this item will be for free if the customer buys your item AS18947.
You can see in the below example that indeed a new line has been added for item CM11222 for the quantity 1 and the price 0. This is what we intended when we created the modifier JLO-01. If you check via actions > “View Adjustments” you will also notice that a adjustment to the order is done by modifier JLO-01
