This section provides information about Common Account Summary tables in the Oracle Insurance Data Foundation application such as Asset and other Account Summary, Trading and Investment, Instruments Contracts, and Capital and Borrowings, and step-by-step instructions to use this section.
Topics:
· Abstract
· Trading and Investments Contract Tables
· Macroeconomic Variable Tables
· Capital Instruments Position and Transactions Tables
· Other Common Account Summary Tables
· Abstract of Common Account Summary Result Tables
· Deploying Common Account Summary Tables on Hive
· Populating Common Account Summary Dimension Tables
· Populating Common Account Summary T2T Result Tables
For an Insurance company, this entity stores account covering their exposures to Trading, Investment as well as Loans.
OIDF Subject Area of Trading and Investment refers to the significant portion of assets of the Insurance Company. Trading and Investment Subject Area comprise of following types of entities:
· Contracts: Investment Contracts refers to a contract or transaction or scheme where entity invest money either on its own behalf or on behalf of their clients with the expectation of profit from sale or holding of such investment.
· Instrument: Tradable investments use instruments with ISIN or CUSIP or Other known identifiers. Instrument definition most commonly is published by the issuer of instrument, which can be another public or private or government entity or stock exchange.
Investment contracts refer to a contract or transaction or scheme where entity invest money either on its own behalf or on behalf of their clients with the expectation of profit from sale or holding of such investment. Capital Instruments also make use of the same relevant instrument set of tables.
Contracts
OIDF has different entities known as product processors to hold a variety of instrument types. Given below is the list of trading and investment product processors.
Instruments
Instrument refers to an issue or derivative contract which is tradable in the market. Over the counter, contracts are considered as instruments. Thus Government bonds, municipal bonds, futures contracts, option contracts and excludes forwards, swap contracts, and so on. Multiple contracts in product processor entities mentioned above can have the same instrument traded.
The following table explains the need for creating an instrument before populating the contract for a given product processor.
Product Processor |
A record in instrument contract master and dimension is required? |
|---|---|
Stage Investments |
Yes for Exchange Traded Contracts. |
Stage MM Contracts |
Yes for Exchange Traded Contracts. |
Stage Futures |
Yes |
Stage Option Contracts |
Yes |
Stage Credit Derivatives |
Yes for Exchange Traded Contracts. |
Stage FX Contracts |
No |
Stage Forwards |
No |
Stage Mutual Funds |
Yes |
Stage Commodities |
Yes for Exchange Traded Contracts. |
This section provides Instrument, Trading and Investments Contract table details.
Topics:
· About Trading and Investments Contract T2Ts (Result Tables)
Instruments Dimension table names and their description are given here.
Logical Dimension Table Name |
Dimension Table Description |
|---|---|
Instruments Contracts Dimension |
This table stores data related to contracts and instruments in the market and their details such as Effective Date, Maturity Date, Face Value, Day Convention, Strike, and so on. |
Geography Dimension |
This table stores a distinct list of all geographical locations, where any of the transaction channels of the Insurance Company are located. |
The mapping details for the Instruments Dimension tables are given here.
Map Reference Number |
Source Table Name |
Logical Stage Table Name |
Dimension Table Name |
Logical Dimension Table Name |
|---|---|---|---|---|
69 |
STG_INSTRUMENT_CONTRACT_MASTER |
Stage Instrument Contract Master |
DIM_INSTRUMENT_CONTRACT |
Instrument Contract Dimension |
64 |
STG_GEOGRAPHY_MASTER |
Stage Geography Master |
DIM_GEOGRAPHY |
Geography Dimension |
Instruments Contracts T2Ts and their description are given here.
T2T Name |
T2T Description |
|---|---|
T2T_STG_MM_CAS |
This T2T stores the details of the Money Market contract. For example Certificates of Deposit (CDs), U.S. Treasury bills, commercial paper, municipal notes, federal funds, and so on. The money market is used by a wide array of participants, from a company raising money by selling commercial paper into the market to an investor purchasing CDs as a safe place to park money for the short term. |
T2T_STG_OPTIONS_CAS |
This T2T stores records of call and put options that are bought (long) or sold (short) by the customer. |
T2T_STG_FUTURES_CAS |
This T2T stores the details of the Futures contract. |
T2T_STG_CREDIT_DERIVATIVES_CAS |
This T2T stores the details of the Credit Derivatives contract. |
T2T_STG_COMMODITIES_CAS |
This T2T stores the details of Commodities contract. |
T2T_STG_SWAPS_CONTRACTS_CAS |
This T2T stores the details of the swaps contract. |
T2T_STG_FX_CONTRACTS_CAS |
This T2T stores the details of Foreign Exchange contracts. |
The mapping details for the Instruments T2Ts are given here.
Source Table Name |
Logical Stage Table Name |
Fact Table Name |
Logical Fact Table Name |
T2T Name |
|---|---|---|---|---|
STG_MM_CONTRACTS |
Stage Money market Contracts |
FCT_COMMON_ACCOUNT_SUMMARY |
Fact Common Account Summary |
T2T_STG_MM_CAS |
STG_OPTION_CONTRACTS |
Stage Option contracts |
FCT_COMMON_ACCOUNT_SUMMARY |
Fact Common Account Summary |
T2T_STG_OPTIONS_CAS |
STG_FUTURES |
Stage Futures |
FCT_COMMON_ACCOUNT_SUMMARY |
Fact Common Account Summary |
T2T_STG_FUTURES_CAS |
STG_CREDIT_DERIVATIVES |
Stage Credit Derivatives |
FCT_COMMON_ACCOUNT_SUMMARY |
Fact Common Account Summary |
T2T_STG_CREDIT_DERIVATIVES_CAS |
STG_COMMODITIES |
Stage Commodities |
FCT_COMMON_ACCOUNT_SUMMARY |
Fact Common Account Summary |
T2T_STG_COMMODITIES_CAS |
STG_SWAPS_CONTRACTS |
Stage Swap Contracts |
FCT_COMMON_ACCOUNT_SUMMARY |
Fact Common Account Summary |
T2T_STG_SWAPS_CONTRACTS_CAS |
Trading and Investments Contract T2Ts and their description is given here.
T2T Name |
T2T Description |
|---|---|
T2T_STG_INVESTMENTS_CAS |
This T2T stores the details of the investment contract. For example, Government and Municipal Bonds, Covered/Secured Bonds, Corporate Bonds, and so on. |
T2T_STG_MUTUAL_FUNDS_CAS |
This T2T stores the details of the Mutual Fund contract. |
T2T_FCT_FUND_CIS_COMPOSITION |
This T2T stores the composition details of the Investment Funds. |
T2T_FCT_INSTRUMENT_RATING_DETAILS |
This T2T stores the rating of trading instruments including the Mitigants that are tradable. |
T2T_FCT_INSTRUMENT_MARKET_PRICES |
This T2T stores relevant information about breach or penalty levied by the regulator in respect of- Domestic Regulatory Liquidity requirements (CRR and SLR) for all branches including overseas branches. |
T2T_STG_TRADING_ACCOUNT_CAS |
This T2T stores the details such as product type traded in the accounts such as Equities, Corporate Debt, and the degree of risk the customer is willing to take on the investments in this account, the total net value of all the assets associated with this account, and so on. |
The mapping details for the Trading and Investments Contract T2Ts are given here.
SOURCE TABLE NAME |
Logical Stage Table Name |
Fact Table Name |
Logical Fact Table Name |
T2T Name |
|---|---|---|---|---|
STG_INVESTMENTS |
Stage Investments |
FCT_COMMON_ACCOUNT_SUMMARY |
Fact Common Account Summary |
T2T_STG_INVESTMENTS_CAS |
STG_MUTUAL_FUNDS |
Stage Mutual Funds |
FCT_COMMON_ACCOUNT_SUMMARY |
Fact Common Account Summary |
T2T_STG_MUTUAL_FUNDS_CAS |
STG_FUND_CIS_COMPOSITION |
Stage Fund CIS Composition |
FCT_FUND_CIS_COMPOSITION |
Fact Fund CIS Composition |
T2T_FCT_FUND_CIS_COMPOSITION |
STG_INSTRUMENT_RATING_DETAILS |
Stage Instrument Rating Details |
FCT_INSTRUMENT_RATING_DETAILS |
Fact Instrument Rating Details |
T2T_FCT_INSTRUMENT_RATING_DETAILS |
STG_INSTRUMENT_MARKET_PRICES |
Stage Instrument Market Prices |
FCT_INSTRUMENT_MARKET_PRICES |
Fact Instrument Market Prices |
T2T_FCT_INSTRUMENT_MARKET_PRICES |
STG_TRADING_ACCOUNT |
Stage Trading Account |
FCT_COMMON_ACCOUNT_SUMMARY |
Fact Common Account Summary |
T2T_STG_TRADING_ACCOUNT_CAS |
For information about Instrument Options, see Guaranteed Benefits and Embedded Options and Embedded Options Tables.
Macroeconomic variables are the indicators that point to the current trends in the economy and the factors affecting the economy. These variables are used to get behavioral data of the market systems operating on a large scale and to understand how different sectors of the economy are related to each other.
OIDF supports the data storage of different macroeconomic variable values across different regions. This stored data contains both historical values and future projections. This data is used in LLFP (OFS IFRS Application Pack) to compute the probability of default using the Cox proportional-hazards model.
Macroeconomic Variable Dimension table name and its description are given here.
Logical Dimension Table Name |
Dimension Table Description |
|---|---|
Macroeconomic Variable Dimension |
This table stores dimension data related to the macroeconomic variables. This table is seeded by the LLFP application (OFS IFRS Application Pack). It contains different macroeconomic variable names, descriptions, and the type of value (absolute or percent). |
The mapping details for the Macroeconomic Variable Dimension table is given here.
Map Reference Number |
Source Table Name |
Logical Stage Table Name |
Dimension Table Name |
Logical Dimension Table Name |
|---|---|---|---|---|
852 |
STG_MACRO_ECONOMIC_VARIABLE |
Stage Macro Economic Variable |
DIM_MACRO_ECONOMIC_VARIABLE |
Macroeconomic Variable Dimension |
Macroeconomic Variable T2T and its description are given here.
T2T Name |
T2T Description |
|---|---|
T2T_FCT_MACRO_ECONOMIC_VAR_DTLS |
This T2T stores the data for different macroeconomic variable values across different regions. This T2T contains both historical values and future projections. This data is used in LLFP (OFS IFRS Application Pack) to compute the probability of default using the Cox proportional-hazards model. |
The mapping details for Macroeconomic Variable T2T is given here.
Source Table Name |
Logical Stage Table Name |
Fact Table Name |
Logical Fact Table Name |
T2T Name |
|---|---|---|---|---|
STG_MACRO_ECONOMIC_VAR_DTLS |
Stage Macro Economic Variable Details |
FCT_MACRO_ECONOMIC_VAR_DTLS |
Fact Macro Economic Variable Details |
T2T_FCT_MACRO_ECONOMIC_VAR_DTLS |
Capital Instruments Position and Transactions tables pertain to the capital instrument and transactional part of the balance sheet of a financial institution. It covers capital instruments such as Equity and Debt along with non-retail borrowings done by financial institutions.
Capital Instruments Position and Transactions T2Ts and their description are given here.
T2T Name |
T2T Description |
|---|---|
T2T_FCT_CAP_INSTR_POSITIONS |
This T2T stores the regulatory position of capital instruments and details of treatment to the capital instruments under I and III regulations. |
T2T_FCT_CAP_INSTR_TXNS |
This T2T stores the transactions on the capital instruments. The entity stores settled and unsettled transactions differentiated by mnemonic codes. It also stores the transactions that are settled with cash or with other means such as securities. |
T2T_FCT_INSTR_PROPOSED_TXNS |
This T2T stores the proposed set of instruments that will be transacted by the Insurance Company. |
The mapping details for the Capital Instruments Position and Transactions T2Ts are given here.
Source Table Name |
Logical Stage Table Name |
Fact Table Name |
Logical Fact Table Name |
T2T Name |
|---|---|---|---|---|
STG_CAP_INSTR_POSITIONS |
Stage Capital Instrument Positions |
FCT_CAP_INSTR_POSITIONS |
Fact Capital Instrument Positions |
T2T_FCT_CAP_INSTR_POSITIONS |
STG_CAP_INSTR_TXNS |
Stage Capital Instrument Transactions |
FCT_CAP_INSTR_TXNS |
Fact Capital Instrument Transactions |
T2T_FCT_CAP_INSTR_TXNS |
STG_INSTR_PROPOSED_TXNS |
Stage Instrument Proposed Transactions |
FCT_INSTR_PROPOSED_TXNS |
Fact Instrument Proposed Transactions |
T2T_FCT_INSTR_PROPOSED_TXNS |
Other Common Account Summary T2Ts and their description, and T2T mapping details are given here.
The target table is Fact Common Account Summary (FCT_COMMON_ACCOUNT_SUMMARY).
Source Table Name |
Logical Stage Table Name |
T2T Name |
T2T Description |
|---|---|---|---|
STG_REPO_CONTRACTS |
Stage Reporting Contracts |
T2T_STG_REPO_CONTRACTS_CAS |
This T2T stores the details of repurchase contracts. |
STG_CUSTODIAL_ACCOUNTS |
Stage Custodial Accounts |
T2T_STG_CUSTODIAL_ACCOUNTS_CAS |
This T2T stores the details of all accounts held in a custodial capacity. |
STG_MANAGED_INV_ADV |
Stage Managed Investment Advances |
T2T_STG_MANAGED_INV_ADV_CAS |
This T2T stores the details of all managed investment account and other services. |
STG_TRUSTS |
Stage Trusts |
T2T_STG_TRUSTS_CAS |
This T2T stores the trust accounts held by the financial institution of their customers. The Trust account acts as a parent account for the savings or deposits held underneath and maintained in CASA or TD tables. |
Customer account-level data from the Oracle Financial Services Analytical Applications (OFSAA) staging product processor tables must be consolidated into a standardized relational Business Intelligence (BI) data model. This consolidation is done to have all the staging product processor table data in a single Fact table. The Common Account Summary tables data can be used for building cubes, which allow rollup of data for a dimension or a combination of dimensions. This relational BI model consists of the following vertically partitioned Common Account Summary tables that are organized by the application subject area.
The preceding Common Account Summary tables are part of the data model but there are no seeded T2T definitions available to populate these tables. T2T processes must be custom configured to populate these tables to use measures defined on these tables for reporting.
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.1.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
Follow this SCD process to populate data into a Dimension table:
NOTE:
You can also follow this SCD process to populate data into any Hive-related Dimension table.
1. To populate data into a Dimension table, execute the SCD batch. For a detailed procedure, see the Slowly Changing Dimension (SCD) Process.
2. To check the SCD batch execution status of a Dimension table, follow the procedure Check the Execution Status of the SCD Batch.
3. To verify log files, and check the error messages (if any), follow the procedure Verify Log Files and Check Error Messages.
NOTE:
Ensure to load FCT_COMMON_ACCOUNT_SUMMARY and FSI_EXCHANGE_RATES tables prior to loading any of the other Account Summary tables.
Ensure to manually configure the SETUP_MASTER table with required GAAP_CODEs before executing Account Summary Population T2Ts. For an account, load only one GAAP_CODE to Fact Common Account Summary table. By default, OIDF installer seeds the following entry into SETUP_MASTER. When executing through a batch, the RUNSkey defaults to -1.
Follow this T2T process to populate data into any T2T Result table:
NOTE:
Only RDBMS T2Ts can be executed using the PMF.
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.