Search Results fetch_details

Overview

APPS.EAM_ASSIGN_EMP_PUB is the public PL/SQL interface for returning the employees eligible to be assigned to a specific work order operation, or to a work order operation resource context, within Oracle Enterprise Asset Management (EAM). The package header (EAMPESHS.pls) documents its purpose as the "Package Interface for returning the various employees eligible for assignment to a specific workorder-operation or workorder-operation-resource context." It was initially created on 11-Mar-2005 and carries a 120.0.12010000.3 version stamp, placing it in the Oracle E-Business Suite 12.1.1 and 12.2.2 release family. The API classification is PUB, indicating the package is a supported, externally callable interface rather than an internal implementation detail.

Functionally, the package answers two related business questions: which employees can be assigned to a given operation or resource instance, and what are the assignment details for those employees. This supports the EAM work order staffing workflow, where maintenance planners and supervisors select qualified personnel based on availability, competence, shift calendars, and existing assignment load.

Key Procedures and Functions

• GET_EMP_SEARCH_RESULTS_PUB — public entry point that returns the set of employees eligible for assignment to a work order operation or operation-resource context.
• GET_EMP_ASSIGNMENT_DETAILS_PUB — public entry point that returns assignment detail records for eligible or assigned employees.
• GET_EMP_SEARCH_RESULTS_PVT — private worker routine implementing the employee search logic invoked by the public wrapper.
• GET_EMP_ASSIGNMENT_DETAILS_PVT — private worker routine implementing the assignment detail retrieval logic.
• GET_EMP_ASSIGNMENT_STATUS — determines the current assignment status for an employee in the work order context.
• COMPETENCE_TYPE_CHECK and COMPETENCE_CHECK — evaluate whether an employee possesses the competence or qualification required for the operation.
• CAL_ASSIGNED_HOURS — calculates hours already committed to the employee, supporting availability and percentage calculations.
• CAL_AVAILABLE_HOUR — derives available capacity for the employee over the evaluation window.
• CAL_HR_SYS_BETWEEN_HORIZON and CAL_HR_SYS_BEFORE_HORIZON — compute working hours inside and outside the requested horizon, which is where the user-supplied p_horizon_start_date parameter governs the start of the availability window.
• DATE_EXCEPTION — flags or handles date exceptions encountered during calendar evaluation.
• CAL_EXTRA_HOUR_START_DT, CAL_EXTRA_HOUR_END_DT, CAL_EXTRA_24_HR_END_DT, CAL_EXTRA_24_HR_ST_DT, CAL_EXTRA_HOUR_SAME_DT, and CAL_EXTRA_HOUR_GENERIC — a family of helpers that resolve start and end boundaries for standard and 24-hour shift patterns, including same-day and extra-hour scenarios.
• FETCH_DETAILS — assembles detail rows after the search and calculation steps complete.

Tables Accessed

The package reads and writes through APPS synonyms. Assignment and search result staging tables include EAM_EMP_ASSIGNMENT_DETAILS_TBL and EAM_EMP_SEARCH_RESULT_TBL, which correspond to the record structures declared in the header. Human resources and calendar data are obtained from BOM_CALENDAR_DATES, BOM_DEPARTMENTS, BOM_DEPARTMENT_RESOURCES, BOM_DEPT_RES_INSTANCES, BOM_RESOURCES, BOM_RESOURCE_EMPLOYEES, BOM_RESOURCE_SHIFTS, BOM_SHIFT_DATES, and BOM_SHIFT_TIMES. Competence data is sourced from PER_COMPETENCE_ELEMENTS. Work order context is derived from EAM_WORK_ORDER_DETAILS and WIP_DISCRETE_JOBS, while MTL_PARAMETERS supplies organization-level parameters, including the HR system integration setting that controls calendar-based hour derivation.

Usage Notes

The package is invoked from the EAM work order operations window and related assignment forms whenever a user searches for assignable employees, and it may be called from custom code or concurrent programs needing the same eligibility logic. Callers pass a horizon start date (p_horizon_start_date) and associated horizon parameters to bound the availability calculation; the calendar helpers then derive available, assigned, and unassigned hours, percentages, and shift-aware start and completion dates. Because the package is classified PUB, its public procedures are the supported integration surface, whereas the PVT procedures, the CAL_* helpers, DATE_EXCEPTION, and FETCH_DETAILS are internal and should not be called directly. The package is referenced by one other database package, confirming its role as a shared dependency within the EAM module. Direct DML against the underlying EAM and BOM tables should be avoided in favor of this API.