Search Results vacancy_ageing_suph

Overview

APPS.HRI_OLTP_PMV_DYNAMIC is a PL/SQL package that belongs to the Oracle Human Resources Intelligence (HRI) family of packages used by the Oracle E-Business Suite (EBS) iRecruitment and Human Resources product families. The package is compiled with AUTHID CURRENT_USER, meaning its SQL statements execute under the privileges of the invoking schema rather than the definer, which is significant here because the package is intended to be called from the Oracle Daily Business Intelligence (DBI) and Payroll Management Viewer (PMV) presentation layers.

Functionally, the package provides dynamic, parameter-driven analytic calculations across a set of staffing metrics: Vacancy Ageing, Time to Fill, Time to Hire, and Time from Accept to Hire. Each metric is exposed through multiple variants distinguished by the suffixes _ORGH (organization hierarchy) and _SUPH (supervisor hierarchy), plus variants prefixed VAC_ (vacancy-based) and APL_ (applicant-based). These variant names reflect the different organizational perspectives from which the HR metric may be calculated — by manager hierarchy or by organizational hierarchy — so that the same KPI can be presented to different audiences without duplicating logic.

Key Procedures and Functions

The ETRM metadata documents 47 procedures and functions in this package. All are functions returning VARCHAR2, and all accept a single input parameter, p_params_tbl, of type BIS_PMV_PAGE_PARAMETER_TBL. The user search term "bis_pmv_page_parameter_tbl" maps directly to this parameter type, which is the standard parameter-collection type used by the Oracle Daily Business Intelligence PMV (Page Metric View) framework to pass page context — date ranges, organization IDs, and similar dimensions — into dynamic metric functions.

The documented functions fall into four metric families:

• Vacancy Ageing: VACANCY_AGEING_SUPH, VACANCY_AGEING_ORGH — compute how long open vacancies have remained unfilled, viewed from the supervisor or organizational hierarchy.
• Time to Fill: VAC_TIME_TO_FILL_ORGH, VAC_TIME_TO_FILL_SUPH, APL_TIME_TO_FILL_ORGH, APL_TIME_TO_FILL_SUPH — measure the elapsed interval between requisition opening and placement.
• Time to Hire: APL_TIME_HIRE_ORGH, VAC_TIME_HIRE_ORGH, APL_TIME_HIRE_SUPH, VAC_TIME_HIRE_SUPH, TIME_TO_HIRE_ORGH, TIME_TO_HIRE_ORGH_1, TIME_TO_HIRE_ORGH_2, TIME_TO_HIRE_SUPH — report the interval from requisition or posting to hire, with the numbered ORGH variants providing alternate calculation definitions.
• Time from Accept to Hire: APL_ACCPT_HIRE_ORGH, APL_ACCPT_HIRE_SUPH, VAC_ACCPT_HIRE_ORGH, VAC_ACCPT_HIRE_SUPH, TIME_ACCPT_HIRE_ORGH, TIME_ACCPT_HIRE_SUPH — measure the gap between an applicant's acceptance and actual hire date.

Tables Accessed

The ETRM extract does not list explicit base tables for this package; the documented objects are addressed through APPS synonyms and the underlying HRI OLTP staging and summary tables used by the Recruitment and HR analytics schemas. In practice, these functions read from the HRI recruitment fact and dimension tables that store requisition, applicant, vacancy, and hire-event data. Because the package is AUTHID CURRENT_USER, the actual table resolution depends on the invoking schema's synonym set.

Usage Notes

HRI_OLTP_PMV_DYNAMIC is not intended for direct business use. It is invoked by the Daily Business Intelligence PMV rendering engine whenever a PMV page requests a recruitment metric. The page definition passes a BIS_PMV_PAGE_PARAMETER_TBL collection containing runtime prompt values (for example period, organization, or supervisor), and the corresponding function returns a formatted VARCHAR2 result that the PMV framework places on the page. The package is not referenced by any other packages per the documented dependency metadata, confirming its role as a terminal presentation-layer calculator.

Customization should be limited to extending or wrapping these functions rather than modifying the delivered package, since Oracle supplies it through the HRI product patches and any direct changes may be overwritten during upgrades or patching. When diagnosing incorrect KPI values, the first step is to confirm that the calling PMV page has populated BIS_PMV_PAGE_PARAMETER_TBL correctly, because all 47 functions depend entirely on that parameter collection and return a null or error string if the expected parameters are absent.