RAC Cover File & Data Processes

RAC Cover File & Data Processes RAC Process Document

Support Document

Version 0.5

RAC Commercial Team

1

Go Home

Jo Cardew - RAC Motoring Services RAC House, Brockhurst Crescent, Bescot, Walsall, WS5 4AW

Contents

SECTION 3: COVER FILE TRANSACTIONS

8

SECTION 1: DATA PROCESSES

4

COVER FILE TRANSACTIONS

Delta files are our preferred type of cover files.

Table of contents

Contents & Highlights / 2 Introduction & Highlights / 3 Section 1: Data Processes / 4 RAC Systems / 5 Section 2: Single Asset Policies / 6 Unique Asset Identifier / 7 Section 3: Cover File Transactions / 8 Transaction Flags / 9 Examples of Delta Transactions / 10 Updating an Asset Record / 11 Changing Cover Levels / 12 Cancelling an Asset Record / 13 Section 4: Exception Reporting / 14 Process to Report Exceptions / 15

Section 5: Data Feed Process / 16 Data Feed Process Flow / 17 Section 6: Cover File Formatting / 18 Cover File Formatting / 19 Encoding Specific Values / 20 Section 7: Cover File Fields / 22 Cover File Fields / 23 Cover File Fields / 24 Cover File Fields / 25 Cover File Fields / 26 Cover File Fields / 27 Section 8: Appendix / 28 Example Full Refresh Cover File / 29 Next Steps / 30

DATA PROCESSES In this document you will find information on data required from you by RAC, the format we (RAC) need to receive that data in, and how that data will be handled from a transactional processing perspective.

2

Go Home

SECTION 4

14

SECTION 5: DATA FEED PROCESS

16

EXCEPTION REPORTING When we receive a cover file from you, we will put that file through a data validation process.

DATA FEED PROCESS

The diagram shows you the process involved as a decision tree.

SECTION 7

22

In This Document In this document you will find information on data required from you by RAC, the format we (RAC) need to receive that data in, and how that data will be handled from a transactional processing perspective. See Section 5 for the process overview.

COVER FILE FIELDS The table contains specific field by field information and should be read in conjunction with section 6.

3

Go Home

1

Data Processes

In this document you will find information on data required from you by RAC, the format we (RAC) need to receive that data in, and how that data will be handled from a transactional processing perspective.

4

Go Home

RAC require customer data in order to execute three functions relating to RAC Breakdown and related products sold by you:

Relevant asset and cover information is also transmitted from D4B to our Breakdown Management system “I/CAD”; for example, that Asset “X” has R/REC/AH cover and is active from 10/12/2016 until 09/12/2017, Asset “Y” has R/REC/AH/OT and is active from 15/12/2016 until 05/05/2017. I/CAD (Intelligent Computer Aided Despatch) is the Operational system used by our Breakdown Assistance and Customer Solutions Centres. Colleagues within our contact centres will use the customer data to validate entitlement to service, register a claim against the breakdown policy and provide the appropriate solution to each breakdown incident (claim). Once a breakdown incident is completed, incident data is then transmitted back to D4B to: a. Associate a Claim back to the asset cover record for usage purposes and b. Produce a Pay on Use invoice where needed (some clients will be 100% POU, others will have elements of an incident that are POU, such as parts fitted at the roadside)

• Claims Handling • Invoice production • Support MI Production

RAC Systems When your cover file is submitted to RAC, it is processed through a number of validation checks and protocols that we call CCID (Customer Cover Input Data). CCID determines if all mandatory data items have been received and whether data has been presented in approved formats. Where needed, CCID will produce exception reports (see section 4). Once CCID has completed its checks, Customer data is loaded into our Drive 4 Business (D4B) system, which is our contract database and billing engine. Assets are aligned to agreements within a Client Hierarchy. D4B will calculate appropriate subscription charges due for each asset received, and will produce the relevant invoice in line with the contractual invoice periods.

5

Go Home

2

Single Asset Policies The following information details how data transactions should be sent to RAC for Single-asset policies.

6

Go Home

