Oracle EPF – EPB: How to Consolidate Actuals?

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.

Leave a Comment