Search Results hz_phone_area_codes




Overview
Explore in AI Chat

Overview

HZ_PHONE_AREA_CODES is a reference (lookup) table in the Oracle E-Business Suite Receivables (AR) schema that stores telephone number area codes organized by country and territory. It is part of the Oracle Trading Community Architecture (TCA) model, which underpins customer, contact, and location data across EBS 12.1.1 and 12.2.2. The table provides the master list of dialing prefixes used to validate and normalize phone numbers captured against parties, sites, contacts, and locations. Because TCA is shared across multiple modules — Order Management, Service, CRM, and Receivables — this table serves as a common validation source for telephone data entry throughout the application.

From a Data Vault modeling perspective, the mined FK structure suggests classifying HZ_PHONE_AREA_CODES as a standalone table. It carries descriptive reference attributes that are relatively static and would be modeled as a reference or lookup construct rather than a transactional hub, link, or satellite, since it does not participate in the core party relationship graph.

Key Information Stored

The table contains 12 documented columns in the 12.2.2 physical schema. The most significant are:

  • TERRITORY_CODE — The country or territory to which the area code belongs. This is the primary grouping key and the leading column of the unique index.
  • PHONE_COUNTRY_CODE — The international dialing country code associated with the territory.
  • AREA_CODE — The actual area or city dialing prefix being defined.
  • DESCRIPTION — User-facing description of the area code, typically the geographic region or city name.
  • TIMEZONE_ID — Foreign key referencing HZ_TIMEZONES, linking the area code to a default time zone.
  • ZD_EDITION_NAME — The editioning column introduced in the 12.2 online patching architecture, which enables edition-based redefinition of the reference data.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the TCA APIs to detect concurrent updates.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns tracking record provenance and modification history.

The business-key candidate is the unique index HZ_PHONE_AREA_CODES_U1 (TERRITORY_CODE, AREA_CODE, ZD_EDITION_NAME), which enforces that an area code is unique within a territory and edition. No separate surrogate primary key is documented; the composite unique key functions as the natural identifier.

Common Use Cases and Queries

Typical scenarios include validating phone number entry, deriving the time zone for a contact or site based on its phone prefix, and building country-level dialing reports. A common query to resolve an area code to its time zone is:

  • SELECT p.area_code, p.description, t.timezone_code FROM hz_phone_area_codes p, hz_timezones t WHERE p.timezone_id = t.timezone_id AND p.territory_code = :territory AND p.area_code = :area_code;
  • Reporting all area codes defined for a given country, ordered by area code, for data-entry validation lists.
  • Joining to HZ_CONTACT_POINTS or HZ_LOCATIONS to enrich phone data with geographic descriptions extracted from DESCRIPTION.
  • Reconciliation queries identifying phone numbers whose area code is absent from this reference table, indicating a data-quality gap.

Because the table is edition-enabled, queries in a 12.2 environment should account for ZD_EDITION_NAME when comparing against the runtime edition.

Related Objects

  • HZ_TIMEZONES — Referenced via HZ_PHONE_AREA_CODES.TIMEZONE_ID; supplies the time zone definition for each area code.
  • HZ_CONTACT_POINTS — Stores actual phone numbers for parties and contacts, validated against area code entries.
  • HZ_LOCATIONS — Location records whose phone attributes rely on area code validation.
  • HZ_PARTIES — Party records indirectly associated with area codes through contact points.
  • HZ_PHONE_COUNTRY_CODES — Companion reference table for country-level phone codes.
  • HZ_GEOGRAPHY_IDENTIFIERS / HZ_TERRITORIES — Provide territory definitions referenced by TERRITORY_CODE.
  • AR.HZ_PHONE_AREA_CODES_U1 — The unique index enforcing the business key constraint.

These objects together form the TCA telephone data validation layer used consistently across Oracle EBS modules.