This section provides information about the Exchange Rates tables in the Oracle Insurance Data Foundation application.
Topics:
· Business Use Case Indicating the Requirement of two Exchange Rates Tables
· Overview of the Exchange Rates Table
· About Exchange Rates T2Ts (Result Tables)
· Deploying Exchange Rates Tables on Hive
· Populating Exchange Rates T2T Result Table
The Exchange Rates table stores the list of all exchange rates for all types of currency. These are standalone tables.
The purpose of the Exchange Rate tables is to offer the value of one currency in relation to another currency.
In a downloadable format, the customers supply the Exchange Rates information. The applications may require the information in a different format. The current Exchange Rates population is enhanced to store the following variations:
· Inverse Rate: When the exchange rate information of two currencies is provided for a base and a counter currency, the inverse rates if not available is derived and populated.
· Triangulation Rate: When exchange rate information of two pairs are available as part of the download, with a common currency in each of the pair, the exchange rate for currencies not common as part of the download pair is derived and populated.
All the balance sheet computations are done based on the average price or the closing price. For illustration, assume that an insurance company has branches in multiple countries, and therefore, multiple exchange rates scenarios exist. Then the insurance company must decide to choose the closing price of the exchange rate. Therefore, the insurance company computes the balance sheet based on the entity in each country. Assume that the insurance company is operative in four different countries. If the legal entity is based on country A, then A category of prices is used for balance sheet computation, and if the legal entity is based on country B, then B category of prices is used, and C category of prices based on country C, and D category of prices based on country D.
To achieve this, for a single day, the insurance company captures multiple exchange rates because of different countries. For Currency A to Currency B, the insurance company must capture three pairs of exchange rates. OIDF handles this type of scenario using the Rate Data Source Code, which is based on the data source. The insurance company chooses the time zone.
The insurance company creates different data sources such as country AB, country AC, and country AD. A mapping exists from Legal Entity to each data source. If the Legal Entity is of country B, then the insurance company uses AB as the data source and all the prices of data source AB are used to compute all the transactions. This is the business use case that resulted in the requirement of two Exchange Rates tables in OIDF.
The existing Stage Exchange Rates table cannot be modified due to the presence of the PK column. The existing customers also do not need two Stage Exchange Rates tables.
OIDF has two Exchange Rates Staging tables. They are:
· STG_EXCHANGE_RATE_HIST
· STG_FORWARD_EXCHG_RATES
Spot rates will be loaded with Tenor 0.
The Exchange Rate table FSI_EXCHANGE_RATES is loaded from a View table VW_FSI_RATE_TRIANGULATION, where the VW_FSI_RATE_TRIANGULATION table is created on top of the Exchange Rates Stage tables through the T2T process.
Prior to the OIDF 8.1.0.0.0 release, T2T to load data from STG_EXCHANGE_RATE_HIST was only provided for exchange rates. Now T2T for loading data from STG_FORWARD_EXCHG_RATES is also provided. There are two different data loading categories in OIDF for the Exchange Rates tables. They are:
· The data loading method supported for the Exchange Rates table from the OIDF 8.1.0.0.0 release.
The existing customers can follow the new data loading method for the Exchange Rates tables. However, the new customers must follow this new data loading method for the Exchange Rates tables. In the new method, the data loads from the View table VW_FSI_RATE_TRIANGULATION into the Stage table STG_FORWARD_EXCHG_RATES.
NOTE |
This is the only data loading method available in the v8.1.0.0.0 and onward releases for the Exchange Rates tables. |
Only the existing customers can follow the earlier data loading methods for the Exchange Rates tables.
ATTENTION |
The method of data loading into the STG_EXCHANGE_RATE_HIST table is no more supported in the OIDF v8.1.0.0.0 and onward versions for the Exchange Rates tables. |
In an integrated environment, there can be a scenario, where the customer is using two applications and each of the applications refer to the STG_FORWARD_EXCHG_RATES table or the STG_EXCHANGE_RATE_HIST table. This scenario can result in duplicate data loads into the STG_FORWARD_EXCHG_RATES and STG_EXCHANGE_RATE_HIST tables. For this scenario, these are the recommendations:
· For the data load, the STG_EXCHANGE_RATE_HIST table supersedes the STG_FORWARD_EXCHG_RATES table.
· The implementation team ensures that T2T_FSI_EXCHANGE_RATES is used for the STG_EXCHANGE_RATE_HIST table data load.
· The STG_EXCHANGE_RATE_HIST table loads the FSI_EXCHANGE_RATES table.
The following are two Exchange Rates T2Ts:
· T2T_FSI_EXCHANGE_RATES
· T2T_FSI_EXCHANGE_RATES_FRWD
Exchange Rates T2T for the STG_EXCHANGE_RATE_HIST table and its description is as follows.
Figure 84: Exchange Rates T2T for the STG_EXCHANGE_RATE_HIST table and its description
T2T Name |
T2T Description |
T2T_FSI_EXCHANGE_RATES |
This T2T stores history of the exchange rates between two currencies sourced through the STG_EXCHANGE_RATE_HIST table. |
The mapping details for the Exchange Rates T2T is as follows.
NOTE |
Ensure to verify and load data into the STG_EXCHANGE_RATE_HIST table using the Table to Table (T2T) component of Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) framework. |
Figure 85: The mapping details for the Exchange Rates T2T
Source Table Name |
Logical Stage Table Name |
Fact Table Name |
Logical Fact Table Name |
T2T Name |
VW_FSI_RATE_TRIANGULATION |
FSI Rate Triangulation View |
FSI_EXCHANGE_RATES |
FSI Exchange Rates |
T2T_FSI_EXCHANGE_RATES |
T2T_FSI_EXCHANGE_RATES_FRWD is added in the OIDF 8.1.0.0.0 release. The Exchange Rates T2T for the STG_FORWARD_EXCHG_RATES table and its description is as follows.
Figure 86: The Exchange Rates T2T for the STG_FORWARD_EXCHG_RATES table
T2T Name |
T2T Description |
T2T_FSI_EXCHANGE_RATES_FRWD |
This T2T stores history of the exchange rates between two currencies sourced through the STG_FORWARD_EXCHG_RATES table. |
The mapping details for the Exchange Rates T2T is as follows.
NOTE |
Only this T2T is a part of the OOTB Runchart. Ensure to verify and load data into the STG_FORWARD_EXCHG_RATES table using the Table to Table (T2T) component of Oracle Financial Services Analytical Applications Infrastructure (OFSAAI) framework. |
Figure 87: The mapping details for the Exchange Rates T2T
Source Table Name |
Logical Stage Table Name |
Fact Table Name |
Logical Fact Table Name |
T2T Name |
VW_FSI_RATE_TRIANGULATION |
FSI Rate Triangulation View |
FSI_EXCHANGE_RATES |
FSI Exchange Rates |
T2T_FSI_EXCHANGE_RATES_FRWD |
All RDBMS related Result tables can also be deployed on Hive (Stage and Results). Deploy the Hive T2Ts using the Rules Run Framework. For more information, see the Rules Run Framework section in the Oracle Financial Services Advanced Analytical Applications Infrastructure User Guide Release 8.1.0.0.0.
NOTE |
In general, Stage and Result tables are also supported in Hive. However, there are some exceptions. For a list of tables that are not supported in Hive, see List of Unsupported T2Ts |
Execute the T2T process through the Oracle Insurance Data Foundation Execution Run in the Process Modelling Framework.
NOTE |
When executing the Run, the Run SKey is auto-generated and stamped against each record. |
Follow this T2T process to populate data into any T2T Result table:
NOTE |
Only RDBMS T2Ts can be executed using the PMF. FSI_EXCHANGE_RATES table must be loaded prior to loading any of the Account Summary tables. |
1. To populate data into any T2T Result table, execute the PMF process for that T2T. For a detailed procedure, see the following sections:
a. Prerequisites for loading T2T.
b. Select the Run Parameters and Execute the Run.
2. To check the T2T execution status and verify the log files of any Result table, follow the procedure in the Verify the Run Execution section.
3. To check the error messages, if any, follow the procedure in the Check Error Messages section.