Table: Engagement
Columns
NameTypeConstraintsDescription
engagement_KEYintNOT NULLAssigned by COMMON_dp_Allocate_Unique_Identifiers. Database assigned primary key for this table
update__staff_KEYintNOT NULL
client_KEYintWhen NULL the record is an Engagement Template, otherwise the record is an Engagement that belongs to a single Client record.
engagement_template_KEYintNOT NULLKey to a Engagement Template record.
descriptionnvarchar (250)NOT NULLUser defined description.
long_descriptionnvarchar (-1)NOT NULLLong user defined description
engagement_type_KEYintKey to a Engagement Type record.
engagement_status_KEYintNOT NULLKey to a Engagement Status record.
office_KEYintKey to an Office record.
biller__staff_KEYintKey to a Staff record.
reviewer__staff_KEYintKey to a Staff record.
manager__staff_KEYintKey to a Staff record.
rate_type_KEYintNOT NULLKey to a Rate Type record. (1=No Rate, 2=Staff Rate, 3=Fixed Rate)
billing_rate_type_KEYintKey to a Billing Rate Type record (required when rate_type_KEY=2).
fixed_ratedecimal (9, 2)NOT NULLRate to use when rate_type_KEY=3
markup_factordecimal (6, 4)NOT NULLMultiplied by the extension amount so work can be marked up or down.
tax_area_KEYintKey to a Tax Area record.
recurring_bill_KEYintKey to a Recurring Bill record.
recurring_bill_method_KEYintKey to a Recurring Bill Method record (required if and only if recurring_bill_KEY is not null).
recurring_bill_amountdecimal (9, 2)NOT NULLThe user will need to enter an amount if the engagement is going to be setup for recurring billing.
recurring_bill__staff_KEYintKey to a Staff record to be used for progress method recurring bill (required if and only if recurring_bill_method_KEY=1).
recurring_bill__activity_KEYintKey to an Activity record to be used for a progress method recurring bill (required if and only if recurring_bill_method_KEY=1).
gl_accountnvarchar (60)NOT NULLDefines the GL Account to which this engagement will be exported.
engagement_surcharge_method_KEYintNOT NULLKey to an Engagement Surcharge Method record. (1=No surcharge, 2=Based on Activity, 3=Flat amount)
surcharge_amountdecimal (9, 2)NOT NULLRate to use when surcharge_type_KEY = 3
create__changeset_KEYintNOT NULL
update__changeset_KEYintNOT NULL
Primary key
NameColumnsDescription
PK_Engagement$engagement_KEYengagement_KEYCLUSTERED
Unique constraints
NameColumnsDescription
UK_Engagement$client_KEY$engagement_template_KEYclient_KEY, engagement_template_KEY
UK_Engagement$engagement_KEY$client_KEYengagement_KEY, client_KEYThis unique constraint supports the fact that if an Engagement is assigned to a Sheet Entry the Engagement must be for the same Client as the Client assigned to the Sheet Entry.
Indexes
NameColumnsDescription
IX_Engagement$biller__staff_KEYbiller__staff_KEYIndex supports foreign key FK_Engagement__Staff__biller__staff_KEY
IX_Engagement$billing_rate_type_KEYbilling_rate_type_KEYIndex supports foreign key FK_Engagement__Billing_Rate_Type__billing_rate_type_KEY
IX_Engagement$engagement_status_KEYengagement_status_KEYIndex supports foreign key FK_Engagement__Engagement_Status__engagement_status_KEY
IX_Engagement$engagement_surcharge_method_KEYengagement_surcharge_method_KEYIndex supports foreign key FK_Engagement__Engagement_Surcharge_Method__engagement_surcharge_method_KEY
IX_Engagement$engagement_template_KEYengagement_template_KEYIndex supports foreign key FK_Engagement__Engagement_Template__engagement_template_KEY
IX_Engagement$engagement_type_KEYengagement_type_KEYIndex supports foreign key FK_Engagement__Engagement_Type__engagement_type_KEY
IX_Engagement$manager__staff_KEYmanager__staff_KEYIndex supports foreign key FK_Engagement__Staff__manager__staff_KEY
IX_Engagement$office_KEYoffice_KEYIndex supports foreign key FK_Engagement__Office__office_KEY
IX_Engagement$rate_type_KEYrate_type_KEYIndex supports foreign key FK_Engagement__Rate_Type__rate_type_KEY
IX_Engagement$recurring_bill__activity_KEYrecurring_bill__activity_KEYIndex supports foreign key FK_Engagement__Activity__recurring_bill__activity_KEY
IX_Engagement$recurring_bill__staff_KEYrecurring_bill__staff_KEYModifying Staff checks Engagements by recurring_bill__staff_KEY
IX_Engagement$recurring_bill_KEYrecurring_bill_KEYIndex supports foreign key FK_Engagement__Recurring_Bill__recurring_bill_KEY
IX_Engagement$recurring_bill_method_KEYrecurring_bill_method_KEYIndex supports foreign key FK_Engagement__Recurring_Bill_Method__recurring_bill_method_KEY
IX_Engagement$reviewer__staff_KEYreviewer__staff_KEYIndex supports foreign key FK_Engagement__Staff__reviewer__staff_KEY
IX_Engagement$tax_area_KEYtax_area_KEYIndex supports foreign key FK_Engagement__Tax_Area__tax_area_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Engagement__Activity__recurring_bill__activity_KEYrecurring_bill__activity_KEYActivity
FK_Engagement__Billing_Rate_Type__billing_rate_type_KEYbilling_rate_type_KEYBilling_Rate_Type
FK_Engagement__Client__client_KEYclient_KEYClient
FK_Engagement__Engagement_Template__engagement_template_KEYengagement_template_KEYEngagement_Template
FK_Engagement__Engagement_Type__engagement_type_KEYengagement_type_KEYEngagement_Type
FK_Engagement__Office__office_KEYoffice_KEYOffice
FK_Engagement__Recurring_Bill__recurring_bill_KEYrecurring_bill_KEYRecurring_Bill
FK_Engagement__Staff__biller__staff_KEYbiller__staff_KEYStaff
FK_Engagement__Staff__manager__staff_KEYmanager__staff_KEYStaff
FK_Engagement__Staff__recurring_bill__staff_KEYrecurring_bill__staff_KEYStaff
FK_Engagement__Staff__reviewer__staff_KEYreviewer__staff_KEYStaff
FK_Engagement__Tax_Area__tax_area_KEYtax_area_KEYTax_Area
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Schedule_Item__Engagement__engagement_KEYengagement_KEYSchedule_Item
FK_Sheet_Entry__Engagement__engagement_KEYengagement_KEYSheet_Entry

Schema diagram