Advertisements

Oracle EBS General Ledger data flow and data model

In just about any ERP financial accounting system, including Oracle EBS General Ledger, the General Ledger module is integrated with other modules, such as Accounts Payable and Accounts Receivable.  The General Ledger will collect and store financial activity information from the other modules, and will be the module that best reflects the overall financial condition of the company. Because of this, the General Ledger is typically heavily used for financial reporting from OBIEE, OBIA, or other tools.

This post aims to provide a quick overview of the data model of Oracle EBS General Ledger and the data flow within the module.

Oracle General Ledger is a part of the Oracle E-Business Suite Financials Application.  Many of the other Oracle Applications modules are integrated with Oracle General Ledger, and send transactions to the General Ledger in the form of journals.

For example, when a company receives an invoice, the transaction is entered into Oracle Payables; and then when the invoice is paid, another transaction is generated in Oracle Payables. There could be tens, hundreds or even thousands of these transactions per day.  This activity is recorded in the Oracle General Ledger in the form of journals.

Similarly, when a company makes a sale, and then when payment is received for that sale, those transactions are recorded in the Oracle Receivables module, and the transactions are also passed on to the Oracle General Ledger with the Transfer program.

Below is a diagram of Oracle General Ledger’s data flow and some of the key tables in the data model.

OracleGL_DataFlow

Oracle EBS Modules and External Systems populate the GL_INTERFACE table through the Transfer program and Load programs respectively.  The Journal Import Process takes those records and loads them into the GL_JE_BATCHES, GL_JE_HEADER and GL_JE_LINES tables. The Posting Process calculates and loads balances from those records into the GL_BALANCES table.

The tables covered here are some of the sources for the OBIA star schemas (fact and dimension tables) used in OBIA Financials, and in particular, the General Ledger dashboard and analyses.

GL_INTERFACE This table holds financial transactions   (journals) transferred from other Oracle Applications modules and external   systems.
GL_JE_BATCHES This table identifies a “batch” of   journals that are related and processed together. Each batch contains one or   more journals.
GL_JE_HEADER Each journal has one journal header   and one record this table
GL_JE_LINES Each journal has one or more journal   lines and are tied together by the journal header

Some other important supporting tables in the GL data model include:

GL_CODE_COMBINATIONS This is the Accounting Flexfield table   and it stores the chart of accounts values, and so the table contains the   valid GL account combinations allowed in the system, along with other   relevant information about the accounts.
GL_LEDGERS This table stores all the Ledgers and   Ledger Sets in the Oracle GL system
GL_PERIODS This   table stores information   about the accounting periods defined in the Oracle GL system. Each row contains   information such as, start date, end date of the period, the period type, the   fiscal year, and the period number.
FND_CURRIENCIES This table stores the list of valid   currencies to be used in Oracle Applications
Advertisements

Oracle E-Business Suite (EBS) Foundation Tables

This post is simply a list of Oracle E-Business Suite (EBS) Foundation tables. The Foundation tables contain data that relate to the entire suite of applications – they are not specific to any one module.

Some foundation tables are used in Oracle BI Applications (OBIA), for example, the FND_USER, FND_USER_RESP_GROUPS, and FND_RESPONSIBILITY_VL tables are used in security-related Initialization Blocks.

In general, it’s good to be familiar with these tables when working on OBIEE projects with EBS as a source system.

