Results for “ben_ler_f_vl”

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

Overview

BEN_LER_F_VL is a seeded, read-only view in the APPS schema belonging to the Advanced Benefits (BEN) product family. It presents the translated, user-facing form of the Learn, Educate and Research (LER) entity, which Advanced Benefits uses to model a participant's eligibility for benefit programs and plans. The suffix "VL" follows the Oracle E-Business Suite convention for a "value list" view: it joins a base (non-translated) entity table to its translation table and filters the translation rows to the language of the current session via USERENV('LANG'). As a result, the view exposes one logical LER definition per effective-dated row, with the display name rendered in the runtime language.

In Oracle EBS 12.1.1 and 12.2.2 the object is documented as VALID and is referenced through synonyms, most commonly APPS.BEN_LER_F_VL. It is not a table for transaction processing; its purpose is to provide a denormalized, security-cleared projection of LER data for reporting, Oracle Forms LOVs, and data extraction. The view inherits the effective-dating model of Advanced Benefits, meaning callers must apply SYSDATE predicates against EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to obtain the currently valid row.

Underlying Base Objects

The documented definition of BEN_LER_F_VL is a two-table join:

• BEN_LER_F — the base LER entity table, aliased LER. It stores LER identifiers, effective dates, descriptive policy attributes, WHO columns, and the OBJECT_VERSION_NUMBER used for optimistic locking.
• BEN_LER_F_TL — the translation (TL) table, aliased TL, which stores the language-specific NAME.

The join condition is LER.LER_ID = TL.LER_ID AND LER.EFFECTIVE_START_DATE = TL.EFFECTIVE_START_DATE, with the additional filter TL.LANGUAGE = USERENV('LANG'). Including EFFECTIVE_START_DATE in the join is essential because LER definitions are effectively dated; without it, historical and current translation rows would multiply. In the ETRM metadata both base objects are recorded as SYNONYM references, which is typical of seeded EBS view definitions that resolve through the APPS synonym layer. No columns are sourced from a third table, so the view is a straightforward translation wrapper over BEN_LER_F.

Key Columns

• LER_ID — primary key of the LER definition; the foreign key used by downstream eligibility and program configuration tables.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the effective-dating window for the row.
• NAME — the translated display name from BEN_LER_F_TL, filtered to the session language.
• BUSINESS_GROUP_ID — the business group that owns the definition.
• TYP_CD — the LER type classification.
• CK_RLTD_PER_ELIG_FLAG — indicates whether related-person eligibility is checked.
• LER_EVAL_RL — the rule or evaluation reference applied to the LER.
• CM_APLY_FLAG, OVRIDG_LE_FLAG, QUALG_EVT_FLAG — behavior flags governing communication applicability, life-event override, and qualifying-event handling.
• WHN_TO_PRCS_CD, SLCTBL_SLF_SVC_CD, LER_STAT_CD — processing-timing, self-service selectability, and status codes.
• DESC_TXT, PTNL_LER_TRTMT_CD, TMLNS_EVAL_CD, TMLNS_PERD_CD, TMLNS_DYS_NUM, TMLNS_PERD_RL, OCRD_DT_DET_CD, LF_EVT_OPER_CD — descriptive text, potential LER treatment, and timeliness/life-event evaluation attributes.
• LER_ATTRIBUTE_CATEGORY and LER_ATTRIBUTE1..30 — the standard EBS descriptive flexfield segment columns.
• WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER — audit and concurrency fields.

Common Use Cases and Queries

Typical uses include validation reports listing LER definitions by business group, LOV queries in custom forms and OAF pages, and extracts feeding downstream eligibility analysis or data-warehouse loads. A current-effective query is:

• SELECT ler_id, name, typ_cd, ler_stat_cd FROM apps.ben_ler_f_vl WHERE business_group_id = :p_bg_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date ORDER BY name;
• SELECT l.ler_id, l.name, l.desc_txt, l.ler_eval_rl FROM apps.ben_ler_f_vl l WHERE l.ler_id = :p_ler_id AND SYSDATE BETWEEN l.effective_start_date AND l.effective_end_date;
• SELECT l.ler_id, l.name, l.tmlns_eval_cd, l.tmlns_dys_num FROM apps.ben_ler_f_vl l WHERE l.qualg_evt_flag = 'Y' AND l.ler_stat_cd = 'A';

Because the view performs the translation join, callers should not re-join BEN_LER_F_TL. If the session language has no translation row, the LER will not appear — a frequent cause of "missing rows" in multi-language environments. When historical data is required, omit the SYSDATE predicate and select on effective_start_date and effective_end_date explicitly. Always qualify functions on indexed columns with care, and prefer BETWEEN SYSDATE AND ... patterns to keep the effective-date logic clear and index-friendly.