Search Results hz_phone_area_codes_n1

Overview

The AR.HZ_PHONE_AREA_CODES table is a reference (seed) table within the Oracle E-Business Suite Trading Community Architecture (TCA) model, owned by the AR schema. It stores the telephone area codes associated with a particular country or territory, enabling validation, formatting, and timezone resolution of telephone numbers captured against parties, contacts, and locations. The table resides in the APPS_TS_SEED tablespace, which is consistent with its role as seeded reference data rather than high-volume transactional data.

From a Data Vault modeling perspective, the mined relationship structure classifies this object heuristically as standalone. It does not reference other tables except through the optional TIMEZONE_ID foreign key to HZ_TIMEZONES, and it is not itself referenced by other objects. This suggests treating it as a reference or hub-like dimension for area code and territory combinations, with a dependent relationship to timezone data.

Key Information Stored

The table contains twelve documented columns. The most significant are:

• TERRITORY_CODE — The territory or country to which the area code belongs. This column participates in the unique business key.
• AREA_CODE — The telephone area code itself. It also participates in the unique business key.
• PHONE_COUNTRY_CODE — The country prefix used for international telephone numbers.
• DESCRIPTION — A descriptive label for the area code.
• TIMEZONE_ID — A foreign key to HZ_TIMEZONES, allowing the area code to be associated with a geographic timezone.
• OBJECT_VERSION_NUMBER — A version number used for optimistic locking during concurrent updates.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns tracking row creation and modification.

The unique index HZ_PHONE_AREA_CODES_U1 defines the business key as TERRITORY_CODE, AREA_CODE, and ZD_EDITION_NAME. This index is the object referenced in the user's search and is the primary mechanism enforcing uniqueness of an area code within a territory. The metadata does not document a separate surrogate primary key; the uniqueness of a row is therefore governed by this composite unique index. Two non-unique indexes exist for performance: HZ_PHONE_AREA_CODES_N1 on PHONE_COUNTRY_CODE and AREA_CODE, and HZ_PHONE_AREA_CODES_N2 on TIMEZONE_ID.

Common Use Cases and Queries

Typical uses include validating a user-entered area code against a selected territory, deriving the timezone for a phone number, and generating telephone number reports grouped by country or region. The following query retrieves area codes for a given territory:

• SELECT TERRITORY_CODE, PHONE_COUNTRY_CODE, AREA_CODE, DESCRIPTION FROM AR.HZ_PHONE_AREA_CODES WHERE TERRITORY_CODE = :p_territory ORDER BY AREA_CODE;

To resolve the timezone for a specific area code, join to HZ_TIMEZONES:

• SELECT a.AREA_CODE, t.TIMEZONE_NAME FROM AR.HZ_PHONE_AREA_CODES a, HZ_TIMEZONES t WHERE a.TIMEZONE_ID = t.TIMEZONE_ID AND a.TERRITORY_CODE = :p_territory;

A lookup by country prefix is optimized by the N1 index:

• SELECT AREA_CODE, DESCRIPTION FROM AR.HZ_PHONE_AREA_CODES WHERE PHONE_COUNTRY_CODE = :p_prefix;

Related Objects

The documented dependency information identifies one explicit foreign key relationship, with the remaining associations inferred from TCA usage:

• HZ_TIMEZONES — Referenced through HZ_PHONE_AREA_CODES.TIMEZONE_ID, providing timezone details for each area code.
• HZ_PHONES — Stores telephone numbers for parties and contacts; area codes captured here are validated against this reference table.
• HZ_CONTACT_POINTS — The parent contact point entity for phone records, linking phone data to parties.
• HZ_LOCATIONS — Location records that carry telephone numbers resolved against area code data.
• FND_TERRITORIES — Provides the territory definitions corresponding to TERRITORY_CODE.
• FND_USER — Referenced indirectly via CREATED_BY and LAST_UPDATED_BY for audit purposes.

Because the table is standalone with respect to inbound references, these associations are predominantly application-driven rather than enforced by database constraints. The ZD_EDITION_NAME column reflects support for editioned reference data.