This section includes the following topics:
· OFS ALM - OFS LRM Cash Flow Integration
· Create/Execute LRM Batch from Command Line
· Updating Port Changes in OFS LRM
· Setup Master Table Configuration
· Business Assumption Data Maintenance
· Run Management Data Maintenance
· Executing Intraday Post Load Batch and Intraday Real Time Run
· Performance Improvement Guidelines
· Generating Download Specifications
This section is applicable only if a bank has both OFS Asset Liability Management and OFS Liquidity Risk Management installed and if the cash flows generated by OFS ALM are to be considered for calculation within OFS LRM. The following steps are required to be performed to consider the cash flows generated by OFS ALM for LRM computations:
1. Open the Application Preferences window in the Oracle Financial Services Analytical Applications Infrastructure window. Choose Risk Applications > Liquidity Risk Management > Application Preferences on the Left-Hand Side (LHS) menu.
2. Select one or multiple cash flow generation processes from the Contractual Cash Flow Process Selection section. This selection enables OFS LRM to use the contractual cash flows generated by OFS ALM for further computations. See the Contractual Cash Flow Selection section for more information.
NOTE:
For LRM cash flow, every product has a unique scenario that is, for the same product scenarios are not repeated.
See the ALM LRM Integration page in the OFS Liquidity Risk Management V8.1.0.0.0 Run Chart for details on the batches to be executed to populate the cash flows from ALM output tables.
3. Once these batches are executed, verify whether the data for the selected process IDs is appearing in the Fact Process Cash Flow table.
4. LRM provides out-of-the-box time bucket definitions which are seeded in the DIM_RESULT_BUCKET table. Before defining any ALM time bucket, ensure to increase the sequence SEQ_DIM_RESULT_BUCKET.nextval value to the max+1 value of the N_RESULT_BUCKET_SKEY of DIM_RESULT_BUCKET.
To generate the execution IDs, the following steps can be followed by passing all requisite parameters at command line. Follow these steps, to create or execute LRM batch from command line:
1. Navigate to the $FIC_APP_HOME/icc/bin directory and open the lrmExecParams.properties file.
2. Provide all the required parameters. All Parameters are mandatory, except for the Contractual Run Execution ID which is required only for BAU/Stress Run.
a. RUNID:
— For Contractual Run and BAU Run, the N_RUN_OBJECT_ID (FSI_LRM_RUN_PARAM.N_RUN_OBJECT_ID) of the run definition you are trying to execute.
Example: If the name of the Run definition created is TEST2131, execute the following query to get the n_run_object_id.
SELECT T.N_RUN_OBJECT_ID FROM FSI_LRM_RUN_PARAM T WHERE T.V_RUN_NAME LIKE 'TEST2131';
— For Stress Run, the ID of the Stress Run created (ST_STRESS_MASTER.V_STRESS_ID)
b. RUNTYPE: The type code of the Run to be executed is as follows:
— 10: Contractual Run; 11: BAU Run; 12: Stress Run
c. INFODOM: Enter the name of the INFODOM which is in use.
d. FICMISDATE: Enter the FIC MIS date to be used for execution.
e. RUNEXECDESC: Enter the description for the execution.
f. CONTRAEXECID: For BAU/Stress Run, enter the ID of the Contractual Run execution to be used. The following query can be used to find the various Contractual Run Execution IDs:
NOTE:
Choose any value of ITEM_ID column as an input for CONTRAEXECID from the result of the query.
g. REPCURRENCY: Enter the code of the reporting currency to be used. The currency code can be found using the following query:
SELECT V_ISO_CURRENCY_CD, V_CURRENCY_NAME FROM DIM_CURRENCY ORDER BY UPPER(V_CURRENCY_NAME)
h. EXCHGRATESRC: Enter the code of the exchange rate source to be used. The code can be found using the following query:
SELECT LOOKUP_CD, LOOKUP_DESCRIPTION FROM FSI_LRM_LOOKUP_TL T WHERE T.CATEGORY_ID = 19
i. LCRHORIZON: Enter the LCR Horizon (in days) to be used. The default provided is 30.
j. USER: Enter the OFSAA application user name.
k. EXECUTE: Enter Y/N. Here, Y= Execute Run and N=Create Batch Only.
3. Run the lrmBatch.sh file to create or execute a batch. Provide the input parameter as EXECUTE=Y to execute the batch.
4. Ensure that the wsdl URL is replaced with correct values in the LRMWSservices.properties file under the $FIC_APP_HOME/icc/lib LRM_WSDL_LOCATION=$PROTOCOL$://$WEBSERVERHOST$:$WEBSERVERPORT$/$CONTEXT$/lrmService?wsdl directory.
If you refer to the OFS AAI document on how to configure infrastructure ports and execute it, the changes reflect only in the OFS AAI configuration files but not OFS LRM files.
It is recommended to change the LRM_WSDL_LOCATION port number manually in the $FIC_APP_HOME/icc/lib/LRMWSservices.properties file with the new servlet port. Note that this change is required only when the servlet port is changed, otherwise it is not required to update the file.
The setup master table must be configured for calculating LCR Option 2. The setup master table configuration is as follows.
Column Name |
Component Description |
Example Component Value |
Comment |
|---|---|---|---|
LCR_OPT2_MAX_THRESHOLD_EXEMPT_HAIRCUT_PCT |
Maximum threshold percent which is exempt from haircut for LCR option 2 |
25 |
The maximum threshold haircut percentage which needs to be exempted must be entered and this should be a whole value and not a percentage. |
LRM4G_HOL_CODE |
US 4G Holiday Code |
1 |
The holiday code which needs to be used for "US FR2052a Run" and "US FR2052a Run" Runs. |
LRM_STD_CCY_CD |
Standard Currency used in currency conversion |
USD |
If reporting currency is not selected, then the default value provided here is used. |
OPTION3_HAIRCUT |
Additional Haircut required for option 3 LCR |
0.1 |
This setup parameter is used for BIS run. This is the haircut percent for option type 3 specified in decimals. (10% will be given as 0.1). |
DIM_PRODUCT_BALANCE_SHEET_CATEGORY_ASSET |
Place Holder for Asset |
ASSET |
This parameter is to identify the ASSET products in the balance sheet. This should be same as the balance category of assets which is given as download in STG_PRODUCT_MASTER. These values will be used in DTs to identify ASSET products. |
DIM_PRODUCT_BALANCE_SHEET_CATEGORY_LIABILITY |
Place Holder for LIABILITY. |
LIABILITY |
This parameter is to identify the LIABILITY products in the balance sheet. This should be same as the balance category of liabilities which is given as download in STG_PRODUCT_MASTER. These values will be used in DTs to identify LIABILITY products. |
DIM_PRODUCT_BALANCE_SHEET_CATEGORY_OFF_BAL_SHEET |
Place Holder for OFF BALANCE SHEET. |
OFF BALANCE SHEET |
This parameter is to identify the OFF BALANCE SHEET products in the balance sheet. This should be same as the balance category of off-balance sheet items which is given as download in STG_PRODUCT_MASTER. These values will be used in DTs to identify OFF BALANCE SHEET products. |
DT_ALLOC_HINT_MATERIALIZE |
Appends /*+ materialize */ hint in the SELECT statement of the Allocation Engine Merge Query. |
N |
This is an optional performance enhancement parameter. If this parameter is set to ‘Y’ then the /*+ materialize */ hint will be added to the backend query. |
DT_ALLOC_HINT_USE_HASH |
In Allocation Engine Merge Query , whether to use /*+ USE_HASH */ to merge in FCT_AGG_CASH_FLOWS table. |
Y |
This is an optional performance enhancement parameter. If this parameter is set to Y, then the /*+ USE_HASH */ hint will be added to the backend merge query. |
DT_FSI_EXCHANGE_RATE_HIST |
The entry decides which lookup exchange rates table to be used in Aggregate Cash Flows Population. Y = FSI_EXCHANGE_RATES and N = FCT_FCST_EXCHANGE_RATES |
Y |
A setup entry which will decide if the spot exchange rates or forecast exchange rate must be used. If the parameter is set to Y spot exchange rate will be used (from FSI_EXCHANGE_RATES) else the forecasted exchange rate will be used (from FCT_FCST_EXCHANGE_RATES). |
DT_PARALLEL_DOP |
Degree of parallelism to be used in DML and Queries statements in data transformations. |
8 |
This is a performance enhancement parameter given as an option to the user. The number specifies the degree of parallelism to be set for the session when the backend query is executing. This parameter will be considered only if DT_PARALLEL_ENABLE parameter is set to Y. |
DT_PARALLEL_ENABLE |
Enables parallel sessions for DML and Queries statements in data transformations. |
Y |
This is a performance enhancement parameter given as an option to the user. If the parameter is set to Y parallel execution will be enabled for the backend query. This parameter along with DT_PARALLEL_DOP will decide the parallelism. |
LRM4G2A_TIME_BKT_SYS_ID |
Time Bucket Definition sys ID for US 4G 2A. |
-1234567 |
For 4G 2A reporting, the time bucket selected must be specified here. The time bucket sys ID must be given here. -1234567 is the out of the box time bucket sys ID. If you choose to customize it with a new time bucket, the corresponding time bucket sys ID mustbe specified here. |
LRM4G2B_TIME_BKT_SYS_ID |
Time Bucket Definition sys ID for US 4G 2B. |
-1234568 |
For 4G 2B reporting, the time bucket selected must be specified here. The time bucket sys ID must be given here. -1234568 is the out of the box time bucket sys ID. If you choose to customize it with a new time bucket, the corresponding time bucket sys ID must be specified here. |
LRM_24_MONTH_LOOKBACK_HIST |
Contains a boolean value to specify if it is a historical load. 1 specifies historical. Default 0. |
1 |
This parameter is used while computing 24-month look back for maximum 30-day net cash flows. If the parameter is set to 1 then 24 months population (historical) will be done. If the parameter is 0 then only the maximum cash flow for the last 30 days will be populated to FSI_MTM_COLL_VAL_CHANGE. |
LRM_PROD_LIST_FOR_ESTABLISHED_REL |
This entry specifies the list of products to be considered for established relationship identification. The standard product type code (Level 1 Code) should be given comma separated like LOANS, DEP, ANNUITY. |
LOANS, DEP |
This parameter specifies the list of products to be considered for established relationship identification. The standard product type code (Level 1 Code) should be given comma separated like LOANS, DEP, ANNUITY. |
OPTION3_HAIRCUT |
Additional Haircut required for option 3 LCR. |
0.1 |
This setup parameter is used for BIS run. This is the haircut percent for option type 3 specified in decimals. (10% will be given as 0.1). |
LRM_CASHFLOW_PROCESS_SCEN_SKEY |
Surrogate Key for the Process Scenario (N_PROC_SCEN_SKEY) of table FCT_PROCESS_CASHFLOW. |
-1 |
If user wants to use OFS ALM cash flows for LRM processing then user has to enter the scenario skey of ALM cash flows which needs to be considered for LRM processing. LRM will filter the records based on scenario filter and process on these records. |
STRUCTURED_OUTFLOW_COMP |
Credit Line purpose to be consider for computing Structured Outflow Amount Computation. |
SPONS |
This parameter takes the V_STANDARD_PRODUCT_TYPE_CODE to identify the products which are considered as Credit Line Purpose for computing the Structured Outflow Amount. |
FR2052A_REPORTERS_DESCRIPTION |
This signifies the total consolidated assets and on-balance sheet exposures of the reporting firm. |
1 |
The values for this column name must be based on the U.S. firms asset size and 1, 2, 3 signifies the following respectively. U.S. firms with ? $700 billion in assets or ? $10 trillion in assets under custody; FBOs identified as LISCC firms. U.S. firms with ? $250 billion in assets or ? $10 billion in foreign exposure; FBOs with ? $250 billion in U.S. assets U.S. firms with < $250 billion in assets and < $10 billion in foreign exposure; FBOs with < $250 billion in U.S. assets. |
FR2052A_REPORTING_SI10 |
U.S. firms with less than $700 billion in total consolidated assets and less than $10 trillion in assets under custody and FBOs with less than $250 billion in U.S. assets have the option of not reporting this product. |
Y |
To report S.I.10, this flag must be Y, otherwise set it as N. |
FR2052A_REPORTING_SI12 |
U.S. firms with less than $700 billion in total consolidated assets and less than $10 trillion in assets under custody and FBOs with less than $250 billion in U.S. assets have the option of not reporting this product. |
Y |
To report Supplemental S.I.12, this flag must be Y, otherwise set it as N. |
FR2052A_REPORTING_SI14 |
U.S. firms with less than $700 billion in total consolidated assets and less than $10 trillion in assets under custody and FBOs with less than $250 billion in U.S. assets have the option of not reporting this product. |
Y |
To report Supplemental S.I.14, this flag must be Y, otherwise set it as N. |
FR2052A_REPORTING_SI7_SI8 |
If the total amount reported is related to distinct disputes over the previous year for products S.I.7 and S.I.8 is less than $500 million, the reporting firm need not report this product. |
Y |
To report Supplemental S.I.7, S.I.8, this flag must be Y, otherwise set it as N. |
BIS_REP_CRY |
BIS_REP_CRY. |
EUR |
This parameter captures the regulatory limit for the total aggregated funding raised from small business customers who are to be treated similar to the retail customers while identifying deposit stability and applying run-off rates within LCR computations. Any SME customer providing an aggregate funding up to the limit specified as part of this attribute is accorded the same treatment as a retail customer for the purposes of LCR computation. Any SME customer providing an aggregate funding greater than this limit is treated as a wholesale customer. The seeded value for this is 1 million as per BCBS 238. |
BIS_SMALL_BUIS_LIMIT |
BIS Small Business EOP Balance Limit. |
1000000 |
This parameter captures the currency code in which the regulatory limit for the total aggregated funding raised from small business customers who are to be treated similar to the retail customers, that is, BIS_SMALL_BUIS_LIMIT is captured. The seeded value for this is EUR as per BCBS 238. |
SIGNIFICANT_CURRENCY_LIABILITY |
Currency Significance Percentage. |
0.05 |
This attribute indicates the definition of a significant currency in terms of the percentage of the aggregate liabilities denominated in that currency over the total liabilities of the bank. A currency is considered “significant” if the aggregate liabilities denominated in that currency amount to 5% or more of the bank's total liabilities. The default value provided for this field is 0.05 (5%) as per the regulatory definition. |
CASHFLOW_NSFR_BANDS |
Hierarchy for NSFR Cash Flow Interval. |
HLRMNS02 |
The entry for cash flow interval dimension into the setup master table consists of the Hierarchy code related to the bands pertaining to NSFR. The hierarchy code HLRMNS02 contains the NSFR cash flow interval bands. |
BIS_GAAP_CODE |
This entry captures the GAAP code to be considered while moving data from STG tables to processing table for BIS jurisdiction. |
INGAAP |
This entry captures the GAAP code to be considered while moving data from STG tables to processing table for the purpose of regulatory computations as per the BIS jurisdiction. DIM_GAAP is the corresponding dimension table. However, DEFAULT_GAAP entry from the setup_master table continues to be considered for DIM_ACCOUNT population. |
LRM_APP_PREF_BASEL_RUN_ID
|
Basel Regulatory Run Id Selected in Application Preference. |
-1 |
The component value stores the executed basel run skey which is used during the population of Standard Accounting head information for NSFR computation. |
EBA_GAAP_CODE |
This entry captures the GAAP code to be considered while moving data from STG tables to processing table for EBA jurisdiction. |
UKGAAP |
This
entry captures the GAAP code to be considered while moving data
from STG tables to processing table for the purpose of regulatory
computations as per the EBA jurisdiction. |
RBI_GAAP_CODE |
This entry captures the GAAP code to be considered while moving data from STG tables to processing table for RBI jurisdiction. |
INGAAP |
This entry captures the GAAP code to be considered while moving data from STG tables to processing table for the purpose of regulatory computations as per the RBI jurisdiction. DIM_GAAP is the corresponding dimension table. However, DEFAULT_GAAP entry from the setup_master table continues to be considered for DIM_ACCOUNT population. |
CALC_USED_PORTION_PLCD_COLL_USING_MKT_VALUE |
This entry captures the run purposes where used portion of placed collaterals is calculated using market values. Any new run purpose should be added, separated by comma. |
3,7,16,18 |
This entry captures the list of Run purposes where the used portion of placed collaterals is calculated using market values. The default values for these are the Run Purpose codes corresponding to the Run Purposes Basel III Liquidity Ratios Calculation, and RBI Basel III Liquidity Ratio Calculation, EBA Delegated Act Liquidity Ratio Calculation, and BOT Liquidity Ratio Calculation. If this calculation is to be done for any new run purpose, its Run Purpose code should be added to this list, separated by commas. |
CALC_SLR_PLCD_COLL |
This entry captures the run purposes where the SLR amount from placed collaterals is calculated. Any new run purpose should be added, separated by comma. |
7 |
This entry captures the Run purposes where the SLR eligible placed collaterals are to be identified and the SLR eligible amount is calculated. Currently the default value is 7, which corresponds to the RBI Run Purpose. If any other Run Purpose needs this calculation, its Run Purpose Code should be added, separated by comma. |
HIST_OPERATIONAL_BAL_CALC_UPD |
Operational Balance value calculation through the historical balance approach. |
Y |
This entry captures the approach for identifying operational portion of accounts classified as operational deposits. A value of Y indicates that the operational balance is calculated by the application using the historical balance approach where the historical time window to be considered is specified as part of the setup_master parameter DAYS_HIST_OPER_BAL_CALC_UPD. A value of N indicates that such operational balance is available as a download. |
DAYS_HIST_OPER_BAL_CALC_UPD |
Number of Days considered for calculating Operational Balance value through the historical balance approach. |
90 |
This entry captures the historical time window to be considered while calculating the operational portion of accounts classified as operational deposits based on the historical balance approach. This value is captured in terms of days. |
LRM_RBI_SME_AGG_FUNDING_AMT |
Funding Limit. |
50000000 |
This entry captures specifies the aggregate funding threshold for identifying the counterparties as SME’s based on limit specified by the regulator. The amount mentioned here is considered in INR. |
SIGNIFICANT_COUNTERPARTY_LIAB |
Significant Counterparty - Percentage of Total Liability. |
0.01 |
This entry captures the minimum threshold, as a percentage of the total liability amount, for a counterparty to be considered significant. The default value is 0.01 which implies that if the total liabilities from a given counterparty are in excess of 1% of the total liabilities of the legal entity, the counterparty is considered significant. |
CALC_ESLR_COLL |
This entry captures the run purposes where the market value for the ESLR staging types with net CBLO collaterals are calculated. Any new run purpose should be added, separated by comma. |
7 |
This entry captures the run purposes where the market value for the ESLR staging types with net CBLO collaterals are calculated. Any new run purpose should be added, separated by comma. |
DAYS_HIST_OPER_BAL_CALC_UPD |
Number of Days considered for calculating Operational Balance value through the historical balance approach. |
90 |
Number of Days considered for calculating Operational Balance value through the historical balance approach. |
EBA_AGG_DEPOSIT_THRESHOLD |
This attribute captures the deposit threshold for treating the deposits from an SME equivalent to retail deposits as per EBA guidelines. As per the EBA Delegated Act, liabilities from an SME would be treated similar to retail deposits where the aggregate deposits by such SME or company on a group basis do not exceed EUR 1 million. |
1000000 |
This attribute captures the deposit threshold for treating the deposits from an SME equivalent to retail deposits as per the EBA guidelines in terms of Euros. This relates to the threshold for aggregate deposits from a given customer, beyond which an SME would not be treated similar to retail deposits. For instance, if the value is specified as 1000000, any SME whose aggregate deposits do not exceed EUR 1000000 is treated similar to a retail customer. |
EBA_CUST_EMPLOYEES_LIMIT |
This attribute captures the employee threshold for considering a particular party as an SME as per EBA guidelines. The EBA SME definition states that an enterprise must employ fewer than 250 employees to be classified as a small and medium-sized enterprise (SME). |
250 |
This attribute captures the employee threshold for considering a particular party as an SME as per EBA guidelines. This relates to the EBA SME definition which provides the employee threshold for an enterprise beyond which an enterprise is not eligible to be classified as an SME. For instance, if the value is provided as 250, then the application will consider only enterprises that employs less than 250 employees as an SME. |
EBA_HQLA_ISSUE_SIZE_THRESHOLD_1 |
This attribute captures the first issue size threshold for classifying a covered bond or debt security as an HQLA in terms of Euros. For a non-Euro denominated security, the application will convert the issue size to Euros prior to comparing it with this value. |
500000000 |
This attribute captures the first issue size threshold for classifying a covered bond or debt security as an HQLA in terms of Euros. For a non-Euro denominated security, the application will convert the issue size to Euros prior to comparing it with this value. |
EBA_HQLA_ISSUE_SIZE_THRESHOLD_2 |
This attribute captures the second issue size threshold for classifying a covered bond or debt security as an HQLA in terms of Euros. For a non-Euro denominated security, the application will convert the issue size to Euros prior to comparing it with this value. |
250000000 |
This attribute captures the second issue size threshold for classifying a covered bond or debt security as an HQLA in terms of Euros. For a non-Euro denominated security, the application will convert the issue size to Euros prior to comparing it with this value. |
EBA_TOTAL_CUSTOMER_ANNUAL_SALES |
This attribute captures the annual turnover threshold for considering a particular party as an SME as per EBA guidelines. The EBA SME definition states that an enterprise should have an annual turnover not exceeding EUR 50 million to be classified as a small and medium-sized enterprise (SME). |
50000000 |
This attribute captures the annual turnover threshold for considering a particular party as an SME as per EBA guidelines in terms of Euros. This relates to the EBA SME definition which provides the threshold for the annual turnover beyond which an enterprise is not eligible to be classified as an SME. For instance, if the value is provided as 50000000, then the application will consider only enterprises whose annual turnover is up to this value as an SME. |
EBA_TOTAL_CUSTOMER_ASSETS |
This attribute captures the annual balance sheet threshold for considering a particular party as an SME as per EBA guidelines. The EBA SME definition states that that an enterprise should have an annual balance sheet total not exceeding EUR 43 million to be classified as a small and medium-sized enterprise (SME). |
43000000 |
This attribute captures the annual balance sheet threshold for considering a particular party as an SME as per EBA guidelines in terms of Euros. This relates to the EBA SME definition which provides the threshold for the annual balance sheet total beyond which an enterprise is not eligible to be classified as an SME For instance, if the value is provided as 43000000, then the application will consider only enterprises whose annual balance sheet total is up to this value as an SME. |
FALLCR_PERCENT |
SLR Securities Allowable under FALLCR in Percentage. |
15 |
This attribute captures the SLR eligible securities allowed to be included in the stock of HQLA under the Alternative Liquidity Approach allowed by RBI, the Facility to Avail Liquidity for Liquidity Coverage Ratio (FALLCR), as a percentage of the Net Demand and Term Liabilities (NDTL). |
HIST_OPERATIONAL_BAL_CALC_UPD |
Operational Balance value calculation through the historical balance approach. |
Y |
The attribute identifies whether users want to compute the operational portion of the deposits classified as operational as per the regulatory criteria using the historical balance approach or provide it as a download. A value Y indicates that the operational balance is to be computed by the application. |
MSF_PERCENT |
SLR Securities Allowable under Marginal Standing Facility in Percentage. |
2 |
This attribute captures the SLR eligible securities allowed to be included in the stock of HQLA under the Marginal Standing Facility, as a percentage of the Net Demand and Term Liabilities (NDTL). |
NSFR_ENABLED |
This enables NSFR option in Run Management screen for mentioned purposes. |
3, 7, 18 |
This attribute enables the NSFR option in the Run Definition window for the Run Purpose Codes provided. |
RBICASHFLOW_NSFR_BANDS |
Hierarchy for RBI NSFR Cash Flow Interval. |
HLRMNS08 |
This hierarchy is setup parameterized as part of the requirement of the development of the UI screen for configurations of NSFR assumptions. This is because hierarchy is with respect to the cash flows intervals which is normally time bucket definitions for the other assumptions. This hierarchy contains the cash flow maturity bands as per RBI requirement. |
ADDI_DER_LIA_RSF |
This attribute captures the percentage factor for the additional portion of derivative liabilities to be included as part of RSF assumption. |
20 |
This attribute captures the percentage factor for the additional portion of derivative liabilities to be included as part of RSF assumption. |
SEC_TRANS_TREATMENT_PURPOSE_VAL |
Secured Transactions Treatment Purpose Value. |
-1: Off 3: On |
The secured transactions treatment purpose feature can be turned ‘ON’ to update the secured and unsecured calculations at account-collateral level. |
LRM_BOT_SME_AGG_FUNDING_AMT |
BOT Funding Limit. |
50000000 |
This entry captures specifies the aggregate funding threshold for identifying the counterparties as SME’s based on limit specified by the regulator. The amount mentioned here is considered in THB. |
LRM_REPLINE_TABLE |
The entry for LRM_REPLINE_TABLE is added in the setup_master table. The dimensions defined in the table FSI_LRM_REP_LINE_ATTR_DETAILS are displayed in the user interface along with the nodes of each hierarchy. |
FSI_LRM_REP_LINE_ATTR_DETAILS |
|
BACK_DATED_RUN_PURPOSE |
This entry captures the run purposes where the back dated functionality is available. |
16, 7, 4, 13, 8, 9, 19 |
This entry captures the run purpose for which backdated is required. Currently it is available for the values: · 16 corresponds to the EBA Run Purpose. · 7, 8, 9 corresponds to the RBI Run Purpose. · 4, 13 correspond to the US FED Run Purpose. · 19 corresponds to the Deposit Insurance Calculations - US Fed Run Purpose. |
NEW_MSF_PERCENT |
SLR Securities Allowable under Marginal Standing Facility in Percentage with effect from March 27 2020 and applicable up to June 30 2020 |
3 |
This attribute captures the SLR eligible securities allowed to be included in the stock of HQLA under the Marginal Standing Facility, as a percentage of the Net Demand and Term Liabilities (NDTL). It is effective from March 27 2020 and applicable up to June 30, 2020. |
The steps for business assumption data maintenance are as follows:
1. Adding Existing Dimension to the Assumption
The steps to add an existing dimension (which is already part of LRM Data Model) in the assumption are as follows:
a. Cash Flow Attribute
If it is a Cash Flow Attribute then, follow these steps:
i. The cash flow attribute must be present in Fact Account Cash flow table and Fact Aggregate Cash flow table with same column name.
ii. The cash flow attribute must be part of the primary key in the Fact Aggregate Cash flow table.
iii. Add an entry to the FSI LRM tabular column metadata table. The mandatory columns have to be filled in FSI LRM tabular column metadata table as shown in the following table:
Column Name |
Example Value |
|---|---|
V_TABLE_NAME |
Should be "FCT_AGG_CASH_FLOWS" |
V_COLUMN_NAME |
Column_Name |
V_DATA_TYPE |
Data type of the column |
F_CONSTRAINT_TYPE_FLAG |
Should be "P". |
b. Account Attribute
You must add an entry in FSI LRM Business Dimensions table and enter the values which are in the following table:
Column Name |
Column Description |
Example Value |
|---|---|---|
n_business_dimension_number |
This attribute stores the Running sequence for the list of business dimensions used. Business dimensions are a set of hierarchies to which liquidity business assumption can be specified. |
56 |
v_dimm_agg_cashflow_col_name |
This attribute stores the physical name of the column in the Fact aggregate cash flow table which represents corresponding business dimensions. |
F_COLLATERAL_COVER_SHORT_POS |
v_dimm_acct_summary_col_name |
This attribute stores the physical name of column in FSI LRM Instrument summary table which represents corresponding business dimension. |
F_COLLATERAL_COVER_SHORT_POS |
v_dimension_table_pk_name |
This attribute stores the physical name of the Primary key column for the dimension table of business dimension used. |
V_FLAG_CODE |
v_dimension_table_name |
This attribute stores the physical name of the dimension table for business dimensions used. |
DIM_BOOLEAN_FLAGS |
v_dimension_hierarchy_code |
This attribute stores the hierarchy code of the business dimensions used. |
HLRM230 |
v_dimension_alias_table_name |
This attribute stores the metadata alias name of dimensions table for business dimensions used in LRM application. Aliases names are created for dimensions like underlying asset level on dimensions asset level or for all flag dimensions on dimension Boolean flag. If the business hierarchy is not created on alias table then this attribute should be empty. |
DIM_COLLATERAL_COVER_SHORT_POS |
v_business_dimension_name |
This attribute stores the name of business dimensions used. |
Collateral Covering Short Position Flag |
v_business_dimension_desc |
This attribute stores the description for business dimensions used. |
Collateral Covering Short Position Flag |
v_business_dimension_code |
This attribute stores the unique code for business dimensions used. |
B037 |
f_selection_flag |
This attribute indicates if corresponding business dimension is selected by user for performing analysis. |
Y |
f_pk_numeric_flag |
This attribute indicates if the primary key column of the physical table of the dimension table is numeric. |
N |
2. Adding a New Dimension
The steps to add a new dimension (which is not present in LRM Data Model) in the assumptions are as follows:
§ New dimensions can be added by including the new dimensions table or creating an alias over the existing dimension table.
§ Create a hierarchy on the dimension table or alias.
a. Cash Flow Attribute
If it is a cash flow attribute, add a column to the following tables:
— Stage Account Cash flow
— Fact Process Cash flow
— Fact Account Cash flow
— Fact Aggregate Cash flows
b. Ensure that the cash flow attribute in the Fact Account Cash Flow table and Fact Aggregate Cash Flow table has the same name.
c. Include as part of the primary key in the Fact Aggregate Cash flow table.
d. Add an entry to the FSI LRM tabular column metadata. The mandatory columns to be filled in FSI LRM tabular column metadata are listed in the following table.
Column Name |
Example Value |
|---|---|
V_TABLE_NAME |
Should be "FCT_AGG_CASH_FLOWS" |
V_COLUMN_NAME |
Column_Name |
V_DATA_TYPE |
Data type of the column |
F_CONSTRAINT_TYPE_FLAG |
Should be "P". |
e. Account Attribute
If it is an account attribute, then add a column to the following tables:
— FSI LRM Instrument
— Fact Aggregate Cash Flow
— FSI LRM Composite Key DIMS
— Fact Business Assumption Audit Trail
This changes the appropriate T2Ts which displays the data from one table to another table.
You must add an entry in FSI LRM Business Dimension and enter the values which are specified in the following table.
Column Name |
Column Description |
Example Value |
|---|---|---|
n_business_dimension_number |
This attribute stores the Running sequence for the list of business dimensions used. Business dimensions are a set of hierarchies to which liquidity business assumptions can be specified. |
56 |
v_dimm_agg_cashflow_col_name |
This attribute stores the physical name of the column in the Fact aggregate cash flow table which represents corresponding business dimensions. |
F_COLLATERAL_COVER_SHORT_POS |
v_dimm_acct_summary_col_name |
This attribute stores the physical name of the column in the FSI LRM Instrument summary table which represents corresponding business dimension. |
F_COLLATERAL_COVER_SHORT_POS |
v_dimension_table_pk_name |
This attribute stores the physical name of Primary key column for the dimension table of business dimension used. |
V_FLAG_CODE |
v_dimension_table_name |
This attribute stores the physical name of the dimension table for business dimensions used. |
DIM_BOOLEAN_FLAGS |
v_dimension_hierarchy_code |
This attribute stores the hierarchy code of business dimensions used. |
HLRM230 |
v_dimension_alias_table_name |
This attribute stores the metadata alias name of dimensions table for business dimensions used. Aliases names are created for dimensions like underlying asset level on dimensions asset level or for all flag dimensions on dimension Boolean flag. If the business hierarchy is not created on an alias table then this attribute should be empty. |
DIM_COLLATERAL_COVER_SHORT_POS |
v_business_dimension_name |
This attribute stores the name of business dimensions used. |
Collateral Covering Short Position Flag |
v_business_dimension_desc |
This attribute stores the description for business dimensions used. |
Collateral Covering Short Position Flag |
v_business_dimension_code |
This attribute stores the unique code for business dimensions used. |
B037 |
f_selection_flag |
This attribute indicates if the corresponding business dimension is selected by user for performing analysis. |
Y |
f_pk_numeric_flag |
This attribute indicates if the primary key column of the physical table of the dimension table is numeric. |
N |
3. Adding tasks to the Assumptions
The following steps add a task to the assumptions registered through LRM Business Assumption window:
You must add an entry in the FSI LRM Assumption Tasks table and enter the values which are specified in the following table.
Column Name |
Column Description |
Example Value |
|---|---|---|
v_task_placement |
This attribute stores the identifier if the task is to be stitched pre-offsetting (offsetting refers to Allocation Engine) or post offsetting (offsetting refers to Allocation Engine) List of values are PRE-OFFSETTING, POST-OFFSETTING. |
POST-ASSUMPTION |
v_task_identifier |
This column stores the unique identifier for the task to be included for the assumption. |
LRMRULE0477 |
v_assumption_sub_category_name |
This attribute stores liquidity business assumption sub-category name. Liquidity business assumption category and sub-category are types of assumptions which are supported and provided out of box. |
72 |
v_assumption_category_name |
This attribute stores liquidity business assumption category name. Liquidity business assumption category and sub-category are types of assumptions which are supported and provided out of box. |
84 |
n_task_sequence |
This attribute stores the sequence in which task is supposed to appear in the Assumption process. |
2 |
v_task_type |
This column stores the type of the task to be included for the assumption. Possible values can be DT, Rule. |
TYPE3 |
v_task_name |
This column stores the name of the task to be included for the assumption. |
LRM - Assumption Application Change Balance Update |
The steps for run management data maintenance are as follows:
1. Adding a process for pre/post assumption processing
In LRM Run management, select the purpose for defining a Run from the Run Definition window. Select the required purpose from the following:
§ Basel III Liquidity Ratios Calculation
§ FR 2052 a Report Generation
§ FR 2052 b Report Generation
§ Long Term Gap Calculation
§ U.S Fed Liquidity Ratio Calculation
Once the purpose is selected and the required parameters are selected, a Run is created.
If it is Contractual Run, the Run stitches the processes. The processes are taken from the FSI LRM Process Purpose Map table. This process map table contains all the processes which are required for a Run definition in LRM. When the Run type is Contractual, all the processes with Run type as contractual and process placement as PRE-ASSUMPTION in FSI LRM Process Purpose Map are stitched as part of Contractual Run.
If it is BAU Run, in addition to the processes which come from the process map table, the processes related to the business assumptions selected are automatically stitched into BAU Run.
For Contractual Run, all the processes in FSI LRM Process Purpose Map must have process placement as PRE-ASSUMPTION only. In BAU, they are both PRE/POST-ASSUMPTION processes.
If you must add an entry in FSI LRM Process Purpose Map table, enter the values which are specified in the following table.
Column Name |
Column Description of the Entry |
Example Value |
|---|---|---|
v_Run_type |
This attribute holds the code for the Run type. The list of values for this column is from FSI_LRM_LOOKUP_TL.LOOKUP_CD with category_id as 1. (filter FSI_LRM_LOOKUP_TL.category_id = 1) |
10 |
v_Run_purpose |
This attribute stores the purpose of the Run. The list of values for this column is from FSI_LRM_LOOKUP_TL.LOOKUP_CD with category_id as 10. (filter FSI_LRM_LOOKUP_TL.category_id = 10). |
2 |
v_process_placement |
This attribute stores the identifier if the process is to be stitched pre assumption or post assumption List of values are "PRE-ASSUMPTION","POST-ASSUMPTION" |
PRE-ASSUMPTION |
n_process_sequence |
This attribute stores the sequence in which the process should appear in the Run. |
1 |
v_Run_type_desc |
This attribute holds the description for values Contractual, BAU, Stress. |
Contractual |
v_Run_purpose_desc |
This attribute stores the description of purpose of the Run. It holds the values are Long Term Gap Calculation, Basel III Liquidity Ratios Calculation, U.S. Fed Liquidity Ratio Calculation, FR 2052a Report Generation, FR 2052b Report Generation. |
Long Term Gap Calculation |
v_rrf_process_object_id |
This attribute stores the unique ID of RRF process which will be part of Run for corresponding purpose and Run type. Execution will include this process in the Run. |
LRM_PROCESS_001 |
v_rrf_process_description |
This attribute stores the description of RRF process which will be part of Run for corresponding purpose and Run type. Execution will include this process in the Run. |
LRM - Party and Product Type Reclassification |
v_folder_name |
This column stores the folder name for which process is defined. |
LRMSEG |
This section details the steps to execute Intraday Post Load Batch and Intraday Real Time Run from Command Line.
1. Environment Prerequisites for External Scheduler Utility
The following are the Environment Prerequisites for External Scheduler Utility:
i. JAVA_HOME (Required) points at JAVA bin installation directory.
ii. ES_HOME (Required) points to the ES Home folder ($FIC_HOME/utility/ES).
iii. Go to the ES Utility directory in $FIC_HOME/utility/ES/conf.
— Update <Infodom>.ini file and specify the values.
— MISDATE= Enter the MIS Date in format mm-dd-yyyy (example: MISDATE=01-31-2010)
iv. USERNAME= Log into OFSAAI (example: USERNAME=BASELUSER)
This must be updated daily once for the MISDATE.
NOTE:
<infodom> in the name of the file <infodom>.ini must be replaced with the infodom name.
a. Intraday - Real Time - Incremental Load Processing
Prerequisite
The following are the prerequisites for Intraday - Real Time - Incremental Load Processing:
i. The Load Run ID must be generated and the corresponding entry is present in the table REV_LOAD_RUN_MASTER.
The Load Run ID population occurs in sequence for every successive staging load. The application recognizes the maximum value of Load Run ID as corresponding to the latest data.
ii. The stage tables must be loaded with the load Run ID.
iii. For Incremental Snapshot Load: For <INFODOM>_INTRADAY_POST_LOAD batch, the parameter passed to Task1 is ‘I’ (By default ‘I’ is present).
The following steps must be followed to execute the Intraday Post Load batch and Intraday -Real Time - Incremental Run:
i. Go to the $FIC_HOME/ficapp/icc/bin directory.
ii. Execute IntradayIncrementalLoadBatch.sh (the execute permission must be provided if needed).
This triggers the Intraday Post Load batch and Intraday -Real Time - Incremental Run.
To automate the incremental load process during the day, this task is appended as the last task while triggering stage table population.
iii. You can view the ES batch logs for individual batch run in the following location:
$ES_HOME/log/ESIC_<batchrunid>.log
iv. You can view the task level logs in the following location:
$FIC_HOME /ficdb/log
/ftpshare/OFSTRINFO/logs
v. All the latest records for the MIS_DATE are moved to reporting.
b. Intraday - Real Time - Complete Snapshot Load Processing
Prerequisite
i. The Load Run ID must be generated and the corresponding entry is present in the REV_LOAD_RUN_MASTER table.
ii. The Load Run ID population occurs in sequence for every successive staging load. The application recognizes the maximum value of Load Run ID as corresponding to the latest data.
iii. The stage tables must be loaded with that load Run ID.
iv. For Complete Snapshot Load: <INFODOM>_INTRADAY_POST_LOAD batch, it must edited through OFSAAI and the parameter ‘S’ is passed to Task1. (By default, ‘I’ is present).
The following steps must be followed to execute the Intraday Post Load batch and Intraday -Real Time - Snapshot Run:
i. Go to the $FIC_HOME/ficapp/icc/bin directory.
ii. Execute IntradaySnapshotLoadBatch.sh (Give execute permission if needed).
This triggers the Intraday Post Load batch and Intraday -Real Time - Snapshot Run.
To automate the incremental load process during the day, this task is appended as the last task while triggering stage table population.
iii. You can view the ES batch logs for individual batch run in the following location:
$ES_HOME/log/ESIC_<batchrunid>.log
iv. You can view the task level logs in the following location:
$FIC_HOME /ficdb/log
/ftpshare/OFSTRINFO/logs
v. All the records corresponding to the latest load Run ID registered in the table REV_LOAD_RUN_MASTER for the MIS_DATE is moved to reporting.
This section explains LRM specific configurations. The migration process from one set up to another is as follows:
NOTE:
For Object Migration, see the OFS Advanced Analytics Infrastructure User Guide which explains the generic configuration.
1. Holiday Calendar
a. Excel import/export functionality is available. You must use this functionality for migration.
See the Excel Import/Export section for information on holiday calendar Excel export/import feature.
2. Time Bucket
Time bucket migration definitions used either in Business Assumption or in Run definition are migrated automatically.
3. Business Assumption
a. Assumptions can be migrated using OFSAAI Command-line Utility to Migrate Objects. To understand the migration process, see the Command-line Utility to Migrate Objects section of the OFSAAI 8.1.0.0.0 User Manual.
b. To export/import LRM Assumption objects, the following object codes and object types must be provided:
OBJECT Code= “Assumption Name” ~ “Version of the assumption”
Type="902"
For example, to export an assumption:
<OBJECTS>
<OBJECT Code="US Unmodified Assumption~1 " Type="902" />
</OBJECTS>
NOTE:
· Once an assumption object is imported, dependent objects like Time bucket definition (if any) are automatically migrated.
· If only assumption name is provided without version number, then the latest version of the given assumption will be migrated.
4. Run Definition:
a. Run Definitions can be migrated using OFSAAI Command-line Utility to Migrate Objects. To understand the migration process, see the Command-line Utility to Migrate Objects section of the OFSAAI 7.3.5.0.0 User Manual.
b. To export/import LRM Run objects, the following object codes and object types must be provided.
OBJECT Code= “Run Name”
Type="901"
For example, to export an assumption:
<OBJECTS>
<OBJECT Code="US Final Run” Type="901" />
</OBJECTS>
NOTE:
Once a Run definition is imported, dependent objects like Time bucket definition, assumptions definitions (in case of BAU or Stress Run) are automatically migrated.
See the OFS LRS Performance Tuning Guide for details.
Data Model for OFS Liquidity Risk Management is available on customer request as an erwin file.
Download specifications can be extracted from this model. See the Data Model Document Generation: Creating Data Dictionary for OFSAA/OFSDF and Download Specifications for OFSAA Staging from ERwin whitepaper for more details.
The additional information are as follows:
· GAAP code is a primary key in staging to support multi-GAAP implementations. The staging product processors to fsi_lrm_instrument T2T’s filter for ‘USGAAP’.
· Load Run ID is part of the primary key in staging with default set to zero.
· The reserved codes for out-of-box assumptions and runs are as follows:
§ The codes for the seeded assumptions are generated starting from a sequence of 9999995000.
§ The codes for the process/rules/measures/dataset/BP (Metadata) starts from the sequence of 9000.
To determine if all operational requirements for eligible HQLA as given in the RBI circular are met, it is expected that the bank must provide the same through f_hqla_eligibility_flag as Y. Apart from this, there are place holders in the staging for some operational conditions. For example: Controlled by Treasury, operational capabilities to monetize flag and so on have been used in rules to check and update the meets_operational_conditions_for_hqla condition.
To add a new custom run purpose, follow these steps to add an entry in the Lookup and Process Purpose Map tables:
1. Add an entry in the FSI_LRM_LOOKUP_TL table.
2. Execute the following SQL query in the atomic schema by replacing the placeholders PURPOSE_ID and PURPOSE_DESCRIPTION.
insert into FSI_LRM_LOOKUP_TL (LOOKUP_CD, LANG_CODE, CATEGORY_ID, LOOKUP_DESCRIPTION) values ('##PURPOSE_ID##', 'en_US', '10', '##PURPOSE_DESCRIPTION##') /
Where,
PURPOSE_ID value is any value greater than 50.
PURPOSE_DESCRIPTION is a description of the new purpose.
3. Add an entry in the FSI_LRM_PROCESS_PURPOSE_MAP table.
4. Add the task entries for the ##PURPOSE_ID## added in step 1, in the Process Purpose map table for both Contractual and BAU Runs. This gives the pre and post Tasks in the Run.
5. To enable the NSFR option for any of the custom run purposes, enter the respective Run purpose code as comma separated values against the component code NSFR_ENABLED in the SETUP_MASTER table. This code enables the NSFR option in the Run Management screen for the mentioned purposes.
select * from setup_master where v_component_code like 'NSFR_ENABLED'
6. In the fsi_lrm_process_purpose_map table, update the F_FCAST_ENABLED column with the appropriate values, for all the runs. The available values are 1 or 2, where:
§ 1 is Yes, which indicates that forecasting is enabled.
§ 2 is No, which indicates that forecasting is not enabled.
OFS LRM supports audit logging for application specific objects. For details, see the Creating Audit Trail Report section in the OFS AAI User Guide.
For Log4J customizations, see the Logging section in the OFS AAI Administration Guide.