Results for “ancestor_code”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AS_HZ_CLASS_CODE_DENORM is a denormalized table owned by the OSM schema within the AS (Sales Foundation) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its stated purpose is to store customer classification code relations data that has been flattened and pre-joined from a set of normalized source objects: HZ_CLASS_CATEGORIES, HZ_CLASS_CATEGORY_USES, HZ_CLASS_CODE_RELATIONS, FND_LOOKUP_TYPES_TL, and FND_LOOKUP_VALUES. Rather than repeatedly resolving classification hierarchies, meanings, and lookup translations at query time, this table materializes those relationships into a single wide structure.

The table is documented with a heuristic Data Vault classification of standalone, mined from its foreign key structure. This suggests that AS_HZ_CLASS_CODE_DENORM is best modeled as an independent, denormalized reporting object rather than as a hub, link, or satellite within a formal Data Vault design. Its only documented foreign key is toward FND_SECURITY_GROUPS, reinforcing its role as a self-contained classification reference rather than a transactional entity participating in a rich relational web.

Because it aggregates translated meanings, hierarchical ancestor codes, and descriptive attributes across multiple HZ and FND sources, the table is primarily a read-optimized companion to the HZ customer classification model used within Sales Foundation processing.

Key Information Stored

The table carries 46 documented columns. The most significant are the business classification identifiers and their translated descriptors, which together form the natural uniqueness of a row.

• CLASS_CATEGORY and CLASS_CATEGORY_MEANING — the classification category and its translated display meaning.
• CLASS_CODE, CLASS_CODE_MEANING, and CLASS_CODE_DESCRIPTION — the classification code value plus its translated meaning and description.
• ANCESTOR_CODE and ANCESTOR_MEANING — the parent/ancestor entry in the classification hierarchy.
• CONCAT_CLASS_CODE and CONCAT_CLASS_CODE_MEANING — a concatenated hierarchical code path and its meaning.
• LANGUAGE — the language context of the denormalized translations.
• CODE_LEVEL — the depth of the classification code within its hierarchy.
• START_DATE_ACTIVE, END_DATE_ACTIVE, ENABLED_FLAG, SELECTABLE_FLAG — effective-dating and usability controls.
• SEGMENT1 through SEGMENT10 and their SEGMENTn_MEANING counterparts — flexible segment values and translations.
• Standard audit and concurrency columns including CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, OBJECT_VERSION_NUMBER, and the program/request columns (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE).

The documented business-key candidate is the unique index AS_HZ_CLASS_CODE_DENORM_U1, defined over CLASS_CATEGORY, CLASS_CODE, ANCESTOR_CODE, LANGUAGE. No separate surrogate primary key column is enumerated in the metadata; the uniqueness of a row is thus governed by that composite business key. The table also carries SECURITY_GROUP_ID, which is a foreign key to FND_SECURITY_GROUPS.

Common Use Cases and Queries

The principal scenario is customer classification reporting, where callers need flattened classification hierarchies with meanings resolved without traversing the HZ_FND lookup chain.

• Retrieving enabled, selectable codes for a given category and language:
  SELECT CLASS_CODE, CLASS_CODE_MEANING FROM AS_HZ_CLASS_CODE_DENORM WHERE CLASS_CATEGORY = :cat AND LANGUAGE = USERENV('LANG') AND ENABLED_FLAG = 'Y' AND SELECTABLE_FLAG = 'Y';
• Reconstructing an indented hierarchy using CODE_LEVEL and ANCESTOR_CODE.
• Joining to assignment or party records by CLASS_CODE and CLASS_CATEGORY to enrich reporting with descriptions rather than raw codes.

Because meanings and descriptions are pre-translated, reports avoid joins to FND_LOOKUP_VALUES_TL and FND_LOOKUP_TYPES_TL, improving performance for high-volume classification lookups.

Related Objects

The following objects are most significant to this table, based on documented metadata and its denormalization sources:

• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID on AS_HZ_CLASS_CODE_DENORM.
• HZ_CLASS_CATEGORIES — source of classification categories.
• HZ_CLASS_CATEGORY_USES — source of category usage relationships.
• HZ_CLASS_CODE_RELATIONS — source of the hierarchical code relations.
• FND_LOOKUP_TYPES_TL — source of translated lookup type definitions.
• FND_LOOKUP_VALUES — source of lookup values and their meanings.

Together these objects define the normalized model that AS_HZ_CLASS_CODE_DENORM consolidates for Sales Foundation classification reporting.