Search Results purge_processed_recs

Overview

GHR_MTO_PKG is an Oracle EBS Human Resources (HRMS/ETRM) PL/SQL package owned by the APPS schema that supports the Mass Transfer Operations (MTO) functionality for U.S. federal government agencies. Mass transfer operations allow HR administrators to identify, evaluate, and act upon a large group of employees or positions in a single batch process — for example, reallocating personnel between duty stations, agencies, or organizational structures — rather than processing each transfer individually. The package encapsulates the core business logic required to execute a mass transfer, evaluate eligibility of affected records against defined criteria, populate detail rows from pending personnel action requests, and maintain auxiliary position information such as selection flags and comments.

The header declares AUTHID CURRENT_USER, meaning all SQL executes with the privileges of the invoking user, and defines a custom mass_error exception used to signal processing failures during batch executions. The package is classified as OTHER in the ETRM 12.2.2 metadata and is not referenced by any other package, indicating it functions as a top-level executable unit invoked directly by concurrent programs or forms rather than as a shared utility library.

Key Procedures and Functions

• EXECUTE_MTO — The primary driver procedure. It receives a mass transfer identifier, an action code, and an option controlling whether vacant positions are displayed, and returns standard concurrent program OUT parameters (error buffer and return code).
• PURGE_PROCESSED_RECS / PURGE_OLD_DATA — Cleanup routines that remove processed or obsolete staging rows associated with a session or a mass transfer.
• POP_DTLS_FROM_PA_REQ — Populates mass transfer detail records from existing personnel action requests for a given person and effective date; added under a bug fix to accommodate employee number.
• CHECK_SELECT_FLG — Boolean function that validates the selection flag for a position within a given action and effective date context.
• INS_UPD_POS_EXTRA_INFO / UPD_EXT_INFO_TO_NULL / UPD_EXT_INFO_API — Insert, update, or nullify position extra information such as selection flags and comments; the API variant delegates to the supported extra-info API.
• UPDATE_SEL_FLG — Refreshes the selection flag on a position record.
• CHECK_ELIGIBILITY — Evaluates whether a person or assignment qualifies for a mass transfer based on organizational structure, office symbol, personnel office, agency sub-element, duty station, occupation series, and action type. It supports both current and target ("l_") attribute combinations.
• GET_DUTY_STATION_ID — Retrieves a duty station identifier from a duty station code at a given effective date.
• GET_POS_GRP1_DDF / GET_POS_GRP2_DDF — Populate descriptive flexfield (DDF) values for position group 1 and 2 attributes used in MTO criteria.
• GET_EXTRA_INFO_COMMENTS — Returns comments stored in position extra information.
• CREATE_MASS_ACT_PREV — Creates mass action preview rows for administrative review before final execution.
• ASSIGN_TO_SF52_REC — Assigns records to a Standard Form 52 (federal personnel action request) record.
• GET_TO_AGENCY / GET_MTO_NAME — Retrieval helpers returning the target agency and mass transfer operation name.

Tables Accessed

The package reads and writes across three functional areas. Personnel action request tables — GHR_PA_REQUESTS, GHR_PA_REMARKS, GHR_PA_REQUEST_EXTRA_INFO, and GHR_REMARKS — store the source request data from which MTO details are populated. Mass transfer staging and control tables — GHR_MASS_TRANSFERS, GHR_MASS_TRANSFER_CRITERIA, and GHR_MASS_ACTIONS_PREVIEW — hold the batch identifier, selection criteria, and preview output generated during eligibility screening. Human Resources foundation tables — PER_POSITION_EXTRA_INFO, PER_PEOPLE_EXTRA_INFO, PER_GRADES, PER_GRADE_DEFINITIONS, PER_ORG_STRUCTURE_ELEMENTS, PER_ORG_STRUCTURE_VERSIONS, PER_PERSON_TYPES, and PER_POS_STRUCTURE_ELEMENTS — supply the organizational hierarchy, grade, person type, and position data required for eligibility validation and DDF derivation.

Usage Notes

GHR_MTO_PKG is most commonly invoked through Oracle EBS concurrent programs, where EXECUTE_MTO is called with the standard p_errbuf and p_retcode OUT parameters required by the concurrent manager request interface. Prior to execution, the mass transfer header and its criteria are typically defined through an EBS form, and CREATE_MASS_ACT_PREV is used to generate a preview so administrators can review the affected population before committing. The package has no dependent packages, so customizations should call its documented procedures directly rather than expecting reusable subroutines. Because the header uses AUTHID CURRENT_USER, the invoking user must have appropriate privileges on all referenced tables and synonyms. When extending or debugging MTO behavior in 12.1.1 or 12.2.2, the eligibility logic in CHECK_ELIGIBILITY and the detail population in POP_DTLS_FROM_PA_REQ are the most frequent points of investigation.