Results for “benbv_per_eligibility_v”

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

Overview

BENBV_PER_ELIGIBILITY_V is a VALID database view owned by the APPS schema within the BEN (Advanced Benefits) product of Oracle E-Business Suite, applicable to releases 12.1.1 and 12.2.2. The view exposes eligibility participation and restriction criteria stored against benefit programs and plans, presenting both the raw coded values and their decoded, user-facing descriptions in a single flattened result set. Its principal role is to support reporting, data extraction, and integration interfaces that must translate the internal BEN lookup codes into readable business terminology without requiring the consuming process to join to the lookup infrastructure directly.

The "BV" prefix conventionally indicates a business view intended for customer-facing or downstream consumption rather than an internal concurrent program query. It is a read-only construct, so it is safe to use in custom reports, extracts, and integration queries.

Underlying Base Objects

The documented ETRM 12.2.2 metadata lists the referenced base objects as BEN_ELIG_PER_F, BEN_ELIG_PER_OPT_F (both exposed as synonyms), and the HR_BIS package. The two BEN tables are the eligibility participation and eligibility participation option tables in the Advanced Benefits schema, which hold the configured eligibility rules for a program or plan. This view selects from those tables and applies HR_BIS.BIS_DECODE_LOOKUP to convert stored codes into descriptions.

The HR_BIS package is the decoding utility. Each lookup call follows the pattern HR_BIS.BIS_DECODE_LOOKUP('BEN_<LOOKUP_TYPE>', <COLUMN>), pulling the meaning associated with the code from the BEN lookup types such as BEN_TM_UOM, BEN_BNDRY_PERD, BEN_WV_PRTN_RSN, BEN_PRTN_OVRRIDN_RSN, BEN_WV_PRTN_CTFN_TYP, BEN_INELG_RSN, BEN_ONCE_R_CNTNG, and YES_NO. Because the view is defined with these decode calls, output is already multilingual-aware and consistent with the application's displayed values.

Key Columns

• PL_WVD_FLAG — the plan waived flag, decoded through BIS_DECODE_LOOKUP('YES_NO', PL_WVD_FLAG). This is the column referenced by the search term "pl_wvd_flag" and indicates whether the plan has been waived for the participant or eligibility record.
• ELIG_FLAG — eligibility indicator, also decoded as Yes/No.
• Effective and period dates — EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, PRTN_STRT_DT, PRTN_END_DT, WAIT_PERD_STRT_DT, and TO_CHAR(WAIT_PERD_CMPLTN_DT), which define the temporal validity of the eligibility rule.
• Ordering columns — PL_ORDR_NUM, PLIP_ORDR_NUM, PTIP_ORDR_NUM, used to sequence programs, plan-in-program, and plan-type-in-program entries.
• Rule value columns — AGE_VAL, AGE_UOM, LOS_VAL, LOS_UOM, HRS_WKD_VAL, COMP_REF_AMT, PCT_FL_TM_VAL, and their RT_ (related) counterparts, representing the eligibility test quantities and units.
• Decoded lookup columns — AGE_UOM, LOS_UOM, HRS_WKD_BNDRY_PERD_CD, WV_PRTN_RSN_CD, INELG_RSN_CD, PRTN_OVRIDN_RSN_CD, and the various flags, each paired with a BIS_DECODE_LOOKUP description.

Common Use Cases and Queries

Typical scenarios include auditing which plans are waived for a population, extracting eligibility rules for a benefits data warehouse, and troubleshooting why a participant was ineligible. A sample query filtering on the waived flag is:

SELECT pl_wvd_flag, elig_flag, prtn_strt_dt, prtn_end_dt, inelg_rsn_cd
FROM   apps.benbv_per_eligibility_v
WHERE  pl_wvd_flag = 'Y'
AND    sysdate BETWEEN effective_start_date AND effective_end_date;

Because decoded columns are characters, filtering on the raw code column (for example, PL_WVD_FLAG) returns the untranslated value, whereas the decoded column returns the description. Joins to BEN_ELIG_PER_F or BEN_ELIG_PER_OPT_F are generally unnecessary since the view already contains the participation-level data.