Advanced Bank Reconciliation

This page contains the answers to the most frequently asked questions (FAQ's) about Advanced Bank Reconciliation.

What Is Advanced Bank Reconciliation ?

How Do I Install ABR ?

How Do I Uninstall ABR ?

How Do I Set Up ABR Initially ?

Explanation Of Closing Balance Report

How Do I Back Out A Reconcile ?

Statement Transactions Are Not Appearing On The Reconcile Window

How Do I Delete a Duplicate Statement ?

How Do I Remove Duplicate Statement Transactions From The ABR Reconcile Window ?

Populating ABR Cheque Number When Using Integration Manager

Negative Bank Statement Amounts Are Showing As Zero Amounts On The Reconcile Window

Can I Extract GL Transactions from a Historical Year ?

How Can I Make A GL Posting That Won't Be Extracted Into ABR ?

How Do I Remove Incorrectly Extracted GL Transactions from ABR

Error Message: A remove operation on table NC_Unreconciled_GL_TRX failed

How Do I Remove Unwanted Unreconciled Transactions ?

What Does the ABR Checklinks Do ?

 

What Is Advanced Bank Reconciliation ?

Advanced Bank Reconciliation is a module for Microsoft Great Plains which provides an alternative, automated multicurrency bank reconciliation. It works as a replacement for the Great Plains' Bank Reconciliation module and this module is not required.

For more product information see the Advanced Bank Reconciliation product page.

How Do I Install ABR ?

1. Make sure that you have a good backup of your data and that all users are logged out of ABR.

2. Run the self-extracting exe file and extract the chunk file to all workstations which at present have ABR loaded, run Great Plains and include new code.

3. On one workstation run the Nolans ABR Install with all options EXCEPT Build Setup Info ticked, unless this is a brand new install of ABR, when you need to have the Build Setup Info checkbox ticked also.

How Do I Uninstall ABR ?

1. From later releases of v6.00 Nolans ABR on SQL onwards, stored procedures are created in EACH company database. If they are present they will ned to be removed using MS SQL Enterprise Manager. The stored procedures are:

   ncExtractGLTransactions
   ncUpdateMatchedTable
   ncReconcile

2. In each client directory, delete the abr.dic dictionary, and, if present, the NCLF2440.dic and NCLR2440.dic modified forms and reports dictionaries. Edit the Microsoft Great Plains.set file to remove all references to Nolans ABR.

3. Remove all Nolans Intercompany palette items. These are:

   Transactions >> Financial
   Bank Statement Entry, ABR Reconcile, ABR Ex.Rate Assign
   Routines >> Financial
   ABR Extract Trx, ABR Statement Import
   Setup >> Financial
   ABR Import Maint, ABR Import Format, ABR Reconcile Rules, ABR Bank Setup, ABR Extract Setup
   Reports >> Financial
   Adv. Bank Rec.
   Utilities >> Financial
   ABR Extract Except, ABR Bank Cleardown, ABR Bank TRX Cleardown
   Enquiry >> Financial
   ABR Bank Statements, ABR Reconcile, ABR Cheque Enquiry

4. If desired (although this is not strictly necessary), remove all ABR tables from the company databases. These all begin with NCABR.

How Do I Set Up ABR Initially ?

1. Pick a date where you know that every transaction before that date has been reconciled on your previous system. Run an extract up to that date. This may not be so bad as it sounds as the ABR Extract will only read Open transactions. Enter a dummy statement for the total amount and reconcile. The purpose of this is to guard against a user forgetting to enter a start date when extracting some time in the future, and causing these already reconciled transactions to be extracted.

2. Now run an extract from the end date of the previous extract to the date at which you want to start using ABR. This extract will contain some transctions which have been reconciled using your previous system, and some which have not been reconciled. Enter another dummy statement for the total of the previously reconciled transactions, set the statement closing balance to your last reconciled statement balance, match this against the previously reconciled transactions, and reconcile.

3. You should now have only your unreconciled transactions displayed in the ABR Reconcile window, ready to reconcile against the statements you enter into the ABR Bank Statement Entry window.

Explanation Of Closing Balance Report

1. The Statement Ending Balance is what the bank tells us is our current statement balance.

2. However, ABR tells us that there are unreconciled statement transactions, so we REVERSE these from the Statement Ending Balance to get back to a reconciled statement balance (Net Ending Balance).

3. We then add on to the reconciled statement balance the unreconciled GL transactions to get a figure which should agree to what Great Plains tell us is the bank balance (Overall Book Balance).

How Do I Back Out A Reconcile ?

To use the Unreconcile Utility successfully you will need to know the Reconcile Numbers of the reconciles that you want to 'back out'. If you are not sure how to obtain this information, please contact the Nolan Computers Support Team for assistance.

1. Make sure that you have a good backup of your data and that all users are logged out of Great Plains.

2. If the Bank ID on which you wish to perform the unreconcile has a Reconcile Account, then, on the workstation on which you are going to run the unreconcile, exit from Great Plains, edit the Dex.ini file and add the line:

   OverrideUnreconcile=TRUE

   Then log back into Great Plains.

3. Add the Unreconcile Utility to the Shortcut Bar - it's in the NCL Advanced Bank Rec. product, Financial series, window name Unreconcile Transactions.

4. Run the Unreconcile Utility. Enter the appropriate Bank ID, Start Reconcile Number, and End Reconcile Number, then press the Unreconcile button. This will bring all of the reconciled GL and Statement transactions back into the unreconciled tables ready to be re-matched and re-reconciled. At this point check whether everything looks OK, if there are any problems, then restore from the backup and contact the Nolan Computers Support Team immediately. If everything is OK then proceed with the re-reconcile.

5. Please be aware that the Unreconcile Utility DOES NOT reverse the GL postings made between the Bank GL account and the Reconcile GL account by the original reconcile. This posting will need to be reversed manually before re-reconciling. The manual reversing journal needs to have Source Document type of NCABR to ensure that it is ignored by subsequent extracts.

Statement Transactions Are Not Appearing On The Reconcile Window

The first thing to look at when unreconciled statement transactions do not appear on the right hand side of the Reconcile window is to make sure that the statement which the transactions appear on is balanced. That is, on the Bank Statement Entry window, check that the user-entered Ending Balance in the statement header information is equal to the calculated Ending Balance at the bottom right of the window.

The calculated Ending Balance is the Starting Balance plus all the statement transaction amounts. An unbalanced statement can be saved by the user overriding a warning message, but none of the transactions will appear on the Reconcile window.

How Do I Delete a Duplicate Statement ?

You can delete any unreconciled statement by calling it up in the Bank Statement Entry window (Transactions >> Financial >> Bank Statement Entry) and hitting the Delete button at the top of the window. However, if this does not cause the statements after the deleted statement to have their starting and ending balances calculated automatically, you could try the following:

1. Make sure that you have a good backup of your data, and that all users are logged out of ABR.

2. Run an ABR Checklinks, for the appropriate Bank ID only, with the Report Only Option TICKED. You may need to add the Checklinks to the Shortcut Bar - it's in the Financial Series of the Nolans Advanced Bank Rec product.

3. Check the report produced, if there are no fatal errors reported (they have the words 'Checklinks cannot fix this' in the error message) then proceed to 4),otherwise contact the Nolan Computers Support Desk for advice.

