Search Results gmt_deviation_hours

Overview

HZ_TIMEZONES_VL is a translated (VL) view owned by the APPS schema in Oracle E-Business Suite, classified under the AR – Receivables product family. It exposes time zone reference data maintained by the Trading Community Architecture (TCA) foundation, presenting the identifier, descriptive attributes, and daylight saving time rules for each time zone recognized by the application. The "_VL" suffix denotes that the view joins a base table to its translation table and filters the translated rows by the session language, so each query returns a single, language-appropriate name and description per time zone.

Within Oracle EBS 12.1.1 and 12.2.2 the object is reported as VALID and serves primarily as a reporting and integration surface. Receivables, Order Management, and other modules that need to display or validate customer, site, or party time zone information read through this view rather than the underlying tables. Because it presents a localized, read-only projection of TCA reference data, it is well suited to concurrent programs, BI Publisher data models, and inbound/outbound interface lookups.

Underlying Base Objects

The view text is a two-table join:

SELECT TZ.TIMEZONE_ID, TZ_TL.NAME, TZ_TL.DESCRIPTION, TZ.GLOBAL_TIMEZONE_NAME,
       TZ.GMT_DEVIATION_HOURS, TZ.DAYLIGHT_SAVINGS_TIME_FLAG, ...
FROM   HZ_TIMEZONES TZ, HZ_TIMEZONES_TL TZ_TL
WHERE  TZ.TIMEZONE_ID = TZ_TL.TIMEZONE_ID
AND    TZ_TL.LANGUAGE = USERENV('LANG')

HZ_TIMEZONES is the non-translated base table holding the numeric and rule-based attributes of each time zone, including GMT_DEVIATION_HOURS and the daylight saving parameters. HZ_TIMEZONES_TL is its translation table, keyed by TIMEZONE_ID and LANGUAGE, and carries the translatable NAME and DESCRIPTION. In the documented ETRM metadata both are referenced as synonyms, resolving through the APPS synonym layer to the TCA base objects.

The language predicate USERENV('LANG') restricts output to the language of the current session, which is the defining characteristic of a VL view. If no translated row exists for the session language, the time zone will not be returned; consumers requiring an unfiltered or fallback-language listing must query the base tables directly.

Key Columns

• TIMEZONE_ID – Primary identifier for the time zone; the join key between the base and translation tables and the value typically stored on TCA party, location, and account records.
• NAME – Translated display name of the time zone, sourced from HZ_TIMEZONES_TL.
• DESCRIPTION – Translated descriptive text for the time zone, also from HZ_TIMEZONES_TL.
• GLOBAL_TIMEZONE_NAME – Language-independent name of the time zone, drawn from the base table.
• GMT_DEVIATION_HOURS – The offset, in hours, of the time zone from Greenwich Mean Time. This is the column most frequently sought when users search for "gmt_deviation_hours," and it is the authoritative numeric offset used in scheduling, date arithmetic, and interface conversions.
• DAYLIGHT_SAVINGS_TIME_FLAG – Indicates whether the time zone observes daylight saving time.
• BEGIN_DST_MONTH, BEGIN_DST_DAY, BEGIN_DST_WEEK_OF_MONTH, BEGIN_DST_DAY_OF_WEEK, BEGIN_DST_HOUR – The rule set describing when daylight saving time begins.
• END_DST_MONTH, END_DST_DAY, END_DST_WEEK_OF_MONTH, END_DST_DAY_OF_WEEK, END_DST_HOUR – The corresponding rule set for when daylight saving time ends.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN – Standard audit columns carried from the base table.

Common Use Cases and Queries

Typical scenarios include validating a time zone identifier entered on a customer or site record, populating a list of values, rendering localized time zone names on a report, and retrieving the GMT offset for date/time conversion logic.

Listing all time zones with their offsets:

SELECT timezone_id, name, global_timezone_name, gmt_deviation_hours
FROM   hz_timezones_vl
ORDER BY name;

Resolving the offset for a specific time zone:

SELECT name, gmt_deviation_hours, daylight_savings_time_flag
FROM   hz_timezones_vl
WHERE  timezone_id = :p_timezone_id;

Retrieving daylight saving rules for zones that observe DST:

SELECT timezone_id, name, gmt_deviation_hours,
       begin_dst_month, begin_dst_day_of_week, begin_dst_hour,
       end_dst_month, end_dst_day_of_week, end_dst_hour
FROM   hz_timezones_vl
WHERE  daylight_savings_time_flag = 'Y';

Because the view enforces USERENV('LANG'), these queries return names and descriptions in the language of the executing session. Reports and interfaces that must display a fixed language should set the session language explicitly, while processes requiring every time zone regardless of translation coverage should fall back to HZ_TIMEZONES and HZ_TIMEZONES_TL.