Table: dbo.Checkbook
Table which holds data related to clients bank accounts.
Columns
NameTypeConstraintsDescription
checkbook_KEYint (4)NOT NULLPrimary key for this table.
client_KEYint (4)NOT NULLKey of client that owns the checkbook.
descriptionnvarchar (100)NOT NULLCheckbook description.
bank_KEYint (4)Key of Bank account is tied to.
bank_account_type_KEYnvarchar (22)NOT NULLType of bank account.
bank_account_numbernvarchar (34)NOT NULLBank account the checkbook draws from.
next_check_numberint (4)NOT NULLNext check number.
next_direct_deposit_voucher_idnvarchar (30)NOT NULLID of next direct deposit voucher.
use_separate_direct_deposit_voucher_idbit (1)NOT NULLFlag 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_numbernvarchar (30)NOT NULLReference number to use for bank reconciliation.
payroll_layout__report_definition_KEYint (4)Key of report definition to use when printing payroll checks from this checkbook.
vendor_layout__report_definition_KEYint (4)Key of report definition to use when printing vendor checks from this checkbook.
logo__report_image_KEYint (4)Bank logo image
signature_1__report_image_KEYint (4)First signature image
signature_2__report_image_KEYint (4)Second signature image
date_format_KEYint (4)Date format to use on printed checks.
bank_account_direct_deposit_status_KEYnvarchar (16)NOT NULLFK the direct deposit prenote status
number_of_digits_allowed_for_micr_auxiliary_onustinyint (1)NOT NULLNumber of digits allowed for the MICR auxiliary on-us field
starting_position_of_micr_onustinyint (1)NOT NULLStarting position for printing the MICR on-us field
use_beginning_micr_onus_symbolbit (1)NOT NULLFlag which indicates if the leading MICR symbol is printed for the on-us field
funding_effective_date_selectiontinyint (1)NOT NULLFirm funding effective date selection. 1== CheckDate 2 == SystemDate
funding_effective_date_offsetint (4)NOT NULLFirm funding effective date banking days offset
fund_payroll_transactionbit (1)NOT NULLFlag which indicates that this bank account will automatically generate impound funding transactions for payroll checks
fund_tax_agent_transactionbit (1)NOT NULLFlag which indicates that this bank account will automatically generate impound funding transactions for payroll tax agent checks
fund_firm_vendor_transactionbit (1)NOT NULLFlag which indicates that this bank account will automatically generate impound funding transactions for vendor checks
fund_payroll_agent_transactionbit (1)NOT NULLFlag which indicates that this bank account will automatically generate impound funding transactions for payroll agent checks
is_account_inactivebit (1)NOT NULLIs account inactive flag.
gl_account_KEYint (4)Key to the gl_account the checkbook draws from.
bank_reconciliation_frequency_type_KEYint (4)NOT NULLReconciliation frequency time period
bank_reconciliation_finalized_available_to_editint (4)NOT NULLThe 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_KEYint (4)The GL Account for the tax agents funding liability.
payroll_funding_liability__gl_account_KEYint (4)The GL Account for the payroll funding liability.
payroll_agent_funding_liability__gl_account_KEYint (4)The GL Account for the payroll agents funding liability.
account_holder_namenvarchar (100)NOT NULLContains the name of the account holder
bank_reconciliation__journal_KEYint (4)NOT NULLThe key of the journal used in bank reconciliation when writing transactions with a General Ledger component, such as adjustments.
positive_pay_start_datesmalldatetime (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_KEYint (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_KEYint (4)NOT NULLType of format selected for MICR line (Standard, Custom).
custom_micr_section_1nvarchar (40)NOT NULLFirst section of the custom MICR line (can be Aux on-us, transit or on-us).
custom_micr_section_2nvarchar (24)NOT NULLSecond section of the custom MICR line (can be Aux on-us, transit or on-us).
custom_micr_section_3nvarchar (36)NOT NULLThird section of the custom MICR line (can be Aux on-us, transit or on-us).
form_adjustment_write_off_thresholddecimal (9, 2)NOT NULLImpound only: Threshold amount for form adjustement write off.
form_adjustment_write_off__gl_account_KEYint (4)Impound only: The GL account for form adjustment write off. NULL indicates no account has been selected.
impounding__gl_account_KEYint (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_KEYint (4)NOT NULLReference to primary sort by type for printing payroll checks
employee_sort_by_type_KEYint (4)Reference to secondary sort by type for printing payroll checks
payroll_check_printing_primary__sort_order_type_KEYint (4)NOT NULLReference to primary sort order for printing payroll checks
payroll_check_printing_secondary__sort_order_type_KEYint (4)NOT NULLReference to secondary sort order for printing payroll checks
vendor_payment_primary_sort_by_type_KEYint (4)NOT NULLReference to primary sort by type for printing vendor checks
vendor_payment_primary__sort_order_type_KEYint (4)NOT NULLReference to primary sort order for printing vendor checks
fund_only_printed_payroll_checksbit (1)NOT NULLFlag indicating funding only printed payroll checks.
fund_only_printed_payroll_agent_checksbit (1)NOT NULLFlag indicating funding only printed payroll agent checks.
fund_only_printed_tax_agent_checksbit (1)NOT NULLFlag indicating funding only printed tax agent checks.
ap_layout__report_definition_KEYint (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_accountbit (1)NOT NULL
fund_payroll_transaction_type_KEYint (4)NOT NULLContains 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_KEYint (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_guiduniqueidentifier (16)NOT NULLA unique identifer for each checkbook row.
prompt_for_next_check_numberbit (1)NOT NULLIndicates if we should prompt the user for the next check number when printing checks.
starting_position_of_micr_onus_deposit_sliptinyint (1)NOT NULLStarting 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_slipnvarchar (40)NOT NULLFirst 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_slipnvarchar (24)NOT NULLSecond 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_slipnvarchar (36)NOT NULLThird 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_KEYint (4)NOT NULLType 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_slipbit (1)NOT NULLFlag which indicates if the leading MICR symbol is printed for the on-us field for deposit slips
deposit_layout__report_definition_KEYint (4)Key of report definition to use when printing deposit slip from this checkbook.
vendor_payment_secondary__sort_order_type_KEYint (4)NOT NULLIt determines the sort order (ascending or descending) for secondary sorting.
vendor_payment_secondary_sort_by_type_KEYint (4)This stores the key for secondary sort order.
yodlee_financial_account_link_status_KEYtinyint (1)NOT NULLThe yodlee financial account link status.
allow_all_applicable_firm_staff_accessbit (1)NOT NULLBit 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_accessbit (1)NOT NULLBit field that indicates whether or not all the client staff having access to the parent client have access to this checkbook.
pre_numbered_check_stockbit (1)NOT NULLPre-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
NameColumnsDescription
PK_Checkbookcheckbook_KEY
Unique constraints
NameColumnsDescription
UK_Checkbook$checkbook_guidcheckbook_guid
UK_Checkbook$client_KEY$descriptionclient_KEY, description
Foreign keys
NameColumnsReferenced tableDescription
FK_Checkbook__Bank__bank_KEYbank_KEYBank (bank_KEY)
FK_Checkbook__Bank_Account_Direct_Deposit_Status__bank_account_direct_deposit_status_KEYbank_account_direct_deposit_status_KEYBank_Account_Direct_Deposit_Status (bank_account_direct_deposit_status_KEY)
FK_Checkbook__Bank_Account_Type__bank_account_type_KEYbank_account_type_KEYBank_Account_Type (bank_account_type_KEY)
FK_Checkbook__Bank_Reconciliation_Frequency_Type__bank_reconciliation_frequency_type_KEYbank_reconciliation_frequency_type_KEYBank_Reconciliation_Frequency_Type (bank_reconciliation_frequency_type_KEY)
FK_Checkbook__Client__client_KEYclient_KEYClient (client_KEY)
FK_Checkbook__Date_Format__date_format_KEYdate_format_KEYDate_Format (date_format_KEY)
FK_Checkbook__Employee_Sort_By_Type__employee_sort_by_type_KEYemployee_sort_by_type_KEYEmployee_Sort_By_Type (employee_sort_by_type_KEY)
FK_Checkbook__Export_Text_File_Format__positive_pay__export_text_file_format_KEYpositive_pay__export_text_file_format_KEYExport_Text_File_Format (export_text_file_format_KEY)
FK_Checkbook__Fund_Payroll_Transaction_Type__fund_payroll_transaction_type_KEYfund_payroll_transaction_type_KEYFund_Payroll_Transaction_Type (fund_payroll_transaction_type_KEY)
FK_Checkbook__GL_Account__form_adjustment_write_off__gl_account_KEYform_adjustment_write_off__gl_account_KEYGL_Account (gl_account_KEY)
FK_Checkbook__GL_Account__gl_account_KEYgl_account_KEYGL_Account (gl_account_KEY)
FK_Checkbook__GL_Account__impounding__gl_account_KEYimpounding__gl_account_KEYGL_Account (gl_account_KEY)
FK_Checkbook__GL_Account__payroll_agent_funding_liability__gl_account_KEYpayroll_agent_funding_liability__gl_account_KEYGL_Account (gl_account_KEY)
FK_Checkbook__GL_Account__payroll_funding_liability__gl_account_KEYpayroll_funding_liability__gl_account_KEYGL_Account (gl_account_KEY)
FK_Checkbook__GL_Account__tax_agent_funding_liability__gl_account_KEYtax_agent_funding_liability__gl_account_KEYGL_Account (gl_account_KEY)
FK_Checkbook__Journal__bank_reconciliation__journal_KEYbank_reconciliation__journal_KEYJournal (journal_KEY)
FK_Checkbook__Micr_Line_Format__micr_line_format_KEYmicr_line_format_KEYMicr_Line_Format (micr_line_format_KEY)
FK_Checkbook__Payroll_Check_Printing_Primary_Sort_By_Type__payroll_check_printing_primary_sort_by_type_KEYpayroll_check_printing_primary_sort_by_type_KEYPayroll_Check_Printing_Primary_Sort_By_Type (payroll_check_printing_primary_sort_by_type_KEY)
FK_Checkbook__Quick_Books_Account__export__quick_books_account_KEYexport__quick_books_account_KEYQuick_Books_Account (quick_books_account_KEY)
FK_Checkbook__Report_Definition__ap_layout__report_definition_KEYap_layout__report_definition_KEYReport_Definition (report_definition_KEY)
FK_Checkbook__Report_Definition__payroll_layout__report_definition_KEYpayroll_layout__report_definition_KEYReport_Definition (report_definition_KEY)
FK_Checkbook__Report_Definition__vendor_layout__report_definition_KEYvendor_layout__report_definition_KEYReport_Definition (report_definition_KEY)
FK_Checkbook__Yodlee_Financial_Account_Link_Status__yodlee_financial_account_link_status_KEYyodlee_financial_account_link_status_KEYYodlee_Financial_Account_Link_Status (yodlee_financial_account_link_status_KEY)
FK_Checkbook__Report_Image__logo__report_image_KEYlogo__report_image_KEYReport_Image (report_image_KEY)
FK_Checkbook__Report_Image__signature_1__report_image_KEYsignature_1__report_image_KEYReport_Image (report_image_KEY)
FK_Checkbook__Report_Image__signature_2__report_image_KEYsignature_2__report_image_KEYReport_Image (report_image_KEY)
FK_Checkbook__Sort_Order_Type__payroll_check_printing_primary__sort_order_type_KEYpayroll_check_printing_primary__sort_order_type_KEYSort_Order_Type (sort_order_type_KEY)
FK_Checkbook__Sort_Order_Type__payroll_check_printing_secondary__sort_order_type_KEYpayroll_check_printing_secondary__sort_order_type_KEYSort_Order_Type (sort_order_type_KEY)
FK_Checkbook__Sort_Order_Type__vendor_payment_primary__sort_order_type_KEYvendor_payment_primary__sort_order_type_KEYSort_Order_Type (sort_order_type_KEY)
FK_Checkbook__Vendor_Payment_Primary_Sort_By_Type__vendor_payment_primary_sort_by_type_KEYvendor_payment_primary_sort_by_type_KEYVendor_Payment_Primary_Sort_By_Type (vendor_payment_primary_sort_by_type_KEY)
FK_Checkbook__Sort_Order_Type__vendor_payment_secondary__sort_order_type_KEYvendor_payment_secondary__sort_order_type_KEYSort_Order_Type (sort_order_type_KEY)
FK_Checkbook__Vendor_Payment_Secondary_Sort_By_Type__vendor_payment_secondary_sort_by_type_KEYvendor_payment_secondary_sort_by_type_KEYVendor_Payment_Secondary_Sort_By_Type (vendor_payment_secondary_sort_by_type_KEY)
FK_Checkbook__Report_Definition__deposit_layout__report_definition_KEYdeposit_layout__report_definition_KEYReport_Definition (report_definition_KEY)
FK_Checkbook__Micr_Line_Format__deposit_slip__micr_line_format_KEYdeposit_slip__micr_line_format_KEYMicr_Line_Format (micr_line_format_KEY)
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Client_Direct_Deposit__Checkbook__checkbook_KEYcheckbook_KEYClient_Direct_Deposit
FK_Bank_Reconciliation_Statement__Checkbook__checkbook_KEYcheckbook_KEYBank_Reconciliation_Statement
FK_Netclient_File_Log__Checkbook__checkbook_KEYcheckbook_KEYNetclient_File_Log
FK_Pending_AP_Payment__Checkbook__checkbook_KEYcheckbook_KEYPending_AP_Payment
FK_OnBalance_Bank_Account__Checkbook__checkbook_KEYcheckbook_KEYOnBalance_Bank_Account
FK_Journal_N_Checkbook__Checkbook__checkbook_KEYcheckbook_KEYJournal_N_Checkbook
FK_Client_Payroll_Information__Checkbook__firm_vendor_impound_deposit__checkbook_KEYfirm_vendor_impound_deposit__checkbook_KEYClient_Payroll_Information
FK_Client_Payroll_Information__Checkbook__firm_vendor_impound_withdrawal__checkbook_KEYfirm_vendor_impound_withdrawal__checkbook_KEYClient_Payroll_Information
FK_Client_Payroll_Information__Checkbook__payroll_agent_impound_deposit__checkbook_KEYpayroll_agent_impound_deposit__checkbook_KEYClient_Payroll_Information
FK_Client_Payroll_Information__Checkbook__payroll_agent_impound_withdrawal__checkbook_KEYpayroll_agent_impound_withdrawal__checkbook_KEYClient_Payroll_Information
FK_Client_Payroll_Information__Checkbook__payroll_impound_deposit__checkbook_KEYpayroll_impound_deposit__checkbook_KEYClient_Payroll_Information
FK_Client_Payroll_Information__Checkbook__payroll_impound_withdrawal__checkbook_KEYpayroll_impound_withdrawal__checkbook_KEYClient_Payroll_Information
FK_Client_Payroll_Information__Checkbook__tax_agent_impound_deposit__checkbook_KEYtax_agent_impound_deposit__checkbook_KEYClient_Payroll_Information
FK_Client_Payroll_Information__Checkbook__tax_agent_impound_withdrawal__checkbook_KEYtax_agent_impound_withdrawal__checkbook_KEYClient_Payroll_Information
FK_Quick_Books_Account_N_Checkbook__Checkbook__checkbook_KEYcheckbook_KEYQuick_Books_Account_N_Checkbook
FK_Bank_Feeds_Import__Checkbook__checkbook_KEYcheckbook_KEYBank_Feeds_Import
FK_Bank_Transaction__Checkbook__checkbook_KEYcheckbook_KEYBank_Transaction
FK_Client_Pay_Schedule__Checkbook__autopay__checkbook_KEYautopay__checkbook_KEYClient_Pay_Schedule
FK_Recorded_Event_Object_N_Checkbook__Checkbook__checkbook_KEYcheckbook_KEYRecorded_Event_Object_N_Checkbook
FK_Skipped_Check_Number__Checkbook__checkbook_KEYcheckbook_KEYSkipped_Check_Number
FK_Bank_Feeds_Retrieved_Financial_Account__Checkbook__checkbook_guidcheckbook_guidBank_Feeds_Retrieved_Financial_Account
FK_Manage_AP_Payment_Option__Checkbook__default__checkbook_KEYdefault__checkbook_KEYManage_AP_Payment_Option
FK_ATF_Payroll_Check_Entry_Option__Checkbook__default__checkbook_KEYdefault__checkbook_KEYATF_Payroll_Check_Entry_Option
FK_Skipped_Direct_Deposit_Voucher_Id__Checkbook__checkbook_KEYcheckbook_KEYSkipped_Direct_Deposit_Voucher_Id
FK_Enter_Transaction_User_Preference__Checkbook__default__checkbook_KEYdefault__checkbook_KEYEnter_Transaction_User_Preference
FK_AR_Customer_Payment_Option__Checkbook__default__checkbook_KEYdefault__checkbook_KEYAR_Customer_Payment_Option
FK_Vendor__Checkbook__default_payment__checkbook_KEYdefault_payment__checkbook_KEYVendor
FK_Spreadsheet_Import_Bank_Account__Checkbook__checkbook_KEYcheckbook_KEYSpreadsheet_Import_Bank_Account
FK_Staff_With_Access_To_Checkbook__Checkbook__checkbook_KEYcheckbook_KEYStaff_With_Access_To_Checkbook
FK_Bank_Reconciliation_Recurring_Adjustment_Template__Checkbook__checkbook_KEYcheckbook_KEYBank_Reconciliation_Recurring_Adjustment_Template
FK_Payroll_Check_Entry_Option__Checkbook__default__checkbook_KEYdefault__checkbook_KEYPayroll_Check_Entry_Option
FK_Bank_Feeds_Retrieved_Transaction__Checkbook__checkbook_KEYcheckbook_KEYBank_Feeds_Retrieved_Transaction
FK_Manual_Funding_Bank_Transaction__Checkbook__checkbook_KEYcheckbook_KEYManual_Funding_Bank_Transaction
FK_Manual_Funding_Bank_Transaction__Checkbook__impound__checkbook_KEYimpound__checkbook_KEYManual_Funding_Bank_Transaction
FK_Bank_Transaction_Template__Checkbook__checkbook_KEYcheckbook_KEYBank_Transaction_Template

Schema diagram