Results for “age_band_fk”

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

Overview

The APPS.EDW_HR_AGE_BAND_PKG package body is a small, single-purpose utility within the Oracle E-Business Suite HRMS (Human Resources) warehouse extraction layer. Its sole documented function, AGE_BAND_FK, resolves a numeric age value into the surrogate primary key of the corresponding age band defined in the Enterprise Data Warehouse (EDW) HR age band dimension. The EDW_ prefix and the hriekagb.pkb header (module "hrie", application "HR Intelligence/EDW") confirm that this object belongs to the HR analytics/warehouse schema rather than to core transactional HR. The function delivers a stable foreign-key value that downstream ETL mappings, BI Publisher reports, and Discoverer/OBIEE extracts use to group employees, applicants, or dependents into standardized age brackets (for example, 18–25, 26–35) without hard-coding band boundaries in every report.

Key Procedures and Functions

• AGE_BAND_FK — A public function that accepts an age (documented as p_age IN NUMBER) and returns the age-band primary key as a VARCHAR2. It opens an internal cursor (age_band_cur) against the age-band lookup view, selects the age_band_pk whose range satisfies age_min <= p_age and p_age < nvl(age_max, p_age + 1), fetches a single row, and returns the value. The function is a narrowly scoped "lookup" routine: no inserts, updates, or deletes are performed, and no parameters are invented beyond the documented single age input.

Tables Accessed

The only object referenced by the cursor is EDW_HR_AGE_BAND_FKV, a foreign-key validation view that exposes the age-band dimension to the HR warehouse. The view supplies age_band_pk, age_min, and nullable age_max columns. The function reads this view exclusively (SELECT only); it does not touch any base HR tables directly, and in ETRM the referenced-tables list for this package is empty because all access is surfaced through the view. The trailing _FKV convention confirms the object is intended for foreign-key validation and reference-data lookup rather than transactional processing.

Usage Notes

Because ETRM records no user-entered parameters and only one function, this package is normally invoked programmatically rather than from a form or concurrent program. Typical callers include HR warehouse ETL mappings, SQL in Discoverer/OBIEE or BI Publisher data models, and the eight other packages documented as referencing it. The handling of a missing band is deliberate: if the cursor returns no row, NVL(l_age_band_pk, 'NA_EDW') returns the sentinel value 'NA_EDW', which signals "not available" to the consuming report. The EXCEPTION WHEN OTHERS block, which re-closes the cursor if still open and repeats the same NVL default, ensures that an unexpected error returns the sentinel rather than propagating an unhandled exception. Developers should therefore treat 'NA_EDW' as a legitimate return value, not an error, and verify that any join to EDW_HR_AGE_BAND_FKV by age_band_pk tolerates it. The version header (120.0, 2005) indicates the object is unchanged since 11i and remains valid in 12.1.1 and 12.2.2.