Saturday, September 25, 2010

Oracle Promotional goods

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.

This is common practice in a lot of businesses and especially in B2C businesses that you get a second item for free if you buy a special product. Oracle is providing you will all the options needed to arrange this inside Oracle e-Business Suite.

For example, if you sell a laptop you want to add an extra battery pack as a promotional item. You can arrange this by using Oracle Advanced Pricing and create a Modifier of the type “Promotion”. As can be seen below we create a modifier named JLO-01.

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.

If we now add the item to an order we will see something nice happening. We use Oracle Order Management to create a new order. If we add the line and we are done you will notice a message popping up stating that a second line is created.

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


Tuesday, September 21, 2010

Linux mount USB

USB disks are used more and more and almost nobody thinks about how a USB drive is attached to the computer. You plugin your disk and it works. When you are done you get your drive and move on. When using windows or most of the Linux GUI driven systems there is nothing to think about. However, if you run a couple of commandline only Linux systems you have to do more than only plugin your USB cable. However, it is not very hard to do this from a Linux shell in a manual way. Basicly it is the same as mounting a new drive or network drive on your system.

The trick is to find out where the drive is mounted under /dev and then mount it at a location on your system by using the standard commands you are used to.

When you attache your USb drive to the system it can be good to have a tail -f running on your /var/log/messsages. You will find a message somthing like the one below when you attache your drive.


Sep 16 17:26:09 homebox00 kernel: [2063013.339435] usb 4-1: new high speed USB device using ehci_hcd and address 4
Sep 16 17:26:10 homebox00 kernel: [2063013.472602] usb 4-1: configuration #1 chosen from 1 choice
Sep 16 17:26:10 homebox00 kernel: [2063013.476190] scsi4 : SCSI emulation for USB Mass Storage devices
Sep 16 17:26:10 homebox00 kernel: [2063013.477177] usb 4-1: New USB device found, idVendor=1058, idProduct=1001
Sep 16 17:26:10 homebox00 kernel: [2063013.477849] usb 4-1: New USB device strings: Mfr=1, Product=2, SerialNumber=3
Sep 16 17:26:10 homebox00 kernel: [2063013.478958] usb 4-1: Product: External HDD
Sep 16 17:26:10 homebox00 kernel: [2063013.479588] usb 4-1: Manufacturer: Western Digital
Sep 16 17:26:10 homebox00 kernel: [2063013.480205] usb 4-1: SerialNumber: 574341553433383836343732
Sep 16 17:26:15 homebox00 kernel: [2063018.486535] scsi 4:0:0:0: Direct-Access WD 10EAVS External 1.05 PQ: 0 ANSI: 4
Sep 16 17:26:15 homebox00 kernel: [2063018.504738] sd 4:0:0:0: [sda] 1953525168 512-byte hardware sectors (1000205 MB)
Sep 16 17:26:15 homebox00 kernel: [2063018.506813] sd 4:0:0:0: [sda] Write Protect is off
Sep 16 17:26:15 homebox00 kernel: [2063018.509230] sd 4:0:0:0: [sda] 1953525168 512-byte hardware sectors (1000205 MB)
Sep 16 17:26:15 homebox00 kernel: [2063018.511297] sd 4:0:0:0: [sda] Write Protect is off
Sep 16 17:26:15 homebox00 kernel: [2063018.512656] sda: unknown partition table
Sep 16 17:26:15 homebox00 kernel: [2063018.518501] sd 4:0:0:0: [sda] Attached SCSI disk


From this you can see that the new drive is at /dev/sda . At the system I am attaching this drive I have a directory at /storage/disk0 and I will use this location to mount the USB drive.

You can now simply use the below command:
mount /dev/sda /storage/disk0

If you do a df -h you will see the new drive attached to your /storage/disk0 location. This can be any other location you want.



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;