Lockbox Interface Table AR_PAYMENTS_INTERFACE

TRANSMISSION_RECORD_ID

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: None.

CREATION_DATE

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: None.

CREATED_BY

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: AR_BATCHES.CREATED_BY, AR_INTERIM_CASH_RECEIPTS.CREATED_BY, or AR_INTERIM_CASH_RECEIPT_LINES.CREATED_BY.

LAST_UPDATE_LOGIN

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: None.

LAST_UPDATED_BY

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: None.

LAST_UPDATE_DATE

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: None.

RECORD_TYPE

Enter the record type. For example, if this is a batch header record, and your bank uses the value 3 to identify batch headers, enter 3 in this column.

validation: Find out from the bank what character they use to identify each record type. Keep in mind that not all banks use all of the record types. Assign values to identify the following types of records: TRANSMISSION HEADER, TRANSMISSION TRAILER, LOCKBOX HEADERS, LOCKBOX TRAILERS, BATCH HEADERS, BATCH TRAILERS, PAYMENT RECORDS, PAYMENT OVERFLOW RECORDS, and SERVICE HEADER.
destination: None.

STATUS

Enter the value AR_PLB_NEW_RECORD_INF for all records inserted into this table. The control files that Receivables provides populate this column.

validation: The import file must leave this column blank.
destination: None.

TRANSMISSION_REQUEST_ID

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: None.

TRANSMISSION_ID

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: None.

DESTINATION_ACCOUNT

Enter the account number of the sending bank.

validation: None.
destination: AR_TRANSMISSIONS.DESTINATION

ORIGINATION

Enter the transit routing number of the sending bank.

validation: If this value is included in a transmission header or trailer, you must have the same value here.
destination: AR_TRANSMISSIONS.ORIGIN

DEPOSIT_DATE

Enter the date on which the transmission was deposited into your bank account. This date can be on any of the record types in the transmission.

Each unique deposit date determines a batch of transmission records. For example, if you enter two unique deposit dates for the transmission, lockbox divides the transmission into two batches of receipts.

validation: None.
destination: AR_BATCHES.DEPOSIT_DATE

GL_DATE

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: AR_BATCHES.GL_DATE, AR_INTERIM_CASH_RECEIPTS.GL_DATE, or AR_CASH_RECEIPT_HISTORY.GL_DATE.

DEPOSIT_TIME

Enter the time the deposit was made.

validation: None.
destination: None.

TRANSMISSION_RECORD_COUNT

Enter the number of records in the import file. Include all of the types of records in the count: headers, trailers, receipts, and overflow records.

validation: If the transmission format includes the transmission header or trailer, lockbox counts all records in this transmission. The validated count includes all receipts and detail records transferred to the interim table.
destination: AR_TRANSMISSIONS.COUNT

TRANSMISSION_AMOUNT

Enter the amount of the transmission.

validation: The sum of all of the receipt amounts within the transmission.
destination: AR_TRANSMISSIONS.AMOUNT

TRANSFERRED_RECEIPT_COUNT

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: AR_TRANSMISSIONS.VALIDATED_COUNT

TRANSFERRED_RECEIPT_AMOUNT

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: AR_TRANSMISSIONS.VALIDATED_AMOUNT

LOCKBOX_NUMBER

For lockbox header or trailer records, enter the lockbox name or number specified by the bank.

For batch header and trailer records, enter the lockbox number assigned to receipts in the batch.

For receipt records, enter the lockbox number assigned to receipts.

For overflow records, enter the number of the lockbox for the receipt.

validation: This column is required on all lockbox headers and trailers. If the lockbox number is included in the lockbox transmission format, it must appear on every batch record. If the lockbox number is included in the lockbox transmission format and you don't have batch records, it must be entered for each receipt and overflow record.
destination: None.

LOCKBOX_BATCH_COUNT

Enter the number of batches in the lockbox.

validation: None.
destination: None.

LOCKBOX_RECORD_COUNT

Enter the number of payment records in the lockbox.

validation: Don’t include payment overflow records.
destination: None.

