Lease Inclusion Plans Setup

This page summarises the various flags and tables within Catch-e that need to be set-up and populated with data to enable Lease Inclusion Plans. Contact your Account Manager to help you implement this feature.

Open the Inclusions Model.xlsx file to see how various inclusions can be set-up and calculated. Use this to work out the structure and pricing calculations you want to use to deliver your inclusions.

Feature Highlights

Inclusions can be either financed or budgeted:

• Budgeted inclusions are only visible for the selected Contract Types

• Financed inclusions are visible in quotes for all Contract Types (future work is planned to enable you to control visibility by Contract Type)

Inclusions can be:

• Selected optionally by the user

• Selected automatically by Contract Type

• Hidden by Client, Make, Model or Variant

Plans can be set-up to:

• Be selected directly by the user

• Populated automatically based on the "Term" and/or using the vehicle value (either Depreciable or On Road)

Retail pricing can be:

• Editable by the user

• Populate with a either a stored value, a calculated value using a combination of rate and flat fee (or both)

• Calculations can be set to use GST, Stamp Duty and Fire Service Levy percentages

Quote Placeholders

Visit the Quote Template Placeholders page to get a listing of available inclusion placeholders.

Settlement Placeholders

Visit the Settlement page for a listing of the inclusion placeholders that are available for use.

Renaming

The field name "Total Lease Inclusions" shown in Quotes / Finance can be relabelled.

Inclusion Configuration

Inclusion Fields

The value that is used to populate into a quote comes from the cost_price_net field in qt_inclusion_plans (and/or cost_price_rate) PLUS margin_net unless it is configured to come from the Eric Interface or a bespoke calculation.

You can update a plan's pricing by adding new plan records with a new effective_date.

inclusion_id — System generated.

Example: 9123456789112345678

supplierid — Optional entry for the Eric Interface. If the inclusion is going to be budgeted, you can enter default supplierid here also for reports.

Example: 100000

inclusioncode — Your Inclusion Code. We recommend using a short code in CAPS here. Your code must not include a hyphen (-). e.g INCL-1 is invalid. Use INCL1 instead.

Example: INCLCODE

name — The name of your Inclusion product. This will be displayed as the Quote / Lease "Inclusion" text.

Example: Inclusion Product Name

editflag — Set as 'yes' or 'no'. If 'yes', users can enter or over-write a calculated "Retail" price. If you are using the Eric Interface set this to 'no'. If either calculatestampdutyflag or calculatefslflag = 'yes', then this setting will be ignored and "Retail" will be display only.

Example: 'yes'

provider — Set as 'internal', 'eric' or 'bespoke'. 'internal' to use pricing stored in qt_inclusion_plans. 'eric' to use Eric Interface pricing. 'bespoke' to use a client-specific calculation. Contact your Account Manager to discuss this if you want to develop one.

Example: 'internal'

calculategstflag — Set as 'yes' or 'no'. Set to 'yes' to calculate and add GST to the inclusion. Check that pricing stored in qt_inclusion_plans is net of GST (this is the expected set-up). For Budgeted inclusions, this flag is used instead of the Contract Types / Accruals "G" to determine the budget's GST. The GST Rate applied in calculations comes from gb_control. Quotes / Lease Inclusions "Retail" will show the (GST inclusive) value. Set to 'no' to leave GST out of the calculations. Quotes / Lease Inclusions "Retail" will show the net value. I.e. net and gross values are the same.

Example: 'yes'

calculatestampdutyflag — Set as 'yes' or 'no'. If set to 'no' this setting will be ignored. If set to 'yes', a Stamp Duty component will calculate as part of your inclusion amount. If calculatestampdutyflag = yes, the edit_flag setting is ignored and the "Retail" price is display-only. Stamp Duty Rates are stored in the control gb_control.

Example: 'no'

calculatefslflag — Set as 'yes' or 'no'. If set to 'no' this setting will be ignored. If set to 'yes', a Fire Service Levy (aka Emergency Services Levy) component will calculate as part of your inclusion amount. If calculatefslflag = yes, the edit_flag setting is ignored and the "Retail" price is display-only. Fire Service Levy Rates are stored in the control gb_control.

Example: 'no'

vehiclevaluebasis — Set as 'depreciable-amount', 'on-road-price' or null. Add a vehiclevaluebasis when you have structured pricing that uses a vehicle value either for plan selection or to calculate the amount.

Example: 'depreciable-amount'

