ServiceScheduling holds promise sets in the following tables:

Time Bands in sp259_time_bands
Band Types in sp258_band_types
Promise Sets in sp257_band_lists
Promise Set Descriptions in sp256_promise_set_descriptions

The following sections describe these tables.

Note that these tables are described in Oracle terminology – for a translation to SQL Server parlance see below.

Oracle Data Type	SQL Server Data Type	Comment
`CHAR`	`CHAR`
`DATE`	`DATETIME`
`NUMBER`	`INT`	Or `DECIMAL` for fields with a precision greater than 9
`VARCHAR2`	`VARCHAR`

sp256_promise_set_descriptions

This table is used to specify promise set (aka band list) names, and an optional ‘number required’ default for appointment offering

Table Definition

list_name		varchar2(2)		not null
description		varchar2(15)	not null
num_req			number(3)
field_id		varchar2(100)	not null

Keys

primary key (list_name)

Column Descriptions

Column Description

list_name

List identifier.

This column does not support wide (i.e. multibyte) characters.

description Free text description.

num_req Number required when appointments (promises) are requested using this promise set.

sp257_band_lists

This table defines one or more ordered lists of time bands to be used in appointment offering. A set of entries with the same list_name is known as a Promise Set.

Table Definition

list_name		varchar2(2)		not null
band_name		varchar2(4)		not null
sequence		number(2)		not null

Keys

primary key (list_name, band_name)
foreign key (band_name) references sp259_time_bands (band_name)
foreign key (list_name) references sp256_promise_set_descriptions (list_name)

Column Descriptions

Column Description

list_name

List identifier. Names the Promise Set.

This column does not support wide (i.e. multibyte) characters.

band_name

Time band identifier. ‘Name’ in the table in section 1.1.

This column does not support wide (i.e. multibyte) characters.

sequence Not used internally but available for ordering the time bands for display.

sp258_band_types

This table is used to define time band types – e.g. two hour time bands, half day time bands etc. The band_type should only be a single character.

Table Definition

band_type		varchar2(2)		not null
description		varchar2(15)	not null
sequence		number(2)		not null
field_id		varchar2(100)	not null

Keys

primary key (band_type)
unique (description)

Column Descriptions

Column Description

band_type

Band type identifier.

This column does not support wide (i.e. multibyte) characters.

description Band type description.

sequence Sequence number; used internally to order the time bands within the list for deletion of duplicates; the lowest sequence number is kept.

sp259_time_bands

This table defines the start and end time of each named time band. The band_type should only be a single character.

Table Definition

band_name		varchar2(4)		not null
description		varchar2(15)	not null
start_time		date			not null
end_time		date			not null
band_type		varchar2(2)		not null
field_id		varchar2(100)	no null

Keys

primary key (band_name)
unique (description)
foreign key (band_type) references sp258_band_types (band_type)

Column Descriptions

Column	Description
`band_name`	Time band identifier. This column does not support wide (i.e. multibyte) characters.
`description`	Time band description.
`start_time`	Start time.
`end_time`	End time.
`band_type`	Time band type. This column does not support wide (i.e. multibyte) characters.

Sequence

The sequence field of sp258_band_types does not order the list of Time Bands returned to the SMS in an offer. It is used internally by ServiceOptimizer when deciding which of a pair of duplicate offers to delete; higher sequence numbers are deleted first. For this reason, higher sequence numbers should be allocated to longer Time Bands.

Browser not supported