FBT, Packaging & Admin Reports: Driver Report Setup

FBT, salary packaging, driver, and administration reports

Driver Report Setup

Report Settings

The following global controls configure driver report behaviour:

Insurance — The calculated Insurance Actual amount is normally governed by the field Contracts / Insurance "Billing Type". This flag can override that field to force every contract to calculate Insurance Actual as the total of transactions stored in Contract / Maintenance and Contract / Payments. Default: no
Receipting — If this is set to 'yes', the receipting values will be used to calculate the "Funds Received" amount on Driver Reports; this will match your accounting values. If it is 'no', the value allocated to any invoices within the report period will be shown, regardless of when the payment was made. Default: yes
Months Deferred — If set to yes exclude driver reports from being included if the contract start plus months deferred is greater than the report date. This criteria is only applicable when the reports are run from Administration / Reports in bulk for emailing. Earlier reports can still be selected in the Contracts / FBT tab. Default: no
Contracts / FBT FBT Year Ending — This control sets the default year shown in the "FBT Year Ending" field and the month shown in the output selection field "Report Months" relative to the system date at the time of use. Some businesses want a lag in this year changing at FBT year end while those reports are still being created and adjusted or for the reports so that all the costs are properly shown (i.e. the default report is not for a part-month). The setting is an integer value, e.g. -2, -1, 0, 1, 2, and uses the following logic: Today's date minus 'contracts_fbt_year_ending_default_offset' (months), then derive the FBT Year End date from that date and FBT Year End date from the Contract End date and use the minimum of those two extracting the Year component for the default. Example: If the value is set as 0, a user will get a default of 2010 (if run on 09/04/2009), whereas setting a value of -1 will display 2009 until 01/05/2009, when they will get 2010. Default: 0

Email Template Selection

Go to Contract Types / Details tab and nominate which template you would like to use for Statutory and Operating leases. The template used is determined by which Contracts / FBT "Benefit Type" is chosen.

Each Driver Report template has an entry in gb_templates. The field group_type is set to one of 'fbt_statutory', 'fbt_operating', or 'fbt' for Driver Reports.

Email Template Names

Statutory	Operating	Comments
Driver FBT Statutory	Driver FBT Operating	Template name: 'pdf_driver_fbt'. Linked to contracts where no budgets are disclosed, e.g. Operating.
Novated Driver	Novated Driver Operating	Template name: 'pdf_novated_driver'. Linked to contracts where Fuel and Insurance are offered as budgeted.
Novated Driver Budgeted	Novated Driver Budgeted Operating	Template name: 'pdf_novated_driver_budgeted'. Linked to Novated Leases where budgets are fully disclosed.
Novated Driver Budgeted with Funds	Novated Driver Budgeted Operating with Funds	Template name: 'pdf_novated_driver_budgeted_with_funds'. Linked to Novated Leases where receipting is performed within Catch-e.

Channel-Specific Email

Create a record in the gb_templates table. This creates the email content that is used when Driver Reports are emailed from the Contracts / FBT tab or from the Fleet / Administration / Reports page for Channels.

Standard Email Setup details apply, except for the following specific settings:

The field channel_id should match the selected channel
The field name should be either 'pdf_novated_driver_budgeted_with_funds' or 'pdf_novated_driver_budgeted_operating_with_funds'. Note: This feature for the time being is restricted to the 'with funds' Driver Reports

These reports can be configured to display a channel-specific logo also. Visit Reports Setup for more details.

Administration Driver Reports

This report is one of eight Driver Reports that are designed for emailing to drivers with a PDF attachment. The emails can be sent in bulk from this screen, or individually from the Contracts / FBT tab.

In PDF form, this report is limited to FBT details only and provides details of the estimated annualised mileage for the chosen FBT year.

Report Filters

Left Column

