Create a Custom Oracle Web ADI Integrator using Desktop Integration Framework GUI

Oracle Web ADI is a well-known functionality that is being heavily used by the End Users in the Oracle E-Business Suite Financials and CRM applications to perform some of their day-to-day data entry tasks in the commonly used Microsoft desktop applications like Excel and Projects and upload the data to EBS. Most of the common tasks like journal entry and creating assets, etc. are provided by seeded functionality, however, with the introduction of new EBS Desktop Integration Framework, we can define custom Integrators to execute the additional tasks required by the business users without much technical effort.

Previously, custom integrators were created using Oracle Web ADI tables and APIs.  Now, with the introduction of GUI based R12.1.2 Oracle E-Business Suite Desktop Integration Framework we can easily define integrators and associated supporting objects, which reduces the development time and makes it easier to maintain integrators too.

The Desktop Integration Framework allows you to:

• Define and maintain seeded and custom Integrators using a graphical user interface.
• Create Custom Integrators that allows to upload data through seeded APIs or directly into tables.
• Create Integrators to download data from text files or using SQL Queries.
• Define data validation rules.
• Embed UI widgets (List of values, Pop lists, Date pickers, Flexfields) in spreadsheets.
• Use the Oracle E-Business Suite Security Model.
• Define layouts and mappings for custom integrators.

By defining an integrator, you enable your users to do the following:

• Generate a formatted data template on the desktop containing application-specific fields for data entry.
• Enter or modify the data in the desktop application with the same kind of validation rules that existed in the EBS application.
• Upload data from the desktop document to feed the data to Staging Tables, Interface tables or directly to base tables.
• Verify the results of the upload process and correct any errors or data issues immediately.

Integrators:

Integrators is a set of metadata that encapsulates all the information needed to integrate a particular Oracle E-Business Suite task with a desktop application.

Reporting-only integrator

If we want to just download data from Oracle E-Business Suite into a desktop document and do not want to upload data, we would define a reporting-only integrator. The resulting document would serve as a report of the Oracle E-Business Suite data that can be viewed in the desktop application.

Non-Reporting Integrators:

If we want to download the data and enter/modify the data in the desktop application then upload it back to EBS, we define Non-Reporting integrators. The different ways we can utilize these integrators are

• Create a blank document that is laid out and formatted according to an Oracle E-Business Suite application’s business rules but contains no data. Users must enter all data into the document manually.
• Create a document that is initially populated with data from a text file. Users can work with the data in the desktop application before uploading it to Oracle E-Business Suite.
• Create a document that is initially populated with data downloaded from Oracle E-Business Suite. Users can work with the data in the desktop application before uploading it back to Oracle E-Business Suite.

Defining Custom Integrators:

We can define custom integrators on any interface table or any custom staging table registered in the EBS application to create an excel format to download and modify the data and upload back into the table with additional data.

For example, the following screenshots will demonstrate defining custom integrator for OE_HEADERS_IFACE_ALL

Step 1: Define a new integrator

Login to Desktop Integration Manager->Create Integrator

Define the Integrator as below and make sure to check the “Display in Create Document Page” checkbox:

Add the “Desktop Integration – Create Document Page” as follows:

Step 2: Create Table Interface.

We can alternatively create interfaces using API procedure and API function instead of the table if we want to directly pass the values to the APIs. We can update the attributes prompts to display meaningful column names in the spreadsheet and specify the default values based on constant value, parameter, profile option, SQL Query, etc.

Step 3: Create content.

You can optionally create content to fetch the existing data from the interface table using an SQL query

Step 4: Create Uploader.

Create the uploader to specify the template and parameters to be used.

Step 5: Create importer.

You can optionally create an importer to specify the standard seeded import program you want to run after the data is uploaded into all the interface tables.

Specify the Concurrent Program Request to be called after uploading the spreadsheets.

Step 6:  Define Layout

Desktop Integration Manager->Manage Integrator->Query for the Integrator -> Define Layout

Create a new layout to the Integrator to specify the number of header records, placement of the fields at the header and line level. You can optionally select which table content columns to be displayed on the desktop document.

Click Next to optionally change the Width of the fields and Position of the fields on the desktop document.

 

