Creating Cash Receipts from Bank Lock Box or EFT Files

If you have customers that pay you through electronic fund transfers (EFT) from their banks, the customer's bank sends you a lock box or EFT file as a flat file or as an EDI 823 document. The file includes the Magnetic Ink Character Recognition (MICR) information: check number, check amount, bank account number, and the customer's American Banking Association (ABA) bank routing number. The account and ABA numbers identify the customer at the bank and in your system. The file from the bank can also contain additional information, such as invoice numbers and zip codes. Using the information in this file, you can set up your system to automatically create cash receipts for open A/R balances for the customer, reducing the time it would take to manually create each EFT payment's cash receipt.

Complete the following setup before you upload and process a bank file to automatically create cash receipts:

Uploading and Viewing the Bank File

Import a bank file into the system from a folder on your computer or directly from a disk or CD. If the file is in Excel format or is a text file and you did not receive a record layout sheet from the vendor, do not upload it directly from the diskette or CD drive. Copy it to a folder on your workstation, save it in .csv (comma delimited) format, and then upload it from the folder.

After you upload the bank file, view the contents of the file to validate that the upload was successful and that the data is correct. Make notes about column widths and column positions for when you process the file.

Note: The Upload function in the Solar Eclipse Report Queue is not yet available. Open the character-based system to complete the following procedure.

From the System > Printers menu, select Your Hold Entries to display the Spooler Control screen.
Use the Upload hot key, select the bank file, and click Open.

In the Enter Spooler Title field, enter a title for the uploaded file and press Enter to display the Spooler Control screen.

A running count of the bytes of data being uploaded displays in the message bar at the bottom of the screen. When finished, the total number of bytes uploaded displays.

Note: The messages " 0 bytes Transferred to Host" or "Upload Impossible" display in the message bar when you have entered the path name incorrectly.

Press Enter to complete the upload.

The file name you uploaded displays at the top of the list on the Spooler Control screen.

Select the file in the list and use the View hot key to display the file in the Hold Entry Pre-View screen.
Identify the columns in the file to help you complete the processing of the file.

If the file is in .csv (variable) format, the data runs together and be separated by commas or another delimiter.
If the file is in fixed-width (blocked) format, the data displays in distinct columns. Fixed-width data files are normally accompanied by a specification sheet that identifies the column descriptions and widths. Use this sheet to set the update parameters in the system.

Press Esc to return to Spooler Control screen.
Continue with the procedures in the next section to process the file.

Note: If you do not want to process the file immediately, it remains in your Hold file for the number of days defined in the Minimum Days Before Report Purge control maintenance record.

Processing the Uploaded Bank File to Create Cash Receipts

When you process the bank file, the system uses the bank mappings set in the Automatic Cash Receipt Options control maintenance to determine how to apply the money for each customer to their open A/R balance. For example, you can set up the mapping to apply the cash amount in the file to the last statement total or to balances that are over 60 days old for the customer.

Note: The Process function in the Solar Eclipse Report Queue is not yet available. Open the character-based system to complete the following procedure.

From the System > Printers menu, select Your Hold Entries to display the Spooler Control screen.
Select the file you want to process and use the Process hot key.
In the Processing Type field, select Spooler Cash Receipts, and use the Begin hot key to display the Spooler Cash Receipts screen.
In the Work ID field, do one of the following. You can use the same work ID for different customers or vendors if several of them send their data in an identical format.

Enter a name that identifies the bank who sent the file or the cash receipts information you are creating to create a template for future updates and complete the fields for processing the data in the file.

Field	Description
Desc	Enter a complete description for the process that will help you identify it next time you process a file.
Branch	Enter the branch whose cash receipts information you want to update. Enter All to include all branches. If you enter a branch when setting up the bank mapping options in the Automatic Cash Receipts Optionscontrol maintenance record, the system uses that branch setting.
Start	For each data type included in the uploaded file, enter the character position on which the data starts in the file. For example, each lock box or EFT file contains the amount of each check that starts in the same place in each row in the file. In the Startfield, enter the starting position, such as 9 to indicate the amount starts on the ninth character from the right in each row of the file. Setting the starting position and the length of each data type tells the system exactly which characters in the file are used for each piece of data.
Lngth	For each piece of data included in the uploaded file, enter the number of characters the column provides for the data. For example, check amounts might start on the ninth character in a row, and the column for check amounts allows up to 10 characters. In this example, enter 9 in the Start field and 10 in the Lngth field next to the Check Amount data format, to indicate that all check amounts in the file start at character 9 and end at character 19.
Dec #	For each numerical data type included in the uploaded file, do one of the following: Enter the number of decimal places if the decimal point is not embedded, but the numeric data contains a defined number of decimal places, such as for cents included in a dollar amount. Leave the field blank if the decimal point is embedded.

Use the Rec Layout hot key to identify the cash receipts format of blocked or variable for the file you are importing. For more information, see Changing Cash Receipts Upload Formats.

Leave the field blank to process the file without saving the settings as a template. If you choose to not create a template, in the Branch field, enter the branch for which to create the cash receipts and complete the fields for processing the data in the file as described above.
Press F10 and select an ID to use an existing template.

Use the Test hot key to display the parsed EFT data according to the data layout column positions.

Use this hot key to view the column layout and make any adjustments to the positioning to get the data layout set perfectly.

After the positioning is set, use the Begin hot key to submit and auto-create the cash receipts.

Reviewing the Created Cash Receipts

As the system creates the cash receipts for the amounts in the bank file, it records the receipts in the Automatic Cash Receipts Review Queue. Any amounts that the system was unable to apply display in the queue with a reconciled amount of zero and a difference of the amount that was unapplied. For example, if the customer made an EFT payment to you, but your system does not have an outstanding A/R balance for that customer.

From the Automatic Cash Receipts Review Queue, select Print > Daily Balancing to generate the Automatic Cash Receipts Balancing Report to review the transactions posted from the EFT file. The report includes information such as total payments, posted and unposted amounts, along with information regarding freight and handling, tax, and discounts. Select View > Error Report to view the Auto Cash Receipts Posting Exceptions Report, which contains any errors encountered while posting the cash receipts.