The following information details how data transactions should be sent to RAC for Single- asset policies. Please refer to Sections 6 & 7 for specific formatting information. An Asset is the physical entity entitled to service – this can therefore be a vehicle OR a beneficiary (person) – but NOT both. 2.1 Unique Asset Identifier (UAI) Data items such as registration numbers are NOT always unique to a specific asset – for example, a cherished plate (reg number) can be moved from one vehicle to another, therefore meaning that it can no longer be used as a way to uniquely identify a specific vehicle. Similarly, for beneficiary based policies, a name is rarely a unique identifier. For these reasons, each asset – whether a vehicle or a beneficiary - should always be presented with a unique asset identifier. This UAI should be used whenever sending us a transaction relating to that specific asset – regardless of the transaction type.

For single asset policies:

• The file should contain one line per asset per policy per change • Transactions should be in the order in which the changes should be processed, for example, a policy needs to be ADDED before it can be UPDATED or CANCELLED

7

Go Home

3

Cover File Transactions Delta files are our preferred type of cover files. Please refer to Sections 6 & 7 for the fields that are expected and any restrictions on the data type to be sent in each field.

8

Go Home

3.1 Transaction flags As we are asking you to send us delta files, we need to ensure that we can interpret the transactions that each line on the file represents. The table below details the transaction flags that should be sent, and when each should be used, including any additional information or accompanying data that is specific to each type of transaction.

Transaction Description

Flag

Should be used when...

Accompanying Data

Retail Sold Price should ALWAYS be sent with an “A” transaction The cover start date should ALWAYS be presented as the original cover start (inception) date of the asset

Notifying RAC of a new asset to go on cover

A

Add

Notifying RAC of an update or amendment to an existing asset – one that you have previously sent us an “A” record for. The “A” record can be contained in the same cover file, as long as it precedes the “U” in terms of row order. Notifying RAC of a cancellation that is to take immediate effect and that, where appropriate, will invoke any cancellation rights

U

Update

D

Delete

N/A

Retail Sold Price should ALWAYS be sent with an “R” transaction

Notifying RAC that an existing policy and its associated asset(s) should be renewed.

R

Renew

All of the Above

Should only be used when sending us a DELTA cover file

Full Refresh Files - Did You Know...

Delta files are our preferred type of cover files. If you cannot send us delta files, we can accept full refresh files: • Full refresh files should contain all the assets covered at the file creation date. • Each asset should only appear once with a corresponding UAI • LAIs should be used to indicate all assets covered under multi-asset policy

• The “Transaction Flag” field should not be used See Section 8 for examples of total refresh cover files

9

Go Home

3.2 Examples of Delta Transactions Delta files are our preferred type of cover files. Please refer to Sections 6 & 7 for the fields that are expected and any restrictions on the data type to be sent in each field. This section will show you examples of common transactions sent in Delta Cover files. (N.B. all financial values are for illustrative purposes only. IPT is at 12% as per June 2017 rate)

3.2.1 Adding a New Asset Record 3.2.1.1 Adding an asset with Standard (Purchased) Cover.

To add new asset records, we need a line per asset with all mandatory asset details, a Unique Asset Identifier per asset, a transaction flag of A, with Retail Sold Price, Commission, Net Sold Price and IPT values

3.2.2 Adding an asset with Mandatory Cover Where breakdown cover is an automatic inclusion to the insurance cover being purchased, this is considered to be “mandatory” cover – as the end consumer is not actively purchasing the cover. The RAC Agreement number is the item of data that informs which level of cover the asset is entitled to. As the cover is mandatory, this also means that there is no Retail Sold Price, and therefore the transaction for adding a new mandatory cover will look like this:

10

Go Home

3.2.3 Updating an Asset Record Updates to asset cover records are processed with immediate effect, and therefore should be submitted to us ONLY when the change needs to take effect. The UAI (Unique Asset Identifier) is critical and the cover start date must be the original cover start date of the asset. 3.2.3.1 Change of Registration (Vehicle based policy) For a change of registration ONLY – i.e. the physical vehicle is the same, it is simply the registration number that is changing, usually due to a cherished plate being added – we would expect to see an update (U) line indicating the new registration number. The UAI tells us that it is the same physical vehicle. This example shows the addition and the update being sent within the same file.

