How to avoid problem with currency data load in IBM OpenPages

Check list of all posts

1. Context

When input data on OpenPage interface, you don’t have any problem, as system will automatically convert local currency amount into base current amount. The problem is to load currency amount with fastmap, the currency amount will be automatically converted into base currency amount, which is not correct from business point of view. Business want to see the exact the same amount as provided from fastmap.  The document provides a solution about how to resolve this problem.

2. Work flow

There are two tiers behind the scene:

• The default built-in feature, which is to convert local amount into base currency amount using the current exchange rate.
• The trigger, in which recognition date is used to get historical exchange rate to get the base currency amount, also get performance many other action, such as calculate net loss from loss impact and loss recovery.

Even we can disable trigger, system will still convert local currency amount to base currency amount. The problem still persists. We cannot disable the built-in feature. Also, disable triggers is not solution for ongoing processing, or on monthly or quarterly basis.

3. Solutions

When exchange rate is provided, Built-in function will use the provided exchange rate. This function opens a door for us to provide exchange rate to calculate base current amount. We can provide exchange rate as

(base amount /  local amount), then get base amount as

(local amount) * (base amount /  local amount)

Technically, as exchange rate cannot be 0 or 1, the implementation is look like below

(DT_DECIMAL,5)(Local_Currency_Code == "CAD" || (DT_DECIMAL,5)[Net Loss (in CAD).Local Amount] == 0.0 ? NULL(DT_I4) : (DT_DECIMAL,5)[Net Loss (in CAD).Base Amount] / (DT_DECIMAL,5)[Net Loss (in CAD).Local Amount])

However, it is still not working, as need to disable triggers, the solution is to build trigger with criteria on row level. If fastmap with [run trigger] = No, then trigger won’t take in effect.

Also please remember making the exchange rate with 8 decimal places for small currency to make currency conversion correctly reflect the base currency amount.