Master Data Setup

Bordereaux

Use the instructions on this page to build the two data sets involved in the overall Bordereaux process: normalized data set and the derived data set. Any field that you plan to use in any form in a bordereau or data management project must have a field in the Master Data set.

The normalized data set is the set of data that is based on your raw source data.Working with your raw data, you construct the first set of fields that you will use to bring in raw data. This is the first stage of filtering multiple field names for data down to a set that you can use to map to your derived field names.
The derived data set is the one that contains the data once it has been filtered through a series of rules to convert it into the bordereaux reports that your company submits to its reporting entities.

How It's Done

The process for creating master data sets is as follows:

Create a raw data field for every field that comes in from your source files regardless of whether you plan to use the data element.
Create a derived data element for fields you need in the output file that are not a direct one-to-one map as raw data (you may use a raw data element as a derived field in the Template).

When you are unable to build a rule to map raw data to derived data, create a business rule that assigns an error status to particular records thereby forcing the need to manually correct the value.

Create New Fields

Once the initial (offline) analysis and process of defining normalized and derived data sets has been completed, it's time to enter the data set fields into the system. You can create the fields manually by following the steps below or you can import them. (See the Import / Export topic for details.)

You will have the ability to edit and refine data attributes even after your data elements are in the system.

Once a data element has been used in a mapping template, the name of the element is no longer editable.

From the OWIT Bordereaux Menu navigate to Data Management Services and then to Master Data Setup. From this screen, users can view any data elements already created in the system; as well as, create new ones.

Create Data Fields

Bordereaux > Data Management > Master Data Setup > Create Fields

Use the information below to set up both raw and defined data fields.

Click Create New Field to open the Create Field dialog.
Click the image to change its size.
Refer to the table below when configuring your field definitions.

Field (MetaData)

Definition Description

Field Code

The database / XML name for the field.

required field)

Field Name

This name displays on reports or other outputs as a label.

required field)

Help Text

Provides information to help define the field for users and indicate how it is used.

(required field)

Data Type

Describes the format and how the master data is to be stored:

Integer
Decimal
String
Date
Boolean

(required field)

Minimum Characters

Identifies the minimum number of required characters accepted when the data type selection is String.

Maximum Characters

Identifies the maximum number of allowed characters when the data type selection is String.

Format

Identifies the format appropriate for the field's data type.

Data formats are available to select when creating the field in the Master Data Set.
Decimal formats are defined when the data type is Decimal. The default value is two decimal places (0.00).

Minimum Value

The lowest numeric value accepted when you choose Integer or Decimal as your data type.

Maximum Value

The largest numeric value accepted when you choose Integer or Decimal as your data type.

Default Value

The value populated in a field by default. (Available to all data types with the exception of the Date data type).

Output Value

The value used as results or outgoing data.

PII Data

Check the box if the data contains Personal Identifiable Information (PII). Any representation of information that permits the identity of an individual to whom the information applies to be reasonably inferred by either direct or indirect means.

Field Type

Choose One:

Raw - Select this option when you create fields to accept unaltered incoming data (Inbound field).
Derived - Select this option when you creating fields for outgoing data to a data output set in your template (Outgoing field)

(required field)

ETL

Use the ETL toggle switch to set whether the field is used in Bordereaux (off/default setting) or for ETL (on).

Bordereaux - The Off setting (default) is used for Bordereaux. More fields are available for tagging data according to insurance import types of Premium, Cash, and Claims.
ETL - Click the toggle to turn the setting to the On position for ETL if you intend to use the data manipulation features for something other than insurance.

Click the image to change its size.

Defined Field Type

In the Bordereaux tool, certain data elements are predefined. The default for this field is None. Select one predefined fields type from the drop-down list. A new field associated with one of these defined fields will populate to the specified defined field types in Binder Management reporting.

GWP Original Currency

Net Premium Due

Underwriter Year

Process Year

State

Policy Effective Date

Policy Expiry Date

Net Premium Paid

Transaction Type

Total Insured Value

Total Incurred

GWP Translated Currency

GWP Functional Currency

Cash Received Original Currency

Cash Received Translated Currency

Cash Received Functional Currency

Claims Paid Original Currency

Claims Paid Translated Currency

Claims Paid Functional Currency

