Search Results get_extract_udt_info

Overview

APPS.PQP_GB_TP_PENSION_EXTRACTS is a PL/SQL package body within the Oracle E-Business Suite HRMS/Payroll module, classified under the OTHER API category. It is a core component of the United Kingdom Teachers' Pensions (TP) statutory reporting framework, responsible for assembling the data extracts that UK local education authorities must submit to the Teachers' Pensions scheme administrator. The package extracts assignment, salary, and employment attribute data for scheme members and formats it into the record structures expected by the Teachers' Pensions return, including the Type 4 (TP4) data set referenced throughout the package's procedure and function names.

The body contains 34 documented procedures and functions in total and is referenced by three other packages, confirming that it operates as a shared service layer rather than a standalone entry point. Its header identifies it as script pqpgbtp4.pkb, last updated in the 12.1.1 codeline (120.1.12010000.3), and it remains available in 12.2.2.

Key Procedures and Functions

The package is organised around three functional groups: global configuration, data retrieval, and instrumentation.

• SET_EXTRACT_GLOBALS and SET_RUN_EFFECTIVE_DATES — initialise package-level global state for a given extract run. SET_RUN_EFFECTIVE_DATES, the object of the user's search, establishes the effective start and end dates that constrain every subsequent query, ensuring the extract reports the assignment and pay state as of the correct payroll period.
• SET_PAY_PROC_EVENTS_TO_PROCESS — determines which payroll processing events fall within the reporting window and should be evaluated.
• GET_EXTRACT_UDT_INFO and GET_TOTAL_NUMBER_DATA_RECORDS — return metadata about the user-defined table driving the extract and the record count for the return.
• Header and identifier accessors — GET_HEADER_SYSTEM_ELEMENT, GET_LEA_NUMBER, GET_ESTB_NUMBER, GET_ORIGINATORS_TITLE, GET_DFEE_REFERENCE_NUMBER, and GET_TP4_EMPLOYMENT_CATEGORY supply the schema-level header fields and establishment/LEA identifiers required on the TP return.
• Employment and salary accessors — GET_TP4_START_DATE, GET_TP4_SALARY_SCALE, CHK_TP4_IS_TEACHER_NEW_STARTER, and GET_TRANSLATE_ASG_EMP_CAT_CODE resolve member-specific attributes, including the translation of assignment and employee category codes into Teachers' Pensions categories.
• Flexfield utilities — GET_DFLEX_VALUE, GET_FLEX_SEGMENT_VALUE, and GET_KFLEX_VALUE resolve descriptive and key flexfield segment values for the extract record.
• Debug instrumentation — DEBUG (three overloads for VARCHAR2, NUMBER, and DATE) and DEBUG_ENTER write trace output through HR_UTILITY, with indentation driven by g_nested_level and conversion handled by FND_NUMBER and FND_DATE.

Tables Accessed

The package reads assignment and person data from PER_ALL_ASSIGNMENTS_F and location data from HR_LOCATIONS_ALL with its descriptive flexfield companion HR_LOCATION_EXTRA_INFO. Organisation and establishment details come from HR_ORGANIZATION_INFORMATION. Payroll and element configuration is drawn from PAY_ELEMENT_ENTRIES_F, PAY_ELEMENT_LINKS_F, PAY_ELEMENT_TYPES_F, PAY_ELEMENT_TYPE_EXTRA_INFO, PAY_EVENT_GROUPS, and PAY_GRADE_RULES_F. Flexfield and user-defined table data are obtained from PAY_USER_TABLES and PAY_USER_COLUMN_INSTANCES_F. Extract run results and record-level output are written to BEN_EXT_RSLT and BEN_EXT_RSLT_DTL, with BEN_EXT_RCD holding the extract record definitions. All tables are referenced through APPS synonyms.

Usage Notes

This package is not normally invoked directly. It is called by the concurrent program that produces the Teachers' Pensions extract, and by the three dependent packages identified in the metadata, which rely on its configuration and accessor routines. Because SET_RUN_EFFECTIVE_DATES governs date-effective filtering for the whole run, it must be executed after SET_EXTRACT_GLOBALS and before any record retrieval. Debug tracing is enabled by uncommenting the block in DEBUG_ENTER and is only active at the top nesting level (g_nested_level = 0); the HR_UTILITY trace pipe is named REQID concatenated with the request identifier. Customisations should call the documented accessors rather than duplicating date-effective logic, since Teachers' Pensions rules change between releases and are maintained centrally here.