Search Results ozf_activity_customers




Overview
Explore in AI Chat

Overview

OZF_ACTIVITY_CUSTOMERS is a denormalized table in the Oracle Trade Management (OZF) module. Its documented purpose is to store Offer and Schedule Market Eligibility information — the qualified, flattened set of trading partners and customers eligible to participate in a specific trade promotion offer or promotion schedule. Rather than resolving eligibility dynamically at runtime through complex qualification joins across HZ_PARTIES, HZ_CUST_ACCOUNTS, and AMS_CUSTOM_SETUPS_B, Oracle EBS precomputes and persists the eligible audience for each activity in this table, dramatically improving query and reporting performance for large promotions.

The table belongs to the OZF schema and is valid in both Oracle EBS 12.1.1 and 12.2.2. It contains 50 documented columns in the ETRM 12.2.2 physical schema. Its primary key is OZF_ACTIVITY_CUSTOMERS_PK, defined on the ACTIVITY_CUSTOMER_ID surrogate column.

From a Data Vault modeling perspective, the metadata's heuristic classification identifies this object as a link. This reflects its structural role: it connects an offer (via OBJECT_ID to OZF_OFFERS) to a party/customer (via HZ_PARTIES_PARTY_ID, CUST_ACCOUNT_ID, CUST_ACCT_SITE_ID), and it carries descriptive performance attributes. In practice it behaves as a link-plus-satellite hybrid because it also stores measures such as actual and forecast revenue, costs, ROI, and units.

Key Information Stored

The most significant columns fall into three functional groups.

Common Use Cases and Queries

This table is primarily consumed for promotion eligibility reporting, customer targeting extracts, and trade-spend analysis. A typical query joins back to OZF_OFFERS and HZ_PARTIES to produce a promotion participant list.

  • Returning all eligible customers for a given offer:
SELECT ac.ACTIVITY_CUSTOMER_ID,
       ac.OBJECT_ID,
       ac.HZ_PARTIES_PARTY_ID,
       ac.CUST_ACCOUNT_ID,
       ac.ACTUAL_REVENUE,
       ac.FORECAST_ROI
FROM   OZF.OZF_ACTIVITY_CUSTOMERS ac
WHERE  ac.OBJECT_ID = :p_offer_id
AND    NVL(ac.ACTIVE_FLAG,'Y') = 'Y'
AND    TRUNC(SYSDATE) BETWEEN ac.START_DATE AND ac.END_DATE;
  • Aggregating actual versus forecast ROI by offer for dashboards.
  • Extracting the qualified customer set for outbound campaign execution.
  • Feeding price list or modifier generation by joining QP_QUALIFIER_ID to pricing qualifier tables.

Because the table is denormalized, filters on OBJECT_ID and START_DATE/END_DATE should be indexed or the query should be constrained by date to avoid full scans on large promotions.

Related Objects

  • OZF_OFFERS — parent offer/schedule; joined via OBJECT_ID = OZF_OFFERS.OBJECT_ID.
  • HZ_PARTIES — trading partner; joined via HZ_PARTIES_PARTY_ID = HZ_PARTIES.PARTY_ID.
  • HZ_CUST_ACCOUNTS — customer account; joined via CUST_ACCOUNT_ID.
  • HZ_CUST_ACCT_SITES_ALL — customer account site; joined via CUST_ACCT_SITE_ID.
  • AMS_CUSTOM_SETUPS_B — custom setup definition; joined via CUSTOM_SETUP_ID.
  • QP_QUALIFIER tables (Advanced Pricing) — via QP_QUALIFIER_ID and QP_QUALIFIER_GROUP for qualifier resolution.

These relationships confirm the table's position as the bridge between Oracle's Trade Management offer model and the Trading Community Architecture (TCA) customer and party model.