Bank Details Import Definitions

Bank Details Import Definitions

Select Setup>Bank Details Import Definitions to access the screen. There are hints and tips on the Add/Edit screen.

Bank Details Imports are used to import Bank Statement 'Helper' Data into Bank Recon. It is ideally sourced from a payments system or similar, which contains all the details of the transactions set to the bank. 1 Helper Bank transaction and multiple Helper Cashbook Transactions are imported per Group. This information is then used in a Recon Helper rule to assist in reconciling bulk payment and collection transactions. See the page on Recon Helper Rules for more information. The Bank Details information is only used by the recon rules so it doesn't matter in what order the imports are done. But the Helper Data, Bank Transaction and enough Cashbook Transactions need to be present in the database for the Helper rule to work.

• An import definition can be added by selecting the Add button

• The user can edit an import definition by selecting the name of that definition

• A specific definition can be deleted by selecting the delete button and then selecting OK

Supported Database Types and Connection String formats:

SQL: Data Source=Server;UID=User;pwd=Password;Initial Catalog=Database;

Oracle: User ID=User;Data Source=Server;Persist Security Info=True;Password=Password

Key Field:

• Bank Recon supports a date key type and an int key type

  • The date type ignores any time component and always ticks over one day at a time

  • ie Last Key is 2011-06-12, the next import will use 2011-06-13

  • Use Date = @keyvalue or Date >=@keyvalue

  • Int key expects a number and will return any records with an ID value greater than the last Key

  • Use ID>@keyvalue

  • Updates the Keyvalue to the max(ID) returned

• The Key Field name must exactly match a column returned by the SQ Query, for example Date or ID. You can also specify a separate keyfield column if preferred

• Use @Keyvalue in a SQL query to return the records newer than the last Key Value

• Use :keyvalue in Oracle

Import Required Fields:

• AccountNumber is the Account Number the Group should be linked to. It uses the same mapping set up in the Cashbook Import Mapping Account Map

• IsBankTransaction is a flag to indicate what table to place the transaction in, the ReconHelperBankTransaction or the ReconHelperCashbookTransaction

  • On a typical payment file the Bank record is the Contra Record for the account and the Cashbook records are the individual instructions sent to the bank

  • GroupId is a unique identifier for every group of 1 Bank Transaction and multiple Cashbook Transactions imported

    • The sum of the Cashbook Transactions per Group must exactly equal the value of the Bank Transaction in the same group