Results for “so_invoice_numbers_v”

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

Overview

The view SO_INVOICE_NUMBERS_V is a reporting and integration construct within the Oracle E-Business Suite Order Entry (OE) module. Its purpose is to expose invoice information associated with order headers and lines by joining order management data in the SO schema to receivables transaction data in the RA schema. The view presents a denormalized, read-only projection that allows order management users, report developers, and integration specialists to resolve the relationship between sales orders and the invoices generated against them.

The view is particularly relevant when an invoice number such as 13016 must be traced back to its originating sales order, customer, order type, and order category. In practice, invoice numbers of this form are numeric transaction numbers drawn from the receivables transaction table, and SO_INVOICE_NUMBERS_V supplies the cross-reference needed to interpret them within the context of order entry rather than receivables alone.

Because the view is documented as "Not implemented in this database" in the ETRM metadata, its presence depends on the specific instance and patch level. In Oracle EBS 12.1.1 and 12.2.2, the definition remains consistent with the view text published in the ETRM reference.

Underlying Base Objects

The view is defined over six referenced base objects, though the ETRM "referenced base objects" registry documents none explicitly. The SELECT statement itself confirms the following tables:

• SO_HEADERS (HD) — order header records, supplying ORDER_NUMBER, HEADER_ID, ORDER_CATEGORY, ORDER_TYPE_ID, PURCHASE_ORDER_NUM, ORIGINAL_SYSTEM_SOURCE_CODE, ORIGINAL_SYSTEM_REFERENCE, CUSTOMER_ID, DATE_ORDERED, and SHIP_METHOD_CODE.
• SO_LINES (L) — order lines, linked to headers and used to bridge to receivables transaction lines.
• RA_CUSTOMER_TRX (TRX) — receivables transactions, supplying INVOICE_NUMBER and INVOICE_DATE.
• RA_CUSTOMER_TRX_LINES (TRXL) — receivables transaction lines, joined via CREDIT_INVOICE_LINE_ID or INTERFACE_LINE_ATTRIBUTE6.
• SO_ORDER_TYPES (OT) — order type name, joined on ORDER_TYPE_ID.
• SO_LOOKUPS (SL) — order category description, restricted to LOOKUP_TYPE = 'ORDER_CATEGORY'.
• RA_CUSTOMERS (RC) — customer name and number, joined on CUSTOMER_ID.

The join between order lines and receivables lines is the critical linkage. It uses either the direct CREDIT_INVOICE_LINE_ID reference or a decoded match on INTERFACE_LINE_ATTRIBUTE6, which permits the view to resolve invoices both through standard credit-invoice flows and through interface-based referencing.

Key Columns

• INVOICE_NUMBER — the receivables transaction number (TRX_NUMBER); for example, value 13016.
• INVOICE_DATE — the transaction date on the receivables record.
• ORDER_NUMBER and HEADER_ID — the sales order identifier and its internal primary key.
• ORDER_TYPE and ORDER_TYPE_ID — the order type name and key.
• ORDER_CATEGORY and ORDER_CATEGORY_DESC — the category code and its lookup meaning.
• PURCHASE_ORDER_NUM — the customer purchase order reference on the header.
• ORIGINAL_SYSTEM_SOURCE_CODE and ORIGINAL_SYSTEM_REFERENCE — source-system traceability attributes.
• CUSTOMER_NAME, CUSTOMER_ID, CUSTOMER_NUMBER, and BILL_TO_CUSTOMER_ID — customer identification and billing entity.
• DATE_ORDERED and SHIP_METHOD_CODE — order date and shipping method.

Common Use Cases and Queries

The most frequent use case is resolving an invoice number to its sales order and customer. A typical query follows:

• SELECT INVOICE_NUMBER, INVOICE_DATE, ORDER_NUMBER, ORDER_TYPE, CUSTOMER_NAME FROM SO_INVOICE_NUMBERS_V WHERE INVOICE_NUMBER = 13016;
• SELECT ORDER_NUMBER, INVOICE_NUMBER, PURCHASE_ORDER_NUM, DATE_ORDERED FROM SO_INVOICE_NUMBERS_V WHERE CUSTOMER_NUMBER = '1002';
• SELECT ORDER_CATEGORY_DESC, ORDER_TYPE, COUNT(*) FROM SO_INVOICE_NUMBERS_V GROUP BY ORDER_CATEGORY_DESC, ORDER_TYPE;

These queries support reconciliation of order-to-invoice cycles, audit of billing by customer, and reporting on order categories. Because the view is not implemented in every database, availability should be verified before use.