Search Results hrfv_time_in_service_analysis




Overview
Explore in AI Chat

Overview

HRFV_TIME_IN_SERVICE_ANALYSIS is an APPS-owned business view template in the Oracle E-Business Suite Human Resources (PER) module, documented as VALID in ETRM for both 12.1.1 and 12.2.2. It is a flexfield-generated business view template: Oracle's flexfield view generation mechanism derives concrete, effective-dated reporting views from this template so that customers and integrators can expose descriptive flexfield context and segment columns alongside a stable set of descriptive attributes. Its principal purpose is to consolidate, into a single queryable object, the tenure measures that Oracle HR calculates through the HR_DISCOVERER package — service in the enterprise, organization, position, job, grade, and location — together with the assignment, person, and organizational context needed to interpret those measures.

The view is intended for reporting and integration rather than for transactional maintenance. It provides a denormalized, human-readable projection of assignment tenure suitable for operational HR reporting, headcount and retention analysis, and downstream extracts feeding data warehouses or third-party HR analytics tools. The object name searched by the user, "months_in_job", is exposed here as a column derived from the time-in-service computation, which is why this view is commonly returned when that term is searched.

Underlying Base Objects

The documented view is defined over the following base objects: PER_ALL_ASSIGNMENTS_F, PER_PERIODS_OF_SERVICE, PER_PEOPLE_X, HR_ALL_ORGANIZATION_UNITS_TL (referenced twice, once for the assignment organization and once for the business group), HR_LOCATIONS_ALL_TL, PER_JOBS_TL, PER_POSITIONS/HR_ALL_POSITIONS_F_TL, and PER_GRADES_TL. Tenure values are not stored; they are computed at runtime by calling HR_DISCOVERER.TIME_IN, which internally relies on HR_GENERAL and HR_BIS, and row-level access is filtered through HR_SECURITY and HR_PERSON_NAME.

PER_ALL_ASSIGNMENTS_F is the driving table, joined to PER_PERIODS_OF_SERVICE on PERIOD_OF_SERVICE_ID and to PER_PEOPLE_X on PERSON_ID. Job, position, grade, and location are attached through outer joins, so an assignment lacking any of those attributes still appears with a null descriptive name. The translated tables (HR_ALL_ORGANIZATION_UNITS_TL, HR_LOCATIONS_ALL_TL) are restricted to the session language, and the view is further limited to employee assignments via ASSIGNMENT_TYPE = 'E' and to rows whose effective dates include the current date, excluding terminated periods of service.

Key Columns

Common Use Cases and Queries

Typical scenarios include identifying employees approaching a tenure milestone, comparing time-in-job against time-in-grade for promotion eligibility, and extracting assignment tenure into a warehouse.

  • Retrieve employees with more than sixty months in their current job:
SELECT person_name, employee_number, job_name, months_in_job
FROM   apps.hrfv_time_in_service_analysis
WHERE  months_in_job > 60
ORDER BY months_in_job DESC;
  • Compare tenure across dimensions for a business group, and join back to PER_ALL_ASSIGNMENTS_F on ASSIGNMENT_ID for additional attributes. Because tenure is computed per row and HR_SECURITY restricts results to the querying user's security profile, performance is best served by filtering on business group or organization before aggregating.