Global Currencies in Oracle BIA

If you read the Oracle Documentation for Oracle BIA (7.6.9.3), you could find the following; “Currency conversions are required because your business might have transactions involving multiple currencies. To create a meaningful report, you have to use a common currency.”

Oracle BIA stores amounts in the following currencies;

- Document Currency –> Currency of the actual Transaction (Can vary in a multinational organization)
– Local Currency –> Currency defined in the Ledger
– Global Currency –> Defined in the DAC

You can use a Global Currency if you want to report all the different Currencies in one Global Currency (eg. ‘EUR’, or ‘USD’). Oracle BIA for Oracle eBS is able to report in 3 different Global Currencies. If you use Oracle BIA for CRM, you are able to store an additional two Global Currencies. You define the Global Currencies in the DAC.

- $$GLOBAL1_CURR_CODE
– $$GLOBAL2_CURR_CODE
– $$GLOBAL3_CURR_CODE

You will have to define a Global Rate Type for each Global Currency as well.

- $$GLOBAL1_RATE_TYPE (for the first global currency)
– $$GLOBAL2_RATE_TYPE (for the second global currency)
– $$GLOBAL3_RATE_TYPE (for the third global currency)

DAC - Currency - Rate - Parameters

You would use the above DAC Parameters for a Document Currency to Global Currency Conversion ($$GLOBALn_CURR_CODE & $$GLOBALn_RATE_TYPE). The $$DEFAULT_LOC_RATE_TYPE-Parameter is used for the Document Currency to Local Currency Conversion.

To get a better understanding about how these Conversions work, you should try to understand the; ‘MPLT_CURCY_CONVERSION_RATES’-Mapplet. The purpose of this Mapplet is to; “Find the Conversion Rate for a given Currency Code, Rate Type and Exchange date. You should find this mapping in the Out-of-the-Box SILOS-Folder.

Informatica - Mapplet - MPLT_CURCY_CONVERSION_RATES - Diagram

In the ‘MPLT_CURCY_CONVERSION_RATES’-Mapplet you will find an Expression; ‘EXPT_CALC_EXCH_RATES.’ This ‘EXPT_CALC_EXCH_RATES’-Expression is responsible for the actual Conversion (Calculation).

Informatica - Mapplet - MPLT_CURCY_CONVERSION_RATES - EXPT_CALC_EXCH_RATES

There are a few other Mapplets with similar name and functionality compared to the ‘MPLT_CURCY_CONVERSION_RATES’-Mapplet.

The Conversion for eg. the GLOBAL1_EXCHANGE_RATE basically consists of three components:

- Input from a Mapping (eg. SIL_APInvoiceDistributionFact – W_AP_INV_DIST_F)
– Lookup to the W_GLOBAL_CURR_G-Table
– Lookup to the W_EXCH_RATE_G-Table

Informatica - Mapplet - MPLT_CURCY_CONVERSION_RATES - EXPT_CALC_EXCH_RATES - Calculate Rate

Input from a Mapping

The ‘MPLT_CURCY_CONVERSION_RATES’-Mapplet is part of a Mapping. Let’s take the ‘SIL_APInvoiceDistributionFact’-Mapping which populates the ‘W_AP_INV_DIST_F’-Table as an example.

Informatica Mapping - SIL_APInvoiceDistributionFact

Lookup to the W_GLOBAL_CURR_G-Table

The ‘SILGlobalCurrencyGeneral_Update’-Mapping in the ‘SILOS’-folder stores the values of the  to the $$GLOBALn_CURR_CODE-, and $$GLOBALn_RATE_TYPE-DAC-Parameters in the W_GLOBAL_CURR_G-Table.

W_GLOBAL_CURR_G - Currency - Rate

As you can see the values in the W_GLOBAL_CURR_G-Table, match with the values in the DAC-Parameters. Of course you can setup these Parameters anyway you like.

Lookup to the W_EXCH_RATE_G-Table

The W_EXCH_RATE_G is a Table which resembles the GL_DAILY_RATE-Table in Oracle eBS. If the contents of this Table are incomplete or incorrect, the whole setup of the different Currencies is useless. The ‘LKP_W_EXCH_RATE’-Lookup is used to find the Conversion Rate for a given Currency Code (‘From’ and ‘To’) and Exchange Date .

Informatica - Mapplet - MPLT_CURCY_CONVERSION_RATES - LKP_W_EXCH_RATE - Complete

 

If you query the  ‘W_AP_INV_DIST_F’-Table an you find the ‘GLOBAL1_EXCHANGE_RATE’-Column empty for a certain Transaction, the above should help you find out the reason.

Good Luck.

About these ads

About Daan Bakboord
I am an Oracle Business Intelligence Consultant with great interest in anything closely related to the Oracle BI Foundation Suite.

2 Responses to Global Currencies in Oracle BIA

  1. Pingback: Another Awesome OBIEE Week | Addidici - 14 Years OBIEE and OBI Applications

  2. Pingback: Global Currencies in Oracle BIA – The Sequel | Oracle BI By Bakboord

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 701 other followers

%d bloggers like this: