Results for “xnp_number_splits”

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

Overview

The XNP_NUMBER_SPLITS table is a core configuration object within the Oracle EBS Number Portability (XNP) product module. It stores the definition of a number split event, in which a contiguous telephone number range is divided or remapped into a new range. Each row captures the relationship between the old number range and the new number range, together with the algorithm — implemented as a stored conversion procedure — used to derive a new number from a legacy number. This data is fundamental to number portability processing, because downstream call routing, provisioning, and rating logic must be able to translate an old subscriber number into its replacement number for the period in which a split is active.

From a Data Vault modeling perspective, the FK relationships mined from the schema suggest that XNP_NUMBER_SPLITS behaves as a link entity. Its two foreign keys both resolve to XNP_NUMBER_RANGES, meaning each record associates two distinct range entities (the old range and the new range) captured at a point in time. Under this heuristic, XNP_NUMBER_SPLITS is the associative construct that joins two range hubs, with the split-level attributes (conversion procedure, permissive dial dates) acting as link-level descriptive context.

Key Information Stored

The table is owned by the XNP schema and, in ETRM 12.2.2, contains 12 documented columns. The most significant are:

• NUMBER_SPLIT_ID — the surrogate primary key, uniquely identifying each split record (enforced by XNP_NUMBER_SPLITS_PK and also by the unique index XNP_NUMBER_SPLITS_U1).
• OLD_NUMBER_RANGE_ID — foreign key to XNP_NUMBER_RANGES, identifying the legacy number range that is being split.
• NEW_NUMBER_RANGE_ID — foreign key to XNP_NUMBER_RANGES, identifying the replacement number range that the old numbers are mapped into.
• CONVERSION_PROCEDURE — the name of the PL/SQL routine that implements the algorithm converting an old number to its new equivalent.
• PERMISSIVE_DIAL_START_DATE — the date from which permissive dialing (simultaneous use of old and new numbers) begins.
• PERMISSIVE_DIAL_END_DATE — the date on which permissive dialing ends and only the new number is valid.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-tenant / operating-unit data segregation.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle EBS WHO columns providing audit and concurrency tracking.

The business-key candidate is the unique index XNP_NUMBER_SPLITS_U2, defined over the composite (NEW_NUMBER_RANGE_ID, OLD_NUMBER_RANGE_ID). This enforces that a given old-to-new range pairing is unique, distinguishing it from the purely surrogate uniqueness of NUMBER_SPLIT_ID.

Common Use Cases and Queries

Typical scenarios involve resolving a legacy dialed number to its current form, auditing the window during which a split is effective, and reporting all splits affecting a given range.

• Identify the conversion algorithm and permissive dialing window for a specific split.
• List all splits whose permissive window is currently open, to determine where both old and new numbers must be accepted.
• Join to XNP_NUMBER_RANGES on both OLD_NUMBER_RANGE_ID and NEW_NUMBER_RANGE_ID to report the actual start and end digits of each range.

A representative query joining the old and new range definitions is:

SELECT s.number_split_id, s.conversion_procedure, s.permissive_dial_start_date, s.permissive_dial_end_date, ro.range_start old_start, ro.range_end old_end, rn.range_start new_start, rn.range_end new_end FROM xnp_number_splits s, xnp_number_ranges ro, xnp_number_ranges rn WHERE s.old_number_range_id = ro.number_range_id AND s.new_number_range_id = rn.number_range_id AND s.permissive_dial_start_date <= SYSDATE AND (s.permissive_dial_end_date IS NULL OR s.permissive_dial_end_date >= SYSDATE);

Related Objects

• XNP_NUMBER_RANGES — referenced twice by this table; joined on XNP_NUMBER_SPLITS.OLD_NUMBER_RANGE_ID and XNP_NUMBER_SPLITS.NEW_NUMBER_RANGE_ID. Provides the digit boundaries of each range.
• FND_SECURITY_GROUPS — referenced by XNP_NUMBER_SPLITS.SECURITY_GROUP_ID; governs row-level access control across operating units in a multi-org environment.
• XNP_NUMBER_SPLITS_PK / XNP_NUMBER_SPLITS_U1 / XNP_NUMBER_SPLITS_U2 — the primary key and unique indexes that enforce surrogate and business-key integrity and act as critical access paths for range-to-split lookups.

Because the split definition drives runtime number translation, any API, concurrent program, or provisioning process in the XNP module that resolves legacy-to-new number mappings depends indirectly on this table through the conversion procedure and range identifiers it stores.