1. Pre-requisites for successful file upload:
1) Pay Deduction and Meghdoot cash files are to be uploaded separately. If a single file has policies for pay deduction and cash, the policies will be reported in error.
2) The PAO code of the pay deduction file should be correctly updated in the pay deduction file.
3) Prior to uploading Pay Deduction file, collection for the special group should be done through the collection module.
4) Naming convention to be followed before uploading any file, so that any particular file can be identified easily. Every file should be named as ‘Office name_Schedule month_PAO Code (PAO code or Meghdoot)_XX(running number from 01).csv
5) Every file, should be restricted to 1000 records, so that the files can be processed quickly thereby reducing the waiting period.
6) In Case of pay deduction file upload the policy should be a part of the special group, any policy which is not a part of special group will be error out. Special group search to be done before creating a new special group.
7) In cases of Death, Maturity, Surrender, Survival claims it is advised to ascertain the premium in the respective policies and in case premium has been either deducted through pay deduction or entries are present in PRB, then the user first makes use of File upload functionality to upload the premium and once the premium is updated in McCamish the user indexes the above said claims.
8) The policy statuses supported in the file are: AP, VL, IL and PM.
2. Out of scope functionalities
1. Loan repayment will not be handled through bulk upload
2. Policies for which service requests like Death claim and surrender requests have been indexed will not be handled, unless the requests have been withdrawn
3. For pay recovery policies, if any extra amount is collected by DoP, then the same will not be uploaded by the csv once these validations are effective.
4. Excess premium related validations are effective only on the new uploads. Any extra premium which is already uploaded in the system will be handled either through collection screen or through excess premium while doing any claim calculation.
3. Collection – Pay Deduction
Ø Click on the collection icon on Dashboard
Ø On the collection screen, select the ‘Special Group’ radio button and then select the ‘Renewal’ radio button
Ø Search the special group for which collection has to be made
Ø Select the schedule month and enter the payment details
Ø Click on the submit button to complete the payment for the special group
If Payment made through Cheque then select Cheque/DD
If Payment is through Book Adjustment (PLI Recovery Schedule given by Accounts branch) then select
Book Adjustment
Ø Once the collection for special group is made, the user can upload the Bulk Pay Deduction file
4. Pay Deduction File upload
Ø The user can upload the pay Deduction and Meghdoot file from the ‘Bulk Policy Upload/Group Policy Maintenance’ icon present on the system administrator’s dashboard.
Ø Two options will be provided to the user to go ahead with the file upload process,
1. Special Group Number – If the user clicks on special group search and enters a special group number, the load file screen along with the transaction details of the special group will appear.
From here the user can upload the pay deduction file.
2. Office code - If the user enters the office code and clicks search, then the user can upload the Meghdoot file.
4.1 Process for Pay Deduction File Upload
Ø Search the special group by name or number.
Ø The transaction details of the collection made against the special group will appear in a grid below.
Ø The user can proceed to upload file by clicking on the Load File Screen button.
Ø On the load file screen the user has to enter the billing dates and upload the pay deduction file which has to be in CSV format only.
Ø On clicking the submit button, the upload file request will be submitted and a confirmatory message will be displayed to the user. A batch process will process this file.
Ø The user can view the historical files by clicking on the View History button. The status will be changed from pending to complete once the batch is executed. The upload, success and error files can be viewed by clicking on the hyperlinks respectively.
All the policies which are not migrated and are uploaded through the file will be kept in a separate error file at the end of the day.
5. CSV Uploading for Cash Policies
Ø To upload Meghdoot file, the user has to search by office code.
Ø The load file button will appear, on clicking the button, the user will be taken to the file upload screen.
Ø The user can upload the Meghdoot file which has to be in CSV format only. On clicking the submit button, the CSV file upload request will be submitted.
Ø The system will do UI validations on the file and throw errors for the impacted rows.
Ø A batch process will upload and process the collections mentioned in the CSV file.
Ø The user can see the historical files from the History tab.
When the user uploads the file, the system will do the initial checks of the file and throw errors for the incorrect records.
System validations will be made for all the mandatory fields. If any mandatory field is left blank or the format is incorrect, a validation message with the error description and the corresponding row and column will be shown on the screen.
The file will not be uploaded until all the validations are corrected and records are updated. Once all validations are clear then the file will be uploaded and a message will be shown that the file is successfully saved.
For Pay recovery policies system will match the total amount for the group in suspense against the total premiums in the file. If the suspense amount is less than the total of premiums in the file, then a validation message will be thrown – suspense amount, total amount in group collection suspense is less than the total premiums in the file.
This file will be in pending status only till the batch will process these payments.
7. CSV fields and UI validations
Field Name
|
Mandatory/Optional
|
Acceptable formats
|
Example
|
UI Error messages
|
CIRCLE_CODE
|
Optional
|
alphabets
| | |
PAO_CODE
|
Conditional Mandatory
Mandatory if the file belongs to special group policies
|
alpha-numeric
| | |
PAO_SUB_CODE
|
Optional
|
NULL
| | |
POLICY_NO
|
Mandatory
|
alpha-numeric
| |
Policy number cannot be blank
|
NAME
|
Optional
|
Only alphabets
| | |
SUM_ASSD
|
Optional
|
Numeric
| | |
LOAN_AVAILED
|
Optional
|
Numeric
| | |
PREM_AMNT
|
Mandatory
|
Numeric
| |
Premium amount can’t be blank
|
DATE_MATURITY
|
Optional
|
Numeric
| | |
DATE_ENTRY
|
Optional
|
Numeric
| | |
AGENT_CODE
|
Optional
|
alpha-numeric
| | |
SCH_MONTH
|
Optional
|
Numeric
| | |
FROM_DATE
|
Mandatory
|
Numeric
|
dd-mm-yyyy dd-mmm-yyyy
|
From date can’t be blank/
Invalid date
|
TO_DATE
|
Mandatory
|
Numeric
|
dd-mm-yyyy dd-mmm-yyyy
|
TO date can’t be blank/
Invalid date
|
LOAN_PRINCIPAL
|
Optional
|
Numeric
| | |
LOAN_INT
|
Optional
|
Numeric
| |
|
TOTAL_RECEIPT
|
Mandatory
|
Numeric
| |
Total receipt Cann’t be blank
|
DEFAULT_AMT
|
Optional
|
Numeric
| |
|
TOTAL_PAYMENT
|
Optional
|
Numeric
| |
|
DATE_PAYMENT
|
Optional
|
Numeric
| |
|
RECEIPT_PAYMENT
|
Mandatory
|
alphabet
| |
|
DATE_TRANSACTION
|
Mandatory
|
Numeric
|
dd-mm-yyyy dd-mmm-yyyy
|
Date_transaction can’t be blank/
Invalid date
Transaction date can’t future date
|
TRANSACTION_TYPE
|
Mandatory
|
alphabet
|
P - for Pay
C - for Cash
|
Transaction Type cannot be blank
|
REBATE_AMT
|
Optional
|
Numeric
| |
|
INTERIM_PAYMENT_NO
|
Optional
|
NULL
| |
|
First_Year_Tax
|
Integer
|
Optional
|
Can be null or numeric
|
1. If transaction date>= 1 Jan 2015 and either FY tax or Renewal tax is not updated, then error message to be displayed- Service tax is required
2. If data type is other than numeric – Only numeric value accepted
|
Renewal_Year_Tax
|
Integer
|
Optional
|
Can be null or numeric
|
1. If transaction date>= 1 Jan 2015 and either FY tax or Renewal
|
Tax_Type
|
Integer
|
Conditional Mandatory
(Required if first year tax/renewal year tax is present)
|
1 for first year tax
2 for renewal year tax
3 for first year as well as renewal year tax
|
1.If FY tax/Renewal tax is updated and Tax Type is blank, then display error message - Tax Type is required
2. If data type is other than numeric – Only numeric value accepted
|
1. If the file format is of more than 28 fields, then display message – Invalid file. This will be a UI validation.
2. The total receipt will be inclusive of total premiums + Interest – Rebate
3. McCamish does not calculate if rebate/interest is correctly calculated. The system matches the total premiums + interest – rebate with total receipt and returns error if this amount is not found matching.
4. If the total number of records in Meghdoot file are more than 1000, then an error message will be shown – ‘Maximum number of records to be uploaded should not be more than 1000’.
8. Batch Validations
| Error Description |
Scenario
|
Payment Record Creation Error
|
When the amount field is other than acceptable values like 'Null' or '0' or a decimal this error would come
|
Payment Record Creation Error
|
When there is any issue with the format of date field this error would come. Acceptable date format is DD-MMM-YY or DD-MMM-YYYY or DD/MM/YYYY or DD/MM/YY
|
Policy Not Available
|
If policy number is missing
|
PaidToDate does not match with From_Date
|
From date is not match with policy, paidtodate +1
|
Invalid Policy Status for Applying Premium
|
Policy status is not PM, AP, AL
|
Policy not Part of given Special Group
|
Policy not Belongs to the Special Group
|
Feed File Premium Amount or Total Receipt is zero
|
Amount Paid Is Zero
|
Feed File From_Date or To_Date is null
|
Feed File i.e. from_date or to_date is null
|
From_Date is not First Day of month or To_Date is not last day of month
|
From_Date is not First Day of month or To_Date is not last day of month
|
Total_Amount not matching with interests and rebates
|
TotalAmount in Feed file is less than Actual TotalAmount calculated from Application
|
Error creating PendEvnt
|
System related errors
|
Error updating PendEvnt
|
System related errors
|
Error updating Suspense
|
System related errors
|
8.1 Other Error messages
1. If the amount of service tax is incorrect, then display error message – Service tax amount incorrect. This will be done in batch processing.
2. If tax type 1 is updated with renewal tax and tax type 2 is updated with first year tax or if tax type 3 is updated with first year tax or renewal year tax, then display error message – Tax Type incorrect. This will be done in batch processing.
3. If the total receipt amount does not include service tax, then update error message in the file as – Total receipt amount does not include service tax.
4. The Meghdoot / Bulk upload batch will do validations on the uploaded file to check if any excess premium is uploaded.
5. In case of excess premium, the record will be rejected and the policy record will be updated in the error file with the following error description – “Total Premium is more than the frequency premium”.
9 Service tax related changes
> New columns to be added for Service Tax and Sales tax in Meghdoot Upload File and Bulk Upload File. The file template which can be downloaded from the screen to be changed.
> The last three columns of existing file will be utilized for these 3 new fields.
> Either Service tax or Sales tax column to be validated if the Transaction date(Receipt Date) is after 1 Jan 2015
> Value in this column needs to be saved in Data base without any calculations involved if uploaded through Meghdoot Upload
> No Accounting will be done for the service tax/sales tax if uploaded through Meghdoot.
> For Bulk Upload, service tax/sales tax amount should be calculated and validated as per issue circle and rate mentioned as per the upload date.
> The total_receipt column in the file should include the service tax component as well.
Error message to be updated in the file if the total receipt amount does not match.
To upload service tax and sales tax through upload file, the file format will be modified and three new columns will be added-
1. First Year Tax
2. Renewal Year Tax
3. Tax type
The validations for these fields are covered in Section 8.
11 Excess Premium Validations
11.1 Existing Business Process
According to the current business process if the user uploads extra premium through the csv upload, the required premium amount is adjusted towards the premium for the months selected and the rest is kept in policy suspense.
This suspense amount will be used when the user tries to pay future premiums through collection screen. If no further premium payment is made then this amount is shown as excess premium when the policy is settled towards claims.
11.2 Problem Statement
Many times users are uploading excess amount through csv upload. There are various reasons for this error:
1. User mistake while uploading files when incorrect amount is entered
2. Extra premium uploaded when policy frequency is changed
11.3 Proposed solution
To stop uploading excess premium from the csv, new validations will be built in the batch. If any excess premium is entered in the file which is more than the frequency premium, then the record will be rejected in the batch.
An examples is mentioned below to upload the file in the correct manner:
1. Policy no - R-TN-TC-EA-288371
2. Paid to Date – 30-JUN-2006
3. Policy Issue date – 1-APR-2006
4. Frequency
§ Monthly - 01-OCT-04 to 30-JUN-15
§ Quarterly from 01-JUL-15
5. Monthly Premium - 164
6. Frequency Premium - 491
The above mentioned policy has two frequencies since it was migrated as monthly policy and then it got converted to quarterly.
To upload the premium for this policy correctly, user should create two records in the file. One record will have premium record for only monthly premium payments. The second record should have only quarterly premium payment record.
Ø For the record with only monthly premium. ‘To Date’ and ‘From Date’ should be selected accordingly. The total premium (Total Receipt) should be equal to the number of months multiplied by the monthly premium amount.
Ø To upload the quarterly premium, user should create another record and the select the ‘To Date’ and ‘From Date’ based on the quarterly frequency. The premium amount should be equal to the number of quarters multiplied by the quarterly premium.
In the example ---
Monthly premium is Rs.164 and no. of months to be updated are 108. So in total receipt column 164*108 = Rs.17712 will be updated.
Similarly when quarterly premium has to be updated, the quarterly premium amount which is Rs.491 is multiplied by the number of quarters. Hence it is 491*1 = Rs.491.
Ø This similar method will be used when premium frequency is half-yearly or annual.
12 Transferring from one special Group to another special group
Ø In case the special group of the policy is changed, the same can be updated in the system by the pay Deduction file.
Ø The premium collection should be done on the new special group and the file should be uploaded using this group number only.
Ø The new PAO code can be entered in the pay deduction CSV file and the system will change the PAO code of the policy.
