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

37  Download (0)

Full text

(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

 Single Database to store all

Reporting data

 Custom SL tables can be

persisted in OBIA Schema

 Single Execution plan/data

load process can be used to load data into DW

(29)

Data Sample

 W_GL_ACCOUNT_D –

All Code combinations

 W_GL_OTHER_F –

Stores all Journal Entries

 W_GL_LINKAGE_INFOR

MATION_G – Reference table

 Custom SL DW Fact

Table – Transaction table to store custom SL

(30)

30

OBIEE Modeling

(31)

OBIEE Modeling

Model the Repository using Oracle recommended Best Practices to support –

 Custom Sub-Ledger Tables

(32)

32

Reporting

(33)

Reporting

Key Reports required are -

 GL Balances report (P&L or Balance Sheet)

 GL Journal Details Report (Include Journal Integration ID)

 Custom Sub-Ledger Distributions Report (Include Journal

Integration ID)

 Enable navigation on Journal Integration ID in Journal Details

Report to Distributions report

(34)

34

Drilldown in BI Applications

Typical Drilldown can be achieved as shown below

 GL Balances to GL Journals

 GL Journals to SL

(35)

(36)

36

References

 Oracle BI Applications Documentation

(37)

Figure

Updating...

References

Related subjects :