Filter	Type	Description
Delivery	Drop List	Choose from '--All--', '--Blank--', 'Email', or 'Post'. If 'Email' is chosen, the "Print Format" defaults to 'Email', in all other cases the "Print Format" defaults to 'PDF'
As at	Date	Only contracts with a "Start Date" on or prior to this date will be included
Manager	List	Select a manager to limit the report selection to the selected Clients / Contact A/C Manager. An Account Manager can use this field run reports for all the clients they manage
Group	List	Select a group to limit the report selection to the selected "Client Group"
Client	List Box	Leave as '--All--' or select the Client or Clients you want to report on. You can select multiple records by holding down the "Ctrl" key and use your mouse to select the items. To select a range of clients, hold down "Ctrl" and "Shift" keys and use either your mouse or arrow keys to select the range
Contract Type	List Box	Leave as '--All--' or select the Contract Type or Types you want to report on. You can select multiple records by holding down the "Ctrl" key and use your mouse to select the items you want
Report Queue	Checkbox	Unchecked by default. The report will be generated for you on this screen for downloading when complete. Select this checkbox if you want to send the report to the Report Queue screen to be downloaded later. If configured, you will receive an email when the report has been processed and is ready to be downloaded. If the report takes a long time to run and exceeds the allowable time, then the report will be sent to the Report Queue. You will be alerted if this is going to happen

Right Column

Filter	Type	Description
Cost Centre	List	The default selection is '--All--' (returns all records). You can also select '--Blank--' (records have no cost centre), or you can select a specific cost centre. When a "Group" or "Client" is selected, only the cost centres that belong to them are shown

Output Options

Option	Type	Description
Print Format	Drop List	Choose from Email, PDF, Office(xls), or Text(CSV)
Print	Button	The print button creates the report in the chosen format. If the selected "Print Format" is 'Email', the Send Email pop-up will be launched

Error Report

The Error Report identifies the items that caused the upload to fail. The file is exported in .csv format.

Below is a list of possible errors you may see in the validation file. Correct these issues and re-upload the file.

The file may refer to a header record or a row number. The row number is for the record that is affected. Each affected row will have its own validation message.

If there is more than one issue with a row, you will see a validation message for each row issue.

If you are using executeImportTableData, the Error Report will be emailed to you if running the job generates it.

Errors

Row	Error	Solution
header	Three reasons why you might see this error are as follows: 1. The header row is missing from the upload file. Add the required header row. 2. The upload file is not in .csv format. Save into .csv. 3. You have blank columns saved in the file. Method 1: From the cell A1 click on Ctrl+End. Highlight the blank columns. Hover over the columns and click on Delete from the keyboard or the mouse. Re-save the file. Method 2: From the Home tab, click Find & Select button in the Editing section. Select Go To Special. Click the radio button for Blanks and click OK. The blank cells are now highlighted. Find the Cells section of the Home tab and click the Delete arrow to select the Delete options. Select Delete Sheet Columns. Also check and if needed Delete Sheet Rows.	File contains duplicate headers
header	Two reasons why you might see this error are as follows: 1. The header row is missing from the upload file. 2. The upload file is not in *.csv format.	Missing or incorrect header row - should contain 'field'
header	The uploaded file does not match the selected table. Example: A file containing vmrs_code_id is being uploaded, but the table that was selected is vm_tables.	Missing or incorrect header row - should contain 'vmrs_code_id'
header	The table you are importing contains a unique key. All fields that are part of the unique key must be present in the import file. Example: The table contains the fields work_code_id, work_code, description. The field work_code is a unique key. A file for vm_tables is being uploaded, and the file contains only the fields work_code_id, description.	Incorrect header row - Missing additional mandatory column(s) 'work_code'
row number	The cell for the listed row and field has an entry of 'null' or 'NULL'. Change the cell value to be blank. Example: an upload contains a record with the field middle_name populated as 'null'.	(23000-1048-Column 'middle_name' cannot be null)
row number	There are empty trailing columns in the file. Open the file. Click on Delete the extra columns. Re-save the file.	(42S22-1054-Unknown column '' in 'field list')
row number	This error appears when unique key constraint is violated. For example, fm_cards table cannot have duplicate card_number and supplier_id combination. Passing duplicate values will trigger this error.	(23000-1062-Duplicate entry 'supplier_id cell_value-card_number cell_value' for key 'supplier_id')
row_number	The unique id of the record has been populated in the data file but does not exist in the database. This can happen if you are uploading a data file to a different environment where the data is not the same. Check the existing data in the table you are uploading to. Example: if uploading to Staging, download the existing file from Staging. 1. For existing records, you can update the records by importing records with an existing unique id. Example: You are importing a record into fm_suppliers. The field supplier_id is populated and already exists in the database. 2. For new records, leave the unique id field blank. The system will allocate the unique ids on import. Example: You are importing a record into fm_suppliers. The field supplier_id should be blank.	Record does not exist
row_number	There is a field that has a foreign key constraint on it. The file contains a record that does not match any of the records in the key field. Example: You are importing a record into fm_cards. The field supplier_id must already exist as a supplier.	(23000-1452-Cannot add or update a child row: a foreign key constraint fails)
row_number	This means the data has been chopped at maximum database length or the data does not match the database field requirement, e.g. like adding in a status that doesn't exist, or using a value of say 20 characters where a maximum of 15 is allowed.	Data truncated
row_number	You are trying to import a record with an incorrect date or the field is blank or the field has a blank space. Example: The file contains a record with a populate date of 31/31/2000.	Incorrect date value: '31/31/2000'
row_number	The file contains a column with a header name that does not match any fields contained in the table. Check the columns for incorrect spelling. Example: an upload contains a column called midlename.	Unknown column 'midle_name'
row_number	The system is expecting numeric values to be populated in this field. The field contains a blank space. The field contains characters that are not recognised by the system. The field contains a blank space in a column that requires a number. Examples: Incorrect integer value: ' ' (space), Incorrect integer value: '^&(&^*', Incorrect decimal value: 'abc'.	Incorrect integer value or Incorrect decimal value
last row number	1. The file contains two records that contain the value for the primary key field, or unique key field. 2. There is a record in the file that will create or update a record in the table to contain a value that already exists in another table record. Example: an upload contains two records with the same client_id value. Solution: When importing existing records into the table, the two fields client_default_id and client_id must both be in the upload file and there can only be one row in the file that contains this combination. Example: The table contains a primary key (card_id) and a unique key (supplier_id, card_number). Search the file and then the existing table records for the reported value and update the file as required. An upload file contains two records with the same supplier_id and card_number values. Solution: When importing existing records into the table, the three fields card_id, supplier_id, and card_number must all be in the upload file and there can only be one row in the file that contains this combination.	(23000-1062-Duplicate entry '100030' for key 'client_id') or (23000-1062-Duplicate entry '100459-12326389' for key 'supplier_id')

