Search Results ozf_activity_products

Overview

OZF_ACTIVITY_PRODUCTS is a denormalized table within the Oracle Trade Management (OZF) module of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. Its documented purpose is to store product eligibility information for offers and schedules. In practical terms, the table records which inventory items or product categories qualify for a given promotional offer or trade activity, along with the financial performance attributed to those products.

Because the table consolidates attributes from offers, offer schedules, product master data, and performance metrics into a single wide structure, it functions as a pre-joined reporting object rather than a normalized transactional entity. The metadata carries fifty-three documented columns, which is consistent with a denormalized design intended to reduce join overhead in trade promotion analysis.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure is satellite-leaning. This suggests the table is best understood as a descriptive satellite attached to an offer or activity hub, carrying measurable attributes (units, revenue, costs, ROI) and descriptive context (product, category, dates) rather than acting as an independent hub or a pure relationship link.

Key Information Stored

The surrogate primary key is ACTIVITY_PRODUCT_ID, enforced by the OZF_ACTIVITY_PRODUCTS_PK constraint. This column uniquely identifies each row and is the only documented unique identifier for the table; no separate business-key unique index is documented in the ETRM metadata.

The most significant columns fall into three groups:

Relationship keys: OBJECT_ID references OZF_OFFERS, tying the row to a specific offer. ACTIVITY_ID and ACTIVITY_TYPE_CODE identify the parent activity, while CUSTOM_SETUP_ID references AMS_CUSTOM_SETUPS_B and supports configurable attribute capture.
Product identification: ITEM, ITEM_TYPE, and ITEMS_CATEGORY describe the eligible product or category. PARENT_ID, PARENT_DESC, and PARENT_CLASS provide hierarchy context for the parent product grouping.
Performance and eligibility metrics: ACTUAL_UNITS, ACTUAL_REVENUE, ACTUAL_COSTS, ACTUAL_ROI and their FORECAST_ counterparts capture offer performance. The product-level variants (ACTUAL_PRODUCT_UNITS, ACTUAL_PRODUCT_REVENUE, FORECAST_PRODUCT_UNITS, and so on) isolate the contribution of the specific item. LIST_PRICE, DISCOUNT, CURRENCY_CODE, ACTUAL_UOM, and FORECAST_UOM support pricing and unit-of-measure analysis, while START_DATE, END_DATE, and ACTIVE_FLAG define validity windows and current status.
Audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN follow standard EBS audit conventions and are essential for incremental extraction.

Common Use Cases and Queries

The most frequent use of this table is trade promotion effectiveness reporting. Analysts join it to OZF_OFFERS to segment offer performance by product or category, and compare forecast against actual ROI to evaluate promotion lift.

Eligible product listing for an offer: Query by OBJECT_ID with an ACTIVE_FLAG and date filter to return all items qualifying for a promotion.
Product-level ROI analysis: Aggregate ACTUAL_PRODUCT_REVENUE against ACTUAL_PRODUCT_COSTS grouped by ITEMS_CATEGORY to rank categories by return.
Forecast accuracy: Compare FORECAST_PRODUCT_UNITS with ACTUAL_PRODUCT_UNITS to measure planning variance.
Incremental extraction: Use LAST_UPDATE_DATE in the WHERE clause for ETL watermarking, given the high row volume typical of denormalized trade data.

A representative pattern joins the table to OZF_OFFERS on OBJECT_ID = OFFER_ID and filters on ACTIVE_FLAG = 'Y' to return currently effective product eligibility records.

Related Objects

OZF_OFFERS — referenced via OZF_ACTIVITY_PRODUCTS.OBJECT_ID; the primary parent for offer-based product eligibility.
AMS_CUSTOM_SETUPS_B — referenced via CUSTOM_SETUP_ID; supplies configurable attribute definitions.
OZF_ACTIVITY_PRODUCTS_PK — the primary key constraint on ACTIVITY_PRODUCT_ID.
Offer and schedule activity tables (OZF_ACTIVITY_*) — share the ACTIVITY_ID and ACTIVITY_TYPE_CODE dimensions for activity-level reporting.
MTL_SYSTEM_ITEMS_B — the logical source of ITEM master data for product joins.

