Results for “so_open_order_invoice_refs_v”

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

Overview

SO_OPEN_ORDER_INVOICE_REFS_V is a reporting and integration view owned by the APPS schema within the Order Entry (OE) product of Oracle E-Business Suite. Its defining purpose is narrow and precisely scoped: it returns the set of customer transaction identifiers (customer_trx_id values from Oracle Receivables) that are referenced by open order lines in Oracle Order Management. The view is exposed as a single logical column, CUSTOMER_TRX_ID, which makes it a lightweight lookup utility rather than a general-purpose transactional query source. The presence of a COMMITMENT_ID source column in the view text reflects the fact that order lines may carry a commitment reference to an invoice, and the view normalizes these references into a uniform Receivables transaction identifier.

The view derives its rows by unifying three distinct reference paths: commitment references held directly on open order lines, credit invoice line references, and return references typed as invoices. Because it filters on OPEN_FLAG = 'Y', it excludes closed and cancelled lines, which makes it suitable for identifying invoices that remain actively tied to open order activity.

Underlying Base Objects

According to the documented ETRM metadata for release 12.2.2, SO_OPEN_ORDER_INVOICE_REFS_V is defined over two referenced base objects: RA_CUSTOMER_TRX_LINES (accessed through a synonym) and SO_LINES (which itself is a view over the sales order line tables, principally OE_ORDER_LINES_ALL). The view text confirms this composition. The first UNION branch selects COMMITMENT_ID from SO_LINES as CUSTOMER_TRX_ID. The second branch joins RA_CUSTOMER_TRX_LINES to SO_LINES on CREDIT_INVOICE_LINE_ID = CUSTOMER_TRX_LINE_ID. The third branch joins RA_CUSTOMER_TRX_LINES to SO_LINES on RETURN_REFERENCE_ID = CUSTOMER_TRX_LINE_ID, restricted to RETURN_REFERENCE_TYPE_CODE = 'INVOICE'. The DISTINCT operator applied to each branch removes duplicate transaction identifiers arising from multi-line invoice relationships.

Key Columns

• CUSTOMER_TRX_ID — The sole documented column. It is the Receivables transaction identifier (RA_CUSTOMER_TRX.CUSTOMER_TRX_ID). Note that in the first UNION branch this value is sourced from SO_LINES.COMMITMENT_ID, meaning the commitment reference is expected to hold a customer transaction identifier value.
• COMMITMENT_ID — Although it does not appear as an output column, it is the operative predicate in the first branch (WHERE COMMITMENT_ID IS NOT NULL) and is the primary term users search against when investigating invoice-to-order commitments.

Common Use Cases and Queries

The view is typically used to enumerate invoices linked to open sales order lines, supporting reconciliation between Order Management and Receivables, open commitment reporting, and downstream integration extracts that require a de-duplicated list of invoice identifiers.

To retrieve all referenced transactions for open order activity:

• SELECT DISTINCT CUSTOMER_TRX_ID FROM APPS.SO_OPEN_ORDER_INVOICE_REFS_V;

To resolve those identifiers to full invoice header detail:

• SELECT v.customer_trx_id, r.trx_number, r.trx_date, r.bill_to_customer_id FROM apps.so_open_order_invoice_refs_v v, apps.ra_customer_trx_all r WHERE r.customer_trx_id = v.customer_trx_id;

Because the view returns only identifiers, it functions reliably as a subquery driver — for example, an EXISTS or IN predicate restricting a larger Receivables or Order Management report to invoices that retain links to open order lines. When interpreting results, note that the DISTINCT clauses suppress multiplicity, so the view is not suitable for measuring how many order lines reference a given invoice; a direct query against SO_LINES joined to RA_CUSTOMER_TRX_LINES is required for that level of detail.