Job Scheduler: Scheduler Job Types

Scheduler Job Types

Overview

Each scheduler job method is stored in the 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 it's detailed page.

Class: 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. 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.

• 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 don't 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 e.g. set the status to 'expired' for all quotes older than 30 days. 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 fmclients, fmsuppliers, 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

System

System Maintenance

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

Glass

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

Red Book

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

Approvals

AutoGuru

Billings

Card Services

Contracts

Finance

Onelink

This class contains methods to interface with the Onelink system.

Payments

MySalPack

Swann

This class contains methods to interface to Swann Insurance's Activ8 system.

Approvals

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 |

MYOB

This class contains methods to interface with MYOB.

JDE

This class contains methods to interface with JDE.

Greentree

This class contains methods to interface with Greentree.

Xero

This class contains methods to interface with Xero.

Scheduler Setup

Send Mail Checkbox

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

Creating Queries

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

Table Details

The scheduler uses 4 tables to run:

gbschedulertypes

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

Field definition:

gbschedulerjobs

gbschedulerjobs contains the web services scheduled for execution. These entries are partially derived from the gbjobtypes table and the source of the gbschedulerqueue table(below). The content includes all jobs ever listed for scheduled execution, but not the schedule itself. Schedule times can be specified as ranges (eg. 1-20), comma separated (eg. 1, 2, 3, …, 20) or a combination thereof (eg. 1, 2, 3-19, 20).
Field definition:

• schedulerjobid — Unique id
• schedulertypeid — Foreign key for gbjobtypes
• schedulertypeid_parent — Job parent
• description — Job description, free text
• paramaters — Paramaters
• 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
• sendmailflag — 'yes' e-mails results to one or more recipients
• attachmentrequiredflag — 'yes' will only send an e-mail 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
• useridsender — gb_users user id of results e-mail 'From' identity
• mail_subect — Subject field for results e-mail, free text
• mail_message — Body of the results e-mail, free text
• mailfooterflag — '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 (e.g. an interface service found and imported a file, item is to be kept for reporting purposes e.g. SLA)
• useridlastedit — gbusers user id of the last person to edit the record
• last_edit — Date and time of the last edit
• reportableexecutiontime — By Default reportableexecutiontime should be 60seconds for all jobs , Newly created and copied job's reportable execution time should be 60seconds If the reportable execution time is increase more than 60seconds, A log should be generated in kibana , If the runtime is moved from outside business hours to within business hours ‘reportableexecutiontime’ 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

Frequently scheduled jobs should have the recyclingflag enabled, a single gbschedulerjobs record can generate over 40,000 gbscheduler_queue entries per month (31 days * 24 hours * 60 minutes).

gbschedulerqueue

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

Field definition:

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

gbschedulerjob_recipients

gbschedulerjobrecipients stores the details of people to be e-mailed when a job successfully completes or fails. There can be many recipients related back to a single gbscheduler_jobs entry.

Field definition: