Table: Schedule_Item | |||
Each item that can be scheduled has a row in this table. | |||
Columns | |||
Name | Type | Constraints | Description |
schedule_item_KEY | int | NOT NULL | Assigned by COMMON_dp_Allocate_Unique_Identifiers. Assigned primary key for this table. |
update__staff_KEY | int | NOT NULL | |
update_date_utc | datetime | NOT NULL | |
create_date_utc | datetime | NOT NULL | |
schedule_item_type_KEY | int | NOT NULL | Key to one of the [Schedule_Item_Type] records. Knowing the type can make finding the associated data easier. 1=Project 2=Task 3=To Do 4=Scheduled Benefit 5=Scheduled Phone Call 6=Meeting 7=Education Course |
staff_assignment_method_KEY | int | NOT NULL | 1=Assigned Staff 2=Work Queue |
priority_KEY | int | NOT NULL | If Priority does not apply to this type of Schedule_Item, use Normal priority. 998=Low 999=Normal 1000=High |
is_all_day_event | bit | NOT NULL | When true, the time on [target_start_date_utc] and [target_complete_date_utc] must be 0:00:00 and may appear offset to whole days in local time for the user. |
target_start_date_utc | datetime | The start of the valid period for this item to be scheduled. | |
budgeted_hours | decimal (9, 2) | NOT NULL | Number of hours budgeted to this item. Items can have a wide target range specified, but only use a small portion of it. |
description | nvarchar (250) | NOT NULL | User defined description. |
schedule_item_note | nvarchar (-1) | NOT NULL | Any comments or notes about the item. |
target_complete_date_exclusive_utc | datetime | The end of the valid period (exclusive) for this item to be scheduled. | |
current_due_date_exclusive_utc | datetime | NOT NULL | Current due date (exclusive) of this schedule item. |
posted_actual_hours | decimal (17, 2) | NOT NULL | Cached value of the hours on posted Sheet_Entry rows associated with this row. |
posted_actual_amount | decimal (17, 2) | NOT NULL | Cached value of the amount on posted Sheet_Entry rows associated with this row. |
remaining_hours_adjustment | decimal (9, 2) | NOT NULL | Cached value of the hours on Schedule_Item_Budget_Remaining_Entry rows associated with this row. |
remaining_hours_adjustment_count | int | NOT NULL | Cached count of the Schedule_Item_Budget_Remaining_Entry rows associated with this row. |
staff_qualifications__filter_KEY | int | An optional Staff filter that represents Staff qualified to work on this item. Some types do not allow Staff qualifications. | |
target_start__time_zone_KEY | smallint (5) | ||
target_complete__time_zone_KEY | smallint (5) | ||
live_actual_hours | decimal (17, 2) | NOT NULL | Cached value of the hours on all Sheet_Entry rows associated with this row. |
live_actual_amount | decimal (17, 2) | NOT NULL | Cached value of the amount on all Sheet_Entry rows associated with this row. |
schedule_item_series_KEY | int | The linked [Schedule Item Series]. | |
activity_KEY | int | The linked [Activity]. | |
client_KEY | int | The linked [Client]. If of__contact_KEY is specified and is the primary contact of a Client, that client_KEY is also this value. | |
engagement_KEY | int | The linked [Engagement]. | |
tracking_description_KEY | int | The linked [Tracking_Description]. | |
reminder_date_utc | datetime | The date the reminder is to be recorded. | |
benefit_KEY | int | Link to the benefit for items of type scheduled benefit. | |
scheduled_benefit_status_KEY | int | Only allowed for items of type scheduled benefit. 1=Pending 2=Approved 3=Denied | |
approved__staff_KEY | int | The Staff that approved the item. | |
approved_date | smalldatetime | The date the item was approved. | |
scheduled_phone_call_status_KEY | int | Only allowed for items of type scheduled phone call. 1=Scheduled 2=Attempted 3=Completed | |
with__contact_KEY | int | For scheduled phone calls, the contact that is to be called. | |
of__contact_KEY | int | For scheduled phone calls, the association of with__contact_KEY involved - for example which Client if of__contact_KEY is associated to multiple Clients. | |
is_open | bit | NOT NULL | False when this item is closed by state (tracking description or status). May be true for items that are closed now because they have elapsed, like a Meeting in the past. |
create__changeset_KEY | int | NOT NULL | |
update__changeset_KEY | int | NOT NULL | |
education_sponsor_KEY | int | ||
education_delivery_format_KEY | int | ||
location | nvarchar (250) | NOT NULL | |
instructor | nvarchar (250) | NOT NULL | |
cost | decimal (9, 2) | NOT NULL |
Primary key | |||
Name | Columns | Description | |
PK_Schedule_Item$schedule_item_KEY | schedule_item_KEY | CLUSTERED | |
Indexes | |||
Name | Columns | Description | |
IX_Schedule_Item$activity_KEY | activity_KEY | ||
IX_Schedule_Item$approved__staff_KEY | approved__staff_KEY | ||
IX_Schedule_Item$benefit_KEY | benefit_KEY | ||
IX_Schedule_Item$client_KEY | client_KEY | ||
IX_Schedule_Item$education_delivery_format_KEY | education_delivery_format_KEY | Index supports foreign key FK_Schedule_Item__Education_Delivery_Format__education_delivery_format_KEY | |
IX_Schedule_Item$education_sponsor_KEY | education_sponsor_KEY | Index supports foreign key FK_Schedule_Item__Education_Sponsor__education_sponsor_KEY | |
IX_Schedule_Item$engagement_KEY | engagement_KEY | ||
IX_Schedule_Item$is_open$schedule_item_type_KEY$target_complete_date_exclusive_utc | is_open, schedule_item_type_KEY, target_complete_date_exclusive_utc | ||
IX_Schedule_Item$of__contact_KEY | of__contact_KEY | ||
IX_Schedule_Item$priority_KEY | priority_KEY | ||
IX_Schedule_Item$schedule_item_KEY$current_due_date_exclusive_utc | schedule_item_KEY, current_due_date_exclusive_utc | ||
IX_Schedule_Item$schedule_item_series_KEY$current_due_date_exclusive_utc | schedule_item_series_KEY, current_due_date_exclusive_utc | ||
IX_Schedule_Item$schedule_item_type_KEY$is_open | schedule_item_type_KEY, is_open | ||
IX_Schedule_Item$schedule_item_type_KEY$target_complete_date_exclusive_utc$schedule_item_KEY | schedule_item_type_KEY, target_complete_date_exclusive_utc, schedule_item_KEY | ||
IX_Schedule_Item$scheduled_benefit_status_KEY | scheduled_benefit_status_KEY | ||
IX_Schedule_Item$scheduled_phone_call_status_KEY | scheduled_phone_call_status_KEY | ||
IX_Schedule_Item$staff_assignment_method_KEY | staff_assignment_method_KEY | ||
IX_Schedule_Item$staff_qualifications__filter_KEY | staff_qualifications__filter_KEY | ||
IX_Schedule_Item$target_complete__time_zone_KEY | target_complete__time_zone_KEY | Index supports foreign key FK_Schedule_Item__Time_Zone__target_complete__time_zone_KEY | |
IX_Schedule_Item$target_complete_date_exclusive_utc | target_complete_date_exclusive_utc | ||
IX_Schedule_Item$target_start__time_zone_KEY | target_start__time_zone_KEY | Index supports foreign key FK_Schedule_Item__Time_Zone__target_start__time_zone_KEY | |
IX_Schedule_Item$target_start_date_utc | target_start_date_utc | ||
IX_Schedule_Item$tracking_description_KEY | tracking_description_KEY | ||
IX_Schedule_Item$with__contact_KEY | with__contact_KEY |