Search Results total_product_value

Overview

The view AS_DOSSIER_PRODTOTALS_V belongs to the Oracle E-Business Suite product family AS – Sales Foundation. It is documented as an Order history product totals view, meaning its purpose is to aggregate product-level monetary totals for a given order header so that downstream reporting, dossier generation, or order-history analytics can present summarized product values without reprocessing each individual sales order line.

In Oracle EBS 12.1.1 and 12.2.2, the AS module (Sales Foundation, historically associated with Oracle Sales and order capture/dossier functionality) uses views of this kind to expose derived, read-only results to reports, concurrent programs, and integration layers. Rather than storing data, AS_DOSSIER_PRODTOTALS_V computes totals on demand from sales order line data, making it a lightweight aggregation layer over transactional tables. Note that the ETRM metadata states the object is not implemented in this database, so the view text below is the authoritative definition even where the physical object is absent.

Underlying Base Objects

The view is defined over a single base table, SO_LINES, which stores individual sales order line records. According to the documented metadata, no other base objects are referenced. The aggregation logic operates as follows:

• Rows are restricted to order lines where LINE_TYPE_CODE is 'DETAIL' or 'REGULAR', excluding non-transactional line types such as headers or promotions.
• Rows are further restricted by ITEM_TYPE_CODE to include only 'MODEL', 'STANDARD', or 'KIT' items. This is the exact predicate referenced by the search term item_type_code, which appears as a filter in the view definition.
• Results are grouped by HEADER_ID, producing one summary row per order header.

No joined tables are documented, so all monetary and quantity figures derive exclusively from columns on SO_LINES.

Key Columns

The view exposes three columns:

• HEADER_ID — The order header identifier inherited from SO_LINES. It is the grouping key, so each row represents the product totals for one order.
• TOTAL_PRODUCT_VALUE — The sum of SELLING_PRICE * (ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY, 0)), wrapped in NVL(...,0). This represents the net selling value of productive lines after cancellations. Because the NVL defaults to 0, headers with no qualifying lines return zero rather than null.
• TOTAL_LIST_VALUE — The sum of LIST_PRICE * (ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY, 0)), also NVL-guarded to 0. This captures the list-price equivalent, useful for discount and margin comparison against TOTAL_PRODUCT_VALUE.

The subtraction of cancelled quantity ensures the totals reflect net ordered product, not gross bookings.

Common Use Cases and Queries

Typical scenarios include order-history dossier reporting, product value summarization for a specific order, and reconciliation of net selling versus list value.

Retrieve totals for a specific order:

• SELECT HEADER_ID, TOTAL_PRODUCT_VALUE, TOTAL_LIST_VALUE FROM AS_DOSSIER_PRODTOTALS_V WHERE HEADER_ID = :p_header_id;

Compare list and selling value across many orders to derive effective discount:

• SELECT HEADER_ID, TOTAL_LIST_VALUE, TOTAL_PRODUCT_VALUE, (TOTAL_LIST_VALUE - TOTAL_PRODUCT_VALUE) AS DISCOUNT_AMT FROM AS_DOSSIER_PRODTOTALS_V;

Aggregate all productive order value for a reporting period (join to a header table on HEADER_ID for date filtering):

• SELECT SUM(TOTAL_PRODUCT_VALUE) FROM AS_DOSSIER_PRODTOTALS_V;

Because the view pre-filters on ITEM_TYPE_CODE of MODEL, STANDARD, or KIT, it conveniently excludes non-product lines. Its aggregation over SO_LINES means it should be used with an appropriate HEADER_ID predicate or join for efficient, targeted queries.