sp057_regions

Each record in this table represents a geographical region.

Regions are organised into a three level hierarchy, represented by the region_level and owning_region_no columns. A level one region is a large region containing (owning) a number of smaller level two regions. Similarly, each level two region contains a number of smaller level three regions.

Each level three region comprises a number of postcodes (or other user defined location codes). This mapping is contained in sp047_postcodes.

Regions in turn are mapped either explicitly or implicitly to zero or more responsibility units. This mapping is contained in table sp059_ru_regions.

Table Definition

Field	Type	Default
`region_no`	`number(9)`
`region_ref`	`varchar2(26)`
`owning_region_no`	`number(9)`
`region_level`	`number(1)`
`internal_travel_time`	`number(6)`
`internal_travel_time2`	`number(6)`
`internal_journey_type`	`number(3)`
`earliest_start`	`date`
`latest_start`	`date`
`no_go_start`	`date`
`no_go_end`	`date`
`tz_no`	`number(2)`
`control`	`number(1)`
`lat1`	`number(18,9)`	`null`
`lon1`	`number(18,9)`	`null`
`lat2`	`number(18,9)`	`null`
`lon2`	`number(18,9)`	`null`

Keys

primary key (region_no)

unique (region_no, region_ref)

unique (region_ref)

foreign key (owning_region_no) references sp057_regions (region_no)

foreign key (internal_journey_type) references sp263_journey_types (journey_type)

foreign key (tz_no) references sp056_time_zones (tz_no)

Primary Key Referenced From

Column Descriptions

Field	Description
`region_no`	ServiceOptimizer-generated unique identifier.
`region_ref`	End users' unique identifier. This column does not support wide (i.e. multibyte) characters.
`owning_region_no`	Identifier of the larger region containing this region.
`region_level`	Number indicating the level of the region within the hierarchy of regions. The largest regions have a `region_level` of `1`; smaller regions have a level of `2`; the smallest regions have a level of `3`.
`internal_travel_time`	Travel time in minutes between two appointments within the region.
`internal_travel_time2`	Alternative travel time for journeys within a region. This value (same postcode travel) is only used if it is lower than the region internal travel time that would otherwise be used.
`internal_journey_type`	Journey type for journeys within the region.
`earliest_start`	Earliest time of day an appointment may be made for the region. The schedulers will ensure that employees are not scheduled to start working in the region before this time. Uses this region's Time Zone.
`latest_start`	Latest time of day an appointment may be made for the region. The schedulers will ensure that employees are scheduled so as to complete any work in the region before this time (i.e. "latest_start" is a misnomer.) Uses this region's Time Zone.
`no_go_start`	No longer supported.
`no_go_end`	No longer supported.
`tz_no`	The time zone associated with a particular region. A level 3 region can have an associated time zone. A level 2 region can have a time zone which is the default time zone of any of its constituent level 3 regions that have a null time zone. Ditto level 1 regions. If this field is null, the time zone applied is found by recursively looking at the time zone of the owning region (up to level 1 region). Otherwise if no owning region has a time zone, `sp083` '`tz_Default`' is used. controlUsed by system to indicate use of postcode travel lookup.
`lat1`	Coordinate system `WGS84latitude` of the top left corner of the region rectangle.
`lon1`	Longitude of the top left corner of the region rectangle.
`lat2`	Latitude of the bottom right corner of the region rectangle.
`lon2`	Longtude of the bottom right corner of the region rectangle.