Comprehensive Insurance Rates Update Process

Follow these steps to update your Comprehensive Insurance Rates.

Steps

A) Contact your Account Manager

Contract your Account Manager to set up the following:

• schcomprehensive_insurance table structure

• Scheduler Job to update insurance rates table

B) Create a CSV for your new rates

Use the provided example file, 2025-26 rates CSV file, as a template.

Refer to the CSV file structure section below for detailed information on the required format and fields.

C) Setup / Import Table Data

• Select Module: "_sch Scheduler"

• Select Table: schcomprehensive_insurance

• Upload your CSV to the Data File section

D) Schedule and Execution

The Scheduler Job set up by your Account Manager will run at the specified time to update insurance rates table.

Upon execution, this job will automatically add the new rate records from your uploaded CSV file to the table.

CSV File Structure

Content of this file is uploaded to the schcomprehensive_insurance table. It has the following fields. Refer to the 2025-26 rates CSV file for examples.

schcomprehensiveinsurance_uuid

MUST be left blank.

state

Must be one of: ACT, NSW, NT, QLD, SA, TAS, VIC, WA.

classificationname

MUST be one of the values in name column in the qtinsurancetypes table. This must match exactly the name column in the qtinsurance_types table.

Download the existing list using:

• Setup / Export Table Data

• Select Module: "qt | Quotes"

• Select Table: qtinsurancetypes

• Check on Include Field Headers

• Download the table content

lower_limit

Enter the lower limit of the rate band.

• For the first band, use 0

• For subsequent bands, the lowerlimit MUST be exactly $0.01 greater than the previous upperlimit

  • Example: if previous upperlimit is 18000.99, next lowerlimit is 18001

upperlimit

Enter the upper limit of the rate band.

• Must be exactly $0.01 less than the next band’s lowerlimit

• Example: if next lowerlimit is 40001, current upperlimit should be 40000.99

excess_rate

Specify the excess rate for the band.

• Use 0 where no excess applies

• Excess rate must be defined per State and Classification at a specific limit (e.g., 135000)

• Enter as decimal (e.g., 0.0232 = 2.32%, 0.0300 = 3%)

basecostmonthly

Enter the base monthly cost for this rate band.

• For the band that includes an excessrate (row with identical lowerlimit and upperlimit), this must match the basecostmonthly of the highest upperlimit band for that state and classification

effective_from

Enter the date from which this rate becomes effective.

• Format MUST be: YYYY-MM-DD

Insurance Annual

Job Processing (Annual Process)

Insurance Billing Type

This field describes how the vehicle's insurance is billed and paid for. It determines how Novated contract insurance costs are shown in reports:

• Novated Driver

• Novated Driver Budgeted

• Novated Driver Operating

• Novated Driver Budgeted Operating

• Budget Km Variance

When Billing Type = Driver Reimbursement or Client Reimbursement, reports show:

• Actual insurance costs from Contracts / Maintenance tab

• Transactions from Contracts / Payments tab in the "Actual" column

• Only posted payments (using Due Date, not posted date)

Payment timing example

An insurance transaction with Due Date 01/04/2010 paid on 15/05/2010:

• April report run on 8 May → NOT included

• April report run on 16 May → INCLUDED in YTD and LTD actuals

When other Billing Types are used ('Blank', 'Not Billed', 'Part of Lease', 'Recharge'):

• Driver Report Actual = Budgeted amounts + Maintenance transactions

Billing Types

Billing Type Defaults

Insurance Premium

Insurance premiums are calculated using:

• Policy Owner

• Insurer

• Insurance Loc.

• Variant

• Total On Road Price

System supports:

• External calculation (default)

• Internal calculation (client-specific logic)

Insurance Calculation using Postcode (illustr.)

Postcode-based pricing can replace location-based pricing when:

• insurancelocationbypostcodeflag = yes

Uses table:

• qtinsurerlocationsbypostcode

Insurance Premium Setup

Global Controls

• comprehensiveinsurancebasis

• insurancecalculationmethod

• fsl_rates

• isd_rates

• insuranceatfault_rate

• insurancegstcalculation_method

• insurancelocationbypostcodeflag

Required tables

• fm_suppliers (Insurance Broker Fee)

• qtinternalinsurers

• qtinsurancerates

• qtinsurancetypes

• qtinsurerlocations

• qtinsurerlocationsbypostcode

• qtinsuranceloadingtypes

• qtinsuranceloadingrates

Insurance Uplift

A budget item called INSUP calculates monthly allowance for insurance increases.

• Based on Monthly Premium

• Applies to all Quotes

• Requires Posting Class setup

Insurance Uplift Fee (INSUP)

NOTE: To make editable, remove both role restriction records:

• tmpquotebudgetnetINSUP

• tmpquotebudgetgstINSUP |