Table: Client | |||
Columns | |||
Name | Type | Constraints | Description |
client_KEY | int | NOT NULL | Assigned by COMMON_dp_Allocate_Unique_Identifiers. Database assigned primary key for this table |
update__staff_KEY | int | NOT NULL | |
client_id | nvarchar (11) | NOT NULL | User defined identifier (unique) |
description | nvarchar (250) | NOT NULL | User defined description. |
client_class_KEY | int | NOT NULL | Key to a Client Class record. |
name_1 | nvarchar (250) | NOT NULL | Name of company if the client type is a non-individual, otherwise it is the display name for the client. |
name_2 | nvarchar (250) | NOT NULL | Only shown on the UI for non-individual clients |
federal_tin | nvarchar (11) | NOT NULL | This field will hold the Fed Tax ID if the client is a non-individual, it will hold the taxpayer SSN for individual clients. |
individual_first_name | nvarchar (100) | NOT NULL | First name of the client if the client is an individual. |
individual_last_name | nvarchar (100) | NOT NULL | Last name of the client if the client is an individual. |
spouse_first_name | nvarchar (100) | NOT NULL | First name of the clients spouse if the client type is an individual. |
spouse_last_name | nvarchar (100) | NOT NULL | Last name of the clients spouse if the client type is an individual. |
client_status_KEY | int | NOT NULL | Key to a Client Status record. |
office_KEY | int | Key to an Office record. | |
entity_KEY | int | Key to an Entity record. | |
manager__staff_KEY | int | Key to a Staff record. | |
associate__staff_KEY | int | Key to a Staff record. | |
industry_code | nvarchar (6) | NOT NULL | Industry code. |
fiscal_year_end_month | int | NOT NULL | End month of the client's fiscal year end. |
date_opened | smalldatetime | Date the client came to your firm, default system date when a new client is added. | |
date_left | smalldatetime | Date your firm terminated the client/firm relationship. | |
comments | nvarchar (-1) | NOT NULL | Any comments about the Client. |
principal__client_KEY | int | NOT NULL | This should be a drop list of all clients, it is required if Bill To Principal is selected. |
assess_service_charge | bit | NOT NULL | Default should be set to whatever is set in firm setup. |
service_charge_KEY | int | Key to a Service Charge record. | |
print_statements | bit | NOT NULL | Determines whether statements are printed for this client. |
use_wip_limit | bit | NOT NULL | Determines whether the WIP limit is enforced. |
wip_limit | decimal (17, 2) | NOT NULL | WIP limit. |
use_accounts_receivable_limit | bit | NOT NULL | Determines whether the accounts receivable limit is enforced. |
accounts_receivable_limit | decimal (17, 2) | NOT NULL | Accounts Receivable limit. |
contact_KEY | int | NOT NULL | Key to the primary Contact record for this Client. |
days_to_due_date | int | NOT NULL | Number of days to be used when calculating a due date in billing. |
billing_instructions | nvarchar (-1) | NOT NULL | Any notes for the biller. |
invoice__report_definition_KEY | int | NOT NULL | Key to an Invoice Report Definition record. |
statement__report_definition_KEY | int | NOT NULL | Key to an Statement Report Definition record. |
referral_type_KEY | int | Key to a Referral Type record. | |
referred_by__client_KEY | int | Key to a Client record. | |
referred_by__contact_KEY | int | Key to a Contact record. | |
referred_by__referral_source_KEY | int | Key to a Referral Source record. | |
referred_by__staff_KEY | int | Key to a Staff record. | |
won_date | smalldatetime | The date this client was won. | |
won_reason_KEY | int | Key to a Won Reason record. | |
lost_reason_KEY | int | Key to a Lost Reason record. | |
lost_to_KEY | int | Key to a Lost To record. | |
client_id_sortable | varchar (48) | NOT NULL | A representation of the client_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 client_id. |
client_guid | uniqueidentifier | NOT NULL | A unique id for this row generated by the database. |
client_note_data | nvarchar (-1) | NOT NULL | Data used by the Client Note control. |
spouse_federal_tin | nvarchar (11) | NOT NULL | This field will hold the SSN for an individual client's spouse. |
billing__contact_KEY | int | NOT NULL | Primary contact for invoice and statement delivery. By default will be the contact key of the client |
billing_email_recipients | nvarchar (500) | NOT NULL | Any additional email address to whom the billing information should be emailed |
create__changeset_KEY | int | NOT NULL | |
update__changeset_KEY | int | NOT NULL | |
preferred__contact_KEY | int | NOT NULL | |
complexity | int | ||
complexity_event_date_utc | datetime | ||
complexity_product_year | int | ||
digita_out_of_date | bit | NOT NULL | Flag that is set when changes are detected to the entity that have not been applied to this database. |
individual_date_of_birth | smalldatetime | Date of birth of the client if the client is an individual | |
spouse_date_of_birth | smalldatetime | Date of birth for the spouse of the client if the client is an individual |
Primary key | |||
Name | Columns | Description | |
PK_Client$client_KEY | client_KEY | CLUSTERED | |
Unique constraints | |||
Name | Columns | Description | |
UK_Client$client_guid | client_guid | ||
UK_Client$client_id | client_id | Each id must be unique. | |
UK_Client$client_id_sortable | client_id_sortable | An index by the sortable ID is desired, and the algorithm ends up producing unique values. | |
UK_Client$contact_KEY | contact_KEY | Each Client must have a different primary Contact. | |
Indexes | |||
Name | Columns | Description | |
IX_Client$associate__staff_KEY | associate__staff_KEY | Index supports foreign key FK_Client__Staff__associate__staff_KEY | |
IX_Client$billing__contact_KEY@contact_KEY | billing__contact_KEY, contact_KEY | This index helps all lookups by billing__contact_KEY. There is a constraint that the billing contact must be one of the client's contacts and the included contact_KEY columns helps that query. | |
IX_Client$client_class_KEY$client_KEY@associate__staff_KEY@client_id@description@manager__staff_KEY@office_KEY | client_class_KEY, client_KEY, associate__staff_KEY, client_id, description, manager__staff_KEY, office_KEY | ||
IX_Client$client_status_KEY | client_status_KEY | Index supports foreign key FK_Client__Client_Status__client_status_KEY | |
IX_Client$entity_KEY | entity_KEY | Index supports foreign key FK_Client__Entity__entity_KEY | |
IX_Client$invoice__report_definition_KEY | invoice__report_definition_KEY | Index supports foreign key FK_Client__Report_Definition__invoice__report_definition_KEY | |
IX_Client$lost_reason_KEY | lost_reason_KEY | Index supports foreign key FK_Client__Lost_Reason__lost_reason_KEY | |
IX_Client$lost_to_KEY | lost_to_KEY | Index supports foreign key FK_Client__Lost_To__lost_to_KEY | |
IX_Client$manager__staff_KEY | manager__staff_KEY | Index supports foreign key FK_Client__Staff__manager__staff_KEY | |
IX_Client$office_KEY | office_KEY | Index supports foreign key FK_Client__Office__office_KEY | |
IX_Client$preferred__contact_KEY@contact_KEY | preferred__contact_KEY, contact_KEY | This index helps all lookups by preferred__contact_KEY. There is a constraint that the preferred contact must be one of the client's contacts and the included contact_KEY columns helps that query. | |
IX_Client$principal__client_KEY | principal__client_KEY | ||
IX_Client$referral_type_KEY | referral_type_KEY | Index supports foreign key FK_Client__Referral_Type__referral_type_KEY | |
IX_Client$referred_by__client_KEY | referred_by__client_KEY | Index supports foreign key FK_Client__Client__referred_by__client_KEY | |
IX_Client$referred_by__contact_KEY | referred_by__contact_KEY | Index supports foreign key FK_Client__Contact__referred_by__contact_KEY | |
IX_Client$referred_by__referral_source_KEY | referred_by__referral_source_KEY | Index supports foreign key FK_Client__Referral_Source__referred_by__referral_source_KEY | |
IX_Client$referred_by__staff_KEY | referred_by__staff_KEY | Index supports foreign key FK_Client__Staff__referred_by__staff_KEY | |
IX_Client$service_charge_KEY | service_charge_KEY | Index supports foreign key FK_Client__Service_Charge__service_charge_KEY | |
IX_Client$statement__report_definition_KEY | statement__report_definition_KEY | Index supports foreign key FK_Client__Report_Definition__statement__report_definition_KEY | |
IX_Client$won_reason_KEY | won_reason_KEY | Index supports foreign key FK_Client__Won_Reason__won_reason_KEY |