Creating custom Web ADI interfaces

There are times when it is appropriate to allow users to upload data in a non-traditional fashion.  Oracle has provided a flexible and relatively easy upload, download, update method, in Web ADIs, to allow users to leverage an interface that they are generally very comfortable with Microsoft Excel.  This portal to gather, modify and upload information into Oracle e-Business Suite modules, opens a new window of possibilities in user interfaces.

Best known for the standard Web ADI integrator used to upload journal entries, custom Web ADI features are quickly becoming a vaunt guard among many modules and business functions.  The process setup a custom integrator is quite straight forward but generally does require some technical capabilities.  Below are the basics steps required in setting up a custom Web ADI, but please note that there are numerous variations and options, particularly with menus and validation.

The Web ADI operates by combining the macro functionality in Excel with the open interface nature of APIs in Oracle.  The macro connects the data in the spreadsheet to a procedure in Oracle and allows for a two way communication between the procedure and the user of the spreadsheet.   Once enabled a new menu option appears in Excel that gives the user the ability to initiate the macro that will call the procedure in Oracle; the procedure normally calls an API that will process the data in the spreadsheet.  The beauty of simplicity in this interaction is that Oracle only has to deal with one record at a time and does not need to manage any looping mechanism as that is handled by the macro in the spreadsheet.

Here is how you can set up a simple Web ADI integrator.

1. Enable Macro Security in Microsoft Excel. This is a one-time security setting that you need to complete in Excel before you can use WebADI. 

a)      Open Excel and go to Tools > Macro > Security

b)      Open the Security window and click on the Trusted Sources tab

 

c)       Check the Trust access to Visual Basic Project checkbox

1. I found it necessary to execute this script(s) to enable the UI for setting up the WebADI

column information

update bne_integrators_b

set enabled_flag = ‘Y’

where application_id = 231

and integrator_code = ‘INTERFACE_COLS’;

3. It is necessary to create a view to retrieve the data that you want to add to or update; however if you are only uploading data then a view is not necessary.

4. Identify or create a procedure that will upload or update the information you are working with.

5. Create a Custom Integrator – this is done through MS Excel

             

1. Assign it to form function that you have access to

Integrator Application Short Name	Integrator User Name	Form Function List
XXCUS	NIS Add Organization Parameters	MASTER_DATA_ADI
This information is defaulted	This information is defaulted	This is the custom form that function that needs to be set up in order to control access to the custom integrator

6. Define the new Layout

          

Responsibility: Desktop Integration

Path: Define Layout

a)      Select the new Integrator from the list of available Integrators and click Go

b)      Click Define Layout to create a new layout and click Update next to an existing layout to modify that layout

c)       Defining a new layout (NOTE: DO NOT use the browser back and forward buttons, use the forms Cancel and Back buttons):

d)      Provide a layout name & layout key, then update Default Types, etc as desired

7. Define the mapping

         

     8.     Create Template for updating existing records

a.    Open Create Documents 

b.   Choose the viewer

    

                   

3. Select the new integrator                 

                      

4. Select the proper Layout

                     

5. Select the Content – represented by the view that will pre-populate the template

                   

6. Select the correct mapping and the Session date

                   

7. Review the information and click Create Document to generate the template

                   

As I mentioned before there are numerous ways to improve the visual and usability aspects of the spreadsheet interface through the use of lists of values, renaming/formatting columns, and validation.

Custom Sources in Oracle R12 Subledger Accounting

With the introduction of the Subledger Accounting (SLA) engine Oracle brought together several approaches of (Oracle e-Business Suite R12) accounting for business events into a single comprehensive, powerful, and flexible solution. The inherent design of the SLA engine is that business events within the application are accounted for and applied to specific accounts based on rules that can rely, primarily, on sources.

Sources are familiar to users of other areas and versions of Oracle EBS but this concept is new to Process Manufacturing (OPM). A source is simply pertinent information (either accounting or transaction related) that comes from some ‘source’ such as an existing configuration within the application, some information directly related to a transaction or a custom source which can derive from nearly anything that a PL/SQL function can generate. A source can be an account string that was assigned to the organization parameters, shipping networks, item definition, etc. The beauty of using sources comes from the relationship between the event being accounted for and the source that holds the accounting string. To illustrate this concept consider an inventory transfer. The clearing account may come from the shipping network, the variance account from the organization setup, and the valuation account from the item configuration. The type of event and surrounding circumstances may lend itself, in part or whole, to the structure of the areas of the application that touch the transaction – therefore the most efficient method of arriving at the accounting is to take it from areas of the application that touch the transaction.

There are two distinct situations where seeded sources generally don’t meet the business requirements. First the seeded sources are limited to only a few configured areas and often there are situations where transactions cannot adequately leverage these seeded sources to meet the business needs. The second situation comes from the exacting and finicky demands of management. Often these business reporting requirements are not met because the implemented model does not support the key requirements and the disparate reporting requirements demand creativity.

