Dashboards, Reporting & Business Intelligence

A great story…in few words, inspiring too!

Larry Ellison’s message

”

Twenty years ago we went public, with 2,100,000 shares of Oracle stock
issued on the NASDAQ. Our share price opened at $15 and closed at
$20.75. If we take into account the ten stock splits we’ve had in the
past twenty years, the adjusted opening price was $.05. We’re proud to
be listed on the NASDAQ and to be part of the NASDAQ-100.

Congratulations and thank you for your hard work, dedication, and focus.
“

The client where I am working have 3 set of books in use. They have 2 calendars, primary currency is USD but one of the SOB uses some foreign currency ‘xyz’

So here is the equation

US SOB : Calendar : US Calendar, Currency: USD

EMEA SOB : Calendar : US Calendar, Currency : XYZ

APAC SOB : Calendar : APAC Calendar, currency : USD

APAC and US Calendars have the same period_names and have the same period date range definitions so far (**).

When it comes to bringing actuals data into EPF and EPB here is the typical process

• Bring calendar definitions for each calendar in GL
• Define Balance Rules for each SOB
• Run  Balanace Rules for the desired Periods
• Create a Business process for Actuals (load and solve)
• Create a report and you are done!

The problem with this approach is when you try to view the report in EPB, you cannot see the consolidated
balances in one shot. for e.g to view the APAC balances, you need to edit the report and include the APAC calendar
hierarchy and if you need to see the US balances, then you to include the US Calendar hierarchy.

The other problem was with EMEA balances, even though we were bringing translated balances ( from ‘XYZ’ to USD) into
Enterprise Performance Foundation (EPF), EPB requires to complete the multi currency setup and the Business Area
needs to be ‘Currency enabled’ to bring the translated actuals from EMEA SOB.

Needless to mention, the user was not happy

Knowing that the client did not have any consolidated SOB in GL, I asked the possibility of having a
consolidated SOB created in GL and then just bring this SOB data into EPB. But the user rejected it saying
its not feasible in near future. I understood this, so now I again command my idea genie for a better solution

Doing some tests and briefing up the API documents my idea genie presented me the following solution.

Bring all the actuals from different SOBs into one SOB e.g ‘US SOB’
Bring balances for ‘USD’ and only the translated ‘USD’ balances for EMEA
Treat the balances as if they are coming from one calendar i.e. ‘US Calendar’
All balances are brought in under one common ‘US Ledger’

The user liked the approach but I had to alert him of the potential drawbacks

You cannot disect and view balances by any specific SOB or ledger in EPB. This also meant that the APAC and US Calendars should have exactly the same naming conventions for periods.

For e.g you cannot have the October 2006 period in US Calendar to be named as ‘OCT-06′ and the APAC calendar to be named as ‘OCT-2006′
(if you have this situation where the names are different, you can always create a workaround for this, ask my idea genie, how? )

The user was fine with the drawbacks as their cost centers belong to single company segment and hence they have a mechanism to see balances for each set of books although indirectly.

So I began prototyping the idea.

• created a package procedure which would extract the balances from gl_balances
• Consolidated the balances and dumped them into the fem_bal_interface_t table
• Called the ‘External FEM Balance load’ program from within this procedure to automatcally load the balances in fem_balances table.
• Create an actuals business process and ran it
• Yahooooooo! My balances are showing in EPB.
• I do some validations at each point GL_Balances to FEM_BAL_interface_t, GL_balances to FEM_balances and finally EPB balances to GL_balances
• I ask the user to do his validations.
• So far the solution works great and is more user friendly then the seeded Balance rule programs

Benefits of this custom approach

• Consolidated Actuals data
• Ease of bringing monthly and historical data
• No need to run different rules for each ledger

Further enhancements that can be done is to call the Actuals business process from within this procedure so if you need to bring in new actuals data for the recently closed business process, all you have to do is just submit this one program. Things couldn’t be much simpler, isn’t it?

