Apps To Fusion

Creating custom Web ADI Integrators.
Using custom Web-ADI integrators, you can create custom excel spreadsheets that can be integrated with Oracle Applications Menus. The structure of this excel will be as per your business needs. This excel will have fields validated by LOV, DropDown Lists etc. The data entered into this excel will then be further validated and gives errors in Excel. Once the valid values entered the data will be loaded into tables via PL/SQL. This is a great feature in Oracle Applications, and many of the functional & business users would love to have this functionality. Using this article, you will be able to implement a custom web-adi integrator to meet the data entry requirements of your business users via excel screen. Standard Web ADI gives General Ledger Journals for 11.0 and 11i.

This document explains about how to create custom integrators, and how to integrator with the functions. After added the custom integrators how to use the custom integrators and insert the data into database through the integrators. This document assumes that the reader has a basic knowledge of PL/SQL procedure and functions.


Background
Web ADI gives functionality with desktop productivity applications to create a more effective working environment.
Traditional enterprise application users can leverage productivity tools such as Microsoft Excel to complete their tasks. This proves helpful when users have a large number of records to enter into the system. Rather than creating records row by row in an HTML form or using a back-end data loader, a formatted Excel worksheet can be created that allows the free-form entry of data - all Excel functionality such as copy/paste, fill-down and data import. And extra functionalities like drop down, List of values are available to help one quickly create, view and edit Information.
Users are made more productive, yet the accuracy of their work is not compromised. All business rules these users encounter in the main application are enforced within the Web ADI worksheet. For example, if the wrong cost center is entered for a journal, the data will not be "committed" to the database; a message will be returned directly to the worksheet where the invalid data exists; the user can then quickly make the correction and save their Excel data to Oracle General Ledger. If a column has more values you can start the search from excel sheet, the results you can get in a OAFramework page and select the value will return to excel sheet.
Web ADI uses the Internet Computing Architecture (ICA). This means no client side install and great performance over WAN, VPN and even dial up connections.

Steps:

1) Develop an Integrator for your own excel template and assign it to the user.

2) User login and launch the excel from Web ADI and enters the values in excel directly (he can enter the values, choose the values from Poplist or List of Values.

3) Choose oracle->upload option in excel and upload the data into the database. When the data gets uploads you can write a PL/SQL to insert the data directly into database or you can do some process of the entered data and then update the database table accordingly.

This document should be used to develop custom integrators. Custom integratos will be used for a case of having a requirement of launching an excel templete from apps and fill the values in the excel file and we will get an option to upload the values into the database table. At the time of uploading the data we can define that where the data should go and we can manupulate the entered data and insert into the database table.


.
Creation of the custom integrator
Usage of custom integrator
Custom integrator will be used to upload the excel values into database tables.
For Example the user wants to upload the data from an excel sheet with a formatted way, and when it uploads the data should be validated and should be returned back if any error occurs or data should do some other calculation with other tables, that can be done.
The template excel can have defaulted values, LOV values and POPList values to select the values.
Once we have the custom integrator we can define as a function and assign to the FND_USER.


Steps to create the custom integrator.
Step 1. Using bne_integrator_utils.CREATE_INTEGRATOR package create the custom integrator
Step 2. Using bne_integrator_utils.CREATE_INTERFACE_FOR_API package create the interface for the created integrator.
Step 3. Using bne_integrator_utils.CREATE_DEFAULT_LAYOUT package create the default layout for the created integrator with the interface.
Step 4. Adding POPList for the required columns.
Step 5. Change the excel column prompts, by default the column headers will come as Database column name.


Now lets see the above steps in detail
Step 1. Create Integrator
Using the standard package bne_integrator_utils.CREATE_INTEGRATOR we can create the integrator, with the following parameters,
PROCEDURE CREATE_INTEGRATOR
(P_APPLICATION_ID IN NUMBER,
P_OBJECT_CODE IN VARCHAR2,
P_INTEGRATOR_USER_NAME IN VARCHAR2,
P_LANGUAGE IN VARCHAR2,
P_SOURCE_LANGUAGE IN VARCHAR2,
P_USER_ID IN NUMBER,
P_INTEGRATOR_CODE OUT NOCOPY VARCHAR2);
P_APPLICATION_ID
--Is the application Id of the application where we are going to create the integrator.
--Use the query, SELECT application_id FROM fnd_application WHERE application_short_name = '<CUSTOM_SHORT_NAME>';
P_OBJECT_CODE – Integrator Code, which will be used internally.
P_INTEGRATOR_USER_NAME – Integartor Name, which will be displayed in the application.
P_LANGUAGE, P_SOURCE_LANGUAGE are the language codes, (US for english).
P_USER_ID – FND_USER id, (0 for sysadmin)
P_INTEGRATOR_CODE – Return parameter, which will be used for the furter steps.



