Table: Sheet_Entry
Columns
NameTypeConstraintsDescription
sheet_entry_KEYintNOT NULLAssigned by COMMON_dp_Allocate_Unique_Identifiers. Database assigned primary key for this table.
row_versiontimestampNOT NULL
update__staff_KEYintNOT NULL
client_KEYintKey to the Client that this entry is for. The client must be the client of the Engagement.
engagement_KEYintKey to the Engagement this entry is for.
activity_KEYintKey to the Activity this entry is for.
entry_datesmalldatetimeDate this entry is for.
staff_billing_rate_KEYintKey to the Staff Billing Rate used to calculate the amount.
unitsdecimal (9, 2)Quantity of units (or hours).
unit_costdecimal (9, 4)Cost per unit (or hour) at the time the amount was computed.
unit_pricedecimal (9, 4)Price per unit (or hour) at the time the amount was computed. Storage is required because amount can be rounded.
amountdecimal (9, 2)Overridden extended price after computation and rounding.
commentnvarchar (-1)NOT NULLAn optional comment.
biller_notenvarchar (-1)NOT NULLAn optional comment for the biller.
sheet_entry_type_KEYintNOT NULL1=Standard
2=Progress Billing
3=Billing Adjustment
4=Timer Entry
5=Remote Standard
6=Exported Remote Standard
staff_KEYintStaff this Sheet Entry is for.
sheet_datesmalldatetimeDate of the Sheet, also known as control date.
approved_datesmalldatetimeDate this item was approved.
approved__staff_KEYintKey of the Staff who approved this item.
posted_datesmalldatetimeDate this item was posted.
posted__staff_KEYintKey of the Staff who posted this item.
project_KEYintOptionally links this Sheet Entry to a Project.
task_KEYintOptionally links this Sheet Entry to a Task.
calculated_amountdecimal (9, 2)Calculated extended price after computation and rounding.
amount_is_overriddenbitNOT NULLTrue 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_KEYintOptionally links this Sheet Entry to a [Education_Course] row.
create__changeset_KEYintNOT NULL
update__changeset_KEYintNOT NULL
completebitNOT NULL
integration_application_KEYintNOT NULLKey to the Integration Application table to denote the source application for this entry.
activity__activity_class_KEYintCached value of the activity class:
1=Billable
2=Non-billable
3=Administrative
activity__activity_method_KEYintCached value of the activity method:
1=Time
2=Expense
project__schedule_item_KEYintCached value of the schedule_item_KEY for the associated Project.
task__schedule_item_KEYintCached value of the schedule_item_KEY for the associated Task.
Primary key
NameColumnsDescription
PK_Sheet_Entry$sheet_entry_KEYsheet_entry_KEYCLUSTERED
Indexes
NameColumnsDescription
IX_Sheet_Entry$activity_KEY$activity__activity_class_KEY$activity__activity_method_KEYactivity_KEY, activity__activity_class_KEY, activity__activity_method_KEYIndex supports foreign key FK_Sheet_Entry__Activity__activity_KEY__activity__activity_class_KEY__activity__activity_method_KEY
IX_Sheet_Entry$activity_KEY$sheet_entry_KEYactivity_KEY, sheet_entry_KEYAn 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_KEYapproved__staff_KEYIndex supports foreign key FK_Sheet_Entry__Staff__approved__staff_KEY
IX_Sheet_Entry$client_KEYclient_KEY
IX_Sheet_Entry$client_KEY$activity_KEY$entry_date$posted__staff_KEY@amount@sheet_entry_KEY@sheet_entry_type_KEY@unitsclient_KEY, activity_KEY, entry_date, posted__staff_KEY, amount, sheet_entry_KEY, sheet_entry_type_KEY, unitsAn 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_KEYclient_KEY, activity_KEY, sheet_entry_type_KEY, sheet_entry_KEYAn 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_KEYeducation_course__schedule_item_KEYIndex 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_KEYengagement_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@unitsengagement_KEY, project_KEY, task_KEY, posted__staff_KEY, activity_KEY, amount, unitsAn 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_KEYentry_date, activity_KEY, sheet_entry_KEY, sheet_entry_type_KEY, engagement_KEY, amount, posted__staff_KEY
IX_Sheet_Entry$entry_date$engagement_KEYentry_date, engagement_KEYAn index by entry_date that covers engagement_KEY.
IX_Sheet_Entry$integration_application_KEYintegration_application_KEYIndex supports foreign key FK_Sheet_Entry__Integration_Application__integration_application_KEY
IX_Sheet_Entry$posted__staff_KEYposted__staff_KEYIndex 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@unitsproject__schedule_item_KEY, activity__activity_method_KEY, amount, posted__staff_KEY, unitsInserting 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_KEYproject_KEY
IX_Sheet_Entry$project_KEY$project__schedule_item_KEYproject_KEY, project__schedule_item_KEYIndex 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@unitssheet_entry_KEY, client_KEY, activity_KEY, entry_date, posted__staff_KEY, amount, sheet_entry_type_KEY, unitsAn 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_KEYsheet_entry_KEY, client_KEY, engagement_KEY, project_KEYAn 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_KEYsheet_entry_type_KEY, staff_KEY, sheet_entry_KEYAn 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_KEYstaff_billing_rate_KEYIndex 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@unitsstaff_KEY, sheet_entry_type_KEY, sheet_date, activity__activity_method_KEY, unitsThis 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@unitstask__schedule_item_KEY, activity__activity_method_KEY, amount, posted__staff_KEY, unitsInserting 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_KEYtask_KEY, project_KEY
IX_Sheet_Entry$task_KEY$task__schedule_item_KEYtask_KEY, task__schedule_item_KEYIndex supports foreign key FK_Sheet_Entry__Task__task_KEY__task__schedule_item_KEY
IX_Sheet_Entry$task_KEY@activity_KEY@amount@posted__staff_KEY@unitstask_KEY, activity_KEY, amount, posted__staff_KEY, unitsCovers the columns needed to compute the actual hours and amount on Task_View.
Foreign keys
NameColumnsReferenced tableDescription
FK_Sheet_Entry__Activity__activity_KEYactivity_KEYActivity
FK_Sheet_Entry__Activity__activity_KEY__activity__activity_class_KEY__activity__activity_method_KEYactivity_KEY, activity__activity_class_KEY, activity__activity_method_KEYActivity
FK_Sheet_Entry__Client__client_KEYclient_KEYClient
FK_Sheet_Entry__Engagement__engagement_KEYengagement_KEYEngagement
FK_Sheet_Entry__Integration_Application__integration_application_KEYintegration_application_KEYIntegration_Application
FK_Sheet_Entry__Project__project_KEYproject_KEYProject
FK_Sheet_Entry__Project__project_KEY__project__schedule_item_KEYproject_KEY, project__schedule_item_KEYProject
FK_Sheet_Entry__Schedule_Item__education_course__schedule_item_KEYeducation_course__schedule_item_KEYSchedule_Item
FK_Sheet_Entry__Staff__approved__staff_KEYapproved__staff_KEYStaff
FK_Sheet_Entry__Staff__posted__staff_KEYposted__staff_KEYStaff
FK_Sheet_Entry__Staff__staff_KEYstaff_KEYStaff
FK_Sheet_Entry__Staff_Billing_Rate__staff_billing_rate_KEYstaff_billing_rate_KEYStaff_Billing_Rate
FK_Sheet_Entry__Task__task_KEYtask_KEYTask
FK_Sheet_Entry__Task__task_KEY__project_KEYtask_KEY, project_KEYTask
FK_Sheet_Entry__Task__task_KEY__task__schedule_item_KEYtask_KEY, task__schedule_item_KEYTask
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Billing_Decision__Sheet_Entry__sheet_entry_KEYsheet_entry_KEYBilling_Decision
FK_Sheet_Entry_Open_Value_Cache__Sheet_Entry__sheet_entry_KEYsheet_entry_KEYSheet_Entry_Open_Value_Cacheon delete cascade;
FK_Timer__Sheet_Entry__sheet_entry_KEYsheet_entry_KEYTimeron delete cascade;

Schema diagram