Search Results xnp_geo_hierarchy




Overview
Explore in AI Chat

Overview

The XNP_GEO_HIERARCHY table is a core data structure within the Oracle E-Business Suite (EBS) Number Portability (XNP) module. It serves as a relational mapping table that defines the hierarchical relationships between different geographic areas. This hierarchy is essential for managing number portability processes, which involve the transfer of telephone numbers between service providers within specific geographic regions. The table enables the system to understand parent-child relationships between areas, such as a state containing multiple rate centers or a country containing multiple states, which is critical for routing, validation, and regulatory compliance in telecommunications operations.

Key Information Stored

The table stores unique records that link a parent geographic area to a child geographic area. Its primary columns are the relationship identifier and the two key foreign key columns that establish the link. The critical columns, as defined by the provided metadata, are:

  • GEO_HIERARCHY_ID: The primary key (PK) column, uniquely identifying each hierarchical relationship record.
  • PARENT_GEO_AREA_ID: A foreign key (FK) column referencing the XNP_GEO_AREAS_B table. It holds the identifier for the parent or containing geographic area.
  • CHILD_GEO_AREA_ID: A foreign key (FK) column also referencing the XNP_GEO_AREAS_B table. It holds the identifier for the child or subordinate geographic area that falls under the parent's jurisdiction.

The table enforces data integrity through a unique constraint (UK1) on the combination of PARENT_GEO_AREA_ID and CHILD_GEO_AREA_ID, preventing duplicate hierarchical links.

Common Use Cases and Queries

A primary use case is validating whether a given geographic area (e.g., a rate center) is valid within a larger region for a porting request. Reports may also leverage this table to generate organizational charts of geographic coverage. Common SQL queries involve joining to the XNP_GEO_AREAS_B table to resolve area IDs into meaningful names. For instance, to find all child areas for a specific parent:

  • SELECT child.GEO_AREA_NAME FROM xnp_geo_hierarchy hier, xnp_geo_areas_b child WHERE hier.child_geo_area_id = child.geo_area_id AND hier.parent_geo_area_id = <PARENT_ID>;

Conversely, to find the parent for a specific child area (a direct lookup relevant to the user's search for "child_geo_area_id"):

  • SELECT parent.GEO_AREA_NAME FROM xnp_geo_hierarchy hier, xnp_geo_areas_b parent WHERE hier.parent_geo_area_id = parent.geo_area_id AND hier.child_geo_area_id = <CHILD_ID>;

Related Objects

The XNP_GEO_HIERARCHY table has a direct and exclusive relationship with the XNP_GEO_AREAS_B table, which is the master list of all geographic areas. As documented in the foreign key relationships:

  • XNP_GEO_AREAS_B: This table is referenced twice by XNP_GEO_HIERARCHY.
    • Via the CHILD_GEO_AREA_ID column.
    • Via the PARENT_GEO_AREA_ID column.

All geographic area identifiers stored in XNP_GEO_HIERARCHY must exist as valid GEO_AREA_ID values in XNP_GEO_AREAS_B. This design centralizes area definitions and ensures referential integrity across the hierarchy.