Search Results ozf_offers

Overview

The OZF_OFFERS table is a core data object within the Oracle Trade Management (OZF) module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It functions as a surrogate table that extends the pricing entity QP_LIST_HEADERS_B to specifically manage promotional offers. Its primary role is to store trade management-specific attributes and lifecycle information for an offer that are not captured in the base pricing tables. The table is central to the offer lifecycle, linking the foundational pricing definition from Oracle Pricing (QP) to the execution, performance tracking, and financial management capabilities of Trade Management.

Key Information Stored

The table's structure is defined by its primary and foreign keys, which delineate its critical data relationships. The surrogate primary key is OFFER_ID. The table enforces uniqueness on OFFER_CODE and, crucially, on QP_LIST_HEADER_ID, which is the foreign key linking directly to the pricing list header in QP_LIST_HEADERS_B. Beyond this link, the table stores essential offer management data. This includes the offer's USER_STATUS_ID (for lifecycle status), PERFORMANCE_START_DATE and PERFORMANCE_END_DATE, and attributes for managing lump-sum offer types. It also holds foreign keys to define the offer's OWNER_ID (resource), BUDGET_SOURCE_ID (funding), ACTIVITY_MEDIA_ID, and associated contacts (BUYING_GROUP_CONTACT_ID) and approval rules (NA_RULE_HEADER_ID).

Common Use Cases and Queries

This table is pivotal for reporting and integration processes related to trade promotions. Common use cases include generating performance reports for active offers, validating offer setup by checking for missing critical attributes, and extracting offer data for downstream financial systems. A typical query would join OZF_OFFERS with QP_LIST_HEADERS_B to get a complete view of an offer's pricing rules and trade management status.

• Sample Query: Retrieve Active Offers with Basic Details
  SELECT o.offer_code, o.offer_id, qlh.name list_name, o.performance_start_date, o.performance_end_date, aus.name user_status FROM ozf_offers o, qp_list_headers_b qlh, ams_user_statuses_b aus WHERE o.qp_list_header_id = qlh.list_header_id AND o.user_status_id = aus.user_status_id AND sysdate BETWEEN o.performance_start_date AND o.performance_end_date;
• Data Validation: Queries often check for offers where the QP_LIST_HEADER_ID is null or where performance dates are inconsistent.
• Integration: The OFFER_ID or QP_LIST_HEADER_ID is used as a key to fetch related discount lines, qualifiers, adjustments, and forecast data from child tables.

Related Objects

OZF_OFFERS sits at the center of a complex data model with extensive relationships. Its primary foreign key dependency is on QP_LIST_HEADERS_B for the core pricing definition. It is referenced as a parent table by numerous key Trade Management entities, forming a hierarchy of offer-related data. Major child tables include OZF_OFFER_DISCOUNT_LINES, OZF_OFFER_QUALIFIERS, and OZF_OFFER_ADJUSTMENTS_B for offer composition. For performance and forecasting, it is linked to OZF_OFFER_PERFORMANCES, OZF_ACT_FORECASTS_ALL, and the various OZF_FORECAST_* tables. The table also integrates with the broader application framework through foreign keys to AMS_USER_STATUSES_B for status, JTF_RS_RESOURCE_EXTNS for ownership, and OZF_FUNDS_ALL_B for budgeting.