Results for “ben_eligy_prfl_d”

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

Overview

BEN_ELIGY_PRFL_D is an APPS-owned database view within the Oracle Advanced Benefits (BEN) module, documented as a retrofitted object in ETRM for releases 12.1.1 and 12.2.2. It presents a denormalized, user-readable projection of eligibility profile definitions, joining the core eligibility profile entity to descriptive lookup values and the identity of the last updating user. Its principal purpose is to support reporting and integration queries that require eligibility profile names, descriptions, statuses, and assessment usage in a single, flat result set, without requiring the caller to re-implement the joins to FND_USER and the HR_LOOKUPS lookup views. Because the view resolves lookup codes to their MEANING values, it is well suited to concurrent programs, BI Publisher data templates, and outbound interfaces where numeric or coded values would otherwise need post-processing. The view is read-only; it exposes no DML semantics and must not be used as a target for inserts, updates, or deletes, which must instead be directed at the underlying BEN_ELIGY_PRFL_F table through the supported Advanced Benefits APIs.

Underlying Base Objects

ETRM records BEN_ELIGY_PRFL_F (accessed via synonym), FND_USER (synonym), HR_API (package), and HR_LOOKUPS (view) as the referenced base objects. The view text confirms this: the driving table is BEN_ELIGY_PRFL_F ELP, which stores the effective-dated eligibility profile definition, including the surrogate key ELIGY_PRFL_ID, the date-tracked NAME and DESCRIPTION, the status code STAT_CD, and the assessment code ASMT_TO_USE_CD. FND_USER is outer-joined on LAST_UPDATED_BY to supply the updating user. Two aliases of HR_LOOKUPS are outer-joined to decode lookups: STAT against lookup type 'BEN_STAT' and ASMT against lookup type 'BEN_ASMT_TO_USE'. All joins to FND_USER and HR_LOOKUPS are outer joins, so rows are not lost when a lookup code is unmapped or the updating user record is unavailable. The presence of HR_API in the dependency list reflects the broader Advanced Benefits package stack rather than a direct column reference in the view text.

Key Columns

• ROW_ID — the ROWID of the underlying BEN_ELIGY_PRFL_F row, used as the unique row identifier in the view.
• ELIGY_PRFL_ID — the system-generated primary key of the eligibility profile; the join key to all related eligibility setup tables.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracked validity range of the profile definition.
• NAME / DESCRIPTION — the user-visible profile name and free-text description as defined on the profile.
• STAT_MEANING — the decoded meaning of STAT_CD from lookup type 'BEN_STAT', indicating the profile's status.
• ASMT_TO_USE_MEANING — the decoded meaning of ASMT_TO_USE_CD from lookup type 'BEN_ASMT_TO_USE', identifying which assessment instrument the profile uses. This column directly answers the frequently searched term "ben_asmt_to_use".
• LAST_UPDATE_DATE / LAST_UPDATED_BY — audit columns; LAST_UPDATED_BY is a numeric user identifier resolved against FND_USER.

Common Use Cases and Queries

Typical uses include auditing which assessment type each eligibility profile is configured to use, extracting profile definitions for data migration or environment comparison, and feeding downstream reporting that filters profiles by status. The following query lists active profiles and their assessment usage:

• SELECT eligy_prfl_id, name, effective_start_date, effective_end_date, stat_meaning, asmt_to_use_meaning FROM apps.ben_eligy_prfl_d WHERE stat_meaning = 'Active' ORDER BY name;
• SELECT eligy_prfl_id, name, asmt_to_use_meaning, last_updated_by, last_update_date FROM apps.ben_eligy_prfl_d WHERE effective_end_date = TO_DATE('4712/12/31','YYYY/MM/DD');
• SELECT asmt_to_use_meaning, COUNT(*) FROM apps.ben_eligy_prfl_d GROUP BY asmt_to_use_meaning ORDER BY 2 DESC;

Because the view is a simple join over a date-tracked table, callers should apply their own effective-date predicates when point-in-time results are required; the view itself does not filter by date unless the caller does so. When the assessment meaning is null, inspection of the underlying ASMT_TO_USE_CD and the BEN_ASMT_TO_USE lookup type is warranted, as the outer join will not suppress such rows. All queries should be executed against the APPS schema or a reporting account with appropriate synonyms and privileges.