Search Results get_timezone_gmt_deviation

Overview

HZ_TIMEZONE_PUB is a public PL/SQL API package owned by the APPS schema in Oracle E-Business Suite, classified as a PUB (public) interface in the ETRM repository for both release 12.1.1 and 12.2.2. Its business purpose is to centralize timezone resolution and date/time conversion logic used across the Trading Community Model and related application modules. Rather than hard-coding timezone identifiers or computing GMT offsets locally, application code calls this package to translate postal addresses, telephone numbers, and geographic descriptors into canonical timezone identifiers stored in the HZ and FND timezone tables. The package additionally exposes utility procedures for converting and formatting date-time values, deriving daylight-saving boundary information, and retrieving a caller's primary zone. Because timezone data drives scheduling, contact validation, and the display of local times on customer, supplier, and party records, HZ_TIMEZONE_PUB acts as a reusable infrastructure layer. The source header identifies the original author and creation date (AWU, 19-AUG-2003) and declares the package with AUTHID CURRENT_USER, meaning that privilege resolution occurs under the invoking schema rather than as definer rights.

Key Procedures and Functions

The documented interface exposes ten procedures and functions. GET_TIMEZONE_ID resolves a timezone identifier from a combination of postal code, city, state, and country inputs, returning the identifier along with the standard API return status, message count, and message data outputs. GET_PHONE_TIMEZONE_ID derives a timezone from telephony attributes, accepting a phone country code, area code, and phone prefix; the source documentation notes that the prefix parameter is reserved for future use and carries no logic, and that the country code is required only when two countries share the same phone country code and no area code is supplied. GET_TIMEZONE_GMT_DEVIATION returns the offset from GMT for a supplied timezone identifier and date, allowing callers to account for date-dependent daylight-saving shifts. CONVERT_DATETIME transforms a date-time value between timezones. GET_TIME and GET_TIME_AND_CODE return formatted time values, with the latter also returning an associated code. GET_PRIMARY_ZONE returns the primary timezone associated with the supplied context, and is the object most frequently targeted by searches such as "get_primary_zone". GET_TIMEZONE_SHORT_CODE returns the abbreviated or short-code representation of a timezone. GET_BEGIN_END_DST_DAY_TIME returns the begin and end day and time boundaries for daylight saving. GET_DATE_FROM_W_AND_D reconstructs a date from a supplied week and day combination. The package reports status through the uniform x_return_status, x_msg_count, and x_msg_data output pattern, and API callers are expected to invoke FND_MSG_PUB to retrieve buffered messages when the message count exceeds one.

Tables Accessed

The package reads and writes the following objects through APPS synonyms. FND_TIMEZONES_B holds the base timezone definitions, including GMT offsets. HZ_TIMEZONES stores the Trading Community timezone reference data used for identifier and short-code resolution. HZ_TIMEZONE_MAPPING provides the mapping rules that associate locations and phone attributes with timezone identifiers, underpinning GET_TIMEZONE_ID and GET_PHONE_TIMEZONE_ID. HZ_PHONE_AREA_CODES and HZ_PHONE_COUNTRY_CODES supply the telephony lookup data used to resolve area codes and country codes during phone-based timezone derivation.

Usage Notes

HZ_TIMEZONE_PUB is a low-level utility API rather than a business object API, and it is referenced by 51 other packages, indicating broad internal reuse. Typical invocations occur from PL/SQL form logic, concurrent programs, and custom extensions that need to populate or validate timezone fields on party, customer, or contact records, or that must convert stored UTC timestamps into local display values. Standard practice requires initializing the message list where the API supports it, checking x_return_status for success or error, and reading x_msg_data or querying the message stack on failure. Because the package is declared AUTHID CURRENT_USER, calling schemas must hold appropriate execute privileges and access to the underlying timezone tables. Implementers should treat the timezone reference tables as seeded data and avoid direct DML against them; all resolution should route through this public API to preserve consistency with Oracle's internal timezone handling.