Results for “ozf_offers”

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

Overview

OZF_OFFERS is the core header table for Oracle Trade Management offers in Oracle E-Business Suite 12.1.1 and 12.2.2. It resides in the OZF schema, which is owned by the Trade Management module, and functions as a surrogate extension to QP_LIST_HEADERS_B, the Oracle Advanced Pricing list header table. Where QP_LIST_HEADERS_B stores the generic pricing list definition shared across price lists, modifier lists, and qualification lists, OZF_OFFERS captures the attributes unique to trade promotions and offers: lifecycle status, performance (accrual/earning) dates, lumpsum offer types, budget linkage, volume tier behavior, and autopay configuration. Every offer row in OZF_OFFERS is anchored to a row in QP_LIST_HEADERS_B through the QP_LIST_HEADER_ID foreign key, so the two tables are read together in virtually every functional query.

From a Data Vault modeling perspective, the FK structure suggests classifying OZF_OFFERS as a hub. It carries its own surrogate key (OFFER_ID), two strong business keys (OFFER_CODE and QP_LIST_HEADER_ID), and is referenced by a large family of satellite-style detail and forecasting tables, which is consistent with hub behavior.

Key Information Stored

The documented physical schema contains 63 columns. The most operationally significant are:

• OFFER_ID — surrogate primary key (OZF_OFFERS_PK) and the internal identifier used by all child tables.
• OFFER_CODE — user-visible unique business key (OZF_OFFERS_U1), typically the offer number presented to end users.
• QP_LIST_HEADER_ID — foreign key to QP_LIST_HEADERS_B and a second unique business key (OZF_OFFERS_U2), enforcing a 1:1 relationship between an offer and its pricing list header.
• OFFER_TYPE — discriminates the kind of offer (for example, discount, lumpsum, or volume-based).
• PERF_DATE_FROM / PERF_DATE_TO — the performance window during which the offer accrues.
• STATUS_CODE / STATUS_DATE / USER_STATUS_ID — the offer lifecycle state and its effective date, with USER_STATUS_ID resolving to AMS_USER_STATUSES_B.
• OWNER_ID — the resource (sales representative or team) that owns the offer, joining to JTF_RS_RESOURCE_EXTNS.
• LUMPSUM_AMOUNT / LUMPSUM_PAYMENT_TYPE — the value and mechanism for lumpsum offers.
• OFFER_AMOUNT / PERCENT_DISCOUNT / ORDER_VALUE_DISCOUNT_TYPE / MODIFIER_LEVEL_CODE — the commercial terms of the offer.
• BUDGET_SOURCE_ID / BUDGET_SOURCE_TYPE / BUDGET_AMOUNT_FC / BUDGET_AMOUNT_TC — funding linkage to OZF_FUNDS_ALL_B and budget amounts in functional and transaction currency.
• ORG_ID / SECURITY_GROUP_ID — Multi-Org and security-group partitioning columns.
• CONFIDENTIAL_FLAG, REUSABLE, RETROACTIVE, AUTOPAY_FLAG — behavioral flags controlling offer eligibility and payment.

Common Use Cases and Queries

Typical scenarios include offer status reporting, performance-period (accrual) reporting, budget consumption tracking, and revaluation of open accruals. A canonical join pulls the offer header together with its pricing list header:

SELECT o.offer_code, o.offer_type, o.status_code, o.perf_date_from, o.perf_date_to, qlh.name
FROM ozf_offers o, qp_list_headers_b qlh
WHERE o.qp_list_header_id = qlh.list_header_id
AND o.org_id = :p_org_id;

A second pattern enumerates offers in a given status window or by owner, using USER_STATUS_ID or OWNER_ID. A third pattern aggregates accruals by joining OZF_OFFER_PERFORMANCES on USED_BY_ID and OZF_OFFER_QUALIFIERS on OFFER_ID. Because child tables are keyed by OFFER_ID, always drive child queries from the parent key. Indexes U1 (OFFER_ID), U2 (QP_LIST_HEADER_ID), and U3 (OFFER_CODE) support the most common access paths.

Related Objects

The most frequently referenced objects in the OZF_OFFERS ecosystem are:

• QP_LIST_HEADERS_B — parent pricing list header; join on QP_LIST_HEADER_ID.
• OZF_OFFER_QUALIFIERS — offer qualification rules; join on OFFER_ID.
• OZF_OFFER_DISCOUNT_LINES / OZF_OFFER_DISCOUNT_PRODUCTS — product-level discount detail; join on OFFER_ID.
• OZF_OFFER_PERFORMANCES — accrual and performance records; join on USED_BY_ID.
• OZF_OFFER_ADJUSTMENTS_B — lumpsum adjustment headers; join on LIST_HEADER_ID.
• OZF_VOLUME_OFFER_TIERS — tier break definitions; join on QP_LIST_HEADER_ID.
• OZF_PRICE_LIST_ATTRIBUTES — pricing attribute assignments; join on QP_LIST_HEADER_ID.
• OZF_ACTIVITY_CUSTOMERS / OZF_ACTIVITY_PRODUCTS — activity targeting; join on OBJECT_ID.
• AMS_USER_STATUSES_B — status lookup; join on USER_STATUS_ID.
• JTF_RS_RESOURCE_EXTNS — offer owner; join on OWNER_ID.