The transaction values would be the same if the Update line (U) were sent in a subsequent separate cover file to the A (Addition) transaction.

3.2.3.2 Change of vehicle If within the terms of the contract and product, a customer is allowed to change their vehicle (asset) without incurring a charge for a new policy, then the transaction that should be submitted within the cover file should look like the example below. In this example, we are changing the first two assets from the Adding a New Asset example in 3.2.1.1.

11

Go Home

3.2.3.3 Change of cover level – Optional to Optional The RAC Agreement Number is the data item within the cover file that indicates the level of cover. Where you are selling more than one level of cover (e.g. Roadside, Roadside & Recovery, Roadside/ Recovery/At Home etc), a customer may want to change their cover. To do this, the transactions should: • Cancel (D) the asset against the original agreement number - any refund due will be automatically calculated, regardless of any negative premium information you may enter in the cover file • Indicate a new record (A) against the upgrade agreement number – as this is an ADD transaction, all financial fields should be completed • Again the UAI is critical to ensure the correct processing of this transaction

3.2.3.4 Change of cover level – Mandatory to Optional In Section 3.2.2, we added an asset with Mandatory cover, and therefore no retail price was involved. If a customer chooses disregard the mandatory cover in favour of an Optional (higher) level of cover, it is anticipated that they will have to pay an additional premium for this transaction, and therefore the transaction should look like this:

In this example, the mandatory and optional levels of cover can exist in isolation – there is no co- dependency on each other.

12

Go Home

3.2.3.5 Upgrade to level of cover - Mandatory cover PLUS Optional levels of cover Where a mandatory level of cover exists, and a customer has the option to upgrade to ADD additional levels of cover, for example their mandatory cover gives them Roadside Only cover, and they want to add Recovery & At Home – the transaction should look like this, with the same UAI being used against the two transaction lines, as it is the same “asset” existing on the two agreements:

In this example, the additional levels of cover can NOT exist without the mandatory cover still being active.

This is distinctly different to them moving from Roadside only mandatory agreement to an optional R/REC/AH agreement (see 3.2.3.4).

3.2.4 Cancelling an Asset Record Cancellations (D) of asset cover records are processed with immediate effect, and therefore should be submitted to us ONLY when the cancellation needs to take effect. The UAI (Unique Asset Identifier) is critical. If a cancellation is within a contractual cooling off period, then a refund will be automatically calculated, regardless of any negative premium information you may enter in the cover file and subject to cancellation terms relating to service being used.

A cancellation transaction (D) can be sent within the same cover file as the add transaction (A), as long as the A transaction precedes the D within the file, as shown in the above example.

Alternatively, a D transaction can be sent in a subsequent cover file to the A.

13

Go Home

4

Exception Reporting

When we receive a cover file from you, we will put that file through a data validation process.

14

Go Home

• To ensure that we have all the mandatory data items that we need to process each line • To identify any data items that, whilst not preventing us from putting an asset on cover, don’t look right (for example- letters in a phone number field) • To identify any exceptions – data items that prevent us from putting an asset on cover or processing a transaction correctly

Every time you send us a cover file, you will receive a confirmation email from us. This email will:

a. Confirm that we have received a file b. Confirm the name of that file and the number

of records processed

If we have identified some exceptions within the cover file, the email will look like the example below, detailing the number of records processed, rejected, accepted with quality issues and accepted without problems. The email will also include an attachment

The attachment within the email will give full details of which records have been rejected or have data quality issues, and will also take you through what to do next.

15

Go Home

5

Data Feed Process

The diagram shows you the process involved as a decision tree.

16

Go Home

Exception report sent to client detailing items that have caused the exceptions and the exception reasons

Any Exceptions?

Data items amended and submitted as a new file

END

YES

NO

Daily CSV Export file to detail: 1.New Business(A) 2.MTAs (U) 3.Cancellations (D) 4.Renewals (R)

File placed in SFTP Location

Execution report issued confirming file received