Foundation Table Purpose
FND_APPLICATION Stores applications registered with Oracle Application Object Library.
FND_APPLICATION_TL Stores translated information about all the applications registered with Oracle Application Object Library.
FND_APP_SERVERS This table will track the servers used by the E-Business Suite system.
FND_ATTACHED_DOCUMENTS Stores information relating a document to an application entity.
FND_CONCURRENT_PROCESSES Stores information about concurrent managers.
FND_CONCURRENT_PROCESSORS Stores information about immediate (subroutine) concurrent program libraries.
FND_CONCURRENT_PROGRAMS Stores information about concurrent programs. Each row includes a name and description of the concurrent program.
FND_CONCURRENT_PROGRAMS_TL Stores translated information about concurrent programs in each of the installed languages.
FND_CONCURRENT_QUEUES Stores information about concurrent managers.
FND_CONCURRENT_QUEUE_SIZE Stores information about the number of requests a concurrent manager can process at once, according to its work shift.
FND_CONCURRENT_REQUESTS Stores information about individual concurrent requests.
FND_CONCURRENT_REQUEST_CLASS Stores information about concurrent request types.
FND_CONC_REQ_OUTPUTS This table stores output files created by Concurrent Request.
FND_CURRENCIES Stores information about currencies.
FND_DATABASES It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.
FND_DATABASE_INSTANCES Stores instance specific information. Every database has one or more instance.
FND_DESCRIPTIVE_FLEXS Stores setup information about descriptive flexfields.
FND_DESCRIPTIVE_FLEXS_TL Stores translated setup information about descriptive flexfields.
FND_DOCUMENTS Stores language-independent information about a document.
FND_EXECUTABLES Stores information about concurrent program executables.
FND_FLEX_VALUES Stores valid values for key and descriptive flexfield segments.
FND_FLEX_VALUE_SETS Stores information about the value sets used by both key and descriptive flexfields.
FND_LANGUAGES Stores information regarding languages and dialects.
FND_MENUS It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.
FND_MENUS_TL Stores translated information about the menus in FND_MENUS.
FND_MENU_ENTRIES Stores information about individual entries in the menus in FND_MENUS.
FND_PROFILE_OPTIONS Stores information about user profile options.
FND_REQUEST_GROUPS Stores information about report security groups.
FND_REQUEST_SETS Stores information about report sets.
FND_RESPONSIBILITY Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.
FND_RESPONSIBILITY_TL Stores translated information about responsibilities.
FND_RESP_FUNCTIONS Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.
FND_SECURITY_GROUPS Stores information about security groups used to partition data in a Service Bureau architecture.
FND_SEQUENCES Stores information about the registered sequences in your applications.
FND_TABLES Stores information about the registered tables in your applications.
FND_TERRITORIES Stores information for countries, alternatively known as territories.
FND_USER Stores information about application users.
FND_VIEWS Stores information about the registered views in your applications.
FND_USER_RESPONSIBILITY  
FND_RESPONSIBILITY_VL  
FND_ORACLE_USERID  
FND_DATA_GROUP_UNITS  

Oracle E-Business Suite (EBS) List of Flexfields

This post is simply a table listing of the Key Flexfields (KFFs) in Oracle E-Business Suite (EBS) (aka Oracle Applications). The table contains the flexfields, along with their Code, Owning Application and base table. 

Flexfields are commonly used in OBIEE reporting for EBS source systems. Flexfields are also an integral part of the Oracle Business Analytics Warehouse (OBAW) data model. It is worthwhile to be familiar with them.

  Name Code Owning Application Table Name
1 Account Aliases MDSP Oracle Inventory MTL_GENERIC_DISPOSITIONS
2 Accounting Flexfield GL# Oracle General Ledger GL_CODE_COMBINATIONS
3 Activity Flexfield FEAC Enterprise Performance Foundation  
4 AHL Route AHLR Complex Maintenance, Repair, and Overhaul  
5 Asset Key Flexfield KEY# Oracle Assets FA_ASSET_KEYWORDS
6 Bank Details KeyFlexField BANK Oracle Payroll PAY_EXTERNAL_ACCOUNTS
7 CAGR Flexfield CAGR Oracle Human Resources  
8 Category Flexfield CAT# Oracle Assets FA_CATEGORIES
9 Competence Flexfield CMP Oracle Human Resources  
10 Cost Allocation Flexfield COST Oracle Payroll PAY_COST_ALLOCATION_KEYFLEX
11 Grade Flexfield GRD Oracle Human Resources PER_GRADE_DEFINITIONS
12 Item Catalogs MICG Oracle Inventory MTL_ITEM_CATALOG_GROUPS
13 Item Categories MCAT Oracle Inventory MTL_CATEGORIES
14 Job Flexfield JOB Oracle Human Resources PER_JOB_DEFINITIONS
15 Location Flexfield LOC# Oracle Assets FA_LOCATIONS
16 Oracle Service Item Flexfield SERV Oracle Inventory MTL_SYSTEM_ITEMS
17 People Group Flexfield GRP Oracle Payroll PAY_PEOPLE_GROUPS
18 Personal Analysis Flexfield PEA Oracle Human Resources PER_ANALYSIS_CRITERIA
19 Position Flexfield POS Oracle Human Resources PER_POSITION_DEFINITIONS
20 Public Sector Budgeting BPS Oracle Public Sector Budgeting  
21 Sales Orders MKTS Oracle Inventory SALES_ORDER_ID
22 Sales Tax Location Flexfield RLOC Oracle Receivables AR_LOCATION_COMBINATIONS
23 Soft Coded KeyFlexfield SCL Oracle Human Resources HR_SOFT_CODING_KEYFLEX
24 Stock Locators MTLL Oracle Inventory MTL_ITEM_LOCATIONS
25 System Items MSTK Oracle Inventory MTL_SYSTEM_ITEMS
26 Territory Flexfield CT# Oracle Receivables RA_TERRITORIES
27 Training Resources RES Oracle Learning Management  

