lakshmi sampath oaug 2011
TRANSCRIPT
Global Marketing
Meet your SLA with SLA (Meet your Service Level Agreement with Subledger Accounting)
Lakshmi Sampath Enterprise Architect
Dell, Inc.
Session ID - 8149
Global Marketing
Speaker Background • Enterprise Architect at Strategy, Technology and Governance Office (ST&GO)
at Dell, Inc.
• Techno-Functional Expert/Architect in implementing Oracle EBusiness Suite 10.7, 11.0.3, 11i & R12 – including iStore, iSupplier, iPayment, CRM – Incentive Compensation, Resource Manager, Territory Manager, Quote to Cash, Procure to Pay, Project Accounting, Financials & HRMS/Payroll modules.
• Presented several Papers and hosted several Panels at the COLLABORATE , Oracle Open World, Austin Oracle Users Group etc.
• Presentation Selection Committee for COLLABORATE - OAUG Forum
• Member of the OAUG Customer Support Council
• ENCORE Presenter @COLLABORATE-2006
• Board of Director for the Oracle Business Intelligence Special Interest Group (OBISIG) and Austin Oracle Users Group (AOUG)
Global Marketing
Agenda • Oracle EBS R12 SubLedger Accounting (SLA) Overview
• Accounting in 11i Vs R12
• Functional Architecture
• SLA Components Overview
• Case-study on Dell
• Technical Architecture
• Implementation Tips and tricks
• Common Issues after Go-Live
• Q&A
Global Marketing
• SLA is a powerful yet flexible rules-based accounting engine, with centralized data model and repository, which generates accounting entries based on source transactions for all Oracle Applications transactions.
• SLA works with Oracle General Ledger to provide an accounting system tailored to your requirements.
• SLA supports user-defined accounting rules.
• SLA allows multiple accounting representations for a single transaction
• Common data model and interface for all subledgers.
SLA Overview
Global Marketing
Flexible Rules Based Accounting
Transaction
US GAAP
US COA
US Calendar
USD
Primary Ledger Centralized
Accounting
Dr Cr
Take Source
System Accounting Conversion Table Nested Condition Statements
Risk
Class
Single segments, or
complete string
01-402-7620-0000-000
01-402-7620-0000-000
Debtor
Account
High
Med
Low
2301
2302
2301
IF Source System = Online AND Customer Type = Related Party AND Country = USA OR Customer Type = Internal
Source
Field Result Rule
WIP
Account
Result
1550
Global Marketing
French Rules
Plan Comptable
French Calendar
EUR
Secondary Ledger
US GAAP
US COA
US Calendar
USD
Primary Ledger Centralized
Accounting
Dr Cr
Transaction
Account
10.000.100.000
15.000.101.000
18.000.102.000
22.000.212.000
65.000.267.000
Account
100.001.2120.000
120.001.2120.000
130.001.2120.000
140.001.2120.000
150.001.2120.000
Translation Table
Sub Ledger Accounting: Multiple Accounting Representations for Parallel Financial Reporting
Global Marketing
Accounting in 11i vs R12
In R12, Accounting Model is separated from the transaction model.
11i -Accounting Model and Transaction Model are tightly integrated
Global Marketing
11i
R12
11i vs R12 – Accounting Structure
GL Primary Ledger
Secondary Ledger
Receivables Payables Projects Fixed Assets
XLA
Purchasing iProcurement
GL Primary Set of Books
Receivables Payables Projects Fixed Assets Purchasing iProcurement
Global Marketing
SLA Functional Overview
• SLA is a service within a product and not a module or application or a product.
• No separate responsibility for SLA
• Single step posting to all ledgers.
• Real time/online accounting.
• Provides audit-trail for all transactions.
Global Marketing
Subledger Applications
Reconciled: Docs, SLA, GL in synch
Subledger Accounting Service
General Ledger
SLA keeps the Subledgers & GL tied out
Accounting Configurations
Journal Entry Setup
Accounting Program
Subledger Journal Entries
Accounting Events
Transactions Subledger Balances
GL Journal Entries and
Balances
Global Marketing
Opening
Balance Debit Credit
Closing
Balance
GENERAL LEDGER
Cash 100 3,500,000 2,750,000 750,100
Receivables 1,000,000 3,000,000 3,500,000 500,000
Inventory 500,000 1,200,000 1,500,000 200,000
Assets 100,000 100,000
Prepayments 1,000 1,000
Payables (750,000) 2,800,000 2,700,000 (650,000)
Accruals (250,000) 50,000 (300,000)
Shares (100,000) (100,000)
Previous Results (501,100) (501,100)
Balance Sheet - 10,500,000 10,500,000 -
Sales 3,000,000 (3,000,000)
COGS 1,500,000 1,500,000
R&D 300,000 300,000
S&M 300,000 300,000
G&A-Fin 300,000 300,000
G&A-IT 150,000 150,000
G&A-HR 150,000 150,000
Period Income 300,000 300,000
Income Statement - 24,000,000 24,000,000 -
SLA Controls that GL Balances and SLA Balances tie, and that both tie to the document (invoice, payment, etc.) sources
Opening
Balance Debit Credit
Closing
Balance
SUBLEDGER ACCOUNTING
Receivables
Customer One 250,000 150,000 400,000 -
Customer Two 350,000 250,000 450,000 150,000
Customer Three 100,000 1,500,000 1,400,000 200,000
Customer Four 100,000 800,000 750,000 150,000
Customer Five 200,000 300,000 500,000 -
Total 1,000,000 3,000,000 3,500,000 500,000
Payables
Vendor I (250,000) 1,000,000 900,000 (150,000)
Vendor II (100,000) 75,000 50,000 (75,000)
Vendor III (150,000) 725,000 775,000 (200,000)
Vendor IX (250,000) 1,000,000 975,000 (225,000)
Total (750,000) 2,800,000 2,700,000 (650,000)
General Ledger Balance
Sub Ledger Details
Global Marketing
Transaction
Create Accounting Process
SLA Journal Entry
GL Journal Entry
GL Balances
Journal Import
Posting
Transaction Flow through SLA to GL
Global Marketing
SLA Components at a Glance
Global Marketing
SLA Accounting Components at a Glance
Subledger Accounting
Method
(SLAM)
Application Accounting Definition
(AAD)
Journal Line Definitions
(JLD)
Sources Ledger
SLA component
(AMB)
Supporting
Components
Global Marketing
Types of Subledger Accounting Method
Five Sub ledger Accounting Methods are seeded in the Application.
• Accrual with Encumbrance accounting
• Cash with Encumbrance Accounting
• Standard Accrual
• Standard Cash
• US Federal Accounting
Global Marketing
SLA Event Model
Event Types
For each transaction type, defines possible actions with accounting significance.
Event Classes
Classifies transaction types for accounting rule purposes.
Global Marketing
Event Model
Event Types Receipt
Created
Applied
Unapplied
Updated
Reversed
Payables Invoice Validated
Adjusted
Cancelled
Purchase Order
Created
Distributed
Cancelled
Rejected
Finally completed
Event Classes Receivables Transactions
Receipts Reversal Bills Receivable Adjustments
Payables Invoice Debit Memo Prepayment Payments Refunds
Purchasing Requisition
Purchase Order
Global Marketing
Journal Line Definitions
Line Type
Journal Entry Descriptions
Account Derivation Rule (ADR)
Global Marketing
Case-Study
Requirement:
Ensure that the create accounting process derives the correct Accounting Flexfield segments based on the receipts applied to the customer based on the sales channel to Accounting Flexfield mapping.
This should work for any cash application – Receipt applied, Receipt unapplied, Receipt reversed etc.
Global Marketing
Case-study
Global Marketing
• Create/Define a PL/SQL code
• Define Custom Sources
• Define Mapping Sets (Optional)
• Define Account Derivation Rules
• Define Journal Line Types (JLT)
• Define Journal Entry Descriptions
• Define Journal Line Definitions (JLD)
• Define Application Accounting Definition (AAD)
• Define Subledger Accounting Method (SLAM)
• Assign SLAM to Ledger
Solution Steps
Global Marketing
Subledger Accounting Definition Steps - Review
Global Marketing
Step 1 – Creation of Custom Source
This must be a backend function in APPS schema, (function in a package or a stand alone function).
These are the parameters in the function – usually mapped to available sources in SLA
Global Marketing
Step 2 – Account Derivation Rule
Custom Source Code/Name
Account Derivation Rule Name
Global Marketing
Step 3 – Create Journal Line Types Journal Line Type copied from seeded
Journal Line Type with Transfer to GL set to Summary
Global Marketing
Step 4 – Create Journal Entry Descriptions Journal Description Name
Global Marketing
Journal Line Definition copied from seeded
Step 5 – Associate JE Description and ADR to Journal Line Definitions at the Journal Line type
Journal Entry Description
Account Derivation Rule
Journal Line Type
Global Marketing
Step 6 –Validate Application Accounting Definition
After attaching the JLD to the Event Class, you have to click on Validate and validate the Accounting Definition
Attach Journal Line Definition to the Event Class
Global Marketing
Step 7 –Attach the Application Accounting Method to the Subledger Accounting Method (SLAM)
Subledger Accounting Method copied from seeded
Attach AAD to Subledger Accounting Method
Global Marketing
Step 8 –Assign SLAM to the Ledger
Attach Subledger Accounting Method to Ledger
Global Marketing
Run the Create Accounting Process
Create Accounting gives 3 options to create accounting
Global Marketing
View Accounting JE from Receipts window
Global Marketing
View T-Accounts
Global Marketing
This can be configured in SLA to capture additional information
View Supporting References
Global Marketing
Subledger Accounting Definition Steps - Review
Global Marketing
Technical Architecture of SLA
Global Marketing
Transaction ( AR )
Distributions
(
RA_CUST_TRX_LINE_GL_DIST
_ALL
&
AR_DISTRIBUTIONS_ALL )
TRANSACTIONS ACCOUNTING
Distribution Links( XLA_DISTRIBUTION_LINKS )
Accounting Lines( XLA_AE_LINES )
Accounting Headers( XLA_AE_HEADERS )
Accounting Events( XLA_EVENTS )
LEDGER
TABLES
Entities( XLA_TRANSACTION_ENTITIES )
Headers
( RA_CUSTOMER_TRX_ALL
&
AR_CASH_RECEIPTS_ALL
&
AR_ADJUSTMENTS_ALL )
GL_IMPORT_REFERENCES
Global Marketing
Technical Architecture The important tables that store data of SLA are given below:
• XLA_SUBLEDGERS
• XLA_SOURCES_B
• XLA_EVENT_SOURCES
• XLA_CONDITIONS
• XLA_ACCTG_METHOD_RULES
• XLA_EVENTS
• XLA_AE_HEADERS
• XLA_AE_LINES
• XLA_ACCOUNTING_ERRORS
• XLA_DISTRIBUTION_LINKS
Global Marketing
Auto Accounting Subledger Accounting
Lookup Sets Mapping Sets
Intermediate Value Input value
Auto Accounting Rules Account Derivation Rules
Parameter Source
Functions Journal Line Types
Auto Accounting Rule Assignment
Journal Line Definitions
Auto-accounting vs Subledger Accounting
Global Marketing
Implementation Considerations
Global Marketing
Tips and Tricks
• Subledger accounting rules and definitions of one module can not be accessed from any other module.
• Seeded Subledger Accounting Methods can not be modified. They need to be copied before they can be modified.
• Any change made to a Sub ledger accounting setup invalidates the accounting process. Before running the “Create Accounting” process, Application Accounting Definition must be validated in order to make sure that the changes are logical.
Global Marketing
Reconciliation
Accounts Reconciliation has improved with the introduction of SLA. This is possible mainly because of the following:
• Transaction Level Journals
• Transaction Details
• Drilldown Facility
• Reports
Global Marketing
SLA Standard Reports
• Account Analysis Report
• Journal Lines Report
• Open Account AP Balances Listing
• Open Period Balances
• Subledger Multi period Accounting and Accrual Reversal Report
• Standard Reports for SLA
• Subledger Period Close Exceptions Report
• Third Party Balances Report
• Trading Partner Summary Report
Global Marketing
Upgrade Considerations
• By default, the R12 upgrade migrates current fiscal year accounting entries from each application to the SLA data model. This can be modified if needed.
• For prior periods, the historical data is preserved. You can drill down from General Ledger to the historical transactions using standard GL drilldown.
• You can upgrade prior periods by request during upgrade
Global Marketing
Issues after Go-Live
Global Marketing
• Journals going missing between subledgers and GL (Negative Ledger id bug)
• Incomplete transactions preventing close
Issues after Go-live
Global Marketing
Data transfer happens from subledger to GL through SLA, during the transfer, some data corruption issues have been reported on the SLA to GL bridge. These issues could be categorised into four categories :
1. Data with Negative Ledger_id in GL tables. 2. Data Marked as transferred in SLA but not reached GL. 3. Data Marked as Not transferred in SLA, but reached GL. 4. Multiple posting issues.
Solution: All the patches are applied, but still having this issue.
No standard report available to highlight differences between SLA and GL layer. We are using custom reports to identify those data corruption issues.
Issue 1: Negative ledger_id Bug 883557.1
Global Marketing
Subledger Period Close Exceptions Report is used to review exceptions
Incomplete: Not ready for accounting. This will be seen in the below scenarios: a) There are incomplete invoices / credit memos or unapproved adjustments
Solution: The invoices / Credit Memos need to be completed or deleted in order to have the period closed.
But we are on 12.1.3, on the latest patch set, and we were still unable to close due to incomplete transactions every month.
We had to develop a fix to overcome this.
Issue 2: Incomplete transactions preventing Close Bug 7623057
Global Marketing
• The purpose of this paper is to give a high-level overview of R12 Subledger Accounting, functional and technical architecture and case-study.
• Hope the information shared in this paper will be more than beneficial to the Oracle Applications User community as a whole.
Epilogue
Global Marketing
Special thanks to the following people at Dell who made this presentation possible.
Hemal Shah, IT Director
Jason Wallis, Executive Director
Rhonda Gass, Vice President of IT
Richard Welch, Solution Architect
Kadey Cochran, Solution Architect
Ed Warren, Solution Architect
Imtiaz Saiyed, Enterprise Architect
Acknowledgements
Global Marketing
• A very special thanks to OAUG GL SIG and Rob Zwiebach for the informative webinars on SLA.
• Last but not the least, Special thanks to my husband Ram Ganapathy for his full support and encouragement in writing this paper.
Acknowledgements