Search Results cz_effectivity_sets_pk

Overview

CZ_EFFECTIVITY_SETS is a configuration management table owned by the CZ (Configurator) schema in Oracle E-Business Suite, present and valid in both release 12.1.1 and 12.2.2. The table stores named, shared effectivity sets that define the date ranges governing when a configurator rule, model node, or rule folder is considered active. Rather than embedding effectivity dates redundantly in every governed record, Oracle Configurator centralizes each reusable effectivity definition in CZ_EFFECTIVITY_SETS and lets other tables reference it through the EFFECTIVITY_SET_ID foreign key.

The primary key, CZ_EFFECTIVITY_SETS_PK, is defined on EFFECTIVITY_SET_ID, a system-generated surrogate identifier. In Data Vault terms, the mined relationship structure classifies this table as hub-leaning: it is a durable, reference-style entity identified by a single surrogate key, with multiple dependent tables pointing to it. From a modeling standpoint, CZ_EFFECTIVITY_SETS behaves as a hub whose child references (CZ_PS_NODES, CZ_RULES, CZ_RULE_FOLDERS, and the import staging tables) act as link or satellite consumers rather than contributors. The table carries 20 documented columns in the 12.2.2 physical schema.

Key Information Stored

The most operationally significant columns include the following:

• EFFECTIVITY_SET_ID — the surrogate primary key and the single join column used by every dependent table.
• NAME — the user-visible label of the named effectivity set. Together with the primary key this forms the practical business-key candidate used in lookups and reports.
• DESCRIPTION and NOTE — free-text attributes describing the purpose and constraints of the effectivity set.
• EFFECTIVE_FROM and EFFECTIVE_UNTIL — the date boundaries defining the active window of the set. These values are propagated to child records that reference the set.
• DELETED_FLAG — logical delete indicator, allowing effectivity sets to be retired without physical removal, preserving referential integrity for existing children.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle EBS audit and concurrency columns.
• USER_STR01–USER_STR04 and USER_NUM01–USER_NUM04 — eight extensibility (DDF-style) columns reserved for customer-specific attributes.

The unique index CZ_EFFECTIVITY_SETS_PK on EFFECTIVITY_SET_ID is the only documented unique constraint, confirming the surrogate key as the sole enforced business-key candidate at the schema level.

Common Use Cases and Queries

Typical uses include determining which rules or model nodes are currently effective, auditing effectivity changes, and reporting on configuration coverage windows.

• Identify all currently active effectivity sets:
  SELECT EFFECTIVITY_SET_ID, NAME, EFFECTIVE_FROM, EFFECTIVE_UNTIL FROM CZ.CZ_EFFECTIVITY_SETS WHERE DELETED_FLAG = 'N' AND SYSDATE BETWEEN EFFECTIVE_FROM AND EFFECTIVE_UNTIL;
• Find rules sharing an effectivity set (join via the documented FK):
  SELECT r.RULE_ID, e.NAME FROM CZ.CZ_RULES r JOIN CZ.CZ_EFFECTIVITY_SETS e ON r.EFFECTIVITY_SET_ID = e.EFFECTIVITY_SET_ID;
• Determine which model nodes are governed by a named set for validation or troubleshooting.
• Audit recently modified effectivity definitions using LAST_UPDATE_DATE and LAST_UPDATED_BY.
• Map customer-defined attributes held in USER_STR01–04 and USER_NUM01–04 for localization or reporting.

Related Objects

The documented foreign-key relationships show the following significant dependents, all of which join on EFFECTIVITY_SET_ID:

• CZ_PS_NODES.EFFECTIVITY_SET_ID — model structure nodes governed by the referenced effectivity set.
• CZ_RULES.EFFECTIVITY_SET_ID — configurator rules whose activation window is inherited.
• CZ_RULE_FOLDERS.EFFECTIVITY_SET_ID — folders grouping rules under shared effectivity.
• CZ_IMP_RULES.EFFECTIVITY_SET_ID — import staging table for rules.
• CZ_IMP_PS_NODES.EFFECTIVITY_SET_ID — import staging table for model nodes.

Because all references flow outward from this hub, CZ_EFFECTIVITY_SETS is a foundational reference table within the Configurator data model and should be treated as master data during any data migration or integration exercise.