Search Results routing_sp_id




Overview
Explore in AI Chat

Overview

APPS.XNP_SV_SMS_V is a reporting and integration view in the Oracle E-Business Suite Telecommunication Management (ETRM) module. It consolidates Short Message Service (SMS) subscription data held in the base table XNP_SV_SMS with service provider and routing number attributes, producing a denormalized, query-ready record of a provisioned SMS subscription and the network and service-provider context surrounding it.

The view is designed so that functional users, reports, and downstream integration processes can retrieve a single row per SMS subscription without repeatedly joining the routing and service-provider entities. It joins the SMS subscription record to the mediator (NRC) service provider, the routing number, and the recipient (REC) service provider, exposing columns from all four logical sources in one projection. This makes it suitable for operational reporting on provisioning status, SIM and subscription identifiers, mediator addresses, and routing information.

The view is relevant to both release levels addressed here. In EBS 12.1.1 and 12.2.2 the underlying ETRM schema objects (XNP_SV_SMS, XNP_ROUTING_NUMBERS, XNP_SERVICE_PROVIDERS) are unchanged in principle, and the view is defined in the APPS schema over synonyms pointing to those objects.

Underlying Base Objects

The view is defined over three referenced base objects, exposed to APPS through synonyms:

  • XNP_SV_SMS — the SMS subscription entity, supplying the primary key SV_SMS_ID, the porting identifier, routing number reference, subscription TN and type, mediator SP identifier, provisioning dates, and the CNAM, ISVM, LIDB, CLASS, WSMSC and RN address/subsystem attributes.
  • XNP_SERVICE_PROVIDERS — used twice: once as SPR_NRC (the mediator or NRC service provider) and once as SPR_REC (the recipient service provider), supplying provider type, code, name, timezone, address, contact and department attributes.
  • XNP_ROUTING_NUMBERS — supplying the routing object reference, interconnect type, routing SP identifier (aliased ROUTING_SP_ID), routing number and routing status.

The join condition in the documented definition links the mediator service provider with an outer join (SVS.MEDIATOR_SP_ID = SPR_NRC.SP_ID (+)), indicating the view tolerates SMS subscription rows that have no matching mediator provider. The routing number and recipient provider are joined in the same FROM clause, producing the flattened projection that characterizes this view.

Key Columns

  • SV_SMS_ID — primary identifier of the SMS subscription row; the effective key of the view.
  • ROUTING_SP_ID — the service provider identifier associated with the routing number (RNR.SP_ID). This is the column surfaced by searches for "routing_sp_id" and is the routing-side counterpart to MEDIATOR_SP_ID.
  • MEDIATOR_SP_ID — the service provider acting as mediator/NRC for the subscription.
  • SUBSCRIPTION_TN and SUBSCRIPTION_TYPE — the subscription telephone number and its classification.
  • PROVISION_SENT_DATE and PROVISION_DONE_DATE — timestamps for provisioning request dispatch and completion, used for turnaround analysis.
  • ROUTING_NUMBER, ROUTING_STATUS, INTERCONNECT_TYPE, ROUTING_REF — routing number value, its status, interconnect classification and object reference.
  • NRC_* columns — attributes of the mediator service provider (type, code, name, timezone, address, phone, email, department, and related contact fields).
  • REC_* columns — corresponding attributes of the recipient service provider, plus REC_SERVED_BY_FLAG.
  • Standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN).

Common Use Cases and Queries

Typical uses include provisioning-status reporting, reconciliation of subscriptions to mediators and routing numbers, and integration extracts that require provider contact details alongside subscription data. A representative query retrieves routing provider context for provisioned subscriptions:

  • Reporting by routing provider: SELECT SV_SMS_ID, SUBSCRIPTION_TN, ROUTING_NUMBER, ROUTING_SP_ID, ROUTING_STATUS FROM APPS.XNP_SV_SMS_V WHERE ROUTING_SP_ID = :p_sp_id;
  • Provisioning turnaround: SELECT SV_SMS_ID, PROVISION_SENT_DATE, PROVISION_DONE_DATE FROM APPS.XNP_SV_SMS_V WHERE PROVISION_DONE_DATE IS NULL;
  • Provider detail lookup: SELECT SV_SMS_ID, MEDIATOR_SP_ID, NRC_NAME, NRC_EMAIL, REC_SP_ID, REC_NAME FROM APPS.XNP_SV_SMS_V WHERE MEDIATOR_SP_ID IS NOT NULL;
  • Subscription by number: SELECT SV_SMS_ID, SUBSCRIPTION_TYPE, ROUTING_REF FROM APPS.XNP_SV_SMS_V WHERE SUBSCRIPTION_TN = :tn;

Because MEDIATOR_SP_ID and ROUTING_SP_ID are distinct columns, users should confirm which provider context is intended before filtering, as a single subscription may reference different providers on the mediator and routing sides.