financed_flag — Set as 'yes' or 'no'. Set as 'no' for a financed product. In this situation, Retail (Gross) is included in the Quotes / Finance "Total Brokerage" amount. Set as 'yes' if using a budget. In this situation, Retail (Gross) is included as a Quotes / Lease Budget row.

Example: 'no'

postingclassid — Add a class here if the inclusion will be budgeted. I.e. financed_flag = 'no'. You will also need to add this class to the Contract Types / Accruals tab for each Contract Type where the inclusion will be used. You can also configure a matching 'Sundry Payment'. Visit the process page Configure a Sundry Payment to set this up.

Example: 100000

status_flag — Set as 'active', 'inactive' or 'suspended'. Only 'active' plans are available for selection on quotes. If no plans have been created or are set as 'active', the inclusion will still be visible, but disabled.

Example: 'active'

Plans Configuration

Plan Fields

inclusionplanid — System generated.

Example: 9123456789112345678

inclusion_code — Your inclusion code.

Example: INCLCODE

inclusionplancode — Your inclusion plan code. We recommend using a short code in CAPS here. Your plancode must not include a hyphen (-). e.g PLAN-1 is invalid. Use PLAN1 instead.

Example: INCLPLAN

inclusionplansubcode — Your inclusion plan sub code. Use a subcode when creating plans that include term and/or vehicle value ranges. This will enable you to hold a single plan with each range described separately. E.g. you might use term to do this.

Example: 12, 24, 36, 48, 60

inclusionplancodequalified — System generated. This code combines unique values of the plan into one descriptive field. It uses hyphens to join the field, which is why the code, plancode and sub_code cannot contain hyphens!

Example: INCLCODE-INCLPLAN-12-0000-00-00

plan_name — The name of your inclusion plan. This will be displayed in the Quote / Lease "Plan" list box.

Example: Inclusion Plan Name

term_lower — Plans can be set up to use Term within lower-upper limits.

Example: 1

term_upper — Plans can be set up to use Term within lower-upper limits.

Example: 60

vehiclevaluelower — Plans can be set up to be selected when the quote's vehicle value falls within the lower-upper limits. I.e. either 'depreciable-amount' or 'on-road-price' as set in vehiclevaluebasis.

Example: 0.00

vehiclevalueupper — Enter an upper limit here for the plan if you are using a lower-upper limit range.

Example: 99999.99

costpricenet — Cost price of the plan. GST is added to this when calculategstflag = yes.

Example: 0.00

costpricerate — Cost price rate of the plan. Used when vehiclevaluebasis is populated and costpricerate > 0. GST is added to this when calculategstflag = yes.

Example: 0.1234

marginnet — Margin price of the plan. GST is added to this when calculategst_flag = yes.

Example: 0.00

effectivedate — Use this to determine when the plan pricing is to start from. A plan is only visible when the effectivedate is on or less than the quote's "Effective Date". The first set of records are normally set with a blank date so that selection is not initially limited.

Example: '0000-00-00'

status_flag — Set as 'active' or 'inactive'. The status of the plan. Only 'active' plans can be selected. If a quote contains an inactive plan, it will still be included and shown, but if it's removed, it cannot be added again.

Example: 'active'

Contract Type Defaults

Selected Lease Inclusion Plans and default into new quotes for a particular Contract Type if configured to do so. Default records are stored in the table qt_inclusion_posting_map_defaults by Lease Inclusion Plan and Contract Type.

When a Contract Type is selected on a quote, this table is checked. If matching records are found, the matched inclusions are checked automatically.

If there is only one active plan available for a selected inclusion, then the plan and the "Retail" price will also populate.

Create default records by creating a file to populate new records into the table qt_inclusion_posting_map_defaults for the required Lease Inclusion Plan and Contract Type combinations. Use the Import Table Data screen to update the table.

NOTE: This feature only works when the Contract Type "Group" is 'Novated'.

Client Exclusions

Go to the Clients / Quote Defaults tab and check any inclusions you do not want to offer to that client. If checked, the inclusion will not show in the Quotes / Lease Inclusion section.

Make, Model or Variant Exclusions

Lease Inclusion Plans can be excluded at make, model or variant level. This setting is hierarchal. If a make is excluded, all models and variants will be excluded. Once set up is complete the 'Lease Inclusion' will not be allowed to be included in a quote.

Contact your Account Manager to assist with this set up. Alternatively you can upload data into the table qt_inclusion_vehicle_exclusions using the Import Table Data feature.

