Search Results copy_user_table

Overview

PQP_COPY_UDT is a public PL/SQL package in the Oracle E-Business Suite HR/Payroll schema (APPS) that supports the User Defined Table (UDT) framework within Oracle Payroll. A User Defined Table is a customer-configured, payroll-specific data structure in which an installation defines its own columns and rows of values — for example, employee-level valid values, statutory lookups, or payroll calculation inputs that are not delivered by Oracle. The package's single documented entry point, COPY_USER_TABLE, performs a server-side duplication of an existing user table definition so that a complete, independent copy can be created, typically as the basis for a new user table that shares the same shape as an existing one.

The source header (pqpcpudt.pkh, version 115.0, dated 2001/12/28) shows that the package spec is declared AUTHID CURRENT_USER, meaning that name resolution and privilege checks are performed against the invoking schema rather than against APPS. In an EBS environment this is significant because the APPS synonyms for the underlying PAY tables must be visible and accessible to the calling session.

Key Procedures and Functions

• COPY_USER_TABLE — The only documented function in the package. It accepts a source user table identifier, a prefix to be applied to the new copied object, and a business group identifier, and returns a NUMBER. The return value conventionally represents the new user table identifier (UDT_ID) generated for the copy, or a status/error indicator. The business group parameter scopes the operation to a specific HR business group so that the copy is created within the correct legislative and security context. The prefix parameter allows the caller to control the naming of the duplicated definition, which is useful when generating variant tables for testing, migration, or parallel configuration.

No other procedures or functions are documented in the ETRM metadata for this package; the API classification is recorded simply as OTHER, indicating that it is an internal utility rather than a formally supported public API.

Tables Accessed

• PAY_USER_TABLES — The header table for user table definitions; holds the UDT identity, name, and business group. COPY_USER_TABLE reads the source row and inserts the new copied definition here.
• PAY_USER_COLUMNS — Stores the column definitions belonging to each user table. Columns are duplicated for the new table so that the copy has the same structure as the source.
• PAY_USER_ROWS_F — The dated (F = date-tracked) table holding the actual row-level data values for a user table. The copy operation replicates row definitions for the new UDT.
• PAY_USER_COLUMN_INSTANCES_F — The dated intersection of columns and rows, holding the individual cell values of the user table. These instances are duplicated to preserve data content in the new table.
• PLITBLM — The standard Oracle PL/SQL index-by table of VARCHAR2 used throughout EBS as an in-memory work area; it is referenced by the package for intermediate key and name collection during processing.

Usage Notes

PQP_COPY_UDT is invoked from PL/SQL rather than directly by an end user. It is referenced by exactly one other APPS package according to the ETRM metadata, which indicates that it is a subordinate utility called by a higher-level UDT maintenance routine. Typical invocation paths include the User Defined Tables maintenance forms in Oracle Payroll (where a "copy" action on a table definition triggers the duplication), payroll data-migration or configuration-transport scripts, and custom code that needs to clone a customer-defined table as part of a rollout between environments.

Because the package is AUTHID CURRENT_USER and is not classified as a supported public API, it should be called from a session that already has the necessary PAY table privileges — normally a session connected as APPS or a schema with the appropriate synonyms and grants. Callers must also pass a valid business group, since the underlying PAY tables are partitioned by BUSINESS_GROUP_ID and the copy would otherwise be created in the wrong organizational context. Given the 2001 vintage of the header, the implementation has been stable across release 12.1.1 and 12.2.2; the ETRM 12.2.2 documentation continues to list the same single function without signature change, confirming backward compatibility.