Results for “xnp_routing_numbers”

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

Overview

The XNP.XNP_ROUTING_NUMBERS table is a core transactional object within the Oracle E-Business Suite XNP – Number Portability product. It stores routing numbers that are used to deliver ported telephone numbers to the appropriate service provider. In the context of number portability processing, routing numbers act as the network-level destination identifiers that allow calls or messages associated with a ported number to be translated and delivered to the correct carrier. The table has a status of VALID in both EBS 12.1.1 and 12.2.2 and is owned by the XNP schema, retaining 14 documented columns in the 12.2.2 physical model.

From a dimensional modeling perspective, the heuristic Data Vault classification derived from the foreign key structure is hub-leaning. This suggests that XNP_ROUTING_NUMBERS functions primarily as a hub entity: it carries a surrogate primary key and a set of descriptive and classification attributes, and it is referenced by multiple downstream tables rather than itself acting as a pure associative link or a history satellite. Modelers should treat the routing number as a durable business entity keyed by its surrogate identifier and its natural business keys.

Key Information Stored

The table’s documented columns include the following most significant attributes:

• ROUTING_NUMBER_ID – the surrogate primary key, defined by the constraint XNP_ROUTING_NUMBERS_PK. This is the identifier used by all dependent tables when joining back to a routing number.
• SP_ID – the service provider identifier, a foreign key to XNP_SERVICE_PROVIDERS. It associates each routing number with the carrier that receives traffic for the ported number.
• ROUTING_NUMBER – the actual routing number value used for call or message delivery.
• INTERCONNECT_TYPE – classifies the interconnect arrangement associated with the routing number.
• OBJECT_REFERENCE – an external or cross-system reference to the routing number object.
• ACTIVE_FLAG – indicates whether the routing record is currently active.
• STATUS – the lifecycle or processing status of the routing number.
• SECURITY_GROUP_ID – the security group, a foreign key to FND_SECURITY_GROUPS, supporting multi-organization or function-based data access.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – the standard Oracle EBS who-columns capturing audit and concurrency information.
• ZD_EDITION_NAME – the editioning column supporting EBS 12.2 online patching and edition-based redefinition.

Three unique indexes define the business-key candidates. XNP_ROUTING_NUMBERS_U1 covers ROUTING_NUMBER_ID and ZD_EDITION_NAME, reinforcing the surrogate key under editioning. XNP_ROUTING_NUMBERS_U2 covers SP_ID, ROUTING_NUMBER, INTERCONNECT_TYPE, and ZD_EDITION_NAME, representing the composite natural business key that prevents duplicate routing entries for the same provider, number, and interconnect type. XNP_ROUTING_NUMBERS_U3 covers OBJECT_REFERENCE and ZD_EDITION_NAME, providing an alternate access path for cross-system reference lookups. The metadata also lists a legacy-style primary key name XNP_ROUTING_NUMBERS_UK1 over SP_ID, ROUTING_NUMBER, and INTERCONNECT_TYPE — the term the user searched for — which corresponds to the U2 business-key concept.

Common Use Cases and Queries

Typical use cases involve resolving a ported telephone number to its current routing target, auditing service provider assignments, and reporting on active routing configurations. A representative query joining routing numbers to their service providers is:

• Lookup by business key: SELECT routing_number_id FROM xnp_routing_numbers WHERE sp_id = :p_sp AND routing_number = :p_num AND interconnect_type = :p_type AND zd_edition_name = 'SET1';
• Provider view: SELECT r.routing_number, r.interconnect_type, s.* FROM xnp_routing_numbers r, xnp_service_providers s WHERE r.sp_id = s.sp_id AND r.active_flag = 'Y';
• Dependency check before decommissioning: count child rows in XNP_SV_SMS, XNP_SV_SOA, and XNP_INTERCONNECT_AREAS referencing a given ROUTING_NUMBER_ID.

Because the table is editioned, queries executed under EBS 12.2 should be edition-aware or rely on the editioning view presented to the application.

Related Objects

XNP_ROUTING_NUMBERS participates in several documented foreign key relationships:

• XNP_SERVICE_PROVIDERS – referenced through XNP_ROUTING_NUMBERS.SP_ID; identifies the destination carrier.
• FND_SECURITY_GROUPS – referenced through SECURITY_GROUP_ID for access control.
• XNP_INTERCONNECT_AREAS – references XNP_ROUTING_NUMBERS.ROUTING_NUMBER_ID, grouping routing numbers by interconnect area.
• XNP_SV_SMS – references ROUTING_NUMBER_ID for SMS delivery configuration.
• XNP_SV_SOA and XNP_SV_SOA_JN – the service order activity table and its journal, both referencing ROUTING_NUMBER_ID.

These dependencies confirm that routing numbers act as a central reference point for number portability service delivery, interconnect grouping, and service order activity.