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:
AMS_ACT_PRODUCTS(SYNONYM) — the driving base object, aliasedAAP. It supplies the activity product identifier, level type code, category, organization, inventory item, quantity, and descriptive flexfield columns.AMS_LOOKUPS(VIEW) — joined twice.LKUP1resolvesARC_ACT_PRODUCT_USED_BYagainst lookup typeARC_ACT_PRODUCT_USED_BY;LKUP2resolvesLEVEL_TYPE_CODEagainst lookup typeAMS_PRODUCT_LEVEL.MTL_SYSTEM_ITEMS_VL(VIEW) — aliasedITM, joined on bothINVENTORY_ITEM_IDandORGANIZATION_ID, supplying concatenated segments and item description.MTL_CATEGORIES_V(VIEW) — aliasedICAT, joined onCATEGORY_IDto supplyCATEGORY_CONCAT_SEGS.MTL_CATEGORY_SETS_V(VIEW) — aliasedICST, joined onCATEGORY_SET_IDto supplyCATEGORY_SET_NAME.
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_BYand its resolvedMEANING(LKUP1.MEANING) — indicates the entity the product line is used by.ACT_PRODUCT_USED_BY_ID— foreign key to the referring entity.LEVEL_TYPE_CODEand its resolvedMEANING(LKUP2.MEANING) — the product level indicator, resolved via lookup typeAMS_PRODUCT_LEVEL; this is the column that answers an ams_product_level query.CATEGORY_ID,CATEGORY_SET_IDand their decoded namesCATEGORY_CONCAT_SEGSandCATEGORY_SET_NAME— category-level specifications.ORGANIZATION_ID,INVENTORY_ITEM_ID,CONCATENATED_SEGMENTS, and itemDESCRIPTION— 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_CATEGORYandATTRIBUTE1throughATTRIBUTE15— 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.
-
View: AMS_ACT_PRODUCTS_V 12.1.1
This view returns products / product categories associated to marketing objects like Events, campaigns.
APPS.AMS_ACT_PRODUCTS_V·↳ AMS_ACT_PRODUCTS·↳ AMS_LOOKUPS·↳ MTL_CATEGORIES_V·Explore AMS module →
-
View: AMS_ACT_PRODUCTS_V 12.2.2
This view returns products / product categories associated to marketing objects like Events, campaigns.
APPS.AMS_ACT_PRODUCTS_V·↳ AMS_ACT_PRODUCTS·↳ AMS_LOOKUPS·↳ MTL_CATEGORIES_V·Explore AMS module →
-
12.1.1 FND Design Data 12.1.1
-
12.2.2 FND Design Data 12.2.2