Oracle R12 Changes Tech And Functional

Migrating Custom 11i Reports to Release 12 Paul Winterstein [email protected] Director, Product Management Noetix Corporation

What To Expect

• • • •

Emphasis on “porting” custom 11i queries Focus on Oracle Financials subject areas More technical than functional What we see companies doing about reporting

About Noetix

• BI solutions experts since 1994 • Offices in North America, Europe and South Asia • Certified with all leading BI Platforms • 2007 Product Awards: KMWorld, eWEEK, DMReview • 1400+ customers globally

PARTNERSHIPS

Industry Recognition

•

100 Companies that Matter in Knowledge Management, KMWorld

•

Codie Award for Best Business Intelligence or Knowledge Management Solution, SIIA

•

Top 100 Companies in the Business Intelligence Industry, DM Review

•

Trend Setting Products 2007, KMWorld

•

Finalist, Excellence Award – Analytics and Reporting, eWeek

•

Top 100 Private Companies Award, Red Herring

•

Washington Technology Fast 50, Deloitte and Touche

•

Washington’s Best 100 Companies to Work For, Washington CEO Magazine

AWARDS

Oracle Reporting Experts

Oracle E-Business Suite Siebel CRM PeopleSoft

NoetixViews NoetixAnswers

Noetix Platform Oracle Database Oracle Discoverer Cognos BI Business Objects Oracle Business Intelligence EE

Everything Starts with a Query

• ETL routines – To replicate the data, to transform it into a

different model, and usually to create stored aggregations

• Reporting tool’s repository – Discoverer EUL, OBI EE Repository, BO Universe, Cognos Framework Mgr. Model, …

• Database views – Virtual transformation of data into a denormalized –

model without replicating the data For direct access

• The information in this presentation is applicable to all approaches

Reporting Tool Repositories

Discoverer / BI Suite EE End User Layer/ Repository

BI Suite

Cognos 8 BI

Topics

Models / Pkgs

Crystal Reports Web Intelligence Business Views Universe

E-Business Suite OLTP Tables

Master File Metadata

Common Data Access Layer

Discoverer / BI Suite EE End User Layer/ Repository

BI Suite

Cognos 8 BI

Topics

Models / Pkgs

Crystal Reports Web Intelligence Business Views Universe

Improved maintainability Improved “time to query” Common business terminology Reuse complex SQL logic

Database Views EBS Tables

Master File Metadata

Subject Areas  Funds Disbursement via new Payments application  Suppliers & Trading Community Architecture  Banks & Trading Community Architecture  Subledger Accounting Also…

 Collections & customer correspondence via Collections application

 Tax Calculations via the new E-Business Tax appl.  XML Publisher Impact of Release 12 to your 11i queries: Very little  complete rewrite

Funds Disbursement

Funds Disbursement

• Moved from Payables to new Payment module

– – –

Creation & validation of payments Aggregation of payments into files Format & transmission of files

• Selection & approval of invoices remains in Payables

Funds Disbursement Changes Co-existing Tables 11i

Release 12

• Invoice payments

AP_INVOICES_ALL AP_PAYMENT_SCHEDULES_ALL

IBY_DOCS_PAYABLE_ALL

• Payments

AP_CHECKS_ALL

IBY_PAYMENTS_ALL

AP_INV_SELECTION_CRITERIA_ALL

IBY_DOCS_PAYABLE_ALL IBY_PAY_SERVICE_REQUESTS IBY_PAY_INSTRUCTIONS_ALL

• Banks

AP_BANK_ACCOUNT_USES_ALL

CE_BANK_ACCT_USES_ALL

• Payment documents

AP_CHECK_STOCKS_ALL

CE_PAYMENT_DOCUMENTS

AP_BANK_ACCOUNTS_ALL

IBY_EXTERNAL_PAYEES_ALL IBY_EXT_BANK_ACCOUNTS IBY_PMT_INSTR_USES_ALL

• Instructions, batches Obsolete Tables

• Supplier & customer bank accounts

Suppliers & Trading Community Architecture

Suppliers & TCA

• Supplier, supplier site, & supplier contact information migrated to TCA tables

• Three new AP tables containing supplierunique data, with links to TCA tables AP_SUPPLIERS AP_SUPPLIER_SITES_ALL AP_SUPPLIER_CONTACTS

• Three old PO Vendors tables obsolete – Views provided for backward compatibility

Supplier Changes Co-existing Tables Release 12 Payables

Release 12 TCA

• Suppliers

AP_SUPPLIERS

HZ_PARTIES

