Search Results ams_act_products

Overview

The AMS_ACT_PRODUCTS table is a core data object within the Oracle E-Business Suite Marketing (AMS) module, specifically for versions 12.1.1 and 12.2.2. It serves as the central repository for associating products with marketing activities. Its primary function is to store detailed information about the specific products, inventory items, or product categories that are being promoted as part of a marketing campaign, event, or offer. This table enables the tracking and management of the product dimension for all promotional efforts, forming a critical link between the marketing execution system and the product master data from Oracle Inventory.

Key Information Stored

The table's structure is designed to capture the relationship between a marketing activity and the product being promoted. The primary key is the ACTIVITY_PRODUCT_ID, a unique system-generated identifier for each product-activity association. Key foreign key columns define the relationships to other entities. The ACT_PRODUCT_USED_BY_ID column links the record to the specific parent marketing object, such as a campaign (AMS_CAMPAIGNS_ALL_B), event header (AMS_EVENT_HEADERS_ALL_B), or event offer (AMS_EVENT_OFFERS_ALL_B). Product identification is achieved through the INVENTORY_ITEM_ID and ORGANIZATION_ID columns, which reference the master item definition in MTL_SYSTEM_ITEMS_B. Alternatively, promotions can be defined at the category level using the CATEGORY_ID (referencing AMS_CATEGORIES_B) and CATEGORY_SET_ID columns, which also link to MTL_ITEM_CATEGORIES for validation.

Common Use Cases and Queries

This table is fundamental for generating reports on product promotion performance and for validating setup. Common analytical queries involve joining AMS_ACT_PRODUCTS to campaign or event headers and metric fact tables to assess the revenue or cost impact of promoting specific items. A typical query pattern retrieves all products associated with a given campaign for a listing or validation report.

• Product Listing for a Campaign: SELECT aap.inventory_item_id, msi.segment1 item_code FROM ams_act_products aap, mtl_system_items_b msi WHERE aap.act_product_used_by_id = :p_campaign_id AND aap.inventory_item_id = msi.inventory_item_id AND aap.organization_id = msi.organization_id;
• Performance Reporting: Joining AMS_ACT_PRODUCTS to AMS_ACT_METRIC_FACTS_ALL via ACTIVITY_PRODUCT_ID allows for the aggregation of metrics like responses or sales, segmented by the promoted product or category.

Related Objects

AMS_ACT_PRODUCTS has extensive relationships within the Marketing and Inventory modules, as defined by its foreign key constraints. Its primary parent relationships are with campaign, event, and offer tables (AMS_CAMPAIGNS_ALL_B, AMS_EVENT_HEADERS_ALL_B, AMS_EVENT_OFFERS_ALL_B). For product data, it relies on Inventory's item master (MTL_SYSTEM_ITEMS_B) and category tables (MTL_ITEM_CATEGORIES, AMS_CATEGORIES_B). A key dependent object is the AMS_ACT_METRIC_FACTS_ALL table, which stores measurable outcomes and uses the ACTIVITY_PRODUCT_ID to tie those facts back to the specific promoted product. This relationship is crucial for closed-loop marketing analysis.