Table: dbo.Bank_Reconciliation_Statement
Columns
NameTypeConstraintsDescription
bank_reconciliation_statement_KEYint (4)NOT NULLPrimary key for this table.
statement_beginning_datesmalldatetime (4)NOT NULLbeginning date for a bank account reconciliation.
statement_ending_datesmalldatetime (4)NOT NULLending date for a bank account reconciliation.
checkbook_KEYint (4)NOT NULLLinks us to checkbook that this bank rec pertains to.
statement_indexint (4)NOT NULL1 will be the first bank rec done for a given checkbook, 2 will be the second, etc. Allows us to see the sequence of the bank recs done for a checkbook.
bank_reconciliation_status_KEYint (4)NOT NULLLinks us to the table that gives the bank rec status: available status types are current, pending, finalized.
beginning_statement_balance_amountdecimal (9, 2)NOT NULLBeginning balance from the bank statement.
ending_statement_balance_amountdecimal (9, 2)NOT NULLEnding balance from the bank statement.
adjustments_amountdecimal (9, 2)NOT NULLadjustment amount for this period.
adjusted_period_balance_amountdecimal (9, 2)NOT NULLadjusted account balance amount.
unreconciled_amountdecimal (9, 2)NOT NULLunreconciled amount for this period.
deposits_other_credits_amountdecimal (9, 2)NOT NULLDeposits and other credits from the bank statement.
checks_other_debits_amountdecimal (9, 2)NOT NULLchecks and other debits from the bank statement.
open_deposits_other_credits_amountdecimal (9, 2)NOT NULLopen deposits other credits for this reconciliation period.
open_checks_other_debits_amountdecimal (9, 2)NOT NULLopen checks other debits for this reconciliation period.
bank_errors_amountdecimal (9, 2)NOT NULLbank errors, this period.
financial_account_reconciled_amountdecimal (9, 2)NOT NULLThis amount is calculated by taking the ending statement amount, adding in the amount of open deposits and credits, subtracting the amount of open checks and debits, and adding or subtracting any bank errors.
prior_statement_uncleared_checks_debits_amountdecimal (9, 2)NOT NULLThis is also called the "total balance of checks and debits in transit". It is the amount of uncleared (open) checks from the last bank rec period.
checks_debits_reconciliation_analysis_total_amountdecimal (9, 2)NOT NULLCalculated by taking the prior_statement_uncleared_checks_debits_amount, adding the current period gl account credit balance, then subtracting the amount of checks and debits cleared on the bank statement.
checks_debits_reconciliation_analysis_difference_amountdecimal (9, 2)NOT NULLCalculated by taking the checks_debits_reconciliation_analysis_total_amount and subtracting the open checks amount.
prior_statement_uncleared_deposits_credits_amountdecimal (9, 2)NOT NULLThis is also called the "total balance of deposits and credits in transit". It is the amount of uncleared (open) deposits from the last bank rec period.
deposits_credits_reconciliation_analysis_total_amountdecimal (9, 2)NOT NULLCalculated by taking the prior_statement_uncleared_deposits_credits_amount, adding the current period gl account debit balance, then subtracting the amount of deposits and credits cleared on the bank statement.
deposits_credits_reconciliation_analysis_difference_amountdecimal (9, 2)NOT NULLCalculated by taking the deposits_credits_reconciliation_analysis_total_amount and subtracting the open deposits amount.
prior_period_bank_reconciliation_ending_balance_amountdecimal (9, 2)NOT NULLEnding balance from the last period's reconciliation.
current_period_financial_account_beginning_balance_amountdecimal (9, 2)NOT NULLThe GL account beginning balance for the current period.
current_period_financial_account_ending_balance_amountdecimal (9, 2)NOT NULLThe GL account ending balance for the current period.
current_period_financial_account_debits_amountdecimal (9, 2)NOT NULLThe debit amount for the GL account for this checkbook.
current_period_financial_account_credits_amountdecimal (9, 2)NOT NULLThe credit amount for the GL account for this checkbook.
statement_file_namenvarchar (520)NOT NULLThe location and name of the imported Bank Statement file
ending_statement_balance_amount_overridedecimal (9, 2)the ending statement balance amount entered by the user. This is sometimes used instead of the calculated amount. If the user does not enter an override amount, then this column will be null. If an override amount has been entered, this will be it, and it will be used as the bank reconciliation statement ending balance amount, unless the user has entered a non-zero amount for statement deposits/additions and/or statement checks/payments. If a non-zero entry exists for one of those two, then this override amount will be ignored, and the calculated amount will be used instead. Therefore, this override amount is used conditionally, it is not always used when it exists.
Primary key
NameColumnsDescription
PK_Bank_Reconciliation_Statementbank_reconciliation_statement_KEY
Unique constraints
NameColumnsDescription
UK_Bank_Reconciliation_Statement$checkbook_KEY$statement_beginning_datecheckbook_KEY, statement_beginning_date
UK_Bank_Reconciliation_Statement$checkbook_KEY$statement_indexcheckbook_KEY, statement_index
Foreign keys
NameColumnsReferenced tableDescription
FK_Bank_Reconciliation_Statement__Bank_Reconciliation_Status__bank_reconciliation_status_KEYbank_reconciliation_status_KEYBank_Reconciliation_Status (bank_reconciliation_status_KEY)
FK_Bank_Reconciliation_Statement__Checkbook__checkbook_KEYcheckbook_KEYCheckbook (checkbook_KEY)
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Bank_Reconciliation_Statement_Summary__Bank_Reconciliation_Statement__bank_reconciliation_statement_KEYbank_reconciliation_statement_KEYBank_Reconciliation_Statement_Summary
FK_Bank_Reconciliation_Client_Adjustment__Bank_Reconciliation_Statement__bank_reconciliation_statement_KEYbank_reconciliation_statement_KEYBank_Reconciliation_Client_Adjustment
FK_Bank_Reconciliation_Recurring_Adjustment_Template__Bank_Reconciliation_Statement__creation__bank_reconciliation_statement_KEYcreation__bank_reconciliation_statement_KEYBank_Reconciliation_Recurring_Adjustment_Template
FK_Bank_Reconciliation_Recurring_Adjustment_Template__Bank_Reconciliation_Statement__deletion__bank_reconciliation_statement_KEYdeletion__bank_reconciliation_statement_KEYBank_Reconciliation_Recurring_Adjustment_Template
FK_Bank_Reconciliation_Rapid_Clear_Unmatched_Transaction__Bank_Reconciliation_Statement__bank_reconciliation_statement_KEYbank_reconciliation_statement_KEYBank_Reconciliation_Rapid_Clear_Unmatched_Transaction
FK_Bank_Reconciliation_Statement_Imported_File_Name__Bank_Reconciliation_Statement__bank_reconciliation_statement_KEYbank_reconciliation_statement_KEYBank_Reconciliation_Statement_Imported_File_Name
FK_Bank_Reconciliation_Adjustment_Order__Bank_Reconciliation_Statement__bank_reconciliation_statement_KEYbank_reconciliation_statement_KEYBank_Reconciliation_Adjustment_Order
FK_Bank_Transaction_Reconciliation_Detail__Bank_Reconciliation_Statement__bank_reconciliation_statement_KEYbank_reconciliation_statement_KEYBank_Transaction_Reconciliation_Detail
FK_Bank_Reconciliation_Statement_Detail__Bank_Reconciliation_Statement__bank_reconciliation_statement_KEYbank_reconciliation_statement_KEYBank_Reconciliation_Statement_Detail
FK_Bank_Reconciliation_Statement_Import__Bank_Reconciliation_Statement__bank_reconciliation_statement_KEYbank_reconciliation_statement_KEYBank_Reconciliation_Statement_Import

Schema diagram