RAC CCID process validates file and content – transmits to RAC D4B Database

Collect data and pass to RAC SFTP client server

START

RAC Operational platform (ICAD) updates with active and inactive assets

SERVICE PROVIDED

Incident Data to D4B for usage and POU Billing purposes

Incident data to EDS for any Customer Reports

RAC Activity

Key

Client Activity

Additional Info Only

17

Go Home

6

Cover File Formatting

The following table contains key technical information regarding the formatting of the file and should be read in conjunction with Section 7 for specific field by field information.

18

Go Home

Technical Element Detail

Detail

Notes & Examples

Our preference is to receive files daily. We can accept files 24/7, 365 days a year

File Frequency

If you have not been provided with your SFTP login details, SFTP folder locations and IP address, please contact your RAC Account Manager We do not accept .xml or text files with fixed length fields.

File Transmission

Our preference is to receive files over SFTP

Our preference file type is .csv We can also accept .dat .cum .txt .xls .xlsx File encoding should be done in UTF-8

File type

See Also “Encoding of specific values” table below

Encoding

Line endings

Line endings should be CR/LF

If you have not been provided with your client identifier, please contact your RAC Account Manager Note that we will not accept a file that has the same name as a file that you sent us previously. It will allow us to distinguish different files Example: ABC01.2016-02-09T14-07-23.csv

The file name should match the following pattern: [client identifier – 3 letters, 2 numbers].[yyyy-mm-dd]T[hh- mm-ss].csv

File Name

Header/ Footer

Each file should have a header row, but no footer/trailer row

All columns should be included in the file in all cases (even the columns that you do not fill) Fields should be delimited by commas (,), and double quotes (“) should be used as a field qualifier. If double quotes are used in a field, then they must be escaped with a backslash. Field values should be trimmed (extra spaces are not allowed in fields). In order to correctly format your field values, you should use all the information (data type and length, authorized values, unit of measure, minimum length) provided in Section 7

Columns

Example: “AF64CUH”, “Doe”, “John”, “12\” tyres”

Fields

Field values

19

Go Home

Encoding of specific values

Value

Encoding requirements

Examples

Nulls

Nulls should be encoded as empty strings

Numbers with decimal value should be encoded with a dot (.) as a decimal separator. Numbers without a decimal value (integers) can either be encoded with a decimal separator followed by zeros, or without a decimal separator. There is no separator for the thousands. Negative values should be encoded with a minus sign (-) in front of the number.

“142” “142.00” “2128.42” “-142”

Numbers

Dates

Dates should be encoded as YYYY-MM-DD (ISO 8601).

“2016-12-31”

Dates with times should be encoded as YYYY-MM-DDTHH:MM:SS (ISO 8601) in the UK time zone.

Dates with times

“2016-12-31T14:03:21”

Telephone numbers

Telephone numbers should be encoded following the E.123 international notation.

“+44 1922 434633”

Vehicle Registration Numbers

Vehicle registration numbers should be in upper-case font, without space.

“AG59DEW”

Units of Measure

Values with units of measure should be encoded without symbol

Postcodes Postcodes should be encoded in upper characters with space.

“WR9 9LA”

“GB” for the United Kingdom “DE” for Germany

Countries should be encoded following ISO 3166-1 alpha-2

Countries

Please see Section 7 for the field by field information for populating the Cover File.

20

Go Home

21

Go Home

7

Cover File Fields

The following table contains specific field by field information and should be read in conjunction with section 6.

22

Go Home

Column name

Description

Data type Mandatory/ Optional

List of authorized values

[Unused field, for future usage only]. This field de- scribes the type of line - is it related to policy, vehicle or beneficiary This field describes the record - is it a new asset, an update, a cancellation, etc. [Unused field, for future usage only]. For future updates only. This field stores the date at which this record should be taken into account by RAC systems. If this field is empty, then the record will be taken into account as soon as possible

- P (for policy) - B (for beneficiary) - V (for vehicle)

Line identifier

Not currently used

Char(1)

Mandatory for delta files, Forbidden for full refresh files

- A (for acquisitions) - U (for updates/MTA) - D (for deletions/ cancellations) - R (for renewals)

