Table: dbo.Checkbook
Table which holds data related to clients bank accounts.
Columns
Name | Type | Constraints | Description
checkbook_KEY | int (4) | NOT NULL | Primary key for this table.
client_KEY | int (4) | NOT NULL | Key of client that owns the checkbook.
description | nvarchar (100) | NOT NULL | Checkbook description.
bank_KEY | int (4) |  | Key of Bank account is tied to.
bank_account_type_KEY | nvarchar (22) | NOT NULL | Type of bank account.
bank_account_number | nvarchar (34) | NOT NULL | Bank account the checkbook draws from.
next_check_number | int (4) | NOT NULL | Next check number.
next_direct_deposit_voucher_id | nvarchar (30) | NOT NULL | ID of next direct deposit voucher.
use_separate_direct_deposit_voucher_id | bit (1) | NOT NULL | Flag which indicates that direct deposit check serial numbers should be drawn from the next_direct_deposit_voucher_id field instead of the next_check_number field
bank_reconciliation_reference_number | nvarchar (30) | NOT NULL | Reference number to use for bank reconciliation.
payroll_layout__report_definition_KEY | int (4) |  | Key of report definition to use when printing payroll checks from this checkbook.
vendor_layout__report_definition_KEY | int (4) |  | Key of report definition to use when printing vendor checks from this checkbook.
logo__report_image_KEY | int (4) |  | Bank logo image
signature_1__report_image_KEY | int (4) |  | First signature image
signature_2__report_image_KEY | int (4) |  | Second signature image
date_format_KEY | int (4) |  | Date format to use on printed checks.
bank_account_direct_deposit_status_KEY | nvarchar (16) | NOT NULL | FK the direct deposit prenote status
number_of_digits_allowed_for_micr_auxiliary_onus | tinyint (1) | NOT NULL | Number of digits allowed for the MICR auxiliary on-us field
starting_position_of_micr_onus | tinyint (1) | NOT NULL | Starting position for printing the MICR on-us field
use_beginning_micr_onus_symbol | bit (1) | NOT NULL | Flag which indicates if the leading MICR symbol is printed for the on-us field
funding_effective_date_selection | tinyint (1) | NOT NULL | Firm funding effective date selection. 1== CheckDate 2 == SystemDate
funding_effective_date_offset | int (4) | NOT NULL | Firm funding effective date banking days offset
fund_payroll_transaction | bit (1) | NOT NULL | Flag which indicates that this bank account will automatically generate impound funding transactions for payroll checks
fund_tax_agent_transaction | bit (1) | NOT NULL | Flag which indicates that this bank account will automatically generate impound funding transactions for payroll tax agent checks
fund_firm_vendor_transaction | bit (1) | NOT NULL | Flag which indicates that this bank account will automatically generate impound funding transactions for vendor checks
fund_payroll_agent_transaction | bit (1) | NOT NULL | Flag which indicates that this bank account will automatically generate impound funding transactions for payroll agent checks
is_account_inactive | bit (1) | NOT NULL | Is account inactive flag.
gl_account_KEY | int (4) |  | Key to the gl_account the checkbook draws from.
bank_reconciliation_frequency_type_KEY | int (4) | NOT NULL | Reconciliation frequency time period
bank_reconciliation_finalized_available_to_edit | int (4) | NOT NULL | The value of this column indicates how many prior finalized bank reconciliation statements are eligible to be opened back up and edited by the user. The maximum number allowed is based on the reconciliation frequency as follows: one years worth for Daily and Weekly and two years worth for Monthly, Quarterly, Semiannually, and Annually.
tax_agent_funding_liability__gl_account_KEY | int (4) |  | The GL Account for the tax agents funding liability.
payroll_funding_liability__gl_account_KEY | int (4) |  | The GL Account for the payroll funding liability.
payroll_agent_funding_liability__gl_account_KEY | int (4) |  | The GL Account for the payroll agents funding liability.
account_holder_name | nvarchar (100) | NOT NULL | Contains the name of the account holder
bank_reconciliation__journal_KEY | int (4) | NOT NULL | The key of the journal used in bank reconciliation when writing transactions with a General Ledger component, such as adjustments.
positive_pay_start_date | smalldatetime (4) |  | Start date for valid checks to include in the process positive pay view. Null means select all transactions.
positive_pay__export_text_file_format_KEY | int (4) |  | Export text file format to use to create positive pay files for this checkbook. Null means this checkbook is not set up for Positive Pay.
micr_line_format_KEY | int (4) | NOT NULL | Type of format selected for MICR line (Standard, Custom).
custom_micr_section_1 | nvarchar (40) | NOT NULL | First section of the custom MICR line (can be Aux on-us, transit or on-us).
custom_micr_section_2 | nvarchar (24) | NOT NULL | Second section of the custom MICR line (can be Aux on-us, transit or on-us).
custom_micr_section_3 | nvarchar (36) | NOT NULL | Third section of the custom MICR line (can be Aux on-us, transit or on-us).
form_adjustment_write_off_threshold | decimal (9, 2) | NOT NULL | Impound only: Threshold amount for form adjustement write off.
form_adjustment_write_off__gl_account_KEY | int (4) |  | Impound only: The GL account for form adjustment write off. NULL indicates no account has been selected.
impounding__gl_account_KEY | int (4) |  | ACS-specific field for allowing a Bank Account to select an Impounding holding GL account. A null value indicates that an account was not selected.
payroll_check_printing_primary_sort_by_type_KEY | int (4) | NOT NULL | Reference to primary sort by type for printing payroll checks
employee_sort_by_type_KEY | int (4) |  | Reference to secondary sort by type for printing payroll checks
payroll_check_printing_primary__sort_order_type_KEY | int (4) | NOT NULL | Reference to primary sort order for printing payroll checks
payroll_check_printing_secondary__sort_order_type_KEY | int (4) | NOT NULL | Reference to secondary sort order for printing payroll checks
vendor_payment_primary_sort_by_type_KEY | int (4) | NOT NULL | Reference to primary sort by type for printing vendor checks
vendor_payment_primary__sort_order_type_KEY | int (4) | NOT NULL | Reference to primary sort order for printing vendor checks
fund_only_printed_payroll_checks | bit (1) | NOT NULL | Flag indicating funding only printed payroll checks.
fund_only_printed_payroll_agent_checks | bit (1) | NOT NULL | Flag indicating funding only printed payroll agent checks.
fund_only_printed_tax_agent_checks | bit (1) | NOT NULL | Flag indicating funding only printed tax agent checks.
ap_layout__report_definition_KEY | int (4) |  | Key of report definition to use when printing AP checks from this checkbook. Null means no AP layout was selected for this checkbook.
is_online_bill_pay_bank_account | bit (1) | NOT NULL |
fund_payroll_transaction_type_KEY | int (4) | NOT NULL | Contains a key to the type of fund payroll transaction selected. This is a modifier for the behavior specified by the fund payroll transactions field.
export__quick_books_account_KEY | int (4) |  | A foreign key to the Quick_Books_Account table, this is the quickbooks side of the mapping during the Acs to Quickbooks export data mapping process. This will be used to map Accounting CS checkbooks to QuickBooks GL accounts of type bank. This key will be null if no export mapping has been done.
checkbook_guid | uniqueidentifier (16) | NOT NULL | A unique identifer for each checkbook row.
prompt_for_next_check_number | bit (1) | NOT NULL | Indicates if we should prompt the user for the next check number when printing checks.
starting_position_of_micr_onus_deposit_slip | tinyint (1) | NOT NULL | Starting position for printing the MICR on-us deposit slip field. Minimum value for starting position is 28, maximum is 32 and the default value is 31, this default ["BR"] value 31 is common for majority bank accounts
custom_micr_section_1_deposit_slip | nvarchar (40) | NOT NULL | First section of the custom MICR line for deposit slip(can be Aux on-us, transit or on-us). If we choose custom option, user has to enter some value. We do not have ["BR"] any default and minimum length is 1, maximum length is 20 for this section
custom_micr_section_2_deposit_slip | nvarchar (24) | NOT NULL | Second section of the custom MICR line for deposit slip(can be Aux on-us, transit or on-us).If we choose custom option, user has to enter some value. We do not have ["BR"] any default and minimum length is 1, maximum length is 12 for this section
custom_micr_section_3_deposit_slip | nvarchar (36) | NOT NULL | Third section of the custom MICR line for deposit slip(can be Aux on-us, transit or on-us). If we choose custom option, user has to enter some value. We do not have ["BR"] any default value and minimum length is 1, maximum length is 18 for this section
deposit_slip__micr_line_format_KEY | int (4) | NOT NULL | Type of format selected for MICR line for deposit slip(Standard, Custom). Standard is by default because some banks use the same routing number for deposit slips ["BR"] as they do for checks and for Custom, if the routing number needs to be different for deposit slips, then they can set it to custom and manually enter the correct["BR"] information
use_beginning_micr_onus_symbol_deposit_slip | bit (1) | NOT NULL | Flag which indicates if the leading MICR symbol is printed for the on-us field for deposit slips
deposit_layout__report_definition_KEY | int (4) |  | Key of report definition to use when printing deposit slip from this checkbook.
vendor_payment_secondary__sort_order_type_KEY | int (4) | NOT NULL | It determines the sort order (ascending or descending) for secondary sorting.
vendor_payment_secondary_sort_by_type_KEY | int (4) |  | This stores the key for secondary sort order.
yodlee_financial_account_link_status_KEY | tinyint (1) | NOT NULL | The yodlee financial account link status.
allow_all_applicable_firm_staff_access | bit (1) | NOT NULL | Bit field that indicates whether or not all the standard firm staff having access to the parent client have access to this checkbook
allow_all_applicable_client_staff_access | bit (1) | NOT NULL | Bit field that indicates whether or not all the client staff having access to the parent client have access to this checkbook.
pre_numbered_check_stock | bit (1) | NOT NULL | Pre-numbered check stock option, when printing checks, we can use this option to know if multi-page checks["BR"] should work as they do today (0) or if multi-page checks should skip check numbers["BR"] based on the number of pages the check takes up (1).

