Scheduler Job Types

Overview

Each scheduler job method is stored in the gb_scheduler_types table and is also classified by module and class. Each method is listed below, grouped by module and class. Find out more about a particular method by going to its detailed page.

Global: Queries

This class contains methods that allow you to run queries, or multiple queries grouped into scripts. They are useful if you want to send reports in a simple format via email, or to run regular update queries over your database.

Note: Where the Status is set to 'Active', and the 'Send Mail' check box is checked ON, a mail recipient must be added for the Job Item to be saved or created. In addition, where the status is 'Inactive', and the 'Send Mail' check box is checked ON, the 'Run Now' function (where available) will fail.

Method	Synopsis
executeQuery	This job type allows you to run a report in the form of an SQL query, and send the output as an email attachment. It is useful for sending regular reports both internally and externally. Using the Attachment Required checkbox, exception reports can be generated where an email is sent only if the report finds anything. See User Defined Queries for instructions on writing queries for the Job Scheduler
executeScript	Scripts are groups of queries that modify data but do not return anything. You can include as many SQL statements you like within a script. A script can include any number of UPDATE / INSERT / SET / LOCK TABLES / UNLOCK TABLES statements. There are many possible uses for these, for example: set the status to 'expired' for all quotes older than 30 days. See User Defined Queries for instructions on writing scripts for the Job Scheduler
mailQuery	Allows you to schedule bulk emails with report attachments to multiple clients or suppliers etc
interfaceQuery	Allows you to extract data into a file with a User Defined Query and save it in your DMZ area. Once in your DMZ area you can access the file using utilities such as FTP. This can be used to create data interfaces from Catch-e to your external systems
interfaceQueryIncremental	Allows you to generate an interface file in the DMZ area containing only records from either fm_clients, fm_suppliers, or fm_contracts, that have changed since the last time this job was run. A User Defined Query is used to define the content of the output file
eventQuery	Allows you to schedule the creation of Contract and Driver Events, that relate to the Event Types currently set up against both modules
executeImportTableData	Allows you to import and update the tables using scheduler

Global: System

This class contains system level methods.

Method	Synopsis
dmzMoveFile	Move a file from one DMZ folder to another, or simply rename a file
dmzUploadFile	Transfer a file from your DMZ folder to an external site via SFTP
dmzUploadSpool	Transfer all of the files stored in one of your DMZ folders to an external site via SFTP. The uploaded files are also archived

Global: System Maintenance

System maintenance jobs are normally scheduled on your behalf by Catch-e staff.

Method	Synopsis
checkSPFRecordForGbMailSenderDomains	This job type checks and validates the SPF record of any domains stored in the gb_mail_sender_domains table
deidentifyDriverData	Allows you to permanently delete or de-identify personal data (including audit records) from the tables using scheduler
rebuildDriverAllocations	This job type allows you to rebuild the fm_contract_driver_allocations table. You might want to run this after importing 'DRIVER' type contract events if you do not want to wait for the scheduled daily rebuild
rebuildDivisionAllocations	This job type allows you to rebuild the fm_contract_division_allocations table. You might want to run this after importing 'DIV' type contract events if you do not want to wait for the scheduled daily rebuild
rebuildCostCentreAllocations	This job type allows you to rebuild the fm_contract_cost_centre_allocations table. You might want to run this after importing 'CC' type contract events if you do not want to wait for the scheduled daily rebuild
updateMailDeliveryStatusFromPostfix	This job type moves all delivered or undelivered emails information from Postfix database to its respective client database

Fleet Management: Glass

This class contains methods to interface with Glass vehicle data via their SFTP site.

Method	Synopsis
importVehicleData	Import Glass vehicle data from Glass SFTP server

Fleet Management: Redbook

This class contains methods to interface with Redbook via their FTP site.

Method	Synopsis
importVehicleData	Import vehicle data from the Redbook FTP server
importVehiclePhotos	Import photos from the Redbook FTP server

Fleet Management: Approvals

Method	Synopsis
autoPostClaims	This job posts claims if sufficient funds are available

Fleet Management: AutoGuru

Method	Synopsis
importApprovals	This job imports a data file from AutoGuru and posts approvals

Fleet Management: Billings

Method	Type	Description
Combined	fm / billings / combined	This job runs scheduled Combined billings
Periodic	fm / billings / periodic	This job runs scheduled Periodic billings
Recharge	fm / billings / recharges	This job runs scheduled Recharge billings
Recharge Fuel	fm / billings / recharges_fuel	This job runs scheduled Recharge Fuel billings

Fleet Management: Card Services

Method	Synopsis
importTransactions	This job imports and posts card service files (for example: fuel transactions) by automating the steps defined in Card Transactions

