Comparing current EBS installation with 12.1.3

If you are planning an upgrade to R12 and wondering about all the ways it will affect you customizations, the following three tools will make your life easier. You can check all the components which have changed between the releases allowing you to manage your customiszations during the upgrade.

EBS File Comparison Report: Provides detailed information about what files were added, removed, or stubbed (made inactive) during changes between Releases 11.5.10.2 and 12.1.3 of Oracle E-Business Suite. It allows you to drilldown into the files(provided they are text-based) and view the differences between the two versions. It can be downloaded from Metalink Note#1446430.1.

EBS Data Model Comparison Report:  Provides the database object definition changes between two EBS releases. This includes information regarding which objects were added, deleted and modified. In case of modified objects, it lists the actual changes made to the objects. This one is more useful than the previous one in the sense that it lists differences between 12.1.3 and all other releases from 11.5.9 onwards. The report can be downloaded from Metalink Note#1290886.1 .

EBS ATG Seed Date Comparison Report: Provides details on the changes between different EBS releases based upon the seed data changes delivered by the product data loader files (.ldt extension) based on EBS ATG loader control (.lct extension) files. This report list differences between 12.1.3 and all other releases from 11.5.10.2 onwards. It can be downloaded from Metalink Note#1327399.1.

The EBS Release Management Team deserves a shout out for this one.

Reclaiming reserved space using tune2fs

The mountpoint (/u01) on which I had installed Oracle E-Business Suite was running out of space.   More frustratingly, almost 5GB of precious space was unavailable since it was reserved by the system. This being an installation on my laptop, hard disk space was at a premium.

