Search Results ozf_denorm_queries_u1

Overview

OZF.OZF_DENORM_QUERIES is a transactional configuration table in the Oracle E-Business Suite Advanced Pricing (OZF) schema. It stores the SQL query text used to resolve Market Eligibilities and Product Eligibilities, which are the denormalized eligibility predicates consumed by Oracle Advanced Pricing when qualifying modifiers, price lists, and qualifiers against markets or products. The table is populated with seeded queries for every seeded Context and Attribute combination defined in the Advanced Pricing flexfield setup, and it also supports customer-defined queries.

The queries stored here are parameterized by bind column names rather than by hard-coded values. Market Eligibility queries must return party_id, while Product Eligibility queries must return inventory_item_id. This convention allows the pricing engine to substitute runtime context values into the query at evaluation time.

From a heuristic Data Vault modeling perspective, the mined FK structure classifies this object as standalone. It has no dependent child tables in the documented relationship data and therefore behaves as a reference or configuration object rather than a hub, link, or satellite in a strict Data Vault sense. The single outbound foreign key to FND_SECURITY_GROUPS (SECURITY_GROUP_ID) is a multi-org security reference rather than a true business relationship.

Key Information Stored

The surrogate primary key is DENORM_QUERY_ID, enforced by the unique index OZF_DENORM_QUERIES_U1. In the 12.2.2 schema this unique index is composite, covering DENORM_QUERY_ID and ZD_EDITION_NAME, reflecting the editioning infrastructure introduced for Online Patching. ZD_EDITION_NAME is therefore a business-key candidate component alongside the surrogate key, and any query that is intended to be edition-safe should filter on it.

• DENORM_QUERY_ID — unique identifier and primary key for each stored query.
• QUERY_FOR — discriminator indicating whether the row resolves Market Eligibility or Product Eligibility.
• CONTEXT — the Advanced Pricing flexfield context to which the query applies.
• ATTRIBUTE — the specific context attribute qualified by the query; CONTEXT and ATTRIBUTE are indexed together in OZF_DENORM_QUERIES_N1.
• CONDITION_ID_COLUMN — bind column name used for equality or inequality (= or <>) comparison operators.
• CONDITION_NAME_COLUMN — bind column name used for BETWEEN comparisons.
• SQL_VALIDATION_1 through SQL_VALIDATION_8 — the eight SQL fragments (each up to 4000 characters) that comprise the denormalized eligibility query.
• ACTIVE_FLAG — controls whether the stored query is currently evaluated by the pricing engine.
• SEEDED_FLAG — distinguishes Oracle-seeded queries from customer-defined ones, protecting seed data during upgrades.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF-based maintenance pages.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS for multi-org security enforcement.
• Standard WHO columns: CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

The table resides in tablespace APPS_TS_TX_DATA with PCTFREE 10; both indexes reside in APPS_TS_TX_IDX.

Common Use Cases and Queries

Typical usage centers on diagnostics of pricing qualification failures, review of seeded versus customized eligibility logic, and auditing of the SQL fragments that drive market and product qualification.

To list all active Market Eligibility queries for a given context:

SELECT denorm_query_id, context, attribute,
       condition_id_column, condition_name_column
  FROM ozf.ozf_denorm_queries
 WHERE query_for = 'Market Eligibility'
   AND context   = :context
   AND active_flag = 'Y'
   AND zd_edition_name = :edition_name;

To distinguish seeded from custom logic during an upgrade impact assessment:

SELECT query_for, context, attribute, seeded_flag,
       last_update_date, last_updated_by
  FROM ozf.ozf_denorm_queries
 WHERE seeded_flag = 'N'
 ORDER BY last_update_date DESC;

To retrieve the decomposed query text for review, concatenate the SQL_VALIDATION_1..8 columns in order, filtering on the surrogate key or on the CONTEXT/ATTRIBUTE business key via index N1. Reporting queries should always join to FND_SECURITY_GROUPS on SECURITY_GROUP_ID when org-level filtering is required, and should filter on ZD_EDITION_NAME to avoid stale edition rows.

Related Objects

• FND_SECURITY_GROUPS — referenced through OZF_DENORM_QUERIES.SECURITY_GROUP_ID; drives multi-org security on query rows.
• FND_FLEX_VALUES / Advanced Pricing flexfield context definitions — supply the CONTEXT and ATTRIBUTE values qualified by each query.
• OZF_ELIGIBILITY_... and price-list qualification tables in the OZF schema — consume the resolved party_id and inventory_item_id results produced by these queries at pricing time.
• MTL_SYSTEM_ITEMS_B — the source of inventory_item_id values returned by Product Eligibility queries.
• HZ_PARTIES — the source of party_id values returned by Market Eligibility queries.
• FND_FLEX_VALIDATION_... and the Advanced Pricing setup UI (OZF_...) — maintain and validate the seeded Context/Attribute combinations that this table mirrors.