Integrating Custom Sub-Ledgers with EBS Using BI Applications Financial Analytics. 03/09/2012 Jamie Adams, Laxmi Vara Prasad Duvvuri AST Corporation

(1)

Integrating Custom Sub-Ledgers with EBS

Using BI Applications Financial Analytics

03/09/2012

Jamie Adams, Laxmi Vara Prasad Duvvuri AST Corporation

(2)

2

Agenda

 Scenario  Previous Options  Proposed Solution/Approach

 Overview of EBS GL Posting

 Overview of Financial Analytics

 EBS Setups Required

 Data Model Design

 OBIEE Development (RPD & Webcat)

 Reporting

 References

(3)

(4)

4

Problem Statement

 Applicable to Organizations with

Business cycles that use Legacy/Third party Systems in conjunction with

Oracle Applications

 Oracle Application GL Module is the

Centralized repository for Balances (Actuals, Encumbrances & Budgets)

 All Third Party/Legacy Accounting data

is posted to Oracle GL Periodically

 Users require Drilldown functionality for

(5)

Accounting and Posting

Sub-Ledger#1

Sub-Ledger#2

Sub-Ledger#3

Oracle EBS Sub-ledgers Oracle General Ledger Interface Transfer Accounting entries Transfer Accounting entries Transfer Accounting entries Transfer Accounting entries Oracle General Ledger Journal Posting

Accounting & Posting

 All accounting distributions are

stored in respective sub-ledgers

 Posted to GL

 All Reports are Generated from

(6)

6

Drilldown Feature

Drilldown

 Ability to View Sub-Ledger

Distributions/Transaction details pertaining to a Journal Entry

 In Oracle Applications drilldown

is supported for various Sub-Ledgers text text Sub-Ledger Accounting Entries Journal Lines Sub-Ledger Accounting Entries Sub-Ledger Accounting Entries Sub-Ledger Accounting Entries Sub-Ledger Accounting Entries Sub-Ledger Accounting Entries Sub-Ledger Accounting Entries Sub-Ledger Accounting Entries Journal Lines Journal Lines

(7)

(8)

8

Previous Options & Pain Points

In a World without Oracle Business Intelligence Applications Businesses would have had to –

 Develop custom User Interface to Support Sub-Ledger reporting

needs

 Manual Effort required during Reconciliation/Period Close activity

 Maintain and Support Multiple Systems

(9)

(10)

10

Oracle Applications - GL Posting & Drilldown

(11)

GL Posting Process - Recap

EBS Sub-Ledgers

Receivables Payables Purchasing Inventory

Sub-Ledger Accounting Module

C re at e A cc o u n tin g GL Interface G L T ra n sfe r GL Journal Entries Jo u rn al Im p o rt GL Balances Jo u rn al P o st in g GL Posting Recap

 All accounting distributions are stored

in respective sub-ledgers

 Accounting is created in SLA

 Accounting entries are periodically

aggregated (based on business needs)

 Entries are periodically transferred to

GL Interface

 Journal Import creates the Journal

entries

 Journals are posted to GL Balances

Tables

 GL_INTERFACE – Interface Table

 GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES – Journals  GL_IMPORT_REFERENCES – Reference table  GL_BALANCES – GL Balances

(12)

12

GL Posting Process – Data Flow

(13)

(14)

14

Oracle BI Applications – Data Model

(15)

General Ledger Balance

Fact Table W_GL_BALANCE_F

 Stores the current balance for general

ledger accounts

 Posted records are considered for

calculating the balances

 Actual balance entries are stored

Granularity

 Code Combination, Ledger and Period

EBS Source Table

 GL_BALANCES

ETL

 This table gets populated by extracting

the Actual Balance entries from GL Balances

Key Dimension Tables

 W_LEDGER_D

 W_MCAL_DAY_D

(16)

16

GL Detail Transactions

Fact Table W_GL_OTHER_F

 Stores all Journal Entries from GL

 Stores LOC Amount and DOC

Amount

 OOTB Supports Actuals only

Granularity

 Data is stored at JE_LINE_NUM

Source Tables

 GL_JE_BATCHES,

GL_JE_HEADERS, GL_JE_LINES

ETL Logic

(17)

W_GL_LINKAGE_INFORMATION_G Explained

 Helper table

 Links GL Ledger Journals in W_GL_OTHER_F to SL

transactions

 No direct join between W_GL_OTHER_F and

W_GL_LINKAGE

 Linking is enabled for W_AP_XACT_F, W_AR_XACT_F,

W_GL_COGS_F or W_GL_REVN_F

 Sourced from GL_IMPORT_REFERENCES in EBS

(18)

18

Metadata Modeling

Model

Join

(19)

(20)

20

W_GL_ACCOUNT_D Explained

 GL Account String is also known as Code Combination

 Sourced from GL_CODE_COMBINATIONS_KFV

 Stores all Code combinations

 Primary key – Code Combination ID

 Works in conjunction with W_GL_SEGMENT_D &

W_HIERARCHY_D

 W_GL_SEGMENT_D – All values are stored

 W_HIERARCHY_D – All hierarchies are stored

(21)

Metadata Model

Fact to GL Account Link GL Account to Segment,

(22)

22

Custom Sub-Ledgers

(23)

Custom Sub-Ledgers

Following features are mandatory in Custom SL Data -

 All Sub-Ledger data should be stored on Oracle Database.

 Accounting String (Code Combination – Structure and Values of each

Segment) should be same across all Sub-Ledger Systems

 All Distributions should be aggregated at (but not limited to) –

 Date

 All Segments of COA

 Currency

 Amount

 Unique Identifier for each batch

 Other References to sub-ledger data

 Maintain record of batches (Unique Identifiers) interfaced

(24)

24

EBS Setups

(25)

EBS Setups

Following Setups are Mandatory in EBS GL -

 Custom GL Journal Source that includes all the References required

 Import References should be enabled for all the sources

(26)

26

Datawarehouse Extensions

(27)

Datawarehouse Extensions

Following are the key Extensions Required in DW –

 Design transaction tables to store the custom sub-ledger detailed

transactions

 All tables should be designed to store GL Journal references

Following are the key extensions required in OBIA DW –

 Extend W_GL_LINKAGE_INFORMATION_G table to support

additional import references setup in EBS

 Linkage

(W_GL_LINKAGE_INFORMATION_G.JOURNAL_LINE_INTEGRAT ION_ID) information should be resolved appropriately in Custom SL Tables

(28)

28

High Level Architecture of DW

Datawarehouse BI Apps Custom Sub-Ledger Data AR AP GL P&S Inventory HR EBS Custom Sub-ledgers O BIA E TL GL_INTERFACE Transfer to GL Cu sto m E TL /E LT