[root@myapps ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda3 6.0G 178M 5.5G 4% /
/dev/hda1 99M 8.7M 85M 10% /boot
none 681M 0 681M 0% /dev/shm
/dev/hda7 2.0G 36M 1.9G 2% /tmp
/dev/hda8 95G 89G 1.3G 99% /u01
/dev/hda2 6.0G 1.9G 3.8G 34% /usr
/dev/hda6 3.0G 93M 2.8G 4% /var
[root@myapps ~]#

The 5GB of space was reserved because ext2 or ext3 filesystem by default allocate 5% of the available blocks for use by the root user. This allows the system to continue running if non-root users fill up the file system and also assists in preventing file fragmentation because the filesystem does not fill up completely.

The good news is that you an use the tune2fs utility to fully reclaim the reserved space. Be careful though not to reclaim all of the reserved space if the file system is used by the root user or for storing log/system files (such as /var and /tmp).

You can use tune2fs with the -l option to view the file system information.

[root@myapps ~]# tune2fs -l /dev/hda8
tune2fs 1.35 (28-Feb-2004)
Filesystem volume name: /u01
Last mounted on: <not available>
Filesystem UUID: e458ad7d-d6c7-481f-8476-e2cd878bd38c
Filesystem magic number: 0xEF53
Filesystem revision #: 1 (dynamic)
Filesystem features: has_journal ext_attr resize_inode dir_index filetype needs_recovery sparse_super large_file
Default mount options: (none)
Filesystem state: clean
Errors behavior: Continue
Filesystem OS type: Linux
Inode count: 12648448
Block count: 25276261
Reserved block count: 1263813
Free blocks: 1589667
Free inodes: 11888886
First block: 0
Block size: 4096
Fragment size: 4096
Reserved GDT blocks: 1024
Blocks per group: 32768
Fragments per group: 32768
Inodes per group: 16384
Inode blocks per group: 512
Filesystem created: Mon May 23 21:44:24 2011
Last mount time: Tue Jan 31 07:12:49 2012
Last write time: Tue Jan 31 07:12:49 2012
Mount count: 44
Maximum mount count: -1
Last checked: Mon May 23 21:44:24 2011
Check interval: 0 (<none>)
Reserved blocks uid: 0 (user root)
Reserved blocks gid: 0 (group root)
First inode: 11
Inode size: 128
Journal inode: 8
Default directory hash: tea
Directory Hash Seed: d32dc62a-1aef-417e-a80e-50f8882bf946
Journal backup: inode blocks
[root@myapps ~]#

And then use the -m option to set the reserved space according to your needs. In this case, I have set the reserved space to 1% of the total space. (Note that the available space is now 5.1GB from the earlier 1.3GB…muchos happiness)

[root@myapps ~]# tune2fs -m 1 /dev/hda8
tune2fs 1.35 (28-Feb-2004)
Setting reserved blocks percentage to 1 (252762 blocks)
[root@myapps ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda3 6.0G 178M 5.5G 4% /
/dev/hda1 99M 8.7M 85M 10% /boot
none 681M 0 681M 0% /dev/shm
/dev/hda7 2.0G 36M 1.9G 2% /tmp
/dev/hda8 95G 89G 5.1G 95% /u01
/dev/hda2 6.0G 1.9G 3.8G 34% /usr
/dev/hda6 3.0G 93M 2.8G 4% /var
[root@myapps ~]#

Using Web ADI to create Service Request

Web Applications Desktop Integrator is a tool that leverages the standard desktop applications to perform some of the Oracle E-Business Suite task. The Web ADI brings Oracle E-Business suite to the desktop where familiar desktop tool like Excel can be used to create spreadsheet, enter and modify data in the spreadsheet and finally upload the data into the Oracle Applications. Web ADI can be very useful for the users who are experienced with Excel and will like to use Excel to enter the data related to Oracle Applications. These users can use the special features of excel i.e. copy, paste, drag cells to increment and various arithmetic functions to optimize their business task and increase their productivity. Data that is entered into the spreadsheet can then be finally be uploaded in to the Oracle Applications with or without validation.

– From the Oracle white paper, Web ADI: Extending E-Business Suite with Desktop Applications

In this post we will see an example of using Web ADI to create Service Requests in Oracle E-Business Suite 11i based on data entered in Microsoft Excel. Though creating a service request isn’t what Web ADI is commonly used for but for the purpose of demonstrating how to create and use custom integrator in Web ADI, it is quite sufficient. Also note that this example is for EBS 11i, there are a few differences in case of R12.

Prerequisite steps to ensure that Web ADI works for you:

1. Set profile option, ‘BNE Allow No Security Rule’ to ‘Yes’
2. Assign ‘Oracle Web ADI’ responsibility to your user. This responsibility has the menu ‘WEBADI MENU’ associated with it
3. Add the submenu, ‘HR ADI Seeded Integrator Form Functions’ to the ‘WEBADI MENU’
4. Enable macros for Microsoft Excel

Once the prerequisite steps are completed, we can start with creating the custom integrator which will upload the service request data from Excel sheet to Oracle E-Business suite and use this data for creating the service request.

The basic steps for creating a custom integrator for Web ADI are:

• Define the custom integrator – This is done by using the Web ADI functionality itself to create a document, populating it with the necessary information and then uploading the document to create the integrator.
• Create a layout for the custom integrator
• Create mappings for the integrator if there is a need to load content from a text file (we will not be performing this step in our example)

1. Create a wrapper package which will use the data uploaded through the Excel sheet to create the service request. I will be using the Create Service Request API in my code. The package specifications for the package which I am using are given below. Note that the procedure xxx_create_sr which creates the Service Requests has five input parameters out of which two have default values. These input parameters will take their values from the Excel sheet.

create or replace package xxx_my_webadi is
 procedure xxx_create_sr(p_consumer_id in varchar2,
 p_complaint_type in varchar2,
 p_problem_summary in varchar2,
 p_optional in varchar2 default null,
 p_comments in varchar2 default null);
end xxx_my_webadi;

2. Define the custom integrator by navigating to Oracle Web ADI>Create Document. Select Excel 2003 as the viewer(do not select the reporting checkbox) and HR Integrator Setup(pre-defined integrator provided by Oracle for creating custom integrators) as the Integrator. Keep the default values for the other options. Review and click on ‘Create Document’

3. A confirmation message will be displayed and an Excel sheet will open. Following details are filled in the Excel sheet:

• Metadata Type- CREATE
• Application Short Name- CS(since I am using the integrator for creating Service Request)
• Integrator User Name- XXX_SR_Test_Intg
• Form Name- GENERAL
• API Package Name- xxx_my_webadi(from Step#1)
• API Procedure Name- xxx_create_sr(from Step#1)
• Interface User Name- XXX_SR_Test_Intf
• Interface Parameter List Name- XXX_SR_Test_Param
• API Type- PROCEDURE.

Note that you can use the List of Values(Add-Ins>Oracle>List of Values) to fill in some of the fields such as Metadata Type, Application Short Name, Form Name and API Type. Double-clicking on these fields will also invoke the List of Values window.

4. Upload the document through the Add-Ins tab(Add-Ins>Oracle>Upload). A confirmation message will be displayed

5. Next the layout needs to be defined by following the navigation Oracle Web ADI>Define Layout. Select the integrator XXX_SR_Test_Intg defined in Step#3 and define the layout. Specify a Layout Name and a Layout Key.

6. The layout has some required fields and some optional fields. The fields are derived from the input parameters of the procedure in Step#1. Note that the input parameters without default values are indicated as required fields here. Default values are specified for the required fields though this is not a mandatory step. Also, I have used one of the optional fields for comments/instructions to the user and I have specified this as a header field so that it is displayed only once in the Excel sheet.

7. The basic steps for creating and using the custom Web ADI integrator is complete and the user can access it by navigating to Oracle Web ADI>Create Document, selecting the integrator and other relevant options. However in order to make this integrator available to other responsibilities and to automatically default other options so that the user does not have to select them every time, we will define a custom function for the integrator by navigating to Application Developer>Application>Function. Details to be provided are:

• Type- SSWA servlet function
• HTML Call- oracle.apps.bne.webui.BneApplicationService
• Parameters- bne:page= BneCreateDoc&bne:viewer= BNE:EXCEL2003&bne:reporting= N&bne:integrator= USER_NAME:XXX_SR_Test_Intg&bne:noreview= Yes (I have inserted spaces after the equals symbol for formatting purposes, please remember to remove them)

8. Assign the function to the appropriate menu

9. Invoke the integrator by accessing the function defined in Step#7 and 8.

10. The following Excel sheet will open. Note the comment in the header section and the default values for the required fields. Double click on the ‘Upl’ column to flag the rows which you want to upload and make necessary changes. Upload the data through Add-Ins>Oracle>Upload. You can select the options as required in the Upload Parameters window.

11. A confirmation message and green smileys will be displayed denoting successful upload.

Resources:

1. Metalink Note#603169.1 -WebADI and Report Manager Webcasts for EBS 11i/R12 — Replay is Now Available
2. Metalink Note#360105.1 -Understanding and Using Web ADI in Oracle HRMS.