sp210_employees

Each record in this table contains either personnel details of an engineer/operative, or details of a crew.

Table Definition

Field	Type	Default
employee_no	number(9)
employee_ref	varchar2(25)
type	number(1)
name	varchar2(50)
surname	varchar2(50)
job_title	varchar2(24)
efficiency	number(3)	100
address1	varchar2(50)
address2	varchar2(50)
address3	varchar2(50)
address4	varchar2(50)
postcode	varchar2(30)
region_no	number(9)
region_ref	varchar2(26)
phone1	varchar2(40)
phone2	varchar2(40)
pager	varchar2(20)
call_sign	varchar2(6)
hand_held1	varchar2(10)
hand_held2	varchar2(10)
current_hand_held	number(1)
schedulable	number(1)
max_overtime	number(4)
printer	varchar2(10)
inform_lead_time	number(4)
work_region_no	number(9)
work_postcode	varchar2(30)
tz_type	number(1)
tz_no	number(2)
earmarked_jobs_max	number(2)
default_start_loc	number(1)	0
default_start_loc_postcode	varchar2(30)
default_end_loc	number(1)	0
default_end_loc_postcode	varchar2(30)
default_LK_centre_loc	number(1)	0
default_LK_centre_loc_postcode	varchar2(30)
default_start_loc_region_no	number(9)
default_end_loc_region_no	number(9)
default_LK_centre_loc_region_no	number(9)
TOTB	number(4)
TOTA	number(4)
vehicle_capacity	number(9)
frozen_lead_days	number(2)
frozen_day_trigger_time	varchar2(4)
show_operative_status	number(1)
travel_status	number(1)
travel_status_time	date
travel_api_time	date
last_moving_time	date
current_loc_type	varchar2(5)
geo_fence_name	varchar2(25)
last_stopped_overnight	date
last_stopped_customer	date
last_stopped_ok	date
last_moving_time	date

Keys

primary key (employee_no)
unique (employee_ref)
foreign key (type) references sp291_employee_types (type)
foreign key (region_no, region_ref) references sp057_regions (region_no, region_ref)
foreign key (current_hand_held) references sp292_current_hht (current_hand_held)
foreign key (work_region_no) references sp057_regions (region_no)
foreign key (tz_no) references sp056_time_zones (tz_no)
foreign key (default_start_loc) references sp271_start_end_locations (location)
foreign key (default_end_loc) references sp271_start_end_locations (location)
foreign key (default_start_loc_region_no) references sp057_regions (region_no)
foreign key (default_end_loc_region_no) references sp057_regions (region_no)
foreign key (default_LK_centre_loc) references sp271_start_end_locations(location)
foreign key (default_LK_centre_region_no) references sp057_regions (region_no)

Primary Key Referenced From

Column Descriptions

