5.19 Payment Pattern Loader
The Payment Pattern Loader provides the ability to load bulk payment pattern definitions
through a back end procedure. This Loader reads the stage table data, does data quality
checks on the same, and load them into FSI_PAYMENT_PATTERN and
FSI_PAYMENT_PATTERN_EVENT tables, if the stage table data is
valid.
Following is the stage table to input the payment pattern:
Table 5-10 STG_PAYMENT_PATTERN
| Column Name | Column Datatype | Column Null Option | Column Is PK | Column Comment |
| V_AMRT_TYPE | VARCHAR2(5) | NOT NULL | Yes | Amortization code between 1000 to 69999. Patterns between this range will be consider for payment processing |
| N_EVENT_ID | NUMBER(5,0) | NOT NULL | Yes | Event Identity Number |
| N_SPLIT_ID | NUMBER(5,0) | NOT NULL | Yes | Holds number of patterns with in split pattern |
| V_PATTERN_TYPE | VARCHAR2(40) | NOT NULL | Yes | List of values could be Absolute, Relative, Split |
| V_TERM_TYPE | VARCHAR2(40) | NOT NULL | Yes | List of values could be Principal and Interest, Principal Only, Interest Only, Level Principal, Final Principal & Interest, Other |
| V_AMRT_TYPE_DESC | VARCHAR2(255) | NOT NULL | No | Alpha numeric value |
| N_PCT_VALUE | NUMBER(8,4) | NULL | No | Percentage applied to each pattern in case of split pattern type |
| V_PAYMENT_EVENT_MONTH | VARCHAR2(20) | NULL | No | Month in which payment event should occur |
| N_PAYMENT_EVENT_DAY | NUMBER(2) | NULL | No | Number of Days Payment type |
| N_PAYMENT_EVENT_FREQ | NUMBER(5,0) | NULL | No | Number of times payment event should occur |
| V_PAYMENT_EVENT_FREQ_MULT | VARCHAR2(40) | NULL | No | List of values could be Days,Months,Years |
| N_PAYMENT_EVENT_REPEAT_VALUE | NUMBER(5,0) | NULL | No | Holds number of times payment frequency should repeat |
| N_AMOUNT | NUMBER(14,2) | NULL | No | Amount |
| V_AMOUNT_TYPE | VARCHAR2(40) | NULL | No | List of values could be % of original Payment,% of current payment,absolute value |
| V_PAYMENT_TYPE | VARCHAR2(30) | NULL | No | List of values could be Conventional, Level principal, Non-amortizing |
The loader program performs the following data quality checks:
- The following values will be checked against the relevant look tables as
mentioned
- Pattern Type: FSI_PATTERN_TYPE_MLS
- CashFlowType: FSI_PAYMENT_TYPE_MLS (Should accept only 100-Principal and Interest and 300-Interest Only)
- Month: FSI_MONTHS_MLS
- Multiplier: FSI_MULTIPLIER_MLS
- Payment Method: FSI_AMOUNT_TYPE_MLS (For Conventional accept only % of Original Payment, % of Current Payment and Absolute value)
- Payment Type: FSI_PMT_PATTERN_TYPE_MLS
- While defining any pattern type like relative or absolute, MONTH and DAY combination should be unique
- MONTH and DAY pair should have valid month and day combination, such as January 31 days and February 28 days (Leap year was not considered) and so on.
- If cash flow value is Principal and Interest then N_AMOUNT cannot be blank. If it is Interest only then V_PAYMENT_TYPE and N_AMOUNT should be blank.
- When payment type is a Non-amortizing and Payment pattern is Relative then N_PAYMENT_EVENT_FREQ and N_PAYMENT_EVENT_REPEAT_VALUE should have values which could range between 1 to 9999.
- One Split pattern can have any number of definition, however the sum of N_PCT_VALUE of all the definition should be 100% and all the payment patterns in the split should be defined.
- All the following fields should have this validation on place:
- Day: Positive Integer Number Range from 1 to 31 depends on the month for which day.
- Percentage: Positive Integer or Decimal Number
- Frequency: Positive Integer range from 1 to 9999
- Repeat: Positive Integer range from 1 to 9999
- Value: Integer numbers from 0 to 9999999999
- For each payment pattern and payment type combinations fields relevant to that would
be populated by the user remaining columns should be populated with default
values:
- Payment Pattern: Absolute
- Payment Type: Conventional / Level Principal
- Columns gets populated with user values: Code , Description, Pattern Type, Payment Type, Month, Day, Cash Flow Type, Payment Method,Value, Percentage (in case of Split pattern type
- Payment Type: Non amortizing Payment type.
- Columns gets populated with user values: Code , Description , Pattern Type, Payment Type, Month, Day, Percentage (in case of Split pattern type)
- Payment Pattern: Relative
- Payment Type: Conventional / Level Principal
- Columns gets populated with user values: Code, Description, Pattern Type, Payment Type, Frequency, Multiplier, Repeat, Cash Flow Type, Payment Method, Value, Percentage (in case of Split pattern type)
- Payment Type: Non amortizing Payment type.
- Columns gets populated with user values: Code, Description, Pattern Type, Payment Type, Frequency, Multiplier, Repeat, Percentage (in case of Split pattern type).
The loader program defaults values for each column in case values provided by user are not relevant for the pattern and payment patterns they defined.