Results for “break_type_code”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

OZF.OZF_VOLUME_OFFER_TIERS is a Trade Management (OZF) transaction table that stores the tier definitions associated with volume-based offers. In Oracle EBS Release 12.1.1 and 12.2.2, volume offers allow an organization to grant graduated discounts or accruals based on the quantity or value a customer purchases. Each row in this table represents a single tier — a discrete volume break point — within such an offer, capturing the threshold, the discount mechanism, and the resulting benefit. The table is owned by the OZF schema and is documented as VALID in the ETRM data dictionary.

From a dimensional modeling perspective, the metadata classifies this object as satellite-leaning under a heuristic Data Vault assessment. This suggests that OZF_VOLUME_OFFER_TIERS functions primarily as a descriptive satellite whose rows depend on a parent offer record, rather than acting as an independent hub or as a pure associative link. The tier attributes are meaningful only in the context of the offer header they attach to.

Key Information Stored

The table contains eleven documented columns. The surrogate primary key is VOLUME_OFFER_TIERS_ID, enforced by the constraint OZF_VOLUME_OFFER_TIERS_PK. A unique index, OZF_VOLUME_OFFER_TIERS_U1, is also defined on this same column, indicating it is the only documented business-key candidate. The most significant columns include:

• VOLUME_OFFER_TIERS_ID — surrogate primary key uniquely identifying each tier row.
• QP_LIST_HEADER_ID — foreign key linking the tier to its parent offer in OZF_OFFERS; this is the association that gives the tier its business context.
• DISCOUNT_TYPE_CODE — identifies the mechanism by which the tier benefit is applied (for example, a percentage or amount discount).
• DISCOUNT — the numeric value of the benefit granted at this tier.
• BREAK_TYPE_CODE — defines how the tier boundary is interpreted, such as quantity-based or amount-based breaks.
• TIER_VALUE_FROM and TIER_VALUE_TO — the lower and upper bounds of the tier range, establishing the qualifying volume window.
• VOLUME_TYPE — indicates whether the tier is measured by volume or value.
• UOM_CODE — the unit of measure in which tier thresholds are expressed.
• ACTIVE — flags whether the tier is currently in effect.
• OBJECT_VERSION_NUMBER — supports optimistic locking for concurrent updates, a standard attribute in R12 OZF entities.

Common Use Cases and Queries

The principal reporting scenario is reconstructing the full tier ladder for a given offer so that qualification logic and expected accruals can be validated. A typical query joins the tier table back to its parent offer:

SELECT t.volume_offer_tiers_id, t.qp_list_header_id, t.break_type_code, t.tier_value_from, t.tier_value_to, t.discount_type_code, t.discount FROM ozf.ozf_volume_offer_tiers t WHERE t.qp_list_header_id = :p_offer_id AND t.active = 'Y' ORDER BY t.tier_value_from;

Analysts also use this object to audit how many tiers are attached to each volume offer, to confirm that tier ranges do not overlap or leave gaps, and to reconcile the tier definition stored in EBS against downstream accrual results. Because OBJECT_VERSION_NUMBER is present, integration and interface programs that update tiers must respect optimistic locking to avoid lost updates.

Related Objects

The documented foreign key establishes a direct dependency on the offer header, and additional Trade Management objects interact with this table in practice:

• OZF.OZF_OFFERS — parent offer header, joined via QP_LIST_HEADER_ID; the FK relationship is documented.
• OZF.OZF_VOLUME_OFFERS — volume-offer extension records associated with the same offer headers.
• OZF.OZF_OFFER_QUALIFIERS — qualifying criteria that determine which customers or products reach a given tier.
• OZF.OZF_OFFER_ACCRUALS / accrual tables — where tier-driven benefits are realized as accruals.
• QP pricing list APIs and concurrent programs that validate and publish volume offers.

Together these objects form the volume-offer framework, with OZF_VOLUME_OFFER_TIERS supplying the graduated benefit structure.