Previous	Next		Contents	Index	Navigation	Glossary	Library

Each column in AR_PAYMENTS_INTERFACE has important, detailed information you need to successfully run AutoLockbox. The Destination column gives you the interim QuickCash tables and the actual Receivables applications tables to which the data is transferred from AR_PAYMENTS_INTERFACE.

Understanding the AR_PAYMENTS_INTERFACE Table

Assigning Values to Columns

Column Name Value

STATUS	Enter the value AR_PLB_NEW_RECORD for all records inserted into this table. The sample SQL*Loader control files Receivables provides fill this column in for you.
DEPOSIT_DATE	Enter the date on which this transmission was actually deposited into your bank account. This date can be on any of the record types in your transmission. Each unique deposit date determines a batch of transmission records. For example, if you enter two unique deposit dates for your transmission, AutoLockbox divides your transmission into two batches of receipts.
RECORD_TYPE	Identify your 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. Find out from your bank what character they use to identify each one. 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, SERVICE HEADER

Receivables lets you determine what information you want to include in you header, trailer, and receipt records. You can reference any of the above types when you define the different records for your transmission format. Below are examples of how you might want to define these.

Assigning Values to Transmission Header and Trailer Records

Column Name Value

TRANSMISSION _RECORD _COUNT	Enter the number of records that you are importing. Include all of the types of records in the count; headers, trailers, receipts and overflow records.
TRANSMISSION _AMOUNT	Enter the amount of the transmission. This is the sum of all of the receipt amounts within the transmission.
DESTINATION_ ACCOUNT	Enter your account number at the sending bank.
ORIGINATION	Enter the sending bank's transit routing number.
DEPOSIT_DATE	Enter the date this transmission was actually deposited in your bank account. When you use SQL*Loader to import your data, it converts the date to the Oracle date format.
DEPOSIT_TIME	Enter the time the deposit was made.

Assigning Values to Lockbox Header or Trailer Records

Column Name Value

LOCKBOX_ NUMBER	Enter the lockbox name or number that your bank specifies. This is the same value that you entered in the Lockboxes window. LOCKBOX_NUMBER is mandatory on all Lockbox Headers and Trailers.
LOCKBOX_ BATCH_COUNT	Enter the number of batches in this lockbox.
LOCKBOX_ RECORD_ COUNT	Enter the number of Payment records in this lockbox. Do not include Payment Overflow records.
LOCKBOX_ AMOUNT	Enter the total value of the receipts in this lockbox.
DESTINATION_ ACCOUNT	Enter your account number at the sending bank. If this value is included in a Transmission Header or Trailer, you must enter the same value.
ORIGINATION	Enter the sending bank's transit routing number. If this value is included in a Transmission Header or Trailer, you must have the same value here.

Assigning Values to Batch Header and Trailer Records

Column Name Value

BATCH_NAME	Enter the name or number that the bank uses to identify the batch. This is required for each Batch Header and Trailer record.
BATCH_ AMOUNT	Enter the total value of all receipts in this batch.
BATCH_ RECORD_ COUNT	Enter the number of receipt records in this batch.
LOCKBOX_ NUMBER	Enter the lockbox number assigned to receipts in this batch. If the lockbox number is included in your format, it must appear on every batch record.
COMMENTS	Enter any free-form comments about this batch.

Assigning Values to Receipt Records

Column Name Value

LOCKBOX_ NUMBER	Enter the lockbox number assigned to your receipts. If the lockbox number is included in your format and you do not have batch records, it must be entered for every receipt record.
BATCH_NAME	Enter the batch name for this receipt. If 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, AutoLockbox divides your transmission into two batches of receipts.
ITEM_NUMBER	Enter a sequential number to indicate the location of this receipt in this batch. You must enter a value even if your format does not have batch, lockbox, or transmission records. Item Number must be unique within a batch, a lockbox (if batches are not provided), or within a transmission (if neither batches nor lockboxes are provided).
REMITTANCE_ AMOUNT	Enter the value of the receipt. You must enter a value for each receipt record.
CURRENCY_ CODE	Enter the currency code for each receipt. Receivables supports AutoLockbox Transmission receipts in different currencies.
EXCHANGE_ RATE	Enter the exchange rate you want Receivables to use for this currency.
EXCHANGE_ RATE_TYPE	Enter the type of exchange rate you are using for this receipt. You can enter Corporate, Spot, or User.
RECEIPT_DATE	Enter the date that is written on your check. If you are using MICR numbers to identify customers, Lockbox requires that this date be equal to or earlier than the date of this AutoLockbox submission; otherwise, the receipts will be unidentified.
RECEIPT_ METHOD	Enter the payment method that you want to associate with this receipt. Payment methods contain information about your bank, bank account, and receipt accounts. This payment method must be the same as the one you assigned to the batch source for this lockbox.
CHECK_ NUMBER	Enter the number printed on the receipt. You must enter a value for each receipt record.
TRANSIT_ ROUTING_ NUMBER	Enter the transit routing number from the receipt. This is optional, but you must enter this number if you enter the account number. Receivables uses transit routing number and account number together to identify the customer.
ACCOUNT	Enter the account number from the receipt. This is optional, but you must enter this number if you enter the transit routing number.
CUSTOMER_ NUMBER	Enter the number assigned to your customer. This is optional.
INVOICE1-8	Enter the invoice numbers to which you apply this receipt. You do not have to start with INVOICE1, nor use all eight of the INVOICE columns on a record before you create a receipt record. You may find a list of valid values in AR_PAYMENT_SCHEDULES.TRX_NUMBER. Do not look at transactions whose class is PMT or GUAR. Invoice numbers are optional.
INVOICE1-8_ INSTALLMENT	Enter the installment number if your invoice has multiple payment schedules. If you do not specify the installment number for an invoice with multiple payment schedules, Receivables will apply to the oldest payment schedule first. The installment number must be on the same record as the associated invoice number.
AMOUNT_ APPLIED1-8	Enter the amount of the receipt to apply to the invoice. You may specify invoice numbers without specifying the amount applied to each of these invoices. If you specify 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 amount applied column's value must be on the same record as the invoice number to which it is applied. For example, you cannot have all of the invoice numbers on the receipt record and all of the amounts applied on the overflow. Applied amounts are optional.
COMMENTS	Enter any free-form comments about this receipt. Receivables stores this data, but does not display these comments in any of the receipt entry windows.
ATTRIBUTE_ CATEGORY	Enter the Descriptive Flexfield category information for this receipt.
ATTRIBUTE1-15	Enter the Descriptive Flexfield attributes for this category. You can use this column to transfer additional information about your receipt. For example, if your bank enters and transmits customer name, you can use an attribute column to import this name. The attributes are visible as Descriptive Flexfields in the Receipt windows.
BILL_TO_ LOCATION	To associate receipts with specific customer sites, enter the billing address for this receipt and include billing location in your transmission format. If the system option Require Billing Location for Receipts is set to Yes, you must enter a value here. In addition, you can set the Require Billing Location field to Yes in the Lockboxes window to require a billing location for a specific lockbox. The value of this field in the Lockboxes window will override the option at the system level. See: Lockboxes.
The name of the customer's bank.	CUSTOMER_ BANK_NAME

