Results for “xnp_sp_contacts”

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

Overview

XNP_SP_CONTACTS is a transactional table in the XNP (Number Portability) product module of Oracle E-Business Suite, owned by the XNP schema. It stores contact information associated with a service provider, capturing the individuals, roles, and communication channels used to reach each provider participating in number portability operations. The table is VALID in both EBS 12.1.1 and 12.2.2 and contains 24 documented columns.

From a dimensional modeling perspective, the mined Data Vault classification for XNP_SP_CONTACTS is satellite-leaning. This heuristic is derived from its foreign key topology: the table references parent entities rather than being referenced by independent hubs, and it carries descriptive attributes (address, phone, email, role) that change over time relative to a business key. In practice, this positions XNP_SP_CONTACTS as a descriptive detail store hanging off the service provider entity, ideal for tracking contact attributes alongside their effective historical state.

Key Information Stored

The table's surrogate primary key is SP_CONTACT_ID, enforced through the XNP_SP_CONTACTS_PK constraint. Two unique indexes define business-key candidates. XNP_SP_CONTACTS_U1 covers (SP_CONTACT_ID, ZD_EDITION_NAME), and XNP_SP_CONTACTS_U2 covers (BUSINESS_ROLE, SP_ID, NAME, ZD_EDITION_NAME) — indicating that a contact is uniquely identified within a service provider by the combination of role, name, and edition.

The most significant columns include:

• SP_CONTACT_ID — surrogate primary key uniquely identifying each contact record.
• SP_ID — foreign key to XNP_SERVICE_PROVIDERS, identifying the owning service provider.
• BUSINESS_ROLE — the functional role of the contact (e.g., technical, administrative) within the provider.
• NAME — the contact person's name.
• ADDRESS_LINE1 / ADDRESS_LINE2 / CITY / STATE / ZIP_CODE / COUNTRY — the postal address of the contact.
• PHONE / MOBILE / FAX / PAGER / PAGER_PIN — multiple telephony channels for reaching the contact.
• EMAIL / INTERNET_ADDRESS — electronic contact endpoints.
• CREATED_BY / CREATION_DATE / LAST_UPDATED_BY / LAST_UPDATE_DATE / LAST_UPDATE_LOGIN — standard EBS audit columns.
• SECURITY_GROUP_ID — the security group controlling row-level access, referencing FND_SECURITY_GROUPS.
• ZD_EDITION_NAME — the edition identifier supporting multi-edition (ZD) data tracking.

Common Use Cases and Queries

Typical reporting and operational scenarios include retrieving all contacts for a provider, filtering by business role, and joining contact details to portability requests for notification and troubleshooting. A representative query listing contacts by role is:

• SELECT c.NAME, c.BUSINESS_ROLE, c.PHONE, c.EMAIL FROM XNP.XNP_SP_CONTACTS c WHERE c.SP_ID = :p_sp_id ORDER BY c.BUSINESS_ROLE, c.NAME;
• SELECT s.SP_NAME, c.NAME, c.EMAIL FROM XNP.XNP_SP_CONTACTS c JOIN XNP.XNP_SERVICE_PROVIDERS s ON s.SP_ID = c.SP_ID WHERE c.BUSINESS_ROLE = 'TECHNICAL';

Additional use cases cover data-quality audits of missing email or phone values, security-group-scoped extracts honoring SECURITY_GROUP_ID, and edition-aware comparisons using ZD_EDITION_NAME.

Related Objects

• XNP_SERVICE_PROVIDERS — parent table joined via XNP_SP_CONTACTS.SP_ID.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for row-level security.
• XNP_SP_CONTACTS_PK — primary key constraint on SP_CONTACT_ID.
• XNP_SP_CONTACTS_U1 / XNP_SP_CONTACTS_U2 — unique indexes defining business keys.
• XNP_SP_CONTACTS_UK1 — unique key on (BUSINESS_ROLE, SP_ID, NAME).

These relationships make XNP_SP_CONTACTS a satellite of the service provider entity, central to number portability contact management.