Exception Reporting

If you are setting up Inclusions, please create a new Scheduler Job linked to the Query named 'Inclusions Exception Report'. This report will alert you if there are any bad data set up scenarios which in turn will prevent you being able to create/edit quotes.

Removing Inclusions

Option 1: Set to Inactive

Set the status_flag = 'inactive' for the inclusion that you wish to remove.

Once done, the inclusion will not be visible in the list box of new quotes or existing quotes where the inclusion is not included. i.e. financed_flag = 'no'.

If a quote has an inactive inclusion selected, it will be de-selected if the quote is edited and will then no longer be available or visible.

Option 2: Set to Suspended

Set the status_flag = 'suspended' for the inclusion that you wish to remove.

Once done, the inclusion will not be visible in the list box of new quotes or existing quotes where the inclusion is not included. i.e. financed_flag = 'no'.

If a quote has a suspended Inclusion selected, it will be remain on the quote even if the quote is edited however, the plan will be read-only.

If you remove the suspended Inclusion then it will no longer be available or visible.

Decommissioning a Plan

Set the status_flag = 'inactive' for the plan that is not required.

Once done, the plan will not be visible in the list box of new quotes or existing quotes that do not have this plan chosen.

If a quote has an inactive plan selected, it will be de-selected if the quote is edited and will then no longer be available or visible.

Useful Queries

Set-up Fields

SELECT * FROM gb_screen_aliases; SELECT * FROM gb_screen_aliases WHERE field = 'total_inclusion_plans'; 

Set-up Inclusions

SELECT * FROM qt_inclusions; SELECT * FROM qt_inclusion_plans; 

Defaults and Exclusions

SELECT * FROM qt_inclusion_posting_map_defaults; SELECT * FROM qt_inclusion_client_exclusions; SELECT * FROM qt_inclusion_vehicle_exclusions; 

Inclusion Exceptions Report

SELECT inclusion_code, posting_map_code, variant_code, variant_id, model_code, model_id, make_code, make_id, client_code, notes, source_table FROM ( -- Default into Quotes by Contract Type SELECT i.inclusion_code, pm.posting_map_code, '' AS variant_code, '' AS variant_id, '' AS model_code, '' AS model_id, '' AS make_code, '' AS make_id, '' AS client_code, 'Included by default (unless excluded elsewhere)' AS notes, 'qt_inclusion_posting_map_defaults' AS source_table FROM qt_inclusion_posting_map_defaults id INNER JOIN gl_posting_maps pm ON pm.posting_map_id = id.posting_map_id INNER JOIN qt_inclusions i ON i.inclusion_id = id.inclusion_id AND i.status_flag = 'active' UNION ALL -- Excluded by Make/Model/Variant SELECT i.inclusion_code, '' AS posting_map_code, v.variant_code, v.variant_id, mo.model_code, mo.model_id, mk.make_code, mk.make_id, '' AS client_code, 'Excluded by Make/Model/Variant' AS notes, 'qt_inclusion_vehicle_exclusions' AS source_table FROM qt_inclusion_vehicle_exclusions ive INNER JOIN qt_inclusions i ON i.inclusion_id = ive.inclusion_id AND i.status_flag = 'active' LEFT JOIN qt_variants v ON v.variant_id = ive.variant_id LEFT JOIN qt_models mo ON mo.model_id = ive.model_id LEFT JOIN qt_makes mk ON mk.make_id = ive.make_id UNION ALL -- Excluded by Client SELECT i.inclusion_code, '' AS posting_map_code, '' AS variant_code, '' AS variant_id, '' AS model_code, '' AS model_id, '' AS make_code, '' AS make_id, cl.client_code, 'Excluded by Client' AS notes, 'qt_inclusion_client_exclusions' AS source_table FROM qt_inclusion_client_exclusions ie INNER JOIN fm_clients cl ON cl.client_id = ie.client_id INNER JOIN qt_inclusions i ON i.inclusion_id = ie.inclusion_id AND i.status_flag = 'active' ) ib -- WHERE inclusion_code = '' ORDER BY inclusion_code, posting_map_code, client_code; 

Quote Data

SELECT * FROM qt_quote_inclusion_plans WHERE quote_id = '10000X'; 

Troubleshooting

Quotes / Lease - Lease Inclusion Plans - the Inclusion is Visible, but the Check Box is Disabled

The quote's "Effective Date" must be on or after the date stored in the Lease Inclusion Plan record (effective_date field).