Search Results ams_offer_products

Overview

The AMS_OFFER_PRODUCTS table is a core data object within the Oracle E-Business Suite (EBS) Marketing (AMS) module, specifically for versions 12.1.1 and 12.2.2. Its primary function is to store the product definitions associated with active offers created through the Oracle Marketing Online (OML) interface. The table acts as a junction, linking marketing offers to the specific products or product lists from the Quoting (QP) module that are being promoted. It is designed to hold data exclusively for "request only active offers," indicating it supports targeted, transactional marketing campaigns rather than static promotional catalogs.

Key Information Stored

While the provided metadata does not list all columns, the structure centers on the relationship between an offer and its constituent products. The critical columns include the primary key, OFFER_PRODUCT_ID, which uniquely identifies each product association within an offer. A central foreign key is QP_LIST_HEADER_ID, which links to the QP_LIST_HEADERS_B table. This connection allows a marketing offer to reference a predefined price list or a list of items from the Advanced Pricing module, thereby defining the exact products, their pricing, and eligibility rules for the campaign. Other typical columns would likely include OFFER_ID (linking to the parent offer in a table like AMS_OFFERS_ALL), creation dates, and active status indicators.

Common Use Cases and Queries

This table is essential for generating the product-specific details of a marketing offer for both operational and analytical purposes. Common use cases include generating fulfillment data for an order captured from a campaign, validating product eligibility during offer presentation, and reporting on which products are most frequently promoted. A typical query would join this table to the offer header and product list tables to retrieve a complete offer definition.

• Sample Query: SELECT op.offer_product_id, o.offer_name, qlh.name list_name FROM ams_offer_products op, ams_offers_all o, qp_list_headers_b qlh WHERE op.offer_id = o.offer_id AND op.qp_list_header_id = qlh.list_header_id AND o.status_code = 'ACTIVE';
• Reporting: Analysts use this table to create reports on product penetration across campaigns, analyzing the correlation between promoted products and resulting sales.

Related Objects

The AMS_OFFER_PRODUCTS table has defined relationships with other key EBS objects. Its primary foreign key relationship is with QP_LIST_HEADERS_B, the table storing price list headers. This is the critical link to the Quoting and Advanced Pricing functionality. The parent table is almost certainly AMS_OFFERS_ALL, which stores the header information for marketing offers, though this specific foreign key is not detailed in the snippet. The table is protected by the primary key constraint AMS_OFFER_PRODUCTS_PK on the OFFER_PRODUCT_ID column. Data is typically created, updated, and accessed via the Oracle Marketing Online application's standard user interface and its underlying PL/SQL APIs.