Search Results ozf_act_offers

Overview

The OZF_ACT_OFFERS table is a core transactional entity within the Oracle Trade Management (OZF) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. As documented in the ETRM, its primary role is to store the high-level details of promotional offers that are associated with a marketing campaign. It functions as a central linking table between the campaign management framework and the specific promotional offers defined within the Trade Management system. This table is essential for managing the lifecycle of trade promotions, enabling businesses to plan, execute, and analyze promotional activities tied to broader marketing initiatives.

Key Information Stored

The table's structure is designed to capture the relationship between an offer and its parent campaign. While the full column list is not detailed in the provided excerpt, the metadata highlights critical identifiers. The primary key is the ACTIVITY_OFFER_ID, a unique system-generated identifier for each record. Two significant foreign key relationships are explicitly noted: the QP_LIST_HEADER_ID links the promotional offer to a price list header in the Oracle Pricing (QP) module, defining the specific pricing rules of the offer. The ACT_OFFER_USED_BY_ID column establishes a foreign key relationship to the AMS_CAMPAIGNS_ALL_B table, which is part of the Oracle Marketing (AMS) foundation, thereby formally associating the offer with a specific marketing campaign.

Common Use Cases and Queries

This table is pivotal for queries that analyze the composition and performance of trade promotions within campaigns. Common reporting use cases include listing all offers for a specific campaign, identifying offers that use a particular pricing list, or auditing offer creation. A typical SQL pattern to retrieve offer details for a campaign would involve joining OZF_ACT_OFFERS with the campaign and offer master tables. For example:

• SELECT cao.ACTIVITY_OFFER_ID, ozf.OFFER_NAME, ams.CAMPAIGN_NAME FROM OZF_ACT_OFFERS cao, OZF_OFFERS ozf, AMS_CAMPAIGNS_ALL_B ams WHERE cao.ACT_OFFER_USED_BY_ID = ams.CAMPAIGN_ID AND cao.QP_LIST_HEADER_ID = ozf.QP_LIST_HEADER_ID AND ams.CAMPAIGN_NUMBER = 'CAMP1001';

This table is also critical for data integrity during processes like campaign copy or when generating performance reports that roll up offer-level metrics to the campaign level.

Related Objects

The OZF_ACT_OFFERS table sits at a key integration point between modules. As per the provided foreign key metadata, its primary relationships are:

• AMS_CAMPAIGNS_ALL_B: The master table for marketing campaigns. The ACT_OFFER_USED_BY_ID foreign key enforces that every promotional offer record in OZF_ACT_OFFERS is linked to a valid campaign.
• OZF_OFFERS: The master table for trade promotion offers within the OZF module. The relationship via QP_LIST_HEADER_ID connects the campaign activity to the detailed offer definition and its pricing rules.
• QP_LIST_HEADERS (implied): While not listed, the QP_LIST_HEADER_ID foreign key logically references the pricing list headers in the QP schema, which define the specific terms of the promotional offer.

This network of relationships underscores the table's role in integrating Trade Management's promotional execution with the Marketing module's campaign planning and the Pricing module's rules engine.