5.22 Behaviour Pattern Loader

The Behaviour Pattern Loader provides the ability to load bulk Behaviour pattern definitions through a back end procedure. This Loader reads input data from STG_BEHAVIOUR_PATTERN_NRP table – performs data quality checks on the same – and loads the definitions into FSI_BEHAVIOUR_PATTERN_MASTER and FSI_BEHAVIOUR_PATTERN_DETAIL tables based on following conditions:

• New BP: If pattern code in stage table is not already present in FSI table, then insert data after quality check and necessary transformations
• Existing BP: If pattern code in stage is already present in FSI table, then compare ‘created date’. If date in stage table is higher (that is, more recent) than that in FSI table, then overwrite the definition otherwise skip and log appropriate message.

The utility is called from a Batch called Behaviour Pattern Loader. The utility is designed to be executed for a single AS OF DATE only, that is, it would fetch the data from STAGE table for the given MIS date and push the records to DTL/MASTER table accordingly.

The structure of the Stage table is as follows:

Table 5-11 STG_BEHAVIOUR_PATTERN_NRP

Column Name	Logical Name	Data Type	Null Allowed ?	PK	Column Comments
FIC_MIS_DATE	Extraction Date	DATE	No	Yes	Date on which the behaviour pattern definition was created. Normally indicates the calendar date from which it is valid.
F_REPLICATING_PORTFOLIO_FLG	Replicating Portfolio Flag	CHAR(1)	Yes	No	This indicates whether the behaviour pattern definition is for replicating portfolio (FTP use case) or not. List of Values are Y for Replicating Portfolio and N for Non-replicating portfolio.
N_PATTERN_CD	Pattern Code	NUMBER(5)	No	Yes	Code assigned to behaviour pattern definition. This must be a number between 70000 to and 99999
N_PATTERN_PERCENTAGE	Pattern Percentage	NUMBER(22,6)	Yes	No	This stores the percentage of current balance that is used as cash flow on the event date. Within one pattern code sum of percentages must not exceed 100.
N_SEQUENCE_NUMBER	Sequence Number	NUMBER(3)	No	Yes	Within one pattern multiple tenors can be defined. Sequence denotes the order of each tenor.
N_PATTERN_TENOR	Pattern Tenor	NUMBER(5)	Yes	No	This is the tenor specified in behaviour pattern definition and is used to decide cash flow event. It must be read in conjunction with Tenor Unit.
V_PATTERN_SUBTYPE_CD	Behaviour Sub Type Display Code	VARCHAR2(5)	Yes	No	This indicates the sub-type of behaviour for which pattern is defined. Expected values are: If Behaviour Type Display Code is Non Maturing (NM) then expected values are CR for Core and VL for Volatile; If Behaviour Type Display Code is Non Performing (NP) then expected values are SS for Substandard, DF for Doubtful and L for Loss; If Behaviour Type Display Code is Devolvement and Recovery (DR) then expected values are SD for Sight Devolvement, SR for Sight Recovery, UD for Usance Devolvement, UR for Usance Recovery, U for Usance and S for Sight.
V_CREATED_BY	Created By	VARCHAR2(20)	Yes	No	Identifier for the user or model that created the behaviour pattern definition. It can also denote the system from which definition is sourced.
V_PATTERN_DESCRIPTION	Pattern Description	VARCHAR2(255)	Yes	No	Description for the behaviour pattern definition given by user.
V_PATTERN_NAME	Pattern Name	VARCHAR2(30)	Yes	No	Name of the behaviour pattern definition given by user.
V_PATTERN_TYPE_CD	Behaviour Type Display Code	VARCHAR2(40)	Yes	No	This indicates the type of behaviour for which pattern is defined. Expected values are NM for Non Maturing, NP for Non Performing and DR for Devolvement and Recovery.
V_PATTERN_TENOR_UNIT	Pattern Tenor Unit	VARCHAR2(1)	Yes	No	This indicates the unit in which Tenor is specified. List of values are D for Days, M for Months and Y for Years.

Following checks will be performed on the intermediate data populated by the user in STAGE table. The following list of values will be checked against the relevant look-up tables:

• Pattern Code should be between 70000 and 99999.
• BP Pattern Name:
  • If it is a new pattern code, then name should not be already used by another BP.
  • If it is an existing pattern code, then name should be same as BP existing in FSI table. If names are different, then that from FSI table will be retained.
• Behaviour Type Display Code should be present in table FSI_BEHAVIOUR_TYPE_CD column BEHAVIOUR_TYPE_DISPLAY_CD
  • Behaviour Sub Type Display Code should be present in table FSI_BEHAVIOUR_SUB_TYPE_CD column BEHAVIOUR_SUB_TYPE_DISPLAY_CD
• Tenor should be a valid number.
• Tenor unit should be present in table FSI_MULTIPLIER_CD column MULTIPLIER_CD.
• Percentage for one pattern code sum of percentage across all Behaviour Type Display Code must not exceed 100. Sequence for one pattern code sequence number must not repeat.