Step 2. Create Interface
PROCEDURE CREATE_INTERFACE_FOR_API
(
P_APPLICATION_ID IN NUMBER,
P_OBJECT_CODE IN VARCHAR2,
P_INTEGRATOR_CODE IN VARCHAR2,
P_API_PACKAGE_NAME IN VARCHAR2,
P_API_PROCEDURE_NAME IN VARCHAR2,
P_INTERFACE_USER_NAME IN VARCHAR2,
P_PARAM_LIST_NAME IN VARCHAR2,
P_API_TYPE IN VARCHAR2,
P_API_RETURN_TYPE IN VARCHAR2 DEFAULT NULL,
P_UPLOAD_TYPE IN NUMBER,
P_LANGUAGE IN VARCHAR2,
P_SOURCE_LANG IN VARCHAR2,
P_USER_ID IN NUMBER,
P_PARAM_LIST_CODE OUT NOCOPY VARCHAR2,
P_INTERFACE_CODE OUT NOCOPY VARCHAR2);

P_APPLICATION_ID – Application Id, which we are creating the custom integrator.
P_OBJECT_CODE – Interface Code for internal use.
P_INTEGRATOR_CODE – Code for the integrator, which we created in the step1.
P_API_PACKAGE_NAME - When the user uploads the excel sheet this package being called.
P_API_PROCEDURE_NAME - When the user uploads the excel sheet this procedure being called.
In this procedure the columns will be mapped with the excel sheet. From this procedure the data needs to be inserted into the database table.
P_INTERFACE_USER_NAME – Name of the Interface, which we are creating now.
P_PARAM_LIST_NAME - Parameter list.
P_API_TYPE - API type, PROCEDURE or FUNCTION.
P_API_RETURN_TYPE - What is being returned from the API
P_UPLOAD_TYPE
Type of upload, (Set to 0, 1 or 2. 0 = Custom Upload Type (uses
BNE_INTERFACES_B.UPLOAD_OBJ_NAME to obtain class name to load). 1= upload to Table.
2 = Upload to PL/SQL API)
P_LANGUAGE, P_SOURCE_LANG - Language being used in the upload(US-English)
P_USER_ID - User Id for the reference (0 – Sysadmin)
P_PARAM_LIST_CODE - Return code for the parameter list.
P_INTERFACE_CODE - Return code for the created Integrator.





Step 3. Creating default layout.
PROCEDURE CREATE_DEFAULT_LAYOUT
(
P_APPLICATION_ID IN NUMBER,
P_OBJECT_CODE IN VARCHAR2,
P_INTEGRATOR_CODE IN VARCHAR2,
P_INTERFACE_CODE IN VARCHAR2,
P_USER_ID IN NUMBER,
P_FORCE IN BOOLEAN,
P_ALL_COLUMNS IN BOOLEAN,
P_LAYOUT_CODE IN OUT NOCOPY VARCHAR2);
P_APPLICATION_ID –Application Id, which we are creating the custom integrator.
P_OBJECT_CODE – Default Layout code, for internal use.
P_INTEGRATOR_CODE - Integrator code which we have created in the step1.
P_INTERFACE_CODE - Interface code, which we have created in the step2.
P_USER_ID - User Id (0 – Sysadmin)
P_FORCE - If the layout name exists over write or error out(true or false)..
P_ALL_COLUMNS - Layout to be created for all the columns (true or false).
P_LAYOUT_CODE - Out parameter for the created default layout.





Step 4. Creating POPLIST for the required columns.
Now we are ready with the custom integrator, if we want to create POPLIST, User hint for any of the created integrator columns, use the following sql,

Create POPLIST for a column UNIT_OF_MEASURE
UPDATE bne_interface_cols_b
SET val_id_col = 'UNIT_OF_MEASURE',
val_mean_col = 'UNIT_OF_MEASURE',
val_type = 'TABLE',
lov_type = 'POPLIST',
val_obj_name = 'MTL_UNITS_OF_MEASURE_VL',
val_addl_w_c = 'DISABLE_DATE > sysdate or DISABLE_DATE is null'
WHERE interface_col_name = 'P_UNIT_OF_MEASURE'
AND application_id = lc_application_id_result
AND interface_code = lc_interface_code;
val_obj_name is the table name where the values should come from and val_addl_w_c is the where clause for the POPLIST restriction.

