After spending a lot of time troubleshooting, I finally cracked the code on a particularly stubborn Sage 300 ERP bank reconciliation problem. This issue goes beyond a simple bug and can completely block your month-end closing process. Here's what happened and how to fix it.

The Problem Unfolds

Everything started normally when we opened the Reconcile Statements function to review a client's work. They had been struggling with the process, so I decided to walk through it step by step from the beginning.

The deposits reconciled perfectly. All items matched, and the total at the bottom tied out exactly to the bank statement. So far, so good.

Then came the withdrawals. After carefully going through each check and transaction on the statement, we selected all the appropriate items. But when we compared totals, something was wrong. The withdrawal total shown on screen didn't match the debits on the bank statement, even though we had verified that every single item was properly selected.

We ran the Withdrawal Status Report and cross-checked everything. All the selected items matched what should have been reconciled. Yet somehow, the system was showing an incorrect total withdrawal amount.

Digging Into the Database

After exhausting the usual troubleshooting steps and documentation, I decided to investigate more deeply. I created a database dump of the live company data and loaded it into a training environment so I could safely experiment without messing anything up.

In the test environment, the same problem persisted. Here's where it got interesting: I unreconciled every single item in the account, but the withdrawal total still showed $2,273.85 instead of zero. When I filtered to show only reconciled items, nothing appeared, confirming that no items were actually marked as reconciled.

This told me the total wasn't being calculated from the visible reconciliation data. Instead, it had to be stored somewhere in the database tables.

Finding the Culprit

I consulted the Data Layout documentation and opened SQL Server Management Studio to examine the BKACCT table directly. Scanning through the data, I found my phantom $2,273.85 amount stored in not one, but two fields: RECWTCLR and RECWPCLR.

I zeroed out both fields, saved the changes, and returned to the reconciliation screen. Success. The total now correctly showed zero.

The Root Cause

The next question was how this happened in the first place. Everything had been done through Sage's standard interfaces, with no manual database editing.

Looking back through the reports, I noticed a reversal transaction for exactly the same amount as the discrepancy. This gave me a theory to test.

Reproducing the Issue

To confirm my hypothesis, I ran this test:

1. Selected a $50 withdrawal for reconciliation and saved, keeping the reconciliation screen open
2. Opened the Reverse Transactions screen in Bank Services
3. Located and reversed the reconciled withdrawal (the system processed this without errors or warnings)
4. Returned to the still-open reconciliation screen
5. Selected two more $50 items for reconciliation and saved
6. Closed and reopened the reconciliation screen

The result confirmed the problem: the screen showed a total of $150 (for three $50 items), but only two items were visible in the reconciliation list. The reversed item had disappeared from view, but its amount remained in the running total.

The Fix

The solution involves directly updating the BKACCT table:

1. Back up your database before making any changes
2. Use SQL Server Management Studio to access the BKACCT table
3. Locate the problematic bank account record
4. Set both RECWTCLR and RECWPCLR fields to zero
5. Save the changes
6. Return to the Reconcile Statements screen to verify the totals are now correct.

Prevention

The issue occurs when users reverse transactions that have already been reconciled while keeping the reconciliation screen open. To prevent this problem:

• Always close reconciliation screens before processing transaction reversals
• Train users to complete reconciliations before making any transaction changes
• Consider implementing workflow procedures that separate reconciliation and transaction modification processes

While this was initially frustrating and hard to diagnose, understanding both the cause and the fix should help anyone who encounters this particular reconciliation phantom balance issue. The key is knowing where Sage stores these running totals and how the reversal process can leave orphaned amounts in the database.

PositiveVision 

PositiveVision is a trusted ERP consultant with nearly two decades of experience helping businesses implement and optimize their ERP solutions. We offer comprehensive support to ensure seamless integration and solutions tailored to each client's unique needs. If you need ERP support or consulting in the Chicago area, contact us today to get started.

Linda Baran

Linda Baran is in charge of the people side of PositiveVision. Linda’s background includes working in a variety of industries including investment, manufacturing, and information technology.