Table: dbo.Bank_Transaction
Columns
NameTypeConstraintsDescription
bank_transaction_KEYint (4)NOT NULLPrimary key
transaction_datesmalldatetime (4)Date of the transaction
bank_transaction_type_KEYint (4)NOT NULLThe type of bank transaction
checkbook_KEYint (4)NOT NULLThe checkbook/bank account the transaction is associated with
transaction_status_KEYint (4)NOT NULLThe current status of the bank transaction
amountdecimal (9, 2)NOT NULLThe amount of the transaction
reference_numbernvarchar (32)NOT NULLEither a reference or check number depending on transaction type
memonvarchar (480)NOT NULLA memo associated with the transaction
descriptionnvarchar (240)NOT NULLThe transaction description or name of the payee, depending on transaction type
is_handwritten_checkbit (1)NOT NULLIndicates whether or not a withdrawal is a handwritten check
edited_ach_effective_datesmalldatetime (4)Effective date of the transaction if edited by the user - null otherwise
funding_processed_status_KEYint (4)NOT NULLFunding electronic processing status values: 0 - tx has not been processed, 1 - tx has been funded but not processed, 2 - tx has been processed, 3 - do not ever process this tx cuz it was created before funding was set up.
export_journal_entry_to_csa_status_KEYint (4)NOT NULLThe export status of the bank transaction.
bank_transaction_guiduniqueidentifier (16)NOT NULLA unique GUID key for this row
are_bank_reconciliation_details_overridden_by_userbit (1)NOT NULLDid the user override the system generated bank reconciliation detail for this bank transaction? This flag is set to false (0) if the bank transaction's bank reconciliation detail has been automatically generated by the system; it is set to true (1) if the detail was entered by the user. If set to true, then it is up the developer to make sure the bank reconciliation detail stays in sync with the bank transaction; the system will not automatically make adustments as the bank transaction's amount or description changes.
data_origin_KEYint (4)NOT NULLThe origin of each bank transaction. For example, Data Entry or CSA Conversion.
sent_check_stub_to_employee_self_servicebit (1)NOT NULLThis flag is set to 1 (true) for pay checks that have been transmitted to Employee Self Service. It is set to 0 (false) for pay checks that haven't been sent yet and for all other transaction types.
reference_number_sortablenvarchar (160)NOT NULLA representation of the reference_number column that sorts in natural order. A trigger keeps this field synchronized with the content of reference_number.
origin__accounting_application_KEYint (4)The application that was running when the bank transaction was created.
generated_by__transaction_template_KEYint (4)If we created the bank transaction from a template, and the template has not been deleted, then this is the key of the template we used. Otherwise, it will be null.
auto_clear_in_bank_recbit (1)NOT NULLThis flag determines how the transaction is treated by the Bank Reconciliation process. If true, the transaction is automatically marked cleared during the next Bank Reconciliation session; if false, the transaction is left uncleared and the user will have to manually clear it.
exported_to_quick_booksbit (1)NOT NULLThis flag should be true if the transaction has been exported to quickbooks. It should be false otherwise.
check_output_type_KEYint (4)Specifies the printed mode of the transactions having type as Payroll/Vendor checks (Paper/Remote). If null then the transactions are of some other types.
is_proxy_for_a_distributionbit (1)NOT NULLTrue when the only purpose of the transaction is to affect checkbook balance, but not account balances. Otherwise, false.
bank_reconciliation_clear_status_KEYint (4)NOT NULLUsed to track if the aggregate clear status of matching Bank_Transaction_Reconciliation_Detail rows.
skip_negative_fundingbit (1)NOT NULLA bool flag to indicate whether or not to create negative funding when reversing a check.
Primary key
NameColumnsDescription
PK_Bank_Transactionbank_transaction_KEY
Unique constraints
NameColumnsDescription
UK_Bank_Transaction$bank_transaction_guidbank_transaction_guid
Foreign keys
NameColumnsReferenced tableDescription
FK_Bank_Transaction__Accounting_Application__origin__accounting_application_KEYorigin__accounting_application_KEYAccounting_Application (accounting_application_KEY)
FK_Bank_Transaction__Bank_Transaction_Type__bank_transaction_type_KEYbank_transaction_type_KEYBank_Transaction_Type (bank_transaction_type_KEY)
FK_Bank_Transaction__Check_Output_Type__check_output_type_KEYcheck_output_type_KEYCheck_Output_Type (check_output_type_KEY)
FK_Bank_Transaction__Checkbook__checkbook_KEYcheckbook_KEYCheckbook (checkbook_KEY)
FK_Bank_Transaction__Data_Origin__data_origin_KEYdata_origin_KEYData_Origin (data_origin_KEY)
FK_Bank_Transaction__Export_Journal_Entry_To_Csa_Status__export_journal_entry_to_csa_status_KEYexport_journal_entry_to_csa_status_KEYExport_Journal_Entry_To_Csa_Status (export_journal_entry_to_csa_status_KEY)
FK_Bank_Transaction__Funding_Processed_Status__funding_processed_status_KEYfunding_processed_status_KEYFunding_Processed_Status (funding_processed_status_KEY)
FK_Bank_Transaction__Transaction_Status__transaction_status_KEYtransaction_status_KEYTransaction_Status (transaction_status_KEY)
FK_Bank_Transaction__Transaction_Template__generated_by__transaction_template_KEYgenerated_by__transaction_template_KEYTransaction_Template (transaction_template_KEY)
FK_Bank_Transaction__Bank_Reconciliation_Clear_Status__bank_reconciliation_clear_status_KEYbank_reconciliation_clear_status_KEYBank_Reconciliation_Clear_Status (bank_reconciliation_clear_status_KEY)
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Bank_Feeds_Bank_Transaction__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYBank_Feeds_Bank_Transaction
FK_Web_Employee_Check_Stub_Queue__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYWeb_Employee_Check_Stub_Queue
FK_Initial_Open_Item_Bank_Transaction__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYInitial_Open_Item_Bank_Transaction
FK_Quick_Books_Online_Exported_Journal_Entry__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYQuick_Books_Online_Exported_Journal_Entry
FK_GL_Transaction_N_Bank_Transaction__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYGL_Transaction_N_Bank_Transaction
FK_Direct_Deposit_Bank_Transaction__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYDirect_Deposit_Bank_Transaction
FK_Transfer_Bank_Transaction__Bank_Transaction__client__bank_transaction_KEYclient__bank_transaction_KEYTransfer_Bank_Transaction
FK_Transfer_Bank_Transaction__Bank_Transaction__impound__bank_transaction_KEYimpound__bank_transaction_KEYTransfer_Bank_Transaction
FK_Positive_Pay_File_Detail__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYPositive_Pay_File_Detail
FK_Reprintable_Bank_Transaction__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYReprintable_Bank_Transaction
FK_Workforce_Hub_Check_Stub_Queue__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYWorkforce_Hub_Check_Stub_Queue
FK_GL_Balancing_Entry_N_Bank_Transaction__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYGL_Balancing_Entry_N_Bank_Transaction
FK_Check_Print_Run_N_Bank_Transaction__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYCheck_Print_Run_N_Bank_Transaction
FK_Manual_Funding_Bank_Transaction__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYManual_Funding_Bank_Transaction
FK_Funding_Withdrawal_Bank_Transaction_Detail__Bank_Transaction__detail__bank_transaction_KEYdetail__bank_transaction_KEYFunding_Withdrawal_Bank_Transaction_Detail
FK_Funding_Withdrawal_Bank_Transaction_Detail__Bank_Transaction__funding_withdrawal__bank_transaction_KEYfunding_withdrawal__bank_transaction_KEYFunding_Withdrawal_Bank_Transaction_Detail
FK_Bank_Transaction_Reversal__Bank_Transaction__reversal__bank_transaction_KEYreversal__bank_transaction_KEYBank_Transaction_Reversal
FK_Bank_Transaction_Reversal__Bank_Transaction__reversed__bank_transaction_KEYreversed__bank_transaction_KEYBank_Transaction_Reversal
FK_Impound_Payment_Client_Bank_Transaction_Detail__Bank_Transaction__client__bank_transaction_KEYclient__bank_transaction_KEYImpound_Payment_Client_Bank_Transaction_Detail
FK_Impound_Payment_Client_Bank_Transaction_Detail__Bank_Transaction__impound__bank_transaction_KEYimpound__bank_transaction_KEYImpound_Payment_Client_Bank_Transaction_Detail
FK_Bank_Transaction_Accounting_Source_Override_Journal_Entry_Distribution__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYBank_Transaction_Accounting_Source_Override_Journal_Entry_Distribution
FK_Form_1099_Print_Threshold_Override__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYForm_1099_Print_Threshold_Override
FK_Bank_Transaction_Event__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYBank_Transaction_Event
FK_Bank_Reconciliation_Adjustment_Order__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYBank_Reconciliation_Adjustment_Order
FK_AR_Payment__Bank_Transaction__ar_deposit__bank_transaction_KEYar_deposit__bank_transaction_KEYAR_Payment
FK_Ach_Entry_Detail_Record__Bank_Transaction__bank_rec_funded_deposit_detail__bank_transaction_KEYbank_rec_funded_deposit_detail__bank_transaction_KEYAch_Entry_Detail_Record
FK_Bank_Transaction_Distribution__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYBank_Transaction_Distribution
FK_Bank_Transaction_Address__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYBank_Transaction_Address
FK_Form_1099__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYForm_1099
FK_Bank_Transaction_Accounting_Source_Override_Bank_Transaction_Distribution__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYBank_Transaction_Accounting_Source_Override_Bank_Transaction_Distribution
FK_Payroll_Bank_Transaction__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYPayroll_Bank_Transaction
FK_Bank_Transaction_Reconciliation_Detail__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYBank_Transaction_Reconciliation_Detail
FK_Bank_Transaction_Reconciliation_Detail__Bank_Transaction__source__bank_transaction_KEYsource__bank_transaction_KEYBank_Transaction_Reconciliation_Detail
FK_Bank_Reconciliation_Statement_Detail__Bank_Transaction__adjustment__bank_transaction_KEYadjustment__bank_transaction_KEYBank_Reconciliation_Statement_Detail
FK_Vendor_Bank_Transaction__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYVendor_Bank_Transaction
FK_Print_Payable_Transaction_Selection__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYPrint_Payable_Transaction_Selection
FK_Funding_Withdrawal_Liability_Detail__Bank_Transaction__funding_withdrawal__bank_transaction_KEYfunding_withdrawal__bank_transaction_KEYFunding_Withdrawal_Liability_Detail
FK_Print_Check_Selection__Bank_Transaction__bank_transaction_KEYbank_transaction_KEYPrint_Check_Selection

Schema diagram