Search Results pay_user_tables_pkg




Overview
Explore in AI Chat

Overview

APPS.PAY_USER_TABLES_PKG is a PL/SQL package body in the Oracle E-Business Suite Payroll (PAY) schema. It implements the server-side logic that governs "user-defined tables," the configurable code-value tables that Oracle Payroll administrators create to drive validation lists, lookups, and selectable values throughout the payroll application. These user tables differ from seeded system tables in that customers define their own columns and rows, and the package provides the controlled DML, uniqueness enforcement, referential checks, and multi-language translation handling required to maintain them safely.

The package also embodies the standard Oracle EBS Table Handlers API pattern. Every generated table handler exposes a uniform set of entry points — insert, update, delete, lock, uniqueness and reference checks, and DML status reporting — so that Forms, concurrent programs, and other APIs can perform row maintenance against the underlying user-table entities without embedding direct SQL. Because the object is a package body, its public specification defines the interface while the body contains the implementation and the private helper logic. The metadata classifies it under "OTHER" for API purposes and confirms it is VALID.

Key Procedures and Functions

The ETRM documentation lists thirteen documented entry points. Their responsibilities are as follows:

  • INSERT_ROW — creates a new user-table definition row, applying database defaults and validating input before writing.
  • UPDATE_ROW — modifies an existing user-table row; typically used in conjunction with locking.
  • DELETE_ROW — removes a user-table row, first verifying that no dependent data prevents deletion.
  • LOCK_ROW — acquires a row-level lock to enforce optimistic concurrency between concurrent sessions.
  • CHECK_UNIQUE — enforces uniqueness constraints on key columns such as the user-table name.
  • CHECK_REFERENCES — verifies referential integrity against related tables before a delete is committed.
  • GET_DB_DEFAULTS — returns column default values so callers populate new rows consistently with the database definition.
  • UT_LOV_CONVERSION — converts user-table data into a lists-of-values representation for validation and selection.
  • ADD_LANGUAGE — inserts translated rows for a language that has no translation yet.
  • TRANSLATE_ROW — updates an existing translated (TL) row for a given language.
  • VALIDATE_TRANSLATION — checks that translation content satisfies the required attributes.
  • CHECK_BASE_UPDATE — prevents a base-language row from being altered while translations exist, protecting translation consistency.
  • RETURN_DML_STATUS — returns the success or failure status of the preceding DML operation to the caller.

Tables Accessed

The package operates on the PAY_USER_TABLES family, referenced through APPS synonyms. It reads and writes PAY_USER_TABLES (the base definition), PAY_USER_TABLES_TL (translated attributes), PAY_USER_TABLES_S (the sequence source for primary keys), and the VL view (PAY_USER_TABLES_VL) that joins base and translated content for the current session language. Detail rows are held in PAY_USER_ROWS_F and column definitions in PAY_USER_COLUMNS. FND_LANGUAGES and FND_MESSAGE support language-aware processing and localized error messages, while HR_GENERAL, HR_UTILITY, HR_API, HR_CHKFMT, and HR_LOOKUPS supply shared Human Resources utility functions used for validation and formatting.

Usage Notes

PAY_USER_TABLES_PKG is not referenced by any other database object; it functions as a top-level handler invoked by application code rather than by other PL/SQL packages within the database. It is typically called from Oracle Forms user-table maintenance windows, from concurrent programs that generate or migrate user-table definitions, and from custom code that must insert, change, or delete these definitions programmatically. The metadata records that it is itself referenced by eleven other packages, reflecting its role as a shared utility for user-table maintenance.

Because it depends on HR_API, HR_GENERAL, and HR_UTILITY, sessions invoking the package require the standard HR security context and initialization that those utilities expect. The separation of INSERT_ROW, UPDATE_ROW, DELETE_ROW, and LOCK_ROW calls also means that any custom integration should follow the lock-then-modify-then-check sequence used by Forms table handlers, and inspect RETURN_DML_STATUS to determine whether each operation succeeded before proceeding. The package relies on the PAY_PTT_INS, PAY_PTT_UPD, and PAY_PTT_DEL utilities for underlying row manipulation, so direct SQL against the user-table tables should be avoided in favour of calling these documented procedures.