Table: dbo.Bank_Reconciliation_Statement_Import
This table will hold the list of bank statement transactions imported from the bank via a file. These transactions will be used by bank reconciliation to clear bank transactions. It will hold on to these transactions when the user chooses to finish the bank rec later and once the user finalizes, it will lose all its data for the given Bank Statement.
Columns
NameTypeConstraintsDescription
bank_reconciliation_statement_import_KEYint (4)NOT NULLPrimary key.
bank_reconciliation_statement_KEYint (4)NOT NULLLink to the bank_reconciliation_statement record.
bank_reconciliation_statement_detail_KEYint (4)Link to the reconciliation detail recond. Could be null when non-matched.
transaction_datesmalldatetime (4)NOT NULLTransaction date from the bank statement import file.
reference_numbernvarchar (32)NOT NULLReference (check) number from the bank statement import file.
amountdecimal (9, 2)NOT NULLTransaction amount from the bank statement import file.
transaction_descriptionnvarchar (240)NOT NULLTransaction description from the bank statement import file.
is_creditbit (1)NOT NULLT/F: true if this transaction is a Credit, false it a Debit
bank_reconciliation_statement_imported_file_name_KEYint (4)NOT NULLForeign key linking a transaction back to the imported statement file it was read from.
ach_file_header_record_KEYint (4)This key is used in statement import for auto matching a single bank reconciliation statement import record with direct deposit Ach totals ["BR"] (sum of amounts from BTRD rows grouped by both ACH file header record key and bank reconciliation statement key). Null means that there is no ["BR"] match found and a valid key value links the statement import record to its ACH record match in the direct deposit. When a user unclears a direct deposit ["BR"] ACH file record in direct deposit tab in bank reconciliaiton, this key is used with the bank reconciliation statement key to find the matched record ["BR"] in bank reconciliation statement import table and set the key value to null (this way, the statement import record is available for matching).
Primary key
NameColumnsDescription
PK_Bank_Reconciliation_Statement_Importbank_reconciliation_statement_import_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Bank_Reconciliation_Statement_Import__Ach_File_Header_Record__ach_file_header_record_KEYach_file_header_record_KEYAch_File_Header_Record (ach_file_header_record_KEY)
FK_Bank_Reconciliation_Statement_Import__Bank_Reconciliation_Statement__bank_reconciliation_statement_KEYbank_reconciliation_statement_KEYBank_Reconciliation_Statement (bank_reconciliation_statement_KEY)
FK_Bank_Reconciliation_Statement_Import__Bank_Reconciliation_Statement_Detail__bank_reconciliation_statement_detail_KEYbank_reconciliation_statement_detail_KEYBank_Reconciliation_Statement_Detail (bank_reconciliation_statement_detail_KEY)
FK_Bank_Reconciliation_Statement_Import__Bank_Reconciliation_Statement_Imported_File_Name__bank_reconciliation_statement_KEY__babank_reconciliation_statement_KEY, bank_reconciliation_statement_imported_file_name_KEYBank_Reconciliation_Statement_Imported_File_Name (bank_reconciliation_statement_KEY, bank_reconciliation_statement_imported_file_name_KEY)
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_BS_Bank_Reconciliation_Statement_Import__Ach_File_Header_Record__Bank_Reconciliation_Statement_Importbank_reconciliation_statement_import_KEYBS_Bank_Reconciliation_Statement_Import__Ach_File_Header_Record

Schema diagram