14.1 sp370_operative_days
- Billy Sewell (Unlicensed)
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 14.1 sp210_employees(employee_no)
foreign key (start_loc_type) references /wiki/spaces/SCH141/pages/27066920(location)
foreign key (start_loc_region_no) references /wiki/spaces/SCH141/pages/27068047(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 /wiki/spaces/SCH141/pages/27099986(resp_unit_no)
Primary Key Referenced From
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. |