The file layout in this document is used for the upload of insured lives details for credit life insurance products.
Note:
Currently, the automatic creation of a My Work Service Request for a death claim and the automatic lapsing of an insured life’s cover has not been developed as this needs to be triggered by the Account Status values in the file and it is not known at this stage what event must be triggered by which Account Status.
An Excel file in the format as per the details in the table in File Layout below will be received each month. The file name will contain the Product Code, the date of the file in the format DDMMYYYYY and a sequence number e.g. UCPL3009201401 where:
UCPL is the Product Code for the Unsecured Customer Personal Loans product
30092014 is the file date
01 is the sequence number
When the file is uploaded, the system will check if there is a Membership with a Membership Reference Number equal to the value for the Account Number field linked to a Natural Person record with the same Surname, Name, Identity Number and Date of Birth. If found, the system will update the values as per the details in the IN.pensions field column in the table below:
· Sum Assured
· Outstanding Term
If a Membership with a Membership Number equal to the value for the Account Number field is not found, the system will create a new Membership record as per the details in the IN.pensions field in the table below.
If a Natural Person record is found with the same Surname, Name, Identity Number and Date of Birth and no Membership with a Membership Reference equal to the value for Account Number is found, the system will create a new Membership record and associate it to this Natural Person.
If a Membership is found with a Membership Reference Number equal to the value for Account Number linked to a Natural Person record for which the values of any of the following fields are the same but one or more are different, the system will update the Natural Person record with the new value:
· Surname
· Name
· ID Number
· Date of Birth
If a Membership is found with a Membership Reference Number equal to the value for Account Number linked to a Natural Person record for which the values for Surname, Name and Date of Birth are the same and the value for either of the following fields is different to the values for the Natural Person or Client Address, the system will update the Natural Person and / or Client Address record with the new value:
· Gender (Natural Person)
· Nationality (Natural Person)
· Postal Address 1 (Client Address - Address Line 1)
· Postal Address 2 (Client Address - Address Line 2)
· Physical Address (Client Address – Address Code)
· Postal Town (Client Address – Address Line 4)
· Postal Code (Client Address – Postal Address Line 4)
· Country of Residence (Client Address – Country of Residence)
The system will Compare the Memberships in the file with the record of the Membership records updated for the previous month and if a Membership was updated in the previous month and is not included in the current file, and update the Membership Status to PENDING.
The system will read the Date of Last Payment on the Memberships for which the Membership Status is PENDING and if the month of the date is more than two months earlier than the file date month and the Membership is not included in the file, the system will update the Membership Status to ENDED and create a Member Values record with a Type of ASSURANCE DETS and Sub-Type of REASON and a Classification of PREMIUMS MISSED.
Upload the file as per the details in the IN.pensions field column in the table below.
|
Column |
Field Name |
Format |
Size |
IN.pensions field |
|
A |
Surname |
A |
100 |
Surname on Natural Person |
|
B |
Name |
A |
100 |
First Name 1 on Natural Person |
|
C |
ID Number |
A/N |
50 |
Identity Number on Natural Person. Update Identity Type on Natural Person with the value for the UDPV Object ID Type with a Description of NATIONAL IDENTITY DOCUMENT |
|
D |
Passport Number |
A/N |
50 |
Identity Number on Natural Person. Update Identity Type on Natural Person with the value for the UDPV Object ID Type with a Description of PASSPORT DOCUMENT. |
|
E |
Date of Birth |
N |
8 |
Date of Birth on Natural Person |
|
F |
Gender |
A |
1 |
Update the value for Sex on Natural Person with the value for the UDPV Object Sex as follows: If value is M then MALE If value is F then FEMALE |
|
G |
Postal Address 1 |
A/N |
4 |
Address Line 1 on Client Address with a Type of Address POSTAL |
|
H |
Postal Address 2 |
A/N |
400 |
Address Line 2 on Client Address with a Type of Address POSTAL |
|
I |
Physical Address |
A/N |
400 |
Address Line 3 on Client Address with a Type of Address POSTAL |
|
J |
Postal Town |
A |
100 |
Address Line 4 on Client Address with a Type of Address POSTAL |
|
K |
Postal Country |
A/N |
50 |
Country on Client Address with a Type of Address POSTAL |
|
L |
Postal Code |
A |
5 |
Postal Code on Client Address with a Type of Address POSTAL |
|
M |
Country of Residence |
A |
5 |
Country of Residence on Client Address |
|
N |
Nationality |
A |
100 |
Nationality on Natural Person |
|
O |
Mobile phone number |
N |
13 |
Do not load |
|
P |
Email address |
A/N |
50 |
Do not load |
|
Q |
Joint account indicator |
A |
4 |
Not required don’t load. |
|
R |
Account number |
A/N |
30 |
Membership Reference Number. Default the Scheme Code to the Scheme Code for the Scheme for which the Type of Fund is CREDIT LIFE and the Country is the Country applicable for the directory in which the file is found. Refer to Note below. |
|
S |
Account status |
N |
15 |
If the value is equal to ACCOUNT CLOSED or ACCOUNT CLOSED TODAY check if there is a value for Close Date. If not write an error message to the error log. |
|
T |
Account type |
A/N |
50 |
Contribution Membership Group and Benefit Membership Group with a Code equal to the Value for Account Type. If not found link the Membership to the Contribution and Benefit Membership Groups for which the value for Default is Y. |
|
U |
Close Date |
N |
8 |
DDMMYYYY If the value is not null update the CAR End Date for the Membership with this date and update the Membership Status to ENDED with an Effective Date equal to the Close Date value. |
|
W |
Principal Amount |
N |
15 |
Amount on Member Values record with a Type of ASSURANCE DETS and a Sub-Type of INSURED AMOUNT |
|
X |
Sum Assured |
N |
15 |
Create an Actual Earnings record with a Salary Nomination with a Member Salary Component Type of BASIC, Amount equal to this value and an Effective Date equal to the file date. In addition, Amount on Member Values record with a Type of ASSURANCE DETS and a Sub-Type of SUM ASSURED If a new Membership record is being created read the Benefit Package with a Purpose of VALIDATION and Benefit Event with an Event Category of MATURITY and read the From and To values on the Scale linked to the Benefit Product with a Description of MAXIMUM SUM ASSURED. If the Sum Assured value is greater than the To value found or less than the To value write an error message to the error log. |
|
Y |
Credit Limit |
N |
15 |
Not required don’t load. |
|
Z |
Original Term |
N |
4 |
Description on Member Values record with a Type of ASSURANCE DETS and a Sub-Type of TERM |
|
AA |
Outstanding Term |
N |
4 |
If a new Membership record is being created determine the Maturity Date based on the Date of Commencement with BLK and the Outstanding Term value in months and create a Member Values record with a Type of ASSURANCE DETS and a Sub-Type of MATURITY DATE and a value for Date equal to the Maturity Date. Read the Benefit Package with a Purpose of VALIDATION and Benefit Event with an Event Category of MATURITY and read the To value on the Scale linked to the Benefit Product with a Description of MAXIMUM TERM. If the value for Outstanding Term is greater than the To value found write an error message to the error log. |
|
AB |
Date of Commencement with BLK |
N |
8 |
CAR Start Date |
|
AC |
Original Date of Commencement |
N |
8 |
Date on Member Values record with a Type of ASSURANCE DETS and a Sub-Type of APPL DATE |
|
AD |
Premium Paid |
N |
|
Not required don’t load. This will be calculated in the billing process. |
|
AE |
Tax Pin Number |
AN |
30 |
Description on Member Values record with a Type of EXTERNAL DOCS and a Sub-Type of TAX/PIN |
If a new Membership record is being created, the system will calculate the premium amount payable by reading the Income rule linked to the Income Type with a Global Group Type of CONTRIBUTION and the Contribution Membership Group for the Membership. If the Calculation Type is % OF SALARY the premium will be calculate based on the value for Sum Assured. The system will create a Membership Payment record with the following:
· Type of Payment of BASIC
· Start Date equal to CAR Start Date
The system will create a Membership Payment Detail record with the following:
· Effective Date equal to CAR Start Date
· Amount of Regular Payment equal to premium amount
· Frequency equal to the Frequency on the Income Rule
· Status equal to ACTIVE
For existing Memberships the system will calculate the premium amount based on the Sum Assured value in the file.
The system will read the Income rule linked to the Income Type with a Global Group Type of CONTRIBUTION and the Contribution Membership Group for the Membership. If the Calculation Type is % OF SALARY the premium based on the value for Sum Assured will be calculated.
The system will create a new Membership Payment Detail record with Amount of Regular Payment equal to the new premium amount.
The system will extract the data that was processed in the file and create a csv file and attach it to an email with the Email Address for the Team to which the Scheme is linked. The system will create the file with the following information:
· Account Number (Membership Reference Number)
· Surname (Surname)
· Name (First Name 1)
· ID Number (Identity Number)
· Date of Birth (Date of Birth)
· Gender (Sex)
· Nationality (Nationality)
· Account Type (Code and Description on Contribution
Membership Group)
· Principal Amount (Amount on Members Values with a Type of ASSURANCE DETS and Sub-Type SUM ASSURED)
· Maturity Date (Date on Member Values with a Type of ASSURANCE DETS and Sub-Type MATURITY DATE)
· Original Term (Description on Member Values with a Type of ASSURANCE DETS and SUB-TYPE TERM)
· Account Status (Membership Status)
· Closed Date (CAR End Date)
· Update Type (see details below)
· Tax Pin Number (Description on Member Values record with a Type of EXTERNAL DOCS and a Sub-Type of TAX/PIN)
Update Type
Include a value for Update Type and populate it with a value to indicate whether it is a new life assured, amendment to an existing life assured or where the cover has ceased as per the table below.
|
Update Type |
Details |
|
NEW |
The Membership was created for the first time |
|
AMENDED |
A value other than the Closed Date was updated for an existing Membership |
|
ENDED |
There was a value for Closed Date and the Membership Status was updated to ENDED or the Membership was not on the file for two months. |
|
PENDED |
The Membership Status was updated to PENDED |
|
CLOSE DATE ERROR |
If the value for Account Status is equal to ACCOUNT CLOSED or ACCOUNT CLOSED TODAY and there is no value for Close Date. |
|
MIN/MAX ERROR |
If the Sum Assured is less than or greater than the minimum sum assured or greater than the maximum sum assured, or the Outstanding Term is greater than the maximum term |