OBIA - Currencies and their Exchange Rates.

A business does the transaction in multiple currencies. Therefore, to create a meaningful report you have to use the common currency across the transactions.

The warehouse of oracle BI uses the below types of currencies:-

01.Document Currency: - This is the currency of transaction. For Example: - If you purchase a laptop from the supplier in France, the document currency would be EUR.

02.Local currency: - The local currency is the base currency of your ledger, or the currency in which your accounting entries are recorded.

03.Global currencies: - Oracle BI provides three global currencies which is useful while creating enterprise wide report. For every transactional amount fetched from source, ETL part of Oracle BI loads the document and local amounts into the warehouse table. Along with the amount it also stores the exchange rates required to convert the document amount into each of the three global currencies. For fact tables, there are two amount columns one for the Local currency amount and other for the Document currency amount. In addition, there are three columns covering the Global currency (for example, global _amount1) and their corresponding exchange rate columns. These global _amounts are stored in aggregate tables of the corresponding facts. For Example :-

W_AP_XACT_GRPACCT_FSCLQTR_A.AP_GLOBAL1_AMT = W_AP_XACT_F.AP_DOC_AMT* W_AP_XACT_F.GLOBAL1_EXCHANGE_RATE   

  To configure the global currencies you want to report:

1.   In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

2.   Display the Source System Parameters tab.

3.   Locate the following parameters, and set the currency code values for them in the Value field:

o    $$GLOBAL1_CURR_CODE (for the first global currency).

o    $$GLOBAL2_CURR_CODE (for the second global currency).

o    $$GLOBAL3_CURR_CODE (for the third global currency).

You can specify any currency, provided an exchange rate is specified for the conversion to the local currency. Ensure that you spell the currencies as they are spelled in your source OLTP system.

4.   Save your changes.

OBIA-Exchange Rates

As discussed above, to get the correct value of GLOBAL1_AMT, we require the value of GLOBAL1_EXCHANGE_RATE.Therefore, exchange rate types is the important parameter to understand to get the amount in global currencies.

To configure exchange rate types:

1.   In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.

2.   Display the Source System Parameters tab.

3.   Locate the following DAC parameters and set the exchange rate type values for them in the Value field:

o    $$GLOBAL1_RATE_TYPE

o    $$GLOBAL2_RATE_TYPE

o    $$GLOBAL3_RATE_TYPE

o    $$DEFAULT_LOC_RATE_TYPE (the conversion rate type for document currency to local currency conversion).

Make sure you spell the exchange rate type values as they are spelled in your source OLTP system.

4.   Save your changes

 

Exchange Rates Calculations (OBIA-ETL)

Exchange rate calculation is done by the mapplet MPLT_CURCY_CONVERSION_RATES_ToGlobalCurrenciesOnly in SILOS. This mapplet is responsible for getting the correct exchange rates for a date chosen, where a currency conversion to any of the three possible global currencies is involved. 

We first get the value of GLOBAL1_CURR_CODE by doing a lookup on W_GLOBAL_CURR_G table, condition match on Datasource Number and Tenant Id.


The W_GLOBAL_CURR_G table is always populated with one record and that record has the value of $$GLOBAL1_RATE_TYPE,$$GLOBAL2_RATE_TYPE,$$GLOBAL3_RATE_TYPE,$$DEFAULT_LOC_RATE_TYPE AND $$GLOBAL1_CURR_CODE,$$GLOBAL2_CURR_CODE,$$GLOBAL3_CURR_CODE populated from DAC. The mapping SIL_GlobalCurrencyGeneral_Update just read the above mentioned DAC parameters and loads same record into W_GLOBAL_CURR_G.
Once the GLOBAL1_CURR_CODE is retrieved as part of the lookup on table W_GLOBAL_CURR_G

We check below four conditions:-


01. Is GLOBAL1_CURR_CODE NULL. If so populate GLOBAL1_EXCH_RATE as NULL
02. If first condition is not satisfied we check Is GLOBAL1_CURR_CODE = DOC_CURR_CODE.Then populate GLOBAL1_EXCH_RATE as 1.0
03. If first and second condition is also not satisfied then is DOC_CURR_CODE = 'STAT'.If so then populate GLOBAL1_EXCH_RATE as 1.0
04. If first,second and third condition is also not satisfied then is GLOBAL1_CURR_CODE = LOC_CURR_CODE.If so then below :-
                   A. Is LOC_EXCHANGE_RATE is NULL if so then GLOBAL1_EXCH_RATE as NULL
                   B. If first condition is not satisfied we check Is LOC_CURR_CODE = DOC_CURR_CODE then populate GLOBAL1_EXCH_RATE as 1.0
                   C. Default do a lookup on W_EXCH_RATE_G on the condition (DOC_CURR_CODE,LOC_CURR_CODE,EXCH_DT,LOC_RATE_TYPE_VAR,DATASOURCE_NUM_ID) and populate GLOBAL1_EXCH_RATE [LOC_RATE_TYPE_VAR = IIF(ISNULL(LOC_RATE_TYPE), DEFAULT_LOC_RATE_TYPE (DAC Value), LOC_RATE_TYPE)]

Default case :- Do a lookup on W_EXCH_RATE_G on the condition (DOC_CURR_CODE, GLOBAL1_CURR_CODE, EXCH_DT,GLOBAL1_RATE_TYPE, DATASOURCE_NUM_ID) and get the value of EXCH_RATE and populate it as GLOBAL1_EXCH_RATE.



Now how W_EXCH_RATE_G is getting populated:-
The W_EXCH_RATE_G is populated by SIL_ExchangeRateGeneral(This mapping is responsible for loading dimension records for the table W_EXCH_RATE_G based on the corresponding staging area table. This mapping takes care of inserting new records as well as updating existing records in the target table. On a requirement basis, this mapping also 'soft-deletes' records from the target table (meaning, marking a record as 'Deleted' instead of physically deleting them).)

SIL_ExchangeRateGeneral reads the data from W_EXCH_RATE_GS table.


W_EXCH_RATE_GS basically picks the data from GL_DAILY_RATES OLTP table.

General Ledger provides the following predefined daily conversion rate types:
 Spot: An exchange rate which you enter to perform conversion based on the rate on a specific date. It applies to the immediate delivery of a currency.

Corporate: An exchange rate you define to standardize rates for your company. This rate is generally a standard market rate determined by senior financial management for use throughout the organization.
User: An exchange rate you specify when you enter a foreign currency journal entry.
EMU Fixed: An exchange rate General Ledger provides automatically when you enter journals (after the EMU effective starting date) using a foreign currency that has a fixed relationship with the Euro.
OBIA 7.9.6.x does not support Spot and User rate types.