Oracle R12 SLA Customization
Author: Sireesh Vishnubhatla
Page 1
Contents
Contents
Introduction
Definition of Sub-Ledger Accounting
Sample Client Business Process:
Process/System Flow
Technology
Program Logic (Code used to derive the Location (Segment2) from Bank
Stmt Line)
1. To update the table of contents, put the
cursor anywhere in the table and press
[F9]. To change the number of levels
displayed, select the menu option Insert
>Index and Tables, make sure the Table
of Contents tab is active, and change the
Number of Levels to a new value.
Page 2
Page 3
Introduction
This paper enumerates factors which should be taken into consideration while doing
customization of Sub-Ledger Accounting(SLA) in R12 using a data base function.
These factors can be categorized broadly into two: people and technology. Following
recommendations given in this paper should lead to better understanding on how we can setup
and customize the Sub-Ledger Accounting(SLA) which is a new feature in R12.
Audience
Project managers, Functional consultants, Technical consultants, Technical designers /
developers.
Page 4
Definition of Sub-Ledger Accounting
Release 12 of Oracle Applications, Subledger Accounting (SLA) has been introduced, which is a Rule-
based accounting engine, toolset & repository which is supporting most of Oracle business Suite modules.
SLA has an option of allowing multiple accounting representations for a single business event, resolving
conflicts between corporate and local fiscal accounting requirements. The Functionality is very similar to
Global accounting engine
● SLA is an intermediate step between subledger products and the Oracle General Ledger.
● Journal entries are created in Subledger Accounting and then transferred to Oracle
General Ledger
● Each subledger transaction that requires accounting is represented by a complete and
balanced subledger journal entry stored in a common data model
This is the typical flow within one product of SLA (e.g Purchasing, AP, Cash Management) that can be
best shown in the below change diagram as discussed:
Page 5
Sample Client Business Process:
Business Requirement
Client reconciles store sales on a daily basis. Daily BAI files will be loaded into Oracle Cash
Management to be reconciled with summarized Point of Sale (POS) data. The POS data is loaded
into Oracle, summarized storewise and daywise for tender type Cash and Check only. This
amount should match up to the amount that was deposited by the store manager.
When there is a variance between the deposited amount and the amount that is reported by POS
or a debit/credit memo from the bank, this expense amount needs to be applied at the store level.
Standard Oracle functionality
Oracle Functionality allows for the variance/debit/credit amount to be applied at the
Consolidated Account level.
Proposed Solution
In order to satisfy the requirement of tracking expenses at the store level, the proposed
workaround is to store the location segment in a Flexfield on the bank statement line and using
Oracle’s Custom SLA functionality to insert this location value into the location segment of the GL
ValueString.
There are 3 transaction types which need to be tracked at the store level:
1. Variance expenses between Bank Statements and POS.
2. Clearing POS/Certegy Transactions.
3. Debit Memos and Credit Memos on Bank Statement Lines.
Workaround using Custom SLA:
The first two transaction types will be allocated at the store level without using Oracle’s SLA
functionality.
The workaround is required for Transaction Type (3) Debit and Credit Memos on the Bank
Statement Line which are created using the ‘Bank Statement CashFlow Creation’ program
in Oracle. By defining this custom source, it will remove any chance for error when applying
expenses to the store level.
In the previous design without SLA feature, a discover report would contain all of the journal
entries to be uploaded into Oracle GL. The Cash Accounting analyst would have to copy and paste
these adjusting entries into Oracle.
Using the SLA functionality, there is no adjustment needed as the first expense journal entry will
already be at the store level. Additionally this removes any chance of error during the copy/paste
process.
For any Debit Memo or Credit Memo Application and Reconciliation, the “custom source” will
allow us the ability to apply any expense amount to the store location. In order to use Subledger
Accounting to meet this requirement, we will need to define a custom source which will derive the
Location Segment (DFF) stored at the bank statement line that can hold the store location for the
Page 6
GL Accounting String.
Assumptions:
There is a DFF on every bank statement line which contains the GL location segment for that
specific store.
The DFF will be restricted to a list of values which only contains valid location segments.
Page 7
SLA: Developer> Sub ledger Accounting Setups>Accounting Methods
Builder>Sources>Custom Sources
Page 8
Process/System Flow
Bank Statement Lines
(Store Location value in Attribute1(DFF) column)
Derive Location Segment Value from SLA: Custom Source (Custom Function)
“Bank Statement Cash flow Creation” Program Transactions (Create Accounting using the “Store
Location” and Accounting Combination from SLA Custom Source
Create Accounting
Page 9
Page 10
Technology
Following are the Setups required in the Sub-Ledger module (here it is Cash Management), to
successfully implement SLA Customization
CE: SLA Developer
Cash Management> SLA Developer>Subledger Accounting
Setups>Accounting Methods Builder>Methods and
Definitions>Subledger Accounting Methods
XX Process: Business Area: Date:
Cash Management
Control Number: Priority(H, M, L): Process Owner:
H XYZ
Subledger Accounting Method
Method Code XXGL_Custom_Accounting_Convention Owner User
Enabled
Method Name XXGL_Custom_Accounting_Convention Yes
Transaction XXGL_Corporate_Flexfield Accounting XX_GL_Corporate_Flexfield
Application Accounting Definition Assignments
**This is a copy of the Standard Accrual Accounting Method. All Definitions will remain
the same with the exception of Cash Management.**
Application Name Owner Start End
Cash Management XX_Cash_Management_Standard User
Application Accounting Definitions
Application
Cash Management Owner
User
XXCE_Standard_Accounting Version
Definition Code
XXCE_Standard_Accounting Description
Definition Name
XX_GL_Corporate_Flexfield Accounting XX_GL_Corporate_Flexfield
Transaction
Page 11
Application Accounting Definition Assignments
**This is a copy of the Cash Management Standard Accounting Definition. All Event
Definitions will remain the same with the exception of Bank Statement Cash Flows.**
Create
Event Class Event Type Validation Status Locked
Accounting
Bank Statement Cash
All Valid Yes Yes
Flows
Journal Lines Definitions Assignments
Event Class Owner Description
XXCE_Bank Statement Cashflow User
Application Accounting Definitions
Application Cash Management Event Type All
Event Class Bank Statement Cashflows Owner User
Enabled
Definition Code XXCE_Standard_Accounting Yes
Budgetary
Definition Name No
Control
Description
Transaction XX_GL_Corporate_Flexfield Accounting XX_GL_Corporate_Flexfield
Line Assignments
Journal Line Type Owner Inherit Line Description Owner A
Description
Statement Line Cash CR Oracle Yes Statement Line Journal Entry Line Oracle Y
Description
Statement Line Cash DR Oracle Yes Statement Line Journal Entry Line Oracle Y
Page 12
Description
Statement Line Payment Offset DR Oracle Yes Statement Line Journal Entry Line Oracle Y
Description
Statement Line Payment Offset CR Oracle Yes Statement Line Journal Entry Line Oracle Y
Description
Account Derivation Rules
Statement Line Payment Offset DR
Segment Inherit Rule Name Description Side
All Segments No Cashflow Offset Account
Location No XXCE_Location
Statement Line Payment Offset CR
Segment Inherit Rule Name Description Side
All Segments No Cashflow Offset Account
Location No XXCE_Location
Account Derivation Rule
Application Cash Management Owner User
Rule Code XXCE_Location
Rule Name XXCE_Location Enabled Yes
Description
Transaction XX_GL_Corporate_Flexfield Accounting XX_GL_Corporate_Flexfield
Output Type Segment - Location
Page 13
Priorities
Priority Value Type Value Input Source Segment
10 Source XXCE_LOCATION_GET_FNC
Condition
Seq ( Source Segment Operator Value Type Independent Value Segment An
Value
10 XXCE_LOCATION_GET_FNC IS NOT NULL
Custom Source
Application Cash Management
Custom Source Code XXCE_LOCATION_GET_FNC
Custom Source Name XXCE_LOCATION_GET_FNC Enabled Yes
Description
PL SQL XXXXCE_LOCATION_GET_
Function C
Return Data Options
Data Type Segment Lookup Application Value Set Accounting Flexfield Lookup Type
10 Management Segment Yes
Parameters
Seq Type Name Source Description
1 Source Cashflow Number Cashflow number that uniquely identifies the cashflow
Page 14
Page 15
Page 16
Program Logic (Code used to derive the Location (Segment2)
from Bank Stmt Line)
CREATE OR REPLACE FUNCTION XXCE_LOCATION_GET_FNC (p_cashflow_id NUMBER) RETURN
CHAR IS
========================================================================
=====
-- Function: XXCE_LOCATION_GET_FNC
--
-- Desc: This function derives the Unique Location Segment (Segment2-Retail Store) from Stmt Line DFF
--
-- Name Type Data type Description
-- ---------------------- --------- ------------- -----------------------------
-- p_cashflow_id IN NUMBER Cashflow Number
-----------------------------------------------------------------------------
-- Return Char(Location)
--
--
-- MODIFICATION HISTORY
--
-- Date Chg Req # Author Comments
-- ----------- --------- --------------- ------------------------------------
-- DD-MM-YY 1.0 Name Original
-- =========================================================
l_location apps.gl_code_combinations.segment2%TYPE;
l_cashflow_id NUMBER:=p_cashflow_id;
BEGIN
--Derive Location in A/c Flexfield from the Stmt Line DFF
SELECT csl.attribute1 Location_Segment
INTO l_location
FROM
ce_cashflows cc,
ce_statement_lines csl
WHERE cc.statement_line_id = csl.statement_line_id
AND csl.attribute1 IS NOT NULL
AND cc.cashflow_id = l_cashflow_id;
RETURN l_location;
EXCEPTION
WHEN OTHERS THEN
-- If no Location Segment is found then Return Null to default Std Acc. Combination
RETURN NULL;
END XXCE_LOCATION_GET_FNC;
Page 17
