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:
- Set profile option, ‘BNE Allow No Security Rule’ to ‘Yes’
- Assign ‘Oracle Web ADI’ responsibility to your user. This responsibility has the menu ‘WEBADI MENU’ associated with it
- Add the submenu, ‘HR ADI Seeded Integrator Form Functions’ to the ‘WEBADI MENU’
- 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:
- Metalink Note#603169.1 -WebADI and Report Manager Webcasts for EBS 11i/R12 — Replay is Now Available
- Metalink Note#360105.1 -Understanding and Using Web ADI in Oracle HRMS.
Notes on Oracle Applications 