Search Results get_session_date




Overview
Explore in AI Chat

Overview

APPS.BEN_PEN_LER is a PL/SQL package body in the Oracle E-Business Suite Advanced Benefits (OLAM / BEN) module. Its name derives from the "LER" entity — the Life Event Reason / Learning and Event Rules construct used by the Benefits engine to determine which life events, and which reason codes, trigger changes to a participant's benefit elections. The package encapsulates lifecycle-event record checking logic, validating and comparing old versus new versions of a Benefits LER record (the g_pen_ler_rec structure) as they are processed by the Benefits rules engine.

The package file header identifies it as bepentrg.pkb (version 120.0, dated 2005/05/28), placing it in the Benefits "trigger" layer. It operates against the effective-dated LER base table and its associated change-set intersection and relationship tables, ensuring that only LERs valid as of the session's effective date are applied.

The user query for get_session_date corresponds to a private cursor declared within this package body. Rather than reading the system date, the cursor selects effective_date from FND_SESSIONS filtered by userenv('SESSIONID'), returning the effective date associated with the current EBS session. This is a standard EBS technique for obtaining an application-form effective date (the "session date") so that effective-dated logic honours the date the user is working with, not merely the database server date. The package also defines a companion get_system_date cursor returning trunc(sysdate) from DUAL, illustrating the deliberate distinction between the user's session context and the physical system date.

Key Procedures and Functions

  • LER_CHK — The single documented program unit. It compares an old LER record (p_old) against a new LER record (p_new), optionally referencing an effective date, and performs the validation/trigger checks required before the change is accepted. It declares internal cursors — notably get_session_date, get_system_date, and get_ler — and uses a cache table (ben_letrg_cache.g_egdlertrg_inst_tbl) to hold instance-level LER trigger data.
  • get_session_date (private cursor) — Retrieves the effective date for the current session from FND_SESSIONS via USERENV('SESSIONID'); used to bound all effective-dated queries in the package.
  • get_ler (private cursor) — Selects eligible LER_ID and OCRD_DT_DET_CD values from BEN_LER_F where the session date falls between the LER's effective start and end dates, further restricted by existence checks against the participant-information change-set and related-person change-set intersection entities. This implements the association between source table BEN_PRTT_ENRT_RSLT_F and the LER configuration.

Tables Accessed

  • FND_SESSIONS — Source of the session effective date; read by get_session_date.
  • BEN_LER_F — The base LER definition table; read to obtain LER_ID and occurrence-detail determination codes effective on the session date for the business group.
  • BEN_PER_INFO_CHG_CS_LER_F and BEN_LER_PER_INFO_CS_LER_F — Participant-information change-set intersection tables; joined to link person-info change sets to applicable LERs.
  • BEN_RLTD_PER_CHG_CS_LER_F and BEN_LER_RLTD_PER_CS_LER_F — Related-person change-set intersection tables; the parallel mechanism for dependent/related-person events.
  • BEN_PTNL_LER_FOR_PER — Potential LERs for a person; referenced by the trigger-processing logic.
  • PER_CONTACT_RELATIONSHIPS — HR contact relationship data used to resolve related-person context.
  • DUAL and PLITBLM — Utility references (system date and PL/SQL index-by-table support).

Usage Notes

BEN_PEN_LER is an internal Benefits engine package rather than a public API. It is classified as OTHER in the ETRM registry and is referenced by one other package, indicating it is called programmatically from the Benefits LER trigger-processing stack rather than directly from a form or concurrent program. It is invoked when the Benefits engine evaluates whether a life event reason should fire for a participant, typically during benefits enrolment, life-event processing, or when the LER configuration is refreshed in a user's session. The get_session_date cursor is significant because all effective-dating within the package is anchored to the session's effective date, matching the date context presented in the Professional or Self-Service Benefits user interface. Customisations should avoid calling this package directly; the supported extension points are the underlying LER configuration and the standard Benefits life-event concurrent processes.