Results for “ben_ler_v”

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

Overview

BEN_LER_V is a PL/SQL view owned by the APPS schema in Oracle E-Business Suite, defined within the BEN (Advanced Benefits) product module. The name follows the standard Oracle EBS multilingual view convention, where the "_V" suffix denotes a view that joins a base ("_F") table to its translation ("_TL") table and resolves the appropriate language row for the current session. In this case, BEN_LER_V presents data from the Learner Eligibility Rule (LER) entity, exposing both the operational attributes of the rule and its language-dependent name. The view is documented as VALID in both ETRM 12.1.1 and 12.2.2.

The view's principal role is to provide a session-aware, language-filtered projection of benefits learner eligibility rules, which govern whether a participant qualifies to receive benefits based on learning, training, or related qualification activities. Reports, concurrent programs, and integrations that need to display or evaluate eligibility rules select from BEN_LER_V rather than querying the underlying tables directly, ensuring that the correct translated name and the correct date-effective row are returned for the current user session.

Underlying Base Objects

The view is defined over three documented base objects, all accessed through synonyms in the APPS schema:

• BEN_LER_F — the base (non-translated) table holding the learner eligibility rule records, aliased as LER.
• BEN_LER_F_TL — the translation table holding the language-specific NAME for each rule, aliased as TL.
• FND_SESSIONS — the session table, aliased as SE, used to resolve the current session's effective date and language.

The join conditions are significant: the view restricts results to rows where the session effective date falls between the rule's EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, matches LER.LER_ID and EFFECTIVE_START_DATE to the corresponding translation row, and filters the translation by TL.LANGUAGE = USERENV('LANG'). This design yields exactly one date-effective, correctly translated row per rule per session.

Key Columns

BEN_LER_V exposes the complete column set of the underlying rule table plus the translated name. Key columns include:

• ROW_ID — the physical ROWID of the BEN_LER_F row, commonly used for update/delete operations.
• LER_ID — the primary key of the learner eligibility rule.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective range for the rule version.
• NAME — the translated rule name sourced from BEN_LER_F_TL.
• BUSINESS_GROUP_ID — the business group that owns the rule.
• TYP_CD — the rule type code.
• CK_RLTD_PER_ELIG_FLAG, QUALG_EVT_FLAG, CM_APLY_FLAG, OVRIDG_LE_FLAG — controlling flags for related-person eligibility, qualifying events, compensation application, and override behavior.
• WHN_TO_PRCS_CD, SLCTBL_SLF_SVC_CD, LER_STAT_CD — processing timing, self-service selectability, and rule status codes.
• LER_EVAL_RL — the evaluation rule expression applied to determine eligibility.
• DESC_TXT, SHORT_NAME, SHORT_CODE — descriptive and identifier attributes.
• LER_ATTRIBUTE_CATEGORY and LER_ATTRIBUTE1 through LER_ATTRIBUTE30 — the standard Oracle EBS descriptive flexfield (DFF) columns.
• Standard WHO/audit columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

Typical uses include reporting on active eligibility rules, validating rule configuration during implementations, and extracting rules for integration with external eligibility engines. Because the view self-filters by session date and language, callers normally need not add date or language predicates, though adding a BUSINES_GROUP_ID filter is recommended in multi-organization environments:

SELECT ler_id, name, typ_cd, ler_stat_cd,
       effective_start_date, effective_end_date
FROM   apps.ben_ler_v
WHERE  business_group_id = :p_business_group_id
AND    ler_stat_cd = 'A'
ORDER BY name;

To inspect the evaluation logic and flexfield attributes for a specific rule:

SELECT ler_id, name, ler_eval_rl, ler_attribute_category,
       ler_attribute1, ler_attribute2
FROM   apps.ben_ler_v
WHERE  ler_id = :p_ler_id;

To confirm the translation and date-effective version currently visible to a session:

SELECT ler_id, name, short_name, short_code
FROM   apps.ben_ler_v
WHERE  UPPER(name) LIKE UPPER('%' || :p_search_text || '%');

Because BEN_LER_V returns a ROW_ID corresponding to the base table row, it is also suitable for building read-only maintenance pages where the ROW_ID is passed back for subsequent DML against BEN_LER_F. In all cases, callers should treat the view as read-only and perform any insert, update, or delete operations against the underlying base table using the API layer provided by the Advanced Benefits module.