Results for “ben_elig_inelig”

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

Overview

APPS.BENBV_ELIG_TO_PRTE_RSN_V is a business view (the "BV" prefix denotes a Business View in the Oracle Advanced Benefits / Benefits (BEN) product family) that exposes eligibility-to-participate reason configuration data. It is a read-only view, as confirmed by the WITH READ ONLY clause in its definition, and it is defined over the date-tracked base table BEN_ELIG_TO_PRTE_RSN_F. The view presents rows describing the rules that determine whether a person, or a related party such as a family member, is eligible or ineligible to participate in a benefit program, plan type, plan, option, or program.

Its principal role is to provide a reporting-, integration-, and forms-friendly projection of the eligibility reason configuration. Notably, the view resolves a large number of internal lookup codes into their translated display meanings by calling HR_BIS.BIS_DECODE_LOOKUP for each coded column. This makes the view well suited for extract queries, OBIEE/BIP reports, and integration interfaces where a human-readable value is preferred over a raw code. It also embeds a descriptive flexfield reference token so that a consumer can expand DFF segments for the underlying entity.

Underlying Base Objects

The view is defined over a single documented base object, BEN_ELIG_TO_PRTE_RSN_F (referenced through a synonym), and it depends on the HR_BIS package for lookup decoding. The base table is a date-tracked ("_F") table: each logical rule is stored with EFFECTIVE_START_DATE and EFFECTIVE_END_DATE columns that define its effective-dated validity. The view selects directly from this table (aliased PEO) without joins, so its row cardinality matches the base table's rows for the selected business group.

Row-level security is enforced through the predicate WHERE PEO.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PEO.BUSINESS_GROUP_ID). When the security profile resolves a business group, only that group's rows are returned; otherwise all business groups are visible. The HR_BIS package is referenced both for the security-profile lookup and for the numerous BIS_DECODE_LOOKUP calls that translate codes.

Key Columns

• Effective dating: EFFECTIVE_START_DATE, EFFECTIVE_END_DATE (and rule columns PRTN_EFF_STRT_DT_RL, PRTN_EFF_END_DT_RL) establish when the eligibility rule applies.
• Waiting period: WAIT_PERD_UOM and WAIT_PERD_VAL define the waiting period unit and value; WAIT_PERD_UOM is decoded via the BEN_TM_UOM lookup, and WAIT_PERD_DT_TO_USE_CD/RL govern which date the wait is measured from.
• Maximum period of enrollment: MX_POE_UOM (decoded via BEN_RQD_PERD_ENRT_NENRT_TM_UOM), MX_POE_VAL, MX_POE_RL, and MX_POE_APLS_CD control maximum enrollment-period duration and applicability.
• Family member verification: VRFY_FMLY_MMBR_RL and VRFY_FMLY_MMBR_CD, where the code is decoded against the BEN_FMLY_MMBR lookup — the specific lookup the user search term "ben_fmly_mmbr" refers to.
• Eligibility status: ELIG_INELIG_CD (decoded via BEN_ELIG_INELIG) indicates whether the rule grants eligibility or ineligibility.
• Override controls: PRTN_OVRIDBL_FLAG and IGNR_PRTN_OVRID_FLAG, both decoded via the YES_NO lookup.
• Identity columns: ELIG_TO_PRTE_RSN_ID, BUSINESS_GROUP_ID, LER_ID, PTIP_ID, PLIP_ID, OIPL_ID, PL_ID, and PGM_ID identify the rule and the program/plan hierarchy to which it is attached.
• WHO columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE.
• Descriptive flexfield: a _DF:BEN:BEN_ELIG_TO_PRTE_RSN_F:PEO token supporting DFF segment expansion.

Common Use Cases and Queries

Typical usage includes auditing eligibility rules, reporting family-member verification requirements, and extracting configuration for data migration or interface work. A simple retrieval of active rules for the current business group:

SELECT ELIG_TO_PRTE_RSN_ID, ELIG_INELIG_CD, VRFY_FMLY_MMBR_CD,
       WAIT_PERD_VAL, MX_POE_VAL, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
FROM   APPS.BENBV_ELIG_TO_PRTE_RSN_V
WHERE  TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

To isolate rules requiring family-member verification, filter on VRFY_FMLY_MMBR_CD. Joining to BEN_ELIG_TO_PRTE_RSN_F on ELIG_TO_PRTE_RSN_ID can supply any columns the view decodes or omits. Because the view is read-only and security-scoped, all access remains within the caller's business-group security profile.