sp100_activities

activity_no

number(9)

employee_no

number(9)

resp_unit_no

number(4)

activity_type

number(2)

crew_no

number(9)

crew_leader

number(1)

job_no

number(9)

expected_start

date

expected_end

date

notes

clob

overtime

number(6)

travel_time

number(6)

lunch_time

number(6)

activity_status

number(2)

schedule_status

number(5)

jeopardy_warnings

number(9)

time_scheduled

date

postcode

varchar2(30)

region_no

number(9)

unalloc_reason

number(2)

last_dispatched_eta

date

earmark_dispatch_status

number(2)

earmark_dispatch_type

number(2)

jeopardy_report_marker

number(1)

EOD_travel

number(6)

last_dispatched

date

expected_start_is_dst

number(1)

expected_end_is_dst

number(1)

jeopardy_warnings2

number(9)

default 0

ssr_marked

number(1)

transaction_id

number(9)

local_knowledge

number(2)

expected_duration

number(8) 

expected_overhead

number(8) 

scheduled_duration

number(8) 

scheduled_overhead

number(8) 

actual_duration

number(8) 

has_been_or_is_logged_on

number(1)

confirmed_state

number(1) 

dispatch

number(1)

travel_mode

number(1)

booked_start

date

last_update

date

default sysdate

last_jeopardy_update

date

manually_scheduled

number(1)

default 0

The identifier of the employee undertaking the activity.

Earlier versions of ServiceOptimizer set employee_no to -1 to indicate that the activity is an unscheduled appointment or fault fixing activity (so if the employee_no is -1 then job_no should not be NULL.) From ServiceOptimizer version 3.2 onwards, however, it is not sufficient simply to test for an employee_no of -1 when trying to establish whether or not a job is unresourced. 

When an appointment is booked through the ABS, it will be nominally associated with an employee by setting this column. It is not until the job has been scheduled, or manually allocated via the Gantt interface, that it can be said for certain that the job will be carried out by the specified employee. Until that time, the job is still said to be unresourced, notwithstanding the fact that employee_no has been set. 

To discover whether or not a job is resourced, therefore, it is necessary to check the value of schedule_status (see below). 

The amount of time, in seconds, included in the job duration for the employee to take a break.

This is only relevant to jobs (not Standard Activities).

If the job has no break included, this is set to NULL.

If an All Day job (ref. sp111_jobs.job_status) has a break allocated to it, the break duration is always zero. 

It is possible to resize a job such that its break duration is zero. 

For a job that spans more than one operative day, this is the break duration for the first part job. This column is always NULL for an unresourced job. 

• 0: Tentative.
• 1: (Deprecated).
• 2: Engineer earmarked.
• 3: Engineer contacted.
• 4: Travelling.
• 5: Engineer logged on.
• 6: Engineer logged off.
• 7: Job clearedThis column is always NULL for an unresourced job. 

A number denoting the current scheduling status of the activity, for appointments and fault fixing activities. For Standard Activities, see below. 

This value is a bit pattern, as follows: 

• Scheduled field - 2 bits.
• Bit 0: Manually allocated.
• Bit 1: Auto allocated.

Note that a job will remain unresourced until it has been automatically scheduled, or manually allocated. That is, it is unresourced if neither the Manually allocated or the Auto allocated bits are set - i.e. if schedule_status = 0. 

For Standard Activities, this field is 1 if the standard activity wishes to include breaks, and 0 otherwise. 

Flag indicating whether the activity is in some kind of jeopardy. If this value and the value of jeopardy_warnings2 are both zero, then the activity is not in jeopardy. Zero for Standard Activities. Jeopardy conditions are identified in FS020 and FS175.

This value is treated as a bit pattern as follows:

