Search Results set_effective_date

Overview

The APPS.DT_FNDATE package is a session date maintenance utility within Oracle E-Business Suite. Its principal business function is to manage the effective or "session" date held in the FND_SESSIONS table for a user's active session. In EBS, users frequently operate against a session date rather than the true system date, allowing them to enter, query, and process transactions as of a specified point in time without altering the underlying database clock. DT_FNDATE encapsulates the logic required to read, change, and cleanse that session date, ensuring that applications consistently derive their notion of "today" from a single, controlled source.

The package originated in the early releases of Oracle's distribution products, as reflected in its 1993 creation date and its later renaming to conform to the <prod>_ naming convention. Although its header copyright and naming history tie it to distribution, the package is deployed in the APPS schema and classified as an "OTHER" API, making it a shared infrastructure component rather than a published public interface. It is referenced by 52 other packages, underscoring its role as a foundational dependency across the application stack.

Key Procedures and Functions

The documented package exposes six procedures and functions:

• GET_DATES — Returns the current session date, along with related derived values. According to the package header, it sets a session date (returned as a truncated date), a yesterday date calculated as the session date minus one day, a start-of-time constant of 01-JAN-0001, an end-of-time constant of 31-DEC-4712, and the true system date. It also returns a commit indicator that signals whether the procedure inserted, updated, or deleted rows, instructing the calling form to perform a commit.
• CHANGE_SES_DATE — Updates the session date stored in FND_SESSIONS for the current session, providing the mechanism by which a user's effective date is altered.
• SET_EFFECTIVE_DATE — Added in version 115.1 (13-SEP-2001), this procedure sets the effective date for the session. It is the object most closely associated with the "set_effective_date" search and is the modern entry point for establishing the date against which EBS operations are evaluated.
• DELETE_SES_ROWS — Removes session rows from FND_SESSIONS, with its description later updated as part of the long-term resolution for Bug 4163689.
• DELETE_OLD_SES_ROWS — Declaration added in version 120.1 (07-May-2006) to purge obsolete session rows, supporting the same bug remediation effort.
• CLEAN_FND_SESSIONS — Also added in version 120.1, this procedure performs housekeeping against FND_SESSIONS to remove stale or orphaned session data.

Tables Accessed

DT_FNDATE references four documented objects through APPS synonyms:

• FND_SESSIONS — The central table holding session-level state, including the session date. It is the primary read/write target for GET_DATES, CHANGE_SES_DATE, and SET_EFFECTIVE_DATE, and the subject of the cleanup procedures.
• DT_DATE_PROMPTS_TL — A translatable prompts table associated with date-entry forms, providing language-specific labels used in the session date user interface.
• GV$SESSION — A dynamic performance view used to correlate the EBS session with the underlying database session, supporting identification of the correct row in FND_SESSIONS.
• PLITBLM — A standard PL/SQL index-by table indexed by BINARY_INTEGER, used for internal list handling and bulk operations.

Usage Notes

DT_FNDATE is typically invoked from Oracle Forms during the session initialization and date-change workflow. The GET_DATES procedure is designed to be called by form code, which then inspects the commit indicator and performs a commit when the package has modified session rows. SET_EFFECTIVE_DATE and CHANGE_SES_DATE are called when a user explicitly changes the effective date through a date prompt or a standard EBS date-selection mechanism.

The cleanup procedures (DELETE_SES_ROWS, DELETE_OLD_SES_ROWS, and CLEAN_FND_SESSIONS) are administrative in nature and support the removal of outdated session records, particularly for long-lived or abandoned sessions. Custom code that requires the current session date should call this package rather than reading FND_SESSIONS directly or relying on SYSDATE, because doing so preserves consistency with EBS date-handling semantics and honors any user-selected effective date. The NOCOPY performance changes introduced in version 115.3 for release 11.5.9 remain relevant to 12.1.1 and 12.2.2 deployments, where the package continues to serve as a shared dependency across the applications schema.