Leveraging NoetixViews to Migrate Custom 11i Reports to Release 12

1
Leveraging NoetixViews to Migrate Custom 11i
Reports to Release 12

• Joe Dahl, Solution Consultant

2
Agenda

• The Challenge
• Applications Data Structure Changes 11i -gt R12
• The Solution
• NoetixViews
• What we see companies doing about reporting

3
About Noetix

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

PARTNERSHIPS
4
Noetixs History of Dedication to Oracle
Applications Reporting
Noetix Analytics
Noetix Generator for Cognos
NoetixViews for PeopleSoft
Noetix EUL Generator
Noetix incorporated
Noetix for Siebel
1995
1996
1998
1999
2000
2001
2003
2004
2005
2006
2007
2002
1997
2009
2008
1994
Noetix Platform
Noetix Generator for BusinessObjects
Noetix Dashboard
NoetixViews
Noetix Generatorfor Oracle BI
5
Noetix Business Intelligence
6
Noetix Supports the Full Spectrum of Reporting
Needs
7
Operational Reporting
8
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 tools repository
• Discoverer EUL, OBI EE Server, 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

9
Reporting Tool Repositories
NoetixWebQuery Noetix Dashboard
Cognos8 BI
OracleDiscoverer OracleBI Suite EE
Business Objects Web Intelligence
ETL
Virtualtables
Business areasBusiness models
Models /Packages
Universe
Other data models
Oracle E-Business Suite
EBS Tables
10
Common Data Access Layer
NoetixWebQuery Noetix Dashboard
OracleDiscoverer OracleBI Suite EE
Cognos8 BI
Business Objects Web Intelligence
ETL
Oracle E-Business Suite
DatabaseViews

• Common business terminology

• Improved maintainability

EBS Tables

• Reuse complex SQL logic

• Improved time to query

11
Subject Areas

• Funds Disbursement
• Suppliers Trading Community Architecture
• Banks Trading Community Architecture
• Subledger accounting

Impact of Release 12 to your 11i queries Very
little ?? complete rewrite
12
Funds Disbursement
13
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

14
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
Instructions, batches AP_INV_SELECTION_CRITERIA_ALL IBY_DOCS_PAYABLE_ALL IBY_PAY_SERVICE_REQUESTS IBY_PAY_INSTRUCTIONS_ALL
Obsolete Tables 11i Release 12
Banks AP_BANK_ACCOUNT_USES_ALL CE_BANK_ACCT_USES_ALL
Payment documents AP_CHECK_STOCKS_ALL CE_PAYMENT_DOCUMENTS
Supplier and customer bank accounts AP_BANK_ACCOUNTS_ALL IBY_EXTERNAL_PAYEES_ALL IBY_EXT_BANK_ACCOUNTS IBY_PMT_INSTR_USES_ALL
15
Suppliers Trading Community Architecture
16
Suppliers TCA

• Supplier, supplier site, contact information
  migrated to TCA tables
• Three new AP tables containing supplier-unique
  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

17
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
Supplier Contacts AP_SUPPLIER_CONTACTS HZ_PARTIES HZ_PARTY_SITES HZ_RELATIONSHIPS HZ_ORG_CONTACTS
11i Obsolete Tables 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
18
Banks Trading Community Architecture
19
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

20
Bank Data in TCA
Release 12 TCA
Bank, bank branch, account attributes, contact persons HZ_PARTIES
Bank sites and locations HZ_PARTY_SITES HZ_LOCATIONS
Change history and additional attributes HZ_ORGANIZATION_PROFILES
Contact details and methods HZ_CONTACT_POINTS
Contact titles HZ_ORG_CONTACT
Contact purpose or role HZ_ORG_CONTACT_ROLES
21
Subledger Accounting
22
Subledger Accounting

• All accounting performed before transfer to the
  GL
• User-definable accounting rules
• At the data level, its a big change for all the
  subledgers, though there is a first generation in
  11i Payables Accounting Events

23
Receivables Accounting in 11i

• Final accounting data not generated prior to
  transfer to GL
• Distribution level information ?? GL
• Three distinct distributions tables
• 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

24
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

25
Subledger to Ledger Reportingin 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

26
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

27
Distribution Links Sample Data
Distributions
SLA Je Lines
Trx Line Type Line Ext Amt Currency CCID Acct. Class Amt
11984 Line 1 100 CAD 12871 Rev 100
11984 Freight 1 20 CAD 13053 Freight 20
11984 Freight 1 5 CAD 13053 Freight 5
11984 Tax 1 3 CAD 18170 Tax 3
11984 Tax 1 0.6 CAD 18170 Tax 0.6
11984 Line 2 20 CAD 12871 Rev 20
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
Trx CCID Acct. Class Entered DR Entered CR Acct. DR Acct. CR
11984 13053 Receivable 158.2 123.93
11984 12833 Rounding 0 0.01
11984 12871 Revenue 120 94.01
11984 24718 Freight 25 19.59
11984 18170 Tax 13.2 10.34
Distribution Links
Trx CCID Acct. Class Curr SLA Entered DR SLA Entered CR SLA Acct. DR SLA Acct. CR Unrounded Entered DR Unrounded Entered CR Unrounded Acct. DR Unrounded Acct. DR
11984 13053 Freight CAD 25 19.59 5 3.92
11984 13053 Freight CAD 25 19.59 20 15.67
11984 12833 Receivable CAD 158.2 123.93 158.2 123.93
11984 12871 Revenue CAD 120 94.01 20 15.67
11984 12871 Revenue CAD 120 94.01 120 78.34
11984 24718 Rounding CAD 0 0.01 0 0.01
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
28
Tie It All Together

• Data flows from distributions, thru distribution
  links and SLA tables, to GL
