Results for “sp_id”

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

Overview

XNP_SP_ADAPTERS is a transactional configuration table in the XNP - Number Portability module of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. It stores the mapping of a service provider to an external system, effectively defining which adapter (external interface endpoint) should be used when communicating on behalf of a given service provider. In number portability operations, service providers must exchange messages with external systems such as number portability clearinghouses, local number portability administrations, and third-party gateways; XNP_SP_ADAPTERS governs that routing relationship.

The table resides in the XNP schema and is marked VALID. Its heuristic Data Vault classification, mined from the foreign-key structure, is link. This is a modeling suggestion: the table behaves as a link (or association) entity resolving a many-to-many style relationship between service providers and external systems, rather than as a hub of independent business entities or a descriptive satellite. Practitioners designing a Data Vault or reporting layer around XNP should treat it accordingly, carrying the two foreign keys as the link's core natural key.

Key Information Stored

The documented physical schema contains 12 columns. The most significant are:

• SP_ADAPTER_ID — the surrogate primary key, enforced by XNP_SP_ADAPTERS_PK. It uniquely identifies each service provider to external system mapping row and is the column used by any dependent child tables or APIs.
• SP_ID — foreign key to XNP_SERVICE_PROVIDERS, identifying the service provider whose traffic is being routed.
• FE_ID — foreign key to XDP_FES, identifying the external system or front-end endpoint (adapter target) assigned to that service provider.
• PREFERRED_FLAG — indicates whether this mapping is the preferred adapter for the service provider when more than one candidate exists.
• SEQUENCE — an ordering value used to prioritize among multiple adapters configured for the same service provider.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-org / data-security partitioning of the mapping rows.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle EBS WHO columns capturing audit and concurrency information.
• ZD_EDITION_NAME — editioning column introduced for the 12.2 online patching (Edition-Based Redefinition) architecture, absent in 12.1.1.

Two unique indexes document the business-key candidates. XNP_SP_ADAPTERS_U1 covers (SP_ADAPTER_ID, ZD_EDITION_NAME), preserving uniqueness of the primary key within an edition. XNP_SP_ADAPTERS_U2 covers (FE_ID, SP_ID, ZD_EDITION_NAME), which is the true business rule: a given service provider may be mapped to a given external system only once per edition. The user-searched index name XNP_SP_ADAPTERS_UK1 corresponds to the unique business-key constraint on (FE_ID, SP_ID) in the 12.1.1 naming convention.

Common Use Cases and Queries

The table is consulted at runtime by number portability routines that must resolve the correct external endpoint for an outbound or inbound message. Typical operational and reporting scenarios include:

• Resolving the adapter for a service provider: SELECT fe_id, preferred_flag FROM xnp_sp_adapters WHERE sp_id = :p_sp_id ORDER BY sequence;
• Listing all external systems mapped to a provider, with preference and sequencing, to audit routing configuration.
• Finding providers that share an external system: SELECT sp_id FROM xnp_sp_adapters WHERE fe_id = :p_fe_id;
• Diagnosing duplicate-mapping errors, where a U2/UK1 violation signals an attempt to map the same FE_ID and SP_ID combination twice.
• Reporting on configuration changes within a date range using CREATION_DATE and LAST_UPDATE_DATE, joined to FND_USER via CREATED_BY / LAST_UPDATED_BY.

Because the table is low-volume configuration data, queries are inexpensive and are usually embedded in PL/SQL packages rather than run ad hoc.

Related Objects

• XNP_SERVICE_PROVIDERS — parent of SP_ID; join xnp_sp_adapters.sp_id = xnp_service_providers.sp_id to retrieve provider names and attributes.
• XDP_FES — parent of FE_ID; join xnp_sp_adapters.fe_id = xdp_fes.fe_id to resolve the external system definition.
• FND_SECURITY_GROUPS — parent of SECURITY_GROUP_ID, controlling row visibility under data security.
• XNP_SP_ADAPTERS_PK / XNP_SP_ADAPTERS_U1 / XNP_SP_ADAPTERS_U2 — the supporting unique indexes and constraints that enforce the primary and business keys, including the searched XNP_SP_ADAPTERS_UK1.
• XNP Number Portability concurrent programs and PL/SQL APIs — the runtime consumers that select from this table to determine message routing.
• FND_USER — joined indirectly through CREATED_BY and LAST_UPDATED_BY for audit reporting.