Search Results ozf_offer_adjustment_tiers




Overview
Explore in AI Chat

Overview

The OZF_OFFER_ADJUSTMENT_TIERS table is a core data object within the Oracle E-Business Suite (EBS) Trade Management module (OZF). It serves as the primary repository for storing tiered pricing or discount structures associated with promotional offers. In the context of Oracle Trade Management, an offer adjustment defines a specific discount, chargeback, or other financial incentive. This table enables the definition of multi-level, volume-based, or quantity-based pricing tiers for those adjustments, allowing for complex, graduated promotional strategies. Its role is critical for implementing sophisticated trade promotions where the benefit to a trading partner varies based on predefined performance thresholds.

Key Information Stored

The table's central purpose is to define the sequential tiers of an adjustment. While the full column list is not detailed in the provided metadata, the structure revolves around a unique tier identifier and its relationship to a parent adjustment. The documented primary key is OFFER_ADJST_TIER_ID, which uniquely identifies each tier record. The foreign key column OFFER_ADJUSTMENT_ID is mandatory, linking each tier to its parent offer adjustment defined in the OZF_OFFER_ADJUSTMENTS_B table. Typical columns in such a tier table would include sequence numbers (TIER_NUMBER), threshold values (e.g., MIN_QUANTITY, MIN_AMOUNT), and the corresponding adjustment value or percentage (e.g., ADJUSTMENT_AMOUNT, ADJUSTMENT_PERCENT) that applies once the threshold is met. Standard EBS audit columns like CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, and LAST_UPDATED_BY are also expected.

Common Use Cases and Queries

This table is central to configuring and analyzing tiered promotions. A common business use case is creating a volume discount where a reseller receives a 5% discount for orders over 100 units, and a 10% discount for orders over 500 units; each of these rules would be a separate record in this table. For reporting, analysts query this table to list all tiers for a specific promotion or to validate tier structures. A typical SQL pattern involves joining to the parent adjustment and master offer tables:

  • Listing Tiers for an Adjustment: SELECT tier.tier_number, tier.min_quantity, tier.adjustment_percent FROM ozf_offer_adjustment_tiers tier, ozf_offer_adjustments_b adj WHERE tier.offer_adjustment_id = adj.offer_adjustment_id AND adj.offer_id = :p_offer_id ORDER BY tier.tier_number;
  • Data Validation Query: Checking for gaps or overlaps in tier thresholds often requires analytical LAG/LEAD functions against the sequence and threshold columns.

Related Objects

The table maintains a strict hierarchical relationship within the Trade Management schema. Its primary integration is as a child entity to the offer adjustment header. As documented, the table OZF_OFFER_ADJUSTMENT_TIERS contains a foreign key relationship where OZF_OFFER_ADJUSTMENT_TIERS.OFFER_ADJUSTMENT_ID references the OZF_OFFER_ADJUSTMENTS_B table. This establishes a one-to-many relationship, where a single adjustment can have multiple defined tiers. The OZF_OFFER_ADJUSTMENTS_B table itself is linked to the offer definition (OZF_OFFERS), making OZF_OFFER_ADJUSTMENT_TIERS a key detail table in the overall offer structure. While not listed in the excerpt, this table is likely referenced by transactional or calculation engines within the OZF module that apply the correct tier logic during order processing or settlement.