14.1 sp111_jobs

This table contains the top level details of a requirement for an employee to visit a customer site to perform some job. This job may be either an appointment or a fault fixing activity. The lower level details of the requirement are contained in the job_locations, job_details, job skills and job_equipment tables.

HouseKeeping

This table is tidied by sp_dbtidy's Job group

Table Definition

Field	Type	Default
job_no	number(9)
job_ref	varchar2(50)
creation_date	date	sysdate
bkg_location_no	number(2)
job_type	number(2)
customer_ref	varchar2(30)
customer_name	varchar2(50)
address1	varchar2(50)
address2	varchar2(50)
address3	varchar2(50)
address4	varchar2(50)
postcode	varchar2(30)
phone1	varchar2(40)
phone2	varchar2(40)
contact_name	varchar2(50)
priority	number(2)
description1	varchar2(500)
description2	varchar2(500)
description3	varchar2(500)
description4	varchar2(500)
job_status	number(2)
force_go	number(1)	0
importance	number(1)
on_hold	number(1)
customer_group_id	varchar2(30)
site_id	varchar2(30)
delivery_size	number(9)
collection_size	number(9)
transaction_id	number(9)
dependency_group_id	varchar2(50)
capacity_category_no	number(9)

Keys

primary key (job_no)
unique (job_ref)
foreign key (bkg_location_no) references 14.1 sp218_booking_locations (bkg_location_no)
foreign key (job_type) references 14.1 sp105_activity_types (activity_type)
foreign key (job_status) references 14.1 sp285_job_status (job_status)
foreign key (capacity_category_no) references sp350_capacity_categories (capacity_category_no)

Primary Key Referenced From

Column Descriptions

Field	Description
job_no	ServiceOptimizer-generated unique identifier.
job_ref	End users' unique identifier.
creation_date	The date and time at which the record was created. Uses the time zone of the DBMS server machine.
bkg_location_no	Identifier of the location / external system from which the job details were received by ServiceOptimizer.
job_type	Activity type identifier.
customer_ref	Customer reference.
customer_name	Customer name ...
address1	address ...
address2
address3
address4
postcode	... and postcode.
phone1	Telephone number
phone2	Telephone number
contact_name	A customer contact name.
priority	The job priority. The higher the numeric value, the higher the priority, and the higher the cost of the job being late as specified in table sp313.
description1	Free text description of the requirement.
description2	These description fields are copied into sp100_activities.notes
description3	when the job is booked. Modifications to the job's notes affect
description4	sp100_activities.notes but leave these fields unchanged.
job_status	Job Status (not same as Dispatch Status): 0 - normal 1 - reserved 4 - cleared 8 - All Day Job 9 - reserved (12 - All day cleared is not required since 'cleared' refers to a 'phone clear' in which case the cleared state replaces the all day state)
force_go	A value of 1 indicates that the job should be scheduled even if spares are not available.
importance	Records the importance assigned to the job when it was booked.
on_hold	Set if the job has been marked 'on hold' to stop it being allocated automatically.
customer_group_id	A customer group ID.
site_id	A site group ID.
delivery_size	is the amount of space occupied by the equipment to be removed from the employee's vehicle as part of this job.
collection_size	is the amount of space occupied by equipment that will be loaded onto the employee's vehicle as part of this job.
transaction_id	See SP599_TRANSACTION_AUDIT for details.
dependency_group_id	The dependency group for the job (if any).
capacity_category_no	Capacity Category for the job.