Warning: The system will only validate and report the first error of this kind that is found, so re-check your file carefully to remove any other errors like this or you will get a validation report for each one as it is found!

Additions Report

The additions report includes contracts which commenced in the selected month. The report defaults to the current month.

This report can be emailed to the clients in a scheduler job. More details are available on the System Reports page.

Report Filters

Filter	Type	Description
As at	Date	Only contracts commencing on or prior to this date will be included and contracts suspended on or prior to this date will be excluded
Manager	List	Select a manager to limit the report selection to the selected Clients / Contact A/C Manager. An Account Manager can use this field run reports for all the clients they manage
Group	List	Select a group to limit the report selection to the selected "Client Group"
Client	List Box	Leave as '--All--' or select the Client or Clients you want to report on. You can select multiple records by holding down the "Ctrl" key and use your mouse to select the items. To select a range of clients, hold down "Ctrl" and "Shift" keys and use either your mouse or arrow keys to select the range
Contract Type	List Box	Leave as '--All--' or select the Contract Type or Types you want to report on. You can select multiple records by holding down the "Ctrl" key and use your mouse to select the items you want
Report Queue	Checkbox	Unchecked by default. The report will be generated for you on this screen for downloading when complete. Select this checkbox if you want to send the report to the Report Queue screen to be downloaded later. If configured, you will receive an email when the report has been processed and is ready to be downloaded. If the report takes a long time to run and exceeds the allowable time, then the report will be sent to the Report Queue. You will be alerted if this is going to happen
Cost Centre	List	The default selection is '--All--' (returns all records). You can also select '--Blank--' (records have no cost centre), or you can select a specific cost centre. When a "Group" or "Client" is selected, only the cost centres that belong to them are shown

Report Fields

The following fields are included in the Additions Report:

Group Name
Client Code
Cost Centre
State
Registration Number
Driver Name
Location
Vehicle Description
FBT Value $
Contract Type
Contract Kilometres
Estimated Odometer Reading
Start Date
End Date
Contract Months
Months Expired
Rental Excluding GST $
GST $
Gross Rental $

The Text(CSV) output of this report includes some additional fields.