Results for “zx_fc_codes_denorm_b”

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

Overview

ZX_FC_CODES_DENORM_B is a table owned by the ZX schema within Oracle E-Business Tax (E-Business Tax, product code ZX). It stores relations data for fiscal classification code hierarchies in a denormalized form. There is one row for each classification code that is present in a hierarchy, meaning the table flattens the parent-child structure of fiscal classification codes so that each code carries its full ancestry context without requiring recursive traversal at query time. This supports tax determination, reporting, and hierarchy-aware lookups where the line of descent from an ancestor classification to a descendant classification must be resolved efficiently.

From a data modeling perspective, the metadata provides a heuristic Data Vault classification of link. This is a reasonable suggestion: the table records relationships between classification codes and their ancestors (captured through ANCESTOR_ID) rather than serving purely as a descriptive hub record. The denormalization means the link is materialized alongside descriptive attributes, blending link and satellite characteristics in a single physical structure. In the Oracle EBS 12.1.1 and 12.2.2 releases, this table is documented with 49 columns and two unique indexes that act as business-key candidates.

Key Information Stored

The primary key is ZX_FC_CODES_DENORM_B_PK, defined on (CLASSIFICATION_TYPE_CODE, LANGUAGE, CLASSIFICATION_CODE, ANCESTOR_ID). Two unique indexes provide additional business-key candidates: ZX_FC_CODES_DENORM_B_U1 on (CLASSIFICATION_TYPE_CODE, LANGUAGE, CLASSIFICATION_CODE, ANCESTOR_ID, ZD_EDITION_NAME) and ZX_FC_CODES_DENORM_B_U2 on (LANGUAGE, CLASSIFICATION_TYPE_CODE, CONCAT_CLASSIF_CODE, EFFECTIVE_FROM, ENABLED_FLAG, ZD_EDITION_NAME). The ZD_EDITION_NAME column is part of both unique indexes, consistent with the editioning model used in recent EBS releases (notably 12.2.x) for the ZX schema.

• CLASSIFICATION_TYPE_CODE / CLASSIFICATION_TYPE_ID / CLASSIFICATION_TYPE_NAME — identify the fiscal classification type (for example, a specific tax regime classification category).
• CLASSIFICATION_ID / CLASSIFICATION_CODE / CLASSIFICATION_NAME — the specific fiscal classification code participating in the hierarchy. CLASSIFICATION_ID references ZX_FC_CODES_B.
• ANCESTOR_ID / ANCESTOR_CODE / ANCESTOR_NAME — the ancestor classification linked to the current code, forming the denormalized parent reference.
• CONCAT_CLASSIF_CODE / CONCAT_CLASSIF_NAME — concatenated representation of the classification hierarchy path, used for reporting and display.
• CLASSIFICATION_CODE_LEVEL — the depth of the code within the hierarchy.
• EFFECTIVE_FROM / EFFECTIVE_TO / ENABLED_FLAG — date-effective and enabled status control for the relationship.
• LANGUAGE — the language of the descriptive attributes, referencing FND_LANGUAGES.
• COUNTRY_CODE — country context, referencing FND_TERRITORIES.
• SEGMENT1–SEGMENT10 and SEGMENT1_NAME–SEGMENT10_NAME — flexible segmented attributes and their labels.
• RECORD_TYPE_CODE — identifies the record category.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_LOGIN_ID.

Common Use Cases and Queries

Typical usage resolves a fiscal classification code to its ancestor chain, or lists all descendants of a given ancestor. Because the hierarchy is denormalized, a simple join suffices without CONNECT BY recursion.

• Finding ancestors of a specific code:

  SELECT classification_code, ancestor_code, classification_code_level
  FROM   zx.zx_fc_codes_denorm_b
  WHERE  classification_type_code = :type
  AND    classification_code      = :code
  AND    language                 = USERENV('LANG')
  AND    enabled_flag             = 'Y';

• Listing all codes under an ancestor:

  SELECT classification_code, classification_name, classification_code_level
  FROM   zx.zx_fc_codes_denorm_b
  WHERE  ancestor_id = :ancestor_id
  AND    SYSDATE BETWEEN effective_from AND NVL(effective_to, SYSDATE);

• Reporting by concatenated path using CONCAT_CLASSIF_CODE for display in tax reports.
• Restricting to a country or language using COUNTRY_CODE and LANGUAGE.

Reports frequently filter by ENABLED_FLAG and effective dates to obtain the currently valid hierarchy view, and use ZD_EDITION_NAME when querying editioned data in 12.2.2.

Related Objects

• ZX_FC_CODES_B — the base classification code table; ZX_FC_CODES_DENORM_B.CLASSIFICATION_ID references it.
• FND_TERRITORIES — territory definition; referenced via COUNTRY_CODE.
• FND_LANGUAGES — language definitions; referenced via LANGUAGE.
• ZX_FC_CODES_TL / ZX_FC_CODES_DENORM_TL — associated translated tables that hold language-specific descriptive text for the code and denormalized hierarchies.
• ZX_FC_CODES_VL / ZX_FC_CODES_DENORM_V — views commonly used to expose the effective, translated hierarchy for reporting.
• ZX_FC_TYPES_B — classification type definitions that provide the CLASSIFICATION_TYPE_CODE context.