Results for “hz_timezone_mapping”

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

Overview

HZ_TIMEZONE_MAPPING is a reference table owned by the AR (Receivables) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is the mapping of address elements to time zones. In practice, the table allows Oracle applications to derive a candidate time zone from geographic attributes of an address, such as postal code, city, state, province, country, or area code, rather than relying solely on an explicitly entered time zone value.

The table is registered in ETRM with status VALID and is classified under the AR - Receivables product group, although its columns follow the HZ (Trading Community / Party) naming conventions used by Oracle's customer and address model. It functions as a lookup or reference structure consumed by address validation, party, and location processing logic.

Under the heuristic Data Vault classification mined from its foreign key structure, HZ_TIMEZONE_MAPPING is described as satellite-leaning. This suggests it is best modeled as a descriptive satellite attached to a time zone hub (HZ_TIMEZONES), carrying the address-element context that qualifies each mapping rather than acting as an independent hub or a pure link between business entities.

Key Information Stored

The table contains 14 documented columns. The most significant are summarized below.

• MAPPING_ID — the surrogate primary key, enforced by the HZ_TIMEZONE_MAPPING_PK constraint. It uniquely identifies each address-to-time-zone mapping row.
• TIMEZONE_ID — the foreign key referencing HZ_TIMEZONES, identifying the time zone to which the address elements resolve.
• COUNTRY — the country component of the address used as a matching criterion.
• STATE — the state component of the address.
• PROVINCE — the province component, supporting regions where province rather than state applies.
• CITY — the city or locality component of the address.
• POSTAL_CODE — the postal code used to narrow the time zone resolution.
• AREA_CODE — a telephone area code that can serve as an additional geographic discriminator.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard Oracle who-columns recording audit and concurrency information.
• ZD_EDITION_NAME — the editioning column introduced for Oracle EBS 12.2 online patching support, enabling edition-based redefinition.

The unique index HZ_TIMEZONE_MAPPING_U1 spans MAPPING_ID and ZD_EDITION_NAME, which serves as the edition-aware business-key candidate. The combination of address elements together with TIMEZONE_ID constitutes the semantic business key used for lookup purposes.

Common Use Cases and Queries

The primary use case is resolving an address to a probable time zone during data entry, import, or integration. Applications query the table by supplying one or more address attributes and retrieving the associated TIMEZONE_ID. A typical pattern joins the mapping to HZ_TIMEZONES to obtain the time zone name:

• SELECT m.mapping_id, t.timezone_code FROM hz_timezone_mapping m, hz_timezones t WHERE m.timezone_id = t.timezone_id AND m.country = :country AND m.postal_code = :postal_code;
• Filtering on CITY, STATE, or PROVINCE when postal code data is incomplete or ambiguous.
• Using AREA_CODE as a fallback discriminator for regions where phone numbering plans align closely with geography.
• Reporting on coverage: identifying countries or postal ranges that lack a mapping entry, which may indicate gaps requiring reference data maintenance.
• Extracting rows for a specific edition by filtering on ZD_EDITION_NAME in 12.2 environments.

Related Objects

• HZ_TIMEZONES — the parent reference table; joined via HZ_TIMEZONE_MAPPING.TIMEZONE_ID = HZ_TIMEZONES.TIMEZONE_ID. This is the only documented foreign key relationship.
• HZ_LOCATIONS — address records that rely on time zone derivation logic.
• HZ_PARTY_SITES — party site addresses that frequently require resolved time zone values.
• HZ_LOCATION_PROFILES — location profile structures governing address handling.
• FND_TERRITORIES — territory definitions commonly joined on the COUNTRY column.
• HZ_GEOGRAPHIES and related geography tables — providing hierarchical address validation context.
• AR customer and location APIs — interfaces that consume time zone resolution during address creation and update.