Business Automation and Software Blog

Sage 300 ERP Tip: Fix the Bank Services Reconcile Statement Glitch

Posted by Nicole Sherman on Thu, Oct 25, 2012 @ 08:30 AM

It’s been slow going but I finally found the tip for fixing the Sage 300 ERP (formerly known as Sage Accpac) 6.0 Bank Services bug. Well, this is more than just a bug; it’s a glitch that was unsuspectingly used, which prevents you from closing the month using Reconcile Statements. As I look back and walk thru the chain of events, the reconciliation started out going great.  We opened up Reconcile Statements and reviewed the work that was done so far by the Customer. They were confused and unsure on what they were doing so I felt it was best to go from beginning to end. So we reconciled all the deposits first and the total at the bottom of the screen tied out perfectly.  Great!

Next we reconciled all the withdrawals.  This took quite a while longer with how many checks were on the statement. We finally finished selecting all the withdrawals and then compared our totals to find there was a problem. The total Withdrawals at the bottom of the screen did not match the total withdrawals on the statement. I was out of balance when I compared the withdrawals total on screen to the debits on the statement from the Bank. We reconfirmed that every item had been properly selected. We then ran the Withdrawal Status Report and verified side by side what was selected for withdrawals.  They all matched.  This couldn’t be possible – The Total Withdrawal Amount in the Reconcile Statements screen was inaccurate?

After racking my brain for a while and going thru the tech notes for the database, and still coming up without an answer, I decided to go back to square one.  First I ran a database dump of the current company database.  Next I loaded the dump into my training company so I could work with the data outside of the live environment.  This way I can test things without affecting anyone’s work.

After logging into the training company, I went back to the reconciliation screen.  Same problem, same data.  Then I un-reconciled every single item in the account.  Hmmm… I am still showing a withdrawal amount total of $2,273.85.  I save my work and set the screen to only show reconciled items. Nothing appears, which means there is nothing left as reconciled. This tells me that the total is not calculated based on what is on screen. So, there must be a data table storing it. I head back to the Data Layout pdf and narrow it down to where I believe I may find it.  I start up MSSQL Server Management Studio and scroll to the BKACCT table. Once I have the data on screen in edit mode I scan it for my $2,273.85.  Yep, there it is. Not once but twice!  The culprits are RECWTCLR and RECWPCLR.  I zero out the balances in each, save my changes and go back to the reconciliation screen. When I pull it up I am thrilled to see that it now has gone to zero on the total, which is correct.


Now for the next big question – how did that happen?  Everything that has been done to date has been done using Accpac’s screens. Nothing has been manually edited in the database to change Bank Services.  Thinking back to the reports that I ran, I remember that I saw a Reversal Transaction that was coincidentally the same amount as I am off. Interesting. I decide to test my hypothesis that the reversal caused the issue.

First I select a withdrawal for $50.00 to be reconciled and then I save my work and keep the reconciliation screen open.  (Note: having the screen open while doing the rest of the steps is key.) Next I open up the Reverse Transactions screen in Bank Services. After locating the withdrawal that is currently reconciled, I post the reversal. No errors or warnings, it appears to post the change without any issue.

I switch back to the reconciliation screen that I have left open.  I select 2 more $50.00 items to be reconciled and save my work again. No errors or warnings. Total Withdrawals shown is $150.00. I close out of the reconciliation and then go back in. When I review the total at the bottom of the Reconciliation Screen, it is still $150.00 equal to total of the three items, but only two of the items remain on screen as part of the reconciliation.  The item that was reversed is nowhere to be found. Well, that was good news and bad news. The good news is it can be fixed and we know what happened, why, and how to recreate it.  The bad news is the users can recreate the problem too.  Hopefully this will help anyone else that runs into this problem – it was quite a pain initially!

If you would like assistance with your business management software, please contact PositiveVision for a complmentary consultation today. We would be happy to be of assistance.

Request a Complimentary  Technology Consultation from PositiveVision ► ►

Learn more about PositiveVision's computer consulting services...

Topics: Sage 300 ERP