4. Re-run the ABR Checklinks with the Report Only option UNTICKED. This will correct any non-fatal errors, and also recalculate the statement starting and ending balances. Please be aware that the checklinks will also renumber the statements so that they are in sequence, so any breaks in the sequence of statement numbers will be corrected.

How Do I Remove Duplicate Statement Transactions From The ABR Reconcile Window ?

1. If the duplicated transactions are all on the same statement number, then go in to Bank Statement Entry, call up the appropriate Bank ID and Statement Number and click on the Delete button at the top of the Bank Statement Entry window.

2. If the duplicated statement transactions are on a statement number which also contains valid statement transactions, then go in to Bank Statement Entry, call up the appropriate Bank ID and Statement Number and, for each of the duplicated transactions, highlight the transaction in the scrolling window, click on the Edit menu item in the toolbar, and choose Delete Row from the dropdown menu. You will then need to adjust the Statement Ending Balance to reflect the new value before saving the statement.

3. In both of the above cases you will need to refresh the Reconcile window for the appropriate Bank ID by clicking on the Delete button in the Reconcile window. This will not delete any of the statement transactions themselves, but will delete transactions from the copy table that the Reconcile window uses. Re-entering the Bank ID into the Reconcile window will cause the copy table to be rebuilt from the underlying transactions. Please be aware that refreshing the Reconcile window in this way will cause any match or marked information already in the the Reconcile window to be lost.

Populating ABR Cheque Number When Using Integration Manager

On the ABR Reconcile window for GL transactions the Reference field corresponds to the Reference (REFRENCE) column in the GL Open table (GL20000), and the Document / Cheque Number corresponds to the Originating Document Number (ORDOCNUM) column.

Either of these fields can be used to set up reconcile rules which will allow automatic propose / matching of GL transactions with statement transactions

Negative Bank Statement Amounts Are Showing As Zero On The Reconcile Window

The problem you are encountering is due to the fact that the Bank Statement Entry window doesn't like negative amounts in the Withdrawal and Deposit columns. It processes the amounts entered so that withdrawals become negative amounts on the ABR Reconcile window, and deposits become positive amounts.

To rectify the problem, delete the rows with the negative amounts from the Bank Statement Entry window, then re-enter any negative deposits as a withdrawal, and any negative withdrawals as a deposit.

Can I Extract GL Transactions from a Historical Year ?

1. Normally the ABR GL Extract process will only extract GL transactions from the GL Open table (GL20000) into the ABR Unreconciled GL transactions table. However, in some cases, it is necessary to extract transactions from the GL Historical Transactions table (GL30000).

   In later releases of v6.00 ABR and all releases of v7.00 ABR, this can be accomplished as follows - Exit from Great Plains, edit the Dex.ini file in the Great Plains client directory on the workstation from which you will run the historical extract to add the line:

   HistoryExtract=TRUE

