Results for “hz_timezones_u1”

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

Overview

The AR.HZ_TIMEZONES table is a seed data table in the Oracle E-Business Suite that stores the master list of time zones and their associated daylight-saving time (DST) rules, expressed relative to Greenwich Mean Time. It is owned by the AR schema and is registered as FND Design Data AR.HZ_TIMEZONES with a status of VALID. Because it defines the canonical identity and GMT-relative behavior of every time zone used across the EBS data model, it functions as a foundational reference (lookup) table within the Trading Community Architecture (TCA) and related modules.

The table is stored in the APPS_TS_SEED tablespace, confirming its role as seeded reference data rather than transactional data. Under a Data Vault modeling perspective, the metadata heuristic classifies HZ_TIMEZONES as hub-leaning. This is consistent with the table holding a stable, uniquely identified business entity — the time zone — whose primary key is referenced by numerous dependent tables. In a Data Vault design, TIMEZONE_ID would naturally serve as the hub business key, with descriptive attributes such as GMT deviation and DST rules delivered through an attached satellite.

Key Information Stored

The table contains 22 documented columns. The surrogate primary key is TIMEZONE_ID (NUMBER(15)), which is enforced by the unique index HZ_TIMEZONES_U1 on the same column. No separate business-key column is documented beyond this identifier, so TIMEZONE_ID serves simultaneously as the surrogate key and the effective business-key candidate.

• TIMEZONE_ID — the unique numeric time zone identifier and primary key.
• GLOBAL_TIMEZONE_NAME — the human-readable name of the time zone associated with a specific deviation from GMT; the principal descriptive label.
• GMT_DEVIATION_HOURS — the numeric offset in hours from Greenwich Mean Time (for example, Pacific Standard Time is eight hours behind GMT).
• DAYLIGHT_SAVINGS_TIME_FLAG — indicates whether the zone observes DST (Y for zones using DST, N for those that do not).
• BEGIN_DST_MONTH, BEGIN_DST_DAY, BEGIN_DST_WEEK_OF_MONTH, BEGIN_DST_DAY_OF_WEEK, BEGIN_DST_HOUR — the rule set defining when DST begins in that zone.
• END_DST_MONTH, END_DST_DAY, END_DST_WEEK_OF_MONTH, END_DST_DAY_OF_WEEK, END_DST_HOUR — the rule set defining when DST ends.
• STANDARD_TIME_SHORT_CODE and DAYLIGHT_SAVINGS_SHORT_CODE — abbreviated display codes for the standard and daylight-saving forms of the zone name.
• PRIMARY_ZONE_FLAG — indicates whether the row represents the primary time zone for a location or region.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns recording row creation and last modification.

Common Use Cases and Queries

The primary practical use of HZ_TIMEZONES is resolving a numeric TIMEZONE_ID to a display name and GMT offset when reporting on locations, contacts, tasks, or campaigns. A typical lookup joins it to HZ_LOCATIONS to render the local time zone for a customer or party site:

SELECT l.location_id,
       t.global_timezone_name,
       t.gmt_deviation_hours,
       t.daylight_savings_time_flag
FROM   hz_locations l,
       hz_timezones t
WHERE  l.timezone_id = t.timezone_id;

Another common scenario is generating a reference report of all zones ordered by offset, and filtering to only those that observe DST, which is useful when scheduling tasks or campaign activities:

SELECT timezone_id,
       global_timezone_name,
       gmt_deviation_hours
FROM   hz_timezones
WHERE  daylight_savings_time_flag = 'Y'
ORDER  BY gmt_deviation_hours, global_timezone_name;

Because it is a seed table, validation and reconciliation scripts frequently query it to confirm that every TIMEZONE_ID referenced by transactional tables exists in the master list. Core Application (CS), Marketing (AMS), Tasks (JTF), Service (OKS), and HR (PER) reporting all rely on this table to present times in the user's geographic context.

Related Objects

The table behaves as a reference hub, with many dependent tables carrying a foreign key to HZ_TIMEZONES.TIMEZONE_ID. The most significant relationships include:

• HZ_LOCATIONS.TIMEZONE_ID — the primary association linking a physical location to its local time zone.
• HZ_CONTACT_POINTS.TIMEZONE_ID — resolves time zones for contact point records in TCA.
• HZ_PHONE_AREA_CODES.TIMEZONE_ID and HZ_PHONE_COUNTRY_CODES.TIMEZONE_ID — associate telephone area and country codes with a default time zone.
• CS_INCIDENTS_ALL_B.TIME_ZONE_ID — records the time zone for service incidents and notes.
• JTF_TASKS_B.TIMEZONE_ID and JTF_TASK_AUDITS_B.NEW_TIMEZONE_ID / OLD_TIMEZONE_ID — time zone context for tasks and their audit trail.
• AMS_LIST_HEADERS_ALL.TIMEZONE_ID, AMS_TRIGGERS.TIMEZONE_ID, and AMS_CAMPAIGN_SCHEDULES_B.TIMEZONE_ID — time zone context for marketing lists, triggers, and campaign scheduling.
• MTL_PARAMETERS.TIMEZONE_ID — sets the default time zone for an inventory organization.
• HZ_TIMEZONE_MAPPING.TIMEZONE_ID — maps internal time zone values to the master list.
• OKS_COVERAGE_TIMEZONES.TIMEZONE_ID and PER_EMPDIR_LOCATIONS.TIMEZONE_ID — time zone context for service coverage and employee directory locations.

These dependencies confirm that HZ_TIMEZONES must remain synchronized with any time zone data changes, since an unresolved TIMEZONE_ID would break time-sensitive display and scheduling logic across multiple application modules.