Search Results get_table_value

Overview

APPS.PER_NL_FORMULA_FUNCTIONS is a Dutch localization (NL) helper package body used by Oracle E-Business Suite Payroll and Human Resources formula processing. Its purpose is to expose payroll-related table data to Oracle Payroll formulas running under the Dutch (Netherlands) localization by wrapping the underlying hruserdt.get_table_value utility. The package provides a thin, defensive abstraction that returns a single column value from a named table for a given business group and row key, returning the literal character '0' when no matching row is found rather than raising an exception to the calling formula. Two overloaded forms of the same function are implemented, differing in whether the lookup is resolved at a specific effective date derived from a payroll action. The header reference penlffnc.pkb 120.0.12000000.1 indicates the package is a long-standing, low-revision component shipped with the EBS application tier.

Key Procedures and Functions

The package documents a single public function name, GET_TABLE_VALUE, implemented twice as an overloaded pair:

• GET_TABLE_VALUE (business group form) — Resolves a table value for a business group, table name, column name, and row identifier, delegating to hruserdt.get_table_value. It traps NO_DATA_FOUND internally and substitutes '0' before returning the result, so formulas never receive an unhandled exception for a missing row. The local return variable is anchored to pay_user_column_instances_f.value%type.
• GET_TABLE_VALUE (date-aware form) — Accepts an additional date-earned and payroll action identifier. It queries PAY_PAYROLL_ACTIONS to obtain the effective date and the recorded date earned for the supplied payroll action. If the caller's date earned equals the value stored on the payroll action, the effective date from the action is used as the lookup date; otherwise the caller-supplied date earned is used. The resolved date is then passed to hruserdt.get_table_value, again with NO_DATA_FOUND converted to '0'.

Tables Accessed

Two tables are referenced through APPS synonyms:

• PAY_PAYROLL_ACTIONS — Read by the date-aware overload to retrieve effective_date and date_earned for the given payroll_action_id. This establishes the correct temporal context for date-effective table lookups during payroll run processing.
• PAY_USER_COLUMN_INSTANCES_F — Referenced for its VALUE column type, which anchors the return variable declaration. This ensures the returned datatype matches the user-defined column value storage used by payroll formulas.

The indirect data retrieval is performed by hruserdt.get_table_value, which handles the actual dynamic access to Dutch localization tables and user-defined tables.

Usage Notes

This package is invoked from Oracle Payroll Fast Formula contexts, where formula authors need to read a value from a database table during payroll calculation. Formula writers in the Dutch localization call GET_TABLE_VALUE by name; the overload selected depends on whether the formula supplies a payroll action context. Because the function never propagates NO_DATA_FOUND, formulas can rely on receiving '0' for absent data, avoiding error handling in the formula layer. The package has no dependents and is not invoked by other PL/SQL packages; it is a leaf utility intended for direct formula or custom-code invocation. Typical invocation occurs during payroll run processing, where the date-aware overload aligns lookups to the payroll action's effective date. Customizations should preserve the business group scoping and date-resolution semantics, as these determine which retroactive or effective-dated records are visible to the formula.