Check bne_integrator_utils for more details about creating LOV, Calender LOV and Chapter 5 of Web ADI Integrator Developer’s Guide for Java LOV.

Adding User Hints
UPDATE bne_interface_cols_tl
SET user_hint = '*(Eg.Miscellaneous)'
WHERE prompt_left IN ('CATEGORY_NAME')
AND application_id = <CUSTOM_APPLICATION_ID>
AND interface_code = <CREATED_INTERFACE_CODE>;



Step 5. Changing the PROMPTS for the columns
UPDATE bne_interface_cols_tl
SET prompt_above = 'Item Description',
prompt_left = 'Item Description'
WHERE prompt_above = 'ITEM_DESCRIPTION'
AND application_id = lc_application_id_result
AND interface_code = lc_interface_code;
bne_interface_cols_tl, bne_interface_cols_b are the main tables used for the above changes and for holding integrators info.




Prerequisities.
The following are the prerequisites for Web ADI (11i.BNE.D):
One of the following operating systems must be installed on the client PC
Windows ME
Windows NT 4.0 with Service Pack 3 or later
Windows 2000
Windows XP
Windows 98
Windows Server 2003

Internet Explorer 5.0 or greater installed on your machine.
Set the browser security settings to allow a document to be created on the desktop
Navigate to Tools -> Internet Options and choose the Security tab
Select Local Intranet and choose the Custom Level button
Set the option ‘Initialize and script Active X controls not marked as safe’ to Prompt

One of the following versions of Excel is required if using the General Ledger - Journals Integrator:
Microsoft Excel 97
Microsoft Excel 2000
Microsoft Excel XP
Microsoft Office 2003


For Web ADI to work with Excel XP and 2003 users will have to:
Open Excel
Go to Tools -> Macro -> Security -> Trusted Sources
Check the "Trust access to Visual Basic Project"


Setting the layout and create the Integrator as a function.
We can create our own layout for the excel sheet, Please check the following screen shots. To do the following steps the user should have System Adminstrator responsibility and Web ADI Responsibility or Web ADI’s Main menu.


Manual Setup for Created Integrator Layout and Create Document Function:
This document describes about creating Required Layout from the existing Default Layout. Login into the instance and select the Web ADI responsibility.


Click Define Layout, and select our XXT NCR Upload integrator in the poplist.
You can see the Integrator, which we have created just now in the poplist.


You will get the existing default Layout; take a copy of the layout by clicking the Duplicate button.


Click Update button for the newly copied layout(Eg. NCR Catalog Upload6).




Click Continue.


That will display all the columns available in the table.

Select only required columns(which will be in UnitCap) , the following columns needs to be put into Header Placement.

1) Please enter ----(User Hint)
2) --------------------(Header Underline)
3) Currency
4) Exchange Rate
5) Requisition Description

And Set Default Value for

1) Currency as
SELECT gsb.currency_code
FROM gl_sets_of_books gsb, hr_operating_units hou
WHERE gsb.set_of_books_id = hou.set_of_books_id
AND hou.organization_id = fnd_profile.VALUE ('ORG_ID')

 

And Default Type as SQL
And then click apply.

Now our required layout is ready.

Assigning “Create Document” function to the Menu Tejari POR NCR Catalog Upload (Code: XXT_NCR_UPLOAD_MENU)

The following screenshots will give the steps to create the function and shortcut to create the NCR Upload function.

Click Create Document.

You will get viewer as Excel 2000, Click Next. And the next will give option to select the Integrator, select our custom integrator (Eg. XXT NCR Upload)

Next screen will give the Layout selection, and you will get the newly defined Layout. Select the newly created layout and click next.

Select None as the Content. And click next. The next screen will give the Summary of the current Create Document.

Click on save to save the defined Create Documents so that we can create the Function and the shortcut.

In FND_FORM_FUNCTIONS you can get the newly created function, the FORM_NAME will be BNE_NCR TEMPLATE – 8 (All in caps and prefixed with BNE_), This needs to be added with the custom menu Tejari POR NCR Catalog Upload

(Code: XXT_NCR_UPLOAD_MENU)

You can use Functional Administrator Resonsponsibility (Self Service: Core Services -> Menus) or System Administrator(Forms: Application ->Menu)

 

Now the Menu XXT: NCR Web ADI Integrators Menu will have the functions of Catalog Upload, Catalog Update.

Now you can remove the Web ADI prompt in the menu XXT: NCR Web ADI Integrators Menu, so that users will not see the Web ADI Functions.

 

You can download the sample PL/SQL used for this article from link below
http://www.apps2fusion.com/training_demo/kalimuthu/web-adi/XXT_NCR_UPLOAD_PKG.sql