Click on “Apply” and now you can go back to manager Integrators

Step 7: Define Mapping

Desktop Integration Manager->Manage Integrator->Query for the Integrator -> (Select Integrator) Define Mapping

Create the mapping between the columns displayed on the layout with the database columns of the table we are going to upload the data.

Specify the Mapping Name and Number Of columns to be mapped

Specify the column mappings between source and destination.

Step 8: In order for the Excel and Internet Explorer to open the web ADI templates, make sure following settings are done.

Open Excel, go to File->Options

 

New windows open up as below and click on the “Trust Center” at the bottom and click on the “Trust Center Settings” on the bottom right.

Select “Macro Settings” on the left navigation pane. Under Macro Settings, select “Enable all macros”. Under “Developer Macro Settings”“, select “Trust access to the VBA project object model”“.  See the image below for more details.

Next step is to navigate to the “Protected View” option on the left navigation pane. Once you are there, un-check all the protected view options as show below:

Click OK and close all Excel windows.

Internet Explorer Setups:

Open IE and navigate to IE > Internet Options and select “Security Tab”. Select zone as “Internet” and click on“Custom Level”:

Scroll down and then “Enable” the “Allow status bar updates via script” option.

Step 9: Desktop Integration Manager->Manage Integrator->Query for the Integrator -> (Select Integrator)

Select the integrator and preview the integrator to generate the desktop document in the layout attached to the integrator. You can optionally add components like LOV to the fields on the spreadsheet.

Once the document is completed editing, it can be uploaded back to EBS with the Add-In Oracle feature on the spreadsheet.

Web-ADI Template Settings for Microsoft Excel

Web-ADI Template Settings for Microsoft Excel

Often times, WebADI can be very confusing to work with. There are just too many issues if it is not properly set up. Sometimes you may encounter VBProject Runtime Error when trying to open Web-ADI template file, or sometimes the Excel spreadsheet just hangs up, or can't be opened.

There are few steps you will have to take before WebADI can be properly used:

• Microsoft Office Version
• Microsoft Excel Settings
• Internet Explorer Settings

Follow the steps below given in the screenshots and apply the settings exactly as they are depicted. The screenshots are from Excel 2010, however the settings should also work for 2000, 2003, 2007 versions.

A)  Check the Microsoft Office Version

Make sure you are using 32-bit version of Microsoft Office. Click on Excel --> File --> Help. If you are running 64-bit version, you may want to uninstall 64-bit and re-install the 32-bit version of Microsoft Office.

B)  Open Microsoft Excel --> File --> Options --> Trust Center --> Trust Center Settings

B-1)  Trusted Documents:

Make sure to check "Allow documents on a network to be trusted".

B-2)  Add-Ins:

Make sure to uncheck all the options.

B-3)  ActiveX Settings:

Check only "Enable all controls without restrictions and without prompting". All other options should be unchecked.

B-4)  Macro Settings:

Check "Enable all macros" and "Trust access to the VBA project object model" options.

B-5)  Protected View:

Check only "Enable Protected View for Outlook attachments" and "Enable Data Execution Prevention mode" options. All other options should be unchecked.

In case the WebADI still does not work, then keep all the options unchecked, and retry.

B-6)  Message Bar:

Check "Show the Message Bar in all applications when achieve content, such as ActiveX controls and macros, has been blocked".

B-7)  External Content:

Check the "Enable all Data Connections" and "Enable automatic update for all Workbook Links" options only.

C)  Internet Explorer Settings:

Open Internet Explorer --> Tools --> Internet Options --> Security Tab --> Custom Level.

C-1)  Downloads

Scroll down to Downloads section, and make sure File Download is Enabled.

C-2)  Miscellaneous: 

Scroll down to Miscellaneous section, and make sure to match the following setting.

C-3)  Scripting: 

Scroll down to Scripting section, and make sure to match the following setting. Then press OK.

Now log out from your Oracle Applications and re-log back in. Retry the Web-ADI upload.

References:
Web ADI Errors & Solutions
R12 Web ADI: Make it work with MS Office 2010
Troubleshooting user issues with Oracle Web ADI