Commission

The system can be set-up to return calculated values for the commission rate and fee. These are triggered when Include Commission flag is checked.

Changing any of the following fields will trigger a recalculation of the commission rate and fee: Effective Date, Contract Type, Term (Months), Client Code or Financier.

Open the commission model to see how the commission calculates using different settings.

Order of Rules

Commission Lock & Recalculation Control

  • [gb_controls]recalculate_commission_flag

    • If set to yes, ignore any commission lock and execute the full rule hierarchy below

    • If set to no, respect the commission lock flag (see below)

  • If Quotes / Finance "Lock Commission" flag is checked

    • Standard commission recalculation is not executed

    • Existing "Commission Rate / Fee" values are retained

    • Continue only to rules that explicitly override the lock (e.g. Agreed Lessee Rate, Include Commission, Commission Max enforcement)

  • If Quotes / Finance "Lock Commission" flag is not checked

    • Continue to next step

  1. Setup / Reference Data → qt_financier_posting_map_defaults → Detail "Include Commission"

    1. is unchecked, the Quotes / Finance "Commission Rate / Fee" fields are set to 0.00 and the fields are disabled.

    2. commission lock overrides any Lock Commission setting and recalculation behaviour

    3. is checked, continue to next step

  1. If Clients / Quote Defaults "Agreed Lessee Rate"

    1. is populated, then Quotes / Finance "Commission Rate / Fee" are updated to achieve the "Agreed Lessee Rate".

    2. commission lock overrides Lock Commission and recalculate flags (commission will always be recalculated)

    3. is not populated, continue to next step

  1. If Setup / Reference Data → qt_financier_posting_map_defaults → Detail "Commission %"

    1. is populated, this will be calculated and applied to the quote (the Commission rate(%) selected will use the record where the term is equal to, or lower than the term entered in the quote)

    2. commission lock only applies when commission recalculation is allowed (lock not active OR recalculate flag = yes)

    3. is not populated, continue to next step

  1. If Clients / Quote Defaults "Commission Rate"

    1. is populated, this will be calculated and applied to the quote

    2. commission lock only applies when commission recalculation is allowed (lock not active OR recalculate flag = yes)

    3. is not populated, continue to next step

  1. If Setup / Reference Data → gb_controls "novated_commission_rate"

    1. is populated, this will be calculated and applied to the quote

    2. Commission lock only applies when commission recalculation is allowed (lock not active OR recalculate flag = yes)

    3. is not populated, then Quotes / Finance "Commission Rate / Fee" will be set to 0.00.

  1. If Setup / Reference Data → qt_financier_posting_map_defaults → Detail "Commission Max" and/or "Commission Max %" are populated and the calculated commission is lower than the stored commission in the quote

    1. then the lower, calculated commission will be applied to the quote.

    2. The commission lock rule applies to both calculated and locked commission values (lock does not prevent enforcement)

    3. If commission is reduced due to max limits, the commission rate will also be updated accordingly and the lock state will remain unchanged.


The value nominated in the Setup / Reference Data → gb_controls "commission_rate_basis" field is used as the base rate for this calculation (i.e. 'Depreciable Amount', 'Amount Financed' or 'On Road Price').

"Commission Max %" is calculated using the Setup / Reference Data → qt_financier_posting_map_defaults → Detail "Commission Max Basis" setting. This basis is separate from the Global Control "commission_rate_basis" because this calculation is typically dictated by the Financier.

Note: When Setup / Reference Data → gbcontrols "redundancyinsurance_basis" is also set to 'Amount Financed', the Quotes / Finance "Commission Fee" is calculated after "Redundancy Insurance".

When the Rules are Triggered

The calculation in the commission model and the rules above are triggered in a quote in the following scenarios:

  • When a quote is created by a user in the system or by running the CreateQuotes api.

  • When the "Effective Date" or the "Client Code" is changed

  • When the "Type Code", "Term (Months)" or "Financier" is changed AND Setup / Reference Data → qtfinancierpostingmapdefaults "Novated Commission %" is populated.

  • When the "Type Code", "Term (Months)" or "Financier" is changed AND Setup / Reference Data → qtfinancierpostingmapdefaults "Novated Commission Max" is populated and existing "Commission Rate" is higher than the "Novated Commission Max".

  • When any field that triggers a recalculation of the finance rate is changed AND Setup / Reference Data → qtfinancierpostingmapdefaults "Novated Commission %" is populated.

  • When any field that triggers a recalculation of the finance rate is changed AND Setup / Reference Data → qtfinancierpostingmapdefaults "Novated Commission Max" and/or "Novated Commission Max %" is populated and existing "Commission Rate" is higher than the "Novated Commission Max" and/or "Novated Commission Max %".

