Search Results total_line_amount




Overview
Explore in AI Chat

Overview

AR_INVOICE_TOTALS_V is a seeded Oracle E-Business Suite view owned by the APPS schema and shipped as part of the Oracle Receivables (AR) module. It is documented in ETRM for releases 12.1.1 and 12.2.2 with a status of VALID. The view presents aggregated monetary totals for a customer transaction, resolving charge lines in Oracle Receivables into four summarized buckets: tax, freight, line (goods or services), and overall transaction amount. One row is returned per customer transaction identifier.

Its practical role is reporting and integration. Rather than requiring report authors or downstream interfaces to repeatedly replicate DECODE-based aggregation logic against the transaction line table, the view centralizes that calculation. For a user searching on "total_line_amount," this view is the canonical seeded source for a pre-aggregated invoice value that excludes tax and freight, which is frequently the figure required for revenue reporting, reconciliation, and third-party system feeds.

Underlying Base Objects

The view is defined over three Receivables base objects, accessed through APPS synonyms:

The three objects are joined on TRX.CUSTOMER_TRX_ID = LINES.CUSTOMER_TRX_ID and TRX.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID, with results grouped by TRX.CUSTOMER_TRX_ID.

Key Columns

  • CUSTOMER_TRX_ID — the unique transaction identifier. Serves as the primary key of the view and the join back to RA_CUSTOMER_TRX.
  • TOTAL_LINE_AMOUNT — the sum of EXTENDED_AMOUNT for all lines whose LINE_TYPE is neither FREIGHT nor TAX. This is the "net of tax and freight" line total and the column most often sought by users searching for total_line_amount.
  • TOTAL_TAX_AMOUNT — the sum of EXTENDED_AMOUNT where LINE_TYPE = 'TAX'.
  • TOTAL_FREIGHT_AMOUNT — the sum of EXTENDED_AMOUNT where LINE_TYPE = 'FREIGHT'.
  • TOTAL_AMOUNT — the sum of EXTENDED_AMOUNT across all line types, representing the gross transaction value.

Note that TOTAL_LINE_AMOUNT + TOTAL_TAX_AMOUNT + TOTAL_FREIGHT_AMOUNT will equal TOTAL_AMOUNT as long as line types are limited to those categories; any other LINE_TYPE value falls into TOTAL_LINE_AMOUNT. Sign conventions follow the underlying EXTENDED_AMOUNT, so credit memos and adjustments produce negative contributions.

Common Use Cases and Queries

Typical scenarios include invoice register and revenue reporting, reconciliation of AR balances to the general ledger, extraction feeds to data warehouses, and ad hoc analysis of tax and freight burden by transaction. The following retrieves the summarized amounts for a single transaction:

SELECT customer_trx_id,
       total_line_amount,
       total_tax_amount,
       total_freight_amount,
       total_amount
FROM   apps.ar_invoice_totals_v
WHERE  customer_trx_id = :p_customer_trx_id;

To join summarized totals to transaction header detail, for example to list invoice numbers with their net line value:

SELECT t.trx_number,
       t.trx_date,
       v.total_line_amount,
       v.total_tax_amount,
       v.total_amount
FROM   apps.ar_invoice_totals_v v,
       apps.ra_customer_trx_all t
WHERE  t.customer_trx_id = v.customer_trx_id
AND    t.org_id = :p_org_id
AND    t.trx_date BETWEEN :p_start_date AND :p_end_date;

Because the view is fully aggregated and already filters on completion status and primary transaction types, it is efficient for summary-level reporting but is not a substitute for line-level detail. Queries that must inspect individual lines, non-completed transactions, or transaction types other than the primary printing option should access RA_CUSTOMER_TRX_LINES directly.