Results for “geo_area_type_code”

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

Overview

XNP_GEO_AREAS_B is the base (B) table within the Oracle E-Business Suite Number Portability (XNP) product module, which supports number portability processing for telecommunications service providers. This table stores the definition of given geographic areas used throughout the module to segment numbers, services, and interconnect arrangements by region. It functions as the master reference for geographic area data, holding language-independent attributes that are complemented by the translated XNP_GEO_AREAS_TL table.

In Oracle EBS 12.1.1 and 12.2.2, XNP_GEO_AREAS_B is a core setup and reference object. Other XNP entities such as number ranges, service areas, interconnect areas, and geographic hierarchies all resolve through foreign keys to a geographic area row in this table. In Data Vault modeling terms, the heuristic classification of this object is a hub: its primary key GEO_AREA_ID identifies a unique geographic area business entity, while its descriptive and audit attributes (active flag, audit columns, security group, edition name) are carried in the same physical table rather than split into satellites. This classification is offered as a modeling suggestion, not as a normative property of the EBS schema.

Key Information Stored

The table contains 11 documented columns. The most important are:

• GEO_AREA_ID — the surrogate primary key, defined by the XNP_GEO_AREAS_B_PK constraint. It uniquely identifies each geographic area record and is the value referenced by all dependent tables.
• GEO_AREA_TYPE_CODE and CODE — the composite business key defined by unique index XNP_GEO_AREAS_B_U2. GEO_AREA_TYPE_CODE classifies the area (for example, region, exchange, or other area types used in portability processing), while CODE holds the internal identifier for the area within that type.
• ACTIVE_FLAG — indicates whether the geographic area is currently valid for use by dependent number range, service area, and interconnect area records.
• SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS, enabling Multi-Org and security-group partitioning of geographic area definitions.
• ZD_EDITION_NAME — the edition column that supports Oracle’s edition-based redefinition. It participates in both unique indexes XNP_GEO_AREAS_B_U1 (GEO_AREA_ID, ZD_EDITION_NAME) and XNP_GEO_AREAS_B_U2 (GEO_AREA_TYPE_CODE, CODE, ZD_EDITION_NAME).
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Who columns capturing the audit trail of record creation and modification.

Common Use Cases and Queries

Typical applications of XNP_GEO_AREAS_B include resolving a geographic area code to its surrogate key for downstream association, identifying all number ranges or service areas within a given geographic region, validating active area definitions before configuration, and building territory-based reports.

• Resolving a business key to the surrogate id:
  SELECT geo_area_id FROM xnp_geo_areas_b WHERE geo_area_type_code = :type AND code = :code AND active_flag = 'Y';
• Listing active areas with translated names, joining the TL table:
  SELECT b.geo_area_id, b.code, b.geo_area_type_code, t.description
FROM xnp_geo_areas_b b, xnp_geo_areas_tl t
WHERE b.geo_area_id = t.geo_area_id AND t.language = USERENV('LANG') AND b.active_flag = 'Y';
• Counting number ranges and service areas per geographic area for capacity or coverage reporting, using the foreign keys from XNP_NUMBER_RANGES.GEO_AREA_ID and XNP_SERVICE_AREAS.GEO_AREA_ID.
• Traversing the geographic hierarchy by joining XNP_GEO_HIERARCHY on parent and child GEO_AREA_ID to report parent-child area relationships.

Related Objects

The following significant objects reference XNP_GEO_AREAS_B or are referenced by it. All joins use GEO_AREA_ID unless noted.

• XNP_GEO_AREAS_TL — stores translated geographic area descriptions; joined on GEO_AREA_ID for multilingual reporting.
• XNP_GEO_HIERARCHY — defines parent-child relationships between areas via PARENT_GEO_AREA_ID and CHILD_GEO_AREA_ID, both foreign keys to this table.
• XNP_NUMBER_RANGES — associates dialed number ranges with a geographic area through GEO_AREA_ID.
• XNP_SERVICE_AREAS — associates service area definitions with a geographic area through GEO_AREA_ID.
• XNP_INTERCONNECT_AREAS — links interconnect arrangements to a geographic area through GEO_AREA_ID.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID for security partitioning.
• XDP_FES — references GEO_AREA_ID, extending geographic area usage into the XDP provisioning schema.