Search Results xnp_sp_filters_uk2

Overview

The XNP_SP_FILTERS table is a core data object within the Oracle E-Business Suite (EBS) Number Portability (XNP) module. Its primary function is to manage service provider broadcast exclusions. Specifically, it stores configurable number range filters that define for which subscription broadcasts a particular service provider (SP) does not wish to receive notifications. This is a critical component in streamlining communication within the portability ecosystem, ensuring that service providers are only alerted to relevant subscription events, thereby reducing unnecessary system traffic and processing overhead. The table's integrity is enforced through a primary key and two unique constraints, and it maintains referential integrity with key master tables in the XNP schema.

Key Information Stored

The table's structure is designed to link a service provider with a specific number range for filtering purposes. The key columns include:

• SP_FILTER_ID: The primary key identifier for each filter record.
• SP_ID: A foreign key referencing XNP_SERVICE_PROVIDERS, identifying the service provider applying the filter.
• NUMBER_RANGE_ID: A foreign key referencing XNP_NUMBER_RANGES, identifying the specific range of telephone numbers to be excluded from broadcasts.
• OBJECT_REFERENCE: Part of a unique constraint (UK1), this column likely stores a reference to the specific business object or transaction context for which the filter is applicable.

The unique constraint XNP_SP_FILTERS_UK2 on SP_ID and NUMBER_RANGE_ID ensures that a service provider cannot define duplicate filters for the same number range.

Common Use Cases and Queries

A primary use case is the administrative setup and maintenance of broadcast preferences during service provider configuration. System processes will query this table to filter the recipient list before initiating a subscription broadcast for a number falling within a defined range. Common reporting and validation queries include identifying all filters for a given service provider or verifying if a specific number is within a filtered range. Sample SQL patterns include:

• Retrieving all filtered ranges for a provider: SELECT nr.* FROM xnp_sp_filters f, xnp_number_ranges nr WHERE f.number_range_id = nr.number_range_id AND f.sp_id = :sp_id;
• Checking for an active filter on a specific number: SELECT COUNT(*) FROM xnp_sp_filters f, xnp_number_ranges nr WHERE f.number_range_id = nr.number_range_id AND f.sp_id = :sp_id AND :target_number BETWEEN nr.range_start AND nr.range_end;

These queries are foundational for the broadcast engine's decision logic.

Related Objects

XNP_SP_FILTERS is centrally connected to two primary master tables via foreign key relationships:

• XNP_SERVICE_PROVIDERS: Provides the master list of service providers (SP_ID). The filter is meaningless without a valid provider.
• XNP_NUMBER_RANGES: Provides the master definition of the telephone number ranges (NUMBER_RANGE_ID) that can be ported. The filter applies to these predefined ranges.

This table is likely referenced by various PL/SQL packages within the XNP module responsible for orchestrating the subscription broadcast and notification workflows. Its data directly influences the behavior of these core portability processes.