Oracle WebADI – Technical Overview

At a very high level, a WebADI application consists of an MS Excel spreadsheet template to store the data to be uploaded to Oracle Applications, and a PL/SQL procedure to validate and upload this data row-by-row.

By convention, the PL/SQL procedure has an input parameter corresponding to each column in the spreadsheet, and has no output parameters.

The procedure has no output parameters (errors are communicated back to the spreadsheet using the standard raise_application_error API, where they appear in the “Messages” column).

Rows in the spreadsheet are processed one-by-one. Each row is processed by its own instance of the PL/SQL procedure. In this sense, the logic in the procedure for a specific row can have no knowledge of previous or subsequent rows in the spreadsheet.

The WebADI spreadsheet is launched from a custom Oracle Applications function, attached to an existing Menu, under a suitable Responsibility.

An Oracle WebADI object consists of the following principal components:

  • An integrator
  • An interface
  • A layout
  • A mapping

These components can, to some extent, be created and maintained using the functions available under the Desktop Integrator responsibility (in R12).

They can also be created, modified and deleted using the functions and procedures in a series of seeded PL/SQL packages, principally BNE_INTEGRATOR_UTILS.

WebADI database objects can be found in the BNE schema in the Applications database.

Taking each WebADI component in turn…

The Integrator component is effectively a container for a specific WebADI application, which can be invoked from within Oracle Applications (usually from a conventional applications Function, attached to a Menu). Properties or attributes of the Integrator determine the behaviour of the (MS Excel) user interface, including the types of content (“None”, “Text File”, etc.) which can be downloaded into it, and the upload screen options (“Validate Before Upload”, etc.).

The Integrator definitions are stored in the tables bne_integrators_b and bne_integrators_tl.

The Interface contains a WebADI “column” corresponding to each input parameter in the PL/SQL procedure which manages the WebADI data upload process. The PL/SQL procedure must therefore exist prior to creating the WebADI Interface. By default, interface column names are identical to the parameter names in the PL/SQL procedure.

The interface definitions are stored in bne_interfaces_b and bne_interfaces_tl.

The Interface Column definitions are stored in bne_interface_cols_b and bne_interface_cols_tl.

The Layout contains a column corresponding to each spreadsheet column. The layout can be based on the columns in a database view (if the WebADI application is being used to download data from Oracle Applications), in which case it is created automatically during the Interface creation process. Alternatively, a default layout can be created, with columns simply numbered from 1 to n.

The layout definitions are stored in bne_layouts_b and bne_layouts_tl.

The Layout Column definitions are stored in bne_layout_cols.

The Mapping provides the link between the columns in the data source, and the Interface columns. In other words, the link between the columns in the Excel spreadsheet and the columns in the database. The Mapping must be created manually, in the Applications, via the Desktop Integrator responsibility.

The mapping definitions are stored in bne_mappings_b, bne_mappings_tl and bne_mapping_lines.

Having defined an entire WebADI application, it can be refined by adding Applications-style Lists of Values to the spreadsheet to allow the lookup/validation of fields such as Asset Category, GL Account Code, etc., using API’s such as BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV and BNE_INTEGRATOR_UTILS.CREATE_CCID_KFF.

Note: the above changes will not be visible in the WebADI spreadsheet until the Database (not just the Applications) Server is re-started.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s