Claim Reserves Original Currency

Claims Reserves Translated Currency

Claims Reserves Functional Currency

Acquisition Cost Original Currency

Acquisition Cost Translated Currency

Acquisition Cost Functional Currency

YOA GWP - Original Currency

YOA GWP - Translated Currency

YOA GWP - Functional Currency

Policy Endorsement Effective Date

Policy Endorsement Expiry Date

Date of Loss Payment Plan

Commission Amount

Endorsement/Net Change Amount

Tax/Surcharge Amount

Fees Amount

Accounting Period

Reporting Period

Currency Type

Claim TPA Fee

Admin TPA Fee

Billing From Date

Billing To Date

Billing Due Date

Claim Status

Pay Type

Paid To Date Loss

Paid To Date Fee

Policy Endorsement Effective

Date Policy Endorsement

Expiry Date

Binder Reference

Source Entity

LOB Code

Coverage Code

None

(required when Field Type is Derived)

Import Type

The import type designation is designed for data used in Bordereaux. Each selection used for non-ETL data (aka Bordereaux) identifies the information as pertaining to one of the three insurance-related data types available which are as follows:

Premium - Select this option when importing premium transaction data
Cash - Select this option when importing cash transaction data for policies
Claims - Select this option when you are working with policy claims data
Others - This is the required choice for ETL import types. The system populates this field when the ETL toggle is enabled.

Key

The choices in this section define database keys and designate the transaction type where the data is a key field.

Policy Key - Available only when premium, cash, or claims import types are selected.
Risk Key - Available when any one import type is selected or when all import types are selected.
Coverage Key - Available when any one import type is selected or when all import types are selected.
None (default option)
Claims Key - Available only when the claims import type is selected.
Cash Reconciliation Key - Used to reconcile cash transactions against premium. Available only when premium and cash import types are selected.

(required when Field Type is Derived)

Reference Type

Reference ID and Reference name is used throughout the system to identify individual records in grids.

For Premium and Cash transactions the Reference ID is typically a Policy Number.
For Claims transactions the Reference ID is typically a Claim Number.
Reference Names are typically the Insured Name and used when you have selected both Cash and Premium import types and selected the Reference ID option, then an order of priority must be established where the system recognizes or references them in order.

For example:

In the Premium line, choose whether this type of data will take priority over cash.
In the Cash line, choose whether this type of data will take priority over premium.

Additional Rules:

It is possible to define Reference names and Reference IDs as either Raw or Derived fields.
Can only create one Reference name and Reference ID for per customer/party.
Can only create one primary and one secondary Reference ID per import type.

(required field

Reference Priority

If import type is both Cash and Premium and the Reference ID is used, then an order of priority is established of which record is referenced/recognized first and secondary. For example:

In the Premium line, choose whether this type of data will take priority over cash
In the Cash line, choose whether this type of data will take priority over premium

(required when Reference Type is set to None)

Click the image to change its size.

Field Level

Use to identify if the field you are defining is available in all templates being configured or should only be available to templates being configured to handle a specific line of business. For example Policy Number is usually defined as a Policy Level Field, whereas Vehicle ID Number (VIN) is usually a Coverage or LOB-level field only applicable to Personal or Commercial Auto policies.

Choose one of the three field-level options available:

Retain the None (default) setting when there is no relevance to placing your data field to policy or coverage level in your data hierarchy.
Select the Policy option when the data is relevant at the policy level in your data hierarchy.
Select the Coverage option when the data is relevant to the coverage level in your data hierarchy with subcategories to further define the field.

When you select Coverage, the field level subcatagory dialog displays where you can define a Class of Business (or multiple Classes of Business) and associate the data field to it. In the example below, the field belongs at the coverage level to a commercial property class of business.

Checking the Select box limits the visibility of this data field to when the class of business of a template is Commercial Property. When Mandatory is selected, the data field would always have to have a value present. You can select a class of business without requiring a value in the field.

(required field

Click the image to change its size.

Field Mandatory

When you select the Field Mandatory checkbox, the system puts the record in error status when data is missing for the field during validation.

Once you have completed defining all the elements of the Data Field, click Save to retain your work. To exit without keeping your changes, Click the close icon ( X ) in the upper-right corner of the Create Field box.