Results for “geo_area_id”

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

Overview

XNP_SERVICE_AREAS is a transactional configuration table within the Oracle E-Business Suite Number Portability (XNP) module. It records the geographic service areas in which each service provider is authorized or registered to operate. In telecommunications number portability operations, a service provider's coverage footprint must be explicitly defined so that porting requests can be validated, routed, and rated against the correct geographic jurisdiction. This table supplies that mapping.

The table resides in the XNP schema and is classified as VALID in the ETRM 12.2.2 data dictionary. Its documented physical schema consists of ten columns, and it is referenced by two foreign keys and two unique indexes. Under the heuristic Data Vault classification mined from its foreign key topology, XNP_SERVICE_AREAS models as a link table — it resolves a many-to-many relationship between service providers and geographic areas rather than acting as a standalone hub or a descriptive satellite. This classification is a modeling suggestion derived from the FK structure, not a prescribed physical design.

Key Information Stored

The table's columns are organized around identifying a single authorized provider-to-area association and tracking its audit history.

• SERVICE_AREA_ID — The surrogate primary key, enforced through the XNP_SERVICE_AREAS_PK constraint. It uniquely identifies each service-area assignment row and is the column most commonly used in joins and lookups.
• SP_ID — The service provider identifier, a foreign key to XNP_SERVICE_PROVIDERS. This is a business-key candidate, since the first unique index (XNP_SERVICE_AREAS_UK1) is defined on SP_ID and GEO_AREA_ID.
• GEO_AREA_ID — The geographic area identifier, a foreign key to XNP_GEO_AREAS_B. Together with SP_ID, it forms the natural business key of the relationship.
• ZD_EDITION_NAME — The editioning column, present because the table participates in Oracle EBS 12.2 online patching. Both unique indexes (XNP_SERVICE_AREAS_U1 on SERVICE_AREA_ID and ZD_EDITION_NAME; XNP_SERVICE_AREAS_U2 on SP_ID, GEO_AREA_ID, and ZD_EDITION_NAME) include this column, confirming its role in edition-scoped uniqueness.
• SECURITY_GROUP_ID — A foreign key to FND_SECURITY_GROUPS, enforcing multi-org or data-security partitioning of the rows.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — The standard Oracle EBS WHO columns auditing row creation and modification.

Common Use Cases and Queries

Typical usage includes validating whether a provider serves a given geography before accepting a porting request, producing coverage reports per provider, and reconciling provider footprints against geographic reference data.

A common join pattern resolves provider names against their declared service areas:

• Join XNP_SERVICE_AREAS to XNP_SERVICE_PROVIDERS on SP_ID = SP_ID to list all providers and their covered areas.
• Join XNP_SERVICE_AREAS to XNP_GEO_AREAS_B on GEO_AREA_ID = GEO_AREA_ID to report coverage by geographic area name.
• Filter by SECURITY_GROUP_ID to restrict results to the operating unit in scope.

A representative query is SELECT s.SP_ID, g.GEO_AREA_ID FROM XNP_SERVICE_AREAS s, XNP_GEO_AREAS_B g WHERE s.GEO_AREA_ID = g.GEO_AREA_ID AND s.SP_ID = :provider_id;. Because the table supports editioning, queries against the runtime edition should rely on the editioning view rather than the base table.

Related Objects

• XNP_SERVICE_PROVIDERS — referenced through XNP_SERVICE_AREAS.SP_ID; the parent provider master.
• XNP_GEO_AREAS_B — referenced through XNP_SERVICE_AREAS.GEO_AREA_ID; the geographic area master.
• FND_SECURITY_GROUPS — referenced through XNP_SERVICE_AREAS.SECURITY_GROUP_ID; governs row-level data security.
• XNP_SERVICE_AREAS_PK — the primary key constraint on SERVICE_AREA_ID.
• XNP_SERVICE_AREAS_UK1 / _U1 / _U2 — the unique indexes enforcing business-key uniqueness, including the editioning column.