Currency Conversions

Currency conversions typically run by default when default logic runs. You can also create a new logic file to run a currency conversion.

You run this rule by calling the stored procedure SPRUNCONVERSION from any logic file.

*RUN_STORED_PROCEDURE=SPRUNCONVERSION(Parameters list)

Example:

*RUN_STORED_PROCEDURE=SPRUNCONVERSION([%APP%],[%CATEGORY_SET%],[%CURRENCY_SET%],[GLOBAL],[%SCOPETABLE%],[%LOGTABLE%])

Parameter	Description
`Application`	The application ID.
`Category`	The category ID. We currently support the handling of only one category at a time.
`Currency`	The currency, blank, or Multi.
`Rateentity`	The default Entity member for the Rate application.
`%SCOPETABLE%`	`%SCOPETABLE%` is a variable used by the system to define the name of a logic table.
`%LOGTABLE%`	`%LOGTABLE%` is a variable used by the system to define the name of a Log table.

The stored procedure SPRUNCONVERSION scans all records found in the selected region of data. It translates them according to the RATETYPE property assigned to the ACCOUNT specified in each record, based on the mechanism that all ACCOUNTS are translated according to the definitions contained in the table of parameters called clcFXTRANS.

For information on required properties of the Category and Data Source dimensions, see Dimensions Setup for Consolidations.

The following table describes the fields for the currency conversion rules table.

Field name	Description
Account rate type	The currency conversion type, for example, `AVG`, `END`, `HIST`, taken from the Account dimension's `RATETYPE` property. This is the main driving field, controlling the translation rule to apply to a given account. Note One `RATETYPE` may generate more than one translated value. This can be defined by entering more than one entry with the same `RATETYPE` in the business rule interface for currency rules. End of the note.
Source flow	This field, combined with the 'Account rate type' field, completes the definition of the criteria that drive the applicability of a given rule. In other words, the instruction can be read as follows: If the account has such `RATETYPE` and the `FLOW` is such and such, then apply this rule. This field may contain the following values: A valid base level or parent member ID from the `FLOW` dimension A list of members of the `FLOW` dimension, as defined filtering the members using a value of the `DIMLIST` property (or any property whose name begins with `DIMLIST`) For information about the Flow dimension, see Setting up a legal consolidation application set.
Destination account	The account that stores the specific conversion. If empty, it is the same as the source account's member.
Destination flow	The specific `Accdetail` member where translations are stored. If empty, then it is the same as the source subtable member.
Formula	This field can contain any arithmetic expression combining any defined `RATE` as per the `ACCOUNT` dimension of the `RATE` application. All rates with the `RATETYPE` property value of `FX TRANS` can be used.
Force closing	If checked, force the generation of an additional record where the member of the destination `FLOW` is the closing balance. The closing balance member of the `FLOW` dimension is identified as the member with the property `FLOW_TYPE` = `CLOSING`.
Apply to periodic	This field should only be used in a YTD application for the cases in which the currency conversion should be performed on the `PERIODIC` values and not on the YTD values. If the box is checked, the engine calculates the difference between current period and prior period amounts, and it applies the rate specified in `RATE_FORMULA` field. At the end, the result is added back to prior period's value as written in current period.
Entity FX type	This field can be used to enforce a given set of rules to only apply to a desired set of `ENTITIES`. If this field has a value, the rule is only applied to the entities having a matching value in a similarly named property (`FX_TYPE`) of the `ENTITY` dimension.
Remarks	A short description for this rule.

Formula field

In the FORMULA field, the rates must be enclosed in square brackets:

[END]

[END] – [AVG]

The OPENING value of any rate can also be specified adding the prefix OPE to the rate itself.

Examples:

[OPEEND] – [END]

[OPEAVG]

These OPENING rates do not need to exist in the RATE cube. For example, if there is an [END] rate, the currency translation also automatically recognizes a rate called [OPEEND], which simply corresponds to the [END] rate of the OPENING period (typically last period of last year).

In addition, the RATE_FORMULA field supports the following keywords:

[AS_IS]: Leave untouched a value already existing in the destination currency. The keyword cannot be combined with other rates in the same line. The only valid exception is the format [AS_IS]*-1.
[COPYLC]: This correspond to applying a rate of 1 in the translation.

How the RATE table is selected

While most customers require a single table of rates, there are situations when more than one set of rates is required. In this situation, the translation procedure uses the RateEntity dimension to select the correct table of rates to use.

Whenever a destination currency is selected, the procedure searches for a RateEntity member flagged with this currency in the Currency property. For example, if translating into USD, the system uses the RateEntity member that has the Currency property set to USD.

If there is no RateEntity flagged as the destination currency, the system uses the RateEntity with Currency = ‘’ (blank).

In addition to this, some exceptions by ENTITY can be applied. For example, some entities just entering in the consolidation perimeter may need to be converted at their own specific set of rates. These entities may have a corresponding RateEntity member in the RATE cube. All ENTITIES having a corresponding RateEntity member in the RATE cube use that member as rate table. For example, if there is a RateEntity member named in the same way as the ENTITY USOps, the RateEntity member USOps is used to translate the values of entity ENTITY USOps.

The RateEntity member, when representing an ENTITY, may be any of the following:

A valid base level or parent member ID from the ENTITY dimension of the main cube.
A list of members of the ENTITY dimension, as defined filtering the members using a value of the DIMLIST property (or any property whose name begins with DIMLIST) of such dimension.