Results for “xnp_number_ranges”

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

Overview

XNP_NUMBER_RANGES is a core table in the Oracle EBS Number Portability (XNP) module, which supports telecommunications service providers in managing telephone number portability and number range administration. The table stores information about the characteristics of a service provider's telephone number ranges, including the geographic scope, service type, ownership, and assignment details of each range. It serves as a central repository for defining which blocks of numbers a provider owns, which have been assigned to other providers, and whether those ranges are portable, pooled, or geographically constrained.

In Oracle EBS 12.1.1 and 12.2.2, XNP_NUMBER_RANGES is delivered under the XNP schema with a status of VALID. The table contains 22 documented columns and supports multiple unique indexes that act as business-key candidates. Based on the foreign key structure, a heuristic Data Vault classification suggests this table is hub-leaning, meaning it likely functions as a central entity hub from which number range attributes and relationships radiate. This classification is a modeling suggestion rather than a documented implementation detail.

Key Information Stored

The primary surrogate key for the table is NUMBER_RANGE_ID, enforced by the XNP_NUMBER_RANGES_PK constraint. This identifier uniquely distinguishes each number range record. Two additional unique indexes — XNP_NUMBER_RANGES_UK1 on STARTING_NUMBER and XNP_NUMBER_RANGES_UK2 on OBJECT_REFERENCE — serve as business-key candidates that allow natural identification of a range by its starting number or by an external object reference.

The most significant columns include:

• NUMBER_RANGE_ID — Surrogate primary key uniquely identifying each range.
• STARTING_NUMBER and ENDING_NUMBER — Define the lower and upper bounds of the telephone number range.
• OBJECT_REFERENCE — External or legacy reference identifier, also a unique business key candidate.
• OWNING_SP_ID and ASSIGNED_SP_ID — Foreign keys to XNP_SERVICE_PROVIDERS identifying the provider that owns the range and the provider to which it is assigned.
• GEO_AREA_ID — Foreign key to XNP_GEO_AREAS_B defining the geographic area associated with the range.
• SERVICE_TYPE and STATUS — Classify the type of service and the current lifecycle state of the range.
• GEO_INDICATOR, MOBILE_INDICATOR, and PORTED_INDICATOR — Flags describing geographic constraints, mobile applicability, and whether the range has been ported.
• EFFECTIVE_DATE and ACTIVE_FLAG — Control the temporal validity and active status of the range.
• POOLED_FLAG — Indicates whether the range participates in number pooling.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS enforcing row-level security.
• ZD_EDITION_NAME — Supports edition-based redefinition in 12.2.2.

Common Use Cases and Queries

Typical scenarios include identifying all number ranges owned by a specific service provider, locating ranges assigned to another provider, and reporting on ported or pooled ranges. A common query joins XNP_NUMBER_RANGES to XNP_SERVICE_PROVIDERS to resolve provider names:

• SELECT r.STARTING_NUMBER, r.ENDING_NUMBER, r.STATUS FROM XNP_NUMBER_RANGES r WHERE r.OWNING_SP_ID = :sp_id;
• SELECT r.* FROM XNP_NUMBER_RANGES r WHERE r.PORTED_INDICATOR = 'Y' AND r.ACTIVE_FLAG = 'Y';
• Querying by geographic area: SELECT * FROM XNP_NUMBER_RANGES WHERE GEO_AREA_ID = :geo_id;

Reporting use cases include audits of assigned versus owned ranges, capacity planning based on range utilization, and reconciliation of number splits recorded in XNP_NUMBER_SPLITS. Filtering by EFFECTIVE_DATE supports point-in-time analysis of range ownership.

Related Objects

XNP_NUMBER_RANGES participates in several foreign key relationships that define its dependencies and dependents:

• XNP_GEO_AREAS_B — Referenced via GEO_AREA_ID, supplying geographic area definitions.
• XNP_SERVICE_PROVIDERS — Referenced twice via OWNING_SP_ID and ASSIGNED_SP_ID, supplying provider identity.
• FND_SECURITY_GROUPS — Referenced via SECURITY_GROUP_ID for row-level security.
• XNP_NUMBER_SPLITS — References XNP_NUMBER_RANGES through NEW_NUMBER_RANGE_ID and OLD_NUMBER_RANGE_ID, recording range split history.
• XNP_SERVED_NUM_RANGES — References NUMBER_RANGE_ID, linking ranges to served number records.
• XNP_SP_FILTERS — References NUMBER_RANGE_ID, associating ranges with service provider filter criteria.

These relationships make XNP_NUMBER_RANGES a central hub for number portability data, with downstream tables capturing splits, served ranges, and filtering rules that depend on the defined ranges.