Generating Currency Conversion Calc Scripts in Oracle EPM Planning

A quick guide to the Currency Conversions feature

Multi-currency Planning applications need a calc script that translates local-currency data into one or more reporting currencies. Writing that script by hand means repeating the same FIX, rate lookup and IF/ELSEIF logic for every reporting currency and every rate type. Oracle EPM Planning can generate it for you: the Currency Conversions feature under Create and Manage produces a ready-to-run calc script for every cube, based on the scenarios, versions, years and currencies you select.

Prerequisites

Before you begin, make sure the following are in place:

Configuration steps

1. In the Navigator, under Create and Manage, select Currency Conversions.

2. Enter the calc script file name and description, then the calculation script details.

3. Click Save.

4. Navigate to Rules to open Calculation Manager.

5. Expand a cube (e.g. Plan1) to see the generated calc script.

Get Anusha MP’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

A calc script is generated for each cube, following the naming convention <CalcScriptName>_<CubeName>, for example FX Conv_Plan1 and FX Conv_OEP_FS.

Anatomy of the generated script

Rates are read from a fixed intersection, the “No <Dimension>” members of each non-currency dimension (“No Entity”->”No Product”->”No LoB”…), for the relevant local currency. That is where the rates must be loaded.

Formulas. With USD as the rate base (rates expressed as local → USD):

USD_Reporting = EUR × Rate(EUR) + GBP × Rate(GBP) + USD

The non-base reporting currency is derived by triangulating through USD, so you only need to maintain one set of rates.

Cube-specific differences

The conversion logic is the same in every generated script. What changes is the dimensionality. Each script fixes on the level-0 members of the dimensions that exist in its own cube, and reads exchange rates from that cube’s “No” members. For example, Plan1 includes Intercompany, LoB and Cost Center, while OEP_FS uses Plan Element and Market instead.

You configure it once, and Planning builds the right script for each cube.

Practical notes

• Make sure exchange rates are loaded before running the conversion. If a rate is missing, the reporting currency values will return #MISSING.
• Attach the generated script to a data form so planners can run the conversion directly from the form, for example on save.

Thanks for reading! Give this method a try in your own application, and attach the generated script to a form so your planners can run currency conversion with a single click.

-Anusha