Search Results sold_to_customer_id

Overview

The OE_RMA_INVOICE_NUMBER_V view resides in the APPS schema of Oracle E-Business Suite and belongs to the Order Management (ONT) product family. Its documented purpose is to serve as a source for a ValueSet, meaning it is most commonly attached to a concurrent program parameter, a descriptive flexfield segment, or a report parameter that must present a list of valid RMA-related invoice numbers. Rather than exposing the full universe of receivables transactions, the view is deliberately filtered to foreign batch sources, narrowing its output to invoices that originated through foreign (RMA/intercompany) batch processing.

From a reporting and integration standpoint, the view provides a denormalized, read-only projection that joins header-level receivables transaction data with batch source information and a derived payment status. This makes it convenient for validation lists and for lightweight reporting where callers need the transaction identifier, transaction number, dates, currency, and current payment standing in a single query.

Underlying Base Objects

Per the ETRM 12.2.2 metadata, the view is defined over the following referenced base objects:

• RA_CUSTOMER_TRX (synonym) — the receivables transaction header table, aliased as R, supplying transaction identifiers, numbers, dates, customer, and currency.
• RA_BATCH_SOURCES (synonym) — the batch source definition table, aliased as B, joined on BATCH_SOURCE_ID and constrained to BATCH_SOURCE_TYPE = 'FOREIGN'.
• AR_PAYMENT_SCHEDULES (synonym) — used in a scalar subquery to derive the current payment schedule status.
• AR_LOOKUPS (view) — joined in the same subquery on LOOKUP_TYPE = 'PAYMENT_SCHEDULE_STATUS' and CLASS = 'INV' to translate the status code into its meaning.

The join between RA_CUSTOMER_TRX and RA_BATCH_SOURCES is the defining filter: only transactions whose batch source is of type FOREIGN are returned. The payment status is resolved through an outer-joined lookup, and the NVL wrapper substitutes the literal 'NO STATUS AVAILABLE' when no matching payment schedule or lookup meaning exists.

Key Columns

• CUSTOMER_TRX_ID — Primary identifier of the receivables transaction in RA_CUSTOMER_TRX; the join key to downstream receivables objects.
• TRX_NUMBER — The user-visible invoice or credit memo number; typically the value returned to the ValueSet.
• TRX_DATE — Transaction date of the receivables document.
• BATCH_SOURCE_NAME — Name of the foreign batch source from RA_BATCH_SOURCES.NAME.
• SOLD_TO_CUSTOMER_ID — Identifier of the sold-to customer, sourced from RA_CUSTOMER_TRX.SOLD_TO_CUSTOMER_ID. This column is frequently the target of searches, since it links invoices to a specific customer for RMA and returns processing.
• INVOICE_CURRENCY_CODE — Currency of the transaction.
• INVOICE_CREATION_DATE — System creation timestamp of the transaction record.
• PAYMENT_STATUS — Derived meaning of the payment schedule status; defaults to 'NO STATUS AVAILABLE' when unresolved.

Common Use Cases and Queries

The primary use case is ValueSet population, where the view supplies a validated list of RMA invoice numbers. A typical query retrieves invoices for a specific sold-to customer, which is the column most often searched by users:

• Looking up invoices for a given customer during RMA entry.
• Reporting on foreign-batch invoices with their current payment status.
• Validating a transaction number before referencing it in a returns process.

Sample SQL:

• SELECT trx_number, trx_date, sold_to_customer_id, invoice_currency_code, payment_status FROM oe_rma_invoice_number_v WHERE sold_to_customer_id = :p_customer_id;
• SELECT customer_trx_id, trx_number, batch_source_name, payment_status FROM oe_rma_invoice_number_v WHERE trx_date BETWEEN :p_from AND :p_to ORDER BY trx_date DESC;
• SELECT trx_number FROM oe_rma_invoice_number_v WHERE trx_number = :p_trx_number;

Because the view exposes only foreign batch sources, results are intentionally scoped and should not be treated as a complete receivables transaction listing. Callers requiring the full invoice population should query RA_CUSTOMER_TRX_ALL directly.