14.1 sp560_travel_status_history

This table stores a subset of GPS information received from SERVICEGPS which it has communicated to ServiceOptimizer through a set of 14.1 Update Travel Status SOAP API calls.

Should any call to the 14.1 Update Travel Status SOAP API be unsuccessful, or the GPS system has filtered GPS events (which will be the case for many Moving events), then no record is stored in this table.

HouseKeeping

This table is tidied by sp_dbtidy's Global group.

Table Definition

Field	Type	Nullable
`sequence_no`	`number(9)`	No
`timestamp`	`date`	No
`employee_no`	`number(9)`	No
`event_time`	`date`	No
`travel_status`	`number(1)`	No
`location_type`	`varchar2(5)`	Yes
`latitude`	`float`	Yes
`longitude`	`float`	Yes
`address1`	`varchar2(50)`	Yes
`address2`	`varchar2(50)`	Yes
`address3`	`varchar2(50)`	Yes
`address4`	`varchar2(50)`	Yes
`postcode`	`varchar2(30)`	Yes
`country`	`varchar2(2)`	Yes
`dispatch_status`	`number(1)`	Yes

Keys

primary key (sequence_no)
foreign key (employee_no) references 14.1 sp210_employees(employee_no)
foreign key (location_type) references sp561_geofence_location_types(location_type)
index (timestamp, employee_no)

Column Descriptions

Field	Description
`sequence_no`	Automatically generated number giving each row in the table a unique reference.
`timestamp`	Time when this entry was made into this table. It is stored in the default TZ.
`employee_no`	The employee for which this status was received.
`event_time`	The time at which the GPS recorded the event. This is the time that was passed to ServiceOptimizer in the call to the 14.1 Update Travel Status SOAP API. No timezone conversion is applied to this time.
`travel_status`	This is the travel status that was sent. This can have the following values: 2: Stopped 3 :Stationary 4: Moving 5: Unknown See also `14.1 sp210_employees.travel_status`.
`location_type`	The type of location for a stopped event. Only relevant if `travel_status` is 2.
`latitude,` `longitude`	Latitude and Longitude. If this information is unavailable the values are NULL. Coordinate system is WGS84.
`address1,` `address2,` `address3,` `address4`	Four lines of address information. This information is not always available, so can be `NULL`.
`postcode`	This is the postcode. Note that this is not the same as `/wiki/spaces/SCH141/pages/27100155.postcode`. This information is not always available so can be `NULL`.
`country`	A two character representation of the country (ISO 3166-1 alpha-2). This information is not always available so can be `NULL`.
`dispatch_status`	The dispatch status associated with the operative when the call to the 14.1 Update Travel Status SOAP API is made. This can have the following values: 0: Tentative. 1: Fixed. 2: Engineer earmarked. 3: Engineer contacted. 4: Travelling. 5: Engineer logged on. 6: Engineer logged off. 7: Job cleared. This information is not always available so can be `NULL`.