Oracle Business Intelligence Applications (OBIA) 7.9.6.4 Released

Oracle has released OBIA 7.9.6.4, and it has some significant improvements.  Check out the summary below. You will need an Oracle ID to access the links (if you don’t have one, it’s a free sign up).

—————————————–

Oracle BI Applications 7.9.6.4, featuring further integration of two new BI Applications, is now available.

The products, which became first available in OBIA 7.9.6.3 Extension Pack release, now have significantly enhanced dashboards, reports and subject areas, and now have been completely integrated to the Oracle BI Applications including a unified data model and conformed RPD, DAC and INFA repositories.  In addition, they now also support an expanded BI Applications Certification Matrix.

Existing customers that have not yet deployed should consider 7.9.6.4 instead of the 7.9.6.3 Extension Pack Release.

  • Oracle Manufacturing Analytics, part of the Oracle BI Applications product family, helps discrete and process manufacturing organizations optimize their supply networks by integrating data from across the enterprise value chain, thereby enabling executives, operations managers, cost accountants and production supervisors to make informed and actionable decisions related to manufacturing execution.  The application includes 21 analytical subject areas that are leveraged to deliver 32 dashboard pages that consist of more than 200 pre-built reports.
  • Oracle Enterprise Asset Management Analytics, part of the Oracle BI Applications product family, offers complete and enhanced visibility to enterprise-wide maintenance information. Pre-built reports covering Maintenance History, Maintenance Cost Analysis and Maintenance Work Orders, provide Maintenance Managers information to maximize performance, identify potential issues much in advance, and address them before they escalate into serious problems. The application includes 11 analytical subject areas that are leveraged to deliver 26 dashboard pages that consist of more than 200 pre-built reports.

Highlights of the Oracle BI Applications 7.9.6.4 Release:

  • New Application – Oracle Manufacturing Analytics with pre-built adapters for EBS Process Manufacturing R12.x and EBS Discrete Manufacturing R12.x and 11.5.10
  • New Application – Oracle Enterprise Asset Management Analytics with pre-built adapters for EBS R12.x, EBS 11.5.10 and IBM Maximo 7.5
  • Universal Adapter to extend the capability to other source systems
  • Certified for OBIEE 11g
  • Certified for Exalytics
  • Certified for DAC 11g including support for Exalytics / Times Ten, Patching Framework, Dual ETL Support and many other performance enhancements
  • Native support for mobile and tablet devices
  • Localized in 28 languages
  • Supported on Oracle, SQL Server, DB2 and Teradata DB

Additional related information is available via:

  • Getting Started With Oracle Business Intelligence Applications 7.9.6.4 [Doc ID 1527634.1]
  • OBIA 7.9.6.4: Bugs Fixed in 7.9.6.4 (Doc ID 1528774.1)
  • Oracle Business Intelligence Applications ETL Data Lineage Guide Release 7.9.6.4 [Doc ID 1527475.1]

Oracle positioned as a leader in the Gartner Magic Quadrant for Business Intelligence & Analytics platforms

Gartner released its 2012 “Magic Quadrant for Business Intelligence and Analytics Platforms” report.  As you may expect, Oracle is in the leaders bracket, along with Microsoft, IBM, Tableau Software, SAS, QlikTech, MicroStrategy, Tibco Spotfire, Information Builders and SAP.