Transaction Flag

Char(1)

Refer to Section 6 for the format that should be used

Effective Date

Not currently used

Date

Only UK and Ireland plate numbers are accepted Refer to Section 6 for the format that should be used Refer to Section 6 for the format that should be used The list of possible RAC Agreement numbers shall have been provided to you in advance

Mandatory if cover is not beneficiary- based

Vehicle Registration Number

This is the vehicle plate number.

Varchar(10)

Registration Country

Country in which the vehicle was registered.

Varchar(50)

Mandatory

RAC Agreement Number

This is the identifier of the scheme under which the asset is insured.

Varchar(30)

Mandatory

Partner ID Unique ID of the partner

Optional

[NA]

(broker/office/agency/etc.) Varchar(20)

23

Go Home

Column name

List of authorized values

Description

Data type Mandatory/ Optional

Partner Name

Name of the partner (broker/office/agency/etc.) Varchar(100)

Optional

[NA]

Refer to Section 6 for the format that should be used

Cover Start Date

This is the first day of the coverage

Mandatory for delta files only

Date

Mandatory for delta files if variable agreement

This is the last day of the coverage - assets are still covered on that date [Unused field, for future usage only]. This is a list of optional extras chosen for this asset This is what uniquely identifies an asset (vehicle or beneficiary) This identifier links assets that belong to the same group (in the case of multiple-vehicle or multiple-beneficiary agreements) for example, a policy number Gross retail price. The price at which the broker

Refer to Section 6 for the format that should be used

Cover End Date

Date

Optional extras

Varchar(50) (200)

Not currently used

[NA]

Unique Identifier

Varchar(50)

Mandatory

[NA]

Mandatory for multi-assets policies

Linked Identifier

Varchar(50)

[NA]

Refer to Section 6 for the format that should be used

Retail Sold Price

sells the cover to the customer. Retail Sold Price = Commission + Price to RAC + IPT

Number(8,2) [£]

Mandatory

Refer to Section 6 for the format that should be used

Number(8,2) [£]

Commission This is the broker commission

Optional

Net Sale Price. This is the Gross Retail Sold Price MINUS IPT

Refer to Section 6 for the format that should be used

Net Sold Price

Number(8,2) [£]

Optional

24

Go Home

List of authorized values

Column name

Description

Data type Mandatory/ Optional

The IPT shall be equal to % of premium at current prevailing rate

Refer to Section 6 for the format that should be used

Insurance Premium Tax

Number(6,2) [£]

Optional

This is the manufacturer of the vehicle

Mandatory if vehicle based

Make

Varchar(50)

[NA]

This is the model of the vehicle

Mandatory if vehicle based

Model

Varchar(50)

[NA]

Type of vehicle - Moped, scooter, hatchback, saloon, etc.

Vehicle Type

Varchar(20)

Optional

[NA]

Mandatory if the vehicle has a motor manufacturer free cover period

Date the vehicle was first registered. Also referred to as DVLA date

Refer to Section 6 for the format that should be used

Registration Date

Date

Vehicle Identification Number. Same as chassis number. It is a unique code including a serial number, used by the automotive industry to identify individual motor vehicles This is the vehicle mileage at the date of acquisition (or renewal for the renewals)

VIN

Char(17)

Optional

[NA]

Refer to Section 6 for the format that should be used

Integer [Miles]

Vehicle Mileage

Optional

Transmission Type

Manual or Automatic

Varchar(30)

Optional

“Manual”;”Automatic”

This is the kind of fuel used by the vehicle

“Petrol”; “Diesel”; “Electric”; “Hybrid”

Fuel Type

Varchar(8)

Optional

Refer to Section 6 for the format that should be used Refer to Section 6 for the format that should be used

Integer [Cubic Centimeters]

Engine Size

Engine Size

Optional

Height

Height of the vehicle Number(8,3) [Meters]

Optional

25

Go Home

List of authorized values

Column name

Description

Data type Mandatory/ Optional

Refer to Section 6 for the format that should be used Refer to Section 6 for the format that should be used Refer to Section 6 for the format that should be used Refer to Section 6 for the format that should be used