2. Log back into Great Plains and, when you open the ABR Extract window, you should see an extra button marked Extract From History. This button performs exactly the same function as the normal Extract button, but will work on the GL30000 table instead of the GL20000 table.

   The History Extract facility should be used with care, we would recommend keeping the date range of the extract as small as possible, and removing the facility once it is no longer needed by removing the appropriate line in the Dex.ini file, or changing it to:

   HistoryExtract=FALSE

How Can I Make A GL Posting That Won't Be Extracted Into ABR ?

The way to enter a transaction against the Bank GL account and ensure that it is not extracted into ABR, is to give it a Source Document type of NCABR. This document type is usually reserved for GL postings made by the ABR module itself, and is ignored by the ABR extract.

Removing Incorrectly Extracted GL Transactions from ABR ?

** These actions are probably best carried out under the supervision of Nolans Support **

Unfortunately this is a bit of a 'grey area'. There is no way via the ABR front end to remove unreconciled GL transactions from the Reconcile window. What we usually do in this case is to delete the transactions from the underlying tables.

There are three tables involved (Assuming Great Plains v6.00 and above) - the unreconciled GL transactions table (NCABR304), the matched GL transactions table (NCABR012), and the extract keys table which keeps a record of all of the transactions that have been extracted (NCABR201). The approved method is to uniquely identify the transactions that you want to delete in these tables, then delete them.

The only problem with this is that, as we are deleting rows from the extract keys table, the transactions could be potentially be extracted again if the appropriate date range were entered. We could just omit deleting the rows from the keys table, but the next time that an ABR Checklinks is run it will realise that the rows in the keys table refer to transactions which don't exist anywhere else in ABR, and will delete them itself anyway.

Error Message: A remove operation on table NC_Unreconciled_GL_TRX failed

If you receive the error message 'A remove operation on table NC_Unreconciled_GL_TRX failed because record could not be locked' when running an ABR reconcile, this usually indicates that the reconcile has failed for some reason.

Please see the FAQ 'How Do I Back Out A Reconcile' for details on how to unreconcile the failed reconcile.

How Do I remove Unwanted Unreconciled Transactions ?

1. Enter a GL journal with a source document type of GJ between the bank account and a suspense account for an amount which will contra off the unwanted transaction.

2. Enter an equal and opposite journal to 1) above, with a source document type of NCABR. This will not be extracted by the ABR Extract as it ignores transactions with a source document type of NCABR because this is what ABR uses when it is posting reconcile journals.

3. Run an ABR Extract, which will extract the transaction from 1). This can be matched against the unwanted transaction in the ABR Reconcile window and reconciled. There will be no effect on your GL from the journals in 1) and 2) as they are equal and opposite.

Regarding unwanted statement transactions:
You can remove unwanted statement transactions by accessing the statement via the Bank Statement Entry window, highlighting the appropriate statement lines and clicking on Edit >> Delete Row. You will need to adjust the statement ending balance to reflect the new statement total before you are able to save the statement.

What Does the ABR Checklinks Do ?

The ABR Checklinks will perform the following checks on the data and correct anomalies where possible:

1. Check that for every row in the reconciled statement line auxiliary table (NCABR033) there is a matching row in the main reconciled statement line table (NCABR020).

2. Check that for every row in the reconciled GL line auxiliary table (NCABR032) there is a matching row in the main reconciled GL line table (NCABR019).

3. Check that for every row in the unreconciled statement line (NCABR008) and reconciled statement line (NCABR020) tables, there is a header row in the unreconciled statement header (NCABR007) and reconciled statement header (NCABR029) tables.

4. Recalculate the statement starting and ending balances in the unreconciled and reconciled statement header tables (NCABR007 and NCABR029), and update the last statement balance in the last statement number table (NCABR023).

5. If there are any statements which have rows in the unreconciled statement header table (NCABR007) but which have no lines in the unreconciled statement line table (NCABR008), remove the orphaned header row.

6. Check that all statement headers have a statement date, if not set the header statement date to the latest date of this statement's lines.

7. Check that each row in the extract keys table (NCABR201) has a matching row either in the unreconciled GL transactions (NCABR304) or reconciled GL transactions (NCABR019 and NCABR032) tables. If it doesn't, then remove the row from the keys table.

8. Check that the totals for each Match Number in the reconciled GL transactions table (NCABR032) and reconciled statement line table (NCABR033) agree. If not then flag an error.

9. Remove all rows from from the matched GL transactions table (NCABR012) and matched statement transaction table (NCABR011) so that these tables are rebuilt the next time the Bank ID is entered into the Reconcile window. Be aware that this will cause all match / mark information present when the checklinks is run to be lost.

 

For more general product information see the Advanced Bank Reconciliation product page.

For detailed information on the changes in each release, see the Product Releases page. For a summary of the current versions of all our products, see the Product Release Summary page.