LOCKBOX_AMOUNT

Enter the total value of the receipts in the lockbox.

validation: None.
destination: None.

BATCH_NAME

For batch header and trailer records, enter the name or number that the bank uses to identify the batch.

For receipt records, enter the batch name for this receipt.

For overflow records, enter the batch for this overflow record.

validation: This column is required for each batch header and trailer record. If the batch name is included in your format, it must be entered for every receipt record. Each unique batch name determines a batch of transmission records. For example, if you enter two unique batch names for your transmission, lockbox divides your transmission into two batches of receipts. If the batch name is included in your format, you must enter this name for each overflow record.
destination: AR_BATCHES.LOCKBOX_BATCH_NAME

BATCH_AMOUNT

Enter the total value of all receipts in this batch.

validation: None.
destination: AR_BATCHES.CONTROL_AMOUNT

BATCH_RECORD_COUNT

Enter the number of receipt records in this batch.

validation: None.
destination: AR_BATCHES.CONTROL_COUNT

ITEM_NUMBER

Enter a sequential number to indicate the location of each receipt or overflow record in the batch.

validation: This column is required, even if the lockbox transmission format doesn't have batch, lockbox, or transmission records. The item number must be unique within a batch, a lockbox (if batches aren’t provided), or within a transmission (if neither batches nor lockboxes are provided). All overflow records for a receipt must have the same item number as the receipt record. You must enter an item number for each overflow record to reference the receipt.
destination: None.

CURRENCY_CODE

Enter the currency for each receipt.

validation: None.
destination: AR_BATCHES.CURRENCY_CODE or AR_INTERIM_CASH_RECEIPTS.CURRENCY_CODE.

EXCHANGE_RATE

For receipts, enter the conversion rate to use for this currency.

validation: None.
destination: AR_BATCHES.EXCHANGE_RATE or AR_INTERIM_CASH_RECEIPTS.EXCHANGE_RATE.

EXCHANGE_RATE_TYPE

Enter the conversion rate type to use for the receipt: Corporate, Spot, or User.

validation: None.
destination: AR_BATCHES.EXCHANGE_RATE_TYPE or AR_INTERIM_CASH_RECEIPTS.EXCHANGE_RATE_TYPE.

REMITTANCE_AMOUNT

Enter the value of each receipt in the batch.

validation: A value is required for each receipt record.
destination: AR_INTERIM_CASH_RECEIPTS.AMOUNT

TRANSIT_ROUTING_NUMBER

Enter the transit routing number from the receipt.

validation: This column is optional, but you must enter this number if you enter the account number. Receivables uses the transit routing number and account number together to identify the customer MICR number.
destination: AP_BANK_BRANCHES.BANK_NAME, AP_BANK_BRANCHES.BANK_BRANCH_NAME, or AP_BANK_BRANCHES.BANK_NUM

ACCOUNT

Enter the bank account number from the receipt.

validation: This column is optional, but you must enter this number if you enter the transit routing number. Receivables uses the transit routing number and account number together to identify the customer MICR number.
destination: AP_BANK_ACCOUNTS.BANK_ACCOUNT_NUM

CUSTOMER_BANK_ACCOUNT_ID

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: AR_INTERIM_CASH_RECEIPTS.CUSTOMER_BANK_ACCOUNT_ID

ANTICIPATED_CLEARING_DATE

Date a receipt is expected to clear.

validation: None.
destination: AR_INTERIM_CASH_RECEIPTS.ANTICIPATED_CLEARING_DATE

CHECK_NUMBER

Enter the check number printed on the receipt.

validation: A value is required for each receipt record.
destination: AR_INTERIM_CASH_RECEIPTS.RECEIPT_NUMBER or AR_CASH_RECEIPTS.RECEIPT_NUMBER.

SPECIAL_TYPE

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: AR_INTERIM_CASH_RECEIPTS.SPECIAL_TYPE

AUTOAPPLY_FLAG

Indicates whether the AutoApply process is used during lockbox processing.

validation: None.
destination: AR_INTERIM_CASH_RECEIPTS.AUTOAPPLY_FLAG