GartnerMagicQuadrant_for_BI_and_Analytics

Source: Gartner (February 2013)

Not surprisingly, the report mentions that, in 2012, Data Discovery became a mainstream BI feature, and that Real-time, Content and Predictive Analytics saw increased activity.

You can read all the details here in the … Gartner Magic Quadrant for Business Intelligence and Analytics Platforms report.

Oracle positioned as a leader in the Gartner Magic Quadrant for Data Integration tools

Oracle, along with Informatica, IBM, SAP and SAS, are positioned as leaders in the Gartner Magic Quadrant for Data Integration tools.

Other data integration vendors that made it into the Magic Quadrant, but not in the leaders category are Microsoft, Information Builders, Syncsort, Talend, Pervasive Software.

GartnerMagicQuadrant_for_DataIntegrationTools

Source: Gartner (October 2012)

When performing its analysis, Gartner considers these areas of data integration:

  • Data acquisition for business intelligence (BI) and data warehousing
  • Consolidation and delivery of master data in support of master data management (MDM)
  • Data migrations/conversions
  • Synchronization of data between operational applications
  • Interenterprise data sharing
  • Delivery of data services in an SOA context

And analyzes these features and functionality that the data integration tools should provide:

  • Connectivity/adapter capabilities (data source and target support)
  • Support for different modes of interaction with a range of data structure types
  • Data delivery capabilities
  • Support for the delivery of data across a range of latency requirements
  • Data transformation capabilities
  • Provide facilities for developing custom transformations and extending packaged transformations
  • Metadata and data modeling capabilities
  • Design and development environment capabilities
  • Data governance support capabilities (via interoperation with data quality, profiling and mining capabilities)
  • Deployment options and runtime platform capabilities
  • Operations and administration capabilities
  • Architecture and integration capabilities
  • Service enablement capabilities

Per Gartner, Oracle’s strengths are:

  • Breadth of functionality,
  • Usability of core functionality across use cases
  • Addressing data challenges across a range of application- and data-oriented customer bases

And their cautions are:

  • Enabling product migration,
  • Complexity of integrated deployment across products,
  • Pricing perception and availability of skills

As Oracle-centric developers and solution providers, we are most concerned with the capabilities and future of Oracle Data Integrator (ODI), Oracle Warehouse Builder (OWB), Golden Gate and, in some cases, Informatica (a part of most OBIA installations). These products are doing well in the market, but are a bit behind the install bases of Microsoft, SAP and IBM.  The largest install base is with Microsoft (12K customers), followed by SAP (10K) and IBM (9.4K).  Oracle has 3.5K and Informatica has 5K data integration customers.  With a major push now behind the ODI tool, you can expect to see growth in Oracle’s numbers in the coming years.

Apparently, Oracle also has plans for providing a migration path (a migration wizard) from Oracle Warehouse Builder (OWB) to ODI.  This will be a very welcomed tool for OWB users seeking to migrate to ODI.  And this will further solidify ODI as the data integration tool for Oracle-centric IT organizations, and start to create more availability of skilled ODI resources.

You can read all the details at Gartner Magic Quadrant for Data Integration Tools.

Oracle Financial Analytics Overview

What is Oracle Financial Analytics?
Oracle Financial Analytics is a module of Oracle Business Intelligence Applications, and falls into the grouping of ERP analytics modules. Other Analytics modules under the ERP umbrella include Human Resources, Procurement & Spend, Supply Chain & Order Management, Projects, Asset Management, and Manufacturing. And Analytics modules under the CRM umbrella include Sales, Marketing, Loyalty, Service, Contact Center, and Price.

Oracle Financial Analytics provides companies with deep insight into all aspects of financial control and management.  It provides a multitude of summary and detail views, and many metrics, of information relating to revenues, expenses, assets, liabilities and shareholder equity.  A full spectrum of reports and metrics covers GL, AR, AP, and more, providing key information required to determine the financial state of the company and what actions/decisions may be required.

Why use Oracle Financial Analytics?
In additon to the above benefits, Financial Analytics integrates with Oracle E-Business Suite (EBS), Peoplesoft, JD Edwards, and SAP, which allows companies to implement the technology, filled with years of research and best practices, quickly and with a LOT less risk.

