Results for “object_reference”

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

Overview

XNP_SP_FILTERS is a table in the XNP schema, the Oracle EBS module that supports Number Portability and subscription broadcast processing. The table stores the number ranges for which a given service provider does not want to be informed of subscription broadcasts. In practice, it functions as an opt-out or suppression registry: when subscription broadcast events are generated and routed to service providers, the filter rows defined here instruct the broadcast engine to skip notification for the referenced service provider and number range combinations.

Because the table is populated with 12 documented columns and carries two foreign keys that reference independently existing master entities (service providers and number ranges) plus descriptive and audit attributes, the heuristic Data Vault classification supplied in the metadata is link. Under that modeling suggestion, XNP_SP_FILTERS should be treated as an associative entity that resolves a many-to-many relationship between XNP_SERVICE_PROVIDERS and XNP_NUMBER_RANGES, with the suppression flag effectively expressed by the existence of the row itself rather than by a separate status column. This interpretation is consistent with the unique index on (SP_ID, NUMBER_RANGE_ID), which prevents duplicate filter entries for the same provider/range pairing.

Key Information Stored

The physical table contains 12 columns in the 12.2.2 ETRM release, organized around a surrogate primary key and several business-key candidates.

• SP_FILTER_ID — surrogate primary key, enforced by XNP_SP_FILTERS_PK and included in the XNP_SP_FILTERS_U1 unique index. It uniquely identifies each filter row and is the correct column for foreign-key references from any dependent child records.
• OBJECT_REFERENCE — business-key candidate carrying an external or descriptive reference to the filtered object, enforced by unique index XNP_SP_FILTERS_U2 (OBJECT_REFERENCE alone, and OBJECT_REFERENCE with ZD_EDITION_NAME in the 12.2.2 schema). It supports traceability of the filter entry back to its originating object.
• SP_ID — identifier of the service provider whose notifications are being suppressed; foreign key to XNP_SERVICE_PROVIDERS and part of unique index XNP_SP_FILTERS_U3.
• NUMBER_RANGE_ID — identifier of the number range covered by the filter; foreign key to XNP_NUMBER_RANGES and the second component of XNP_SP_FILTERS_U3.
• EFFECTIVE_DATE — date from which the filter becomes active, supporting date-effective filtering behaviour.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle EBS audit columns populated by the application when the row is inserted or modified.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-organization / security-group data separation consistent with other Oracle EBS tables.
• ZD_EDITION_NAME — editioning column present in the 12.2.2 schema and included in the U1, U2, and U3 unique indexes.

The unique indexes XNP_SP_FILTERS_U1, U2, and U3 identify the business-key candidates, while SP_FILTER_ID is strictly the surrogate key.

Common Use Cases and Queries

The primary operational use case is determining whether a given provider/range combination should receive a broadcast. Application code and custom reports typically query the table to check for the existence of a filter row before dispatching subscription notifications.

• Suppression check: SELECT SP_FILTER_ID FROM XNP.XNP_SP_FILTERS WHERE SP_ID = :p_sp_id AND NUMBER_RANGE_ID = :p_range_id; returns a row when notifications should be suppressed for that pairing.
• Provider filter listing: SELECT f.SP_FILTER_ID, f.OBJECT_REFERENCE, f.NUMBER_RANGE_ID, f.EFFECTIVE_DATE FROM XNP.XNP_SP_FILTERS f WHERE f.SP_ID = :p_sp_id ORDER BY f.NUMBER_RANGE_ID; enumerates all ranges suppressed for a single provider.
• Range coverage reporting: joining to XNP_NUMBER_RANGES to list ranges with no active filter, identifying ranges where broadcasts will always be sent.
• Effectivity filtering: applying EFFECTIVE_DATE <= SYSDATE to include only currently active suppressions, and SECURITY_GROUP_ID predicates in multi-org reporting.

Typical reporting outputs include provider opt-out registers, suppression coverage gaps by number range, and audit reports of filters created or last updated within a period using CREATION_DATE and LAST_UPDATE_DATE.

Related Objects

The following objects are the most significant for joining and dependency analysis based on the documented foreign-key structure.

• XNP_SERVICE_PROVIDERS — referenced by XNP_SP_FILTERS.SP_ID; the master table of service providers who may be suppressed from broadcasts.
• XNP_NUMBER_RANGES — referenced by XNP_SP_FILTERS.NUMBER_RANGE_ID; the master table defining the number ranges targeted by filters.
• FND_SECURITY_GROUPS — referenced by XNP_SP_FILTERS.SECURITY_GROUP_ID; drives security-group partitioning of filter rows.
• XNP_SP_FILTERS_PK / XNP_SP_FILTERS_UK1 / XNP_SP_FILTERS_UK2 — the primary and unique constraints enforcing SP_FILTER_ID, OBJECT_REFERENCE, and SP_ID/NUMBER_RANGE_ID uniqueness.
• XNP subscription broadcast processing logic — the application code that consumes these rows at broadcast time to exclude notifications.

No additional Oracle EBS views or public APIs are documented in this metadata, so integration points should be validated against the deployed XNP schema in each environment.