Search Results ar_pmts_interface_line_details




Overview
Explore in AI Chat

Overview

AR_PMTS_INTERFACE_LINE_DETAILS is an Oracle Receivables (AR) staging table that stores line-level application information captured during the Lockbox import process when that information fails validation. It belongs to the Automatic Receipts and Lockbox processing subsystem, sitting between the raw transmission data loaded by the Lockbox import program and the validated receipt records ultimately created in AR_CASH_RECEIPTS and AR_RECEIVABLE_APPLICATIONS. When the Lockbox program cannot validate a line — for example, because the invoice number cannot be matched, the amount exceeds the open balance, or a required field is missing or malformed — the rejected line detail is written here so that it remains available for correction, revalidation, and re-submission rather than being discarded.

The ETRM metadata classifies this object, using heuristic FK-structure mining, as standalone. In Data Vault modeling terms, this is best treated as a staging or satellite-like structure rather than a true hub or link: it carries descriptive, transaction-time detail about an import line and does not itself anchor a durable business key hierarchy. Its lineage is expressed instead through its foreign keys to the transmission and payment interface tables.

Key Information Stored

The documented physical schema for this table in ETRM 12.2.2 contains 16 columns. The most significant are:

  • TRANSMISSION_REQUEST_ID — Foreign key to AR_TRANSMISSIONS_ALL; identifies the Lockbox transmission (batch) to which this failed line belongs.
  • TRANSMISSION_RECORD_ID — Foreign key to AR_PAYMENTS_INTERFACE_ALL; ties the failed line back to the specific imported receipt record.
  • STATUS — Indicates the validation or processing state of the line detail, driving whether it can be reprocessed.
  • INVOICE_NUMBER — The invoice explicitly referenced on the Lockbox line; a primary business-key candidate for the intended application.
  • APPLY_TO — The application target indicator describing how the amount should be applied.
  • AMOUNT_APPLIED and ALLOCATED_RECEIPT_AMOUNT — The receipt amount allocated to the line and the amount actually applied.
  • LINE_AMOUNT — The gross amount of the line as presented for application.
  • TAX, FREIGHT, CHARGES — Component amounts carried on the line, useful for reconciliation against the corresponding invoice distributions.
  • LINE_DISCOUNT, TAX_DISCOUNT, FREIGHT_DISCOUNT — Discount amounts by component, reflecting the terms captured for the line.
  • CUSTOMER_TRX_ID — Identifier of the target transaction (invoice) once it is resolved, linking the line to AR_TRANSACTIONS.
  • COMMENTS — Free-text notes, typically recording the validation failure reason.

No column in the documented metadata is declared as a formal surrogate primary key; the table behaves as a detail staging structure whose uniqueness is more naturally expressed through the combination of TRANSMISSION_REQUEST_ID and TRANSMISSION_RECORD_ID plus the invoice reference.

Common Use Cases and Queries

Typical usage centers on diagnosing and reprocessing Lockbox failures. Support teams query this table to determine why specific receipts were not applied, and to build exception reports for receipt-entry staff. A common pattern joins to the parent interface record to retrieve payer and receipt context:

  • Extracting all unprocessed lines for a transmission: SELECT * FROM ar_pmts_interface_line_details WHERE transmission_request_id = :req_id;
  • Joining to receipt context: SELECT d.invoice_number, d.line_amount, d.amount_applied, p.* FROM ar_pmts_interface_line_details d, ar_payments_interface_all p WHERE d.transmission_record_id = p.transmission_record_id;
  • Detecting unresolved invoices: rows where CUSTOMER_TRX_ID IS NULL indicate the referenced invoice number could not be matched to a transaction.
  • Ageing exceptions by transmission: aggregating failed lines by TRANSMISSION_REQUEST_ID and comparing against AR_TRANSMISSIONS_ALL creation dates.

Reporting use cases include exception dashboards for cash application teams, audit trails demonstrating which Lockbox lines required manual intervention, and reconciliation of imported amounts versus applied amounts.

Related Objects

  • AR_TRANSMISSIONS_ALL — parent transmission header; join on TRANSMISSION_REQUEST_ID.
  • AR_PAYMENTS_INTERFACE_ALL — parent payment interface record; join on TRANSMISSION_RECORD_ID.
  • AR_CASH_RECEIPTS_ALL — the validated receipt ultimately created when processing succeeds.
  • AR_RECEIVABLE_APPLICATIONS_ALL — the applied transactions produced downstream.
  • AR_TRANSACTIONS / RA_CUSTOMER_TRX_ALL — target invoices referenced via CUSTOMER_TRX_ID and INVOICE_NUMBER.
  • AR_INTERFACE_ERRORS / AR_LOCKBOX_INTERFACE — related error and staging structures used alongside this table during Lockbox import.
  • AutoLockbox — the concurrent program (ARLPLB) that populates and reprocesses these interface records.