R12 SLA:FAQ about Custom Sources

By
Advertisement


1. Where can I find step-by-step instructions on how to create a custom source?


Custom Sources can be used to extend the list of sources available to your Application Accounting
Definitions (AAD). Using standard and system source values as parameters, you can write PL/SQL
functions that create custom sources.
This document describes how you can create and use custom sources. It provides two sample PL/SQLfunctions that you can modify for your own use.

How To Create and Use Custom Sources in Accounting

1) Create the backend function in APPS schema, which will be used by the custom source.
CREATE or replace FUNCTION abc_test_function (xx in number) return number
as
begin
Insert into log_test values(xx);
return xx;
end;
This must be a function, not a procedure. You can add debugging in this function to help you
trace/correct any errors. In this example, there is an insert into log_test. The inputs to the function
will be the sources listed in the ‘Parameters’ section below. The output will be the single value you
want the custom source to return. You may include any normal programming logic within the function
code, such as IF statements, Case statements, loops etc.
Do not issue a direct COMMIT or ROLLBACK or any DDL statement (causing implicit commit) with the
custom source function. This will cause the rest of Accounting to fail. If it is absolutely necessary to
COMMIT, please define a separate xyz function and make a call to the xyz function from the custom
source function. The xyz function can be a pragma autonomous transaction with a COMMIT.

2) Create the custom source, as shown below:



3) Create an ADR that uses the custom source, as shown below:
 


4) On the JLD screen, decide which JLT will use this ADR to derive the account.



NOTE: if the ADR is not visible in the screen above, that means the event class INVOICES has not been
assigned the ‘Transaction Distribution GL Account’ source, which the Custom Source uses. All sourcesused in the ADR must be assigned to the event class.
 


2. Where can I obtain a sample PL/SQL code to see how a custom source is created?

Custom Sources are used to extend the list of sources available to your Application Accounting
Definitions (AAD).
First, determine what your primary goal is before defining a custom source. Be sure to explore other
alternatives, as a custom source may impact the performance of the Create Accounting program.
If you decide to create a custom source, you will need to define a PL/SQL function.
In the function, you must determine the input and output parameters. The output parameters will
become the 'return data options' that you will also need to provide on the Custom Source page with the
name of the PL/SQL function. You must also specify the 'input parameters' that you need in the PL/SQL
function on the Parameters section of the Custom Source page.
A custom source PL/SQL function may use the seeded sources that are available to the Application
Accounting Definition or any constant value.
Note: You will have to define the PL/SQL function in the database, meaning that you will also have to
create the function successfully in the particular database.
Here are two examples of a PL/SQL function:

EXAMPLE 1
In this example, the return data type is Numeric, the function name is 'derived_liability', and two
parameters are used—which come from the seeded source:
APHD_INVOICE_BASE_AMT and INV_PMT_BASE_AMT_DIFF
CREATE OR REPLACE FUNCTION derived_liability (APHD_INVOICE_BASE_AMT IN NUMBER,
INV_PMT_BASE_AMT_DIFF IN NUMBER) RETURN NUMBER IS
a := number;
BEGIN
a = APHD_INVOICE_BASE_AMT - INV_PMT_BASE_AMT_DIFF;
RETURN a;
END;

EXAMPLE 2
In this example, the return data type is ALPHANUMERIC and there is only one input parameter of
NUMBER data type, which comes from the seeded source: AMOUNT
CREATE OR REPLACE FUNCTION adj_doc_flex (AMOUNT IN NUMBER) RETURN VARCHAR2 IS
BEGIN
IF AMOUNT > 0
THEN
RETURN '50034206330';
ELSE RETURN '50034206331';
END IF;
END;


3. What is the best way to add a new source in Payables?

You can use a custom source to accomplish your goal. For example, you may want to create a custom source to control accounting (with new Journal line types) based on this new source. The new source might be from a table other than the tables like AP_INVOICE_DISTRIBUTIONS_ALL, AP_CHECKS_ALL etc.

4. Is there a profile option that controls if custom sources can be created?

There is no profile option that controls if custom sources can be created; however, if the profile option SLA: Setup User Mode is set to anything other than Customer, then you will not be able to create custom sources.

5. Is there any Performance impact if I create a custom source?

  • The impact on performance depends on the actual code of the custom source. Each custom source is processed on its own, so there is no easy way of reducing the number of hits on the same table.
  • Custom sources are retrieved during Create Accounting.

6. When should I consider a Custom Source?

A custom source can be used if the source you want to use for any Accounting Method Builder (AMB) component is not provided by the subledger application, like AR or AP. Before defining a custom source, consider the following:
  • A custom source may have a performance impact on the Create Accounting program. 
  • What would you like to use as a source—this would be the output of the PL/SQL function
How would you derive the source value—this would be the input parmeter(s) of the PL/SQL function. The input values must be available in existing sources provided by the subledger application.
For example, if you need a source to pass the Salesrep Cost Center to an Accounting Derivation Rule (ADR), you can create a function to get cost center information from your third party HR system based on input parameters—i.e., Salesrep Name and Transaction Date. Assuming there are existing sources that provide Salesrep Name and Transaction Date, use these sources as input parameters and use the function you create as the PL/SQL Function in the Define Custom Source window.

6. Can I create a custom source for the Entered and Accounted Amount?

Yes, you can create a custom source for the Entered and Accounted Amount.

6a. Can changes be made after the event class has been established?

The Accounting Attribute Assignments window (Accounting Methods Builder > Sources > Accounting Attribute Assignments) only represents the pool of Accounting Attributes, which then further down the setup chain can be used in the Journal Line Accounting Attributes Assignments window and the Application Accounting Definitions Header Assignments window. So it is correct that some values cannot be changed / deleted in the Accounting Attribute Assignments window, but you should not change / delete anything in that window; instead, you need to expand your pool of Accounting Attributes by adding additional lines for that same Accounting Attribute into the Accounting Attribute Assignments window e.g., multiple lines which all use the Accounting Attribute "Entered Amount" can be added.

Once that is done, the Accounting Attribute / Custom Source can be assigned in the Journal Line Accounting Attributes Assignments window. This is also the only place which is really relevant and where the Create Accounting program will look. So in the Accounting Attribute Assignments window, you control which values CAN be used, but not which values ARE actually used by Create Accounting.

7. How do I create a custom source?

You will need to create a PL/SQL function that returns the custom source value you need. The function can have a single or multiple input parameters. The input values must be available as a system or standard source.

For example, the cost center segment value is based on the item's inventory organization and salesrep. You can define a pl/sql function with Transaction Inventory Item Identifier and Salesperson Number and as inputs and return the cost center segment value.

Then define a custom source in SLA. Since this custom source returns cost center segment value, it can be defined with the following Return Data Options:
  • Data Type = Alphanumeric
  • Segment = Cost Center Segment
  • Accounting Flexfield: checked
  • Parameters will be:
    • Sequence Type Name
    • 10 Source Transaction Inventory Item Identifier
    • 20 Source Transaction Salesperson Number
Once this custom source is defined, you will be able to assign it on the Account Derivation Rule (ADR) form. Custom sources can be used in condition, description, or account derivation rules. You do not have to assign the custom source in the Source Assignment window.