Results for “okx_timezones_v”

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

Overview

OKX_TIMEZONES_V is a reporting and integration view owned by the APPS schema within the OKX – Contracts Integration product family. It exposes a consolidated, translated list of timezone definitions that Oracle E-Business Suite uses to normalize and display date/time values across globally distributed contract, service, and scheduling transactions. The view is registered as VALID and is documented in ETRM for EBS 12.1.1 and 12.2.2.

Its primary role is to reconcile two independent timezone data models that coexist in the EBS data dictionary: the legacy Human Resources / Trading Community (HZ) timezone tables and the upgraded FND timezone tables introduced during the Oracle timezone (DST) upgrade initiative. By joining these models through the UPGRADE_TZ_ID link and the shared TIMEZONE_CODE, the view presents a single row per timezone that carries both the legacy identifier and the current FND timezone name. This makes it a convenient reference for OKX contract integration logic, outbound interfaces, and ad hoc reporting where offsets, daylight saving rules, and human-readable names are all required in one result set.

Underlying Base Objects

The view is defined over four documented base objects, each referenced through a synonym under APPS:

• HZ_TIMEZONES (aliased B) — the legacy timezone master, supplying the primary TIMEZONE_ID and the daylight saving rule attributes.
• HZ_TIMEZONES_TL (aliased TL) — the translation table for the HZ model, joined on TIMEZONE_ID and restricted to LANGUAGE = USERENV('LANG').
• FND_TIMEZONES_B (aliased NEWB) — the upgraded FND timezone base table, joined via NEWB.UPGRADE_TZ_ID = B.TIMEZONE_ID, which is the key mapping between the legacy and current models.
• FND_TIMEZONES_TL (aliased NEWTL) — the FND translation table, joined on TIMEZONE_CODE and LANGUAGE to supply the descriptive NAME and DESCRIPTION values.

The join path requires a match across all four sources, so a HZ timezone appears only when a corresponding upgraded FND timezone and matching translation rows exist in the session language. Consumers should be aware that this is an inner-join view and is not intended as an exhaustive timezone registry.

Key Columns

• TIMEZONE_ID — Legacy HZ identifier for the timezone; the primary correlation key for OKX contract data stored against HZ timezone values.
• GLOBAL_TIMEZONE_NAME — Canonical global timezone name from HZ_TIMEZONES.
• GMT_DEVIATION_HOURS — Standard offset from GMT, expressed in hours. This is the column most commonly referenced in search and reporting, and it reflects standard (non-DST) offset.
• DAYLIGHT_SAVINGS_TIME_FLAG — Indicates whether the timezone observes daylight saving time.
• BEGIN_DST_MONTH / BEGIN_DST_DAY / BEGIN_DST_WEEK_OF_MONTH / BEGIN_DST_DAY_OF_MONTH / BEGIN_DST_HOUR — The rule set describing when daylight saving begins.
• END_DST_MONTH / END_DST_DAY / END_DST_WEEK_OF_MONTH / END_DAY_OF_WEEK / END_DST_HOUR — The corresponding rule set for the end of daylight saving. Note the legacy naming inconsistency: the end-of-week column is exposed as END_DAY_OF_WEEK.
• NAME / DESCRIPTION — Localized display text sourced from FND_TIMEZONES_TL via the current session language.

Common Use Cases and Queries

Typical usages include general timezone lookup, offset analysis for contract scheduling, and providing user-facing pick lists. A standard listing query returns the legacy ID, global name, offset, and localized name:

• SELECT timezone_id, global_timezone_name, gmt_deviation_hours, name FROM apps.okx_timezones_v ORDER BY gmt_deviation_hours;
• SELECT timezone_id, name FROM apps.okx_timezones_v WHERE daylight_savings_time_flag = 'Y';
• SELECT v.name, v.gmt_deviation_hours FROM apps.okx_timezones_v v WHERE v.global_timezone_name = 'Europe/London';
• SELECT timezone_id, name FROM apps.okx_timezones_v WHERE gmt_deviation_hours BETWEEN -5 AND 5;

Because results depend on USERENV('LANG'), report authors should confirm the runtime language of the executing user or concurrent program. When the language has no matching FND_TIMEZONES_TL row, the timezone will not appear in the result set, which can cause apparent gaps in contract-level timezone reporting.