• Supplier sites

AP_SUPPLIER_SITES_ALL

HZ_PARTY_SITES HZ_LOCATIONS

AP_SUPPLIER_CONTACTS

HZ_PARTIES HZ_PARTY_SITES HZ_RELATIONSHIPS HZ_ORG_CONTACTS

11i

Release 12

• Suppliers

PO_VENDORS

AP_SUPPLIERS

• Supplier sites

PO_VENDOR_SITES_ALL

AP_SUPPLIER_SITES_ALL

• Supplier contacts

PO_VENDOR_CONTACTS

AP_SUPPLIER_CONTACTS

• Supplier contacts Obsolete Tables

Oracle’s Supplier / TCA Model

Supplier Contacts

• Single occurrence of each supplier contact

• Supplier contacts

now shared across operating units

Banks & Trading Community Architecture

Banks Data in R12

• Setup in Cash Management (CE) – Bank, bank account setup – Shared with Payables, Receivables, Treasury, & Payroll

• Banks & bank branches now represented as TCA parties

–

11i AP bank data moved to TCA HZ_PARTIES table

• Three key CE tables – CE_BANK_ACCOUNTS for bank accounts – CE_BANK_ACCT_USES_ALL for account uses by –

Operating Units & Legal Entities CE_GL_ACCOUNTS_CCID for bank account use accounting data

Bank Data in TCA • Bank, bank branch, account attributes, contact persons

Release 12 TCA HZ_PARTIES

• Bank sites & locations

HZ_PARTY_SITES HZ_LOCATIONS

• Change history & additional

HZ_ORGANIZATION_PROFILES

• Contact details & methods

HZ_CONTACT_POINTS

• Contact titles

HZ_ORG_CONTACT

• Contact purpose or role

HZ_ORG_CONTACT_ROLES

attributes

Subledger Accounting

Subledger Accounting

• All accounting performed before transfer to the GL

• User-definable accounting rules • At the data level, it’s a big change for all the

subledgers, though there is a first generation in 11i Payables “Accounting Events”

Oracle’s Slide on SLA

1 of 3

Oracle’s Slide on SLA

2 of 3

Oracle’s Slide on SLA

3 of 3

Receivables Accounting in 11i

• Final accounting data not generated prior to transfer to GL

–

•

Distribution level information  GL Three distinct distributions tables – Customer transactions • Invoices / Credit Memos / Debit Memos / … • Accounting class & amounts, but not debits & credits – Receipts & Adjustments • Unapplied, applied • Both debits & credits – Misc. Cash Receipts • Both debits & credits

• “View Accounting” is a report against distributions

11i AR Database Receivables GL ra_cust_trx_line_gl_dist_all

ar_distributions

Transfer to GL

gl_interfaces

misc_cash_distributions

Cross-functional Views

•Adjustment distributions •Customer transaction distributions

•Miscellaneous receipt distributions •Standard receipt distributions

Payables Accounting in 11i

• Accounting data generated and stored in

“Accounting Events” tables prior to transfer to GL

– –

Run “Create Accounting” to populate accounting events tables Accounting event tables  GL

11i AP Database Payables

GL ae_events

invoices Create Accounting invoice_distributions

ae_headers

Transfer to GL

gl_interfaces

ae_lines

Cross-functional Views

•Invoice distributions

•Payment distributions

Subledger to Ledger Reporting in 11i

• Complete, final accounting only available in the GL

– – –

All debits and credits All journal entries All balances

• Challenge: Difficult to link summarized accounting data with source details

Release 12 Subledger Accounting

• All subledger accounting data generated and stored in shared SLA tables prior to transfer to GL

–

Run “Create Accounting” to populate SLA tables

• User can “View Accounting” only after “Create Accounting” is run

The Create Accounting process Tools  Create Accounting

From Distributions to SLA

Distributions

Create Accounting

SLA Events, Headers, & Lines

• Create Accounting process – Applies accounting rules – Loads SLA tables, GL tables – Creates detailed data per accounting rules, stores in SLA “distribution links” table

SLA Distributions Links

Distributions

SLA Distribution Links

SLA Events, Headers, & Lines

• SLA Distribution Links Table – Holds distribution details…finest granularity of – –

accounting data Must join through to get true Distribution  SLA journals matches Multiple distributions may be aggregated into a few SLA journal lines

R12 Accounting Structures

SLA

Subledgers

GL xla_events

Various Subledger Distributions Tables

xla_headers Create Accounting

Transfer to GL xla_lines

xla_distribution_links

gl_interfaces

