Implementing Multiple EFT File Formats for a Single Checkbook in Microsoft Dynamics GP

ACH (EFT) file format configuration within Microsoft Dynamics GP is a relatively straightforward process. However, what do you do when your bank requires you to use one particular format for Consumer transactions and another for Business transactions?

For example, your bank’s requirements could be that you must use a PPD (Prearranged Payment and Deposit) format for Consumer Account transactions and a CCD (Cash Concentration and Disbursement) format for Business Account transactions.

This requirement can easily be addressed by using the standard functionality and tools within Dynamics GP 2013 by following the steps outlined below:

Step 1

Create the two separate EFT formats – one for PPD and one for CCD. This process can be simplified by using the Export function to export the current format, import as a new name, and finally perform any updates to the formats. In this example, there will be separate CCD and PPD formats for both Payables and Receivables for a total of four different formats.

Step 2

The Format Type is not editable after you import a format, so you need to change the Format Type in the exported text file before importing. In the example below, the Format Type was updated from US – NACHA-CCD to US – NACHA-PPD.

Step 3

Once you have created the two formats, you will need to open the Checkbook EFT Payables Options window and select the File Format option to be ‘Based on Vendor’ before assigning the newly created EFT File Formats to each type of vendor.

Step 4

In Microsoft Dynamics GP 2013 you can also set multiple EFT file formats for receivables.

Step 5

The final key step before testing the EFT file formats and generating prenotes is to set up the File Transfer Method for each Vendor (or Customer). During a typical deployment of EFT, the bare minimums of fields are set on the EFT Bank Maintenance window for each Vendor (or Customer). As such the File Transfer method will likely be set to the default value which is ‘Not Specified’.

It is very time consuming to manually update all the File Transfer Method settings for all Vendors (or Customers), so I recommend a SQL update based on the Vendor Class (or Customer Class).

Below is the SQL script to update the EFT File Transfer Method for Vendors based on the Vendor Class. You will need to update the Script to specify the Vendor Classes as they appear in your system.

SY06000.EFTTransferMethod =

1= Not Specified
2= Business Account
3= Corporate Account
4= Personal Account
5= Foreign Account

UPDATE SY06000 SET EFTTransferMethod = 2 -- EFT File Transfer = Business
FROM SY06000 EFT
INNER JOIN PM00200 VENDOR ON
EFT.CustomerVendor_ID = VENDOR.VENDORID
WHERE EFT.SERIES = 4
AND VENDOR.VNDCLSID IN ('1099','G&A') -- Set Vendor Classes Here

UPDATE SY06000 SET EFTTransferMethod = 4 -- EFT File Transfer = Personal
FROM SY06000 EFT
INNER JOIN PM00200 VENDOR ON
EFT.CustomerVendor_ID = VENDOR.VENDORID
WHERE EFT.SERIES = 4
AND VENDOR.VNDCLSID IN ('EMPLOYEE') -- Set Vendor Classes Here

A similar script can easily be developed to perform a similar update for Customer EFT records.

Note: As with any SQL updates, be sure to back up your databases and run the script in your test database first to confirm the desired results.

Step 6

Finish up the process by generating some sample EFT file transactions for each of the file formats. There will be a separate EFT file generated for each of the EFT File Transfer Types. For example, if you have Vendors set for Business Account, Corporate and Foreign File Transfer Method all including your EFT batch, three different EFT files will be generated and all three files need to be submitted to the bank.

Please do not hesitate to contact me should you need assistance.

Written by:
Martin Jahn, Senior GP Consultant
FMT
(760) 930-6400

Posted by: Jakob Bechgaard