Receiving Information from a Lockbox
This topic provides an overview of the lockbox interface and describes the following record layouts for the fixed-file format design of the lockbox processor:
Header record.
Service record.
Lockbox record.
Batch record (Record Type-1).
(Optional) Payment Detail 2 (Check Information).
(Optional) Payment Detail 3 (Customer Information).
(Optional) Payment Detail 4 (Customer Information).
(Optional) Payment Detail 5 (Customer Information).
(Optional) Payment Detail 6 (Customer Information).
(Optional) Payment Detail 7 (Invoice Information).
(Optional) Payment Detail 8 (Invoice Information).
(Optional) Payment Detail 9 (Invoice Information).
When implementing the lockbox interface, the most important issue is the record layout of the payment transactions that are received from the bank. The PeopleSoft interface record layout is extensive. However, you must use only the pieces of it that pertain to the implementation. The record layout is a version of the Bank Administration Institute standards, modified to contain all the information that the PeopleSoft Receivables system accepts.
The record layout has a maximum total of 12 records for one payment, but only the first five records are required: the four control records and the first payment record. If all the information that you need is contained in one or two of the optional customer and invoice records, then the system processes only that amount of information. For example, you may need only the invoice number, MICR ID, and payment amount information to match payments with invoices. If this is the case, then the bank only has to send three of the 10 payment detail records.
The other records contain transmission information, such as the number of batches (or deposits) in the file and the originating bank identifiers. Transmission information will probably be the same every time a lockbox file is sent. Also, batch information is sent to audit the record count. You can have multiple batches (or deposits) in one transmission.
The record layouts for the fixed file format design of the lockbox processor must be given to the banks that receive lockbox payments so that they can send information to you in a format that the system can read. PeopleSoft has written the Lockbox SQR process (AR25001) for you, but it works only if the incoming file follows its design layout.
Record Group ID Assignments
A record group ID is part of the lockbox record layout. It identifies multiple lines of customer information or item information for a single payment. The governing principle in assigning record group IDs is the number of rows that is inserted into PAYMENT_ID_ITEM and into PAYMENT_ID_CUST, the two records that store item and customer information for payments.
Note: The Lockbox process (AR25001) expects records to be in proper sequence in the input file. In particular, detail record types 07, 08, and 09 (for Invoice Information) need to be grouped together and in the proper sequence.
This example of a lockbox file breaks down the format of the records. It has 8 records. The first two are Record Type 1 – Header, and Record Type 2 – Service. The next six records are Record Type 3 – Detail Records. The Detail Record Type field value is in positions 30-31. After the first two records, the diagram shows a 00 detail record (Lockbox) and a 01 detail record (Batch Record). Then there are four detail records of type 02, 03 (Customer) and 07, 08 (Invoice). Notice that the detail types are in numerical order.
This table lists the layout for the header record:
|
Description |
Type |
Size |
Column |
Database Record Field |
|---|---|---|---|---|
|
Record Type, 1 = Header |
Char |
1 |
1 |
Used during processing |
|
Destination ID / Lockbox Recipient |
Char |
20 |
2–21 |
Used during processing |
|
Total Number of Lockboxes |
Num |
5 |
22–26 |
Used during processing |
|
Total Payment Amount |
Num |
16 |
27–42 |
Used during processing |
|
Transmission Date (MMDDYYYY) |
Date |
8 |
43–50 |
LOCKBOX_RUN_LOG.LOCKBOX_TRANS_DT |
|
Transmission Time (HHMMSS) |
Time |
6 |
51–56 |
LOCKBOX_RUN_LOG.LOCKBOX_TRANS_TIME |
This table lists the layout for the service record:
|
Description |
Type |
Size |
Column |
Record Field |
|---|---|---|---|---|
|
Record Type, 2 = Service |
Char |
1 |
1 |
Used during processing |
|
Destination ID / Lockbox Recipient |
Char |
20 |
2–21 |
Used during processing |
|
Bank Transmission ID |
Char |
20 |
22–41 |
Used during processing |
|
Service Type, 400 = Lockbox |
Char |
3 |
42–44 |
Used during processing |
|
Record Length (102) |
Num |
3 |
45–47 |
Used during processing |
|
Characters per Block (0102) |
Num |
4 |
48–51 |
Used during processing |
|
Format, 1 = Fixed Field Format |
Num |
1 |
52 |
Used during processing |
This table lists the layout for the lockbox record:
|
Description |
Type |
Size |
Column |
Record Field |
|---|---|---|---|---|
|
Record Type, 3 = Detail |
Char |
1 |
1 |
Used during processing |
|
Lockbox ID |
Char |
15 |
2–16 |
LOCKBOX_RUN_LOG.LOCKBOX_ID |
|
Batch ID, Unique Within Batch |
Char |
5 |
17–21 |
DEPOSIT_CONTROL.LOCKBOX_BATCH_ID |
|
Sequence Num, 0 = Lockbox Record |
Num |
5 |
22–26 |
Used during processing |
|
Record Group ID (0000) |
Num |
4 |
27–30 |
Used during processing |
|
Detail Record Type (00) |
Char |
2 |
31–32 |
Used during processing |
|
Total Number of Batches |
Num |
6 |
33–38 |
LOCKBOX_RUN_LOG.CONTROL_BATCH_CNT |
|
Total Payment Amount |
Num |
16 |
39–54 |
LOCKBOX_RUN_LOG.CONTROL_PAY_AMT |
|
Total Number of Payments |
Num |
6 |
55–60 |
LOCKBOX_RUN_LOG.CONTROL_PAY_CNT |
|
Deposit Business Unit |
Char |
5 |
61–65 |
LOCKBOX_RUN_LOG.DEPOSIT_BU DEPOSIT_CONTROL.DEPOSIT_BU PAYMENT.DEPOSIT_BU PAYMENT_ID_ITEM.DEPOSIT_BU PAYMENT_ID_CUST.DEPOSIT_BU PAY_MISC_DST.DEPOSIT_BU |
|
Bank |
Char |
20 |
66–85 |
DEPOSIT_CONTROL.BNK_ID_NBR |
|
Bank Account |
Char |
17 |
86–102 |
DEPOSIT_CONTROL.BANK_ACCOUNT_NUM |
This table lists the layout for the batch record:
|
Description |
Type |
Size |
Column |
Record Field |
|---|---|---|---|---|
|
Record Type, 3 = Detail |
Char |
1 |
1 |
Used during processing |
|
Lockbox ID |
Char |
15 |
2–16 |
LOCKBOX_RUN_LOG.LOCKBOX_ID |
|
Batch ID |
Char |
5 |
17–21 |
DEPOSIT_CONTROL.LOCKBOX_BATCH_ID |
|
Sequence Number, 0 = Batch Record |
Num |
5 |
22–26 |
Used during processing |
|
Record Group ID (0000) |
Num |
4 |
27–30 |
Used during processing |
|
Detail Record Type (01) |
Char |
2 |
31–32 |
Used during processing |
|
Total Batch Amount |
Num |
15 |
33–47 |
DEPOSIT_CONTROL.CONTROL_AMT |
|
Total Number of Payments |
Num |
6 |
48–53 |
DEPOSIT_CONTROL.CONTROL_CNT |
|
Deposit Date (MMDDYYYY) |
Date |
8 |
54–61 |
DEPOSIT_CONTROL.ACCOUNTING_DT |
|
Payment Currency |
Char |
3 |
62–64 |
PAYMENT.PAYMENT_CURRENCY |
This table lists the layout for the payment detail 2 record:
|
Description |
Type |
Size |
Column |
Record Field |
|---|---|---|---|---|
|
Record Type, 3 = Detail |
Char |
1 |
1 |
Used during processing |
|
Lockbox ID |
Char |
15 |
2–16 |
LOCKBOX_RUN_LOG.LOCKBOX_ID |
|
Batch ID |
Char |
5 |
17–21 |
DEPOSIT_CONTROL.LOCKBOX_BATCH_ID |
|
Payment Sequence Number |
Num |
5 |
22–26 |
PAYMENT.PAYMENT_SEQ_NUM |
|
Record Group ID |
Num |
4 |
27–30 |
Used during processing |
|
Detail Record Type (02) |
Char |
2 |
31–32 |
Used during processing |
|
Payment ID / Check Number |
Char |
15 |
33–47 |
PAYMENT.PAYMENT_ID |
|
Payment Amount |
Char |
14 |
48–61 |
PAYMENT.PAYMENT_AMT |
|
Payment Date (MMDDYYYY) |
Char |
8 |
62–69 |
PAYMENT.ACCOUNTING_DT |
|
Payment Method |
Char |
3 |
70–72 |
PAYMENT.PAYMENT_METHOD |
|
Payment Currency |
Char |
3 |
73–75 |
PAYMENT.PAYMENT_CURRENCY |
This table lists the layout for the payment detail 3 record:
|
Description |
Type |
Size |
Column |
Record Field |
|---|---|---|---|---|
|
Record Type, 3 = Detail |
Char |
1 |
1 |
Used during processing |
|
Lockbox ID |
Char |
15 |
2–16 |
LOCKBOX_RUN_LOG.LOCKBOX_ID |
|
Batch ID |
Char |
5 |
17–21 |
DEPOSIT_CONTROL.LOCKBOX_BATCH_ID |
|
Payment Sequence Number |
Num |
5 |
22–26 |
PAYMENT.PAYMENT_SEQ_NUM PAYMENT_ID_CUST.PAYMENT_SEQ_NUM |
|
Record Group ID |
Num |
4 |
27–30 |
PAYMENT_ID_CUST.LOCKBOX_RECGRP_NUM |
|
Detail Record Type (03) |
Char |
2 |
31–32 |
Used during processing |
|
Customer Business Unit |
Char |
5 |
33–37 |
PAYMENT_ID_CUST.BUSINESS_UNIT |
|
Customer ID |
Char |
13 |
38–50 |
PAYMENT_ID_CUST.CUST_ID |
|
MICR Number |
Char |
30 |
51–80 |
PAYMENT_ID_CUST.MICR_ID |
Note: On the Payment Detail 3 Lockbox record, the Customer Business Unit must be within the same SetID as the Deposit Business Unit. If the Customer is not found with the SetID using the Deposit Business Unit as the Set Control Value, the Customer Reference record is invalid and will not be created.
This table lists the layout for the payment detail 4 record:
|
Description |
Type |
Size |
Column |
Record Field |
|---|---|---|---|---|
|
Record Type, 3 = Detail |
Char |
1 |
1 |
Used during processing |
|
Lockbox ID |
Char |
15 |
2–16 |
LOCKBOX_RUN_LOG.LOCKBOX_ID |
|
Batch ID |
Char |
5 |
17–21 |
DEPOSIT_CONTROL.LOCKBOX_BATCH_ID |
|
Payment Sequence Number |
Num |
5 |
22–26 |
PAYMENT.PAYMENT_SEQ_NUM PAYMENT_ID_CUST.PAYMENT_SEQ_NUM |
|
Record Group ID |
Num |
4 |
27–30 |
PAYMENT_ID_CUST.LOCKBOX_RECGRP_NUM |
|
Detail Record Type (04) |
Char |
2 |
31–32 |
Used during processing |
|
Remit From SetID |
Char |
5 |
33–37 |
PAYMENT_ID_CUST.REMIT_FROM_SETID |
|
Remit From Customer ID |
Char |
15 |
38–52 |
PAYMENT_ID_CUST.REMIT_FROM_CUST_ID |
|
Corporate SetID |
Char |
5 |
53–57 |
PAYMENT_ID_CUST.CORPORATE_SETID |
|
Corporate Customer ID |
Char |
15 |
58–72 |
PAYMENT_ID_CUST.CORPORATE_CUST_ID |
This table lists the layout for the payment detail 5 record:
|
Description |
Type |
Size |
Column |
Record Field |
|---|---|---|---|---|
|
Record Type, 3 = Detail |
Char |
1 |
1 |
Used during processing |
|
Lockbox ID |
Char |
15 |
2–16 |
LOCKBOX_RUN_LOG.LOCKBOX_ID |
|
Batch ID |
Char |
5 |
17–21 |
DEPOSIT_CONTROL.LOCKBOX_BATCH_ID |
|
Payment Sequence Number |
Num |
5 |
22–26 |
PAYMENT.PAYMENT_SEQ_NUM PAYMENT_ID_CUST.PAYMENT_SEQ_NUM |
|
Record Group ID |
Num |
4 |
27–30 |
PAYMENT_ID_CUST.LOCKBOX_RECGRP_NUM |
|
Detail Record Type (05) |
Char |
2 |
31–32 |
Used during processing |
|
Remit From Customer Name |
Char |
39 |
33–71 |
PAYMENT_ID_CUST.NAME1 |
|
D&B Number |
Char |
9 |
72–80 |
PAYMENT_ID_CUST.DB_NUMBER |
This table lists the layout for the payment detail 6 record:
|
Description |
Type |
Size |
Column |
Record Field |
|---|---|---|---|---|
|
Record Type, 3 = Detail |
Char |
2 |
1 |
Used during processing |
|
Lockbox ID |
Char |
15 |
2–16 |
LOCKBOX_RUN_LOG.LOCKBOX_ID |
|
Batch ID |
Char |
5 |
17–21 |
DEPOSIT_CONTROL.LOCKBOX_BATCH_ID |
|
Payment Sequence Number |
Num |
5 |
22–26 |
PAYMENT.PAYMENT_SEQ_NUM PAYMENT_ID_CUST.PAYMENT_SEQ_NUM |
|
Record Group ID |
Num |
4 |
27–30 |
PAYMENT_ID_CUST.LOCKBOX_RECGRP_NUM |
|
Detail Record Type (06) |
Char |
2 |
31–32 |
Used during processing |
|
Customer City |
Char |
30 |
33–62 |
PAYMENT_ID_CUST.CITY |
|
Customer Postal |
Char |
12 |
63–74 |
PAYMENT_ID_CUST.POSTAL |
This is the layout for the payment detail 7 record:
|
Description |
Type |
Size |
Column |
Record Field |
|---|---|---|---|---|
|
Record Type, 3 = Detail |
Char |
1 |
1 |
Used during processing |
|
Lockbox ID |
Char |
15 |
2–16 |
LOCKBOX_RUN_LOG.LOCKBOX_ID |
|
Batch ID |
Char |
5 |
17–21 |
DEPOSIT_CONTROL.LOCKBOX_BATCH_ID |
|
Payment Sequence Number |
Num |
5 |
22–26 |
PAYMENT.PAYMENT_SEQ_NUM PAYMENT_ID_CUST.PAYMENT_SEQ_NUM |
|
Record Group ID |
Num |
4 |
27–30 |
PAYMENT_ID_ITEM.LOCKBOX_RECGRP_NUM |
|
Detail Record Type (07) |
Char |
2 |
31–32 |
Used during processing |
|
Reference Qualifier |
Char |
2 |
33–34 |
PAYMENT_ID_ITEM.REF_QUALIFIER_CODE |
|
Reference Value |
Char |
30 |
35–64 |
PAYMENT_ID_ITEM.REF_VALUE |
|
Item Line |
Num |
6 |
65–70 |
PAYMENT_ID_ITEM.ITEM_LINE |
|
Entry Event |
Char |
10 |
71–80 |
PAYMENT_ID_ITEM.ENTRY_EVENT |
This table lists the layout for the payment detail 8 record:
|
Description |
Type |
Size |
Column |
Record Field |
|---|---|---|---|---|
|
Record Type, 3 = Detail |
Char |
1 |
1 |
Used during processing |
|
Lockbox ID |
Char |
15 |
2–16 |
LOCKBOX_RUN_LOG.LOCKBOX_ID |
|
Batch ID |
Char |
5 |
17–21 |
DEPOSIT_CONTROL.LOCKBOX_BATCH_ID |
|
Payment Sequence Number |
Num |
5 |
22–26 |
PAYMENT.PAYMENT_SEQ_NUM PAYMENT_ID_CUST.PAYMENT_SEQ_NUM |
|
Record Group ID |
Num |
4 |
27–30 |
PAYMENT_ID_ITEM.LOCKBOX_RECGRP_NUM |
|
Detail Record Type (08) |
Char |
2 |
31–32 |
Used during processing |
|
Total Invoice Amt |
Num |
14 |
33–46 |
PAYMENT_ID_ITEM.ITEM_AMT |
|
Amount of Invoice Paid |
Num |
14 |
47–60 |
PAYMENT_ID_ITEM.PAY_AMT |
|
Discount Amount Taken |
Num |
12 |
61–72 |
PAYMENT_ID_ITEM.DISC_TAKEN |
|
Invoice Date (MMDDYYYY) |
Date |
8 |
73–80 |
PAYMENT_ID_ITEM.ITEM_DT |
This table lists the layout for the payment detail 9 record:
|
Description |
Type |
Size |
Column |
Record Field |
|---|---|---|---|---|
|
Record Type, 3 = Detail |
Char |
1 |
1 |
Used during processing |
|
Lockbox ID |
Char |
15 |
2–16 |
LOCKBOX_RUN_LOG.LOCKBOX_ID |
|
Batch ID |
Char |
5 |
17–21 |
DEPOSIT_CONTROL.LOCKBOX_BATCH_ID |
|
Payment Sequence Number |
Num |
5 |
22–26 |
PAYMENT.PAYMENT_SEQ_NUM PAYMENT_ID_CUST.PAYMENT_SEQ_NUM |
|
Record Group ID |
Num |
4 |
27–30 |
PAYMENT_ID_ITEM.LOCKBOX_RECGRP_NUM |
|
Detail Record Type (09) |
Char |
2 |
31–32 |
Used during processing |
|
Adjustment Amount |
Num |
14 |
33–46 |
PAYMENT_ID_ITEM.ADJUSTMENT_AMT |
|
Adjustment Entry Reason |
Char |
2 |
47–48 |
PAYMENT_ID_ITEM.ADJUSTMENT_REASON |
|
Reference Customer ID |
Char |
15 |
49–63 |
PAYMENT_ID_ITEM.CUST_ID |