Results for “hrfv_person_age_analysis”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

HRFV_PERSON_AGE_ANALYSIS is a business view template in the APPS schema within the PER (Human Resources) module of Oracle E-Business Suite, valid in both release 12.1.1 and 12.2.2. As stated in the ETRM metadata, it is a "business view template from which the flexfield view is generated." In practice, the object supplies the semantic layer that Oracle HRMS uses to expose person age data for reporting, and it serves as a template consumed by the flexfield view generation process. Rather than storing data, the view derives age attributes dynamically from person records, translating date-of-birth values into discrete age bands and a precise age-in-years calculation at query time. It is a read-only analytic object intended for reporting and integration use rather than transactional processing.

Underlying Base Objects

The documented view metadata lists the following referenced objects: HR_ALL_ORGANIZATION_UNITS_TL (synonym), HR_BIS (package), HR_GENERAL (package), HR_PERSON_NAME (package), HR_SECURITY (package), PER_PEOPLE_X (view), and DUAL (synonym). The view text confirms a three-way join across HR_ALL_ORGANIZATION_UNITS_TL (aliased BGRT for the business group name), PER_PEOPLE_X (aliased PEO for person attributes including DATE_OF_BIRTH, PERSON_ID, and BUSINESS_GROUP_ID), and an inline subquery that supplies the current date via DT.TODAY, which is resolved through DUAL. The package references (HR_BIS, HR_GENERAL, HR_PERSON_NAME, HR_SECURITY) support the business intelligence, general utility, name-formatting, and row-level security functions applied elsewhere in the flexfield view generation framework.

Key Columns

The view exposes a business group name and a series of mutually exclusive age-band indicators, each returning 1 or 0 based on TRUNC(MONTHS_BETWEEN(DT.TODAY, PEO.DATE_OF_BIRTH)/12). The bands are: AGE_LESS_THAN_20 (ages 1–19), AGE_20_25, AGE_26_30, AGE_31_35, AGE_36_40, AGE_41_45, AGE_46_50, AGE_51_55, AGE_56_60, AGE_61_65, and AGE_66_70. AGE_MORE_THAN_70 uses a SIGN comparison against 71*12 months. AGE_UNKNOWN returns 1 when DATE_OF_BIRTH is null. AGE_IN_YEARS returns the truncated whole-year age, or 0 when the birth date is null. BUSINESS_GROUP_ID and PERSON_ID provide the identifying keys used to join the view back to the person and organization hierarchy. The user search term "months_between" is central here: MONTHS_BETWEEN is the core date function driving every age-band derivation.

Common Use Cases and Queries

Typical uses include workforce demographic reporting, age-distribution dashboards, retirement-eligibility analysis, and headcount breakdowns by age band. Because AGE_IN_YEARS uses the current date (DT.TODAY), results shift as the reporting date advances. A representative query counts employees per band for a business group:

• SELECT business_group_name, SUM(age_20_25) AS band_20_25, SUM(age_26_30) AS band_26_30, SUM(age_more_than_70) AS over_70 FROM apps.hrfv_person_age_analysis GROUP BY business_group_name;
• SELECT person_id, age_in_years FROM apps.hrfv_person_age_analysis WHERE age_unknown = 0 ORDER BY age_in_years DESC;
• SELECT person_id FROM apps.hrfv_person_age_analysis WHERE age_61_65 = 1 OR age_66_70 = 1 OR age_more_than_70 = 1;

These queries return the derived age metrics for downstream BI Publisher reports, Oracle Discoverer workbooks, or custom PL/SQL integration logic.