Search Results adjust_datetime

Overview

FND_TIMEZONE_PUB is a public PL/SQL package in the APPS schema that provides a centralized utility for timezone conversion within Oracle E-Business Suite. Its principal purpose is to translate a date and time value expressed in one timezone into the equivalent value in a second timezone. This capability is fundamental wherever EBS records, displays, or compares date-stamped data across users, operating units, or servers situated in different geographic regions.

In both release 12.1.1 and 12.2.2, the package is classified as a public API (PUB) under the APPS schema, and it is documented as the single supported gateway for programmatic timezone adjustment. The package source header identifies it with a creation and revision history beginning in 2006, and it carries the standard purity pragma declarations (WNDS, WNPS, RNDS) that guarantee the function does not modify database state. This design makes the routine safe for use inside SQL statements, views, and read-consistent queries.

Key Procedures and Functions

The documented interface exposes one function:

• ADJUST_DATETIME — Accepts a date/time value together with a source timezone and a target timezone, and returns the date/time value adjusted into the target timezone. The source and target timezones are supplied as timezone identifiers (for example, values that correspond to the timezone names held in the EBS timezone reference data), and the incoming date is the value to be converted. The function returns a DATE result suitable for direct assignment, comparison, or storage. Because of the restrict_references pragma, the function may be called from SQL, but it performs no inserts, updates, or deletes.

No overloads, additional helper procedures, or exception-raising entry points are documented for this package; the conversion routine represents the entire supported public surface.

Tables Accessed

The ETRM metadata records no direct table references for FND_TIMEZONE_PUB and identifies no tables that it reads or writes through APPS synonyms. This is consistent with the purity pragma declarations: the package performs its calculation in memory against timezone definitions rather than querying or mutating application tables. The authoritative timezone definitions reside in the FND timezone reference tables maintained by Oracle, but the package does not expose or document direct access to them. Callers therefore do not need to grant or manage additional table privileges when invoking the routine.

Usage Notes

FND_TIMEZONE_PUB is a foundational utility rather than an end-user feature. ETRM metadata indicates that it is referenced by seven other packages, confirming its role as a shared dependency across EBS modules that must normalize timestamps. Typical invocation scenarios include:

• Converting a timestamp captured in a user's local timezone to the application's canonical timezone before storing or comparing it.
• Translating database-stored timestamps into a reporting user's local timezone for display in Oracle Forms, OAF pages, or concurrent program output.
• Use within custom PL/SQL or SQL where consistent, server-side timezone conversion is required instead of relying on client session settings.

Because it is a public API, direct calls are supported and preferable to re-implementing offset logic, which would fail to account for daylight-saving transitions. The function should be used in read-only contexts and is not intended to persist data. When upgrading from 12.1.1 to 12.2.2, the documented interface remains unchanged, so existing calls continue to function without modification. Developers should confirm that the source and target timezone identifiers supplied are valid values recognized by the EBS timezone repository, since the package assumes well-formed input and does not document validation or exception handling behavior.