Length

Length of the vehicle Number(8,3) [Meters]

Optional

Width

Width of the vehicle Number(8,3) [Meters]

Optional

Weight

Weight of the vehicle Number(5,0) [Kilograms]

Optional

Tyre Size

Tyre Size

Varchar(20)

Optional

Vehicle Colour

Vehicle colour

Varchar(50)

Optional

[NA]

Mandato- ry if cover is benefi- ciary-based or hybrid Mandato- ry if cover is benefi- ciary-based or hybrid Mandato- ry if cover is benefi- ciary-based or hybrid

Honorific of the beneficiary

Title

Varchar(4)

[NA]

First name of the beneficiary

Forename

Varchar(50)

[NA]

Last name of the beneficiary

Surname

Varchar(50)

[NA]

Name of the company in which the beneficiary is working

Company Name

Varchar(50)

Optional

[NA]

Refer to Section 6 for the format that should be used

Date of birth of the beneficiary

Date of Birth

Date

Optional

26

Go Home

Column name Description Data type Mandatory/Optional

List of authorized values

First line of beneficiary address Second line of beneficiary address Third line of beneficiary address Fourth line of beneficiary address Fifth line of beneficiary address

Mandatory if cover is beneficiary-based or hybrid Mandatory if cover is beneficiary-based or hybrid

Address Line 1

Varchar(100)

[NA]

Address Line 2

Varchar(100)

[NA]

Address Line 3

Varchar(100)

Optional

[NA]

Address Line 4

Varchar(100)

Optional

[NA]

Address Line 5

Varchar(100)

Optional

[NA]

Mandatory if cover is beneficiary-based or hybrid Mandatory if cover is beneficiary-based or hybrid

Refer to Section 6 for the format that should be used Refer to Section 6 for the format that should be used Refer to Section 6 for the format that should be used Refer to Section 6 for the format that should be used “Personal”; “Business”

Postcode

Postcode

Varchar(8)

Country

Country

Varchar(50)

Address Type Personal or Business

Char(8)

Optional

Home Phone Number

Home Phone Number

Varchar(20)

Optional

Mobile Phone Number

Mobile Phone Number

Varchar(100)

Optional

Email Address Email Address Varchar(200)

Optional

[NA]

Client Reference 1 Client Reference 2 Client Reference 3 Client Reference 4

Additional information Varchar(200) Additional information Varchar(200) Additional information Varchar(200) Additional information Varchar(200)

Optional

[NA]

Optional

[NA]

Optional

[NA]

Optional

[NA]

Client Reference 5

Additional information Varchar(200)

Optional

[NA]

27

Go Home

8

Appendix

Examples for elements and information eluded to in this document’s sections.

28

Go Home

8.1 Example Delta Cover File

8.2 Example Full Refresh Cover File With Full Refresh Cover Files, each subsequent file overrides the preceding one – therefore no transaction flags are required; each cover file represents those assets that are on cover at that point in time and remain on cover until the next cover file is received. Therefore any new records are treated as additions, and any records not sent on a subsequent file are treated as deletions. UAIs are still used to indicate changes (e.g. change of registration), as highlighted in the example below. The file and field formatting should still follow the allowed values as per Sections 6 & 7 of this document.

File 1

File 2 This file is making:

• 1 addition the final asset (FL52RFT) on File 2 did not appear in file 1, and therefore will be added as a new asset and become billable for the first time. You can see that this is the only asset that has financial data associated with it (green highlighted section) • 1 update the red highlighted section is a change of registration – the UAI is the key data item for this transaction. • 1 deletion “TK59MAN” appeared in file 1, but not in file 2, and will therefore be cancelled with immediate effect. If this is within a contractual cooling off period, then a refund will be automatically calculated.

29

Go Home

RAC DATA FILE & PROCESSES

Next Steps

Further to your review of of this document, if you feel you need any further support then please contact your RAC Account Manager. Contacts For data submission or exceptions contact: 1st level: Your RAC Account Manager 2nd level: CCID Team / email CCID@rac.co.uk

30

Go Home