When Rules are Not Triggered

The calculation rules below are not triggered in a quote in the following scenarios:

  • When the "Type Code", "Term (Months)" or "Financier" is changed AND Setup / Reference Data → qtfinancierpostingmapdefaults has not been populated.

  • When the "Type Code", "Term (Months)" or "Financier" is changed AND a Setup / Reference Data → qtfinancierpostingmapdefaults record exists but "Novated Commission %" is not populated and "Novated Commission Max" and/or "Novated Commission Max %" is not populated or is higher than the stored commission.

  • When any field that triggers a recalculation of the finance rate is changed AND Setup / Reference Data → qtfinancierpostingmapdefaults has not been populated.

  • When any field that triggers a recalculation of the finance rate is changed AND a Setup / Reference Data → qtfinancierpostingmapdefaults record exists but "Novated Commission %" is not populated and "Novated Commission Max" and/or "Novated Commission Max %" is not populated or is higher than the stored commission.

Setup Notes

The Quotes / Finance "Commission Rate/Fee" field will be set as 'display-only' if the client has a stored "Agreed Lessee Rate", as this will change the commission rate and fee as they are forced to change in the calculation so that the "Agreed Lessee Rate" is matched.

Visit Role Restrictions → Quotes / Finance to check the set-up details.

Useful Queries

-- Returns fields required for Commission Model:
SET time_zone = 'Australia/Melbourne';
SET @quote_id := 100000;
SET @term := '24';

SELECT 
-- Fields required for calculating Commission
  gbc2.value AS 'GBC_commission_rate_basis' -- , gbc2.effective_from
, q.financed_amount AS 'Amount Financed'
, q.on_road_price_net + q.on_road_price_gst AS 'On Road Price'
, q.depreciable_amount
-- These fields used for "Calculation Validations" section below
, q.lessee_rate * 100 AS 'lessee_rate'
, q.commission_rate * 100 AS 'commission_rate'
, q.commission AS 'commission_fee'
-- Order of Rules #1
, IFNULL(pmd.include_commission_flag, 'Yes') AS '1) include_commission_flag'
-- , 'If no, Commission = $0, if yes, next rule' AS rule_1_result
-- Order of Rules #2
, c.agreed_lessee_rate AS '2) Agreed Lessee Rate'
-- , 'If > 0, Commission locked and floated to ensure Lessee Rate matched, else next rule' AS rule_2_result
-- Order of Rules #3
, pmd.novated_commission_rate AS '3) PMD_novated_commission_rate'
-- , 'If > 0 Calc Commission using this rate, else next rule' AS rule_3_result
-- Order of Rules #4
, cd.novated_commission_rate AS '4) CLI_def_novated_commission_rate'
-- , 'If > 0 Calc Commission using this rate, else next rule' AS rule_4_result
-- Order of Rules #5
, gbc1.value AS '5) GBC_novated_commission_rate' -- , gbc1.effective_from
-- , 'Calc Commission using this rate, even if zero' AS rule_5_result
-- Order of Rules #6
, pmd.novated_commission_maximum_amount
, pmd.novated_commission_maximum_rate
, pmd.novated_commission_maximum_basis
-- , 'All above Rules validated against these maximums' AS rule_6_result

FROM qt_quotes q
INNER JOIN fm_clients c ON c.client_id = q.client_id
LEFT JOIN qt_client_defaults cd ON cd.client_id = c.client_id
LEFT JOIN 
	(SELECT * FROM qt_financier_posting_map_defaults 
	WHERE supplier_id = (SELECT supplier_id FROM fm_suppliers WHERE supplier_code = 'MATTFIN')
	AND posting_map_id = (SELECT posting_map_id FROM gl_posting_maps WHERE posting_map_code = 'NOFB')
	AND term <= 24
	ORDER BY term DESC LIMIT 1) pmd ON pmd.supplier_id = q.supplier_id_financier
JOIN (SELECT value, effective_from FROM gb_controls WHERE name = 'novated_commission_rate' ORDER BY name, effective_from DESC LIMIT 1) gbc1
JOIN (SELECT value, effective_from FROM gb_controls WHERE name = 'commission_rate_basis' ORDER BY name, effective_from DESC LIMIT 1) gbc2
WHERE q.quote_id = @quote_id
GROUP BY q.quote_id;