Custom Queries & Report Queue: Derived Fields odometer

User-defined queries, stored procedures, and report queue

Derived Fields odometer

This library is used to calculate odometer readings based on existing validated readings using rules defined in Estimated Odometer Calculation.

These readings are shown on screens and used in various reports, such as the Service Due Report.

This library can also be used with the following scheduler job methods: executeQuery, mailQuery, and eventQuery.

This library can also be used with the following APIs: printReportQuery and triggerTableImportProcedure.

Query Header

The following lines must be included in your query header:

## odometer ## library_type standard ## library_path /lib/PDF ## library_name odometer

Input Fields

Field	Source	Mandatory
contract_id	fm_contracts > contract_id	Yes
date_filter	Any valid date (dd/mm/yyyy) or from report logic	No. If not passed, the report will use the current date. Add the field into your query or report query to use a specific date or date logic. If you are running the Print a report query API, you can make this field a JSON variable as 'dd/mm/yyyy' or 'yyyy-mm-dd'.
odometer_cutoff_date	Any valid date (dd/mm/yyyy or yyyy-mm-dd)	No. If not passed, the report will use the current date. Add the field into your query or report query to use a specific date or date logic. If you are running the Print a report query API, you can make this field a JSON variable as 'dd/mm/yyyy' or 'yyyy-mm-dd'.

Output Fields

Field	Description
odo_est_current	Estimate odometer at the required date (date_filter)
odo_est_at_term	Estimate odometer at end of contract (date_filter). On the Fleet / Administration / Contracts Header screen, this field is labelled 'Projected'.
odo_diff_at_term	Difference between the estimated odometer at the end of the lease and the contract kilometres plus start kilometres
odo_diff_at_term_rate	Estimated odometer percentage difference at the end of the lease, or ROUND(odo_diff_at_term / contract_km, 2)
odo_variance_at_term	Estimated odometer variance at contract end, or odo_est_current * -1
odo_variance_at_term_rate	Estimated odometer percentage difference variance at term end, or odo_diff_at_term_rate * -1
last_valid_odo_date	Odometer date for the most recent valid odometer reading
last_valid_odo_km	Odometer for the most recent valid odometer reading

Derived Fields payroll_advice

This library is used to calculate an annual fbt_payable amount.

Use this for new 'Statutory Method' contracts to get an annualised FBT Payable amount.

It can be used in Scheduler Queries and Screen Reports.

Note: This library is not related to the Payroll Advice form. Those calculations are not the same.

Note: The Print a report query API does not support derived field libraries.

Input Fields

Field	Source	Rules
contract_id	fm_contracts > contract_id	Mandatory

Output Fields

Field

Description

fbt_payable

When Contracts / FBT "ECM Flag" is checked, then {fbt_payable} = 0. When Contracts / FBT "ECM Flag" is NOT checked, then {fbt_payable} calculated as follows: If Clients / Quote Defaults "Gross Up Rate" = 'fbttype1_grossup_factor', then ROUND(Contracts / FBT "FBT Value" 0.20 fbttype1_grossup_factor 0.47, 2). Else ROUND(Contracts / FBT "FBT Value" 0.20 fbttype2_grossup_factor 0.47, 2). E.g. ROUND(39,363.80 0.20 2.0802 0.47, 2) = 7,697.15. E.g. ROUND(45,000.00 0.20 1.8868 0.47, 2) = 7,981.16.

Sample Query

## library_type standard ## library_path /fleet/administration/contracts/templates ## library_name payroll_advice SELECT t1.contract_id, '->fbt_payable' AS fbt_payable FROM fm_contracts AS t1;

Derived Fields performance_nofb

Derived fields from the /lib/PDF/performance_nofb library

This library is used to calculate FBT operating information.

Input Fields

Field	Source	Rules
contract_id	fm_contracts > contract_id	Mandatory
date_filter	Any valid date (dd/mm/yyyy)	Optional, if left out will default to the current date

Output Fields

