Search Results convert_date_fnd

Overview

OTA_TIMEZONE_UTIL is a utility package in the Oracle E-Business Suite Applications (APPS) schema that centralizes time zone conversion and time zone lookup logic for the Oracle Time and Labor (OTA) and Oracle Learning Management modules. Its primary business function is to reconcile date and time values captured in one time zone with the corresponding value in another, and to resolve the time zone context associated with the client, the server, an event, or a learning offering. Because scheduling, resource booking, and event delivery in these modules frequently span multiple geographic regions, consistent time zone handling is essential to prevent misaligned bookings, incorrect attendance records, and reporting discrepancies.

The package is classified as a UTIL component, meaning it is not a business-facing API in its own right but a supporting library invoked by other application logic. ETRM documentation records it as referenced by 17 other packages, confirming its role as a shared dependency within the OTA schema. The header comment, dated January 2012, reflects the R12 code line.

Key Procedures and Functions

The documented interface comprises fourteen procedures and functions. GET_TIMEZONE_ID is the entry point most commonly searched by developers; it accepts a time zone code and returns the corresponding numeric upgrade time zone identifier from FND_TIMEZONES_B. This identifier is the join key used throughout the FND and HZ time zone data model.

• GET_TIMEZONE_ID — resolves a time zone code to its numeric identifier.
• GET_SERVER_TIMEZONE_CODE — returns the time zone code configured for the application server.
• GET_CLIENT_TIMEZONE_VALS — returns the code and name of the client time zone, derived from the CLIENT_TIMEZONE_ID profile option.
• GET_EVENT_TIMEZONE_VALS — returns the time zone code and name associated with a given event.
• GET_TIMEZONE_NAME — returns the display name for a time zone code.
• CONVERT_DATE and CONVERT_DATE_FND — convert a date from a source time zone to a destination time zone.
• CONVERT_DATE_HZ — performs the same conversion through the HZ (Trading Community) time zone path.
• CONVERT_DATEDT_TIME — converts a date-time value between time zones.
• GET_DATEDT — returns the current date-time in the applicable time zone.
• GET_CLASS_DATEDT — returns the current date-time for a class or offering context.
• GET_RESOURCE_BOOKINGDT — returns the date-time relevant to a resource booking.
• GET_DATEDT_TIME and GET_DATE_TIME — helper functions returning date-time components in the appropriate time zone.

Internally, conversion is delegated to FND_TIMEZONES_PVT.ADJUST_DATETIME. The package source shows CONVERT_DATE_HZ retaining the legacy HZ_TIMEZONE_PUB.Convert_DateTime path in commented form, with the FND call active in R12.

Tables Accessed

The package reads from both the FND and HZ time zone tables and from OTA scheduling tables.

• FND_TIMEZONES_B — base table holding time zone codes and UPGRADE_TZ_ID; queried by GET_TIMEZONE_ID.
• FND_TIMEZONES_TL — translation table supplying time zone names.
• FND_TIMEZONES_VL — view joined to resolve code and name for the client time zone.
• HZ_TIMEZONES — Trading Community time zone identifiers used by the HZ conversion path.
• FND_LANGUAGES — language context for translated name resolution.
• OTA_EVENTS, OTA_OFFERINGS, OTA_CATEGORY_USAGES — event and offering context for event and class date-time lookups.
• OTA_RESOURCE_BOOKINGS — resource booking records supplying booking date-times.

Access is read-only through APPS synonyms; the package does not write to these tables.

Usage Notes

OTA_TIMEZONE_UTIL is typically invoked from Oracle Forms, OTA concurrent programs, and custom PL/SQL rather than directly by end users. Any form or report that displays event, offering, or resource booking times should call the appropriate GET_* function to obtain a correctly localized value, or CONVERT_DATE / CONVERT_DATE_HZ to shift a stored date between zones before display or comparison. The CLIENT_TIMEZONE_ID profile option governs client-side resolution, so custom code relying on GET_CLIENT_TIMEZONE_VALS must ensure the profile is set. When writing new code, prefer the FND-based conversion functions over the HZ variants, consistent with the R12 implementation shown in the package body.