Primary key
Name | Columns | Description
PK_Checkbook | checkbook_KEY |
Unique constraints
Name | Columns | Description
UK_Checkbook$checkbook_guid | checkbook_guid |
UK_Checkbook$client_KEY$description | client_KEY, description |

Foreign keys
Name | Columns | Referenced table | Description
FK_Checkbook__Bank__bank_KEY | bank_KEY | Bank (bank_KEY) |
FK_Checkbook__Bank_Account_Direct_Deposit_Status__bank_account_direct_deposit_status_KEY | bank_account_direct_deposit_status_KEY | Bank_Account_Direct_Deposit_Status (bank_account_direct_deposit_status_KEY) |
FK_Checkbook__Bank_Account_Type__bank_account_type_KEY | bank_account_type_KEY | Bank_Account_Type (bank_account_type_KEY) |
FK_Checkbook__Bank_Reconciliation_Frequency_Type__bank_reconciliation_frequency_type_KEY | bank_reconciliation_frequency_type_KEY | Bank_Reconciliation_Frequency_Type (bank_reconciliation_frequency_type_KEY) |
FK_Checkbook__Client__client_KEY | client_KEY | Client (client_KEY) |
FK_Checkbook__Date_Format__date_format_KEY | date_format_KEY | Date_Format (date_format_KEY) |
FK_Checkbook__Employee_Sort_By_Type__employee_sort_by_type_KEY | employee_sort_by_type_KEY | Employee_Sort_By_Type (employee_sort_by_type_KEY) |
FK_Checkbook__Export_Text_File_Format__positive_pay__export_text_file_format_KEY | positive_pay__export_text_file_format_KEY | Export_Text_File_Format (export_text_file_format_KEY) |
FK_Checkbook__Fund_Payroll_Transaction_Type__fund_payroll_transaction_type_KEY | fund_payroll_transaction_type_KEY | Fund_Payroll_Transaction_Type (fund_payroll_transaction_type_KEY) |
FK_Checkbook__GL_Account__form_adjustment_write_off__gl_account_KEY | form_adjustment_write_off__gl_account_KEY | GL_Account (gl_account_KEY) |
FK_Checkbook__GL_Account__gl_account_KEY | gl_account_KEY | GL_Account (gl_account_KEY) |
FK_Checkbook__GL_Account__impounding__gl_account_KEY | impounding__gl_account_KEY | GL_Account (gl_account_KEY) |
FK_Checkbook__GL_Account__payroll_agent_funding_liability__gl_account_KEY | payroll_agent_funding_liability__gl_account_KEY | GL_Account (gl_account_KEY) |
FK_Checkbook__GL_Account__payroll_funding_liability__gl_account_KEY | payroll_funding_liability__gl_account_KEY | GL_Account (gl_account_KEY) |
FK_Checkbook__GL_Account__tax_agent_funding_liability__gl_account_KEY | tax_agent_funding_liability__gl_account_KEY | GL_Account (gl_account_KEY) |
FK_Checkbook__Journal__bank_reconciliation__journal_KEY | bank_reconciliation__journal_KEY | Journal (journal_KEY) |
FK_Checkbook__Micr_Line_Format__micr_line_format_KEY | micr_line_format_KEY | Micr_Line_Format (micr_line_format_KEY) |
FK_Checkbook__Payroll_Check_Printing_Primary_Sort_By_Type__payroll_check_printing_primary_sort_by_type_KEY | payroll_check_printing_primary_sort_by_type_KEY | Payroll_Check_Printing_Primary_Sort_By_Type (payroll_check_printing_primary_sort_by_type_KEY) |
FK_Checkbook__Quick_Books_Account__export__quick_books_account_KEY | export__quick_books_account_KEY | Quick_Books_Account (quick_books_account_KEY) |
FK_Checkbook__Report_Definition__ap_layout__report_definition_KEY | ap_layout__report_definition_KEY | Report_Definition (report_definition_KEY) |
FK_Checkbook__Report_Definition__payroll_layout__report_definition_KEY | payroll_layout__report_definition_KEY | Report_Definition (report_definition_KEY) |
FK_Checkbook__Report_Definition__vendor_layout__report_definition_KEY | vendor_layout__report_definition_KEY | Report_Definition (report_definition_KEY) |
FK_Checkbook__Yodlee_Financial_Account_Link_Status__yodlee_financial_account_link_status_KEY | yodlee_financial_account_link_status_KEY | Yodlee_Financial_Account_Link_Status (yodlee_financial_account_link_status_KEY) |
FK_Checkbook__Report_Image__logo__report_image_KEY | logo__report_image_KEY | Report_Image (report_image_KEY) |
FK_Checkbook__Report_Image__signature_1__report_image_KEY | signature_1__report_image_KEY | Report_Image (report_image_KEY) |
FK_Checkbook__Report_Image__signature_2__report_image_KEY | signature_2__report_image_KEY | Report_Image (report_image_KEY) |
FK_Checkbook__Sort_Order_Type__payroll_check_printing_primary__sort_order_type_KEY | payroll_check_printing_primary__sort_order_type_KEY | Sort_Order_Type (sort_order_type_KEY) |
FK_Checkbook__Sort_Order_Type__payroll_check_printing_secondary__sort_order_type_KEY | payroll_check_printing_secondary__sort_order_type_KEY | Sort_Order_Type (sort_order_type_KEY) |
FK_Checkbook__Sort_Order_Type__vendor_payment_primary__sort_order_type_KEY | vendor_payment_primary__sort_order_type_KEY | Sort_Order_Type (sort_order_type_KEY) |
FK_Checkbook__Vendor_Payment_Primary_Sort_By_Type__vendor_payment_primary_sort_by_type_KEY | vendor_payment_primary_sort_by_type_KEY | Vendor_Payment_Primary_Sort_By_Type (vendor_payment_primary_sort_by_type_KEY) |
FK_Checkbook__Sort_Order_Type__vendor_payment_secondary__sort_order_type_KEY | vendor_payment_secondary__sort_order_type_KEY | Sort_Order_Type (sort_order_type_KEY) |
FK_Checkbook__Vendor_Payment_Secondary_Sort_By_Type__vendor_payment_secondary_sort_by_type_KEY | vendor_payment_secondary_sort_by_type_KEY | Vendor_Payment_Secondary_Sort_By_Type (vendor_payment_secondary_sort_by_type_KEY) |
FK_Checkbook__Report_Definition__deposit_layout__report_definition_KEY | deposit_layout__report_definition_KEY | Report_Definition (report_definition_KEY) |
FK_Checkbook__Micr_Line_Format__deposit_slip__micr_line_format_KEY | deposit_slip__micr_line_format_KEY | Micr_Line_Format (micr_line_format_KEY) |

