Search Results ancestor_id

Overview

The ZX_FC_CODES_DENORM_B table is a core data structure within the Oracle E-Business Tax (ZX) module for releases 12.1.1 and 12.2.2. It is designed to store denormalized hierarchical relationships for fiscal classification codes. Fiscal classification codes are used to categorize products or services for tax determination purposes, often adhering to standardized taxonomies like the Harmonized System (HS) codes. The table's primary role is to provide an optimized, flattened representation of these code hierarchies, enabling efficient querying and validation of a code's lineage and its ancestors within the tax engine's processing logic.

Key Information Stored

Each row in the table represents a specific classification code within a hierarchy. The primary key uniquely identifies a code's position in a hierarchy for a given language, consisting of CLASSIFICATION_TYPE_CODE, LANGUAGE, CLASSIFICATION_CODE, and ANCESTOR_ID. Key columns include CLASSIFICATION_TYPE_CODE, which identifies the taxonomy (e.g., a product category); CLASSIFICATION_CODE, the actual fiscal code; and ANCESTOR_ID, which stores the identifier of a parent or ancestor code within the hierarchy. The LANGUAGE column supports multilingual descriptions, while the COUNTRY_CODE links the classification to a specific territory. The CLASSIFICATION_ID column is a foreign key to the master code definitions in ZX_FC_CODES_B.

Common Use Cases and Queries

This table is critical for tax setup and real-time transaction tax calculation. A common use case is validating that a product's entered fiscal code is valid and belongs to the correct branch of a hierarchy for a given country. Another is retrieving all ancestor codes of a specific classification to apply inherited tax rules. For reporting, it is used to analyze code assignments across items. A typical query pattern retrieves the full hierarchical path for a code:

• SELECT classification_code, ancestor_id FROM zx_fc_codes_denorm_b WHERE classification_type_code = 'HS_CODE' AND language = 'US' START WITH classification_code = '123456' CONNECT BY PRIOR ancestor_id = classification_id;

For performance, applications often query this denormalized table directly instead of traversing a normalized hierarchy repeatedly.

Related Objects

The table maintains defined foreign key relationships with several key EBS objects, as documented in the ETRM metadata:

• ZX_FC_CODES_B: The master table for fiscal classification code definitions. Joined via ZX_FC_CODES_DENORM_B.CLASSIFICATION_ID to ZX_FC_CODES_B.CLASSIFICATION_ID to retrieve code attributes and status.
• FND_TERRITORIES: The standard Oracle Applications table for country information. Joined via ZX_FC_CODES_DENORM_B.COUNTRY_CODE to validate and provide territory context for the classification hierarchy.
• FND_LANGUAGES: The standard Oracle Applications table for installed languages. Joined via ZX_FC_CODES_DENORM_B.LANGUAGE to support multilingual hierarchy data.

The table's primary key constraint, ZX_FC_CODES_DENORM_B_PK, enforces data integrity for hierarchical queries.