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
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.