The option of custom sources is surprisingly easy to design and implement and is quite powerful and flexible. The custom source allows you to write simple, or complex, PL/SQL functions that will accept parameters and return a value. The value can be account distributions in the application configuration or information about the transaction that can be used in rules (generally as conditions) to derive the accounting.

My experience has been that by combining the power of simple custom sources with the seeded options it is relatively easy to generate complex accounting results with fairly simple rules and configurations. The ability of the accounting engine to accept a complete accounting string and then leverage rules that can replace individual segments based on priority application of additional rules makes it easy to configure and maintain the solution that meets the businesses’ needs.

Here is an example of how to easily create a custom source and combine it with seeded functionality to provide a flexible solution.

Step 1: Determine what ‘gap’ or need you are trying to meet.
NOTE: Believe it or not deferring the actual build of a custom function until the gap is clearly identified will save time and effort in the long run.

In this example we are trying to return the natural account from the organization parameter Material account. The returned value will ultimately be combined with another derivation rule result to become the code combination for the inventory valuation line in a set of journal entries.

Step 2: Develop a PL/SQL function to return the desired value – try to think of multiple applications for the functions so that a single function can be used for multiple applications. Below is an example of a very simple function used to return the natural account from the organization parameter Material Account.

CREATE OR REPLACE function apps.xxdemo_org_mat_acct
(v_organization_code IN VARCHAR2)
RETURN VARCHAR2
IS v_material_natural_acct VARCHAR2(20);
BEGIN
select gcc.segment4
into v_material_natural_acct
from
inv.mtl_parameters mp
, gl.gl_code_combinations gcc
where 1 = 1
and mp.material_account = gcc.code_combination_id
and mp.organization_code = v_organization_code
and msi.segment1 = v_item_number;
RETURN v_material_natural_acct;
END;
/

Step 3: Register the function as a Custom Source

Step 4: Define the Derivation Rule that will call the custom source

Step 5: Assign the Derivation Rule to the appropriate Journal Line Definition

NOTE: Multipe Derivation Rules can be combined to allow specific segments to be overwritten - when the criteria on the rules is met - creating new code combinations. In this case a rule derives a 'base' accounting string when general conditions are met and then the natural account is replaced by a second rule when additional conditions are met. Conditions are defined, and applied, at the rule level.

NOTE: The Organization input parameter will return the Material account - natural account segment - that is defined in the organization parameters form.

OPM R12 and SLA

Oracle Process Manufacturing in R12 and Subledger Accounting

For years Process Manufacturing (OPM) aficionados have reveled in the power of the Manufacturing Accounting Controller (MAC). The ability to utilize a single point of control to generate accounting against inventory impacting transactions was a source of pride. And the plumed feather in the cap of inventory accountants was the ability to generate ‘Test Subledgers’, make corrections/changes and re-run the accounting to achieve different results. Inventory accounting in OPM was straight forward, flexible, and even fun (at times). It was so easy to develop summarized views of the journals and then quickly show the transactions that drove the numbers.

Anyone who has worked with subledger generated journal entries in Oracle’s e-Business Suite can attest to the variation and disparate sources for the accounting. Some modules used AutoAccounting in varying forms, other modules used Create Accounting functions that read various accounting parameters and accounts entered directly against transactions. It was necessary for super users and support personnel to understand the different methods that each subledger used to build accounting. To a novice user the sources and varied rules could be confusing and often inflexible.

As Oracle continues to mature its e-Business Suite of business applications there has been a push to streamline and consolidate functions, taking the best practices of inherent in one area and combining with efficiencies in another to bring the most powerful application possible to bear. One of the single most significant changes to be introduced in release 12 is the concept of shared services for accounting. Across the different subledgers there is one engine that generates accounting, one place where rules are defined, a common set of tables where journals are generated. By leveraging the concept, found in the MAC, of a common engine that contains powerful, flexible rules with the consistency available through the use of sources common to AutoAccounting. Oracle designed a new model that is very powerful and flexible yet uses the same principles across multiple subledgers. The result is Subledger Accounting (SLA).

There are two challenges faced by organizations that feel comfortable with the MAC and are now faced with the new SLA. While the SLA works on a similar principle in the sense that it extracts and analyzes records from the material transactions table very much like the MAC and accounting can be generated over and over to evaluate scenarios and review results before finally committing to a set of journals however that is where the similarities end. The first challenge is that the way that rules are defined has been turned on its head. Oracle has introduced the concept of defining journal entry templates and then applying those templates to transactions based on matching criteria. The accounting for each line within the template is derived from applying one or more rules which generate each journal line respectively. The rules consider criteria provided and then draw accounting from the identified ‘sources’. The sources can be predefined accounting parameters or they can be custom PL/SQL functions that generate the accounting. The second challenge is that analyzing the journal entry results is no longer as simple as knowing one or two views and/or tables and utilizing simple tools to summarize the journal output. In order for the SLA to function across multiple modules it was necessary to build a series of tables and views that support a new html interface used to research and analyze transaction journal entries.