Search Results ozf_denorm_queries




Overview
Explore in AI Chat

Overview

OZF_DENORM_QUERIES is a Trade Management (OZF) table in the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. It stores the SQL query definitions that drive Market Eligibility and Product Eligibility processing within Oracle Trade Management. These denormalized queries are used to resolve the set of eligible parties (for market eligibility) and eligible inventory items (for product eligibility) by returning the party_id and inventory_item_id respectively. A substantial portion of the rows in this table are seeded by Oracle, with customer extensions layered on top.

From a Data Vault modeling perspective, the metadata classifies this table heuristically as standalone. There are no inbound or outbound foreign keys other than the reference to FND_SECURITY_GROUPS via SECURITY_GROUP_ID, so it does not naturally resolve into a hub or link. It is best treated as a reference or configuration satellite holding query definitions, rather than a transactional or relationship-bearing entity.

Key Information Stored

The table contains 24 documented columns. The most significant are:

  • DENORM_QUERY_ID — the surrogate primary key, defined by the constraint OZF_DENORM_QUERIES_PK. It uniquely identifies each denormalized query definition.
  • QUERY_FOR — indicates the eligibility target, distinguishing Market versus Product eligibility queries. Together with CONTEXT and ATTRIBUTE, it forms the business-key candidate OZF_DENORM_QUERIES_UK1.
  • CONTEXT — the attribute context under which the query applies, used for classification and lookup resolution.
  • ATTRIBUTE — the specific attribute driving the eligibility query. This, with the two preceding columns, comprises the unique business key.
  • CONDITION_ID_COLUMN and CONDITION_NAME_COLUMN — the SELECT column names that supply the identifier and descriptive name of the eligibility result (party_id or inventory_item_id).
  • ACTIVE_FLAG — indicates whether the query definition is currently active and therefore eligible for evaluation.
  • SEEDED_FLAG — distinguishes Oracle-seeded queries from customer-created ones.
  • SQL_VALIDATION_1 through SQL_VALIDATION_8 — eight validation slots used to store SQL fragments or validation expressions associated with the query.
  • OBJECT_VERSION_NUMBER — the optimistic locking column used by the OZF framework during concurrent updates.
  • SECURITY_GROUP_ID — the foreign-key reference to FND_SECURITY_GROUPS supporting multi-tenant security grouping.
  • ZD_EDITION_NAME — the editioning column supporting the EBR (Edition-Based Redefinition) model introduced at 12.2, which participates in the unique index OZF_DENORM_QUERIES_U1.
  • Audit columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical usage centers on inspecting which queries are seeded, active, and targetable, and confirming that the correct SELECT columns are in place.

  • List active market eligibility queries: SELECT DENORM_QUERY_ID, QUERY_FOR, CONTEXT, ATTRIBUTE FROM OZF.OZF_DENORM_QUERIES WHERE ACTIVE_FLAG = 'Y' AND QUERY_FOR = 'MARKET';
  • Identify customer-created queries: SELECT * FROM OZF.OZF_DENORM_QUERIES WHERE SEEDED_FLAG = 'N';
  • Resolve a query by business key: SELECT DENORM_QUERY_ID FROM OZF.OZF_DENORM_QUERIES WHERE QUERY_FOR = :p_for AND CONTEXT = :p_ctx AND ATTRIBUTE = :p_attr;
  • Audit recent modifications for troubleshooting eligibility issues: SELECT DENORM_QUERY_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE FROM OZF.OZF_DENORM_QUERIES WHERE LAST_UPDATE_DATE > :p_since ORDER BY LAST_UPDATE_DATE DESC;

Reporting is most useful for configuration validation, regression testing after patches, and documentation of eligibility logic during implementations.

Related Objects

  • FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID, controlling security grouping on each query row.
  • OZF_DENORM_QUERIES_PK — the primary-key constraint on DENORM_QUERY_ID.
  • OZF_DENORM_QUERIES_UK1 — the unique business key over QUERY_FOR, CONTEXT, and ATTRIBUTE.
  • OZF_DENORM_QUERIES_U1 — the editioning-aware unique index on DENORM_QUERY_ID and ZD_EDITION_NAME.
  • OZF eligibility base and result tables — downstream tables that consume the resolved party_id and inventory_item_id outputs to evaluate market and product eligibility.