Clear Pending Batches from Microsoft Dynamics GP Generate EFT File Window

Back in Microsoft Dynamics GP 10 and before, the ability to produce EFT files was not available in UK language installs. This meant a lot of clients used other methods for producing the EFT files and many of them are still using those alternative methods. That said, I’ve done work with a few recently where they had changed banks or otherwise needed to mke changes to the EFT format, so we hve switched them to using the standard functionality so the format can be maintained esily through the EFT File Format window ((Financial area page » Cards » EFT File Format)).

This largely works, but as the clients had been running EFT payment runs, there was a multi-year backlog of entries in the Generate EFT File window ((Financial area page » Transactions » Generate EFT File)) which needed to be cleared. While it is possible to hit the generte button and have them cleared by creating the files, for some cients we were looking at hundreds of files which would need to be created.

Instead I explored the database and found two tables holding entries which would need to be cleared down to remove the pending batches:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DELETE FROM CM20202 --Checkbook Transaction Electronic Funds Transfer (CM20202)
DELETE FROM CM20203 --Checkbook EFT Transaction Batch (CM20203)

This script removes all entries from the Generate EFT File window; before running the script make sure you don’t need any of the entries, test the script on a test company or system and make sure you have a good backup before running the script on live.