Ouch, Modifying Direct Deposit Files

I have definitely had my fair share of ouch moments over the generation of ACH files in Dynamics GP. If a payroll is processed with incorrect information like a mistyped routing number or account number, or if a direct deposit is voided after the payroll is posted, the ACH file that is generated contains incorrect information.

The party line is to void the entire payroll and then re-run with corrected information. Although this is fine for small companies, it doesn't work all that well for large companies with large payrolls.

So, we used to edit the ACH file manually. This worked great, although it was always a little hit and miss to make sure you caught all of the fields that needed to be updated. However, in recent years this is no longer an option due the encoding created at the time the ACH file is created in GP (referred to as "hash" entries). These hash entries will vary based on the information in the file, so there is no reasonable way to update the file manually and still have it accepted by the bank.

So what to do? Well, if the client has an understanding bank, or the bank has a full-featured website, often you can upload the file to the bank and either make the changes in their website or the bank will make the changes for you. But what to do if the bank's website does not have that capability, or the bank itself is not helpful?

Well, in those cases, I have found you can use the following script to update the Direct Deposit records in GP and then regenerate the file (which will recreate the correct hash entries as well). Now, I must say, I have anecdotal experience that this works. But it may not work in all instances, so you need to test it with the bank and also make sure you have a functional backup before doing this!

These scripts assume that the payroll build is still available in the Generate ACH window (Transactions>>Payroll>>Generate ACH). The script should be run against the company database.

UPDATE DD10500 SET DDAMTDLR=’1500.00000’ WHERE DEX_ROW_ID=’insert dex row ID of record you need to change’

The field in the script above (DDAMTDLR) will change the dollar amount, DDTRANUM would update the routing number, and DDACTNUM will update the account number. In all cases, once you make the change, you can regenerate the file and the footer information (totals, hash info, etc) will be correct. To view the contents in the table, use SELECT * FROM DD10500 and you can find the correct dex_row_ID of the record you want to update. You will notice that the records in the table are identified with the build number from the Generate ACH window (field INDEX1 in the table).

Since I sort of stumbled on this through trial and error under pressure, please share any insights you have or other fixes or pitfalls you have found.