Field	Description
employee_no	ServiceOptimizer-generated unique identifier.
employee_ref	End users' unique identifier.
type	The employee type: 0 - person 1 - crew
name	The employee's name as it is to be displayed in the user interface.
surname	The employee's surname (required only to allow sorting).
job_title	The employee's job title.
efficiency	The employee's efficiency rating, as a percentage.
address1	The employee's home address ...
addess2
addess3
addess4
postcode	... and postcode.
region_no	Identifier of the region in which the employee's home address is located.
region_ref	Region reference for the employee's home address.
phone1	Telephone number.
phone2	Telephone number.
pager	Pager number.
call_sign	Unused.
hand_held1	Address of the employee's hand held terminal.
hand_held2	Address of the employee's 2nd hand held terminal.
current_hand_held	Indicator showing which hand held terminal is currently in use (1 or 2).
schedulable	A flag indicating whether or not the employee can be used by the schedulers: 0 - The employee is not schedulable 1 - The employee is schedulable
max_overtime	The maximum number of minutes authorised overtime that the employee can work in any one shift period.
printer	Employee's print location.
inform_lead_time	The inform lead time, in minutes, for this employee. If the value is null, the lead time for the employee's responsibility unit (recorded in sp213_scheduler_parameters), or if this is NULL, the system lead time (recorded in sp083_system_parameters) is used.
work_region_no	A standard start or finish region other than home or depot.
work_postcode	A standard start or finish postcode other than home or depot - used rather than work_region_no.
earmarked_jobs_max	The maximum number of jobs to be auto-earmarked at any given time for this operative. Overrides parameter setting in sp213/sp083 for disp_earmarked_jobs_max for this operative. The count only applies to jobs auto-eamarked. Those earmarked by batch dispatch or user-earmarked do not count towards earmarked_jobs_max, and once a job is no longer earmarked, e.g. it has passed on into the contacted state, it no longer counts either.
tz_type	Flag determining how the "local" time zone for an employee is determined: 0 - Use time zone specified in tz_no field. 1 - Use time zone of employee's home address If this field is null, sp083 'tz_Default' is used.
tz_no	An employee may, through a working day, move between different time zones. The employee's schedule for the day could be represented in terms of the time zone of the location in which s/he is in at any given time, but it is more sensible to assume that the employee has a watch and is working to his/her own "local" time zone. This field is only used if the tz_type field is set to 0. If tz_type is 0 and this field is null, the server time zone is used.
default_start_loc	The location of the operative at the start of his shift: 0 - Home 1 - Work 2 - Depot 3 - Specified
default_start_loc_postcode, default_start_loc_region_no	The location of the operative (postcode and region) at the start of his shift when default_start_loc (above) is 'Specified'.
default_end_loc	The location of the operative at the end of his shift: 0 - Home 1 - Work 2 - Depot 3 - Specified
default_end_loc_postcode, default_end_loc_region_no	The location (postcode and region) of the operative at the end of his shift when default_end_loc (above) is 'Specified'.
default_LK_centre_loc	Identifies the location from which range local knowledge is calculated: 0 - Home 1 - Work 2 - Depot 3 - Specified
default_LK_centre_postcode, default_LK_centre_region_no	Location (postcode and region) from which range local knowledge is calculated when default_LK_centre_loc is 'Specified'.
TOTB	Maximum before shift travel (in minutes). If NULL, the value for the FRU used instead.
TOTA	Maximum after shift travel (in minutes). If NULL, the value for the FRU used instead.
vehicle_capacity	The total amount of space available in the employee's vehicle for Pickups and Deliveries; in user-defined units.
frozen_lead_days	The number of days, starting from 'Today', that should be frozen. If this value is 0 then today does not get frozen until now is later then the frozen_day_trigger_time. If its value is 1 then today will be frozen, and tomorrow will be frozen once it is later than frozen_day_trigger_time – and so on.Takes account of non-working days (ref: sp013_non_working_days).
frozen_day_trigger_time	This is a SP_Time that represents the time at which the next day should be frozen. The time is determined by appending this time to today's date, and is interpreted to be in employee local time.
show_operative_status	When this value is 0 neither the operative status or travel status for this operative is displayed. When it is 1 just the operative status is displayed. When it is 2 just the operative travel status is displayed.When it is 3 then both the operative status and the operative travel status are displayed.
travel_status	The current travel status as set by an EmpChangeTravelStatus API call. Typically this is the status reported to ServiceOptimizer that represents the vehicle's current state, i.e.: Stopped, Stationary, Moving and Unknown.If travel status is not enabled for this operative or this operative has not yet had a travel status supplied on the EmpChangeTravelStatus API then this will be 0.
travel_status_time	This is the date and time at which the travel_status was last changed. This is the current time and not the timestamp supplied on the EmpChangeTravelStatus API call. This is stored in FRU timezone.
travel_api_time	This is the date and time at which the travel_status was last changed according to the timestamp parameter of the EmpChangeTravelStatus API call. This is stored in FRU timezone.
last_moving_time	This is the time that the travel status was last known to be moving, significant when the current travel status is not moving. This will be stored in FRU timezone. This has no meaning outside ServiceOptimizer.
current_loc_type	If the current travel status is Stopped then this value further qualifies the location type. See sp561_geofence_location_types. This is used to select the type of stopped icon on the ServiceGANTT.
geo_fence_name	Descriptive name for the location. This is an optional field and if present it is displayed on the ServiceGANTT's tooltip.
last_stopped_overnight	This is the time that the travel status was last known to be stopped at a location in the overnight class (see sp561_geofence_location_types.location_class). This is stored in FRU timezone. This has no meaning outside ServiceOptimizer.
last_stopped_customer	This is the time that the travel status was last known to be stopped at a location in the customer class (see sp561_geofence_location_types.location_class). This is stored in FRU timezone. This has no meaning outside ServiceScheduling.
last_stopped_ok	This is the time that the travel status was last known to be stopped at a location in the stopped OK class (see sp561_geofence_location_types.location_class). This is stored in FRU timezone. This has no meaning outside ServiceScheduling.