Results for “xnp_service_providers”

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

Overview

XNP_SERVICE_PROVIDERS is a core configuration table within the XNP (Number Portability) module of Oracle E-Business Suite. It stores the definition of each service provider participating in number portability operations — telecommunications carriers, operators, or network entities that own, assign, donate, or receive telephone numbers during a port transaction. The table is owned by the XNP schema and is classified as VALID in ETRM metadata for both Oracle EBS 12.1.1 and 12.2.2.

From a data modeling perspective, the metadata suggests a hub classification. This is consistent with the FK structure: XNP_SERVICE_PROVIDERS has only one outbound foreign key (SECURITY_GROUP_ID referencing FND_SECURITY_GROUPS), while it is referenced by twenty-plus downstream tables. It functions as the master entity from which all portability transactions — audits, number ranges, SOA requests, SMS operations, routing, and failures — derive their provider context.

Key Information Stored

The primary key is SP_ID (XNP_SERVICE_PROVIDERS_PK), a surrogate identifier used throughout the XNP schema. Two unique indexes define business-key candidates: XNP_SERVICE_PROVIDERS_U1 on (SP_ID, ZD_EDITION_NAME) supports editioning, and XNP_SERVICE_PROVIDERS_U2 on (CODE, ZD_EDITION_NAME) enforces uniqueness of the provider short code.

Among the 35 documented columns, the most operationally significant include:

• CODE and NAME — the unique short identifier and descriptive label for the service provider.
• SP_TYPE — categorizes the provider's role (e.g., donor, recipient, mediator).
• TIMEZONE — used to schedule portability processing windows.
• ACTIVE_FLAG and SERVED_BY_FLAG — control whether the provider is selectable for new port transactions.
• SOA_MGT_FLAG, SOA_NETWORK_MGT_FLAG, SMS_DOWNLOAD_FLAG, SMS_NETWORK_MGT_FLAG, SMS_QUERY_FLAG, SV_DOWNLOAD_FLAG, NETWORK_DOWNLOAD_FLAG — capability flags indicating which portability interfaces and message channels the provider supports.
• SECURITY_GROUP_ID — links the row to the EBS security group model.
• Contact and addressing attributes such as ADDRESS_LINE1, CITY, STATE, ZIP_CODE, COUNTRY, PHONE, EMAIL, and MOBILE.
• Standard audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE.

Common Use Cases and Queries

Typical use cases include resolving the provider behind a portability event, validating that a port request is directed at an active provider, and reporting on provider-level activity volumes. A basic lookup by business key:

• SELECT sp_id, code, name, sp_type, active_flag FROM xnp.xnp_service_providers WHERE code = :p_code;
• Listing providers that support SMS interfaces: SELECT code, name FROM xnp.xnp_service_providers WHERE sms_query_flag = 'Y' AND active_flag = 'Y';
• Joining to number ranges to see allocation by provider: SELECT sp.code, nr.start_number, nr.end_number FROM xnp.xnp_service_providers sp, xnp.xnp_number_ranges nr WHERE nr.owning_sp_id = sp.sp_id;
• Reconciling SOA activity by donor/recipient: join XNP_SV_SOA.donor_sp_id and recipient_sp_id back to SP_ID for provider names in reports.

Related Objects

The following are the most significant dependents, all joining on SP_ID (or its role-specific alias):

• XNP_NUMBER_RANGES — references this table twice, via OWNING_SP_ID and ASSIGNED_SP_ID.
• XNP_SV_SOA and XNP_SV_SOA_JN — reference DONOR_SP_ID, RECIPIENT_SP_ID, MEDIATOR_SP_ID, CREATED_BY_SP_ID, and CHANGED_BY_SP_ID.
• XNP_SP_ADAPTERS — defines communication adapters per provider on SP_ID.
• XNP_SP_CONTACTS and XNP_SP_FILTERS — store contact records and message filters keyed by SP_ID.
• XNP_ROUTING_NUMBERS and XNP_SERVICE_AREAS — associate routing and coverage data with SP_ID.
• XNP_SV_AUDITS, XNP_SV_FAILURES, XNP_AUDIT_DISCREPANCIES, and XNP_AUDIT_SMS_FAILURES — capture operational history keyed by SP_ID.
• FND_SECURITY_GROUPS — the sole outbound reference, via SECURITY_GROUP_ID.