CSV Upload File-Service Tax related changes in McCamish

• The last three columns of existing csv file will be utilized for updating records related to service tax as follows:

1. First Year Tax : applicable for premiums collected in the first policy year
2. Renewal Year Tax: applicable for premiums collected in the subsequent policy years
3. Tax Type: 1 for first year tax and 2 for renewal year tax and 3 when S. tax is for first year as well as renewal year.

• 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.
• If the amount of service tax is incorrect, then error message will be displayed – Service tax amount incorrect. 
• If tax type 1 is updated with renewal tax and tax type 2 is updated with first year tax, then error message will be displayed – Tax Type incorrect.

Example 1 :

If premium is collected for first year, then column -‘tax type’ to be updated as 1 and renewal year tax column will be updated as 0. S.tax to be calculated @3.50 % and updated in the column ‘First Year Tax’.  

Policy Issue date – 1-APR-2015
Paid to date-31.08.2015
Frequency- Monthly
Monthly Premium - 164
Premium due from 01.09.2015 till date
S.tax Calculation: Monthly premium * no. of months i.e. 164*4=656
                                S.tax: 656*3.50%=23

First_Year_Tax	Renewal_Year_Tax	Tax_Type
23	0	1

 Example 2 :

If premium is collected for subsequent year, then column-‘tax type’ to be updated as 2 and first year tax column will be updated as 0. S.tax to be calculated @1.75 % and updated in the column ‘Renewal Year Tax’.

Policy Issue date – 1-SEP-2014
Paid to date-31.10.2015
Frequency- Monthly
Monthly Premium - 164
Premium due from 01.11.2015 till date
S.tax Calculation: Monthly premium * no. of months i.e. 164*2=328
                                S.tax: 656*1.75%=6

First_Year_Tax	Renewal_Year_Tax	Tax_Type
0	6	2

 Example 3 :

If premium is collected for first year as well as subsequent year, then column-‘tax type’ to be updated as 3 and S.tax to be calculated @ 3.50% for first year and @1.75 % for renewal year. First year tax will be updated in ‘first year tax’ column and renewal year tax will be calculated in ‘renewal Year Tax’ column.

Policy Issue date – 1-SEP-2014
Paid to date-30.06.2015
Frequency- Monthly
Monthly Premium - 164
Premium due from 01.07.2015 till date
S.tax Calculation: First year Monthly premium * no. of months i.e. 164*2=328
                               First year S.tax: 328*3.50%=11
                               Second year monthly premium * no. of months i.e. 164*4=656
                               Renewal Year S.Tax : 656*1.75%=6

First_Year_Tax	Renewal_Year_Tax	Tax_Type
11	6	3

Example 4  :

The total_receipt column in the file should include the service tax component as well.

If the total receipt amount does not include service tax, then error message will be triggered as – Total receipt amount does not include service tax.

For e.g. If First year premium due is 328 and s.tax calculated on it is 11/-, then Total receipt column will have the total of Premium and service tax i.e. 328+11=339/-.

PREM_AMNT	TOTAL_RECEIPT	First_Year_Tax	Renewal_Year_Tax	Tax_Type
328	339	11	0	1

1.    

Example 5

Value in this tax column needs to be saved in Data base without any calculations involved if uploaded through Meghdoot Upload. The Meghdoot / Bulk upload batch will do validations on the uploaded file to check if any excess premium is uploaded.