Search Results get_date_time

Overview

APPS.OTA_TIMEZONE_UTIL is a utility PL/SQL package within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 codebase that centralizes time zone resolution, conversion, and lookup logic for the Oracle Learning Management (OTA) and, more broadly, the Oracle iLearning / Training Administration modules. The package is owned by the APPS schema and is classified as a UTIL (utility) API in the ETRM metadata, indicating that it is not a business-entity API with a formal lifecycle but rather a reusable service layer consumed by other packages, forms, and concurrent programs.

The primary business function of OTA_TIMEZONE_UTIL is to insulate callers from the complexities of time zone handling across the OTA schema. Events, offerings, resource bookings, and category usages each carry distinct time zone attributes (an online_flag on category usages, a timezone column on events, and a timezone_code on resource bookings). The package reconciles these attributes with the FND (Foundation) and HZ (Trading Community / Customer) time zone reference tables so that display names, identifiers, and converted date/time values remain consistent regardless of the invoking session's locale.

The metadata lists 14 documented procedures and functions, and the package is referenced by 17 other packages, confirming its role as a shared dependency across the OTA product family. Because it declares AUTHID CURRENT_USER, it executes with the privileges of the calling schema, subject to the standard APPS synonym grants.

Key Procedures and Functions

The documented subprograms fall into three functional groups.

• Time zone lookup and identification. GET_TIMEZONE_ID returns the HZ time zone identifier that corresponds to a supplied time zone code and is explicitly noted in the source comment as being used for 11i compatibility code paths. GET_TIMEZONE_NAME — the function the user searched for — returns the display name of a time zone corresponding to a time zone code, resolving through FND_TIMEZONES_TL for the current language. GET_SERVER_TIMEZONE_CODE returns the time zone code set by the profile option "Server Timezone."
• Contextual time zone retrieval. GET_CLIENT_TIMEZONE_VALS returns both the client time zone code and its name. GET_EVENT_TIMEZONE_VALS returns the time zone code and name associated with a specific event. These output-style procedures populate both the code and the human-readable name in a single call.
• Date and time conversion. CONVERT_DATE converts a date from one time zone to another. Related conversion routines include CONVERT_DATEDT_TIME, CONVERT_DATE_FND, and CONVERT_DATE_HZ, which target the FND and HZ time zone representations respectively. Complementary getter functions — GET_DATEDT, GET_DATEDT_TIME, GET_DATE_TIME, GET_CLASS_DATEDT, and GET_RESOURCE_BOOKINGDT — derive date/time values for classes and resource bookings.

Tables Accessed

The package declares two explicit cursors and, per the ETRM metadata, reads from the following tables through APPS synonyms:

• OTA_EVENTS, OTA_OFFERINGS, OTA_CATEGORY_USAGES — joined in GET_CLASS_INFO and GET_RESOURCE_BOOKING_INFO cursors to resolve the online_flag and time zone of a class or event via its parent offering and delivery-mode category usage.
• OTA_RESOURCE_BOOKINGS — supplies timezone_code for resource booking time calculations.
• FND_TIMEZONES_TL, FND_TIMEZONES_B, FND_LANGUAGES — the base and translated time zone reference tables, used to resolve codes to names in the session language (USERENV('LANG')).
• HZ_TIMEZONES — the Trading Community time zone table, used by GET_TIMEZONE_ID for legacy 11i identifier resolution.

Usage Notes

OTA_TIMEZONE_UTIL is typically invoked indirectly. Forms in the OTA/iLearning footprint (class scheduling, resource booking, and event maintenance) and concurrent programs that generate learner-facing schedules call these subprograms to render dates in the appropriate time zone. Custom code that needs to display or compare OTA event, offering, or resource booking times should call GET_TIMEZONE_NAME and the CONVERT_DATE family rather than querying FND_TIMEZONES_TL directly, since the package encapsulates profile-option handling and language-sensitive name resolution. Because the package is AUTHID CURRENT_USER and referenced by 17 other packages, changes to its specification should be treated as having broad impact across the OTA dependency chain.