Search Results hz_class_code_denorm_u1

Overview

The AR.HZ_CLASS_CODE_DENORM table is a denormalized classification hierarchy repository within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 data model. It stores the flattened, pre-joined representation of classification code hierarchies that are otherwise distributed across HZ_CLASS_CATEGORIES, HZ_CLASS_CATEGORY_USES, HZ_CLASS_CODE_RELATIONS, FND_LOOKUP_TYPES_TL, and FND_LOOKUP_VALUES. By materializing the hierarchy in a single structure, the table provides high-performance search and retrieval of classification paths such as "High Tech/Hardware/Hard Drives" without repeatedly traversing normalized parent-child relationships.

In the TCA (Trading Community Architecture) model, classification codes describe customers, organizations, and other entities across configurable categories. This denormalized table is populated by concurrent programs and is intended primarily as a read-optimized artifact rather than a transactional base table.

From a Data Vault modeling perspective (heuristic classification), this table is best treated as standalone. Because it is a derived, denormalized snapshot rather than a source of independent business events or relationships, it does not map cleanly to a hub, link, or satellite. It is functionally closer to a reference/derived data set and should be modeled as a reference or computed entity within an analytical layer.

Key Information Stored

The table contains 22 documented columns. The most significant include:

• CLASS_CATEGORY (VARCHAR2, 30) — The classification category identifier, derived from HZ_CLASS_CATEGORIES.
• CLASS_CODE (VARCHAR2, 30) — The individual classification code value.
• CLASS_CODE_MEANING (VARCHAR2, 80) — The translated meaning of the classification code.
• CLASS_CODE_DESCRIPTION (VARCHAR2, 240) — A fuller description of the code.
• LANGUAGE (VARCHAR2, 30) — The language of the meaning and description, enabling multilingual reporting.
• CONCAT_CLASS_CODE (VARCHAR2, 500) — The concatenated, path-style fully qualified classification code.
• CONCAT_CLASS_CODE_MEANING (VARCHAR2, 2000) — The concatenated, human-readable path of the classification hierarchy.
• CODE_LEVEL (NUMBER) — The depth of the code within its hierarchy.
• START_DATE_ACTIVE / END_DATE_ACTIVE (DATE) — The effective date range, derived from FND_LOOKUP_VALUES.
• ENABLED_FLAG and SELECTABLE_FLAG — Status indicators controlling whether the code is active and selectable in the UI.
• SECURITY_GROUP_ID — Reference to the owning security group, with an FK to FND_SECURITY_GROUPS.

The documented primary key is HZ_CLASS_CODE_DENORM_PK (CLASS_CATEGORY, CLASS_CODE, LANGUAGE). The unique index HZ_CLASS_CODE_DENORM_U1 — the object the user searched for — spans CLASS_CATEGORY, CLASS_CODE, LANGUAGE, and CONCAT_CLASS_CODE, and is the strongest business-key candidate. A nonunique index, HZ_CLASS_CODE_DENORM_N1, supports query filtering on CLASS_CATEGORY, LANGUAGE, SELECTABLE_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, and ENABLED_FLAG. A domain index HZ_CLASS_CODE_DENORM_T1 supports text search on the CONCAT_CLASS_CODE_MEANING column. All indexes reside in APPS_TS_SUMMARY, aligning with the table's summary/read-optimized role.

Common Use Cases and Queries

Typical uses include classification pickers, hierarchy path display, and reporting on classified customers. A representative query retrieves all selectable codes in a category for a given language:

• SELECT class_code, class_code_meaning, concat_class_code_meaning, code_level FROM hz_class_code_denorm WHERE class_category = :p_category AND language = USERENV('LANG') AND selectable_flag = 'Y' AND enabled_flag = 'Y';
• Full-path lookup by depth, e.g., all level-3 codes under a parent path via CONCAT_CLASS_CODE.
• Date-effective filtering using START_DATE_ACTIVE and END_DATE_ACTIVE to reconstruct the classification state at a point in time.
• Text search against CONCAT_CLASS_CODE_MEANING leveraging the domain index HZ_CLASS_CODE_DENORM_T1.

Related Objects

The denormalized table is derived from and related to the following core objects; joins are typically on classification category, code, and language identifiers.

• HZ_CLASS_CATEGORIES — Source of CLASS_CATEGORY definitions.
• HZ_CLASS_CATEGORY_USES — Controls how categories are applied to entity types.
• HZ_CLASS_CODE_RELATIONS — Provides the parent-child hierarchy driving CONCAT_CLASS_CODE and CODE_LEVEL.
• FND_LOOKUP_TYPES_TL and FND_LOOKUP_VALUES — Supply code meanings, descriptions, and effective dates.
• FND_SECURITY_GROUPS — Referenced via SECURITY_GROUP_ID (documented FK).
• HZ_CUST_ACCOUNTS and related TCA entity tables — Consume classification results through category/code joins.

Because population is batch-driven, refresh scheduling and index maintenance on HZ_CLASS_CODE_DENORM_U1 and HZ_CLASS_CODE_DENORM_N1 are essential to sustaining query performance in high-volume EBS environments.