Results for “ar_payments_interface_u1”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AR.AR_PAYMENTS_INTERFACE_ALL is the Oracle Receivables open interface table that stores imported Lockbox information prior to validation. AutoLockbox, the bank-supplied service that allows corporate customers to outsource accounts receivable payment processing, creates one row in this table for each record in a bank transmission. Because a single AutoLockbox operation can process millions of transactions per month, this table functions as a high-volume staging area between the raw bank file and the Receivables receipt tables. When the AutoLockbox validation step is executed, Oracle Receivables transfers validated information out of AR_PAYMENTS_INTERFACE_ALL into AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL, from which receipts are ultimately created and applied.

The table is owned by the AR schema and resides in the APPS_TS_INTERFACE tablespace, a standard destination for EBS interface objects. It is flagged VALID and active, with FND Design Data registered as AR.AR_PAYMENTS_INTERFACE_ALL. The primary key is TRANSMISSION_RECORD_ID. The documented physical schema for Release 12.2.2 contains 262 columns, reflecting the breadth of bank-supplied remittance detail the interface must accommodate. Heuristically, and based on the foreign-key structure provided in the metadata, the object behaves as a Data Vault link — it associates transmission, customer, receipt method, bank account, and site-use references in a single relationship record. This classification is a modeling suggestion rather than a documented attribute.

Key Information Stored

Each row represents one record of a bank transmission. The most consequential columns fall into several groups.

• TRANSMISSION_RECORD_ID — the surrogate primary key and the single column of the unique index AR_PAYMENTS_INTERFACE_U1. It is the only documented business-key candidate for uniqueness.
• TRANSMISSION_ID / TRANSMISSION_REQUEST_ID — identifiers tying the row back to the transmission header (AP_TRANSMISSIONS_SETUP and AR_TRANSMISSIONS_ALL).
• RECORD_TYPE, STATUS, BATCH_NAME, LOCKBOX_NUMBER — control attributes distinguishing header, batch, and detail records and tracking validation status.
• TRANSMISSION_AMOUNT, BATCH_AMOUNT, REMITTANCE_AMOUNT, CURRENCY_CODE — monetary and currency values carried from the bank file.
• CHECK_NUMBER, TRANSIT_ROUTING_NUMBER, ACCOUNT, DEPOSIT_DATE — payment instrument and deposit detail.
• CUSTOMER_ID, CUSTOMER_NUMBER, CUSTOMER_SITE_USE_ID, BILL_TO_LOCATION — customer and site identification used for matching.
• RECEIPT_METHOD_ID, RECEIPT_METHOD, RECEIPT_DATE — the receipt attributes assigned during validation.
• INVOICE1 through INVOICE8 and AMOUNT_APPLIED1 through AMOUNT_APPLIED8 — the repeating remittance detail, with parallel INVOICE_STATUS2 through INVOICE8_STATUS and installment columns.
• ORG_ID — the multi-org operating unit discriminator, making the "_ALL" designation meaningful.
• ATTRIBUTE1–15, GLOBAL_ATTRIBUTE1–20 — descriptive flexfield storage.

Common Use Cases and Queries

The primary operational use is diagnosing Lockbox import and validation failures. Rows that remain in this table after a validation run have not been transferred to the interim receipt tables and represent exceptions requiring correction.

• Counting and aging unvalidated records by status and transmission.
• Reconciling bank transmission totals against batch and lockbox amounts before validation.
• Inspecting remittance detail (invoice numbers and applied amounts) for a specific check.
• Identifying records by customer, check number, or account when a receipt was not generated.

A representative diagnostic query filters the interface by transmission and record type:

SELECT transmission_record_id, transmission_id, record_type, status, check_number, remittance_amount, customer_id, org_id FROM ar.ar_payments_interface_all WHERE transmission_id = :p_transmission_id AND record_type = 'DETAIL' ORDER BY transmission_record_id;

For validation exception reporting, a query joining STATUS against the transmission header in AR_TRANSMISSIONS_ALL reconciles the transmitted count and amount against the rows staged here. Note that the indexes AR_PAYMENTS_INTERFACE_N1 through N6 support access paths on RECORD_TYPE, ITEM_NUMBER, CHECK_NUMBER, CUSTOMER_ID, TRANSIT_ROUTING_NUMBER, ACCOUNT, TRANSMISSION_ID, STATUS, and BATCH_NAME.

Related Objects

The foreign-key metadata identifies the principal referenced objects. AR_TRANSMISSIONS_ALL is referenced through TRANSMISSION_REQUEST_ID and holds the transmission-level record. AP_TRANSMISSIONS_SETUP is referenced through TRANSMISSION_ID, supplying Lockbox transmission configuration. AR_RECEIPT_METHODS is referenced through RECEIPT_METHOD_ID, determining how the resulting receipt is classified. HZ_CUST_ACCOUNTS and HZ_CUST_SITE_USES_ALL are referenced through CUSTOMER_ID and CUSTOMER_SITE_USE_ID respectively, resolving the paying customer and site use. IBY_EXT_BANK_ACCOUNTS is referenced through CUSTOMER_BANK_ACCOUNT_ID, supplying customer bank account detail.

In the reverse direction, several objects reference this table through TRANSMISSION_RECORD_ID: the header and line global temporary tables AR_PMTS_INTERFACE_HEADER_GT, AR_PMTS_INTERFACE_LINES_GT, and the AR_PMTS_INTERFACE_LINE_DETAILS table, all of which participate in the validation transfer. The downstream destination objects during validation are AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL, after which standard Receivables APIs such as the AutoLockbox concurrent program and receipt creation logic complete processing. Detailed column-level import and validation rules are documented in the appendix of the Oracle Receivables User Guide.