Results for “parent_geo_area_id”

22 results




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

Overview

XNP_GEO_HIERARCHY is a table within the XNP (Number Portability) product module of Oracle E-Business Suite, documented as VALID in the XNP schema. Its stated purpose is to hold information about the geographic area hierarchy, capturing the parent-child relationships that define how geographic areas are organized for number portability processing. In practice the table functions as an adjacency-list structure: each row records one directed relationship between a parent geographic area and a child geographic area, allowing arbitrarily deep hierarchies to be represented across multiple rows.

Mined from its foreign key structure, the heuristic Data Vault classification for this object is link. This is a modeling suggestion rather than a documented property: the table primarily resolves a many-to-many style association between geographic areas and carries no descriptive payload beyond its relationship columns and standard audit attributes. In Data Vault terms it would be modeled as a link between two instances of the geographic area hub.

Key Information Stored

The documented physical schema for XNP_GEO_HIERARCHY in ETRM 12.2.2 contains ten columns. The most significant are the following:

Two unique indexes act as business-key candidates. XNP_GEO_HIERARCHY_UK1 / _U2 spans PARENT_GEO_AREA_ID, CHILD_GEO_AREA_ID, and ZD_EDITION_NAME, preventing duplicate parent-child pairings within the same edition. XNP_GEO_HIERARCHY_U1 covers GEO_HIERARCHY_ID and ZD_EDITION_NAME, reinforcing uniqueness of the surrogate key per edition. Notably, the parent and child columns together form the natural business key, while GEO_HIERARCHY_ID serves only as the technical identifier.

Common Use Cases and Queries

The table is queried primarily to resolve geographic area rollups and to validate whether one area is subordinate to another. Typical patterns include joining both key columns back to XNP_GEO_AREAS_B to obtain area names, and recursively traversing the hierarchy to build full paths from root to leaf. A representative join follows:

  • SELECT h.geo_hierarchy_id, p.geo_area_name parent_area, c.geo_area_name child_area FROM xnp.xnp_geo_hierarchy h JOIN xnp.xnp_geo_areas_b p ON h.parent_geo_area_id = p.geo_area_id JOIN xnp.xnp_geo_areas_b c ON h.child_geo_area_id = c.geo_area_id WHERE h.security_group_id = :security_group_id;
  • Reporting on the set of geographic hierarchies assigned to a program or contract by joining GEO_HIERARCHY_ID to PV_PROGRAM_CONTRACTS or PV_PROGRAM_PAYMENT_MODE.
  • Identifying areas with no parent (top-level roots) or no children (leaves) via outer joins or NOT EXISTS subqueries.

Related Objects

The following objects are the most significant dependents or references documented for this table:

  • XNP_GEO_AREAS_B — referenced twice, through PARENT_GEO_AREA_ID and CHILD_GEO_AREA_ID; the master geographic area definition table.
  • FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID, controlling security group access.
  • PV_PROGRAM_CONTRACTS — references GEO_HIERARCHY_ID, associating a geographic hierarchy with program contracts.
  • PV_PROGRAM_PAYMENT_MODE — references GEO_HIERARCHY_ID, linking hierarchies to payment mode configurations.
  • PV_GE_RESP_MAP_RULES — references GEO_HIERARCHY_ID, tying hierarchies into responsibility mapping rules.

Together these relationships establish XNP_GEO_HIERARCHY as the central linking structure between geographic area definitions and the payment, contract, and responsibility mapping entities that consume geographic hierarchies in the Number Portability module.