Site navigation (main menu).

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.

Manage Integrators

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.

Define Integrator

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.

Define Interface

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.

Interface Attributes

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).

Create Layout Fields

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

Create Document

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.

Form Function

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.

Excel Upload

Upload successful!

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

E-Business Suite

12 thoughts on: “Desktop Integration Framework for Web ADI in R12.1.2”

  1. T sIMKISS September 22, 2010 3:11 AM

    Thanks for the post. Would you know what the steps are for creating / getting an LOV working?

    I am trying to create an integrator to load AP Invoices, and would like to have an LOV on the Vendor_num field.

    thanks

  2. Andy Coates October 12, 2010 2:36 PM

    Hi there, have you read section three of the DIF developers guide? LoV are referred to as “components” in the DIF. Always check to see if a suitable component already exists. If a suitable component does not exist, section 3-3 of the guide details how you create new components.

  3. Srini December 4, 2010 5:08 PM

    Great article. I am am able to create the wedADI doc and import data and update the same.

    I used sql query to import data. I am trying to add parameters to the sql query and could not do it successfully. The manual is not clear on this area and I can’t see the parameters defined already to research or debug. Parameterlist screen is bit confusing to me.

    Could you please give some pointers on this?

    Any help will be greatly appreciated.

  4. Sarada May 31, 2011 7:25 AM

    Hi,
    Can you please tell me if there is any change in R12 while we attach a custom integrator to a form function in EBS.I have a custom integrator in 11i.While I try to click on the form function to open the document in R12,it gives an error ‘HTTP 404 error’.I am able to create document for the same custom integrator from desktop integration manager responsibility.But not from the existing menu.Please advise.

  5. Arnab January 30, 2012 2:09 PM

    Nice article. I have a question. I have turned ‘BNE: Allow No Security Rule’ to Yes at site level and despite that I cannot access the Define Layout functionality on any of the seeded integrators or any custome integrator that I create. I keep getting the following error:

    “You do not have permission to access this feature”

    Any clues ?

  6. Andy Coates January 30, 2012 3:07 PM

    Thanks Arnab. Interesting issue. Have you tried bouncing the middle tier? This is sometimes required when changing securing functions. Did you use any securing functions when creating the custom integrator?

  7. Andy Coates January 30, 2012 3:10 PM

    Hi Sarada,

    Did you create the custom integrator using the DIF in R12 or port it from 11i? Have you tried recreating the custom integrator in DIF?

    Andy.

  8. Tatevosian@yahoo.com March 7, 2012 11:43 PM

    this was a great part cheers for the help.

  9. Jini April 12, 2012 8:24 PM

    Hi,

    I need to create a LOV for one of the columns in the spreadsheet.
    1. How do I create a LOV. ( I think it is creating a component)
    2. Once the component is created, how do I specify use this component for the column.

    thanks
    Jini

  10. Andy Coates April 16, 2012 11:09 AM

    Hi Jini, thanks for posting. You are correct, a component is required to achive this. Your best bet is to review the Oracle documentation which is located on MOS:-

    Oracle E-Business Suite Desktop Integration Framework Developer’s Guide, Release 12.1.2 [ID 979354.1]

    See section 3 on managing components. You need to create a component of type “FLEX” or “LOV – TABLE”, depending on the source of the list of values. You have to bear in mind the the valueset is driven by columns in the integrator definition.

    For additional pointers, look at the seeded integrator components e.g:-

    select itm.*
    from bne_components_vl com
    , bne_param_list_items itm
    where com.component_java_class = ‘BneOAValueSet’
    and com.param_list_code = itm.param_list_code
    and component_code = ‘EXP_TYPE_CLASS_LOV’;

    Kind regards,
    Andy.

  11. Rama December 19, 2012 11:26 PM

    How do I add a new column of type ‘Real’ to the existing ‘Interface’. ? I have added a column to the table that is being used in the interface, but it is not getting reflected.
    thank you

  12. Andy Coates January 3, 2013 10:02 AM

    Hi Rama, I am not sure I fully understand your question.

    I presume you have created an Interface of type “Table”? Have you physically added a column to the table in the database? Did you do this after you have created the interface? I am not aware if it is possible to create a new interface attribute of type “Real” after the interface has been created. Have you tried re-creating the interface definition?

    If you have created the interface successfully and your column (attribute) is appearing, make sure that it is included in the layout(s).

    Andy.

Comments are now closed... Please contact us if you have any queries.

sniffen270@mailxu.com