sp370_operative_days

The smallest scheduling unit managed within ServiceOptimizer is a single employee/date also known as an OperativeDay: each record in this table contains the attributes of the employee's schedule as calculated by ServiceOptimizer.

Data is only updated for active FRUs within the FRU memory horizon.

If an employee is not posted to any team on a particular date, there will be no sp370_operative_days record for that OperativeDay.
All date fields are in FRU local time.

Note that although the descriptions below reference fields in sp210_employees and other tables, values can be derived instead from an overriding pattern (e.g. sp122_work_patterns) or date-specific override (e.g. sp064_overrides).

HouseKeeping

This table is tidied by sp_dbtidy's Global group.

Table Definition

Field	Type	Default	Nullable
employee_no	number(9)
schedule_date	date
start_of_day_time	date
end_of_day_time	date
shift_start	date
shift_end	date
authorised_overtime	number(4)
travel_in_owntime_towork	number(6)
travel_in_owntime_home	number(6)
break_duration	number(4)
break_earliest_start	date
break_latest_start	date
start_loc_type	number(1)
start_loc_postcode	varchar(30)
start_loc_region_no	number(9)
end_loc_type	number(1)
end_loc_postcode	varchar(30)
end_loc_region_no	number(9)
lkc_loc_type	number(1)
lkc_loc_postcode	varchar(30)
lkc_loc_region_no	number(9)
is_public_holiday	number(1)
total_capacity_mins	number(6)
resp_unit_no	number(4)

Keys

primary key (employee_no, schedule_date)
foreign key (employee_no) references sp210_employees(employee_no)
foreign key (start_loc_type) references sp271_start_end_locations(location)
foreign key (start_loc_region_no) references sp057_regions(region_no)
foreign key (end_loc_type) references sp271_start_end_locations(location)
foreign key (end_loc_region_no) references sp057_regions(region_no)
foreign key (lkc_loc_type) references sp271_start_end_locations(location)
foreign key (lkc_loc_region_no) references sp057_regions(region_no)
foreign key (resp_unit_no) references sp058_responsibility_units(resp_unit_no)

Primary Key Referenced From

sp371_opday_capacity

Column Descriptions

Field	Description
employee_no	Identifies the employee.
schedule_date	Identifies the date.
start_of_day_time	Boundary between this OperativeDay and the preceding OperativeDay.
end_of_day_time	Boundary between this OperativeDay and the next OperativeDay.
shift_start	Start of shift. See sp115_shifts (start_time). Null if the employee has no shift on this day.
shift_end	End of shift. See sp115_shifts (end_time). Null if the employee has no shift on this day.
authorised_overtime	The maximum number of minutes overtime that can be allocated to the employee on this day. See sp210_employees (max_overtime). Null if the employee has no shift on this day.
travel_in_owntime_towork	Maximum before shift travel (in minutes). See sp210_employees (TOTB). Null if the employee has no shift on this day.
travel_in_owntime_home	Maximum after shift travel (in minutes). See sp210_employees (TOTA). Null if the employee has no shift on this day.
break_duration	Break duration. See sp115_shifts (lunch_duration). Null if the employee has no shift on this day or if the shift has no break.
break_earliest_start	Earliest start for break. See sp115_shifts (lunch_start). Null if the employee has no shift on this day or if the shift has no break.
break_latest_start	Latest start for break. See sp115_shifts (lunch_end). Null if the employee has no shift on this day or if the shift has no break.
start_loc_type start_loc_postcode start_loc_region_no	Location from which employee starts work. See sp210_employees (default_start_loc, default_start_loc_postcode, default_start_loc_region_no).
end_loc_type end_loc_postcode end_loc_region_no	Location from which employee finishes work. See sp210_employees (default_end_loc, default_end_loc_postcode, default_end_loc_region_no).
lkc_loc_type lkc_loc_postcode lkc_loc_region_no	Location about which employee's range local knowledge is centred. See sp210_employees (default_LK_centre_loc, default_LK_centre_postcode, default_LK_centre_region_no).
is_public_holiday	Indicates whether this day is a Public Holiday. 0 = Not a Public Holiday. 1 = Public Holiday.
total_capacity_mins	Total capacity in minutes. Null if capacity checking is not enabled.
resp_unit_no	The responsibility unit that the employee is posted to on this date.