Fleet Management: Contracts

Method	Synopsis
importEvents	This job imports Road Side Assistance call outs from Assist Australia and creates Contract Events against each vehicle

Fleet Management: Finance

Method	Synopsis
UpdateClientFinancierCreditUsed	This scheduler method updates the Clients / Financiers "Used" amount
export255FinanceData	This job type creates an export file as detailed in the 255Finance interface

Fleet Management: Onelink

This class contains methods to interface with the Onelink system.

Method	Synopsis
exportMaintHistory	Incremental. Each maintenance line id exported is stored in fm_maintenance_1link table. See Technical Information 1Link Interface for full selection and exception details
exportVehicle	Complete export. See Technical Information 1Link Interface for full selection and exception details
importInvoices	Complete export. See Technical Information 1Link Interface for full selection and exception details

Fleet Management: Payments

Method	Synopsis
Periodic	This job runs scheduled Periodic payments

Fleet Management: MySalPack

Method	Synopsis
importPackagingData	This job imports MySalPack employee information and packaging transactions

Salary Packaging: Approvals

Method	Synopsis
autoBuildClaims	This job will create and post cost records for salary-packaged items
autoPostClaims	Run this job to post claims when the listed criteria are met: Contract Type Group = 'Salary Packaged', Contract is linked to an Item, Funds Check calculation shows sufficient funds available

Salary Packaging: Payments

Method	Synopsis
autoPostPayments	This job will automatically post the next due Payment when the listed criteria are met: Contract Type Group = 'Salary Packaged', Contract is linked to an Item, Contracts / Payments record "Due Date" meets the Dated To Method job criteria

Accounting: MYOB

This class contains methods to interface with MYOB.

Method	Synopsis
createExportBatch	Creates an MYOB export batch for the given Ledger Type (Sales/Purchases/Journals), equivalent to running the menu option Accounts / Export / MYOB / General Ledger / New - Create. The export batch file data is generated, and cached in the gb_exports table to greatly improve performance whenever the export file is generated. The export batch is saved with a status of 'pending'
createExportFile	Creates and emails an MYOB export file for the given Ledger Type (Sales/Purchases/Journals), for the most recent export batch that is at 'pending' status. It then sets the status to 'exported_ok'

Accounting: JDE

This class contains methods to interface with JDE.

Method	Synopsis
createExportBatch	Creates a JDE export batch for the given Ledger Type (Sales/Purchases/Journals), equivalent to running the menu option Accounts / Export / JDE / General Ledger / New - Create. The export batch file data is generated, and cached in the gb_exports table to greatly improve performance whenever the export file is generated. The export batch is saved with a status of 'pending'
createExportFile	Creates and emails a JDE export file for the given Ledger Type (Sales/Purchases/Journals), for the most recent export batch that is at 'pending' status. It then sets the status to 'exported_ok'

Accounting: Greentree

This class contains methods to interface with Greentree.

Method	Synopsis
createExportBatch	Creates a Greentree export batch for the given Ledger Type (Sales/Purchases), equivalent to running the menu option Accounts / Export / Greentree / General Ledger / New - Create. The export batch file data is generated, and cached in the gb_exports table to greatly improve performance whenever the export file is generated. The export batch is saved with a status of 'pending'
createExportFile	Creates and emails a Greentree export file for the given Ledger Type (Sales/Purchases), for the most recent export batch that is at 'pending' status. It then sets the status to 'exported_ok'

Accounting: Xero

This class contains methods to interface with Xero.

Method	Synopsis
createExportBatch	Creates a Xero export batch for the given Ledger Type (Sales/Purchases/Journals), equivalent to running the menu option Accounts / Export / Xero / General Ledger / New - Create. The export batch file data is generated, and cached in the gb_exports table to greatly improve performance whenever the export file is generated. The export batch is saved with a status of 'pending'
createExportFile	Creates and emails a Xero export file for the given Ledger Type (Sales/Purchases/Journals), for the most recent export batch that is at 'pending' status. It then sets the status to 'exported_ok'

Scheduler Setup

Send Mail Checkbox

Use: Set a preferred default for the job type. 'yes' if you expect an email to sent, or 'no' if an email is not useful
Behaviour: If = 'no', this checkbox will be disabled and emails cannot be sent, regardless of how the flag is set

Creating Queries

Refer to the following pages for guidance on creating scheduler queries:

User Defined Queries
Derived Fields
Encrypted Fields
Office(xls) Format
Office(xlsx) Format

Table Details

The scheduler uses 4 tables to run jobs.

gb_scheduler_types

gb_scheduler_types is maintained by Catch-e support and contains the job definitions for web services available to be scheduled. The content is client specific.

