Results for “frz_age”

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

Overview

APPS.BEN_ELIG_PER_D is a reporting and inquiry view within the Oracle E-Business Suite Advanced Benefits (BEN) module. It exposes the eligibility profile definition rows stored in the BEN_ELIG_PER_F table, but unlike the base table — which stores coded lookup values and foreign keys — BEN_ELIG_PER_D resolves those codes into their human-readable meanings. The view is primarily used to drive the Eligibility Profile Definition form and related descriptive flexfield or report logic, where end users need to read lookup meanings rather than raw codes.

Structurally, BEN_ELIG_PER_D is a denormalized, multi-outer-join view. Nearly every lookup is joined with the Oracle outer join operator (+), meaning the view returns a row for every eligibility profile record even when the corresponding lookup or person record does not exist. This makes it safe to use as the primary driving rowset in reports without losing eligibility profile records.

Underlying Base Objects

The view is defined over the following documented objects:

• BEN_ELIG_PER_F — the eligibility profile fact table and the driving table of the view.
• BEN_LER_F — the Learn, Earn, and Reinforce (LER) record linked to the eligibility profile via LER_ID; supplies LER.NAME as ler_name.
• BEN_PER_IN_LER — the person-to-LER association table.
• PER_ALL_PEOPLE_F — the person master, joined on PERSON_ID.
• FND_USER — resolves LAST_UPDATED_BY to a user name.
• FND_CURRENCIES_TL — joined as COMP_REF to give the compensating reference currency name.
• HR_LOOKUPS — used extensively to translate lookup codes into meanings (HR_LOOKUPS is itself a view, frequently listed alongside the HR_API package).

The heavy reliance on HR_LOOKUPS means the view does not simply map one or two codes; it resolves well over a dozen independent lookup types, including eligibility, age, length of service, and freezing rules.

Key Columns

Columns fall into three functional groups:

• Identity and effective dating: ELIG_PER_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, ROW_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE.
• Eligibility criteria and their meanings: HRS_WKD_VAL and hrs_wkd_bndry_perd_meaning (working hours and the hours/week boundary period), AGE_VAL and age_meaning, LOS_VAL and los_meaning, COMP_REF_AMT with comp_ref_meaning, CMBN_AGE_N_LOS_VAL, OVRID_SVC_DT, and PCT_FL_TM_VAL.
• Freeze and override controls: FRZ_AGE, FRZ_CMP_LVL, FRZ_HRS_WKD (exposed through frz_hrs_wkd_meaning), plus prtn_ovridn_meaning, no_mx_prtn_ovrid_thru_meaning, prtn_ovridn_rsn_meaning, and PRTN_STRT_DT / PRTN_END_DT.

The user's search term, frz_hrs_wkd, corresponds to the FRZ_HRS_WKD lookup code on BEN_ELIG_PER_F and surfaces in the view as frz_hrs_wkd_meaning through the join on HR_LOOKUPS. The HRS_WKD_VAL and hrs_wkd_bndry_perd_meaning columns capture the actual working-hours threshold, while FRZ_HRS_WKD governs whether that threshold is frozen at enrollment.

Common Use Cases and Queries

Typical uses include reporting on eligibility rules for plan enrolment, auditing freeze and override settings, and joining eligibility profiles to compensation references. A representative query filtering on the working-hours freeze lookup is:

SELECT elig_per_id, hrs_wkd_val, hrs_wkd_bndry_perd_meaning, frz_hrs_wkd_meaning
FROM   apps.ben_elig_per_d
WHERE  frz_hrs_wkd_meaning IS NOT NULL
AND    SYSDATE BETWEEN effective_start_date AND effective_end_date;

To audit which users last changed eligibility profiles within a period:

SELECT elig_per_id, ler_name, last_updated_by, last_update_date
FROM   apps.ben_elig_per_d
WHERE  last_update_date >= TRUNC(SYSDATE)-30
ORDER BY last_update_date DESC;

Because the view returns meanings rather than codes, it is well suited to ad-hoc queries and BI Publisher data models, while the underlying BEN_ELIG_PER_F remains preferable for high-volume programmatic processing.