Results for “line_lumpsum_amount”

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

Overview

The AMS_ACT_PRODUCTS table resides in the AMS (Marketing) schema and stores information about the products that are promoted as part of a promotion event. It functions as the intersection between marketing activities — campaigns, events, and event offers — and the inventory items those activities promote. Each row represents a single product-to-activity association, capturing not only which item is being promoted, but also the commercial terms, forecasting attributes, and category context that apply to that promotion.

Within Oracle EBS 12.1.1 and 12.2.2, this table sits at the core of the Oracle Marketing and Trade Management data model. Promotion designers, campaign managers, and trade-promotion analysts depend on it to define the product set attached to a promotion, to associate promotional scan values and lump-sum amounts, and to support subsequent metric collection. Downstream, AMS_ACT_METRIC_FACTS_ALL references AMS_ACT_PRODUCTS through ACTIVITY_PRODUCT_ID, so the table effectively anchors promotional performance measurement.

From a heuristic Data Vault modeling perspective, the FK structure suggests a satellite-leaning classification. The table carries a dense set of descriptive and transactional attributes (flags, amounts, quantities, forecast values, UOM, channel) keyed to a single surrogate primary key, with foreign keys radiating outward to parent entities rather than forming a pure many-to-many link.

Key Information Stored

The documented physical schema for 12.2.2 contains 43 columns. The most significant are:

• ACTIVITY_PRODUCT_ID — Surrogate primary key (AMS_ACT_PRODUCTS_PK) and the sole documented unique-index column (AMS_ACT_PRODUCTS_U1). This is the primary business-key candidate for the row and is the column referenced by AMS_ACT_METRIC_FACTS_ALL.
• ACT_PRODUCT_USED_BY_ID — Polymorphic foreign key pointing to the owning activity, joining to AMS_CAMPAIGNS_ALL_B, AMS_EVENT_HEADERS_ALL_B, or AMS_EVENT_OFFERS_ALL_B depending on context.
• ARC_ACT_PRODUCT_USED_BY — Discriminator identifying which parent activity type ACT_PRODUCT_USED_BY_ID resolves to.
• INVENTORY_ITEM_ID and ORGANIZATION_ID — Identify the promoted item and its inventory organization, joining to MTL_SYSTEM_ITEMS_B and contributing to MTL_ITEM_CATEGORIES.
• CATEGORY_ID and CATEGORY_SET_ID — Category context for the promoted product, joining to AMS_CATEGORIES_B and MTL_ITEM_CATEGORIES.
• PRIMARY_PRODUCT_FLAG — Marks the lead product for an activity.
• PRODUCT_SALE_TYPE and LEVEL_TYPE_CODE — Control how the product is sold and the level at which the association applies.
• ENABLED_FLAG and EXCLUDED_FLAG — Govern whether the product is active in the promotion or explicitly excluded.
• LINE_LUMPSUM_AMOUNT, LINE_LUMPSUM_QTY, QUANTITY — Commercial values for the line.
• SCAN_VALUE, SCAN_UNIT_FORECAST, UOM_CODE, ADJUSTMENT_FLAG, CHANNEL_ID — Scan-based promotion and forecast attributes.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing multi-org/security data partitioning.
• Standard audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER) and 15 ATTRIBUTE flex columns.

Common Use Cases and Queries

Typical reporting includes listing all products promoted under a given campaign or event, verifying category coverage, and reconciling promotional scan values. A simple retrieval by owning activity:

• SELECT ap.ACTIVITY_PRODUCT_ID, ap.INVENTORY_ITEM_ID, ap.ORGANIZATION_ID, ap.SCAN_VALUE FROM AMS.AMS_ACT_PRODUCTS ap WHERE ap.ACT_PRODUCT_USED_BY_ID = :activity_id AND ap.ENABLED_FLAG = 'Y';
• Joining to items for item descriptions: SELECT ap.*, msi.SEGMENT1 FROM AMS.AMS_ACT_PRODUCTS ap, MTL_SYSTEM_ITEMS_B msi WHERE ap.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID AND ap.ORGANIZATION_ID = msi.ORGANIZATION_ID;
• Aggregating promoted product counts per campaign via AMS_CAMPAIGNS_ALL_B.
• Measuring performance by linking ACTIVITY_PRODUCT_ID to AMS_ACT_METRIC_FACTS_ALL for scan or volume actuals versus SCAN_UNIT_FORECAST.

Related Objects

• AMS_CAMPAIGNS_ALL_B — joined via ACT_PRODUCT_USED_BY_ID when the parent activity is a campaign.
• AMS_EVENT_HEADERS_ALL_B and AMS_EVENT_OFFERS_ALL_B — joined via ACT_PRODUCT_USED_BY_ID for event and offer parents.
• MTL_SYSTEM_ITEMS_B — joined via INVENTORY_ITEM_ID and ORGANIZATION_ID for item detail.
• MTL_ITEM_CATEGORIES — joined via INVENTORY_ITEM_ID, ORGANIZATION_ID, and CATEGORY_SET_ID.
• AMS_CATEGORIES_B — joined via CATEGORY_ID for category name and hierarchy.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID for security partitioning.
• AMS_ACT_METRIC_FACTS_ALL — references AMS_ACT_PRODUCTS through ACTIVITY_PRODUCT_ID, providing the downstream metric fact linkage.