go beyond basic r12 subledger accounting - a deep dive...

Go Beyond Basic R12 Subledger Accounting - A Deep Dive Into the Supporting References Functionality

Prepared by:

Prithis Das

Dynamique Solutions LLC

Presenter’s background

■ Over 18 years in the Oracle EBS sphere

■ Over 22 years of business process improvement experience

■ Consulting experience with Big 5 and other major organizations

■ Oracle Certified Specialist in General Ledger

■ Chartered Accountant and Management Accountant from India

■ Authored and presented papers on SLA, Web ADI, Reports Manager at several OAUG and local events

■ Currently on a major R12 upgrade

■ Contact:

▪ Cell: 630.3649457

▪ Email: [email protected]

▪ Web: Dynamiquesolutionsllc.com

Agenda

■ Introduction to Supporting References

■ Case Study

■ Building blocks

■ The Oracle process

■ View Supporting References

■ Key considerations

■ Limitations

■ References

■ Q&A

Introduction to Supporting References

■ What are Supporting References?

■ Powerful analytic feature

▪ In effect, extends the Accounting Flexfield without cluttering GL

with subledger detail

▪ Also known as Analytical Criteria in Fusion Apps

■ Supporting References are data items that:

▪ Store additional transaction information on ―Subledger Journal

Entry (JE)‖ at header or line level to be used for analytical

purposes

— Note the use of Subledger JE, not General Ledger JE

▪ Maintain account balances for a specific Ledger at Period level

for every combination of ―Supporting Reference + Code

Combination ID (CCID)‖

Introduction

■ Examples of Supporting References

▪ Product or Line of Service on Receivables invoices when

―Product‖ is not a segment available in the Accounting Flexfield

▪ Payee Name on Miscellaneous Receipts in Receivables

▪ Project number on Payables invoices when ―Project‖ is not an

available AFF segment and Projects module is not used

▪ Project Manager, Salesperson information on invoices

▪ Employee and Deduction information on Payables invoices for

Payroll deductions

▪ Other examples are - Geographies, channel, industry,

investment type, fund manager or product category can be

tracked as Supporting References without including these key

business dimensions in the Accounting Flexfield

Introduction

Case Study

■ Background information

▪ Our Accounting Flexfiled segments are Company, Account,

Department and Intercompany

— Note that there is no segment for

– Product

– Branch Office

– Project

■ Business Requirements

▪ Track revenues in Oracle AR lines by products, branches and

projects

▪ Tag AR invoices with Project Managers who have generated the

revenue

— Note that there is no field to capture the Project Manager

Case Study

Building Blocks

■ Receivables Descriptive Flexfield configurations

▪ Invoice Line Information Descriptive Flexfield

▪ Value Sets to hold DFF values

■ Supporting References Headers, Details and Sources

■ Subledger Accounting configurations

▪ Subledger Accounting Method

▪ Application Accounting Definition

▪ Event Class ―Invoice‖

— Supporting Reference for PM will be attached as a Header

Assignment

▪ Journal Line Definition

▪ Journal Line Type

— Supporting Reference for Line Attributes will be attached to the JLT

Building Blocks

■ Invoice Line Information Descriptive Flexfield

▪ To capture Revenue

Type or Product

▪ To capture Project

Number

▪ To capture Branch

Office

Building Blocks

■ Value Sets to hold values for:

▪ Revenue Types or Products

▪ Project Numbers

▪ Branch Offices

■ Values:

▪ Products

▪ Projects

▪ Branch offices

Building Blocks

■ Invoice Information Descriptive Flexfield

▪ To capture Project Manager

Building Blocks

■ Value Set to hold values for:

▪ Project Managers

■ Values:

▪ Project Managers

Building Blocks

■ Supporting References

▪ Unlimited number of Supporting References can be defined

— Navigation: Receivables Manager > Setup > Accounting > Accounting Methods Builder > Journal Entry Setups > Supporting References

Building Blocks

Reference Detail

Reference Header

Source assignments

■ Supporting References: Header

▪ Owner is ―User‖ for all custom Supporting References

▪ Code

— First character must be alphabetic

— Remaining characters may be either alphanumeric or underscores

— Alphabetic characters must all be uppercase

— No spaces are permitted

■ Name and Description field

■ Enabled checkbox

Building Blocks

■ Maintain Balances

▪ Supporting Reference without balances

— Mainly used as Subledger Journal Entry (JE) references

— Can be assigned at Subledger Journal Entry Header (JEH) or Journal Entry Line (JEL) level

▪ Supporting Reference with balances

— Supporting Reference Balances are calculated

— Can only be assigned at line level

Building Blocks

■ If Maintain Balances is checked, additional drop-down ―Year End Carry Forward‖ is displayed

▪ Always

— Supporting Reference Balances at the end of the fiscal year are always

carried forward to the next fiscal year

