Friday, August 18, 2017

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:
Oracle Web ADI - Desktop Integration Manager
Add the “Desktop Integration – Create Document Page” as follows:
Oracle Web ADI - Create Document Image
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.
Oracle Web ADI Image
Step 3: Create content.
You can optionally create content to fetch the existing data from the interface table using an SQL query
Oracle Web ADI - Create SQL Content
Step 4: Create Uploader.
Create the uploader to specify the template and parameters to be used.
Oracle Web ADI - Create Uploader
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.
Oracle Web ADI - Create Importer
Specify the Concurrent Program Request to be called after uploading the spreadsheets.
Upload 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.
Define layout 1
Define layout 2
Click Next to optionally change the Width of the fields and Position of the fields on the desktop document.
 Change widths
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
image025
Specify the column mappings between source and destination.
image027
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
 image028
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.
image030
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.
image032
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:
image034
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 onCustom Level”:
image036
Scroll down and then “Enable” the “Allow status bar updates via script” option.
image038
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.
image040
Once the document is completed editing, it can be uploaded back to EBS with the Add-In Oracle feature on the spreadsheet.

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...