Results for “xnp_sp_contacts_u2”

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

Overview

XNP.XNP_SP_CONTACTS is a seed-data table in the Oracle E-Business Suite 12.1.1 and 12.2.2 data model, owned by the XNP schema. It holds contact information for service providers registered in the EBS environment — typically the personnel, business contacts, and administrative points of contact maintained against each service provider record. The table resides in the APPS_TS_SEED tablespace with PCT Free 10, consistent with its role as reference and configuration data rather than high-volume transactional data. Its status is documented as VALID in ETRM, and it is registered under FND Design Data as XNP.XNP_SP_CONTACTS.

From a Data Vault modeling perspective, the FK structure suggests a satellite-leaning classification. The table's central business key (SP_ID) references XNP_SERVICE_PROVIDERS, and the contact attributes are descriptive in nature, which aligns with satellite semantics attaching descriptive context to a parent service-provider entity. Some secondary relationships, such as the FK to FND_SECURITY_GROUPS, indicate an auxiliary role in hosted or multi-tenant deployments rather than a hub-like identity table.

Key Information Stored

The primary surrogate key is SP_CONTACT_ID (NUMBER), enforced by the primary key XNP_SP_CONTACTS_PK and by the unique index XNP_SP_CONTACTS_U1 (SP_CONTACT_ID, ZD_EDITION_NAME). The foreign key SP_ID links each contact row to the parent service provider in XNP_SERVICE_PROVIDERS. The business-key candidate unique index XNP_SP_CONTACTS_U2 covers BUSINESS_ROLE, SP_ID, NAME, and ZD_EDITION_NAME — meaning a given service provider should have at most one contact per role per name (per edition).

The most important descriptive columns include:

• BUSINESS_ROLE — the role or function the contact plays for the service 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 — availability and telecom channels.
• EMAIL and INTERNET_ADDRESS — electronic contact information.
• SECURITY_GROUP_ID — used in hosted environments and foreign-keyed to FND_SECURITY_GROUPS.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.
• ZD_EDITION_NAME — editioning column participating in both unique indexes.

The nonunique index XNP_SP_CONTACTS_N1 on SP_ID supports retrieval of all contacts for a given service provider.

Common Use Cases and Queries

Typical usage centers on joining contacts back to the service provider to produce role-based contact lists, notifications, and directory reports. A representative pattern:

• List all contacts for a service provider: SELECT c.BUSINESS_ROLE, c.NAME, c.PHONE, c.EMAIL FROM XNP.XNP_SP_CONTACTS c WHERE c.SP_ID = :p_sp_id ORDER BY c.BUSINESS_ROLE;
• Find the primary role contact: filter by BUSINESS_ROLE and NAME using the U2 unique key columns, which are index-covered.
• Validate uniqueness before inserts by testing (BUSINESS_ROLE, SP_ID, NAME) — the U2 index will raise a constraint violation otherwise.
• Reporting joins to XNP_SERVICE_PROVIDERS to surface provider names alongside contact details.
• Audit queries filtering on LAST_UPDATE_DATE or CREATED_BY to detect recent maintenance.

Because the table is a seed table, queries are typically read-only and relatively low cardinality.

Related Objects

• XNP.XNP_SERVICE_PROVIDERS — parent entity; join on XNP_SP_CONTACTS.SP_ID = XNP_SERVICE_PROVIDERS.SP_ID.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for hosted environment scoping.
• XNP_SP_CONTACTS_PK — primary key constraint on SP_CONTACT_ID.
• XNP_SP_CONTACTS_U1 — unique index on (SP_CONTACT_ID, ZD_EDITION_NAME).
• XNP_SP_CONTACTS_U2 — unique index on (BUSINESS_ROLE, SP_ID, NAME, ZD_EDITION_NAME).
• XNP_SP_CONTACTS_N1 — nonunique index on SP_ID.
• APPS_TS_SEED tablespace — storage container shared with other reference data.