Search Results delete_old_ses_rows

Overview

DT_FNDATE is a server-side PL/SQL package body owned by APPS in the Oracle E-Business Suite. Its documented purpose is the maintenance and retrieval of the session date stored in the FND_SESSIONS table — the row that governs the effective "system date" seen by an EBS application session. Rather than allowing every form or concurrent program to read the database clock directly, EBS centralizes the notion of a current session date so that date-sensitive processing (reporting periods, transaction dating, validation of date ranges) behaves consistently within a given login.

The package dates back to 1993, when it was authored by P.K. Attwood and first delivered under a different name as part of the release 4 "DT" (date) server-side code base. It was subsequently renamed to dt_fndate in October 1994 to conform to the naming convention that all objects in this family begin with the product prefix. The header line 120.1 indicates the version shipped with Release 12, making the package relevant to both 12.1.1 and 12.2.2 installations. Because it is not classified as an API in the conventional sense, it is best treated as internal infrastructure rather than a supported public interface, although it is referenced by 52 other packages in the instance.

Key Procedures and Functions

ETRM documents six procedures and functions in this package body. Parameter lists are not documented here and are deliberately omitted.

• GET_DATES — Retrieves the date values for the current session, including the system-wide session date and associated global values. Its select statement was changed early in the package's history to query trunc(sysdate), and later fixes (versions 110.1/115.1) corrected the population of the global system date when global variables had not yet been initialized but a row already existed in FND_SESSIONS. Additional validation ensures the "yesterday" global is nulled when the session date equals 01/01/0001.
• CHANGE_SES_DATE — The procedure corresponding to the search term change_ses_date. It updates the session date held in FND_SESSIONS. A change introduced in version 115.3 raises an exception when no matching row is found in FND_SESSIONS, and later validation (115.7) applies the same 01/01/0001 rule to the yesterday global.
• SET_EFFECTIVE_DATE — Added in September 2001 (version 115.5) to establish the effective date for the session, providing a distinct entry point alongside the older date-change logic.
• DELETE_SES_ROWS — Removes stale session rows. It was rewritten in 1999 to eliminate a join between FND_SESSIONS and a v$ view, and in 2001 was converted to use gv$session in order to support Oracle Parallel Server.
• DELETE_OLD_SES_ROWS — A related cleanup routine that targets older, no-longer-required session rows.
• CLEAN_FND_SESSIONS — A housekeeping procedure for purging or resetting FND_SESSIONS contents so that the table does not accumulate orphaned session records.

The change history also notes that HR_UTILITY calls were removed in version 115.8, reducing the package's external dependencies.

Tables Accessed

The documented tables referenced through APPS synonyms are:

• FND_SESSIONS — the primary target. All read and write activity in this package revolves around the session row that stores the current session date and related values.
• GV$SESSION — consulted by the deletion routines to identify active database sessions. The gv$ variant replaced v$session to remain valid in Oracle Parallel Server environments.
• DT_DATE_PROMPTS_TL — a translated table of date prompt definitions, used in support of date prompting behavior.
• PLITBLM — a PL/SQL index-by table utility structure used for in-memory handling of row identifiers or similar collections.

Usage Notes

DT_FNDATE is invoked primarily from the EBS forms layer and from other server-side packages rather than directly by end users. When a user changes the effective date through the standard "Change Date" mechanism in an EBS form, the underlying call path reaches CHANGE_SES_DATE or SET_EFFECTIVE_DATE, which then persists the value in FND_SESSIONS. The GET_DATES routine is called on session initialization and whenever the cached session date globals need to be refreshed.

The cleanup procedures — DELETE_SES_ROWS, DELETE_OLD_SES_ROWS, and CLEAN_FND_SESSIONS — are typically executed as periodic housekeeping, either from concurrent programs or from administrative scripts, to remove session rows whose owning database sessions have terminated. Because the package is referenced by 52 other packages, customizations that call it should treat it as internal infrastructure and avoid modifying its behavior. Sites on 12.1.1 and 12.2.2 should be aware that the ONLINE PATCHING and editioning infrastructure in 12.2 may affect how the package is compiled and invoked, but the documented procedure set remains unchanged across both releases.