Search Results get_user_column_id

Overview

PAY_USER_TABLE_DATA_PUMP is an Oracle EBS Oracle Payroll (PAY) schema package owned by APPS and classified under ETRM as API type OTHER. It supports the Payroll Descriptive Flexfield and user-defined table framework, in which customers extend payroll processing with their own tables, columns, rows, and column instances. These user-defined definitions are stored in the PAY_USER_TABLES, PAY_USER_COLUMNS, PAY_USER_ROWS_F, and PAY_USER_COLUMN_INSTANCES_F tables, and formulas that operate on them are stored in the FF_FORMULAS_F and FF_FORMULA_TYPES tables. The package functions as a surrogate-key and object-version-number resolver. Callers supply business-facing identifiers — user keys or names — and receive the corresponding numeric primary key or object version number (OVN) needed to read or update the underlying definition rows. The package is declared AUTHID CURRENT_USER (invoker's rights), so it executes with the privileges of the calling session rather than as definer APPS, which is important in a multi-tenant, multi-business-group payroll deployment. The header carries the revision marker $Header: pyputdpm.pkh 115.0 2003/10/29 20:50 scchakra noship $, indicating the package is marked noship and has been stable since the 11.5 code line, carried forward into 12.1.1 and 12.2.2.

Key Procedures and Functions

The package exposes nine documented functions, each a targeted lookup. GET_USER_TABLE_ID returns the numeric identifier for a user table given its user key, while GET_USER_TABLE_OVN returns that table definition's object version number. GET_USER_COLUMN_ID resolves a user column's numeric identifier from its user key, and GET_USER_COLUMN_OVN returns the matching object version number — this pair is the one most commonly referenced by name in troubleshooting searches such as "get_user_column_id." GET_FORMULA_ID resolves a formula identifier from a formula name together with a business group identifier, reflecting the business-group partitioning of payroll formulas. GET_USER_ROW_ID and GET_USER_ROW_OVN provide the equivalent identifier and version lookups for user-defined rows, with the row OVN variant also accepting an effective date so that date-tracked row definitions resolve to the correct version. GET_USER_COLUMN_INSTANCE_ID and GET_USER_COLUMN_INSTANCE_OVN perform the most granular resolution, using a column user key, business group identifier, row user key, and effective date to locate the specific dated column instance. No procedure parameters beyond those shown in the header are invented here; the functions are typed lookups returning NUMBER.

Tables Accessed

The package reads from the PAY tables that hold user-defined payroll definitions: PAY_USER_TABLES, PAY_USER_COLUMNS, PAY_USER_ROWS_F, and PAY_USER_COLUMN_INSTANCES_F. The "_F" suffix denotes date-tracked (effective-dated) entities, which explains why the row and column instance lookups accept effective dates. Formula resolution reads FF_FORMULAS_F and FF_FORMULA_TYPES. These objects are referenced through APPS synonyms, so the package relies on the standard EBS synonym layer to reach the PAY and FF base tables rather than hard-coding schema-qualified names.

Usage Notes

PAY_USER_TABLE_DATA_PUMP is typically invoked from Oracle Forms user-defined table and column maintenance screens, from Payroll concurrent programs that materialize or validate user-defined data, and from custom PL/SQL that must convert a user key into a surrogate ID before joining to the definition tables. Because the lookups are effective-dated for rows and column instances, callers must pass a meaningful effective date to obtain the version in force at the desired point in time. The package is referenced by four other packages in the ETRM repository, confirming its role as a shared utility layer. When diagnosing "get_user_column_id" behaviour, verify that the supplied user key is unique, that the caller has read access to PAY_USER_COLUMNS through the APPS synonyms, and that invoker's-rights execution context is appropriate for the session.