Preparing and Uploading Limit Definitions

20.3 Preparing and Uploading Limit Definitions

Step 1: Create a limit definition with the following attributes and upload data to table


                    STG_ALM_GAP_LIMIT_DTL:

Table 20-1 Limit Definition

Column Name	Name	Data Type	Nullable	Primary Key	Description
D_EFFECTIVE_END_DATE	Effective End Date	DATE	Yes	No	Date till which the limit definition is effective.
D_EFFECTIVE_START_DATE	Effective Start Date	DATE	Yes	No	Date from which the limit definition is effective.
FIC_MIS_DATE	Extraction Date	DATE	No	Yes	Date till which the limit definition is effective.
N_BUCKET_NUMBER	Bucket Number	NUMBER(5)	No	Yes	This is to identify a dynamic bucket number.
N_LWR_LIMIT_AMOUNT	Lower Limit Amount	NUMBER(22,3)	Yes	No	Normally the limit will be specified as a range. The lower value of the range must be given here.
N_START_DATE_INDEX	Start Date Index	NUMBER(5)	No	Yes	This is to identify dynamic bucket start dates.
N_UPR_LIMIT_AMOUNT	Upper Limit Amount	NUMBER(22,3)	Yes	No	Normally the limit will be specified as a range. The upper value of the range must be given here.
V_ALM_GAP_MEASURE	ALM Gap Measure Name	VARCHAR2(100)	No	Yes	Name of the measure for which limit is specified. Indicative Values are 'Net Reprice Gap' and 'Cumulative Reprice Gap'.
V_CURRENCY_TYPE_CD	Currency Type Code	VARCHAR2(40)	No	Yes	This column decides whether the given limits are for Base (Natural) currency or consolidated (Reporting) currency. Indicative Values are ‘Entered’ for base or natural currency and ‘Functional’ for consolidated or reporting currency.
V_FCAST_RULE_NAME	Forecast Rate Rule Name	VARCHAR2(60)	No	Yes	Name of the forecast rate rule for which limit is specified.
V_ISO_CURRENCY_CD	ISO Currency Code	VARCHAR2(3)	No	Yes	Code of the currency for which limit is specified. In case currency type is reporting then this column will store reporting currency code.
V_LIMIT_METHOD	Limit Method	VARCHAR2(60)	Yes	No	The manner in which limit is specified. Indicative Values are 'Absolute' when the limit is defined as the amount or 'Relative' when limit is defined in terms percentage.
V_LV_CODE	Legal Entity Code	VARCHAR2(20)	No	Yes	Stores the code for the legal entity to which the branch belongs.
V_ORG_UNIT_CODE	Organization Unit Code	VARCHAR2(40)	No	Yes	This column stores the cost center to which the account belongs to. Dim_Org_Unit is the equivalent dimension table for this column.
V_SCENARIO_NAME	Scenario Name	VARCHAR2(40)	No	Yes	Name of the forecast rate scenario for which limit is specified.
V_TIME_BUCKET_NAME	Time Bucket Name	VARCHAR2(30)	No	Yes	Name of the time bucket which has been created from UI and for which limit is being specified.

Step 2: Maintain limit in processing or metadata tables.

To maintain the limit in processing or metadata table, you can execute the batch <INFODOM>_ALM_GAP_LIMIT_LOADER (GAP Limits Loader) from the ICC Batch screen within OFSAAI Framework. There is a seeded batch <INFODOM>_ALM_GAP_LIMIT_LOADER with the following parameters for the task:

Execution Date: Date of batch execution
Batch_run_id: ID of the batch

Here, no parameters are required from the user.

During this batch execution, data is uploaded and copied to the processing area where master definitions with changes over time will be maintained. Quality check and limit definition updating are also performed here.

Following quality checks will be performed here:

Dimension Data: All values like legal entity, currency, and so on. should be checked to confirm that they are present in respective dimension tables and are active. This includes the following:
1. Check if the legal entity code for a given record conforms to values in DIM_LEGAL_ENTITY_B; only those values which are ‘leaf-only’ and ‘enabled’ are considered.
2. Check if the currency type code for a given record conforms to values in FSI_CURRENCY_TYPE_MLS.
3. Check if the currency code for a given record conforms to ISO codes in FSI_CURRENCIES.
Metadata: Time bucket, start date index, forecast rule, the scenario should be checked with relevant metadata tables to confirm that there correct and active. This includes the following:
1. Check if the bucket-name for a given record conforms to values in FSI_TIME_BUCKET_MASTER; follow this up and check if the bucket number for that particular record exists in DIM_RESULT_BUCKET.
2. Check if the start-date index for a given record conforms to the bucket-name: start-date index mapping in FSI_LR_BUCKETS_AUX and TIME_BUCKET_SYS_ID.
3. Check if the forecast-rule name for a given record conforms to values in FSI_M_OBJECT_DEFINITION_TL; follow it up and check if the scenario-name for that record conforms to forecast-rule name: scenario name mapping in FSI_FCAST_RATES_SCENARIOS.
Values given for limit method and reprice measures must be from the prescribed list.
Check if the repricing gap measure is one of 'NET REPRICE GAP' or 'CUMULATIVE REPRICE GAP'.
Effective start date should be LESSER THAN effective end date. However, the date of execution (As of Date) can be LESS THAN, GREATER THAN AND EQUAL TO effective start date. The date of execution is allowed to be LESS THAN AND EQUAL TO effective end date but never greater than the effective end date.

The limit type should be one of ‘ABSOLUTE’ or ‘RELATIVE’.

Table 20-2 Limit Amount

Limit Amount – Lower Value	Limit Amount – Upper Value
0	100
110	200

Here, the lower value starts from 110 whereas the upper value of the previous band was 100. Thus, there is break, that is, 101-109 is lost.
When the limit method is Absolute, then the upper value of limit should be greater than lower value and there should be no breakage of the gap between given ranges. For example: If the limit is defined in a way then there is a break.
When the limit method is Relative, then the lower limit value should not be less than 0 and the upper value should not be more than 100.
‘Valid till’ date is in future (higher than as of date) and greater than ‘valid from’ date.
Conditional upper and lower limits include the following: Succeeding lower limit should be continuous with previous upper limit whether the method is Absolute or Relative. (bucket continuity constraint)
Limits should be non-negative irrespective of method or lower or upper. The upper limit should be greater than lower limit All buckets in a set (as differentiated by bucket name after filtering through other Primary Key columns) must follow the same limit method.
For limit method RELATIVE, first lower limit must be 0 and last upper limit should be 100 to cover the entire percentage spectrum; of course, neither limit should cross 100 at any stage If any record within a limit definition fails the quality test, the entire limit definition will be rejected, and a message will be logged in FSI_ MESSAGE_LOG table.

Step 3: Prepare limit definition for reports.
In this step, limit definitions from the master will be prepared for use in BI by reports. You can execute this from the ICC Batch screen within the OFSAAI framework. A seeded batch INFODOMNAME_TRANSFORMALMRESULT is provided with the Task component as ALMBI TRANSFORMATION and following parameters for the task:
- Execution Date: Date of batch execution.
- Batch_run_id: ID of the batch.
- Process ID (PID): The process for which the task is to be executed, either from UI or from the back-end.
- Limit flag(b_limit_flag): Possible values are 'Y' or 'N'; the limits should be prepared only if the flag is 'Y'.
- Rerun flag (p_re_run_flag): Possible values are 'Y' or 'N'; if flag is 'Y', previously present limit definitions will be removed.
Here, Process ID, Limit Flag, and Rerun parameters are required user input.