Field	Description
fbt_year_start	FBT year start date
budget_fin_net_wlf	Budget Finance net for whole lease fraction
budget_maint_net_wlf	Budget Maintenance net for whole lease fraction
budget_tyres_net_wlf	Budget Tyres net for whole lease fraction
budget_reg_net_wlf	Budget Registration net for whole lease fraction
budget_ins_net_wlf	Budget Insurance net for whole lease fraction
budget_fees_net_wlf	Budget Fees net for whole lease fraction
budget_fuel_net_wlf	Budget Fuel net for whole lease fraction
budget_other_net_wlf	Budget Other net for whole lease fraction
total_budgets_net_wlf	Total Budgets net for whole lease fraction
total_budgets_gst_wlf	Total Budgets GST for whole lease fraction
total_budgets_gross_wlf	Total Budgets gross for whole lease fraction
budget_fin_net_ltd	Budget Finance net for lease to date
budget_maint_net_ltd	Budget Maintenance net for lease to date
budget_tyres_net_ltd	Budget Tyres net for lease to date
budget_reg_net_ltd	Budget Registration net for lease to date
budget_ins_net_ltd	Budget Insurance net for lease to date
budget_fees_net_ltd	Budget Fees net for lease to date
budget_fuel_net_ltd	Budget Fuel net for lease to date
budget_other_net_ltd	Budget Other net for lease to date
total_budgets_net_ltd	Total Budgets net for lease to date
total_budgets_gst_ltd	Total Budgets GST for lease to date
total_budgets_gross_ltd	Total Budgets gross for lease to date
actual_fin_net_ltd	Actual Finance net for lease to date
actual_fin_gst_ltd	Actual Finance GST for lease to date
actual_maint_net_ltd	Actual Maintenance net for lease to date
actual_tyres_net_ltd	Actual Tyres net for lease to date
actual_reg_net_ltd	Actual Registration net for lease to date
actual_fees_net_ltd	Actual Fees net for lease to date
actual_fuel_net_ltd	Actual Fuel net for lease to date
actual_other_net_ltd	Actual Other net for lease to date
total_actuals_net_ltd	Total Actuals net for lease to date
total_actuals_gst_ltd	Total Actuals GST for lease to date
total_actuals_gross_ltd	Total Actuals gross for lease to date
variance_fin_net_ltd	Variance Finance net for lease to date
variance_maint_net_ltd	Variance Maintenance net for lease to date
variance_tyres_net_ltd	Variance Tyres net for lease to date
variance_reg_net_ltd	Variance Registration net for lease to date
variance_fees_net_ltd	Variance Fees net for lease to date
variance_fuel_net_ltd	Variance Fuel net for lease to date
variance_other_net_ltd	Variance Other net for lease to date
total_variance_net_ltd	Total Variance net for lease to date
total_variance_gst_ltd	Total Variance GST for lease to date
total_variance_gross_ltd	Total Variance gross for lease to date
total_allocated_gross_ltd	Total allocated gross for lease to date
total_on_account_gross_ltd	Total on account gross for lease to date
total_receipted_amount_gross_ltd	Total receipted amount gross for lease to date
total_funds_variance_gross_ltd	Total funds variance gross for lease to date
current_balance	Current balance

Derived Fields rentals

Derived fields from the /lib/PDF/rentals library

This library is used to calculate rental amounts and associated finance information.

Input Fields

Field	Source	Rules
contract_id	fm_contracts > contract_id	Mandatory
date_filter	Any valid date (dd/mm/yyyy)	Mandatory

Output Fields

Field	Description
rental_net	Total Rental (excluding GST)
rental_gst	Total Rental GST
rental_gross	Total Rental (including GST)
finance_net	Includes Posting Classes 'FIN' and 'CSD' if linked to Posting Map (Contract Type)
finance_gst	Includes Posting Classes 'FIN' and 'CSD' GST if linked to Posting Map (Contract Type)
finance_gross	Includes Posting Classes 'FIN' and 'CSD' (including GST) if linked to Posting Map (Contract Type)
insurance_net	Includes Posting Class 'INS' (excluding GST)
insurance_gst	Includes Posting Class 'INS' GST
insurance_gross	Includes Posting Class 'INS' (including GST)

Derived Fields service

Derived fields from the /lib/PDF/service library

This library is used to calculate service due information based on existing validated readings from the system.

See also the Estimated Odometer Calculation page for the logic used when an odometer reading needs to be estimated for this library.

These readings are used in the Service Due Report.

This library can be used with the triggerTableImportProcedure API.

This library can be used with the Print a report query API.

Input Fields

Field	Source	Rules
contract_id	fm_contracts > contract_id	Mandatory
date_filter	Any valid date (yyyy-mm-dd)	Mandatory

Output Fields

Field	Description
odo_est_current	Estimate odometer at the required date
last_service_date	Date of last service
last_service_odometer	Odometer of last service
service_due_odometer	Service due at this odometer
service_due_date	Service due at this date
service_due_in_days	Service due in this number of days
service_due_in_km	Service due in this number of kilometres
latest_odometer	Odometer of the most recent valid odometer reading
latest_date	Date of the most recent valid odometer reading
as_at_span	Difference in days between the required date and today (1 if they are the same)
service_overdue	Flag to indicate a service is due, either 'yes' or 'no'

Derived Fields vehicle_photos

This library is used to obtain a single photo code per variant.

This library can also be used with the following scheduler job methods: executeQuery, mailQuery, and eventQuery.

This library can also be used with the following APIs: printReportQuery.

Query Header

The following lines must be included in your query header:

## vehicle_photos ## library_type standard ## library_path /lib/PDF ## library_name vehicle_photos

Input Fields

Field	Source	Mandatory
variant_id	qt_variants > variant_id	Yes

Output Fields

photo_code — Table may contain multiple photo_code values for a given variant_id (e.g. front, rear, etc.). 'Front' is treated as the default followed by Side, Interior, and Rear respectively. If no photo_codes exist, the value 'no_photo' will be returned.