Search Results cal_extra_hour_end_dt
Overview
EAM_ASSIGN_EMP_PUB is the public PL/SQL API package supplied by Oracle Enterprise Asset Management (EAM) to identify and evaluate employees who are eligible for assignment to a specific work order, work order operation, or work order-operation-resource context. The package body, stored under the APPS schema and delivered in the EAMPESHB.pls script, exposes query and search capabilities used by the Maintain Work Order and related EAM forms so that maintenance supervisors can select competent, available personnel against a defined scheduling horizon.
The header identifies the file revision as 120.15.12010000.2, dated 30-Oct-2008, with initial creation attributed to Samir Jain on 11-Mar-2005. The package follows the standard Oracle Application Object Library API conventions, including the FND_API global constants (G_FALSE, G_VALID_LEVEL_FULL), an API version parameter defaulted to 1.0, and the conventional OUT parameters x_return_status, x_msg_count and x_msg_data. A private debugging facility (g_debug driven by the APPS_DEBUG profile option) and stub procedures msg and purge are present but effectively disabled.
Key Procedures and Functions
The package contains 19 documented procedures and functions, divided into public entry points, private implementation routines, and calendar/horizon calculation helpers associated with the cal_extra_hour_end_dt search term.
- GET_EMP_SEARCH_RESULTS_PUB — public entry point that returns the set of employees eligible for assignment based on horizon dates, organization, work order, competence type and identifier, resource, operation sequence, department and person filters.
- GET_EMP_ASSIGNMENT_DETAILS_PUB — public API that returns assignment details for a given employee and work order context.
- GET_EMP_SEARCH_RESULTS_PVT and GET_EMP_ASSIGNMENT_DETAILS_PVT — private implementations invoked by the corresponding public wrappers.
- GET_EMP_ASSIGNMENT_STATUS — determines the current assignment status of an employee relative to the requested context.
- COMPETENCE_TYPE_CHECK and COMPETENCE_CHECK — validate that the competence type and competence identifier supplied match the resource requirements for the operation.
- CAL_ASSIGNED_HOURS and CAL_AVAILABLE_HOUR — compute hours already assigned to the employee and the remaining available hours within the horizon.
- CAL_HR_SYS_BETWEEN_HORIZON, CAL_HR_SYS_BEFORE_HORIZON and DATE_EXCEPTION — derive working hours from the shop calendar for periods inside or preceding the requested horizon, applying calendar exceptions.
- 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 bound extra or overtime hour windows, including same-day and 24-hour spanning cases. These directly explain the user's cal_extra_hour_end_dt query.
- FETCH_DETAILS — loads the result rows into the out tables populated by the public APIs.
Tables Accessed
The package reads and writes through APPS synonyms. Result and assignment staging occurs in EAM_EMP_SEARCH_RESULT_TBL and EAM_EMP_ASSIGNMENT_DETAILS_TBL. Work order context is drawn from EAM_WORK_ORDER_DETAILS and WIP_DISCRETE_JOBS, both of which carry cal_extra_hour_start_dt and cal_extra_hour_end_dt style scheduling columns.
Competence validation reads PER_COMPETENCE_ELEMENTS. Resource and department data come from BOM_RESOURCES, BOM_DEPARTMENTS and BOM_DEPARTMENT_RESOURCES, with resource instances in BOM_DEPT_RES_INSTANCES. Employee-to-resource relationships are resolved through BOM_RESOURCE_EMPLOYEES, while shift and calendar logic draws on BOM_RESOURCE_SHIFTS, BOM_SHIFT_DATES, BOM_SHIFT_TIMES and BOM_CALENDAR_DATES. MTL_PARAMETERS supplies organization defaults.
Usage Notes
EAM_ASSIGN_EMP_PUB is invoked primarily from the EAM work order forms when a planner searches for assignable employees. The package is also referenced by one other APPS package. Customizations should call the *_PUB procedures, honor the FND_API return status and message stack, and respect the API version parameter. Callers should pass horizon dates consistent with the source work order, since the cal_extra_hour_* helpers depend on those values when evaluating available and overtime hours.