{{ model.title }}
This page is currently being edited by {{ model.editingBy }}.Tax Collection: SmartFusion Modules > Tax Collection > Process > Import Lockbox Payments
Utility Billing: SmartFusion Modules > Utility Billing > Process > Import Lockbox Payments
Recent Changes:
6.216 - Added the AQ2 lockbox type for Utility Billing.
6.208 - Added functionality to timestamp the import file, then move it to a folder called "processed" that resides under the folder that a user imports it from. So, if a user imports a file called "c:\test\testimport.txt" it will rename that file to "testimport**timestamp**.txt" and will then put it in "c:\test\processed".
6.175 - Added validation to check if the deposit batch being used belongs to a deposit that is locked.
6.147 - Correction to Wells Fargo Online Payments issue where last payment in every file was not being processed. Also added logic to read route extension if exists.
6.144 - New lockbox type for Wells Fargo Online Payments. This feature will only be available if the Wells Fargo Online Payments is turned on in the Interface section of the license file.
Refer to bottom of page for changes prior to version 6.140Introduction
The importing of bulk lockbox payments in SmartFusion is possible for users of utility billing and tax collection that have set up lockbox payments with their branch. The import process will read from a chosen file and create payment transactions just as if the user had manually entered each payment. The form requires an import file, a receipt type (limited by module), a lockbox provider code (limited by module), a payment date to be recorded on all payment transactions, and a payment method (check or credit card) [utility billing only!]. in configuration item you have to have a lockbox receipt type for UB and CC.
Once processed, the import file will be time-stamped and moved to a folder called "processed" that resides under the folder that the user imported from.
UB Single-line | UB Multi-line | UB Multi-line BB&T | UB Simple Layout | UB AQ2 | Tax Standard
Import Lockbox Payments Report
The import lockbox payments form and report are shared by both billing and tax collection modules. The report is divided into two sections, first the parsed information contained in the import file is displayed for any entry that was successfully parsed. Below that is a section for import entries that contained an exception, preventing them from being accepted as payments, complete with the specific exception that occurred (examples below). These items can be rerun after corrections to either the current database or the import file, as needed. However, nothing prevents the "good" entries from being parsed a second time (creating another payment transaction if "Print & Update" was chosen) if the import file is processed a second time with completed entries in it.
Tax Collection Only
If on demand penalty and interest is enabled the associated penalty and interest transactions will be created and posted just like if a normal payment was accepted regardless to if they are being paid off.
Possible exception messages:
- "No customer found matching this account."
- "No customer location found for this account."
- "Route number and sequence do not match a customer location associated with this account." *
"Route number and sequence returned multiple customer locations associated with this account." *
Exceptions 3 and 4 appear only for files parsed by the Multi-line lockbox provider.
Utility Billing Single-line Specification
Because a route number and sequence for each customer are not determined in the import file, the most recently modified customer location with a relationship to the customer will be associated to the payment that is created.
Check Record
| Field | Start | Length | Comments |
|---|---|---|---|
| Record Type | 1 | 1 | Should be "C" |
| Batch Number | 2 | 3 | Batch Sequence (not used currently) |
| Check Amount | 5 | 10 | Implied decimal of 2 places, will be added after parsing |
| Transaction Number | 15 | 9 | Sequence number of the check in the transaction |
| Account Number | 24 | 9 | SmartFusion customer account number |
| Check Digit | 33 | 2 | Not used currently |
| Check Number | 35 | 10 | Customer's check number, not assigned by bank |
| Customer Name | 45 | 20 | Not necessary, customer name will be filled out regardless |
Utility Billing Multi-line Specification
Check Record
| Field | Start | Length | Comments |
|---|---|---|---|
| Record Type | 1 | 1 | Should be "C" |
| Batch Number | 2 | 3 | Batch Sequence (not used currently) |
| Check Amount | 5 | 10 | Implied decimal of 2 places, will be added after parsing |
| Check Number | 15 | 10 | Customer's check number, not assigned by bank |
| Transaction Number | 25 | 2 | Sequence number of the check in the transaction |
| Customer Name | 27 | 20 | Not necessary, customer name will be filled out regardless |
Stub Record (when not using the "Include route suffix" or the "Include route extension" options)
| Field | Start | Length | Comments |
|---|---|---|---|
| Record Type | 1 | 1 | Should be "S" |
| Batch Number | 2 | 3 | Batch Sequence (not used currently) |
| payment Amount | 5 | 10 | Can't contain decimals, not assigned by bank |
| Account Number | 15 | 10 | SmartFusion customer account number |
| Transaction Number | 25 | 2 | Sequence number of the check in the transaction |
| Route Number | 27 | 7 | Customer location's route number |
| Route Sequence | 34 | 7-10 | Customer location's sequence number |
Stub Record (when using the "Include route suffix" option)
| Field | Start | Length | Comments |
|---|---|---|---|
| Record Type | 1 | 1 | Should be "S" |
| Batch Number | 2 | 3 | Batch Sequence (not used currently) |
| payment Amount | 5 | 10 | Can't contain decimals, not assigned by bank |
| Account Number | 15 | 10 | SmartFusion customer account number |
| Transaction Number | 25 | 2 | Sequence number of the check in the transaction |
| Route Number | 27 | 7 | Customer location's route number |
| Route Sequence | 34 | 10 | Customer location's sequence number |
| Route Suffix | 44 | 10 | Customer location's suffix number |
| Route Extension | 54 | 3 | Customer location's extension number |
Stub Record (when using the "Include route extension" option)
| Field | Start | Length | Comments |
|---|---|---|---|
| Record Type | 1 | 1 | Should be "S" |
| Batch Number | 2 | 3 | Batch Sequence (not used currently) |
| payment Amount | 5 | 10 | Can't contain decimals, not assigned by bank |
| Account Number | 15 | 10 | SmartFusion customer account number |
| Transaction Number | 25 | 2 | Sequence number of the check in the transaction |
| Route Number | 27 | 7 | Customer location's route number |
| Route Sequence | 34 | 10 | Customer location's sequence number |
| Route Extension | 44 | 3 | Customer location's extension number |
Trailer Record
| Field | Start | Length | Comments |
|---|---|---|---|
| Record Type | 1 | 1 | Should be "T" |
| Lockbox Number | 2 | 3 | Lockbox number (not used currently) |
| Deposit Date | 5 | 6 | Format: YYMMDD (not used currently) |
| Number of Checks | 11 | 4 | Total number of checks |
| Total Deposit | 15 | 10 | Deposit total. Implied decimal of 2 places, added after parsing |
Utility Billing Multi-line BB&T Specification
This lockbox provider type uses the same fields as the multi-line specification, but does not contain a route number and sequence, and therefore can not determine which customer location the payment transaction is supposed to be related to. The most recently modified customer location with a relationship to the customer will be associated to the payment that is created. Only the stub record section has any formatting differences from the multi-line specification.
Check Record
Same as Multi-line specification
Stub Record
| Field | Start | Length | Comments |
|---|---|---|---|
| Record Type | 1 | 1 | Same as Multi-line specification |
| Batch Number | 2 | 3 | Same as Multi-line specification |
| payment Amount | 5 | 10 | Same as Multi-line specification |
| Account Number | 15 | 10 | Same as Multi-line specification |
| Transaction Number | 25 | 2 | Sequence number of the check in the transaction |
| Customer Name | 27 | 27 | Not necessary, customer name will be filled out regardless |
Trailer Record
Same as Multi-line specification
Utility Billing Simple Layout (Layout used in ICS)
Header Record
| Field | Start | Length | Comments |
|---|---|---|---|
| Record Type | 1 | 1 | Should be "H" |
| Process Date | 2 | 8 | Used as Payment date(Format : MMDDYYYY) |
| RPS_ID (District Number) | 10 | 4 | |
| Filler (Spaces) | 14 | 67 |
Detail Record
| Field | Start | Length | Comments |
|---|---|---|---|
| Account Number | 1 | 9 | SmartFusion customer account number |
| Payment Amount | 10 | 9 | implied decimal (2 place values) |
| Filler (Spaces) | 19 | 62 |
Trailer Record
| Field | Start | Length | Comments |
|---|---|---|---|
| Record Type | 1 | 1 | Should be "T" |
| Total Amount | 2 | 12 | Deposit total. Implied decimal of 2 places, added after parsing |
| Total Items | 14 | 5 | Total number of checks |
| Filler (Spaces) | 19 | 62 |
Utility Billing - Wells Fargo Online Payments
This feature is only available if the Wells Fargo Online Payments is enabled in the interface section of the license file.
The file is a fixed length file.
File Structure
- FH - File header (Not used)
- PTH - Payment type header (Not used)
- BH - Batch header (Not used)
- PR - Payment - (Account Number, Amount, Date)
- PPR - Payment payor name and address (Name)
- PNT - Payment level notes (Route, Sequence, Suffix, Extension)
- BT- Batch trailer (Not used)
- FT- File trailer (Not used)
Utility Billing AQ2 Specification
| Detail Record | Beginning Position | Length | Comment |
|---|---|---|---|
| Record Type | 1 | 1 | "D" |
| Batch Number | 2 | 3 | Numeric |
| Check Amount | 5 | 10 | Numeric (implied decimal) |
| Trans Number | 15 | 9 | Numeric |
| Account Number | 24 | 9 | Numeric |
| Check Digit(s) | 33 | 2 | Numeric, can be defaulted to 00 |
| Check Number | 35 | 10 | Numeric |
| Customer Name | 45 | 20 | Alphanumeric |
| Filler | 65 | 15 | Spaces |
| Trailer Record | Beginning Position | Length | Comment |
|---|---|---|---|
| Record Type | 1 | 1 | "T" |
| Batch Number | 2 | 3 | Numeric |
| Lockbox Number | 5 | 3 | Numeric |
| Deposit Date | 8 | 6 | YYMMDD |
| Number of Checks | 14 | 4 | Numeric |
| Lockbox Total | 18 | 10 | Numeric (implied decimal) |
| Filler | 28 | 52 | Spaces |
Tax Standard Specification
| Header Record | Length | Comment |
|---|---|---|
| Record Type | 1 | Should be "H" |
| Delimiter | 1 | Should be "," |
| Lockbox Number | 5 |
| Detail Record | Length | Comment |
|---|---|---|
| Record Type | 1 | Should be "D" for detail |
| Delimiter | 1 | Should be "," |
| OCR Account number | 15 | Year 4 chars + Installment 1 Char (0-9 only) + receipt number 10 chars (left pad '0') |
| Delimiter | 1 | Should be "," |
| Amount Paid | 9 | Leading zero and implied decimal (2 place values) |
| Delimiter | 1 | Should be "," |
| Check Number | 10 | Leading zeros |
| Trailer Record | Length | Comment |
|---|---|---|
| Record Type | 1 | Should be "T" |
| Delimiter | 1 | Should be "," |
| Number of Transactions | 9 | Leading zeros |
| Delimiter | 1 | Should be "," |
| Total of amounts | 9 | Leading zeros implied decimal (2 place values) |
Generating Sample File Imports for Testing
Changes Prior to Version 6.140
6.132 - modified the logic of the lockbox import for single line and BB&T formats to first go to the first active location, sorted by balance (largest first), then start date (latest first). If there is not active location, it will then try to apply the payment to the first finalized location with a balance, again sorted by balance (largest first), then start date (latest first). If either of those conditions are not met, then it will go to the location with the latest start date.
6.131 - Updated the tax discount calculation to use Number of Days for County tax customers and Discount End Date for non-County tax customers.
6.127 - Added the option to import using "Simple layout".
6.73 - Change the single line import to always apply the payment on the newest active location (by start date). If no active locations exist, the payment will go to the location with the most current start date regardless of status.
6.52
- Import lock boxes will now assign an update number that can be used to rollback the transaction and cash collection records that were created from the import.
- Added the "Include route suffix" and "Include route extension" check boxes. These allow for the user to include the route suffix and/or the route extension in their lockbox import specification. If "Include route suffix" is checked, SmartFusion will require the route suffix AND route extension to be included in the specification, if "Include route extension" is only checked, SmartFusion will require the route extension to be included in the specification. If neither of these new options are selected, SmartFusion will operate as normally when using the multi-line lockbox provider. These options are only available when in the Utility Billing module AND when using the multi-line lockbox provider.
6.50 - Set up the Multi-line import to take the rest of the line and extract the number out of there.
6.40 - When on demand penalty and interest is enabled the pending amounts will be included and the associated transactions will be created whether they were paid off or not.
6.27
- Changed the multiline and BB&T imports to use the deposit date in the trailer record as the receipt date.
- Disabled the Process Date control when in UB and using multiline or BB&T import types.