Payments Lockbox
A lockbox is a service provided by some banks to their business customers who receive a high volume of payments by check. The payments can be sent to an address at the bank, and the bank will deposit the checks in to the customer's account and then send the customer a file detailing all the payments received. Opentaps can then read such a file and create corresponding payments and apply them to your customers' invoices.
IMPORTANT: In most cases, the banks will send the payments in a text file format, and the format may vary slightly from bank to bank.
Working with Lockboxes
Go to the Manage Lockbox Batches in Financials to upload your lockbox file. You should then see it in the list of lockbox batches which are available for processing. Click on one of them to go to the Lockbox Batch Details, where you can review the individual payments and their allocations to the invoices. You can change the way the payments are allocated, but you cannot modify the original data from the lockbox file. As each payment item from the lockbox batch is processed, the outstanding amount of the lockbox batch will be decreased. Once the outstanding amount is zero, the lockbox batch is consider fully processed.
Lockbox processing will check the following:
- That the lockbox items (see below) have a routing number and account number corresponding to one of your payment methods. You need to setup your payment method as an EftAccount entity.
- That the lockbox totals match
- That this lockbox has not already been processed
- Other low level checks: no duplicate sequence, line length must be 80, the records are in the correct order.
When payments from lockboxes are processed, they will be assigned to the payment method associated with the routing and account number in the lockbox batch item. This causes the lockbox payments to be posted directly to the general ledger account of the payment method, rather than to undeposited receipts.
See also: Lockbox Tests
Lockbox File Format
The lockbox file is a fixed field flat file. The first character of each line describes what kind of data is contained in that line. For our purposes, we can consider the lockbox filed to be divided into several batches, with each batch beginning with a line which starts with a "5" and ending with a line which starts with a "7". Within each batch, each payment is on a line which starts with a "6", and each payment can be allocated to several customer invoices on lines which begin with a "4". There are additional fields in most of these lines, but they are not used except for the ones listed below.
Start of Batch
These are lines starting with a "5" and are mapped to the opentaps LockboxBatch entity. The following fields are used:
| Field Name | Start | End | Length | Example Value |
|---|---|---|---|---|
| Record type | 1 | 1 | 1 | 5 |
| Batch number | 2 | 4 | 3 | 014 |
Payment Items
These are lines starting with a "6" and are mapped to the opentaps LockboxBatchItem entity. The following fields are used:
| Field Name | Start | End | Length | Example Value |
|---|---|---|---|---|
| Record type | 1 | 1 | 1 | 6 |
| Batch number | 2 | 4 | 3 | 014 |
| Item number | 5 | 7 | 3 | 001 |
| Current date | 15 | 20 | 6 | 081118 |
| Destination Account Number | 20 | 30 | 10 | 1234567890 |
| Routing Number | 30 | 40 | 10 | 1234567890 |
| Check amount | 41 | 50 | 10 | 0000027994 |
| Check number | 69 | 80 | 12 | 000000008635 |
The routing and account numbers should match a payment method (EftAccount) of your organization.
The check number is expressed in cents, so 27994 is $279.94.
Payment Item Details
These are lines starting with a "4" and are mapped to the opentaps LockboxBatchItemDetail entity. These lines typically described how the payment items from above are to be applied to customers and their invoices. The following fields are used:
| Field Name | Start | End | Length | Example Value |
|---|---|---|---|---|
| Record type | 1 | 1 | 1 | 4 |
| Batch number | 2 | 4 | 3 | 014 |
| Item number | 5 | 7 | 3 | 001 |
| Detail number | 9 | 10 | 2 | 01 |
| Invoice number | 11 | 20 | 10 | 0000556632 |
| Invoice amount | 21 | 30 | 10 | 27994 |
| Customer number | 61 | 70 | 10 | 0000012943 |
As with the payment items from above, the invoice amount is expressed in sense, so 27994 is $279.94.
End of Batch
These are lines starting with a "7" and are also mapped to the opentaps LockboxBatch entity. They follow at the bottom of all the payment items and payment item details and wrap up a batch, matching the lines which start with a "5". The following fields are used:
| Field Name | Start | End | Length | Example Value |
|---|---|---|---|---|
| Record type | 1 | 1 | 1 | 7 |
| Batch count | 21 | 23 | 4 | 064 |
| Batch amount | 24 | 33 | 10 | 0001023877 |
The batch amount is also in cents, so the amount above his $10,238.77.
