Importing A/R Payment and Distribution Data from Bank Files

You can import and process electronic customer payments from a bank or electronic lock box and apply them to A/R invoices.  Different banks use different file formats. Set up a Map ID for each type of file format  that will be imported.

Setup

To prepare for the import:

1. On the Accounts Receivable Parameters form, specify the A/R Payment Import Archive Path. This is the folder where the A/R payment file is stored after it has been processed. You archive the files in case they need to be reimported or referred to later.
2. Optionally, use the A/R Customer Bank Account form to link customers with their bank routing and account numbers. This information is used during the A/R Payment Import Workbench validation process. When a payment is processed, the system checks to see if a record exists on this form for the bank routing and bank account. If so, it uses that information to find the customer associated with the payment. If not, the user specifies the customer in the workbench, and a record is added to this A/R Customer Bank Account form for future reference.

Mapping

Because it is a standard format already defined in SyteLine, you only need to specify a Map ID and the Type when you set up mapping for the NACHA CCD bank file format. For other delimited or non-delimited bank file formats, you must specify additional information about the file format and how the contents are mapped to the import workbench tables.

Use these forms to set up the mapping:

• A/R Payment Import Mappings
• A/R Payment Import Field Mappings
• A/R Payment Import Conversions

For more information, see Mapping Bank Files to A/R Payments and Distributions.

Importing

After customers' payments are deposited in your bank, use the A/R Payment Import utility to import electronic files from your bank that provide a record of the A/R payments. Then process the data with the A/R Payment Import Workbench:

1. To access the utility from the workbench form, click A/R Payment Import. You can also open the utility standalone.
2. Select a valid import map ID, as defined on the A/R Payment Import Mappings form.
3. When the map ID is specified, the default import path for that ID is displayed. You can change the path, which should be specified in UNC format (//server/folder).
4. Click Process.

   The utility reads each file in the import path and imports each file based on the mappings defined for the map ID. The data from successfully imported files are loaded into the workbench tables, and the files are moved to the Archive Directory specified in the Accounts Receivable Parameters form.

   If an error occurs while importing a file, the error is captured and no more files are processed. You can view the error in the Background Task History form.

5. Open the A/R Payment Import Workbench form to view and manipulate the values and then post the data to the A/R payment tables.

Note: Bank reconciliation records are not imported with this utility. Only A/R payments and distributions are imported and processed using the import utility and workbench. Bank reconciliations are created when the A/R payments are posted in SyteLine.

Processing

Use the A/R Payment Import Workbench form to view, modify, and validate the payment and distribution information so that it is ready to post. All batches that were successfully imported are listed in this form.

Note: For EFT files in NACHA CCD format, the import assumes that positions 40-54 (Company's ID Number) of record type 6 in the EFT file contain the check number. The import places this value in the Check Number field of the workbench form. If the import finds any non-numeric characters in those positions of the file, it places a zero in this field. If this field is not populated with the check number, please contact the bank to determine if the bank can place the check number in positions 40-54.

1. Select a batch ID to view its status:
   • If the status is Processed, every payment record in the batch has been processed; you can click Delete to remove the batch from the form.  The original file is stored in the Archive folder in case it needs to be reviewed later.
   • If the status is Unprocessed, at least one payment is not processed; that record has a status of Unprocessed, Error, or Hold.
2. Click Validate to validate all payments and distributions for batches where the payment status is Unprocessed or Error. During validation, if the customer field is blank for a payment or distribution record, the system:
   • Tries to find the customer number based on the invoice number.
   • If that fails, tries to find a matching customer in the A/R Customer Bank Account form, based on the bank routing and account number.
   • If that fails, leaves the customer number blank.

   Other validations are performed. Any resulting validation error messages are displayed in the grid.

3. Correct any validation errors and fill in any blank customer numbers.
4. In the Payment grid, select the payment records that you want to process. If you do not want to process a record, you can change its status to Hold.
5. The Distribution grid lists all distributions associated with a selected payment in the Payment grid. You cannot add or delete records in this grid. If you do not want a distribution to be processed, set the Apply Amount and Discount Amount to zero. Distribution records are disabled for a payment whose status is Processed or Hold.
6. Click Process.

   All payments and distributions in the batch ID that have a payment status of Unprocessed or Error are validated, even if they are not selected.

   All selected payments that are unprocessed with no errors will be processed. A/R Payment and A/R Payment Distribution records are created. When an A/R Payment record is created, the customer, routing number and bank account are checked against the A/R Customer Bank Account records. If a matching customer bank account record is not found, one is created.

Note: Discount amounts are not calculated on the distribution records. They are either included in the values from the import file, or they must be manually updated.

Related Topics

Mapping Bank Files to A/R Payments and Distributions

Example: Bank Import File Mapping