The name of the customer's bank branch.	CUSTOMER_ BANK_BRANCH NAME
REMITTANCE_ BANK_NAME	The name of the bank that received the payment.
REMITTANCE_ BANK_BRANCH_NAME	The name of the bank branch that received the payment.

Assigning Values To Overflow Records

Column Name Value

LOCKBOX_ NUMBER	Enter the number of the lockbox for this receipt. If the lockbox number is included in your format and you do not have any batch records, you must enter this number for each receipt and overflow record.
BATCH_NAME	Enter the batch for this overflow record. If the batch name is included in your format, you must enter this name for each overflow record.
ITEM_NUMBER	Enter a sequential number to indicate the location of the overflow record in this batch. All overflow records for a receipt have the same item number as the receipt record. You must enter an item number for each overflow record to reference the receipt.
OVERFLOW_ INDICATOR	Receivables uses this column to indicate overflow records for the current receipt. You determine your overflow indicator in your transmission format. To identify the last overflow record, enter a value that is different from your overflow indicator. 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 is not '0', it is identified as the last overflow record. You must enter a value for all overflow records.
OVERFLOW_ SEQUENCE	Enter a sequential number to indicate the order of overflow records. Within each receipt, the Overflow Sequence usually begins with 1.
INVOICE1-8	Enter the invoice numbers to which you apply this receipt. You do not have to start with INVOICE1, nor use all eight of the INVOICE columns on a record before you create an overflow record. You can find a list of valid values in AR_PAYMENT_SCHEDULES.TRX_NUMBER. Do not look at transactions whose class is PMT or GUAR. You may supply invoice numbers without specifying the amount applied to each invoice. Invoice numbers are optional.
INVOICE1-8_ INSTALLMENT	Enter the installment number if your invoice has multiple payment schedules. If you do not specify the installment number for an invoice with multiple payment schedules, then Receivables will apply to the oldest payment schedule first. The installment number must be on the same record as the associated invoice number.
AMOUNT_ APPLIED1-8	Enter the amount of the receipt to apply to the invoice. If you specify invoice numbers without specifying the amount applied to each invoice, Receivables applies the receipt to the invoices starting with the oldest receipt first. The value of the amount applied column must be on the same record as the invoice number to which the receipt amount is applied.

System Assigned Columns

Column Name	Type
TRANSMISSION_RECORD_ID	NUMBER
CREATION_DATE	DATE
CREATED_BY	NUMBER
LAST_UPDATE_LOGIN	NUMBER
LAST_UPDATED_BY	NUMBER
LAST_UPDATE_DATE	DATE
TRANSMISSION_REQUEST_ID	NUMBER
CUSTOMER_ID	NUMBER
SPECIAL_TYPE	CHAR(20)
GL_DATE	DATE
STATUS	CHAR(30)
INVOICE1-8_STATUS	CHAR(30)
RECEIPT_METHOD_ID	NUMBER(15)
TRANSMISSION_ID	NUMBER(15)
INVOICE1-8_STATUS	VARCHAR2(30)
CUSTOMER_BANK_ACCOUNT_ID	NUMBER(15)
CUSTOMER_SITE_USE_ID	NUMBER(15)
TRANSFERRED_RECEIPT_COUNT	NUMBER
TRANSFERRED_RECEIPT_AMOUNT	NUMBER
Table 1 - 24. System Assigned Columns

See Also