Search Results total_freight_amount

Overview

AR_INVOICE_TOTALS_V is a predefined Oracle E-Business Suite view owned by the APPS schema and shipped as part of the Oracle Receivables (AR) product family. In both release 12.1.1 and 12.2.2, the view carries a VALID status and is documented in the ETRM repository. Its stated purpose is to collect the total amount of charges for a transaction, presenting aggregated monetary figures for completed, printable transactions in a single denormalized row per transaction.

The view is read-only and is not a base table. It functions as a reporting and integration convenience object, collapsing the line-level detail held in the receivables transaction tables into transaction-level totals. Because it aggregates by customer transaction identifier, it is well suited to reporting tools, custom concurrent programs, and interface extracts that require summarized invoice amounts rather than individual lines. Users searching for total_freight_amount will find that this is one of the primary aggregate columns exposed by the view.

Underlying Base Objects

The view is defined over three base objects, referenced in the ETRM metadata as synonyms:

• RA_CUSTOMER_TRX — the transaction header table, supplying CUSTOMER_TRX_ID, COMPLETE_FLAG, and CUST_TRX_TYPE_ID.
• RA_CUSTOMER_TRX_LINES — the transaction line table, supplying LINE_TYPE and EXTENDED_AMOUNT for each charge line.
• RA_CUST_TRX_TYPES — the transaction type table, supplying DEFAULT_PRINTING_OPTION.

The join logic is significant. The view restricts results to transactions where COMPLETE_FLAG equals 'Y' (completed transactions) and where the associated transaction type has DEFAULT_PRINTING_OPTION equal to 'PRI' (printable, primary transactions). Joining is performed on CUSTOMER_TRX_ID between the header and lines tables, and on CUST_TRX_TYPE_ID between the header and type tables. Aggregation is grouped by TRX.CUSTOMER_TRX_ID, producing one summarized row for each qualifying transaction.

Key Columns

• CUSTOMER_TRX_ID — the transaction identifier and the grouping key; it links back to RA_CUSTOMER_TRX.
• TOTAL_TAX_AMOUNT — the sum of EXTENDED_AMOUNT for lines whose LINE_TYPE is 'TAX'.
• TOTAL_FREIGHT_AMOUNT — the sum of EXTENDED_AMOUNT for lines whose LINE_TYPE is 'FREIGHT'. This is the column most commonly sought when reconciling freight charges on an invoice.
• TOTAL_LINE_AMOUNT — the sum of EXTENDED_AMOUNT for all lines that are neither 'FREIGHT' nor 'TAX', i.e. the net merchandise or line amount.
• TOTAL_AMOUNT — the sum of EXTENDED_AMOUNT across all lines, representing the gross transaction total.

Note that the category amounts are derived using DECODE logic on LINE_TYPE, and that TOTAL_LINE_AMOUNT excludes freight and tax to avoid double counting.

Common Use Cases and Queries

Typical scenarios include invoice summary reporting, freight reconciliation, tax versus line value analysis, and feeding downstream systems such as General Ledger interfaces or data warehouses. A basic query retrieving freight totals for a transaction follows:

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

To list all transactions with non-zero freight, use:

SELECT customer_trx_id, total_freight_amount FROM apps.ar_invoice_totals_v WHERE total_freight_amount > 0 ORDER BY total_freight_amount DESC;

Because the view already restricts to completed and printable transactions, callers should not expect to see incomplete or non-printable transactions in the result set. Queries returning no rows for a freight search typically indicate either that the transaction is not complete and printable, or that no lines were classified with LINE_TYPE 'FREIGHT'.