Exchange Rates and Global Currencies in OBIA data warehouse

Performing currency conversions on business transactions is a common requirement when companies deal with global operations.    These transactions are typically executed in a number of different currencies depending on where the transactions take place.   To create useful analyses on the consolidated transaction data, it is necessary to store the data in one or more common currencies.
 
The Oracle Business Analytics Warehouse supports the following currency types:

■ Document currency. The currency in which the transaction was performed and the
related document was stored in.   For example, if your company purchases a desk from a supplier in France, the document currency will likely be in Euros.

■ Local currency. The accounting currency of the legal entity or ledger in which the
transaction occurred. 

■ Global currency. The Oracle Business Analytics Warehouse stores up to three
group currencies configured using the Oracle Business Intelligence Data
Warehouse Administration Console.  For example, if your company is a multinational enterprise with headquarters in the United States, USD (US Dollars) will be one of the three global currencies.

The global currencies must be pre-configured prior to loading data so that exchange rates can be applied to the transactional data as it is loaded into the data warehouse.  For every monetary amount extracted from the source, the ETL mapping stores the document and local amounts in the target table. It also stores the correct exchange rates required to convert the document amount into each of the three global currencies.   Generally, there will be eight columns on a fact table for each amount:  one local amount, one document amount, three global amounts, and three exchange rates used to calculate the global amount.

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 (making sure to the values are consistent with the source system exchange rate table):

        $$GLOBAL1_CURR_CODE (for the first global currency).
        $$GLOBAL2_CURR_CODE (for the second global currency).
        $$GLOBAL3_CURR_CODE (for the third global currency).

 When Oracle BI Applications converts your transaction records' amount from document currency to global currencies, it also requires the exchange rate types to use to perform the conversion. For each of the global currencies, Oracle BI Applications also enables you to specify the exchange rate type to use to perform the conversion. Oracle BI Applications also provides three global exchange rate types for you to configure.

Oracle BI Applications also converts your transaction records' amount from document currency to local currency. Local currencies are the base currencies in which your accounting entries and accounting reports are recorded. In order to perform this conversion, Oracle BI Applications also enables you to configure the rate type that you want to use when converting the document currency to the local currency.

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:

        $$GLOBAL1_RATE_TYPE             (rate type for GLOBAL1_CURR_CODE)
        $$GLOBAL2_RATE_TYPE             (rate type for GLOBAL2_CURR_CODE)

        $$GLOBAL3_RATE_TYPE             (rate type for GLOBAL1_CURR_CODE)

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

Exchange Rate ETL:

The exchange rates are stored in the table W_EXCH_RATE_G.   For Peoplesoft implementations, W_EXCH_RATE_G is loaded using from W_EXCH_RATE_GS which is populated using mapping SDE_PSFT_ExchangeRateDimension.   

Within that mapping is a mapplet mplt_BC_PSFT_ExchangeRateDimension which performs the actual extract of data from PS_RT_RATE_TBL using the SQL below:

SELECT
PS_RT_RATE_TBL.RT_RATE_INDEX,
PS_RT_RATE_TBL.TERM,
PS_RT_RATE_TBL.FROM_CUR,
PS_RT_RATE_TBL.TO_CUR,
PS_RT_RATE_TBL.RT_TYPE,
PS_RT_RATE_TBL.EFFDT,
PS_RT_RATE_TBL.RATE_MULT,
PS_RT_RATE_TBL.RATE_DIV,
PS_RT_RATE_TBL.LASTUPDDTTM
FROM
PS_RT_RATE_TBL, PS_RT_INDEX_TBL
WHERE
PS_RT_RATE_TBL.RT_RATE_INDEX = PS_RT_INDEX_TBL.RT_RATE_INDEX AND
PS_RT_RATE_TBL.TERM = 0 AND PS_RT_INDEX_TBL.DEFAULT_INDEX = 'Y'
AND PS_RT_RATE_TBL.LASTUPDDTTM > $$LAST_EXTRACT_DATE;