This chapter contains information about the Exchange Rates tables in the Oracle Financial Services Data Foundation application.
Topics:
· Business Use Case Indicating the Requirement of two Exchange Rates Tables
· Overview of the Exchange Rates Tables
· About Exchange Rates T2Ts (Result Tables)
· Multiple Execution of T2Ts using the Runchart
· Populating Exchange Rates T2T Result Tables
The Exchange Rates table stores the list of all exchange rates for all types of currency. This is a standalone table.
The purpose of the Exchange Rate table is to offer the value of one currency in relation to another currency.
In downloadable format, the customers supply Exchange Rates. 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 are 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 download, with a common currency in each of the pair, 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 a bank has branches in multiple countries and therefore, multiple exchange rates scenarios exist. Then the bank must decide to choose the closing price of exchange rate.
Therefore, the bank computes the balance sheet based on the entity in each country. Assume that the Bank 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, bank captures multiple exchange rates because of different countries. For CurrencyA to CurrencyB, the bank must capture three pairs of exchange rates. OFSDF handles this type of scenario using the Rate Data Source Code, which is based on the data source. The bank chooses the time zone. The bank creates different data sources such as country AB, country AC, and country AD. Mapping exists from Legal Entity to each data source. If the Legal Entity is of country B, then the bank uses AB as the data source and all the prices of data source AB is used to compute all the transactions. This is the business use case that resulted in the requirement of two Exchange Rates tables in OFSDF.
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.
OFSDF has two Exchange Rates Staging tables. They are:
· STG_EXCHANGE_RATE_HIST
· STG_FORWARD_EXCHG_RATES
Both Spot and Forward FX rates. Sport 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 OFSDF 8.1.0.0.0 release, T2T to load data only from STG_EXCHANGE_RATE_HIST was 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 OFSDF for the Exchange Rates tables as follows:
· Data loading method supported for the Exchange Rates table from the OFSDF 8.1.0.0.0 release
The existing customers may 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 may follow the previous 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 OFSDF 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 may result in the 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 the T2T 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.
This section provides information about the existing Exchange Rates T2Ts.
Topics:
· About Exchange Rates T2T for the STG_EXCHANGE_RATE_HIST table
· About Exchange Rates T2T for the STG_FORWARD_EXCHG_RATES table
Exchange Rates T2T for the STG_EXCHANGE_RATE_HIST table and its description are as follows.
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 are 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.
Source Table Name |
Logical Stage Table Name |
Fact Table Name |
Logical Fact Table Name |
T2T Name |
|---|---|---|---|---|
VW_FSI_RATE_TRI ANGULATION |
FSI Rate Triangulation View |
FSI_EXCHANGE_ RATES |
FSI Exchange Rates |
T2T_FSI_EXCHANGE_ RATES |
T2T_FSI_EXCHANGE_RATES_FRWD is added in the OFSDF 8.1.0.0.0 release. The Exchange Rates T2T for the STG_FORWARD_EXCHG_RATES table and its description are as follows.
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 are as follows.
NOTE:
Only this T2T is part of the OOB 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.
Source Table Name |
Logical Stage Table Name |
Fact Table Name |
Logical Fact Table Name |
T2T Name |
|---|---|---|---|---|
VW_FSI_RATE_TRI ANGULATION |
FSI Rate Triangulation View |
FSI_EXCHANGE_ RATES |
FSI Exchange Rates |
T2T_FSI_EXCHANGE_ RATES_FRWD |
Use the Process ID FSDF_SOURCED_RUN to populate data in the FSI_EXCHANGE_RATES table with the Run SKey defaulted to -1.
NOTE:
Use these exchange rates to populate data in the FSDF Result tables that do not contain any Run SKey.
Use the FSDF Source Run to populate the previously mentioned Results tables (that use the exchange rates), which were populated against the Run SKey -1.
The T2T is also a part of the seeded Financial Services Data Foundation Execution Run. When executing the Run, the Run SKey is auto-generated and stamped against each record. The FSDF Result tables with valid Run SKey in their Primary Key are a part of this Run. The exchange rates populated against each Run SKey are used to populate the Results Area of the same Run SKey.
NOTE:
The Exchange Rates T2Ts are a part of the Run Chart twice:
The first is through a batch. This inserts the Run SKey as -1 in the Exchange Rates table.
The second is through a Run. This inserts the Run SKey as an actual Run SKey in the Exchange Rates table.
For example, the FCT_COMMON_ACCOUNT_SUMMARY table is not a Run enabled table. Therefore, the join is with -1. However, the FCT_REG_ACCOUNT_SUMMARY table is a Run enabled table. Therefore, the join is with the actual Run SKey.
All the T2Ts that are a part of the Process ID FSDF_EXE_RUN, use the exchange rates with the actual Run SKey, and the other T2Ts use the Run SKey -1.
Execute Runs through Process Modelling Framework. The T2T is part of the Financial Services Data Foundation Sourced Run. Therefore, execute the process through the Financial Services Data Foundation Execution Run. For more information, see the section Executing Run through Process Modelling Framework in OFSDF.
NOTE:
When executing the Run, the Run SKey is auto-generated and stamped against each record.
For more information about Process Modelling Framework, see Oracle Financial Services Analytical Applications Infrastructure Process Modelling Framework Orchestration Guide Release 8.1.2.0.0.