Let me know if you find any flaws with the approach or have any suggestions.

A new exciting patch was released recently.  ZPB.B Rollup Patch F1 provides some eagerly anticipated functionality and some critical bug fixes.  In this patch EPB development have integrated a version of the FCH Excel Add-in.  This Add-in allows customers who use the OGL link to bring GL data into EPB, to create or open dynamic reports in Excel and drill down to GL transactional data.

5493249 Oracle Enterprise Planning and Budgeting Rollup Patch F1

There is more information on the Excel Add-in here, and as always the EPB About Document contains all the information you need on patch levels, certification, documentation here

Oracle Applications provides users with the ability to conduct business
electronically between trading partners based on Electronic Commerce standards
and methodology. One form of Electronic Commerce is Electronic Data Interchange
(EDI). EDI is an electronic exchange of data between trading partners. Interface data
files are exchanged in a standard format to minimize manual effort, speed data
processing, and ensure accuracy. The Oracle e-Commerce Gateway (formerly
known as Oracle EDI Gateway) performs the following functions:

• Define trading partner groups and trading partner locations.

• Enable transactions for trading partners.

• Provide general code conversion between trading partner codes or standard codes and the codes defined in Oracle Applications.

• Define interface data files so that application data can integrate with your trading partner’s application or an EDI translator.

For inbound transactions, import data into application open interface tables so
that application program interfaces (API) can validate and update Oracle
application tables.
For outbound transactions, extract, format, and write application data to
interface data files.
Oracle e-Commerce Gateway augments the existing standard paper document
capabilities of Oracle Applications, or adds functionality where no corresponding
paper documents exist.

As an option to the mandatory perpetual costing system, which uses
either the standard or average costing methods, Cost Management
provides support for two methods of Periodic Costing:
• Periodic Average Costing (PAC)
• Periodic Incremental LIFO (Last–In First–Out)
Periodic Costing is an option that enables you to value inventory on a
periodic basis. There are three principal objectives of Periodic Costing:
• To capture actual acquisition costs based on supplier invoiced
amounts plus other direct procurement charges required by
national legislation or company policy
• To capture actual transaction costs using fully absorbed resource
and overhead rates
• To average inventory costs over a prescribed period, rather than
on a transactional basis
You must set up a perpetual costing method (standard or average) for
each inventory organization that you establish in Oracle Inventory. In
addition, you have the option to use Periodic Costing.
You can choose to create Periodic Costing distributions and send them
to the General ledger after a Periodic Costing run. You can also disable
the perpetual costing General Ledger transfer, electing instead to
produce accounting entries only after a Periodic Costing run.
Oracle maintains the perpetual system and the periodic system (if one
is enabled) separately and produces separate reports.

Fiscal Calendar is an interesting concept. Usually it is geared towards companies who have financial obligations like reporting to government regulatories etc.

In a Fiscal Calendar, the first month of the year is usually not January (which is the usual first month of a normal calendar). Companies decide when they want to start a year. For e.g in India, most of the businesses start their new year during Diwali. Lets say a company decides their first month of their fiscal year is going to be Jun so the last month is May.

How is the numbering done in oracle?

If you query the gl_periods table ,  you will find columns such as period number, period_name, period year.

Lets say you are looking at fiscal year 2007.
Your 1st period in fiscal 2007 is Jun of 2006(regular calendar), so the period_number is 1 and the period_name would be something like Jun-2007 or Jun-07.

and the last period that is fiscal May-07 would be falling in May-2007 of regular calendar.

And then the next fiscal year i.e 2008 will begin in Jun-2007 (regular calendar) and end in May-2008 of regular calendar.

can it be more complicated

