Search Results ams_offers




Overview
Explore in AI Chat

Overview

The AMS_OFFERS 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 functions as a surrogate table to the pricing entity QP_LIST_HEADERS_B, extending the standard pricing list functionality with marketing-specific attributes required for offer management. Its primary role is to serve as the central repository for defining and tracking marketing offers, bridging the gap between the advanced pricing engine and the marketing execution system. By maintaining a foreign key relationship to QP_LIST_HEADERS_B via the QP_LIST_HEADER_ID column, it ensures that every marketing offer is fundamentally linked to a valid price list, while storing additional metadata critical for campaign and promotion management.

Key Information Stored

The table's structure is designed to capture the unique lifecycle and characteristics of a marketing offer. The primary identifier is the OFFER_ID (surrogate key), with OFFER_CODE and QP_LIST_HEADER_ID serving as unique alternate keys. Beyond the essential link to the pricing list, the table stores crucial operational data. This includes status management via USER_STATUS_ID (linked to AMS_USER_STATUSES_B), performance dates for scheduling, and attributes for specific offer types like lumpsum offers. It also holds relational data such as the OFFER_OWNER_ID (linked to JTF_RS_RESOURCE_EXTNS), the ACTIVITY_MEDIA_ID (linked to AMS_MEDIA_B), and a BUYING_GROUP_CONTACT_ID (linked to HZ_PARTIES), which collectively define the offer's ownership, communication channel, and associated customer contact.

Common Use Cases and Queries

This table is central to queries involving offer lifecycle reporting, performance analysis, and integration checks. A common use case is generating a report of all active offers with their associated price list details and status. A typical query pattern would join AMS_OFFERS with QP_LIST_HEADERS_B and the relevant status table.

  • Sample Query: Basic Offer Listing
    SELECT ao.offer_code, ao.offer_name, qlh.name price_list_name, aus.name user_status
    FROM ams_offers ao,
    qp_list_headers_b qlh,
    ams_user_statuses_b aus
    WHERE ao.qp_list_header_id = qlh.list_header_id
    AND ao.user_status_id = aus.user_status_id
    AND SYSDATE BETWEEN ao.start_date AND NVL(ao.end_date, SYSDATE+1);
  • Integration Validation: Identifying offers missing a corresponding price list entry by checking for NULL or orphaned QP_LIST_HEADER_ID values.
  • Ownership Reporting: Querying offers owned by a specific marketing resource by filtering on OWNER_ID.

Related Objects

AMS_OFFERS is a hub table with significant dependencies. Its primary relationship is with QP_LIST_HEADERS_B in the Pricing (QP) module, which defines the core pricing rules. Key foreign key relationships, as documented, link it to several foundational tables: AMS_USER_STATUSES_B for status tracking, AMS_CUSTOM_SETUPS_B for configuration, AMS_MEDIA_B for the activity channel, JTF_RS_RESOURCE_EXTNS for resource management, and HZ_PARTIES for customer data. A notable reverse relationship exists where the table PV_PG_INVITE_HEADERS_B references AMS_OFFERS.QP_LIST_HEADER_ID, indicating its use in Partner Management (PV) processes for invitation generation. This network of relationships positions AMS_OFFERS as a critical integration point between Marketing, Pricing, Sales, and Partner modules.