Search Results orderby_seq

Overview

The view APPS.IBE_INVOICE_DETAIL_V exposes invoice line detail derived from Oracle Receivables transaction lines, enriched with the decoded meaning of each line type. Within Oracle E-Business Suite 12.1.1 and 12.2.2 it is used by iStore and related self-service invoice presentation modules, providing a denormalized, presentation-ready projection of RA_CUSTOMER_TRX_LINES_ALL. Rather than exposing raw lookup codes, the view resolves the line type through AR_LOOKUPS so that reports and pages display a human-readable value.

A defining behavior is the handling of credit lines that reference an original invoice line. When LINK_TO_CUST_TRX_LINE_ID is populated, the view substitutes the referenced line's LINE_NUMBER for the current line's own number, and derives an ORDERBY_SEQ by concatenating that number with LINE_TYPE. This groups a credit line together with the line it credits for stable display ordering.

Underlying Base Objects

Per documented metadata, the view references two base objects:

• AR_LOOKUPS (VIEW) — supplied through the alias AL_LINE_TYPE, joined on CTL.LINE_TYPE = AL_LINE_TYPE.LOOKUP_CODE and restricted by 'STD_LINE_TYPE' = AL_LINE_TYPE.LOOKUP_TYPE. This provides the MEANING column.
• RA_CUSTOMER_TRX_LINES_ALL (SYNONYM) — referenced twice, as CTL (the driving line) and CTL2 (the linked original line).

The self-join is an outer join: CTL.LINK_TO_CUST_TRX_LINE_ID = CTL2.CUSTOMER_TRX_LINE_ID(+). Consequently, lines that do not reference an original line still appear, with CTL2 columns null. The lookup join, by contrast, is an inner join, so any line whose LINE_TYPE lacks a matching STD_LINE_TYPE lookup is excluded from the result set. The WHERE clause is applied before any ordering, and ORG_ID is carried through from the transaction line, supporting multi-org filtered access.

Key Columns

• CUSTOMER_TRX_LINE_ID — primary identifier of the invoice line.
• LINE_NUMBER — the line's own number, or the referenced line's number for credits.
• ORDERBY_SEQ — display sort key formed from the resolved line number concatenated with LINE_TYPE.
• DESCRIPTION — line description text.
• QUANTITY — NVL(QUANTITY_CREDITED, QUANTITY_INVOICED); credit lines use the credited quantity.
• UNIT_SELLING_PRICE, EXTENDED_AMOUNT — pricing and line total.
• LINE_TYPE — raw lookup code (for example LINE, TAX, FREIGHT, CHARGES).
• MEANING — decoded description of the line type from AR_LOOKUPS; the value surfaced to end users.
• SALES_ORDER, CUSTOMER_TRX_ID, ORG_ID — source order, parent transaction, and operating unit.

Common Use Cases and Queries

Typical uses include self-service invoice drill-down, invoice line reporting by type, and reconciliation of credits to original lines. Because the view already resolves MEANING and credit linkage, callers avoid the lookup and self-join logic themselves.

• Retrieve all lines for a transaction in display order.
• Filter by decoded line type.
• Identify credit lines and their referenced originals.

Sample SQL:

• SELECT line_number, orderby_seq, description, quantity, extended_amount, line_type, meaning FROM apps.ibe_invoice_detail_v WHERE customer_trx_id = :p_trx_id ORDER BY orderby_seq;
• SELECT line_number, quantity, extended_amount, sales_order FROM apps.ibe_invoice_detail_v WHERE customer_trx_id = :p_trx_id AND line_type = 'LINE';
• SELECT c.line_number, c.quantity, c.extended_amount FROM apps.ibe_invoice_detail_v c WHERE c.line_type = 'CM' AND c.customer_trx_id = :p_trx_id;

Note that only line types present in AR_LOOKUPS under STD_LINE_TYPE are returned, and always constrain results by CUSTOMER_TRX_ID or ORG_ID for acceptable performance.