Search Results rec_code

Overview

XNP_SV_SMS_V is a reportable view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the XNP product family (Number Portability) and presents porting information broadcast by the Number Portability Administration Center-style routing entity (referred to in the metadata as the NRC) for the purpose of network update. The view combines subscription-level messaging records with routing number, donor/recipient service provider, and NRC service provider attributes, producing a flattened, denormalized result set suitable for both operational reporting and outbound integration interfaces. In the ETRM documentation the object is registered as a VALID view with the description "Shows porting information broadcasted by the NRC for the intention of network update."

Because it exposes a single wide row per SMS subscription record, the view is well suited to feed provisioning and network-update processes, and to answer audit questions about when porting information was distributed.

Underlying Base Objects

The documented metadata lists three referenced base objects, all resolved through APPS synonyms:

XNP_SV_SMS — the driving table, aliased SVS in the view definition; supplies the subscription messaging and provisioning columns.
XNP_ROUTING_NUMBERS — aliased RNR; supplies the routing number, interconnect type, routing service provider, and routing status.
XNP_SERVICE_PROVIDERS — joined twice, aliased SPR_NRC (the NRC/service provider broadcasting the port) and SPR_REC (the recipient service provider).

The view text joins these sources on the SMS record's routing number identifier and on the two service provider identifiers, projecting both provider joins into a single row with NRC_ and REC_ prefixed column names.

Key Columns

The columns fall into four logical groups:

Subscription/SMS identifiers: SV_SMS_ID (primary key of the SMS record), PORTING_ID (aliased from OBJECT_REFERENCE), ROUTING_NUMBER_ID, SUBSCRIPTION_TN, and SUBSCRIPTION_TYPE.
Provisioning and mediator: MEDIATOR_SP_ID — the service provider identifier of the mediator through which provisioning was sent — plus PROVISION_SENT_DATE and PROVISION_DONE_DATE, which record despatch and completion of the network update.
Network element addressing: CNAM_ADDRESS/SUBSYSTEM, ISVM_ADDRESS/SUBSYSTEM, LIDB_ADDRESS/SUBSYSTEM, CLASS_ADDRESS/SUBSYSTEM, WSMSC_ADDRESS/SUBSYSTEM, and RN_ADDRESS/SUBSYSTEM, giving the signalling point and subsystem for each downstream network element.
Service provider detail: NRC_ and REC_ prefixed attributes from XNP_SERVICE_PROVIDERS (SP_TYPE, CODE, NAME, TIMEZONE, address, phone, email, department) and routing attributes from XNP_ROUTING_NUMBERS (ROUTING_REF, INTERCONNECT_TYPE, ROUTING_SP_ID, ROUTING_NUMBER, ROUTING_STATUS).
Audit: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The view is typically queried to trace which mediator delivered a porting notification and when, to reconcile NRC-broadcast information against recipient records, and to drive provisioning extracts. A user searching for "mediator_sp_id" is generally locating the column that identifies the mediator service provider on the SMS record.

Filter by mediator to identify records routed through a given mediator service provider.
Compare NRC and recipient provider attributes to validate donor/recipient relationships.
Report outstanding updates by comparing PROVISION_SENT_DATE against PROVISION_DONE_DATE.

Typical query:

SELECT sv_sms_id, porting_id, subscription_tn, mediator_sp_id, nrc_name, rec_name, provision_sent_date, provision_done_date FROM apps.xnp_sv_sms_v WHERE mediator_sp_id = :p_mediator_sp_id AND provision_done_date IS NULL;

View: XNP_SV_SMS_V 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:XNP.XNP_SV_SMS_V, object_name:XNP_SV_SMS_V, status:VALID, product: XNP - Number Portability , description: Shows porting information broadcasted by the NRC for the intention of network update , implementation_dba_data: APPS.XNP_SV_SMS_V ,
View: XNP_SV_SMS_V 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:XNP.XNP_SV_SMS_V, object_name:XNP_SV_SMS_V, status:VALID, product: XNP - Number Portability , description: Shows porting information broadcasted by the NRC for the intention of network update , implementation_dba_data: APPS.XNP_SV_SMS_V ,
View: XNP_SV_SOA_VL 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:XNP.XNP_SV_SOA_VL, object_name:XNP_SV_SOA_VL, status:VALID, product: XNP - Number Portability , description: Shows porting information for a single telephone number. Removed ( SVA.CREATED_BY_SP_ID=SPR_OWN.SP_ID ) from WHERE clausefor Service Bureau, since this will be handled automagically by VPD. rnyberg, 11/06/2000. , implementation_dba_data: APPS.XNP_SV_SOA_VL ,
View: XNP_SV_SOA_VL 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:XNP.XNP_SV_SOA_VL, object_name:XNP_SV_SOA_VL, status:VALID, product: XNP - Number Portability , description: Shows porting information for a single telephone number. Removed ( SVA.CREATED_BY_SP_ID=SPR_OWN.SP_ID ) from WHERE clausefor Service Bureau, since this will be handled automagically by VPD. rnyberg, 11/06/2000. , implementation_dba_data: APPS.XNP_SV_SOA_VL ,