Import insurance rates
- Lango: Language and translation manager
The import of rates will read an Excel file that contains the input data for creating insurance rates.
Ins. Rate Import Templates
Rate import templates are set up by the user in the insurance settings area.
Role Center -> One Core - Insurance -> Settings -> Rate Import Templates
The User enters the Rate Import Template by filling in the Template Code and Name.
For the template, the user defines the rows for each sheet of the import file.
Template Lines
On the Template, the user defines the rows for each sheet of the import file.
The rate import template row contains the following fields:
Sheet Name (Sheet Name)
The user enters the batch name of the import file.
Insurance Company No. (Insurance Company No.)
The user selects the insurance company for which he will import rates. After selection, the system will fill in the name of the insurance company.
Insurance Product No. (Insurance Product No.)
The user selects an insurance product. Once selected, the system will fill in the name of the product.
Framework Agreement No. (Framework Agreement No.)
ID of the framework agreement for which the rates are to be imported.
Set No. (Set No.)
ID of the set under which the seedlings are to be imported.
Active (Active)
The user marks the set as "active" when importing the sheet. Sets cannot be imported for inactive template rows
For each template row (i.e. for each sheet of the import file), the user defines a Mapping. In "mapping", the user specifies how the system should process individual columns of the import file.
Ins.Rate Import Mapping
User Mappings Set for Insurance Product Batch Columns
Column No.
The user enters the column number of the import file
xlColID
The system fills in the letter of the corresponding column of the import file
Column Type
The user selects from the options
Standard (1:1) (Standard) – the system stores the content of the field in this column in the "target field" of the rate. For example, the content of column 6 is stored in the typesetting in the Filter field of the tags
Conversion (Conversion) – the system uses the content of the field as the "source field of the conversion table", according to which it searches in the defined conversion table and stores the target field of the conversion table in the typesetting"
Constraint (Restriction) - The system will use the contents of the field to compare the constraint condition in the defined "restriction code" and "field value type". A restriction can be specified in multiple columns of an import file. According to the restrictions, it selects the variant of the insurance product. The variant must comply with all restriction conditions.
Calculation method (Calculation Method) - The system uses the contents of the field to select the calculation method. In this case, the user must enter the "calculation base code" and possibly also the "field value type" to determine the method interval.
Variant (participation) – The system uses the contents of the field to select variants according to the participation. "|" can be used to transform participation.
Variant (limit) – the system will use the contents of the field to select variants according to the limit. "I" can be used to transform the limit.
Target Field
The user selects the rate field where the system saves the result
Target Field Name
After selecting the target field, the system will automatically fill in the
Conversion Table No.
In the case of conversion, the user must enter the conversion code list in which the system is searching.
Conversion Table Name
After selecting a conversion table, the system will automatically fill in the name of the conversion table
Source Conversion Table Field No.
In the case of conversion, the user must specify which field is the source field in the conversion code list. That is, according to which he searches in it.
Source Conversion Field Name
After selecting the source field of the conversion table, the system will automatically fill in the
Target Conversion Table Field No.
In the case of a conversion, the user must specify which field is the target in the conversion code list. I.e. Which will be used as a result
Target Conversion Field Name
After selecting the target field of the conversion table, the system will automatically fill in the
Restriction Code
For constraints, the user must select which constraint it is.
Calculation Basis Code
The user fills in the field
Field Value Type
For a field that is used to evaluate a throttling condition or to search for the interval of the insurance calculation method, the user must know which type of value it is
From Value – this is the lower limit
To Value – this is the upper limit
List of values – a list of matching values will be entered in the column
Constraint Values Delimiter
The field is filled in by the user if it is "field value type" = List of values. By default, the system will use "|" (vertical slash), if the user wants to use a different one, it is necessary to enter in this field – e.g. ";" (semicolon).
Note that the first line of the imported file is the header that the system takes into account and does not import the first line.
Example of mapping "calculation method" for POV (liability insurance)
The premium calculation method for third-party insurance is based on engine capacity.
In the Value Type column of the field, the user specifies which column contains the lower value and which contains the upper value of the interval:
From the import source file:
The system selects the premium calculation method for the rate based on the set. It is important that the "subject category" in the method and the imported rate match.
If there is a category in the imported file for which the insurance calculation method is not based on a set, an error message will follow during the import.
Example of constraint conditions
For example, for the HAV variant, three constraint conditions are defined:
For each condition, we need to specify the column, column type = "constraint", constraint code, and field value type in the source file.
For constraints, the system checks to see if the constraint condition matches exactly. For example, the list of values in the import file must be the same as the list of values on the product variant constraint condition. If the user needs to specify a rate filter only once from the list of values, it must have two columns in the import file. One for the rate filter and the other for the restrictions:
Example of boolean constraint
On the product variant, a restriction can be entered based on a field with a logical value of Yes/No (True/False) – e.g. Financed Object Used:
In the source file for importing rates, the column for "used" cars will be filled with the value TRUE and for "new" cars with the value FALSE:
In the mapping, the user then sets the "column type" of the constraint and the value type of the "list of values" field:
Conversion example
In the import file, there is only the text of the usage method. Using conversion, we will search for this text in the Financed Object Usage code list.
We will store the code from the code list in the resulting typesetting:
Example of participation and limit on product variant
If there is a code from the code list, e.g. insurance limits, in the source file for import, just enter the "column number" for mapping and select the "column type" Variant (limit).
If there is a description from the code list in the source file for import, it is also necessary to enter the "conversion table number" (code list), "source field" = description and "target field" = code that is used to find the corresponding variants. In the example, the search for variants according to "participation" based on the description is used.
Example of Different Groups Overlapping Durations
If there is an overlapping duration for different groups, it is necessary to add "Group" to the variant constraint, because there is a different condition for the financing period for different groups.
If the group was up to the rate, both options would be offered for the 60-month contract and the 84-month contract would not include rates.
Start rate import
After the template, rows, and mappings are created, the user starts the import of rates. If there is no row marked as Active on the template, the system reports an error:
If the template setup is with active rows. The user proceeds to enter the file:
If there is an overlapping duration for different groups, it is necessary to add "Group" to the variant constraint, because there is a different condition for the financing period for different groups
If the group was up to the rate, both options would be offered for the 60-month contract and the 84-month contract would not include rates....
Import Log
After importing rates, the system saves the result in the Import Log table. A user can open a log filtered for a given template:
Field:
Entry No. (Entry No.)
Unique key when importing
Template Code (Template Code)
Imported Template Code
File Name (File Name)
Source import file name with path
Sheet Name (Sheet Name)
Source Import File Batch
Row No. (Row No.)
Row No. of the source import file
Insurance Company No. (Insurace Company No.)
Specified insurance company on the template line
Insurance Product No. (Insurance Product No.)
Entered insurance product on the template line
Framework Agreement No. (Framework Agreement No.)
Entered framework agreement on a template line
Set No. (Set No.)
The set can be specified both on the template line and in the source import file
Date of issue (Created At)
Import Start Date
Creation Time (Creation Time)
Processing Time
Rate No. (Rate No.)
If a rate was created by import, a unique key is entered – the number of the created rate
Result (Result)
Success - if the system did not encounter a problem and created a rate
Error – when the system was unable to create a rate
Error Detail (Error Detail)
Specification of the problem in case of an error
User ID ((User ID)
The user who started the import
Examples of errors
Examples of "error details"
No variant found that meets the restrictions
No variant found with restrictions: -FIX_MES/0/60/-COMMODITY/0/0/SZ-PO_SUMA/0/3,000,000/-STARI_MES/25/60/
Import failed because a variant with this limitation was missing:
After reviewing and adding the missing variant, the import was successful.
Variant with the given deductible not found
Variant not found. Filters: Insurance Company No.: CSOB_POJ, Product No.: STROJNI ALLRISK, Participation Code: '<ERROR>'
There is no variant on the Allrisk Machine without a deductible code.
There may be an error in the import file – the deductible should have been there, or it is necessary to create a variant without deductible.
Variant with limit not found
Conversion of the Indemnity Limit value of CZK 8,000 in the Insurance Limit table failed.
Based on the text "Fulfillment limit CZK 8,000", the system did not find the limit code.
The user needs to check the limit dial.
Calculation method not found
It is necessary to check the insurance calculation methods on the set, possibly even at higher levels. The method was not specified, or it differs from the method in the import file. The problem can also be caused by the lack of an interval in the premium calculation method.
The same error is reported by the system even if the system does not find a method of calculating the same category per set according to the "object category" of the imported rate. E.g. the method was set only for machines in the set, but there were rates for both machines and means of transport in the import file. (Calculation methods on the set needed to be added.)
Calculation method interval not found
Calculation method interval not found 51.
There is no interval with a range of 0 – 3000 on the set
Delete insurance company product Rates button
If the import of rates on a new insurance company product results in the creation of rates that contain an error, it is possible to delete the rates once.
There is a hidden button on the insurance company's product card for this purpose.
The button must be made available to the responsible user:
On the "insurance company product" tab, the responsible user will then see a new Delete Rates button:
A prerequisite for deleting the rates of the "insurance company's product" is that there is no insurance contract with this rate.
If the system finds the rate applied to the insurance contract, it will not perform the deletion.
After the successful deletion of the rates, the user can correct any error and Import Rates Again.