• Bit 0: late contacted (SPJM_LATE_CONTACT) (C).
• Bit 1: late log on (SPJM_LATE_LOGON) (N).
• Bit 2: late log off (SPJM_LATE_LOGOFF) (F).
• Bit 3: job overlap (SPJM_OVL_JOB) (J).
• Bit 4: late earmarked (SPJM_LATE_EARMARK) (E).
• Bit 5: late travelling (SPJM_LATE_TRAVEL) (T).
• Bit 6: (Reserved).
• Bit 7: shift overlap (SPJM_OVL_SHIFT) (S).
• Bit 8: overtime overlap (SPJM_OVL_OT) (O).
• Bit 9: activity overlap (SPJM_OVL_STDACT) (X).
• Bit 10: (Reserved).
• Bit 11: broken promise (SPJM_PROMISE) set whenever any of bits 14 to 19 and jeopardy_warnings2 bits 0 to 2 are set.
• Bit 12: (Reserved).
• Bit 13: unresourced job (SPJM_UNSCHEDULED) (U).
• Bit 14: missed appointment (SPJM_MISSED_APPT) set when bits 18 & 19 are set. 
• Bit 15: (Reserved).
• Bit 16: early – before contract earliest (SPJM_BEFORE_CONTRACT) (B).
• bit 17: overdue - past contract latest (SPJM_PAST_CONTRACT) (P).
• Bit 18: missed appointment (early) (SPJM_MISSED_APPT_EARLY) (Me).
• Bit 19: missed appointment (late) (SPJM_MISSED_APPT_LATE) (M).
• Bit 20: (Reserved).
• Bit 21: (Reserved).
• Bit 22: (Reserved).
• Bit 23: dispatch failure (earmark) (SPJM_DISPATCH_FAIL_EARMARK) (De).
• Bit 24: dispatch failure (unearmark) (SPJM_DISPATCH_FAIL_UNEARMARK) (Du).
• Bit 25: dispatch failure (re-earmark) (SPJM_DISPATCH_FAIL_REEARMARK) (Dr).
• Bit 26: dispatch failure (batch) (SPJM_DISPATCH_FAIL_BATCH) (Db).
• Bit 27: unsuitable shift for all day job (SPJM_ADJ_UNSUITABLE_SHIFT) (A).
• Bit 28: (Reserved).
• Bit 29: job in No Go region for this operative (SPJM_NOGO) (Ng).

Reason job is unallocated: 

