Table: Activity
Columns
NameTypeConstraintsDescription
activity_KEYintNOT NULLAUTO-INCREMENT: Database assigned primary key for this table
update__staff_KEYintNOT NULL
update_date_utcdatetimeNOT NULL
activity_idnvarchar (11)NOT NULLUser defined identifier (unique)
descriptionnvarchar (250)NOT NULLUser defined description.
long_descriptionnvarchar (-1)NOT NULLLong user defined description
activity_category_KEYintKEY of the Activity Category that this row is assigned.
activity_class_KEYintNOT NULLKEY of the Activity Class that this row is assigned
activity_status_KEYintNOT NULLKEY of the Activity Status that this row is assigned
billing_rate_type_KEYintKEY of the Billing Rate Type that this row is assigned (required when rate_type_KEY=2).
unit_pricedecimal (9, 4)NOT NULLPrice per unit for expense, price per hour for time when rate_type_KEY=3 or 0.0000 otherwise.
unit_costdecimal (9, 4)NOT NULLCost per unit for expense, 0.0000 for time.
round_extensionbitNOT NULLUsed to determine if WIP will be rounded to the nearest dollar when it is calculated
charge_sales_taxbitNOT NULLUsed to determine if Sales Tax should be calculated
charge_service_taxbitNOT NULLUsed to determine if Service Tax should be calulated
gl_accountnvarchar (60)NOT NULLDefines the GL Account to which this activity will be exported.
rate_type_KEYintNOT NULL1=No Rate, 2=Staff Rate, 3=Fixed Rate (Always 3 for expense)
activity_method_KEYintNOT NULL1=Time, 2=Expense
activity_surcharge_method_KEYintNOT NULL1=No surcharge, 2=Per Unit, 3=Percent
surcharge_ratedecimal (9, 4)NOT NULLAmount to charge if surcharge_method_KEY=1; Percent to charge if surcharge_method_KEY=2
create_date_utcdatetimeNOT NULL
activity_id_sortablevarchar (48)NOT NULLA representation of the activity_id column that sorts in natural order, meaning that digits are sorted as numbers while non-digits are sorted alphabetically. A trigger keeps this field synchronized with the content of activity_id.
administrative_activity_type_KEYintKEY of the Administrative Activity Type that this row is assigned.
Primary key
NameColumnsDescription
PK_Activity$activity_KEYactivity_KEYCLUSTERED
Unique constraints
NameColumnsDescription
UK_Activity$activity_idactivity_idEach id must be unique.
UK_Activity$activity_id_sortableactivity_id_sortableAn index by the sortable ID is desired, and the algorithm ends up producing unique values.
UK_Activity$activity_KEY$activity_class_KEY$activity_method_KEYactivity_KEY, activity_class_KEY, activity_method_KEYSupports the relationship between these three columns in Sheet_Entry.
Indexes
NameColumnsDescription
IX_Activity$activity_category_KEYactivity_category_KEYIndex supports foreign key FK_Activity__Activity_Category__activity_category_KEY
IX_Activity$activity_class_KEYactivity_class_KEY
IX_Activity$activity_method_KEYactivity_method_KEYIndex supports foreign key FK_Activity__Activity_Method__activity_method_KEY
IX_Activity$activity_status_KEYactivity_status_KEYIndex supports foreign key FK_Activity__Activity_Status__activity_status_KEY
IX_Activity$activity_surcharge_method_KEYactivity_surcharge_method_KEYIndex supports foreign key FK_Activity__Activity_Surcharge_Method__activity_surcharge_method_KEY
IX_Activity$administrative_activity_type_KEYadministrative_activity_type_KEYIndex supports foreign key FK_Activity__Administrative_Activity_Type__administrative_activity_type_KEY
IX_Activity$billing_rate_type_KEYbilling_rate_type_KEYIndex supports foreign key FK_Activity__Billing_Rate_Type__billing_rate_type_KEY
IX_Activity$rate_type_KEYrate_type_KEYIndex supports foreign key FK_Activity__Rate_Type__rate_type_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Activity__Activity_Category__activity_category_KEYactivity_category_KEYActivity_Category
FK_Activity__Billing_Rate_Type__billing_rate_type_KEYbilling_rate_type_KEYBilling_Rate_Type
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Accruable_Benefit__Activity__activity_KEYactivity_KEYAccruable_Benefit
FK_Engagement__Activity__recurring_bill__activity_KEYrecurring_bill__activity_KEYEngagement
FK_Task_Template__Activity__activity_KEYactivity_KEYTask_Template
FK_User_Preference__Activity__entry_expense__activity_KEYentry_expense__activity_KEYUser_Preference
FK_User_Preference__Activity__entry_time__activity_KEYentry_time__activity_KEYUser_Preference
FK_Schedule_Item__Activity__activity_KEYactivity_KEYSchedule_Item
FK_Sheet_Entry__Activity__activity_KEYactivity_KEYSheet_Entry
FK_Sheet_Entry__Activity__activity_KEY__activity__activity_class_KEY__activity__activity_method_KEYactivity_KEY, activity__activity_class_KEY, activity__activity_method_KEYSheet_Entry

Schema diagram