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:
- GEO_HIERARCHY_ID — the surrogate primary key, enforced by the XNP_GEO_HIERARCHY_PK constraint. It uniquely identifies each hierarchy relationship row and is the value referenced by downstream tables such as PV_PROGRAM_PAYMENT_MODE, PV_GE_RESP_MAP_RULES, and PV_PROGRAM_CONTRACTS.
- PARENT_GEO_AREA_ID — the parent node in the hierarchy relationship, with a foreign key to XNP_GEO_AREAS_B.
- CHILD_GEO_AREA_ID — the child node in the relationship, also with a foreign key to XNP_GEO_AREAS_B.
- SECURITY_GROUP_ID — the operating unit / security group discriminator, with a foreign key to FND_SECURITY_GROUPS, governing multi-org access to the row.
- ZD_EDITION_NAME — the editioning column used by Oracle EBS online patching in 12.2.x to distinguish edition-specific versions of a row.
- CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO audit columns recording row creation and modification metadata.
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.
-
Holds information about the geographic area hierarchy
-
Holds information about the geographic area hierarchy
-
TABLE: XNP.XNP_GEO_HIERARCHY 12.1.1
-
TABLE: XNP.XNP_GEO_HIERARCHY 12.2.2
-
VIEW: XNP.XNP_GEO_HIERARCHY# 12.2.2
-
VIEW: XNP.XNP_GEO_HIERARCHY# 12.2.2
-
PACKAGE: APPS.XNP_CORE 12.1.1
-
PACKAGE BODY: APPS.XNP_CORE 12.1.1
-
PACKAGE BODY: APPS.XNP_CORE 12.2.2
-
PACKAGE: APPS.XNP_CORE 12.2.2
-
eTRM - XNP Tables and Views 12.1.1
Stores a list of all timers in progress
-
eTRM - XNP Tables and Views 12.2.2
Stores a list of all timers in progress
-
APPS.XNP_CORE SQL Statements 12.1.1
-
APPS.XNP_CORE SQL Statements 12.2.2
-
eTRM - XNP Tables and Views 12.1.1
Stores a list of all timers in progress
-
eTRM - XNP Tables and Views 12.2.2
Stores a list of all timers in progress
-
PACKAGE BODY: APPS.XNP_CORE 12.2.2
-
PACKAGE BODY: APPS.XNP_CORE 12.1.1