Search Results hz_geographies

Overview

The HZ_GEOGRAPHIES table is a core master data repository within the Oracle E-Business Suite (EBS), specifically under the Receivables (AR) product family. It serves as the system of record for storing standardized, hierarchical data representing physical geographies. This table is fundamental to the Trading Community Architecture (TCA), providing a centralized, normalized source for geographic entities such as countries, states, provinces, counties, and cities. Its primary role is to enable consistent geographic classification and validation across all EBS modules, supporting critical functions like customer address validation, tax determination, territory management, and demographic reporting. By maintaining a single source of truth for geographic definitions, it ensures data integrity and standardization throughout the application.

Key Information Stored

While the provided metadata is concise, the table's purpose indicates it contains columns essential for defining and relating geographic entities. Based on standard TCA data model knowledge, key columns typically include GEOGRAPHY_ID (the primary key), GEOGRAPHY_CODE (a unique identifier, often linked to FND_TERRITORIES), and GEOGRAPHY_TYPE (defining the hierarchical level, such as COUNTRY, STATE, or COUNTY). Other critical columns store the NAME of the geography, its START_DATE and END_DATE for validity tracking, and hierarchical relationship columns like PARENT_GEOGRAPHY_ID to define the structure (e.g., a city belonging to a state). The GEOGRAPHY_CODE's foreign key relationship to FND_TERRITORIES.TERRITORY_CODE, as noted in the metadata, is crucial for integration with core EBS territory and localization setups.

Common Use Cases and Queries

A primary use case is the validation and enrichment of party site addresses during data entry via TCA APIs or forms. Reporting and analytics often leverage this table to segment data by region. Common SQL patterns include hierarchical queries to retrieve a full geographic tree and joins to customer location tables for regional analysis.

• Hierarchical Query for a Country/State/City Tree: SELECT LEVEL, NAME FROM HZ_GEOGRAPHIES START WITH GEOGRAPHY_CODE = 'US' CONNECT BY PRIOR GEOGRAPHY_ID = PARENT_GEOGRAPHY_ID ORDER SIBLINGS BY NAME;
• Joining to Customer Sites for Regional Reporting: SELECT hg.NAME STATE, COUNT(hps.PARTY_SITE_ID) FROM HZ_GEOGRAPHIES hg, HZ_PARTY_SITES hps WHERE hg.GEOGRAPHY_ID = hps.LOCATION_ID AND hg.GEOGRAPHY_TYPE = 'STATE' GROUP BY hg.NAME;

Related Objects

The HZ_GEOGRAPHIES table is central to the TCA data model. As per the metadata, it has a direct foreign key relationship to FND_TERRITORIES, linking operational geography data to EBS's foundational territory definitions. It is heavily referenced by other TCA entities, most notably the HZ_LOCATIONS table, where a LOCATION_ID often corresponds to a GEOGRAPHY_ID. Key APIs, such as those in the HZ_GEOGRAPHY_PUB package, are used to create, update, and validate records in this table. Furthermore, it is integral to the RA_ADDRESSES_ALL and HZ_PARTY_SITES tables for address management, and is used by tax modules like E-Business Tax for jurisdiction-based rule determination.