Table: OZF_ACTIVITY_PRODUCTS 12.1.1
owner:OZF, object_type:TABLE, fnd_design_data:OZF.OZF_ACTIVITY_PRODUCTS, object_name:OZF_ACTIVITY_PRODUCTS, status:VALID, product: OZF - Trade Management , description: Denorm table for Offer and Schedules Product Eligibility , implementation_dba_data: OZF.OZF_ACTIVITY_PRODUCTS ,
Table: OZF_ACTIVITY_PRODUCTS 12.2.2
owner:OZF, object_type:TABLE, fnd_design_data:OZF.OZF_ACTIVITY_PRODUCTS, object_name:OZF_ACTIVITY_PRODUCTS, status:VALID, product: OZF - Trade Management , description: Denorm table for Offer and Schedules Product Eligibility , implementation_dba_data: OZF.OZF_ACTIVITY_PRODUCTS ,
SYNONYM: APPS.OZF_ACTIVITY_PRODUCTS 12.1.1
owner:APPS, object_type:SYNONYM, object_name:OZF_ACTIVITY_PRODUCTS, status:VALID,
PACKAGE BODY: APPS.OZF_ACTIVITY_DENORM_PVT 12.1.1
SYNONYM: APPS.OZF_ACTIVITY_PRODUCTS 12.2.2
owner:APPS, object_type:SYNONYM, object_name:OZF_ACTIVITY_PRODUCTS, status:VALID,
PACKAGE BODY: APPS.OZF_ACTIVITY_DENORM_PVT 12.2.2
VIEW: OZF.OZF_ACTIVITY_PRODUCTS# 12.2.2
owner:OZF, object_type:VIEW, object_name:OZF_ACTIVITY_PRODUCTS#, status:VALID,
VIEW: OZF.OZF_ACTIVITY_PRODUCTS# 12.2.2
APPS.OZF_SCHEDULE_DENORM_PVT SQL Statements 12.1.1
TABLE: OZF.OZF_ACTIVITY_PRODUCTS 12.1.1
owner:OZF, object_type:TABLE, fnd_design_data:OZF.OZF_ACTIVITY_PRODUCTS, object_name:OZF_ACTIVITY_PRODUCTS, status:VALID,
APPS.OZF_SCHEDULE_DENORM_PVT SQL Statements 12.2.2
APPS.OZF_OFFR_ELIG_PROD_DENORM_PVT SQL Statements 12.2.2
PACKAGE BODY: APPS.DPP_PRICING_PVT 12.1.1
owner:APPS, object_type:PACKAGE BODY, object_name:DPP_PRICING_PVT, status:VALID,
TABLE: OZF.OZF_ACTIVITY_PRODUCTS 12.2.2
owner:OZF, object_type:TABLE, fnd_design_data:OZF.OZF_ACTIVITY_PRODUCTS, object_name:OZF_ACTIVITY_PRODUCTS, status:VALID,
APPS.OZF_OFFR_ELIG_PROD_DENORM_PVT SQL Statements 12.1.1
PACKAGE BODY: APPS.OZF_SCHEDULE_DENORM_PVT 12.2.2
owner:APPS, object_type:PACKAGE BODY, object_name:OZF_SCHEDULE_DENORM_PVT, status:VALID,
PACKAGE BODY: APPS.OZF_OFFR_ELIG_PROD_DENORM_PVT 12.1.1
owner:APPS, object_type:PACKAGE BODY, object_name:OZF_OFFR_ELIG_PROD_DENORM_PVT, status:VALID,
PACKAGE BODY: APPS.DPP_PRICING_PVT 12.2.2
owner:APPS, object_type:PACKAGE BODY, object_name:DPP_PRICING_PVT, status:VALID,
PACKAGE BODY: APPS.OZF_SCHEDULE_DENORM_PVT 12.1.1
owner:APPS, object_type:PACKAGE BODY, object_name:OZF_SCHEDULE_DENORM_PVT, status:VALID,
PACKAGE BODY: APPS.OZF_NET_ACCRUAL_ENGINE_PVT 12.1.1
owner:APPS, object_type:PACKAGE BODY, object_name:OZF_NET_ACCRUAL_ENGINE_PVT, status:VALID,
Table: OZF_OFFERS 12.1.1
owner:OZF, object_type:TABLE, fnd_design_data:OZF.OZF_OFFERS, object_name:OZF_OFFERS, status:VALID, product: OZF - Trade Management , description: This is a surrogate table to QP_LIST_HEADERS_B for Offers related information. QP_LIST_HEADER_ID is the foreign key to QP_LIST_HEADERS_B. This table will capture status, peformance dates and lumpsum related offer types. , implementation_dba_data: OZF.OZF_OFFERS ,
12.1.1 DBA Data 12.1.1
12.1.1 DBA Data 12.1.1
Table: OZF_OFFERS 12.2.2
owner:OZF, object_type:TABLE, fnd_design_data:OZF.OZF_OFFERS, object_name:OZF_OFFERS, status:VALID, product: OZF - Trade Management , description: This is a surrogate table to QP_LIST_HEADERS_B for Offers related information. QP_LIST_HEADER_ID is the foreign key to QP_LIST_HEADERS_B. This table will capture status, peformance dates and lumpsum related offer types. , implementation_dba_data: OZF.OZF_OFFERS ,
PACKAGE BODY: APPS.OZF_NET_ACCRUAL_ENGINE_PVT 12.2.2
owner:APPS, object_type:PACKAGE BODY, object_name:OZF_NET_ACCRUAL_ENGINE_PVT, status:VALID,
PACKAGE BODY: APPS.OZF_OFFR_ELIG_PROD_DENORM_PVT 12.2.2
owner:APPS, object_type:PACKAGE BODY, object_name:OZF_OFFR_ELIG_PROD_DENORM_PVT, status:VALID,
PACKAGE BODY: APPS.OZF_SCHEDULE_DENORM_PVT 12.1.1
PACKAGE BODY: APPS.OZF_SCHEDULE_DENORM_PVT 12.2.2
PACKAGE BODY: APPS.OZF_ADJUSTMENT_EXT_PVT 12.2.2
owner:APPS, object_type:PACKAGE BODY, object_name:OZF_ADJUSTMENT_EXT_PVT, status:VALID,
12.2.2 FND Design Data 12.2.2
12.2.2 DBA Data 12.2.2
PACKAGE BODY: APPS.OZF_ADJUSTMENT_EXT_PVT 12.1.1
owner:APPS, object_type:PACKAGE BODY, object_name:OZF_ADJUSTMENT_EXT_PVT, status:VALID,
12.1.1 FND Design Data 12.1.1
12.2.2 DBA Data 12.2.2
12.2.2 DBA Data 12.2.2
12.2.2 DBA Data 12.2.2
12.1.1 DBA Data 12.1.1
12.2.2 DBA Data 12.2.2
PACKAGE BODY: APPS.OZF_OFFR_ELIG_PROD_DENORM_PVT 12.2.2
PACKAGE BODY: APPS.OZF_OFFR_ELIG_PROD_DENORM_PVT 12.1.1
APPS.DPP_PRICING_PVT SQL Statements 12.1.1
APPS.DPP_PRICING_PVT SQL Statements 12.2.2
APPS.OZF_ADJUSTMENT_EXT_PVT dependencies on OZF_ACTIVITY_PRODUCTS 12.1.1
APPS.DPP_PRICING_PVT dependencies on OZF_ACTIVITY_PRODUCTS 12.1.1
APPS.DPP_PRICING_PVT dependencies on OZF_ACTIVITY_PRODUCTS 12.2.2
APPS.OZF_NET_ACCRUAL_ENGINE_PVT dependencies on OZF_ACTIVITY_PRODUCTS 12.2.2
APPS.OZF_NET_ACCRUAL_ENGINE_PVT dependencies on OZF_ACTIVITY_PRODUCTS 12.1.1
APPS.OZF_ADJUSTMENT_EXT_PVT dependencies on OZF_ACTIVITY_PRODUCTS 12.2.2
APPS.OZF_SCHEDULE_DENORM_PVT dependencies on OZF_ACTIVITY_PRODUCTS 12.1.1
APPS.OZF_OFFR_ELIG_PROD_DENORM_PVT dependencies on OZF_ACTIVITY_PRODUCTS 12.2.2