Results for “hz_geo_structure_levels”

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

Overview

HZ_GEO_STRUCTURE_LEVELS is a table owned by the AR (Receivables) schema in Oracle E-Business Suite, holding the definition of geographical hierarchical structures used throughout the E-Business Tax and geography data model. Per the ETRM metadata it is described succinctly as storing "the geographical hierarchical structures," and its status is VALID in both 12.1.1 and 12.2.2. The table defines the parent/child relationships between geography types — for example, how a COUNTRY relates to a STATE, a STATE to a COUNTY, and a COUNTY to a CITY — and binds those relationships to a specific relationship type. It therefore underpins geography validation, tax jurisdiction determination, and address validation logic performed by the Receivables and E-Business Tax engines.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure is satellite-leaning. This suggests the object is best treated as a descriptive satellite attached to a geography business key (the composite of geography identifiers and edition), rather than as an independent hub or link. Analysts applying Data Vault or dimensional techniques should regard this as a modeling suggestion only.

Key Information Stored

The documented physical schema contains 20 columns. The most significant are:

• GEOGRAPHY_ID — surrogate identifier for the geography record participating in the hierarchy.
• GEOGRAPHY_TYPE — the type (level) being defined, such as COUNTRY, STATE, or CITY.
• PARENT_GEOGRAPHY_TYPE — the type that sits immediately above GEOGRAPHY_TYPE in the hierarchy.
• RELATIONSHIP_TYPE_ID — foreign key to HZ_RELATIONSHIP_TYPES; qualifies the nature of the parent/child association.
• COUNTRY_CODE — the country to which the structure level applies.
• GEOGRAPHY_ELEMENT_COLUMN — identifies the underlying geography element (address element) mapped to this level.
• ADDR_VAL_LEVEL — the address validation level assigned to the geography level.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OA Framework.
• ZD_EDITION_NAME — the edition (set) to which the row belongs; a key component for multi-edition geography data.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard WHO audit columns.
• APPLICATION_ID, PROGRAM_ID, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_LOGIN_ID, LAST_UPDATE_LOGIN — concurrent program and session context columns.

The unique index HZ_GEO_STRUCTURE_LEVELS_U1 covering (GEOGRAPHY_ID, GEOGRAPHY_TYPE, PARENT_GEOGRAPHY_TYPE, ZD_EDITION_NAME) identifies the business-key candidate. GEOGRAPHY_ID acts as the surrogate identifier, while the composite of the unique index provides the natural key.

Common Use Cases and Queries

Typical reporting and configuration queries resolve the parent/child chain for a geography type, or join to HZ_RELATIONSHIP_TYPES to describe the relationship. For example:

• Listing all hierarchy levels for a country: SELECT geography_type, parent_geography_type, relationship_type_id FROM hz_geo_structure_levels WHERE country_code = :country AND zd_edition_name = :edition;
• Resolving geography validation levels: SELECT geography_type, addr_val_level FROM hz_geo_structure_levels ORDER BY geography_type;
• Joining to relationship types: SELECT g.geography_type, r.relationship_type_name FROM hz_geo_structure_levels g, hz_relationship_types r WHERE g.relationship_type_id = r.relationship_type_id;

These queries feed dashboards for address validation configuration, tax jurisdiction setup, and geography maintenance, and are also used for data migration verification of the ZD_EDITION_NAME dimension.

Related Objects

• HZ_RELATIONSHIP_TYPES — referenced via HZ_GEO_STRUCTURE_LEVELS.RELATIONSHIP_TYPE_ID; the definitive FK for this table.
• HZ_GEOGRAPHIES — the master geography table holding valid GEOGRAPHY_ID values.
• HZ_GEO_STRUCTURE_TYPES — defines the geography types referenced by GEOGRAPHY_TYPE and PARENT_GEOGRAPHY_TYPE.
• FND_TERRITORIES — related via COUNTRY_CODE for country definition.
• HZ_EDITION_MAP / ZD_EDITION — the edition dimension referenced by ZD_EDITION_NAME.
• FND_APPLICATION / FND_CONCURRENT_PROGRAMS — relate via APPLICATION_ID and PROGRAM_ID for audit context.
• E-Business Tax regime/tax jurisdiction tables — consume these structure levels during jurisdiction resolution.