What is Oracle Financial Analytics comprised of?
By listing some of the content of the BI module, without going into too much detail, you will be able to get a sense for the type of information and see the vast amount of content included.

————————
6 Dashboards
————————

General Ledger: provides information about the key areas of required for financial management including balance sheet, cash flow, liquidity, expenses, working capital and liquidity.

Payables: provides information about how effectively and efficiently the company is meeting its payment obligations.

Receivables: provides information about effectively and efficiently the company is collecting payment from sales.

Projects: provides financial information about projects.

Profitability: provides imformation for the key areas for profitability including margins by customer and product, profit and loss statements, revenues, and the various “Return-On metrics” such as ROA, ROI, ROE.

US Federal Financial Performance: provides information about budgets and payments

————————
36 dashboard pages
– broken out by dashboard below:
————————
General Ledger: Overview, Balance Sheet, Cash Flow, Budget vs Actual, Asset Usage, Liquidity, Financial Structure, GL Balance, Trial Balance

Payables: Overview, AP Balance, Payments Due, Effectiveness, Payment Performance, Supplier Report, Invoice Details, All AP Transactions

Receivables: Overview, AR Balance, Payments Due, Effectiveness, Payment Performance, Customer Report, Invoice Details, All AR Transactions

Projects: Project Invoices

Profitability: Overview, P&L, Margins, Revenue, Products, Customers

US Federal Financial Performance: Budget Summary, Budget Details, Budget Spending, Payables

————————
255 Reports – A small sampling of the reports include:
————————
Cash Flow Summary
Monthly & Quarterly Cash Flow
Net Working Capital Summary
Operating Cycle and Cash Cycle
Monthly & Quarterly Balance Sheet
Budget vs Actual – Current & YTD Operating Expenses
Top 10 Categories by Expense Variance -and- Top 10 Cost Centers by Expense Variance
Payables Turnover -and- Receivables Turnover
Cash Ratio
Days Cash in Hand
Quick Ratio -and- Current Ratio
Debt Coverage
Debt to Asset -and- Debt to Equity
GL Account Balance -and- Trial Balance

————————
488 Metrics – A small sampling of the metrics include:
————————
Receivables
AR Balance: • DSO • Closing Group Amt • Credit Limit Used % • Total AR Overdue Amt
AR Aging: • AR Aging 1-30 Amt • AR Due 1-30 Amt • AR Overdue 1-30 Amt
Payment Performance: • AR Payment Days • AR Weighted Days • Times Paid Before Due
AR Transactions: • AR Avg Invoice Amt • AR Credit Memo Amt

Payables
AP Balance: • DPO • Closing Group Amt • Total AP Overdue Amt • Overdue Amt to Total %
AP Aging: • AP Aging 1-30 Amt • AP Due 1-30 Amt • AP Overdue 1-30 Amt
Payment Performance: • AP Payment Days • AP Weighted Days • Times Paid Before Due
AP Transactions: • AP Avg Invoice Amt • AP Avg Payment Amt

General Ledger
Balance Sheet: • Cash • Accounts Receivable • Debt to Equity Ratio • Current Ratio
Asset Turnover: • AR Turnover • AP Turnover • Inventory Turnover • Cash Cycle • Fixed Assets Turnover
Cash Flow: • Operating Cash Flow • Investing Cash Flow • Financing Cash Flow • Net Cash Flow

Profitability
Profitability Returns: • Return on Equity • Return on Assets • Return on Capital
Margins: • Gross Margin % • Operating Margin % • EBT Margin % • Net Income Margin %
Product Profitability: • Revenue • Product Gross Profit • Product Operating Profit
Customer Profitability: • Revenue • Customer Gross Margin

Who will use and benefit from Financial Analytics?
CFOs, Controllers, Accounting Managers, Finance Analysts, Department Heads, Payables and Receivables Managers, Payables & Receivables Analysts, Project Managers, Project Accountants, and much more.

Where can I find more information?
More information about Oracle Financial Analytics can be found here:
http://www.oracle.com/us/solutions/business-analytics/analytic-applications/business-role/financial-analytics/overview/index.html
And more information about Oracle Business Intelligence in general can be found here:
http://www.oracle.com/us/solutions/business-analytics/overview/index.html