Results for “ams_offer_type”

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

Overview

APPS.AMS_P_OFFER_PRODUCTS_V is a reporting view in the Oracle E-Business Suite Advanced Marketing (AMS) module that joins offer header information to the products, categories, and inventory items associated with each offer. It presents a denormalized, read-only projection that combines campaign/offer metadata from AMS_ACT_OFFERS with product assignment records from AMS_ACT_PRODUCTS_V, and enriches the result with the description of the associated Oracle Advanced Pricing (QP) list header.

A salient characteristic of this view is its handling of the OFFER_TYPE column. Rather than exposing the raw lookup code, the view passes OFF.OFFER_TYPE through AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_OFFER_TYPE', OFF.OFFER_TYPE) to resolve the lookup into its human-readable meaning. This makes the view particularly relevant to users searching for ams_offer_type, since it is the point at which the AMS_OFFER_TYPE lookup is applied and surfaced for reporting.

The view is commonly consumed for offer-to-product analysis, price list association reporting, and integration extracts where a flattened offer/product row is required without navigating the underlying normalized AMS tables. It is defined with an active-offer filter: only offers whose END_DATE is null or still in the future are returned.

Underlying Base Objects

The view is defined over the following documented base objects:

• AMS_ACT_OFFERS (SYNONYM) — Aliased as OFF. Supplies offer header attributes including offer code, status, dates, and the OFFER_TYPE lookup code. Joined to the product view on OFF.ACTIVITY_OFFER_ID = AAP.MASTER_OBJECT_ID.
• AMS_ACT_PRODUCTS_V (VIEW) — Aliased as AAP. Supplies the product, category, and inventory item detail, and is constrained by AAP.MASTER_OBJECT_TYPE = 'OFFR' to restrict rows to offers rather than other AMS activity objects.
• QP_LIST_HEADERS_VL (VIEW) — Aliased as QPL. Provides the price list description, joined via OFF.QP_LIST_HEADER_ID = QPL.LIST_HEADER_ID.
• AMS_UTILITY_PVT (PACKAGE) — Provides GET_LOOKUP_MEANING, used to translate the AMS_OFFER_TYPE lookup code into its display meaning.

The join path is a three-way inner join across the offer header, the associated price list, and the product assignment view, filtered to offers that remain currently valid.

Key Columns

• ACTIVITY_PRODUCT_ID — Unique identifier of the activity product association row.
• CAMPAIGN_ID / MASTER_OBJECT_ID — Identifier of the parent campaign/offer master object.
• OFFER_CODE, STATUS_CODE, STATUS_DATE, START_DATE, END_DATE — Offer lifecycle attributes.
• OFFER_TYPE — Raw lookup code from the AMS_OFFER_TYPE lookup type.
• GET_LOOKUP_MEANING('AMS_OFFER_TYPE', OFF.OFFER_TYPE) — The translated, user-facing offer type meaning.
• DESCRIPTION — Description of the associated QP list header (price list).
• LEVEL_TYPE_CODE / LEVEL_TYPE — Indicates the granularity of the product assignment (item, category, etc.).
• CATEGORY_ID, CATEGORY_NAME, CATEGORY_SET_ID, CATEGORY_SET_NAME — Category context for the assigned product.
• ORGANIZATION_ID, INVENTORY_ITEM_ID, INVENTORY_ITEM_NUMBER, INVENTORY_ITEM_DESCRIPTION — Inventory item context.
• EXCLUDED_FLAG — Indicates whether the product is explicitly excluded from the offer.

Common Use Cases and Queries

Typical usage includes reporting all currently active offers with their associated products and price lists, or listing offers by translated offer type. The following query returns active offers and their products, showing the decoded offer type:

• SELECT campaign_id, offer_code, GET_LOOKUP_MEANING('AMS_OFFER_TYPE', OFFER_TYPE) offer_type_meaning, inventory_item_number, excluded_flag FROM APPS.AMS_P_OFFER_PRODUCTS_V;
• Filtering by offer type to isolate a single promotion class, grouping by OFFER_TYPE for distribution reporting, or joining to QP list tables using the offer's price list association.

Because the view already restricts to non-expired offers and already resolves the AMS_OFFER_TYPE lookup, it is a convenient source for dashboards and extracts that would otherwise require joining AMS_ACT_OFFERS, AMS_ACT_PRODUCTS_V, QP_LIST_HEADERS_VL, and the lookup table manually.