Distribution Links Sample Data Distributions Trx #

Line Type

Line #

Ext Amt

Currency

CCID

Acct. Class

SLA Je Lines

Amt

11984

Line

1

100

CAD

12871

Rev

100

Trx #

11984

Freight

1

20

CAD

13053

Freight

20

11984

13053

Receivable

158.2

123.93

11984

Freight

1

5

CAD

13053

Freight

5

11984

12833

Rounding

0

0.01

11984

Tax

1

3

CAD

18170

Tax

3

11984

12871

Revenue

120

94.01

11984

Tax

1

0.6

CAD

18170

Tax

0.6

11984

24718

Freight

25

19.59

11984

Line

2

20

CAD

12871

Rev

20

11984

18170

Tax

13.2

10.34

11984

Tax

2

2

CAD

18170

Tax

2

11984

Tax

2

0.4

CAD

18170

Tax

0.4

11984

Tax

3

6

CAD

18170

Tax

6

11984

Tax

3

1.2

CAD

18170

Tax

1.2

11984

CAD

12833

Rec

158.2

11984

CAD

24718

Round

0

SLA Entered DR

SLA Entered CR

CCID

Acct. Class

Entered DR

Entered CR

Acct. DR

Distribution Links

Trx #

CCID

Acct. Class

Curr

11984

13053

Freight

CAD

25

19.59

5

3.92

11984

13053

Freight

CAD

25

19.59

20

15.67

11984

12833

Receivable

CAD

11984

12871

Revenue

CAD

120

94.01

20

15.67

11984

12871

Revenue

CAD

120

94.01

120

78.34

11984

24718

Rounding

CAD

11984

18170

Tax

CAD

13.2

10.34

1.2

0.94

11984

18170

Tax

CAD

13.2

10.34

.04

0.31

11984

18170

Tax

CAD

13.2

10.34

0.6

0.47

11984

18170

Tax

CAD

13.2

10.34

6

4.7

11984

18170

Tax

CAD

13.2

10.34

2

1.57

11984

18170

Tax

CAD

13.2

10.34

3

2.35

158.2

SLA Acct. DR

SLA Acct. CR

123.93

0

Acct. CR

Unrounded Entered DR

Unrounded Entered CR

158.2

0.01

Unrounded Acct. DR

Unrounded Acct. DR

123.93

0

0.01

View Accounting report Tools  View Accounting

Tie It All Together

• Data flows from distributions, thru

distribution links and SLA tables, to GL

• Three key stages – Distributions (before accounting applied) – SLA (in draft or final form) – GL • Three key views of the data – Distributions only – Tie distributions to SLA to GL – Tie GL back to distributions

Noetix Support for Oracle SLA Payables & Receivables

Accounting Subject Area

GL  Subledger Cross-Functional View (Final accounting data only)

Subledger  GL Cross-Functional View (Final and draft accounting data)

Payables invoices

GL_Je_SLA_AP_Inv_Dist

AP_Inv_Dist_SLA_GL_Je

Payables invoice payments

GL_Je_SLA_AP_Pmt_Dist

AP_Pmt_Dist_SLA_GL_Je

Receivables adjustments

GL_Je_SLA_AR_Adj_Dist

AR_Adj_Dist_SLA_GL_Je

Receivables miscellaneous receipts

GL_Je_SLA_AR_Misc_Rcpt_Dis t

AR_Misc_Rcpt_Dist_SLA_GL_Je

Receivables standard receipts

GL_Je_SLA_AR_Std_Rcpt_Dist

AR_Std_Rcpt_SLA_GL_Je

Receivables transactions (all)

GL_Je_SLA_AR_Tran_Dist

AR_Tran_Dist_SLA_GL_Je

The Impact?

• Some 11i queries will easily migrate to Rel. 12 – Some with no changes at all – Some will need table name changes • Some 11i queries will need moderate attention

–

Data moved to TCA will require query rewrites, but the basic reports can remain the same

• Some 11i queries won’t migrate 1:1 to Rel. 12 – SLA may require new reports, queries & ETL routines

Report Strategies • Best practice: Establish a common data access layer between queries and OLTP tables

– – –

Use common business terms for query objects, data elements, and the descriptions of both Persist this “business metadata” between database upgrades Remap the “technical metadata” (SQL)

• OLTP database views – Common data access layer – Usable by reporting & query tools, & ETL • Reporting tools’ metadata repositories – Map to data access layer in the OLTP database

Thank You!

Paul Winterstein [email protected] Director, Product Management Noetix Corporation