Search Results lockbox_amount

Overview

AR_PAYMENTS_INTERFACE_ALL is the Oracle Receivables (AR) staging table that holds imported Lockbox information which has not yet been validated or applied. It is the landing zone for bank transmission data processed through the Lockbox interface, sitting upstream of actual receipt creation in AR_CASH_RECEIPTS_ALL and its distributions. Records remain here while the Lockbox validation and AutoLockbox workflow evaluates whether the transmission record can be converted into a valid customer receipt; records that fail validation persist with a non-validated status and require correction or reprocessing.

Physically, the table belongs to the AR schema and contains 262 documented columns in the ETRM 12.2.2 reference, reflecting the wide, loosely structured nature of bank lockbox files. The ATOMIC primary key is AR_PAYMENTS_INTERFACE_PK, defined on TRANSMISSION_RECORD_ID. Under a heuristic Data Vault classification derived from its foreign key structure, this table behaves as a link object, since it principally records relationships between a transmission (AR_TRANSMISSIONS_ALL), a customer (HZ_CUST_ACCOUNTS), a receipt method (AR_RECEIPT_METHODS), a customer site use (HZ_CUST_SITE_USES_ALL), and a customer bank account (IBY_EXT_BANK_ACCOUNTS). This classification is a modeling suggestion rather than an Oracle-declared design.

Key Information Stored

The most operationally significant columns include:

• TRANSMISSION_RECORD_ID — surrogate primary key and the single-column unique index AR_PAYMENTS_INTERFACE_U1; also the join key to the AR_PMTS_INTERFACE_HEADER_GT, AR_PMTS_INTERFACE_LINE_DETAILS, and AR_PMTS_INTERFACE_LINES_GT tables.
• STATUS and RECORD_TYPE — drive validation state and the type of transmission record (header, lockbox, batch, or item level).
• TRANSMISSION_REQUEST_ID, TRANSMISSION_ID — foreign keys back to AR_TRANSMISSIONS_ALL and AP_TRANSMISSIONS_SETUP, tracing the originating bank transmission.
• CUSTOMER_ID, CUSTOMER_SITE_USE_ID, CUSTOMER_NUMBER — customer identification and bill-to linkage used during matching.
• RECEIPT_METHOD_ID, RECEIPT_METHOD — how the receipt is to be created.
• CUSTOMER_BANK_ACCOUNT_ID — reference to the originating customer bank account in IBY_EXT_BANK_ACCOUNTS.
• REMITTANCE_AMOUNT, CURRENCY_CODE, CHECK_NUMBER, TRANSIT_ROUTING_NUMBER, ACCOUNT — core payment instrument data captured from the bank file.
• INVOICE1 through INVOICE8 and AMOUNT_APPLIED1 through AMOUNT_APPLIED8 — the overflow-capable application detail used by AutoLockbox to match remittance lines to open invoices.
• DEPOSIT_DATE, GL_DATE, RECEIPT_DATE — key accounting and aging dates.
• LOCKBOX_NUMBER, BATCH_NAME, ITEM_NUMBER — bank-side identifiers used for reconciliation and de-duplication.
• ORG_ID — multi-org partitioning column supporting operating unit segregation.

An extensive attribute and global attribute block (ATTRIBUTE1–15, GLOBAL_ATTRIBUTE1–20) supports extensibility, and a large CPG_* block is reserved for Consumer Packaged Goods/retail lockbox processing.

Common Use Cases and Queries

Typical scenarios include monitoring unvalidated lockbox records, diagnosing rejected transmissions, and reconciling bank remittance to receipts. A representative query to inspect failed or pending records:

• SELECT transmission_record_id, lockbox_number, batch_name, item_number, status, remittance_amount FROM ar.ar_payments_interface_all WHERE status <> 'PROCESSED' AND org_id = :org_id;

A common pattern joins the table to its parent transmission to trace the source file:

• SELECT p.*, t.* FROM ar.ar_payments_interface_all p, ar.ar_transmissions_all t WHERE p.transmission_request_id = t.transmission_request_id;

Reporting use cases include lockbox error dashboards, daily deposit summaries by lockbox number, aging of unapplied transmissions, and reconciliation of REMITTANCE_AMOUNT against SUM(AMOUNT_APPLIED1..8). The AR_PMTS_INTERFACE_* global temporary tables are frequently joined on TRANSMISSION_RECORD_ID to drive custom Lockbox validation logic.

Related Objects

• AR_TRANSMISSIONS_ALL — joined on TRANSMISSION_REQUEST_ID; the parent transmission header.
• AR_RECEIPT_METHODS — joined on RECEIPT_METHOD_ID; receipt method validation.
• HZ_CUST_ACCOUNTS — joined on CUSTOMER_ID; customer identity resolution.
• HZ_CUST_SITE_USES_ALL — joined on CUSTOMER_SITE_USE_ID; bill-to/remit-to site resolution.
• IBY_EXT_BANK_ACCOUNTS — joined on CUSTOMER_BANK_ACCOUNT_ID; customer bank account reference.
• AP_TRANSMISSIONS_SETUP — joined on TRANSMISSION_ID; transmission setup reference.
• AR_PMTS_INTERFACE_HEADER_GT, AR_PMTS_INTERFACE_LINE_DETAILS, AR_PMTS_INTERFACE_LINES_GT — referencing tables joined on TRANSMISSION_RECORD_ID.

These relationships, together with the primary key AR_PAYMENTS_INTERFACE_PK, make AR_PAYMENTS_INTERFACE_ALL the central integration point between bank lockbox files and downstream Receivables receipt processing.