Results for “benbv_elig_per_v”

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

Overview

BENBV_ELIG_PER_V is a VALID database view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2, delivered as part of the BEN (Advanced Benefits) product family. The view presents information about persons who have been evaluated by the Advanced Benefits eligibility engine and found eligible, and in some cases ineligible, for a particular program or plan. It acts as a denormalized, reporting-friendly projection of the eligibility determination engine's output, joining the underlying eligibility-per-person data with concurrent-program participation attributes and decoded lookup values.

Because it exposes both raw coded values and their human-readable decoded meanings, BENBV_ELIG_PER_V is well suited for operational reporting, ad hoc queries, extracts, and integration layers that need to answer questions such as which employees qualify for a plan, why an ineligibility determination was made, or what age, length-of-service, hours-worked, and compensation thresholds were applied during the eligibility test.

Underlying Base Objects

The documented base objects referenced by BENBV_ELIG_PER_V are:

• BEN_ELIG_PER_F (exposed through a SYNONYM) — the eligibility-per-person entity that stores the results and parameters used when evaluating a person's eligibility for a program or plan.
• HR_BIS (PACKAGE) — the Business Intelligence System package, invoked here as HR_BIS.BIS_DECODE_LOOKUP to translate stored lookup codes into descriptive values.

BENBV_ELIG_PER_V is defined essentially as a SELECT over BEN_ELIG_PER_F (aliased PEP), with the descriptive flexfield rendered using the "…" marker _DF:BEN:BEN_ELIG_PER_F:PEP. Every code column is passed through HR_BIS.BIS_DECODE_LOOKUP against its corresponding lookup type, so the view effectively layers friendly meaning on top of the base table without requiring downstream code to perform the joins itself.

Key Columns

The view exposes the full eligibility-per-person attribute set, including the parameters evaluated by the eligibility engine and their results:

• Effective dating and ordering: EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, PROGRAM_UPDATE_DATE, and the plan/program ordering keys PL_ORDR_NUM, PLIP_ORDR_NUM, PTIP_ORDR_NUM.
• Eligibility test parameters: AGE_VAL/AGE_UOM, LOS_VAL/LOS_UOM, CMBN_AGE_N_LOS_VAL, HRS_WKD_VAL, PCT_FL_TM_VAL, COMP_REF_AMT/COMP_REF_UOM, and the retention variants (RT_AGE_VAL, RT_LOS_VAL, RT_HRS_WKD_VAL, and related UOMs).
• Waiting period: WAIT_PERD_STRT_DT and WAIT_PERD_CMPLTN_DT.
• Overrides and reasons: OVRID_SVC_DT, PRTN_OVRIDN_RSN_CD, WV_PRTN_RSN_CD, WV_CTFN_TYP_CD, and INELG_RSN_CD, each accompanied by a decoded description.
• Boundary period and flags: HRS_WKD_BNDRY_PERD_CD and RT_HRS_WKD_BNDRY_PERD_CD, plus flags such as DSTR_RSTCN_FLAG, DPNT_OTHR_PL_CVRD_RL_FLAG, and PL_HGHLY_COMPD_FLAG.
• Descriptive flexfield: the BEN_ELIG_PER_F DFF is exposed via the standard DFF notation.

Common Use Cases and Queries

Typical uses include eligibility audits, enrollment troubleshooting, and feeding downstream interfaces with eligible populations.

• Eligible persons for plans: query effective-dated rows where INELG_RSN_CD is null.
• Ineligibility analysis: group by INELG_RSN_CD to explain why persons fail eligibility.
• Threshold reporting: report AGE_VAL, LOS_VAL, HRS_WKD_VAL, and PCT_FL_TM_VAL to explain qualification criteria.
• Waiting period tracking: use WAIT_PERD_STRT_DT and WAIT_PERD_CMPLTN_DT for benefit-commencement projections.

A representative query:

SELECT e.effective_start_date, e.effective_end_date,
  HR_BIS.BIS_DECODE_LOOKUP('BEN_TM_UOM', e.age_uom) age_unit,
  e.age_val, e.los_val, e.hrs_wkd_val,
  HR_BIS.BIS_DECODE_LOOKUP('BEN_INELG_RSN', e.inelg_rsn_cd) inelig_reason
FROM apps.benbv_elig_per_v e
WHERE TRUNC(SYSDATE) BETWEEN e.effective_start_date AND e.effective_end_date;

Because the DFF is included, applications must use the standard DFF access APIs rather than reading the DFF segment values directly.