Contents
Feature Specification
Feature Name: Electronic Checks
Overview
The sponsor of this feature wishes to be able to print checks to a file in accordance with electronic check standards using the NACHA file format used in the United States. Users would submit that file to their banking institution using using third party methods such as a web interface provided by their bank. This specification is designed to generate a file only and does not include an interface for direct communication with ACH institutions.
ACH file printing will be available exclusively in xTupleERP Standard and OpenMFG editions.
Functional Requirements
- Add an option to enable ACH printing on bank accounts
- Add additional ACH required fields to Bank Account record
- Add an option to enable ACH printing on vendor records
- Add additional ACH required fields
- Add the ability to print checks to NACHA format files where applicable
New Terms and Definitions
ACH
Automated Clearing House (ACH) is the name of an electronic network for financial transactions in the United States. ACH processes large volumes of both credit and debit transactions which are originated in batches.
NACHA
NACHA-The Electronic Payments Association, formerly the National Automated Clearing House Association, is an organization that develops electronic solutions to improve the ACH payment system in the United States.
Transmit
- Designates functionality related to the transmission of data outside xTupleERP.
Related Existing Functionality
The printing of files in a specialized format falls under the umbrella of Electronic Data Interchange (EDI), which xTupleERP supports in some areas such as invoicing. However in the EDI tab for Vendor in v.3.1, there actually is no support for EDI per say, it actually only refers to purchase order E-mail settings. This new support for printing ACH file is also a corollary of EDI, but is again strictly speaking not an implementation of EDI. For this reason the EDI tabs in Vendor and Customer will be renamed "Transmit" to reflect the broader definition of options that may be available for exporting documents outside the system which may or may not include EDI support.
User-Level Functionality
Users of xTuple Standard Edition or better will be able to specify which bank accounts and vendors support direct deposit via ACH, and allow required information for an ACH transaction to be specified on the respective records. If ACH is support is enabled, users will be able to print ACH check runs or individually for bank accounts and vendors for which ACH printing is enabled.
Note: Screen shot changes pictured for Accounting Configuration, Bank Account and Vendor have already been committed to the xTuple SVN code base.
Configure Accounting
On xTuple Standard and OpenMFG editions the Accounts Payable tab on the Accounting Configuration window will have an additional group box option to enable ACH check printing. When enabled, the user will be able to edit a batch number field which will default to 1. The batch number will be used in the ACH file, and as a means to associate multiple checks together that were printed to an ACH file.
Bank Account
The screenshot above shows a new "Transmit" tab that has been added to the Bank Account screen which should only be visible when ACH check printing is enabled in Accounts Payable configuration. The fields shown are used in ACH file transmissions as follows:
- Immediate Destination: The routing number for the bank account.
- Immediate Origin: The user's 10 digit company number. The use of an IRS Federal Tax Identification is recommended.
- Immediate Origin Name: The user's company name, up to 23 characters.
- Reference Code: An optional field users may use to describe the files for internal accounting purposes.
Immediate Destination and Origin fields will be mandatory when ACH check printing is enabled.
Vendor
The "EDI" tab on the Vendor screen will be changed to "Transmit." Radio button options will be added to toggle between Purchase Order e-mail settings and check ACH settings. When toggled to the Check settings page the user will be presented with a checkable group box to enable ACH check printing for this vendor. When checked they will be able to enter the information presented as shown:
- Routing Number: The transit routing number of the receiving financial institution.
- Account Number: Receiver's account number at their financial institution.
- Account Type: Checking or Savings.
- Individual Identification Number: Receiver's identification number. This number may be printed on the receiver's bank statement by the Receiving Financial Institution.
- Individual Name: Name of Receiver.
- Discretionary Data: For the user company's internal use if desired.
Routing Number, Account Number and Individual Name will be required fields if ACH check printing is enabled.
Currently the EDI tab is hidden if batch manager is not enabled. This logic should be changed so that this tab is hidden only when batch manager and ACH check printing are not enabled. If batch manager is enabled and ACH checks are not, then the Checks button should be disabled. If ACH printing is enabled and Batch Manager is not, then the Purchase Order button should be disabled and the Checks radio button should be selected.
Printing
The View Check Run, Print Check and Print Checks windows should all be modified to run a query when they are launched to see if ACH check printing is enabled. If so, the print button should be modified to two options to print "to Printer" and "to ACH File" as shown:
If the selected bank account is not ACH enabled, the "to ACH File" menu option should be disabled. On the Print Check and View Check Run windows if the vendor for the selected check is not ACH enabled, the the "to ACH File" menu option should be disabled.
If the user chooses to print checks to an ACH File, they will be prompted with a file save dialog window that will allow them to select where the file will be saved. The default file name will be the batch number to be used on the file, with a ".dat" extension. When the user selects okay, the file should be generated and saved to the selected location, and the folder location should be stored in local settings so the next time the user prints an ACH file, the file dialog starts in the same folder they saved to previously.
When a user is printing an entire check run, there may be situations where both ACH and non-ACH enabled vendors have been selected. When a user chooses to print from the Print Checks screen the application should checked for this mixed scenario. If they are printing to printer the application should warn that ACH enabled vendors will be included in the check run and asked if they still want to proceed. If they are printing to ACH, the user should be warned that non-ACH enabled vendors will be excluded from the printing and asked whether they still want to proceed.
Report Changes
The check register display and report should have a column added to it for "Batch" that will display the ACH batch number. This column should be set to hidden by default.
Problems and Alternatives
The biggest shortcoming to this design is that it makes no provision for transmitting the ACH files directly to banking institutions. However, this development represents a good "first pass" at electronic check support, and we certainly will be able to extend that support in the future.
The internal design below specifies that the logic for ACH check generation be handled as a database function. Alternatively it could be generated by C++ code. The advantage of having it in C++ code is it would fast and much more tamper resitant from users. Also C++ generally has a toolset that might make the creation of the algorithm cleaner and more compact. However, the benefit to having the logic in the database is that the function we more accesible if it did need to be patched or modified, and also it would be easier to access from non-Qt based extensions of xTuple. Finally, since our direction is to generally put as much business logic in the database as possible, it follows we should put logic like this in the database where we can.
It is likely that users outside the United States will want to print checks to formats other than NACHA. It should be easy enough in future releases to include additional electronic check formats as radio button options and format functions as they are requested.
Internal Design
When the user prints to an ACH file, the first thing that will happen is all eligible checks (which means excluding checks for non-ACH vendors) will be updated with the next batch number stored in metrics. The application will then call a table function in Postgres called formatAchCheck(int) that will pass in the batch number used on the current check run. The function will return multiple rows of a single text column and write them to a text file to the location indicated by the user. If the print is successful, the checks in the batch will be updated with a print status of true, and the next batch number will be incremented. If it is not successful, the batch numbers associated with the check will be rolled back.
The file will be generated according to NACHA format standards which are described in detail here:
The mappings between xTuple data and the file requirements described in the NACHA specification are straight forward, with only these additional notes:
- The service class code on the Batch Header record will always be 200
- The only standard entry class used will be PPD
- The transaction code on the PPD Entry Detail record will be either 22 if the vendor uses a checking account or 32 for a savings account.
- The trace number requirement on the PPD Entry Detail record will be the bank routing number, minus the check digit, plus a unique sequential number.
There is also a sample file that can be useful as a reference point here:
Basic Algorithms
It will be at the developer's discretion to design the looping algorithm required to support the above specification.from them?
Schema Changes
Only minor schema changes are required to support this specification including additional fields for bank account and vendor to support saving ACH settings, and a field on check header to store batch numbers. It will be at the developer's discretino to determine the types and naming of these fields.
No new privileges are required
Stored Procedure Changes
A new function called formatAchChecks(int) will be used to create the ACH file dataset. This function should be included only in the standardserver codebase.
Performance Considerations
None.
Error Handling
None anticpated other than basic trapping described in the user section.
QA Considerations
This application should be tested against at least two live bank accounts in real banking institutions. Preferably one internal by xTuple, and one by the sponsor.
Gateway Bank will be used for internal testing. Our contact there is Cindy Orozco who recommends that we forward her a sample file for inspection before attempting to transmit any live files. Her e-mail address is cindyorozco@gwfh.com and her phone number is 252-331-4009 x8101.
Documentation Considerations
Documentation impact is minor. However, users should be advised in the documentation to contact their banking institutions to acquire data required to create ACH enabled bank accounts and vendors.
Release Considerations
This feature is scheduled for the 3.2 release of xTupleERP