Results for “activity_product_id”

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

Overview

AMS_P_OFFER_PRODUCTS_V is a public view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMS (Marketing) product family. It returns the products associated with marketing offers, exposing offer-level attributes alongside the product, category, and organization context to which each offer applies. The view is classified as VALID in both ETRM 12.1.1 and 12.2.2 and is intended for reporting, integration, and inquiry rather than transactional maintenance. Because it is a public (APPS-owned) view, it may be queried directly from custom reports, concurrent programs, OBIEE/BI Publisher extracts, or interface programs without requiring access to the underlying transactional tables. A key design characteristic is that the view filters out offers whose END_DATE has passed, so only currently active or open-ended offers are returned.

Underlying Base Objects

The view text defines a three-way join over the following documented base objects, all resolved through APPS synonyms:

• AMS_ACT_OFFERS (SYNONYM) — the marketing offer header. Joined on OFF.ACTIVITY_OFFER_ID = AAP.MASTER_OBJECT_ID and restricted by AAP.MASTER_OBJECT_TYPE = 'OFFR'.
• AMS_ACT_PRODUCTS_V (VIEW) — the product/level assignment lines. Provides the category, category set, organization, inventory item, and excluded-flag columns.
• QP_LIST_HEADERS_VL (VIEW) — the Oracle Advanced Pricing list header, joined on OFF.QP_LIST_HEADER_ID = QPL.LIST_HEADER_ID, supplying the offer description.
• AMS_UTILITY_PVT (PACKAGE) — invoked as AMS_UTILITY_PVT.GET_LOOKUP_MEANING('AMS_OFFER_TYPE', OFF.OFFER_TYPE) to translate the offer type code into its lookup meaning.

The join condition (OFF.END_DATE IS NULL OR OFF.END_DATE > SYSDATE) enforces the active-offer restriction. Note the view text selects AAP.MASTER_OBJECT_ID using the alias CAMPAIGN_ID, while the documented column list exposes it as OFFER_ID.

Key Columns

• ACTIVITY_PRODUCT_ID — primary key of the product assignment line.
• OBJECT_VERSION_NUMBER — optimistic locking version for the source product row.
• OFFER_ID / OFFER_NAME / OFFER_CODE — identifying attributes of the parent marketing offer.
• OFFER_STATUS_CODE, OFFER_STATUS_DATE — offer lifecycle state and the date it was set.
• OFFER_START_DATE, OFFER_END_DATE — validity window of the offer.
• OFFER_TYPE, OFFER_TYPE_NAME — the AMS_OFFER_TYPE lookup code and its decoded meaning.
• LEVEL_TYPE_CODE, LEVEL_TYPE — the level at which the product is attached (for example category versus item).
• CATEGORY_ID, CATEGORY_NAME, CATEGORY_SET_ID, CATEGORY_SET_NAME — category context of the assignment.
• ORGANIZATION_ID — inventory organization context.
• INVENTORY_ITEM_ID, INVENTORY_ITEM_NUMBER, INVENTORY_ITEM_DESCRIPTION — the product identity. INVENTORY_ITEM_NUMBER is the user-facing item number that administrators and report authors most commonly search on.
• EXCLUDED_FLAG — indicates whether the product is excluded from the offer.

Common Use Cases and Queries

Typical scenarios include listing all products on active offers, locating which offers reference a given item number, and feeding offer-to-item mappings into pricing or order capture integrations. The following example retrieves active offer/product combinations for a specific item number:

• SELECT offer_id, offer_name, offer_code, offer_status_code, offer_start_date, offer_end_date, inventory_item_number, inventory_item_description, category_name, organization_id, excluded_flag FROM apps.ams_p_offer_products_v WHERE inventory_item_number = 'AS54888';
• SELECT offer_code, offer_type_name, inventory_item_number, category_name FROM apps.ams_p_offer_products_v WHERE excluded_flag = 'N' AND offer_status_code = 'ACTIVE' ORDER BY offer_code, inventory_item_number;

Because the view always applies the END_DATE filter, reports requiring historical or expired offers must query AMS_ACT_OFFERS and AMS_ACT_PRODUCTS_V directly rather than this view.