14.1 sp207_dispatch_messages

This table provides a persistent store for dispatch messages generated as result of changes to jobs and standard activities, so that they can be recovered in the event of system failure. It is not a complete copy of all messages that are waiting to dispatch. Once a message from this table has delivered successfully it is deleted from the table.

HouseKeeping

This table is tidied by sp_dbtidy’s Global group (messages that have lost their ‘posting’ as the engineer has move to another FRU), and by the Job group (specific jobs)

Table Definition

Field	Type	Default	Nullable
`message_no`	`number(9)`
`job_ref`	`varchar2(50)`
`creation_time`	`date`
`earmark_type`	`number(2)`
`dispatch_status`	`number(2)`
`employee_no`	`number(9)`
`reearmark`	`number(1)`
`ets`	`date`
`eta`	`date`
`etf`	`date`
`postcode`	`varchar2(30)`
`region_no`	`number(9)`
`team_no`	`number(9)`
`iru_no`	`number(4)`
`lunch_time`	`number(6)`
`tentative_job_count`	`number(6)`
`book_loc_no`	`number(2)`
`send_before`	`number(9)`
`ISVWDL`	`number(1)`

Keys

Key Type	Column	Reference Table	Reference Column
primary key	`message_no`
foreign key	`dispatch_status`	`sp287_dispatch_statuses`	`dispatch_status`
foreign key	`employee_no`	`14.1 sp210_employees`	`employee_no`
foreign key	`region_no`	`/wiki/spaces/SCH141/pages/27068047`	`region_no`
foreign key	`iru_no`	`/wiki/spaces/SCH141/pages/27099986`	`resp_unit_no`
foreign key	`team_no`	`/wiki/spaces/SCH141/pages/27001323`	`team_no`
foreign key	`book_loc_no`	`14.1 sp218_booking_locations`	`bkg_location_no`

Column Descriptions

Field	Description
`message_no`	Primary key: unique identifier for this message
`job_ref`	The job this message relates to. In the case of the standard activity it is the `activity_no` from `14.1 sp100_activities`.
`creation_time`	Date/time at which the message was generated -- uses FRU Time Zone
`earmark_type`	Type of message this relates to: 0 - UserEarmark 1 - AutoEarmark 2 - Unearmark 3 - ReEarmark 4 - BatchEarmark 5 - RealTimeEarmark 6 - SA_UserEarmark 7 - SA_AutoEarmark 8 - SA_Unearmark 9 - SA_ReEarmark 10 - SA_BatchEarmark 11 - SA_RealTimeEarmark 12 - CheckClient (Internal Use Only) 13 -TerminateSession (Internal Use Only)
`dispatch_status`	Delivery status of message, references `sp287_dispatch_statuses`.
`employee_no`	Employee the job/SA was assigned to when the message was generated, references `sp210_employees`.
`reearmark`	Boolean, whether this message is a re-earmark notification or not.
`ets`	Expected start time of the job/SA when message was generated - uses FRU Time Zone.
`eta`	Expected arrival time of the job/SA when message was generated - uses FRU Time Zone.
`etf`	Expected end time of the job/SA when message was generated - uses FRU Time Zone.
`postcode`	Postcode of the job/SA.
`region_no`	Region number of the job or standard activities postcode.
`team_no`	Team that the employee was posted to when the message was generated.
`iru_no`	IRU that the employee was posted to when the message was generated.
`lunch_time`	Amount of break time when the message was generated (in seconds) - only applies if a job.
`tentative_job_count`	Count of jobs that have been allocated to this employee (EmpID), but which he doesn’t yet know about.
`book_loc_no`	Booking location for the job.
`send_before`
`ISVWDL`	Indicates if the job is a Virtual Work Dynamic Location job or not.