Search Results ord_num




Overview
Explore in AI Chat

Overview

APPS.BEN_WFREP_ELIG_INFO_VW is an Oracle E-Business Suite view that consolidates eligibility determination results produced by the Oracle Advanced Benefits batch eligibility engine. It serves as the data source behind Workflow-based eligibility notification reports, presenting one row per person per program, plan, or option-in-plan combination for which an eligibility or ineligibility outcome has been recorded. The view is exposed under the APPS schema and is intended for reporting, integration, and diagnostic purposes rather than transactional maintenance.

The most striking characteristic of the view is the synthetic ORD_NUM column, which is the object of the search term "ord_num". This literal integer is not derived from any base table; it is hard-coded in each branch of the defining UNION ALL to enforce a deterministic presentation order across the three levels of benefit hierarchy.

Underlying Base Objects

The ETRM metadata documents the following referenced base objects, all exposed as synonyms in the APPS schema:

The view is a three-way UNION ALL. The first branch selects rows at program level only (PL_ID and OIPL_ID null). The second branch covers plan-level rows (PL_ID populated, OIPL_ID null), outer-joining BEN_PGM_F so a plan may appear with or without a parent program, and concatenating program and plan names. The third branch covers option-in-plan rows (both PL_ID and OIPL_ID populated), again outer-joining the program and building a three-part hierarchical name. Each branch filters BEN_BENEFIT_ACTIONS.PROCESS_DATE against the effective start and end dates of the program and plan rows, which is what makes the view effective-date aware.

Key Columns

  • ORD_NUM — constant 1, 2, or 3 indicating program, plan, or option level. Used for ORDER BY and hierarchy labeling.
  • ELIG_FLAG — the eligibility outcome (eligible/ineligible) from BEN_BATCH_ELIG_INFO.
  • INELIG_TEXT — the reason text explaining an ineligible determination.
  • NAME — a concatenated, level-appropriate label (program, program - plan, or program - plan - option).
  • PERSON_ID — the person for whom eligibility was evaluated.
  • BENEFIT_ACTION_ID — links back to the batch benefit action that produced the row.
  • PGM_ID, PL_ID, OIPL_ID — the program, plan, and option-in-plan identifiers. Note the view projects PGM_ID into PL_ID and OIPL_ID in the first branch to keep a uniform column set.

Common Use Cases and Queries

Typical usage is to report the eligibility decisions from a given batch action and to sort them by level. A representative query is:

  • SELECT person_id, ord_num, name, elig_flag, inelig_text FROM ben_wfrep_elig_info_vw WHERE benefit_action_id = :p_action ORDER BY person_id, ord_num;
  • Filtering to ineligible outcomes: WHERE elig_flag = 'N' AND ord_num = 1 to isolate program-level failures.
  • Reconstructing a person's hierarchical eligibility path: WHERE person_id = :p_person ORDER BY ord_num.

Because the view is read-only and effective-dated through the benefit action process date, it is safe to embed in concurrent programs, BI Publisher data templates, and workflow notification queries.