• 1: (Obsolete) (Forced: no RU available).
• 2: Forced: no operative is working.
• 3: Forced: no operative has local knowledge.
• 4: Forced: no operative has skills.
• 5: Forced: no operative has spares.
• 6: Forced: schedule capacity would be exceeded.
• 7: Forced: no time available within access hours.
• 8: Forced: without skill check.
• 9: Forced: with skill check.
• 10: (Obsolete) (Forced: mandatory operative's RU not available).
• 11: Forced: mandatory operative is not working.
• 12: Forced:  mandatory operative has no local knowledge.
• 13: Forced: mandatory operative doesn't have skills.
• 14: Forced: mandatory operative doesn't have spares.
• 15: Forced: mandatory operative would exceed schedule capacity.
• 16: Manually de-allocated.
• 17: De-allocated by the optimizer.
• 18: Forced: no operative has time available.
• 19: Replaced by a more important job.
• 20: (Obsolete).
• 21: Operative found to not have a shift (for an AllDayJob) when ServiceOptimizer started up.
• 22: De-allocated because the job spanned postings after an API call.
• 23: Forced: no operative has suitable shift for AllDay job.
• 24: Forced: mandatory operative has no suitable shift for AllDay job.
• 25: De-allocated because the job spanned shift pattern assignments after an API call.
• 26: De-allocated because the job spanned an operative day boundary after an API call.
• 27: De-allocated because the job overlapped a standard activity after an API callThis column is always NULL if the job is resourced (i.e. not unallocated). 

Flag which records a job on the Gantt's Jeopardy Report as 'marked'. Takes one of two values:

• 0: not marked.
• 1: marked job.

The amount of travel time, in seconds, which is required to travel from the job location to the end-of-day location. This is only relevant to the last job of a day. This column is always NULL for an unresourced job.

Flag indicating which time the job start is in when the time is during Daylight Saving. For example, in the UK:

• 0: the job starts in GMT.
• 1: the job starts in BST.

This column is always NULL for an unresourced job. 

Flag indicating which time the job end is in when the time is during Daylight Saving. For example, in the UK: 

• 0: the job ends in GMT.
• 1: the job ends in BST.

An additional flag indicating further jeopardy conditions in addition to those indicated by jeopardy_warnings.

Zero for Standard Activities. Jeopardy conditions are identified in FS020 and FS175. This value is treated as a bit pattern as follows:

• Bit 0: outside access hours (SPJM2_OUTSIDE_ACC_HOURS) (Ac).
• Bit 1: (Reserved).
• Bit 2: outside service hours (SPJM2_OUTSIDE_SERV_HOURS) (Se).
• Bit 3: job in a dependency group with an unresourced job (SPJM2_DEP_UNSCHEDULED) (Gu).
• Bit 4: broken dependency group – should be same operative (SPJM2_DEP_NOT_SAME_OPER) (Gs).
• Bit 5: broken dependency group – should be different operative (SPJM2_DEP_NOT_DIFF_OPER) (Gd).
• Bit 6: broken dependency group – time constraint violated (SPJM2_DEP_MISSED_TIME) (Gt).
• Bit 7: not all spares in van (SPJM2_EXT_SPARES) (Sv).

Indicates whether the marked check box on the Gantt client Same Site Report is checked or not.

• 0: not marked.
• 1: marked.

Working time (in minutes) the job was predicted to take when it was booked, given its current position in the schedule.

For a resourced job this:

• Takes account of operative efficiency.
• Includes overhead time (where applicable).
• Excludes travel and break.
• Does not reflect modifications to the job's duration after booking.

For an unresourced job this is the same as its booked duration (sp108_job_details.booked_duration). 

Overhead time (in minutes) the job was predicted to take when it was booked, given its current position in the schedule.

For a resourced job this takes account of same-site groups and does not reflect modifications to the job's duration after booking.

For an unresourced job this is the same as its booked overhead (sp108_job_details.booked_overhead). 

Working time (in minutes) that the job was predicted to take - up to the time when the Actual Duration became known.

For a resourced job this:

• Takes account of operative efficiency.
• Includes overhead time (where applicable).
• Excludes travel and break.
• Is updated to reflect modifications to the job's duration after booking.

For an unresourced job this is the same as its normalised scheduled duration (sp108_job_details. normalised_scheduled_duration). 

Overhead time (in minutes) that the job was predicted to take - up to the time when the Actual Duration became known.

For a resourced job this:

• Takes account of same-site groups.
• Is updated to reflect modifications to the job's duration after booking.

For an unresourced job this is the same as its normalised scheduled overhead (sp108_job_details. normalised_scheduled_overhead). 

Working time (in minutes) that the job actually took and hence only known after the job has been Logged Off or Cleared: until then this is NULL. Only represents the true actual duration of the job if SP was supplied with the job's real LoggedOn time. (i.e. has_been_or_is_logged_on set to 1). Otherwise, represents the time from the estimated ETA to the actual ETF. Includes overhead, excludes break and travel. An unresourced job has no actual duration hence this field will be NULL.

Identifies whether arrangements for the site visit have been confirmed with the customer.

• 0: Not yet been confirmed.
• 1: Is in the process of being confirmed.
• 2: Has been confirmed.
• 3: Is in the process of being reconfirmed.

NULL for a standard activity.

Identifies whether a standard activity is dispatchable.

• 0: no dispatch.
• 1: dispatch. 

Specifies the travel mode for the standard activity.

• 0: No travel mode.
• 1: Given start time is also the start time for travelling.
• 2: Given start time is the arrival time so start time needs to be adjusted to accommodate travel time. 

The travel time to the job, in seconds, returned by the Real Time Travel Server (only used if RTT enabled with SP083 postcode_tm_lookup)

manually_scheduled

Indicates if the job has been manually moved on the Gantt; 1 means that the job has been moved;  0 means the job has not been moved. A job is considered to have been moved when the following manual actions have been performed:

• • Change to a job's start time or arrival time.
  • Change to a job's end time.
  • Change to a job's operative.
  • Dragging a job from the in-tray (unresourced) to the schedule (not ABS allocate).