Search Results orderby_seq

Overview

IBE_INVOICE_DETAIL_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the IBE (iStore) product family. It exposes invoice line detail information drawn from Oracle Receivables, reformatting the transactional structure of RA_CUSTOMER_TRX_LINES_ALL into a presentation layer suitable for iStore self-service invoice and order history pages. The view is essentially a reporting and integration artifact: rather than requiring iStore UI code to re-derive line sequencing, line type meanings, and credit-versus-invoice quantity logic, the view encapsulates those rules in SQL.

The most notable characteristic of the view is the computed ORDERBY_SEQ column, which is frequently the target of user searches. iStore invoice detail pages must render lines in a deterministic, human-readable order that accounts for linked lines (for example, a credit line tied to an original invoice line). ORDERBY_SEQ guarantees this ordering without exposing the underlying link logic to the calling code.

Underlying Base Objects

The view is defined over three referenced objects, of which two appear explicitly in the documented metadata:

• RA_CUSTOMER_TRX_LINES_ALL (referenced via SYNONYM) — the Receivables transaction line table. It appears twice in the FROM clause: as CTL (the primary line) and as CTL2 (the linked line), joined through the outer join condition CTL.LINK_TO_CUST_TRX_LINE_ID = CTL2.CUSTOMER_TRX_LINE_ID(+).
• AR_LOOKUPS (VIEW) — joined on CTL.LINE_TYPE = AL_LINE_TYPE.LOOKUP_CODE with the fixed predicate 'STD_LINE_TYPE' = AL_LINE_TYPE.LOOKUP_TYPE, supplying the decoded LINE_TYPE meaning.

The self-join on RA_CUSTOMER_TRX_LINES_ALL is the mechanism behind the ORDERBY_SEQ derivation: when a line is linked to another line, the linked line's number is substituted so that related lines sort adjacent to one another.

Key Columns

• CUSTOMER_TRX_LINE_ID — Primary identifier of the invoice line.
• LINE_NUMBER — Derived through DECODE: when LINK_TO_CUST_TRX_LINE_ID is NULL the line's own LINE_NUMBER is used; otherwise the linked line's LINE_NUMBER is substituted.
• ORDERBY_SEQ — Concatenation of the derived LINE_NUMBER and CTL.LINE_TYPE, producing a stable sort key for display ordering.
• DESCRIPTION, QUANTITY, UNIT_SELLING_PRICE, EXTENDED_AMOUNT — Commercial line attributes. QUANTITY resolves to NVL(QUANTITY_CREDITED, QUANTITY_INVOICED), so credits report the credited quantity.
• LINE_TYPE and MEANING — The lookup code and its decoded STD_LINE_TYPE meaning from AR_LOOKUPS.
• SALES_ORDER, CUSTOMER_TRX_ID, INVOICE_CURRENCY_CODE, ORG_ID — Contextual and multi-org columns linking the line to its order, transaction header, currency, and operating unit.

Common Use Cases and Queries

Typical scenarios include iStore invoice detail rendering, customer-facing order and invoice history extracts, and reconciliation reports requiring credit lines to appear alongside their originating lines.

SELECT line_number, orderby_seq, description, quantity,
       unit_selling_price, extended_amount, meaning, sales_order
FROM   apps.ibe_invoice_detail_v
WHERE  customer_trx_id = :p_trx_id
  AND  org_id = :p_org_id
ORDER BY orderby_seq;

Because ORDERBY_SEQ combines line number and line type, it is the preferred ordering clause. Note that the view carries no ORG_ID predicate internally, so multi-org security must be applied by the caller.