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.
