Search Results get_user_column_name

Overview

APPS.PQP_GB_CPX_EXTRACT_FUNCTIONS is a United Kingdom (GB) payroll extract utility package used by Oracle E-Business Suite Payroll and Oracle HRMS to assemble the pension and National Insurance (NI) data required for statutory financial reporting. The package name reflects its role: it supplies the extraction "functions" consumed by the Compensation and Pension Extract (CPX) process, the mechanism by which Oracle Payroll produces formatted outbound data files for pension providers, HM Revenue & Customs (HMRC) submissions, and other third-party interfaces.

The package operates with AUTHID CURRENT_USER, so it executes under the privileges of the invoking schema and inherits the caller's security context. It is fixed to GB legislation through the global g_legislation_code constant of 'GB' and relies heavily on a set of package-level globals—business group, effective date, extract type, and extract user-defined table (UDT) name—established once per run. The unit is primarily a helper library: it exposes the low-level lookups that higher-level extraction logic uses to resolve element entries, balances, input values, and UDT column definitions into the values written to the extract file.

Key Procedures and Functions

The package documents 63 procedures and functions. The principal entries include:

• SET_EXTRACT_GLOBALS / SET_PERIODIC_RUN_DATES — initialise the package globals (business group, effective date, extract type, UDT name, and extract date range) and compute the periodic run date windows used by downstream lookups.
• GET_UDT_ID / GET_USER_COLUMN_ID / GET_USER_COLUMN_NAME — resolve a user-defined table to its identifier and translate between UDT column names and column identifiers. GET_USER_COLUMN_NAME is the function most frequently sought when customising extracts, because it maps the internal column ID back to the display name configured in the UDT.
• FETCH_CPX_UDT_DETAILS — retrieves the UDT structure and definition rows that govern the layout of the extract output.
• GET_INPUT_VALUE_ID / GET_PAY_BAL_ID / GET_PAY_ELE_IDS_FROM_BAL — perform reference-data lookups against input values, balance types, and element types, returning the surrogate IDs required to query element entries and run results.
• GET_ELE_ENTRY_VALUE — returns the value held on a specific element entry, used to populate pension and NI fields.
• GET_ASG_BAL_VALUE / GET_PERSON_BAL_VALUE / GET_BALANCE_VALUE / GET_REMUNERATION_FROM_BAL — return aggregated balance values at assignment, person, or generic level, including the remuneration figure used in pension calculations.
• GET_NI_CONT_IN_ELE_DETAILS (documented as GET_NI_CONT_OUT_ELE_DETAILS) and GET_NI_INDICATOR — resolve NI category and contribution element details and return the NI indicator for an assignment.
• GET_ASG_EMPLOYMENT_CAT, GET_PART_TIME_INDICATOR, GET_MARITAL_STATUS, CHK_IS_EMPLOYEE_A_STARTER — return assignment and personal attributes that affect contribution rates and pension eligibility.

Parameter lists are intentionally not reproduced here; callers should confirm exact signatures against the deployed package specification.

Tables Accessed

The package reads and writes through APPS synonyms, including:

• PAY_ELEMENT_TYPES_F, PAY_ELEMENT_LINKS_F, PAY_ELEMENT_ENTRIES_F, PAY_ELEMENT_ENTRY_VALUES_F — element definition, eligibility, and entry data used to derive pension and NI contributions.
• PAY_INPUT_VALUES_F — input value definitions referenced by name globals such as employee and employer contribution input values.
• PAY_BALANCE_TYPES, PAY_BALANCE_FEEDS_F, PAY_RUN_RESULTS — balance and payroll run result sources for remuneration and contribution totals.
• PAY_ASSIGNMENT_ACTIONS, PAY_PAYROLL_ACTIONS, PAY_ELEMENT_CLASSIFICATIONS — assignment/payroll context and element classification.
• BEN_EXT_DFN, BEN_EXT_RCD, BEN_EXT_RCD_IN_FILE, BEN_EXT_RSLT_DTL — extract definition, record, and result detail tables that hold extract layout and produced values (e.g. g_header_system_element maps to VAL_01).

Usage Notes

PQP_GB_CPX_EXTRACT_FUNCTIONS is referenced by zero other packages in the ETRM metadata, indicating that it is invoked directly by the GB pension/CPX concurrent program or by custom extract wrappers rather than forming part of a shared API stack. It is not a public processing API; it is a component of the delivered GB payroll extract solution and is executed at extract run time, after the extract definition and UDT are selected. Customisations typically call individual functions to obtain specific values—most commonly GET_USER_COLUMN_NAME, GET_BALANCE_VALUE, and GET_ELE_ENTRY_VALUE—from within a bespoke extract formula or PL/SQL hook. Because behaviour depends on the package globals being populated, callers must ensure SET_EXTRACT_GLOBALS (or the equivalent initialisation path) has run before any getter is invoked. The package is classified as OTHER and is not supported for direct modification; extensions should be built against the documented functions only, with signatures verified against the target 12.1.1 or 12.2.2 instance, as the file header reflects a 2010 build (pqpgbcpx.pkh 120.8).