Results for “ams_product_level”

10 results




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

Overview

APPS.AMS_ACT_PRODUCTS_V is a reporting and integration view in the Oracle E-Business Suite Oracle Marketing (AMS) module. It exposes the product-level detail lines attached to a marketing activity, campaign, or event, denormalizing the underlying transaction table AMS_ACT_PRODUCTS with descriptive information drawn from inventory, category, and lookup reference views. The view is the standard access point for reporting on which products, categories, or organization levels are associated with an activity, and for extracting that relationship in a form suitable for downstream integration.

The name typically associated with this object in user searches is ams_product_level. This phrase corresponds directly to the lookup type AMS_PRODUCT_LEVEL that the view joins against to translate LEVEL_TYPE_CODE into a meaningful description. The view therefore serves as the primary mechanism for resolving the level at which a product specification applies within an activity product line.

Underlying Base Objects

The view is defined over one synonym and four supporting views, joined by outer joins so that an activity product row is never lost when reference data is missing:

Key Columns

  • ACTIVITY_PRODUCT_ID — primary identifier for the activity product line; the principal join key to activity and campaign tables.
  • OBJECT_VERSION_NUMBER — optimistic locking column carried through from the base table.
  • ARC_ACT_PRODUCT_USED_BY and its resolved MEANING (LKUP1.MEANING) — indicates the entity the product line is used by.
  • ACT_PRODUCT_USED_BY_ID — foreign key to the referring entity.
  • LEVEL_TYPE_CODE and its resolved MEANING (LKUP2.MEANING) — the product level indicator, resolved via lookup type AMS_PRODUCT_LEVEL; this is the column that answers an ams_product_level query.
  • CATEGORY_ID, CATEGORY_SET_ID and their decoded names CATEGORY_CONCAT_SEGS and CATEGORY_SET_NAME — category-level specifications.
  • ORGANIZATION_ID, INVENTORY_ITEM_ID, CONCATENATED_SEGMENTS, and item DESCRIPTION — item-level specifications.
  • EXCLUDED_FLAG, ADJUSTMENT_FLAG, LINE_LUMPSUM_QTY, LINE_LUMPSUM_AMOUNT, SCAN_VALUE, SCAN_UNIT_FORECAST, CHANNEL_ID, QUANTITY, UOM_CODE — forecasting, scan, and quantity attributes.
  • ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the descriptive flexfield segment values.

Common Use Cases and Queries

Typical usage includes reporting all products attached to a campaign, determining the level (ams_product_level) at which each product line is defined, and exporting activity product data for integration or forecasting analysis.

Retrieving product lines for an activity, resolving level and item description:

  • SELECT activity_product_id, level_type_code, meaning, inventory_item_id, concatenated_segments, description, quantity FROM apps.ams_act_products_v WHERE activity_product_id = :p_id;
  • SELECT level_type_code, meaning, COUNT(*) FROM apps.ams_act_products_v GROUP BY level_type_code, meaning; — distribution of activity products by level, satisfying an ams_product_level lookup requirement.
  • SELECT activity_product_id, concatenated_segments, category_concat_segs, category_set_name, excluded_flag FROM apps.ams_act_products_v WHERE organization_id = :org AND inventory_item_id IS NOT NULL;

Because all reference joins are outer joins, rows persist even when item, category, or lookup values are absent, making the view safe for exhaustive reporting.