Search Results ben_wfrep_elig_info_vw




Overview
Explore in AI Chat

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

Overview

BEN_WFREP_ELIG_INFO_VW is a reporting view owned by the APPS schema within the Oracle Advanced Benefits (BEN) product. Its name reflects its intended purpose: it supplies eligibility information to the Benefits Workflow Reporter, consolidating the outcome of batch eligibility processing so that eligibility and ineligibility determinations can be presented to end users, printed on reports, and consumed by workflow notifications. In Oracle EBS 12.1.1 and 12.2.2 the view remains a stable, VALID object in the ETRM dictionary, defined over the same core Benefits tables across both releases.

The view answers a single practical question for a given benefit action and person: at which program, plan, and option-in-plan level was the person found eligible or ineligible, and if ineligible, what explanation was recorded. This makes it a natural source for eligibility audit extracts, open enrollment exception reporting, and workflow messages that must explain why a participant did not receive a given benefit offering.

Underlying Base Objects

The documented base objects referenced by the view are all APPS synonyms: BEN_BATCH_ELIG_INFO, BEN_BENEFIT_ACTIONS, BEN_OIPL_F, BEN_OPT_F, BEN_PGM_F, and BEN_PL_F. BEN_BATCH_ELIG_INFO stores the per-person eligibility results produced by a batch eligibility run, keyed by BENEFIT_ACTION_ID, PERSON_ID, and the level identifiers PGM_ID, PL_ID, and OIPL_ID. BEN_BENEFIT_ACTIONS provides the parent action header, including PROCESS_DATE, which anchors all effective-date lookups. The remaining objects are the standard Benefits date-tracked ( _F ) tables for program, plan, and option-in-plan definitions.

Internally the view is a three-branch UNION ALL, distinguished by the ORD_NUM column. Branch 1 (ORD_NUM = 1) returns program-level eligibility, where PL_ID and OIPL_ID are null. Branch 2 (ORD_NUM = 2) returns plan-level eligibility, joining BEN_PGM_F optionally and BEN_PL_F mandatorily. Branch 3 (ORD_NUM = 3) returns option-in-plan eligibility, additionally joining BEN_OIPL_F and BEN_OPT_F. Each branch constrains the date-tracked rows using BFT.PROCESS_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE, with NVL-based fallbacks at the outer levels.

Key Columns

  • ORD_NUM — Sort discriminator identifying the eligibility level: 1 = program, 2 = plan, 3 = option in plan.
  • ELIG_FLAG — The eligibility outcome for the person at that level (eligible versus ineligible).
  • INELIG_TEXT — Free-text explanation captured when the person is ineligible.
  • NAME — A concatenated display label built with DECODE, combining program, plan, and option names appropriate to the branch.
  • BENEFIT_ACTION_ID — Links the row to the batch eligibility action that produced it.
  • PERSON_ID — The participant to whom the determination applies.
  • PGM_ID, PL_ID, OIPL_ID — Program, plan, and option-in-plan identifiers. Note that OIPL_ID is populated from PGM_ID in branch 1, PL_ID in branch 2, and BEI.OIPL_ID in branch 3, so it always carries the most specific level identifier available.

Common Use Cases and Queries

Typical usage includes reconciliation of batch eligibility output, workflow notification content, and exception reports for participants marked ineligible at any level. Because the view normalises the three hierarchy levels into a uniform shape with ORD_NUM ordering, callers can retrieve a complete eligibility picture for a person with a single query.

To list all eligibility rows for a specific action and person:

  • SELECT ord_num, name, elig_flag, inelig_text, pgm_id, pl_id, oipl_id FROM ben_wfrep_elig_info_vw WHERE benefit_action_id = :p_action_id AND person_id = :p_person_id ORDER BY ord_num;

To identify all option-in-plan level ineligibility reasons for a batch action:

  • SELECT person_id, name, inelig_text FROM ben_wfrep_elig_info_vw WHERE benefit_action_id = :p_action_id AND ord_num = 3 AND elig_flag = 'N';

Because OIPL_ID reflects the most specific level available, it is frequently used as the join key back to option-in-plan configuration; when searching on this column, filter ord_num = 3 to restrict results to true option-level rows.