CUSTOMER_NUMBER

Enter the customer account number.

validation: This column is optional.
destination: None.

OVERFLOW_INDICATOR

Receivables uses this column to indicate overflow records for the current receipt. You must enter a value for all overflow records.

validation: To identify the last overflow record, enter a value different from the overflow indicator in the transmission format. For example, in the BAI transmission format, 0 indicates an overflow record. You have three overflow records for a receipt, the first two records have 0 as the overflow indicator and the third record has 9. Since the third record isn’t 0, this record is identified as the last overflow record.
destination: None.

OVERFLOW_SEQUENCE

Enter a sequential number to indicate the order of overflow records.

validation: Within each receipt, the Overflow Sequence usually begins with 1.
destination: None.

CUSTOMER_ID

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: AR_INTERIM_CASH_RECEIPTS.PAY_FROM_CUSTOMER or AR_CASH_RECEIPTS.PAY_FROM_CUSTOMER.

BILL_TO_LOCATION

Enter the customer bill-to site for this receipt and include the bill-to site in the transmission format.

validation: If the Require billing location for receipts Receivables system option is enabled, you must enter a value in this column. If the Require billing location for receipts Receivables system option isn’t enabled, you only have to enter a value in this column if the Require billing location option on the lockbox record is enabled.
destination: None.

CUSTOMER_SITE_USE_ID

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: AR_INTERIM_CASH_RECEIPTS.SITE_USE_ID or AR_CASH_RECEIPTS.CUSTOMER_SITE_USE_ID.

RECEIPT_DATE

For receipt records, enter the date written on the check.

validation: If you’re using MICR numbers to identify customers, this date must be equal to or earlier than the date of the lockbox submission. Otherwise, the receipts are processed as unidentified.
destination: AR_INTERIM_CASH_RECEIPTS.RECEIPT_DATE, AR_INTERIM_CASH_RECEIPTS.EXCHANGE_DATE, AR_CASH_RECEIPTS.RECEIPT_DATE, or AR_CASH_RECEIPTS.EXCHANGE_DATE.

RECEIPT_METHOD

Enter the receipt method to associate with a receipt.

validation: Receipt methods contain information about the bank, bank account, and receipt distributions. The receipt method in this column must be the same as the receipt method assigned to the batch source for the lockbox.
destination: None.

RECEIPT_METHOD_ID

Receivables assigns a value to this column during the import process.

validation: The import file must leave this column blank.
destination: AR_INTERIM_CASH_RECEIPTS.RECEIPT_METHOD_ID or AR_CASH_RECEIPTS.RECEIPT_METHOD_ID.

INVOICE1-8

For receipt records and overflow records, optionally enter the invoice numbers that a receipt is applied to.

validation: You don't have to start with INVOICE1, nor use all eight of the INVOICE columns on a record before you create a receipt record or an overflow record. You can find a list of valid values in AR_PAYMENT_SCHEDULES.TRX_NUMBER. You can supply invoice numbers without specifying the amount applied to each invoice.
destination: None.

MATCHING1_DATE - MATCHING8_DATE

For matching dates.

validation
destination: None.

RESOLVED_MATCHING_NUMBER1-8

For resolved matching numbers.

validation
destination: None.

RESOLVED_MATCHING1_DATE - RESOLVED_MATCHING8_DATE

For resolved matching dates.

validation
destination: None.

MATCH_RESOLVED_USING

For a match resolved.

validation: GD: No description or validation information is provided.
destination: None.

RESOLVED_MATCHING1_INSTALLMENT - RESOLVED_MATCHING8_INSTALLMENT

For resolved matching installments.

validation
destination: None.

INVOICE1_STATUS - INVOICE8_STATUS

Status of the invoices that a receipt is applied to.

validation: None.
destination: None.

COMMENTS

For batch header and trailer records and for receipt records, enter any free-form comments.

validation: For receipt records, Receivables stores these comments but doesn't display them on the receipts.
destination: AR_BATCHES.COMMENTS or AR_INTERIM_CASH_RECEIPTS.COMMENTS

