Search Results ozf_offer_products




Overview
Explore in AI Chat

Overview

The OZF_OFFER_PRODUCTS table is a core transactional data store within the Oracle Trade Management (OZF) module of Oracle E-Business Suite (EBS). It functions as the master repository for product line items associated with active promotional offers. As per the official documentation, this table specifically stores all products defined for "request only active offers" created through the Trade Management application. Its primary role is to establish and maintain the relationship between an offer and the specific products or product categories to which the offer's pricing, discount, or funding terms apply. This enables the precise targeting and execution of trade promotions within the EBS ecosystem.

Key Information Stored

The table's structure is designed to link an offer to its applicable products and to define the offer's commercial terms for those products. The primary key is the OFFER_PRODUCT_ID, a unique system-generated identifier for each product-offer association. A critical foreign key column is QP_LIST_HEADER_ID, which links the record to a price list (QP_LIST_HEADERS_B) in Oracle Pricing (QP). This linkage is fundamental, as it associates the promotional offer with the specific pricing rules and modifiers defined in the Pricing module. While the provided metadata does not list all columns, typical data stored includes the parent OFFER_ID, product identifiers (such as INVENTORY_ITEM_ID and ORGANIZATION_ID), and attributes controlling eligibility, application, and status of the product within the offer.

Common Use Cases and Queries

This table is central to reporting and analytics on promotional product coverage and for validating offer setup. Common use cases include generating a list of all products eligible for a specific active promotion, auditing which offers apply to a particular product, and ensuring pricing consistency between Trade Management and Pricing modules. A typical query would join OZF_OFFER_PRODUCTS to the offer header table (OZF_OFFERS) and product master tables (such as MTL_SYSTEM_ITEMS_B). For example, to list active offers for a product, one might use:

  • SELECT off.OFFER_NUMBER, off.NAME, op.OFFER_PRODUCT_ID
  • FROM OZF_OFFER_PRODUCTS op, OZF_OFFERS off
  • WHERE op.OFFER_ID = off.OFFER_ID
  • AND op.INVENTORY_ITEM_ID = :item_id
  • AND off.STATUS = 'ACTIVE';

Related Objects

The OZF_OFFER_PRODUCTS table maintains defined relationships with other key EBS objects, primarily through foreign key constraints. The documented foreign key relationship is with the Oracle Pricing module:

  • QP_LIST_HEADERS_B: Linked via OZF_OFFER_PRODUCTS.QP_LIST_HEADER_ID. This is a critical integration point, ensuring the promotional offer's product terms are governed by a valid price list from QP.

While not listed in the provided metadata, this table is inherently linked to the offer header (OZF_OFFERS) via an OFFER_ID column and to inventory/product tables (e.g., MTL_SYSTEM_ITEMS_B) via product identifier columns. It is also a likely parent table for more granular adjustment or fulfillment detail tables within the OZF schema.