Overview

You are not able to reconcile bank statements. You receive the following error:

Error occurred while proceccing Request
Error Details:
Error Number  : 2006
Error Message : Batch_Number exists in Transact
Error Source  : BankReconciliation
Error Methos  : CallSave
Error Time    : 11/3/2020 9:56:13 AM

Solution

When a bank reconciliation is started but not finished, it can create incomplete or duplicate records in the database. This issue is solved by deleting the incorrect bank reconciliation entry. 

Backup the existing data

1. Open SQL Server Management Studio and connect to the SQL server.
2. Expand Databases.
3. Right-click on the database you want to back up, then select Tasks > Back up. 
4. On the Back Up Database window, make sure the Database field contains the name of the database you want to back up.
5. Select the Backup Type. By default, it is Full - leave it set to that.
6. Click Remove to remove the default/last backup file name.
7. Click Add to open the Select Backup Destination window.
8. Click [...] next to the File Name field.
9. On the Locate Database Files window, select the folder where you want the backup file to go. By default, it is ..\Microsoft SQL Server\MSSQL.1\MSSQL\Backup.
10. In the File Name field, type the name for this backup, with a .bak extension.
    For example, xyz_20080221.bak for a backup of the XYZ database created on 21 February 2008.
11. Click OK to close the Locate Database Files window.
12. Click OK to close the Select Backup Destination window.
13. Click OK to start the backup. The progress icon displays in the lower-left corner, and a ‘completed successfully’ message displays when it's done.

Identify the incorrect entry

1. Open SQL Server Management Studio on the server system.
2. In the left Object Explorer window, use the navigation tree to find the Everest database.
3. Right-click on the database name and select New Query.
4. To identify the incorrect entry, run the following query:

   SELECT *
   FROM BANK_RECONCILIATION
   WHERE COA_CODE = '{bank_code}'
   ORDER BY STATEMENT_DATE
   

   Replace {bank_code} with the Bank Code as configured in your system. Make a note of the value of the BANK_RECONCILIATION_ID column corresponding to the incorrect entry.

5. Check whether there is an entry in the bank reconciliation entry table, based on the previous query's BANK_RECONCILIATION_ID result for the latest statement date, that has a duplicated journal_number:

   SELECT *
   FROM   bank_recon_entry
   WHERE  bank_recon_id = {recon_id}; 
   

   Replace {recon_id} with the previously noted BANK_RECONCILIATION_ID value.

   Note

   Other than a duplicated value, there can also be an incomplete entry containing a NULL value. For incomplete entries, they need to be backed up and then removed from the bank_reconciliation table.

Delete the incorrect entry

After you identify the incorrect entry using the steps above, remove it by running the following query:

BEGIN TRANSACTION

DELETE FROM bank_reconciliation
WHERE  coa_code = '{bank_code}'
       AND bank_reconciliation_id = {recon_id}

COMMIT TRANSACTION 

Replace {bank_code} with the Bank Code as configured in your system and {recon_id} with the previously noted BANK_RECONCILIATION_ID value.

Testing

After deleting the incorrect entry, bank reconciliation should work as intended. If you are still experiencing issues, please contact support.