Results for “ams_act_products_v”

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

Overview

The AMS_ACT_PRODUCTS_V view is a marketing module (AMS) reporting object owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It returns the products and product categories associated with marketing objects such as events and campaigns. The view is a denormalized read layer over the AMS_ACT_PRODUCTS base table, resolving foreign-key identifiers into descriptive values drawn from inventory, category, and lookup sources. Because it presents human-readable item numbers, category names, and lookup meanings directly, it is commonly used for reporting, concurrent program queries, and integration extracts that require the marketing product association without performing multiple joins manually.

Underlying Base Objects

The view is defined over five documented objects:

• AMS_ACT_PRODUCTS (synonym) — the primary transactional table holding activity/product associations, including quantity, forecast, and scan-related attributes.
• AMS_LOOKUPS (view) — joined twice via outer joins to resolve the ARC_ACT_PRODUCT_USED_BY and AMS_PRODUCT_LEVEL lookup codes into meanings.
• MTL_CATEGORIES_V (view) — provides the concatenated category segments for the CATEGORY_ID.
• MTL_CATEGORY_SETS_V (view) — provides the category set name for the CATEGORY_SET_ID.
• MTL_SYSTEM_ITEMS_VL (view) — provides the concatenated item segments and description for the INVENTORY_ITEM_ID, joined on both organization and item.

All joins to the lookup, category, category set, and item views are outer joins (denoted (+)), so rows persist even when descriptive data is missing. The lookup joins use the outer-join syntax with the lookup type and code matched against the base table columns.

Key Columns

The view exposes the following principal columns:

• ACTIVITY_PRODUCT_ID — primary identifier for the product/activity association.
• OBJECT_VERSION_NUMBER — optimistic locking version for the row.
• MASTER_OBJECT_TYPE / MEANING, MASTER_OBJECT_ID — the parent marketing object (for example, an event or campaign).
• LEVEL_TYPE_CODE / LEVEL_TYPE — whether the association is a product or a product category.
• CATEGORY_ID, CATEGORY_NAME, CATEGORY_SET_ID, CATEGORY_SET_NAME — category identifiers and resolved descriptive names.
• ORGANIZATION_ID, INVENTORY_ITEM_ID, INVENTORY_ITEM_NUMBER, INVENTORY_ITEM_DESCRIPTION — item identifiers and resolved item attributes.
• EXCLUDED_FLAG, ADJUSTMENT_FLAG — status flags controlling inclusion and adjustment behavior.
• LINE_LUMPSUM_QTY, LINE_LUMPSUM_AMOUNT, QUANTITY — quantity and monetary measures.
• SCAN_VALUE — the scan value attribute carried on the association, returned directly from AMS_ACT_PRODUCTS and relevant to scan-based product listings.
• UOM_CODE, SCAN_UNIT_FORECAST, CHANNEL_ID — unit of measure, forecast unit, and channel context.

Common Use Cases and Queries

Typical scenarios include listing all products attached to a campaign, reporting item-level quantities and forecasts, and extracting product associations for integration. A representative query retrieving product associations for a given master object is:

SELECT ACTIVITY_PRODUCT_ID,
       MASTER_OBJECT_ID,
       LEVEL_TYPE,
       INVENTORY_ITEM_NUMBER,
       INVENTORY_ITEM_DESCRIPTION,
       CATEGORY_NAME,
       QUANTITY,
       UOM_CODE,
       SCAN_VALUE
FROM   APPS.AMS_ACT_PRODUCTS_V
WHERE  MASTER_OBJECT_ID = :p_object_id
AND    NVL(EXCLUDED_FLAG,'N') = 'N';

A scan-oriented query isolating rows with a populated scan value is:

SELECT ACTIVITY_PRODUCT_ID, INVENTORY_ITEM_NUMBER, SCAN_VALUE, SCAN_UNIT_FORECAST
FROM   APPS.AMS_ACT_PRODUCTS_V
WHERE  SCAN_VALUE IS NOT NULL;

Because descriptive values are resolved through outer joins, reports should guard against null item and category descriptions. Filtering on LEVEL_TYPE_CODE distinguishes product-level rows from category-level rows, and CHANNEL_ID can be used to aggregate forecasts by channel.