Results for “hz_hierarchy_nodes”

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

Overview

HZ_HIERARCHY_NODES is a denormalized table in the Oracle E-Business Suite Receivables (AR) schema that stores flattened, pre-computed representations of hierarchical relationships between entities in the Trading Community Architecture (TCA) model. Rather than recursively traversing HZ_RELATIONSHIPS to resolve multi-level hierarchies at query time, applications read from HZ_HIERARCHY_NODES, which materializes parent-child links along with their depth and positional context. This design supports efficient reporting and navigation of organizational, customer, and party hierarchies without repeated recursive joins. In Oracle EBS 12.1.1 and 12.2.2 the table is owned by AR and remains a VALID object.

From a Data Vault modeling perspective, the mined foreign-key structure suggests classification as a link table. Each row connects a parent entity to a child entity (via PARENT_OBJECT_TYPE and CHILD_OBJECT_TYPE referencing FND_OBJECT_INSTANCE_SETS, and RELATIONSHIP_ID referencing HZ_RELATIONSHIPS), making it an associative structure that resolves the underlying relationship graph into navigable edges.

Key Information Stored

The table exposes 20 documented columns in the 12.2.2 schema. The most significant include:

• HIERARCHY_TYPE — classifies the hierarchy being represented (for example, organizational or party structures).
• PARENT_ID / PARENT_TABLE_NAME / PARENT_OBJECT_TYPE — identify the parent node, its base table, and its object-type set reference.
• CHILD_ID / CHILD_TABLE_NAME / CHILD_OBJECT_TYPE — identify the child node and its base table and object-type set reference.
• LEVEL_NUMBER — the depth of the node within the hierarchy, enabling level-based rollups.
• TOP_PARENT_FLAG / LEAF_CHILD_FLAG — flags marking root and terminal nodes respectively.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-range validity for the hierarchy edge.
• STATUS — lifecycle status of the node record.
• RELATIONSHIP_ID — the foreign key linking back to the originating HZ_RELATIONSHIPS row.
• ACTUAL_CONTENT_SOURCE — indicates the source of the denormalized content.

Standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) are also present. The metadata does not document a surrogate primary key or named unique index, so primary-key and business-key candidacy should be confirmed against the live dictionary rather than assumed.

Common Use Cases and Queries

Typical scenarios include hierarchical account and party rollups, organizational reporting, and resolving ancestor or descendant paths for a given entity. A common pattern filters by hierarchy type and level:

• Retrieve all root nodes for a hierarchy: SELECT * FROM HZ_HIERARCHY_NODES WHERE HIERARCHY_TYPE = :type AND TOP_PARENT_FLAG = 'Y';
• List children of a specific parent: SELECT CHILD_ID, CHILD_TABLE_NAME FROM HZ_HIERARCHY_NODES WHERE PARENT_ID = :id AND PARENT_TABLE_NAME = :tbl;
• Filter by current validity using EFFECTIVE_START_DATE and EFFECTIVE_END_DATE relative to SYSDATE.
• Join to HZ_RELATIONSHIPS via RELATIONSHIP_ID to obtain relationship-type and directional detail.

Because the table is denormalized, it is well suited to reporting queries that would otherwise require recursive CONNECT BY logic against the relationship graph.

Related Objects

The documented foreign keys and parent relationships identify the following significant objects:

• HZ_RELATIONSHIPS — referenced via HZ_HIERARCHY_NODES.RELATIONSHIP_ID; the transactional source of edges.
• FND_OBJECT_INSTANCE_SETS — referenced twice, via PARENT_OBJECT_TYPE and CHILD_OBJECT_TYPE, defining the object types involved.
• TCA party and relationship tables (for example HZ_PARTIES) — the underlying entities that PARENT_ID and CHILD_ID resolve to through their respective table names.

These dependencies confirm that HZ_HIERARCHY_NODES functions primarily as a navigational and reporting layer over the TCA relationship model rather than as a system of record.