Field	Description
scheduler_type_id	Unique id
bespoke_flag	'yes' indicates a client specific service
module	Module to which this job is assigned, for example fm or gb
class	Class where the method resides
method	Web service to be executed
description	Default job description, free text field
send_mail_flag	'yes' emails results to one or more recipients
attachment_required_flag	'yes' only sends results email if something is generated
mail_subject	Subject field for results email, free text
mail_message	Body of the results email, free text
mail_footer_flag	'yes' appends the standard company mail footer
super_flag	'yes' implies a system job, only visible by super users

gb_scheduler_jobs

gb_scheduler_jobs contains the web services scheduled for execution. These entries are partially derived from the gb_scheduler_types table and are the source of the gb_scheduler_queue table (below). The content includes all jobs ever listed for scheduled execution, but not the schedule itself.

Note: Schedule times can be specified as ranges (for example: 1-20), comma separated (for example: 1, 2, 3, ..., 20) or a combination thereof (for example: 1, 2, 3-19, 20).

Field	Description
scheduler_job_id	Unique id
scheduler_type_id	Foreign key for gb_scheduler_types
scheduler_type_id_parent	Job parent
description	Job description, free text
parameters	Parameters
minutes	0-59, minute(s) in the hour to schedule the job
hours	0-23, hour(s) in the day to schedule the job
days	1-31, day(s) of the month to schedule the job. Use "EOM" if you want to run the job on last day of the month
months	1-12, month(s) of the year to schedule the job
weekdays	0-7, day(s) of the week to schedule the job, 0 = Sunday
last_run	Date and time of last execution
created	Creation date and time
send_mail_flag	'yes' emails results to one or more recipients
attachment_required_flag	'yes' will only send an email if results are generated
record_type	Optional type of Catch-e entity to attach results to
record_id	Optional unique id of Catch-e entity to attach results to
user_id_sender	gb_users user id of results email 'From' identity
mail_subject	Subject field for results email, free text
mail_message	Body of the results email, free text
mail_footer_flag	'yes' appends the standard company mail footer
recycling_flag	'yes' will recycle the last 'completed' item in the scheduler queue unless: (a) an error occurs (status would be 'error'); (b) an email is linked to the queue item; (c) the service asked the scheduler not to recycle the queue item in its response (for example: an interface service found and imported a file, item is to be kept for reporting purposes for SLA). Note: Frequently scheduled jobs should have the recycling_flag enabled. A single gb_scheduler_jobs record can generate over 40,000 gb_scheduler_queue entries per month (31 days × 24 hours × 60 minutes)
user_id_last_edit	gb_users user id of the last person to edit the record
last_edit	Date and time of the last edit
reportable_execution_time	By default reportable_execution_time should be 60 seconds for all jobs. Newly created and copied job's reportable execution time should be 60 seconds. If the reportable execution time is increased more than 60 seconds, a log should be generated in Kibana. If the runtime is moved from outside business hours to within business hours 'reportable_execution_time' will be reset to the default value or kept at the existing value whichever is lower
status_flag	'active' causes the job to be immediately scheduled

gb_scheduler_queue

gb_scheduler_queue is the execution schedule for jobs from gb_scheduler_jobs. The content of this table is maintained by the job scheduler. The job scheduler checks entries in gb_scheduler_jobs for those requiring addition to gb_scheduler_queue before executing required jobs listed in gb_scheduler_queue.

Note: Within a minute of an active gb_scheduler_job record being created, the job scheduler will have created a gb_scheduler_queue entry for this job. Setting the gb_scheduler_job record to inactive will delete pending gb_scheduler_queue entries.

Field	Description
scheduler_queue_id	Unique id
scheduler_job_id	Foreign key for gb_scheduler_jobs
scheduled_start	Date and time to be executed
actual_start	Date and time of execution
actual_end	Date and time of completion
message	Error or completion text returned by the web service
mail_batch_no	Mail batch that results emails were included in
recycle_flag	If 'no' this item cannot be recycled
status_flag	Indicative of the state of this queue entry

gb_scheduler_job_recipients

gb_scheduler_job_recipients stores the details of people to be emailed when a job successfully completes or fails. There can be many recipients related back to a single gb_scheduler_jobs entry.

Field	Description
scheduler_job_id	Foreign key for gb_scheduler_jobs
recipient_no	Sequenced counter of recipients for the given scheduled job
name	Free text, name of the email recipient
address	Email address of the recipient
type	To, Cc or Bcc
receipt_flag	'yes' to return a receipt from job email
receive_errors_flag	'yes' to receive job errors, 'no' to receive job results
status_flag	Indicative of the status of this recipient