Blog

Desktop Integration Framework for Web ADI in R12.1.2

R12.1.2 contains powerful new functionality called the ‘Desktop Integration Framework’, or ‘DIF’ for short. The DIF allows E-Business Suite users to create custom integrators for Web ADI, and to therefore load data directly into the EBS using Microsoft Excel, Projects or Word.The DIF also allows users to create integrators for reporting, thus offering another method of exporting data out of the EBS to the desktop.

Some of you may be aware that similar functionality can already be utilised in 11.5.10.2. However, the DIF is a complete solution that brings all associated integrator setup together. This article will explore the basic features of the DIF by demonstrating how to create a simple custom integrator for loading Events into Oracle Projects.

This article is not intended to be used for training or as an implementation guide. There are however some good Oracle resources that I recommend reviewing. Firstly a link to the Desktop Integration Framework Developers Guide can be found in My Oracle Support note 979354.1. Secondly there is a very good eSeminar on the subject at Oracle University (don’t miss slide 37 which contains a lengthy video of a working example).

The DIF is not packaged with R12.1.2 by default, and needs to be installed. To install the DIF apply patch 9055234. This will provide a new responsibility entitled ‘Desktop Integration Manager’. It might also be worth installing patch 9366099 at this stage which fixes some known issues with the DIF. Please also read My Oracle Support note 549758.1 to ensure that your system profile options are correctly set.

Once the DIF is installed and setup, use the new Desktop Integration Manager responsibility to create a new integrator. Select the Manage Integrators function to create and amend custom integrators. Note that seeded Oracle integrators can not be amended. The screenshot below shows the Manage Integrators landing page.

When defining an integrator one must enter the integrator name, the integrator’s internal name and the application to which the integrator belongs. In additon the user can also enable / disable the integrator and remove the integrator from the Web ADI create document form. Optionally integrator parameters can also be specified. Although parameters and Parameter Lists are not covered by this article, a useful value for the the Upload Parameter field is ‘HR Upload Parameter List’. This is a seeded parameter list that allows the user to select whether they want to upload all / flagged rows and whether to validate records in the Web ADI document. If you are familiar with Web ADI already you will have no doubt seen this parameter list in Excel when uploading a spreadsheet.

Additionally one can secure an integrator using form function security. By attaching functions to the integrator, the integrator will only be available to user’s who have menu access to said functions. Be warned, in testing this functionality I have found that if you add a securing function to an integrator (and save) then it can not be removed! Another tip is that if you set the profile option ‘BNE Allow No Security Rule’ to ‘Yes’ then function security is ignored in Web ADI. The screenshot below shows the integrator definition.

Once the integrator header is populated we need to define an Interface. An inteface can take the form of a table or an API (procedure or function). In this article I have created an interface using a PL/SQL procedure.

For the more technically minded this is an interesting discussion point. At this stage one may think “Great! There are loads of Projects APIs that I could use straight away to create custom integrators”. However, in reality many of the Projects APIs have initialisation procedures that need to be in sequence i.e. there is not one single procedure that can be called directly. The “create event” Projects API is a good example of this; there are 3 separate PL/SQL procedures in the package pa_event_pub that need to be called one after the other. Another issue with some APIs is that their input parameters contain internal database ids e.g. project_id. These would be fairly useless to users inputting data in a Web ADI document.

In this example I have therefore used a PL/SQL covering procedure that utilises the Oracle “create event” API. The covering procedure has “real” input parameters e.g. Project Number, Task Number, Bill Amount etc. that ‘feeds’ the Oracle API. The interface definition can be seen in the screenshot below.

The next step is to define the interface ‘Attributes’. The interface attributes represent the columns that will be available when you create a Web ADI document using the integrator. Interface attributes are automatically picked up from the interface definition, so in this example the attributes are created as the input and output parameters of the PL/SQL procedure. As can be seen in the screenshot below, one is able to enable / disable attributes, decide whether attributes will be displayed when creating Web ADI Layouts, and also change attribute names. Default attribute values can also be specified such as constant values or SQL queries. Note that default attibute values specified here will not display in Web ADI documents; they are used in the condition that the attribute value is NULL during upload.

Additional attribute properties (like validation of values) can be set using the ‘update’ button next to each attribute. Another neat feature of the DIF is the ability to create and assign what are called ‘Components’ to attributes. As described in the developer’s guide, “A component is a set of metadata that provides a list of values for a field in a desktop document”. For example, you can double click a cell in your custom Excel Web ADI document to get a list of values pop-up e.g. Project Number. Those of you who have used Web ADI documents before will already be familiar with the use of components.

In this example I have changed the name of several attributes and disabled all of those attributes that are not required in my Web ADI layout. Please remember that you must not disable those attributes that are required as inputs for the API! The below screenshot shows the interface attribute page.

There are many more features of the DIF, such as content sets and parameter lists which I can include in future blogs if there is interest. Content sets and associated mappings are necessary if you are creating a custom integrator for reporting or to amend existing records in the EBS. However, in this example we are not extracting any records from the EBS and our custom integrator is now complete.

Before we can use the custom integrator we must define a default Layout (all Web ADI integrators must have at least one layout). Layouts and associated Styles can be cofigured in existing Web ADI responsibilities or from within the DIF page ’Manage Integrators’ (the button can be seen back in the first screenshot). In the layout we can select default values for fields in the document, as well as field placement (context, header or line level).

We can now use an existing Web ADI responsibility (e.g. Desktop Integration) to create a document using our new integrator and layout.

In this example I have defined a Web ADI document for Excel, but documents for Word or MS Projects can also be created. The Web ADI document can then either be created straight away or saved. If you choose to save the document definition you can either create a shortcut (for furture use) or a form function. If you save the document as a form function you can then attach it to an existing EBS menu, as seen in the screenshot below.

The rest of the process is the same as using a seeded Web ADI document. In this example we open the Web ADI document by selecting the form function we added to our Projects menu. The document then opens in our chosen viewer (Excel), and data can be entered and uploaded using the Oracle toolbar.

That’s it! An integrated and secure solution for loading, amending and extracting data from the EBS. Please let me know how you get on using the DIF and/or if there is anything else you wish to see blogged on the subject. I am keen to see other examples of how people are using the DIF.

21 Apr, 2010 by Andy Coates

E-Business Suite