Search Results g_effective_date

Overview

HRUSERDT is a PL/SQL package body in the APPS schema that supports Oracle EBS Payroll and Human Resources functionality related to user-defined tables (UDTs). Its principal business function is to resolve effective-dated values from user-defined table structures using an effective date supplied by the caller, a session-level cached date, or the effective date recorded against the current application session. The package therefore acts as a controlled access layer over the user table definition and instance data stored in the PAY_USER_TABLES, PAY_USER_ROWS_F, and PAY_USER_COLUMN_INSTANCES_F tables, applying the effective date and legislation context required by Oracle HRMS effective-dating rules. The header comment identifies the underlying source file as pyuserdt.pkb, version 120.2.12000000.1, shipped in January 2007, which places the package within the long-standing Payroll user table architecture that remains supported on both EBS 12.1.1 and 12.2.2.

The user search term "g_effective_date" corresponds directly to the package-level global variable declared at line 6 of the source, which caches the effective date for reuse across calls within the same database session.

Key Procedures and Functions

• SET_G_EFFECTIVE_DATE — Assigns the caller-supplied date to the package global g_effective_date, establishing a cached effective date for subsequent value lookups performed in the same session.
• UNSET_G_EFFECTIVE_DATE — Clears the g_effective_date global by setting it to NULL, removing the cached effective date so that later calls revert to session-derived or explicitly passed dates.
• GET_TABLE_VALUE — The functional core of the package. It returns a value from a user-defined table by resolving the effective date, determining the legislation code for the supplied business group, and identifying whether the user table uses range-based or match-based lookup semantics. The effective date resolution follows an explicit precedence: a parameter date is used if supplied; otherwise the cached g_effective_date is used; otherwise the effective date is read from FND_SESSIONS for the current session. Legislation code and table definition are cached in package globals to avoid repeated queries.

Tables Accessed

• FND_SESSIONS — Read to obtain the session effective date when no parameter or cached date is available.
• PAY_USER_TABLES — Read to determine the range_or_match attribute and the user table identifier for the requested table.
• PAY_USER_COLUMNS — Read to resolve column definitions associated with the user table being queried.
• PAY_USER_ROWS_F — Read to locate the effective-dated row matching the resolved effective date.
• PAY_USER_COLUMN_INSTANCES_F — Read to retrieve the actual stored value for the requested row and column.

All access is performed through APPS synonyms under the standard EBS data model.

Usage Notes

HRUSERDT is an internal utility rather than a public API, classified as OTHER in the ETRM metadata. It is referenced by 96 other packages, indicating broad internal reuse across Payroll and HRMS logic. Typical invocation occurs from FastFormula contexts, payroll run processing, and custom PL/SQL that must resolve user table values under a specific effective date. When a caller needs multiple lookups against the same date, SET_G_EFFECTIVE_DATE should be called once and UNSET_G_EFFECTIVE_DATE called afterwards to prevent stale caching across logical operations. Custom code should supply p_effective_date explicitly wherever deterministic results are required, since reliance on FND_SESSIONS introduces dependency on session context.