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.