Incoming foreign keys
Name | Columns | Referencing table | Description
FK_Client_Direct_Deposit__Checkbook__checkbook_KEY | checkbook_KEY | Client_Direct_Deposit |
FK_Bank_Reconciliation_Statement__Checkbook__checkbook_KEY | checkbook_KEY | Bank_Reconciliation_Statement |
FK_Netclient_File_Log__Checkbook__checkbook_KEY | checkbook_KEY | Netclient_File_Log |
FK_Pending_AP_Payment__Checkbook__checkbook_KEY | checkbook_KEY | Pending_AP_Payment |
FK_OnBalance_Bank_Account__Checkbook__checkbook_KEY | checkbook_KEY | OnBalance_Bank_Account |
FK_Journal_N_Checkbook__Checkbook__checkbook_KEY | checkbook_KEY | Journal_N_Checkbook |
FK_Client_Payroll_Information__Checkbook__firm_vendor_impound_deposit__checkbook_KEY | firm_vendor_impound_deposit__checkbook_KEY | Client_Payroll_Information |
FK_Client_Payroll_Information__Checkbook__firm_vendor_impound_withdrawal__checkbook_KEY | firm_vendor_impound_withdrawal__checkbook_KEY | Client_Payroll_Information |
FK_Client_Payroll_Information__Checkbook__payroll_agent_impound_deposit__checkbook_KEY | payroll_agent_impound_deposit__checkbook_KEY | Client_Payroll_Information |
FK_Client_Payroll_Information__Checkbook__payroll_agent_impound_withdrawal__checkbook_KEY | payroll_agent_impound_withdrawal__checkbook_KEY | Client_Payroll_Information |
FK_Client_Payroll_Information__Checkbook__payroll_impound_deposit__checkbook_KEY | payroll_impound_deposit__checkbook_KEY | Client_Payroll_Information |
FK_Client_Payroll_Information__Checkbook__payroll_impound_withdrawal__checkbook_KEY | payroll_impound_withdrawal__checkbook_KEY | Client_Payroll_Information |
FK_Client_Payroll_Information__Checkbook__tax_agent_impound_deposit__checkbook_KEY | tax_agent_impound_deposit__checkbook_KEY | Client_Payroll_Information |
FK_Client_Payroll_Information__Checkbook__tax_agent_impound_withdrawal__checkbook_KEY | tax_agent_impound_withdrawal__checkbook_KEY | Client_Payroll_Information |
FK_Quick_Books_Account_N_Checkbook__Checkbook__checkbook_KEY | checkbook_KEY | Quick_Books_Account_N_Checkbook |
FK_Bank_Feeds_Import__Checkbook__checkbook_KEY | checkbook_KEY | Bank_Feeds_Import |
FK_Bank_Transaction__Checkbook__checkbook_KEY | checkbook_KEY | Bank_Transaction |
FK_Client_Pay_Schedule__Checkbook__autopay__checkbook_KEY | autopay__checkbook_KEY | Client_Pay_Schedule |
FK_Recorded_Event_Object_N_Checkbook__Checkbook__checkbook_KEY | checkbook_KEY | Recorded_Event_Object_N_Checkbook |
FK_Skipped_Check_Number__Checkbook__checkbook_KEY | checkbook_KEY | Skipped_Check_Number |
FK_Bank_Feeds_Retrieved_Financial_Account__Checkbook__checkbook_guid | checkbook_guid | Bank_Feeds_Retrieved_Financial_Account |
FK_Manage_AP_Payment_Option__Checkbook__default__checkbook_KEY | default__checkbook_KEY | Manage_AP_Payment_Option |
FK_ATF_Payroll_Check_Entry_Option__Checkbook__default__checkbook_KEY | default__checkbook_KEY | ATF_Payroll_Check_Entry_Option |
FK_Skipped_Direct_Deposit_Voucher_Id__Checkbook__checkbook_KEY | checkbook_KEY | Skipped_Direct_Deposit_Voucher_Id |
FK_Enter_Transaction_User_Preference__Checkbook__default__checkbook_KEY | default__checkbook_KEY | Enter_Transaction_User_Preference |
FK_AR_Customer_Payment_Option__Checkbook__default__checkbook_KEY | default__checkbook_KEY | AR_Customer_Payment_Option |
FK_Vendor__Checkbook__default_payment__checkbook_KEY | default_payment__checkbook_KEY | Vendor |
FK_Spreadsheet_Import_Bank_Account__Checkbook__checkbook_KEY | checkbook_KEY | Spreadsheet_Import_Bank_Account |
FK_Staff_With_Access_To_Checkbook__Checkbook__checkbook_KEY | checkbook_KEY | Staff_With_Access_To_Checkbook |
FK_Bank_Reconciliation_Recurring_Adjustment_Template__Checkbook__checkbook_KEY | checkbook_KEY | Bank_Reconciliation_Recurring_Adjustment_Template |
FK_Payroll_Check_Entry_Option__Checkbook__default__checkbook_KEY | default__checkbook_KEY | Payroll_Check_Entry_Option |
FK_Bank_Feeds_Retrieved_Transaction__Checkbook__checkbook_KEY | checkbook_KEY | Bank_Feeds_Retrieved_Transaction |
FK_Manual_Funding_Bank_Transaction__Checkbook__checkbook_KEY | checkbook_KEY | Manual_Funding_Bank_Transaction |
FK_Manual_Funding_Bank_Transaction__Checkbook__impound__checkbook_KEY | impound__checkbook_KEY | Manual_Funding_Bank_Transaction |
FK_Bank_Transaction_Template__Checkbook__checkbook_KEY | checkbook_KEY | Bank_Transaction_Template |