1. User Guide
  2. Data Loaders
  3. Prepayment Rate Data Loader
  4. Prepayment Rate Data Loader

5.5.2 Prepayment Rate Data Loader

The Prepayment Rate Data Loader program populates the target OFSAA Prepayment Model table with the values from the staging table. The procedure will load prepayment rate data for a specified Prepayment Model rule or all Prepayment models that are present in the staging table. The program assumes that the Prepayment Model assumptions have already been defined using OFSAA Prepayment Model rule UIs before loading Prepayment Model rates.

The program performs the following functions:

  • The program performs certain checks to determine if:

The Prepayment Model dimensions present in staging are the same as those present in the OFSAA Prepayment Model metadata tables.

The bucket members of each of the dimensions present in staging are same as those present in the metadata tables.

The number of records present in the STG_PPMT_MODEL_HYPERCUBE table for a Prepayment Model is less than or equal to the maximum number of records that are allowed, which is determined by multiplying the number of buckets per dimension of the Prepayment Model.

PPMT_MDL_SYS_ID DIMENSION_ID NUMBER_OF_BUCKETS
20100405 8 2
20100405 4 3

Then the maximum number of records = number of buckets of dimension 8 * number of buckets of dimension 4

That is, maximum number of records = 2 * 3

Therefore, maximum number of records = 6 records

Check is made by Prepayment Rate Data Loader whether the number of records present in STG_PPMT_MODEL_HYPERCUBE table for a Prepayment model 20100405 is less than or equal to 6 or not.

  • If the above quality checks are satisfied, then the rates present in the Staging table are updated to the OFSAA prepayment model metadata table.
  • Any error messages are logged in the FSI_MESSAGE_LOG table and can be viewed in OFSAAI Log Viewer UI.

After the Prepayment Rate loader is completed, you should query the FSI_PPMT_MODEL_HYPERCUBE table to look for the new rates. Also, you can verify the data using the Prepayment Model Assumption UI.

Populating the data into STG_PPMT_MODEL_HYPERCUBE

  • V_PPMT_MDL: The Name of the Prepayment Model as stored in FSI_M_OBJECT_DEFINITION_TL table. If Prepayment Model name is given, also provide the Folder name. If the Prepayment Model System ID is provided, then populate this field with -1.
  • N_ORIG_TERM: Original term of the contract
  • N_REPRICING_FREQ: The number of months between instrument repricing
  • N_REM_TENOR: Remaining term of the contract (in Months)
  • N_EXPIRED_TERM: Expired term of the contract (in Months)
  • N_TERM_TO_REPRICE: Repricing term of the contract (in Months)
  • N_COUPON_RATE: The current gross rate on the instrument
  • N_MARKET_RATE: Forecast rate representing alternate funding
  • N_RATE_DIFFERENCE: Spread between the current gross rate and the market rate
  • N_RATE_RATIO: Ratio of the current gross rate to the market rate
  • N_PPMT_RATE: User defined prepayment rate for the associated dimension value combination
  • FIC_MIS_DATE: The As of Date for which the data being loaded is applicable
  • V_FOLDER_NAME: Name of the Folder which holds the Prepayment Model. If the Prepayment Model System ID is provided, then populate this field with -1. If Folder name is provided, then provide Prepayment Model name as well.
  • N_PPMT_MDL_SYS_ID: The System Identifier (Object Definition ID) of the Prepayment Model to which this data will be loaded. If Prepayment Model name and Folder are provided, then populate this field with -1.

Column mapping from source to target

Source STG_PPMT_MODEL_HYPERCUBE to Target FSI_PPMT_MODEL_HYPERCUBE mapping:

N_ORIG_TERM -> ORIGINAL_TERM

N_REPRICING_FREQ ->REPRICING_FREQ

N_REM_TENOR -> REMAINING_TERM

N_EXPIRED_TERM -> EXPIRED_TERM

N_TERM_TO_REPRICE -> TERM_TO_REPRICE

N_COUPON_RATE -> COUPON_RATE

N_MARKET_RATE -> MARKET_RATE

N_RATE_DIFFERENCE -> RATE_DIFFERENCE

N_RATE_RATIO -> RATE_RATIO

N_PPMT_RATE -> PPMT_RATE

N_PPMT_MDL_SYS_ID -> PPMT_MDL_SYS_ID when N_PPMT_MDL_SYS_ID <> -1, otherwise it performs a lookup in FSI_M_OBJECT_DEFINITION_TL based on the Name and Folder provided in the staging tabl

Example

Based on the FSI_M_PPMT_MODEL table, for data in the staging table with Prepayment Model System ID 20100405:

PPMT_MDL_SYS_ID DIMENSION_ID NUMBER_OF_BUCKETS

20100405 8 2

20100405 4 3

The maximum number of records = (number of buckets of dimension 8) * (number of buckets of dimension 4).

That is, maximum number of records = 2 * 3

Therefore, maximum number of records = 6 records.

The Prepayment Rate Data Loader checks whether the number of records present in STG_PPMT_MODEL_HYPERCUBE table for Prepayment Model 20100405 is less than or equal to 6.

If the above quality checks are satisfied, then the rates present in the Staging table are updated to the OFSAA Prepayment Model metadata table.

Any error messages are logged in the FSI_MESSAGE_LOG table.