Results for “xnp_sv_sms”

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

Overview

The XNP.XNP_SV_SMS table is a container object within the Oracle E-Business Suite Number Portability (XNP) module, responsible for holding subscription versions downloaded to the network. It functions as the persistence layer for subscription data associated with SMS-related provisioning activities, capturing routing information, mediator service provider assignments, and service-specific subsystem addresses used during number portability operations.

From a data modeling perspective, the heuristic classification derived from the foreign key structure identifies this table as satellite-leaning. This suggests that XNP_SV_SMS is best modeled as a satellite entity attached to a broader subscription or routing hub, carrying descriptive and versioned attributes rather than serving as a pure hub of independent business keys or a pure link between two hubs. Analysts building a Data Vault representation should therefore treat this object as a source of descriptive detail keyed to parent entities such as routing numbers and service providers.

Key Information Stored

The table comprises 26 documented columns. The surrogate primary key is SV_SMS_ID, enforced through XNP_SV_SMS_PK. A unique index, XNP_SV_SMS_U1, also exists on SV_SMS_ID, marking it as the documented business-key candidate. The most significant columns include:

• SV_SMS_ID — the primary and business-key identifier for each subscription version record.
• OBJECT_REFERENCE — an external or system-level reference to the underlying subscription object.
• ROUTING_NUMBER_ID — foreign key to XNP_ROUTING_NUMBERS, linking the record to its routing context.
• SUBSCRIPTION_TN — the telephone number associated with the subscription.
• SUBSCRIPTION_TYPE — categorizes the subscription for provisioning logic.
• MEDIATOR_SP_ID — foreign key to XNP_SERVICE_PROVIDERS identifying the mediating service provider.
• PROVISION_SENT_DATE — timestamp indicating when the provisioning request was transmitted.
• PROVISION_DONE_DATE — timestamp indicating completion of provisioning.
• CNAM_ADDRESS / CNAM_SUBSYSTEM — calling name database address and subsystem identifiers.
• ISVM_ADDRESS / ISVM_SUBSYSTEM — ISVM subsystem routing details.
• LIDB_ADDRESS / LIDB_SUBSYSTEM — LIDB subsystem routing details.
• CLASS_ADDRESS / CLASS_SUBSYSTEM — CLASS service address and subsystem.
• WSMSC_ADDRESS / WSMSC_SUBSYSTEM — wireless SMSC routing information.
• RN_ADDRESS / RN_SUBSYSTEM — routing number address and subsystem.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-org or row-level security.

Audit columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide standard WHO-column tracking.

Common Use Cases and Queries

This table is typically queried to audit subscription provisioning status, reconcile network downloads, and report on porting activity by service provider or routing number. A representative query to retrieve pending provisions is:

• SELECT sv_sms_id, subscription_tn, mediator_sp_id, provision_sent_date FROM xnp_sv_sms WHERE provision_done_date IS NULL AND security_group_id = :sg_id;

Reporting on service provider volume is another frequent pattern, joining to XNP_SERVICE_PROVIDERS on mediator_sp_id. Reconciliation queries compare provision_sent_date against provision_done_date to detect latency in network provisioning. Operational dashboards commonly filter by subscription_type and aggregate by routing_number_id.

Related Objects

The following objects are directly related through documented foreign key relationships:

• XNP_ROUTING_NUMBERS — referenced via XNP_SV_SMS.ROUTING_NUMBER_ID.
• XNP_SERVICE_PROVIDERS — referenced via XNP_SV_SMS.MEDIATOR_SP_ID.
• FND_SECURITY_GROUPS — referenced via XNP_SV_SMS.SECURITY_GROUP_ID.
• XNP_SV_ORDER_MAPPINGS — references this table via SV_SMS_ID.
• XNP_SV_SMS_FE_MAPS — references this table via SV_SMS_ID.

Together these relationships position XNP_SV_SMS as a central subscription-version container linking routing, service provider, and downstream mapping objects within the Number Portability data model.