Oracle Applications support flow manufacturing and costing for Flow
Manufacturing in both standard and average costing organizations.
Assembly Completions and Return Transactions
You can complete assemblies from and return assemblies to both
scheduled and unscheduled flow schedules using the Work Order–less
Completions window. When you return assembles to a flow schedule,
the components are automatically returned to inventory. Unscheduled
flow schedules, also known as work order–less completions, make it
possible for you to complete and return assemblies without having to
first create a job or repetitive schedule.
When you complete flow schedules, the costs incurred and variances
and costs relieved are processed and reported with respect to the
transaction itself. This costing methodology differs from that used for
discrete jobs, which track and report costs by job, and repetitive
schedules, which maintain and track costs by repetitive assembly/line
combination and allocate them across repetitive schedules.
Transaction Processing
Component backflush transactions, triggered by flow schedule
completion transactions, are automatically costed. The resource and
overhead transactions that are triggered by completion transactions are
likewise automatically costed.
Only one completion transaction and its associated component,
resource, and overhead transactions must be processed each time a
flow schedule is transacted. In other words, these transactions are
processed as a set. Resource and overhead transactions from other
flow schedules or other completion transactions for this same flow
schedule are not processed in this set. This ensures that period balance
information is properly maintained. If any transaction associated with
flow schedule completions fail to process, all associated transactions
are rolled back.
Zero Balance
Under standard costing, all cost transactions and variance transactions
associated with flow schedule completions are processed at the same
time as the Assembly Completion. Therefore the net balance in the
flow schedule is always zero. Flow schedule assembly return
transactions are similarly processed.

Assembly Scrap
Assembly scrap is costed the same way for both standard and average
costing.
You can scrap and return scheduled and unscheduled assemblies at any
routing operation. You can scrap and return scrapped assemblies even
if there is no routing. Operation Pull and Assembly Pull components
associated with the scrap operation and prior operations are
automatically backflushed. Assemblies scrapped can be returned, thus
returning the issued components back to inventory.
WIP scrap transactions and WIP return from scrap are available
transaction types in the Work–Orderless Completions window.

You can cost all project related manufacturing transactions, then capture
these costs and transfer them to Oracle Projects. You can associate items
and manufacturing business processes with specific projects, and
optionally tasks, to track quantity and cost information through these
business processes. Project manufacturing costing allows you to process
and cost material, labor, and associated overheads against a specific
project or a group of projects for a specific customer.

Daily Business Intelligence Family Pack G (7.3) is now available from Metalink. Please see the About Document, Metalink Note 391782.1 for a complete list of new features along with installation and setup instructions. New Feature Overview
This release launches Daily Business Intelligence for Receivables.

Daily Business Intelligence for Receivables empowers Receivables, Credit and Collections managers to stay on top of the critical information related to their department’s operational performance.

The increased visibility enables Receivables and Collections managers to reduce the time of operational problems discovery and resolution, and therefore, increase the effectiveness of their departments. It also provides valuable insight into the state of customers’ receivables payment patterns enabling Credit Managers to formulate Credit Policies that would prevent undesired impact on company’s bottom-line.

Daily Business Intelligence for Receivables consists of the Receivables Management and Receivables Status dashboards. Together they allow analysis of the key performance indicators (�KPIs�) such as Days Sales Outstanding, Average Days Delinquent, Open Receivables and Past Due Receivables Amount, and Unapplied Receipts Amount across multiple dimensions, such as time, organization, customers, and collectors. They empower managers to evaluate the performance of their organizations and discover systemic processing inefficiencies. In addition they offer valuable insight essential for evaluating the productivity of individual collectors.

This release also further enhances the Daily Business Intelligence for Financials.

Profit and Loss Analysis provides valuable insight necessary for evluating the information related to a company’s revenue, cost of goods sold, operating expenses, gross margin and operating margin.

The Profit and Loss Analysis Dashboard targets financial analysts and line managers who are responsible for supporting and analyzing revenue and expenditures for particular cost centers and company entities across different ledgers. The Dashboard features a company/cost center/account-oriented view of the company’s key profit and loss activity.