Search Results hz_class_code_denorm




Overview
Explore in AI Chat

Overview

HZ_CLASS_CODE_DENORM is a denormalized reference table in the Oracle E-Business Suite Receivables (AR) schema, owned by AR and validated across 12.1.1 and 12.2.2. As its name and description indicate, it stores a flattened representation of the classification code hierarchy used by Oracle's Trading Community Architecture (TCA) and customer master data. Classification codes are the user-defined categories—such as Industry, Customer Segment, or Territory—that are attached to parties, organizations, and contacts. Rather than requiring recursive traversal of parent-child relationships in HZ_CLASS_CODE and its related hierarchy tables, HZ_CLASS_CODE_DENORM presents the hierarchy in a pre-joined, flattened form optimised for query performance and reporting.

From a heuristic Data Vault modeling perspective, the mined foreign-key structure (a single reference to FND_SECURITY_GROUPS) does not establish hub or link semantics, and the metadata classifies the object as standalone. It therefore behaves more like a reference or lookup satellite holding descriptive classification attributes rather than a transactional hub or link. The composite primary key HZ_CLASS_CODE_DENORM_PK (CLASS_CATEGORY, CLASS_CODE, LANGUAGE) confirms that the grain is one row per classification code per category per language, with denormalized descriptive columns carried alongside.

Key Information Stored

The 22-column structure carries both the classification identity and its denormalized descriptive payload. The most operationally significant columns are:

  • CLASS_CATEGORY — the classification category (for example, a code set or group) to which the code belongs; part of the primary key.
  • CLASS_CODE — the classification code value; part of the primary key.
  • LANGUAGE — the language context, enabling multilingual descriptions; part of the primary key.
  • CONCAT_CLASS_CODE — the concatenated (hierarchy-qualified) form of the code, used to represent parent-child paths in a single scalar value. It also participates in the unique index HZ_CLASS_CODE_DENORM_U1 (CLASS_CATEGORY, CLASS_CODE, LANGUAGE, CONCAT_CLASS_CODE).
  • CLASS_CODE_MEANING and CLASS_CODE_DESCRIPTION — the human-readable meaning and longer description of the code.
  • CONCAT_CLASS_CODE_MEANING — the descriptive label for the concatenated code path.
  • CODE_LEVEL — the depth of the code within the hierarchy, a key differentiator for denormalized navigation.
  • START_DATE_ACTIVE and END_DATE_ACTIVE — the effective date range during which the classification is valid.
  • ENABLED_FLAG and SELECTABLE_FLAG — status flags controlling whether the code is active and whether it may be chosen in user interfaces.
  • SECURITY_GROUP_ID — the sole documented foreign key, referencing FND_SECURITY_GROUPS for multi-tenant data isolation.

Standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE) and the concurrent-program columns (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE) round out the structure. The surrogate primary key HZ_CLASS_CODE_DENORM_PK is a composite business key, while HZ_CLASS_CODE_DENORM_U1 provides an alternate unique constraint over the concatenated hierarchy path.

Common Use Cases and Queries

The table is primarily consumed for read-oriented reporting where the full classification hierarchy must be resolved without recursive joins. Typical scenarios include customer segmentation reports, territory or industry analyses, and LOV-style lookups that must respect ENABLED_FLAG and SELECTABLE_FLAG. Effective-dating queries filter on START_DATE_ACTIVE and END_DATE_ACTIVE.

A representative pattern retrieving enabled codes for a category in a given language is:

  • SELECT class_category, class_code, class_code_meaning, concat_class_code, code_level FROM hz_class_code_denorm WHERE class_category = :category AND language = USERENV('LANG') AND enabled_flag = 'Y' AND NVL(end_date_active, SYSDATE) >= SYSDATE ORDER BY concat_class_code;
  • Security-aware access should additionally constrain security_group_id to the caller's group.

Related Objects

The documented relationship set is deliberately narrow. The principal related objects are:

  • FND_SECURITY_GROUPS — referenced via HZ_CLASS_CODE_DENORM.SECURITY_GROUP_ID, enforcing multi-tenant filtering.
  • HZ_CLASS_CODE_DENORM_PK — the composite primary key index (CLASS_CATEGORY, CLASS_CODE, LANGUAGE).
  • HZ_CLASS_CODE_DENORM_U1 — the unique index over CLASS_CATEGORY, CLASS_CODE, LANGUAGE, CONCAT_CLASS_CODE.

The table logically complements the TCA classification model (HZ_CLASS_CODE and its category definitions), which supplies the underlying hierarchy that this object denormalizes. Reporting layers, customer master extracts, and segmentation queries typically join HZ_CLASS_CODE_DENORM to party-related TCA tables on CLASS_CODE and CLASS_CATEGORY to attach classification labels to customer records.