▪ Based on account

— Ending Supporting Reference Balances for balance sheet accounts

(accounts whose account type is Asset, Liability or Owner’s Equity) are

carried forward to the next fiscal year, while they are reset to zero for

income statement accounts (accounts whose account type is Expense or

Revenue)

▪ Never

— Supporting Reference Balances are reset to zero at the beginning of a new

fiscal year

Building Blocks

Building Blocks

■ In our example the first Supporting References Header is

Parameter Value

Code XXPD_AR_INVOICE_DETAILS

Name XXPD AR Invoice Line Details

Enabled Yes

Maintain Balances Checked

Year End Carry Forward Based on Account

Building Blocks

■ In our example the second Supporting References Header is

Parameter Value

Code XXPD_PROJECT_MANAGER

Name XXPD Project Manager

Enabled Yes

Maintain Balances Unchecked

Year End Carry Forward Field does not appear on screen

Building Blocks

■ Supporting References: Detail

▪ Each Supporting Reference can have maximum 5 ―Supporting

Reference Details

▪ Detail Code

— First character must be alphabetic

— Remaining characters may be either alphanumeric or underscores

— Alphabetic characters must all be uppercase

— No spaces are permitted

■ Detail Name

■ Detail Description

Building Blocks

■ In our example, the Supporting References Details for the Supporting Reference ―XXPD AR Invoice Line Details‖ are:

Detail Code Detail Name

XXPD_REVENUE_TYPE Product

XXPD_PROJECT_NUMBER Project Number

XXPD_BRANCH_OFFICE Branch Office

Building Blocks

■ In our example, the Supporting References Details for the Supporting Reference ―XXPD Project Manager‖ are:

Detail Code Detail Name

XXPD_PROJECT_MANAGER XXPD Project Manager

Building Blocks

■ Supporting References: Source Assignments

▪ Each Supporting Reference Detail can have one or more

―Sources‖ assigned

▪ Only one source can be assigned for each Application and each

Event Class

▪ Tip: In this example we have used a DFF Attribute as a Source

for a Supporting Reference. Your Source could be one of

several hundred existing table columns.

Building Blocks

■ Source Assignments

▪ Each Supporting Reference Detail can have one or more

―Sources‖ assigned

▪ We have selected (for Detail ―Product‖)

— Application = Receivables

— Event Class Name = Invoice

— Source Name = Transaction Line Information Flexfield Attribute 1

▪ because Product will be stored in this attribute as per DFF setup

Building Blocks

■ Source Assignments

▪ Your Source could be one of several hundred existing table

columns.

Building Blocks

■ In our example, for the Supporting Reference ―XXPD AR Invoice Line Details‖, our Source Assignments are:

Detail Application Event Class Name

Source Name

Product Receivables Invoice Transaction Line Information Flexfield Attribute 1

Project Number

Receivables Invoice Transaction Line Information Flexfield Attribute 3

Branch Office

Receivables Invoice Transaction Line Information Flexfield Attribute 4

Building Blocks

■ In our example, for the Supporting Reference ―XXPD Project Manager‖, our Source Assignments are:

Detail Application Event

Class

Name

Source Name

Product Receivables Invoice Transaction Information Flexfield

Attribute 1

Building Blocks

■ Supporting References summary

Building Blocks

■ Custom Subledger Accounting Method (SLAM)

■ Within the custom SLAM

▪ Custom Application Accounting Definition (AAD) for

―Receivables‖

■ Within the custom AAD

▪ For the Event Class Invoice, set the Supporting Reference for

Project Manager as a Header Assignment


▪ Custom Journal Line Definition for Event Class ―Invoice‖

■ Within the custom Journal Line Definition (JLD)

▪ Attach the Supporting Reference to the custom Journal Line

Type

■ Validate the Application Accounting Definition (AAD)

■ Attach the custom SLAM to the Ledger

Building Blocks

■ Custom Subledger Accounting Method (SLAM)

Building Blocks

■ Within the custom SLAM

▪ Custom Application Accounting Definition (AAD) for

―Receivables‖

Building Blocks


▪ For the Event Class Invoice, set the Supporting Reference for

Project Manager as a Header Assignment

Building Blocks


▪ Custom Journal Line Definition for Event Class ―Invoice‖

Building Blocks

■ Within the custom Journal Line Definition (JLD)

▪ Attach the Supporting Reference to the custom Journal Line Type

Building Blocks

■ Validate the Application Accounting Definition (AAD)

Building Blocks

■ Attach the Custom SLAM to the Ledger

▪ Accounting Setup Manager

The Oracle Process

The Oracle Process

■ Enter Receivables Invoices

▪ In the Transaction Information DFF enter the Project Manager

The Oracle Process

■ Enter Invoice Line

▪ In the DFF, enter Product, Project, Branch Office

The Oracle Process

■ Run Create Accounting in Final mode

