Search Results ozf_offer_discount_products

Overview

The OZF_OFFER_DISCOUNT_PRODUCTS table is a core data entity within the Oracle Trade Management (OZF) module of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. It serves as a junction table that defines the specific products or items eligible for a promotional offer or discount. Its primary role is to establish the relationship between an offer's discount line and the inventory items to which that discount applies. This table is essential for enforcing product-level targeting in trade promotions, ensuring discounts are correctly calculated and claimed only for the authorized products.

Key Information Stored

The table's structure centers on linking identifiers and storing product eligibility criteria. The primary key column, OFF_DISCOUNT_PRODUCT_ID, uniquely identifies each product-to-offer association record. The two critical foreign key columns are OFFER_ID, which links to the parent promotion in OZF_OFFERS, and OFFER_DISCOUNT_LINE_ID, which links to the specific discount rule or tier defined in OZF_OFFER_DISCOUNT_LINES. While the provided metadata does not list all columns, typical implementations would include fields to identify the product, such as INVENTORY_ITEM_ID and ORGANIZATION_ID, and potentially attributes for product categories, brands, or other hierarchical groupings to define eligibility rules. Additional columns likely manage object versioning, creation, and last update dates.

Common Use Cases and Queries

This table is central to queries that validate product eligibility, analyze promotion performance, and support claim processing. A common operational use case is verifying if a specific sales order line item qualifies for a promotional discount during order entry. For reporting, analysts query this table to list all products associated with an active offer or to analyze the distribution of discounts across a product portfolio. Sample SQL patterns include fetching all eligible products for a given offer or discount line.

• Retrieve all products for a specific offer: SELECT * FROM ozf_offer_discount_products WHERE offer_id = <offer_id>;
• Validate a specific item for an offer: SELECT COUNT(*) FROM ozf_offer_discount_products odp JOIN ozf_offers o ON odp.offer_id = o.offer_id WHERE odp.inventory_item_id = <item_id> AND o.status = 'ACTIVE';

Related Objects

OZF_OFFER_DISCOUNT_PRODUCTS maintains integral relationships with several key Trade Management tables, forming a critical part of the offer definition hierarchy. As documented, its primary foreign key relationships are with OZF_OFFERS (the master offer header) and OZF_OFFER_DISCOUNT_LINES (the specific discount rules). Furthermore, it is referenced as a parent table by OZF_DISCOUNT_PRODUCT_RELN via the OFF_DISCOUNT_PRODUCT_ID column, which may store additional, more complex product relationship data. This table is also a likely source for key product eligibility views within the OZF schema, and data from it is typically accessed via public Oracle Trade Management APIs for integration and extension purposes.