Search Results user_column

Overview

APPS.PAY_USER_COLUMNS_PKG is a table-handler package that manages the metadata definitions for user-defined columns within Oracle Payroll in Oracle E-Business Suite 12.1.1 and 12.2.2. User columns allow customers to extend payroll processing with customer-specific data elements that feed payroll balances, reports, and third-party integrations. The package encapsulates the standard Oracle Forms table-handler pattern — insert, update, delete, lock, uniqueness checking, and multi-language translation support — for the PAY_USER_COLUMNS entity. It is a low-level data-access and validation layer rather than a business-processing engine; payroll calculation logic is handled by other components. Its API classification in ETRM is OTHER, indicating an internally consumed package rather than a published partner API.

Key Procedures and Functions

The package exposes thirteen documented routines. The core DML handlers are INSERT_ROW, which creates a new user column definition; UPDATE_ROW, which modifies an existing definition; and DELETE_ROW, which removes it. LOCK_ROW performs the standard Forms-style row lock to support optimistic concurrency control. CHECK_UNIQUE validates that a proposed user column name or identifier has not already been used, while CHECK_UNIQUE_F returns a Boolean style result to the calling form for immediate validation. CHECK_DELETE determines whether the record is eligible for deletion, guarding against removal of definitions that are still in use. CHECK_BASE_UPDATE validates attempts to change base (non-translatable) attributes and enforces which fields remain modifiable once translations exist.

Translation support is provided by ADD_LANGUAGE, which seeds rows for a newly installed language; TRANSLATE_ROW, which writes translated name and description values; SET_TRANSLATION_GLOBALS, which establishes the session language context required before translation writes; and VALIDATE_TRANSLATION, which verifies translated content against FND_MESSAGE and FND_LANGUAGES rules. RETURN_DML_STATUS returns the outcome of the most recent DML operation, allowing the calling form to branch on success or failure. Collector routines such as the PAY_PCT_INS, PAY_PCT_UPD, and PAY_PCT_DEL package dependencies are called indirectly to maintain the format/definition side tables associated with user columns.

Tables Accessed

The package operates on the PAY_USER_COLUMNS base table, which holds the primary definition of each user column, and its _S shadow table, which stores the "who columns" audit trail (created by, creation date, last updated by, last update date). The _TL translation table stores language-specific name and description text, and the _VL view exposes the translated and base values in a single queryable structure. PAY_USER_COLUMN_INSTANCES_F holds the flexfield-style instance definitions that map user columns to their behavior in payroll processing. FND_LANGUAGES supplies the installed-language list used by ADD_LANGUAGE and the translation validation routines. DUAL and the STANDARD package provide generic SQL foundation support, and HR_API, HR_UTILITY, and APP_EXCEPTION supply error handling and DateTrack-style conventions consistent with the wider Oracle HRMS/Payroll schema.

Usage Notes

PAY_USER_COLUMNS_PKG is invoked primarily from the Oracle Forms user interface that maintains user column definitions, where the standard table-handler events map directly onto INSERT_ROW, UPDATE_ROW, and DELETE_ROW. It is also called by other PL/SQL components during installation or language-patching operations, and ETRM records that it is referenced by thirteen other packages within the APPS schema. It is not itself referenced by database objects such as triggers or views, confirming its role as a callable library. Custom code that needs to create or maintain user column definitions should invoke these procedures rather than performing direct DML on PAY_USER_COLUMNS, because the package enforces uniqueness, translation integrity, delete guards, and the DML status contract expected by the Forms layer. Direct table writes bypass these validations and risk orphaned rows in the _S, _TL, and instance tables.