Results for “product_type”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AMS_OFFER_PRODUCTS_V is a marketing-facing database view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is defined within the AMS (Marketing) product and exposes the products and product families associated with marketing offers. Marketing offers in Oracle Advanced Marketing are frequently constructed from pricing entities, and this view bridges that gap by resolving offer records to the underlying pricing list headers and their associated items or categories. Rather than requiring report authors to navigate the complex pricing schema directly, the view presents a denormalized, consumer-friendly projection suitable for reporting, integration, and personalization.

The view becomes especially relevant when a user investigates the pricing table QP_LIST_HEADERS, since the offer-to-product relationship is physically stored through pricing list headers and list lines. AMS_OFFER_PRODUCTS_V provides the readable join path that makes those relationships accessible without manual reconstruction of the pricing attribute model.

Underlying Base Objects

The documented metadata identifies five referenced base objects: MTL_CATEGORIES_V (view), MTL_SYSTEM_ITEMS_VL (view), QP_LIST_HEADERS (synonym), QP_LIST_LINES (synonym), and QP_PRICING_ATTRIBUTES (synonym). The view is constructed as a UNION ALL of two distinct SELECT statements.

• The first branch joins QP_LIST_HEADERS, QP_LIST_LINES, QP_PRICING_ATTRIBUTES, and MTL_SYSTEM_ITEMS_VL to return individual inventory items. It filters on PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1' and PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM', and labels the resulting rows as type 'PRODUCT'.
• The second branch joins QP_LIST_HEADERS, QP_LIST_LINES, QP_PRICING_ATTRIBUTES, and MTL_CATEGORIES_V to return item categories. It filters on PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE2' and the same ITEM context, labeling rows as type 'FAMILY'.

Both branches share the join chain QPH.LIST_HEADER_ID = QPLN.LIST_HEADER_ID and QPLN.LIST_LINE_ID = QP.LIST_LINE_ID, and both use DISTINCT to suppress duplicate associations introduced by the pricing attribute model. The QP objects are referenced as synonyms, resolving to the corresponding APPS tables.

Key Columns

• LIST_HEADER_ID — The identifier of the pricing list header, which functions as the link back to the marketing offer. This is the column most often used to correlate the view with QP_LIST_HEADERS.
• PRODUCT_ID — In the product branch, the inventory item identifier; in the family branch, the category identifier.
• PRODUCT_NAME — The concatenated segment description. For products this is the item's concatenated segments; for families it is the category concatenated segments.
• PRODUCT_TYPE — A literal discriminator valued either 'PRODUCT' or 'FAMILY', indicating whether the row originated from the item branch or the category branch.

Common Use Cases and Queries

Typical usage includes listing all products eligible under a given offer, joining offer metadata to item or category descriptions, and producing eligibility extracts for downstream systems. The PRODUCT_TYPE column allows a single query to separate items from families, or to aggregate both.

• All products for a known offer: SELECT product_id, product_name, product_type FROM ams_offer_products_v WHERE list_header_id = :p_list_header_id;
• Only inventory items: add AND product_type = 'PRODUCT'.
• Join to the pricing header: SELECT v.product_name, h.name FROM ams_offer_products_v v, qp_list_headers h WHERE v.list_header_id = h.list_header_id;

Because the view is read-only and defined with DISTINCT set operations, it is safe for reporting workloads, though performance benefits from filtering on LIST_HEADER_ID.