To that end, I've compiled the same logic, with few exceptions, into an assembly you can download here. Copy this to your Addins folder and then you can perform this same function from the Sales Batch Entry Additional Menu without having to run a SQL script or access the Reconcile Receivable Amounts Window in GP.
One exception to the logic in the script is that this will not delete empty batches. If you have an opinion as to how valuable that feature might be please leave a comment and I will evolve this accordingly. This utility will simply do the following:
- Update batch totals for those that are NOT locked by a user with a Batch Activity Record (SY00800). This was not designed to reconcile a specific batch but rather all batches at once. So, don't select a batch in Sales Batch Entry when executing this routine unless you want the batch selected to be excluded from the logic.
- Create missing Batch Header records for batches that do exist in SOP WORK (SOP10100) but not SY00500.
This was developed on GP 2010 and tested on both GP 2010 and GP 10.