The Oracle Process

■ Run Create Accounting in Final mode

▪ The Subledger Accounting Balances Update program

(XLABABUP) runs and updates the Supporting References

balances

▪ ―Online Accounting‖ does not spawn Subledger Accounting

Balances Update concurrent program, but balances are

calculated

▪ Subledger Accounting Balances Update concurrent program can

also be run standalone via SRS form

The Oracle Process

Transaction

SLA Journal Entry

GL Journal Entry

GL Balances

Create Accounting

Journal Import

Posting

Supporting Reference Balances Balance Calculation

Functional

perspective

Suble

dger

Ge

ne

ral L

ed

ge

r

The Oracle Process

■ Technical perspective

▪ Run ―Subledger Accounting Balances Update (XLABABUP)‖

concurrent program

▪ Program picks up data from XLA_AE_LINE_ACS

▪ Stamps ―ANALYTICAL_BALANCE_FLAG = P‖ in

XLA_AE_LINES to indicate balance calculation pending

▪ Populates balances in XLA_AC_BALANCES

▪ Updates ―ANALYTICAL_BALANCE_FLAG = Y‖ in

XLA_AE_LINES to indicate balance calculation done

View Supporting References


■ The Subledger Accounting Balances Update program runs and updates the Supporting References balances

■ There are no standard reports for Supporting References

■ Oracle provides a browser-based form to view Supporting Reference balances

▪ Query balances for Ledger, Supporting Reference and Period

▪ Click ―Period Activity‖ to drill down to the journal lines

contributing to the Supporting Reference Balances

■ The Supporting References tagged to transactions can also be viewed through Account Analysis and Drilldown.

■ You may need to clear the cache to see results immediately

▪ You can do this through the Functional Administrator

responsibility


■ Oracle provides a browser-based form to view Supporting Reference balances

▪ Navigation is Receivables Manager > Inquiry > Accounting >

Supporting Reference Balances


■ Supporting References balances can be exported to Excel for further analysis


■ The Supporting References tagged to transactions can also be viewed through Account Analysis and Drilldown.

▪ Navigation is General Ledger > Inquiry > Account Analysis and

Drilldown


■ The Supporting References tagged to transactions can also be viewed through by clicking on the Supporting References icon.


■ Supporting References balances can be viewed from Subledger Journal Entries

▪ Drill down into SL JE from GL JE Line, using Line Drilldown

button


■ Supporting References balances can be viewed from Subledger Journal Entries

▪ Drill down into SL JE from GL JE

Key Considerations

Key Considerations

■ Initial balances for Supporting References

■ Supporting References values can be imported from external system through standard interfaces and used in SLA for further analysis

■ Supporting References can be setup for several Subledgers

■ Custom reports may need to be developed to report on balances

Key Considerations


▪ Starting balance for Supporting Reference in context of an

Application, Ledger and CCID

▪ Initial Balance will contribute to ―Supporting Reference Source

Value‖ combination balance calculated on any day going

forward the date of Initial Balance

▪ Should be defined for a Supporting Reference only if

— Supporting Reference is ―Enabled‖

— ―Maintain Balances‖ flag is set to ―Yes‖

— Initial Balance is in Ledger Currency for a particular ―Period‖

▪ No Journal Entry in SLA to support ―Initial Balances‖

▪ No balance should exist prior to date of Initial Balance

Key Considerations


▪ Define Initial Balances as ―Net‖ of Debit (Dr) and Credit (Cr)

balance

▪ Not possible to define Initial Balances as separate balances for

Dr and Cr

▪ Can be defined for closed period

▪ Can be defined even after SLA has created balances and

entries for Supporting Reference Value

▪ Initial Balances can be modified as well as deleted

▪ Periods of Initial Balances can be modified

Key Considerations

■ Steps to upload Initial balances

■ Upload data into Interface Table -

▪ XLA_AC_BALANCES_INT

■ Submit ―Import Initial Balances for Supporting References (XLAACSUPREF) concurrent program to import data from Interface Table into Base Table –

▪ XLA_AC_BALANCES

■ View Report generated from Concurrent Program

▪ Report uses XLAACSUPREFTEM.rtf

Limitations

Limitations

■ No Average Daily Balances

■ No Entered Currency

■ No standard reports

■ Not possible to get Supporting Reference Balances to include Journal Entries created before the Supporting References were defined

References

References

■ References

■ Oracle Subledger Accounting User's Guide, Part No. B13984-03, Note 748438.1

■ Oracle Whitepaper titled ―Subledger Accounting Supporting References‖

■ Oracle Webcast on Supporting References

■ Note 790978.1 on My Support website: SLA FAQ

Q & A

• Contact

Email [email protected]

Cell 630.3649457

Website Dynamiquesolutionsllc.com

Additional questions?

go beyond basic r12 subledger accounting - a deep dive...

Documents