Search Results get_row_value

Overview

APPS.HRUSERDT is an Oracle E-Business Suite PL/SQL package that provides the runtime access layer for Oracle Payroll's user-defined tables and columns. The package is defined with AUTHID CURRENT_USER, meaning its SQL executes with the privileges of the invoking session rather than the package owner, which allows callers to read user-defined payroll data subject to their own security profile and session context. The header comment identifies the product as Oracle*Payroll and the source file as pyuserdt.pkh, a naming convention consistent with the PY (Payroll) application short name. In ETRM the package is classified as OTHER, indicating it is an internal utility rather than a formally supported public API.

The package exists to abstract the complex, multi-table storage model behind Payroll user-defined tables. Rather than requiring callers to navigate the header, instance, row, and column structures directly, HRUSERDT exposes a small set of entry points that resolve a logical table name, column name, and row identifier into a stored value.

Key Procedures and Functions

The documented interface consists of three callable units, reflecting the state of the package after its final revision in July 2002:

• GET_TABLE_VALUE — A function that returns a VARCHAR2 value representing the contents of a specified column for a given row in a user-defined table. It accepts the business group identifier, the user table name, the column name, the row value, and an optional effective date defaulting to null. This is the primary retrieval routine and the most widely referenced entry point in the package.
• SET_G_EFFECTIVE_DATE — A procedure that establishes a package-level global effective date, presumably so that subsequent calls resolve user table rows as of a consistent point in time.
• UNSET_G_EFFECTIVE_DATE — A procedure that clears the previously set global effective date, returning the package to its default date resolution behavior.

It is important to note that the GET_ROW_VALUE function referenced in the version history (added 10-JUL-2002 by vborhade) was explicitly removed in the 17-JUL-2002 revision. The search term "get_row_value" therefore does not correspond to a currently available entry point in this package; the equivalent capability is served by GET_TABLE_VALUE. The commented-out PRAGMA RESTRICT_REFERENCES for GET_TABLE_VALUE was also removed (Bug 1027169) to permit broader use within SQL and PL/SQL contexts.

Tables Accessed

Per the documented metadata, HRUSERDT references five tables through APPS synonyms:

• PAY_USER_TABLES — defines the user-defined table headers, providing validation that the requested table name is legitimate.
• PAY_USER_COLUMNS — defines the columns belonging to each user-defined table, used to resolve the requested column name.
• PAY_USER_COLUMN_INSTANCES_F — stores the dated column-level definitions, supporting effective-date–aware resolution.
• PAY_USER_ROWS_F — stores the actual row values keyed by row identifier, and is the source of the value returned by GET_TABLE_VALUE.
• FND_SESSIONS — supplies session context, consistent with the AUTHID CURRENT_USER declaration.

Usage Notes

HRUSERDT is an internal Payroll utility rather than a published API. ETRM records it as referenced by 96 other packages, indicating it is invoked predominantly from other server-side PL/SQL within the Payroll application rather than from Oracle Forms or concurrent programs directly. Typical invocation scenarios include payroll calculation and reporting logic that must read user-defined table values, and custom code that needs to resolve user table data while respecting business group and effective-date semantics.

Because the effective-date global is package state, callers that set it should ensure UNSET_G_EFFECTIVE_DATE is called afterward to prevent unintended date resolution in subsequent calls within the same session. Given the removal of GET_ROW_VALUE and the classification as OTHER, developers should treat GET_TABLE_VALUE as the supported retrieval path and avoid reliance on undocumented behavior. Any modifications to underlying PAY_USER_* tables should be performed through the supported Payroll user table maintenance flows, not directly, to preserve the integrity of the structures this package reads.