Table: Sheet_Entry | |||
Columns | |||
Name | Type | Constraints | Description |
sheet_entry_KEY | int | NOT NULL | Assigned by COMMON_dp_Allocate_Unique_Identifiers. Database assigned primary key for this table. |
row_version | timestamp | NOT NULL | |
update__staff_KEY | int | NOT NULL | |
client_KEY | int | Key to the Client that this entry is for. The client must be the client of the Engagement. | |
engagement_KEY | int | Key to the Engagement this entry is for. | |
activity_KEY | int | Key to the Activity this entry is for. | |
entry_date | smalldatetime | Date this entry is for. | |
staff_billing_rate_KEY | int | Key to the Staff Billing Rate used to calculate the amount. | |
units | decimal (9, 2) | Quantity of units (or hours). | |
unit_cost | decimal (9, 4) | Cost per unit (or hour) at the time the amount was computed. | |
unit_price | decimal (9, 4) | Price per unit (or hour) at the time the amount was computed. Storage is required because amount can be rounded. | |
amount | decimal (9, 2) | Overridden extended price after computation and rounding. | |
comment | nvarchar (-1) | NOT NULL | An optional comment. |
biller_note | nvarchar (-1) | NOT NULL | An optional comment for the biller. |
sheet_entry_type_KEY | int | NOT NULL | 1=Standard 2=Progress Billing 3=Billing Adjustment 4=Timer Entry 5=Remote Standard 6=Exported Remote Standard |
staff_KEY | int | Staff this Sheet Entry is for. | |
sheet_date | smalldatetime | Date of the Sheet, also known as control date. | |
approved_date | smalldatetime | Date this item was approved. | |
approved__staff_KEY | int | Key of the Staff who approved this item. | |
posted_date | smalldatetime | Date this item was posted. | |
posted__staff_KEY | int | Key of the Staff who posted this item. | |
project_KEY | int | Optionally links this Sheet Entry to a Project. | |
task_KEY | int | Optionally links this Sheet Entry to a Task. | |
calculated_amount | decimal (9, 2) | Calculated extended price after computation and rounding. | |
amount_is_overridden | bit | NOT NULL | True when the calculated_amount is overridden and is necessary so that the override is not deleted if the values happen to match later. |
education_course__schedule_item_KEY | int | Optionally links this Sheet Entry to a [Education_Course] row. | |
create__changeset_KEY | int | NOT NULL | |
update__changeset_KEY | int | NOT NULL | |
complete | bit | NOT NULL | |
integration_application_KEY | int | NOT NULL | Key to the Integration Application table to denote the source application for this entry. |
activity__activity_class_KEY | int | Cached value of the activity class: 1=Billable 2=Non-billable 3=Administrative | |
activity__activity_method_KEY | int | Cached value of the activity method: 1=Time 2=Expense | |
project__schedule_item_KEY | int | Cached value of the schedule_item_KEY for the associated Project. | |
task__schedule_item_KEY | int | Cached value of the schedule_item_KEY for the associated Task. |
Primary key | |||
Name | Columns | Description | |
PK_Sheet_Entry$sheet_entry_KEY | sheet_entry_KEY | CLUSTERED | |
Indexes | |||
Name | Columns | Description | |
IX_Sheet_Entry$activity_KEY$activity__activity_class_KEY$activity__activity_method_KEY | activity_KEY, activity__activity_class_KEY, activity__activity_method_KEY | Index supports foreign key FK_Sheet_Entry__Activity__activity_KEY__activity__activity_class_KEY__activity__activity_method_KEY | |
IX_Sheet_Entry$activity_KEY$sheet_entry_KEY | activity_KEY, sheet_entry_KEY | An index by activity_KEY and sheet_entry_KEY. This is at least used to determine if an Activity can be deleted and may be used by other queries as well. sheet_entry_KEY is explicitly a key value since it will be present in the row locator anyway. | |
IX_Sheet_Entry$approved__staff_KEY | approved__staff_KEY | Index supports foreign key FK_Sheet_Entry__Staff__approved__staff_KEY | |
IX_Sheet_Entry$client_KEY | client_KEY | ||
IX_Sheet_Entry$client_KEY$activity_KEY$entry_date$posted__staff_KEY@amount@sheet_entry_KEY@sheet_entry_type_KEY@units | client_KEY, activity_KEY, entry_date, posted__staff_KEY, amount, sheet_entry_KEY, sheet_entry_type_KEY, units | An index by client_KEY, activity_KEY, entry_date and posted__staff_KEY DESC that covers amount, sheet_entry_type_KEY, units and sheet_entry_KEY. Many queries access Sheet_Entry by client_KEY, especially for Client Families. | |
IX_Sheet_Entry$client_KEY$activity_KEY$sheet_entry_type_KEY@sheet_entry_KEY | client_KEY, activity_KEY, sheet_entry_type_KEY, sheet_entry_KEY | An index by client_KEY and activity_KEY that covers sheet_entry_type_KEY and sheet_entry_KEY. Many queries access Sheet_Entry by client_KEY, especially for Client Families. | |
IX_Sheet_Entry$education_course__schedule_item_KEY | education_course__schedule_item_KEY | Index supports foreign key FK_Sheet_Entry__Schedule_Item__education_course__schedule_item_KEY | |
IX_Sheet_Entry$engagement_KEY$entry_date$posted__staff_KEY@activity_KEY@amount@sheet_entry_type_KEY | engagement_KEY, entry_date, posted__staff_KEY, activity_KEY, amount, sheet_entry_type_KEY | ||
IX_Sheet_Entry$engagement_KEY$project_KEY$task_KEY$posted__staff_KEY$activity_KEY@amount@units | engagement_KEY, project_KEY, task_KEY, posted__staff_KEY, activity_KEY, amount, units | An index by engagement_KEY, project_KEY, task_KEY, posted__staff_KEY, activity_KEY that covers units and amount used by dbo.Project_View and dbo.Task_View to retrieve actual budget information. | |
IX_Sheet_Entry$entry_date$activity_KEY$sheet_entry_KEY$sheet_entry_type_KEY$engagement_KEY$amount$posted__staff_KEY | entry_date, activity_KEY, sheet_entry_KEY, sheet_entry_type_KEY, engagement_KEY, amount, posted__staff_KEY | ||
IX_Sheet_Entry$entry_date$engagement_KEY | entry_date, engagement_KEY | An index by entry_date that covers engagement_KEY. | |
IX_Sheet_Entry$integration_application_KEY | integration_application_KEY | Index supports foreign key FK_Sheet_Entry__Integration_Application__integration_application_KEY | |
IX_Sheet_Entry$posted__staff_KEY | posted__staff_KEY | Index supports foreign key FK_Sheet_Entry__Staff__posted__staff_KEY | |
IX_Sheet_Entry$project__schedule_item_KEY@activity__activity_method_KEY@amount@posted__staff_KEY@units | project__schedule_item_KEY, activity__activity_method_KEY, amount, posted__staff_KEY, units | Inserting rows in the Sheet_Entry table triggers an update of the cached amounts on Schedule_Item, and needs to query all the Sheet_Entry rows for the same schedule_item_KEY. | |
IX_Sheet_Entry$project_KEY | project_KEY | ||
IX_Sheet_Entry$project_KEY$project__schedule_item_KEY | project_KEY, project__schedule_item_KEY | Index supports foreign key FK_Sheet_Entry__Project__project_KEY__project__schedule_item_KEY | |
IX_Sheet_Entry$sheet_entry_KEY$client_KEY$activity_KEY$entry_date$posted__staff_KEY@amount@sheet_entry_type_KEY@units | sheet_entry_KEY, client_KEY, activity_KEY, entry_date, posted__staff_KEY, amount, sheet_entry_type_KEY, units | An index by sheet_entry_KEY that covers client_KEY, activity_KEY, entry_date, posted__staff_KEY DESC, sheet_entry_type_KEY, amount, and units. Many queries access Sheet_Entry by sheet_entry_KEY and need the covered columns, this index tends to prevent a scan of the Sheet_Entry table in these cases. | |
IX_Sheet_Entry$sheet_entry_KEY@client_KEY@engagement_KEY@project_KEY | sheet_entry_KEY, client_KEY, engagement_KEY, project_KEY | An index by sheet_entry_KEY that covers client_KEY is used when looking for any time that was billed to a different client (for Client Families.) An index by sheet_entry_KEY that covers engagement_KEY and project_KEY greatly speeds up importing conversion data. | |
IX_Sheet_Entry$sheet_entry_type_KEY$staff_KEY@sheet_entry_KEY | sheet_entry_type_KEY, staff_KEY, sheet_entry_KEY | An index by sheet_entry_type_KEY, staff_KEY and sheet_entry_KEY. dp_Sheet_Entry_get_by_staff_KEY_with_open_timers uses this index when finding Sheet_Entry records of type Timer Entry for a given staff. sheet_entry_KEY is explicitly a key value since it will be present in the row locator anyway. | |
IX_Sheet_Entry$staff_billing_rate_KEY | staff_billing_rate_KEY | Index supports foreign key FK_Sheet_Entry__Staff_Billing_Rate__staff_billing_rate_KEY | |
IX_Sheet_Entry$staff_KEY$sheet_entry_type_KEY$sheet_date@activity__activity_method_KEY@units | staff_KEY, sheet_entry_type_KEY, sheet_date, activity__activity_method_KEY, units | This index includes columns to allow the sheet entry summary used in the calendar control to be very fast. | |
IX_Sheet_Entry$task__schedule_item_KEY@activity__activity_method_KEY@amount@posted__staff_KEY@units | task__schedule_item_KEY, activity__activity_method_KEY, amount, posted__staff_KEY, units | Inserting rows in the Sheet_Entry table triggers an update of the cached amounts on Schedule_Item, and needs to query all the Sheet_Entry rows for the same schedule_item_KEY. | |
IX_Sheet_Entry$task_KEY$project_KEY | task_KEY, project_KEY | ||
IX_Sheet_Entry$task_KEY$task__schedule_item_KEY | task_KEY, task__schedule_item_KEY | Index supports foreign key FK_Sheet_Entry__Task__task_KEY__task__schedule_item_KEY | |
IX_Sheet_Entry$task_KEY@activity_KEY@amount@posted__staff_KEY@units | task_KEY, activity_KEY, amount, posted__staff_KEY, units | Covers the columns needed to compute the actual hours and amount on Task_View. |
Incoming foreign keys | |||
Name | Columns | Referencing table | Description |
FK_Billing_Decision__Sheet_Entry__sheet_entry_KEY | sheet_entry_KEY | Billing_Decision | |
FK_Sheet_Entry_Open_Value_Cache__Sheet_Entry__sheet_entry_KEY | sheet_entry_KEY | Sheet_Entry_Open_Value_Cache | on delete cascade; |
FK_Timer__Sheet_Entry__sheet_entry_KEY | sheet_entry_KEY | Timer | on delete cascade; |