• Three key stages
• Distributions (before accounting applied)
• SLA (before transfer to GL)
• GL
• Three key views of the data
• Distributions only
• Tie distributions to SLA to GL
• Tie GL back to distributions

29
The Noetix Solution
30
The Information Gap
Query, Reporting and Analysis Tools
Generic Templates
Additional costs Additional resources Additional
delay
70 of BI implementation costs delay
M a n u a l M a p p i n g
M a p p i n g R e w o r k
lt The Information Gap gt
Custom Application Configuration
Enterprise Application (v1)
Enterprise Application (v2)
31
The Information Gap
Query, Reporting and Analysis Tools
Generic Templates
Generates configuration specific BI content
Noetix MetaBuilder
lt The Information Gap gt
Custom Application Configuration
No additional rework costs or delay
Enterprise Application (v1)
Enterprise Application (v2)
32
Information About Your Setup
33
What is a Noetix view?
Oracle database tables
Flexfield column in the table
Oracle EBS upgrade
doesnt break
remaps view
ATTRIBUTE1 Customer Status
Flexfield column in the view
Cust. Status
34
Oracle Flexfields and Noetix
Key FlexfieldBase Table Column Names
Key FlexfieldView Column Names
SEGMENT1
Company
SEGMENT2
Division
SEGMENT3
Cost Center
Descriptive FlexfieldBase Table Column Names
Descriptive FlexfieldView Column Names
ATTRIBUTE1
Web Site
ATTRIBUTE2
Preferred Vendor
ATTRIBUTE3
Approved Until
35
Product DemonstrationNoetixViews
36
NoetixViews 5.8.7Financial Cross-Functional Views
NoetixViews for Oracle General Ledger
Accounting Subject Area GL-to-Subledger Cross-Functional View Legacy GL-to-Subledger Cross-Functional View Built in R12?
Payables invoices GL_Je_SLA_AP_Inv_Dist GL_AP_Inv_Je_Line_Details GL_Je_AP_Inv_Dist_Details Yes
Payables invoice payments GL_Je_SLA_AP_Pmt_Dist GL_Je_AP_Pmt_Dist_Details Yes
Receivables adjustments GL_Je_SLA_AR_Adj_Dist GL_Je_Tran_AR_Adj_Dist Yes
Receivables miscellaneous receipts GL_Je_SLA_AR_Misc_Rcpt_Dist GL_Je_Tran_AR_Misc_Dist Yes
Receivables standard receipts GL_Je_SLA_AR_Std_Rcpt_Dist GL_Je_Tran_AR_Pymt_Dist Yes
Receivables transactions GL_Je_SLA_AR_Tran_Dist GL_Je_Tran_AR_Tran_Dist Yes
Purchase order and requisition encumbrances GL_Je_SLA_PO_All_Encumb GL_Je_Tran_All_PO_Encumb Yes
Blanket purchase agreements GL_Je_SLA_PO_Blnkt_Encumb GL_Je_Blanket_PO_Encumb Yes
Purchase order encumbrances GL_Je_SLA_PO_Encumb GL_Je_Tran_PO_Encumb Yes
Requisition encumbrances GL_Je_SLA_PO_Req_Encumb GL_Je_Tran_PO_Req_Encumb Yes
Payables
Receivables
Public Sector
37
NoetixViews 5.8.7Financial Cross-Functional Views
NoetixViews for Oracle Assets
Accounting Subject Area FA ? GL Cross-Functional View Legacy FA ? GL Cross-Functional View Built In R12?
Adjustments FA_Adjustments_SLA_GL_Je FA_GL_Journal_Adjustments Yes
Depreciations FA_Deprn_Dtls_SLA_GL_Je FA_Depreciation_Details (value-added) Yes
Depreciation histories FA_Deprn_Dtl_Hist_SLA_GL_Je FA_Depr_Detail_History (basic) Yes
Invoice transactions FA_Invoice_Trx_SLA_GL_Je FA_GL_Invoice_Trans_Je Yes
Transactions FA_All_Trans_SLA_GL_Je FA_GL_Journals Yes
NoetixViews for Oracle Payables
Accounting Subject Area AP ? GL Cross-Functional View Legacy AP ? GL Cross-Functional View Built in R12?
Invoices AP_Inv_Dist_SLA_GL_Je AP_Inv_GL_Tr_Details Yes
Payments AP_Pmt_Dist_SLA_GL_Je AP_Pmt_Dist_GL_Tr_Details Yes
Trial balance AP_Trial_Balance_Details AP_Trial_Balance (value-added) Yes
NoetixViews for Oracle Receivables
Accounting Subject Area AR ? GL Cross-Functional View Legacy AR ? GL Cross-Functional View Built in R12?
Adjustments AR_Adj_Dist_SLA_GL_Je AR_Adjustment_GL_Je_Dist Yes
Miscellaneous receipts AR_Misc_Rcpt_Dist_SLA_GL_Je AR_Misc_Pymt_GL_Je_Dist Yes
Standard receipts AR_Std_Rcpt_Dist_SLA_GL_Je AR_Cust_Pymt_GL_Je_Dist Yes
Transactions AR_Tran_Dist_SLA_GL_Je AR_Cust_Tran_GL_Je_Dist Yes
38
Noetix Support for Oracle SLAPayables
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_Dist 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
Payables invoices GL_Je_SLA_AP_Inv_Dist AP_Inv_Dist_SLA_GL_Je
39
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 re-writes,
  but the basic reports can remain the same
• Some 11i queries wont migrate 11 to Rel. 12
• SLA may require new reports, queries ETL
  routines

40
Reporting Strategies We See

• Best practice Establish a metadata layer between
  queries and OLTP tables
• Use common business terms for query objects, data
  element, their descriptions
• 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

41
Noetix Business Intelligence
42
Q A