ATTRIBUTE_CATEGORY

Enter the descriptive flexfield category information for this receipt.

validation: Descriptive flexfield category used to transfer additional information about a receipt.
destination: AR_INTERIM_CASH_RECEIPTS.ATTRIBUTE1-15 or AR_CASH_RECEIPTS.ATTRIBUTE1-15.

ATTRIBUTE1-15

Enter the descriptive flexfield attributes of the category designated in the ATTRIBUTE_CATEGORY column.

validation: Use this column to transfer additional information about a receipt. For example, if the bank enters and transmits the customer name, use an attribute column to import this name.
destination: AR_INTERIM_CASH_RECEIPTS.ATTRIBUTE1-15 or AR_CASH_RECEIPTS.ATTRIBUTE1-15.

INVOICE1_INSTALLMENT - INVOICE8_INSTALLMENT

For receipt records and overflow records, enter the installment numbers of invoices with multiple payment schedules that a receipt is applied to.

validation: If you don't specify the installment number for an invoice with multiple payment schedules, then Receivables applies the receipt amount to the oldest payment schedule first. The installment number must be on the same record as the associated invoice number.
destination: None.

CUSTOMER_NAME_ALT

The alternate name of the customer.

validation: None.
destination: None.

CUSTOMER_BANK_NAME

The name of the customer bank. Used for receipt records.

validation: None.
destination: None.

CUSTOMER_BANK_BRANCH_NAME

The name of the customer bank branch. Used for receipt records.

validation: None.
destination: None.

REMITTANCE_BANK_NAME

The name of the bank that received the payment. Used for receipt records.

validation: None.
destination: None.

BANK_TRX_CODE

The transaction code of the bank.

validation: None.
destination: None.

AMOUNT_APPLIED1-8

Enter the amount of the receipt to apply to the invoice. If the receipt currency and the transaction currency are different, enter the amount of the receipt to apply in the transaction currency.

validation: If you provide invoice numbers without specifying the amount applied to each invoice, Receivables applies the receipt to the invoices starting with the oldest receipt schedule first. The value of the AMOUNT_APPLIED column must be on the same record as the invoice number the receipt is applied to. For example, you can't have all of the invoice numbers on the receipt record and all of the amounts applied on the overflow record.
destination: AR_INTERIM_CASH_RECEIPTS_ALL.AMOUNT_APPLIED (if a single application) or AR_INTERIM_CASH_RCPT_LINES_ALL.PAYMENT_AMOUNT (if multiple applications).

AMOUNT_APPLIED_FROM1-8

For receipt and overflow records, if the receipt currency and the transaction currency are different, enter the amount of the receipt to apply in the receipt currency.

validation: None.
destination: AR_INTERIM_CASH_RECEIPTS_ALL.AMOUNT (if a single application) or AR_INTERIM_CASH_RCPT_LINES_ALL.AMOUNT_APPLIED_FROM (if multiple applications).

INVOICE_CURRENCY_CODE1-8

For receipt and overflow records, if the receipt currency and the transaction currency are different, enter the currency of the transaction. This column is used for cross-currency receipt applications.

validation: This column is optional. If null, lockbox derives this value from AR_PAYMENT_SCHEDULES_ALL.
destination: AR_INTERIM_CASH_RECEIPTS_ALL.INVOICE_CURRENCY_CODE (if a single application) or AR_INTERIM_CASH_RCPT_LINES_ALL.INVOICE_CURRENCY_CODE (if multiple applications).

TRANS_TO_RECEIPT_RATE1-8

For receipt and overflow records, if the receipt currency and the transaction currency are different, enter the conversion rate used to convert the receipt to the transaction currency.

validation: This value is used for cross-currency receipt applications when the receipt and transaction currencies don’t have a fixed conversion rate.
destination: TRANS_TO_RECEIPT_RATE.

CUSTOMER_REFERENCE_1-8

For customer reference.

validation
destination

CUSTOMER_REASON1-8

For customer reason codes.

validation
destination