Search Results ben_comp_lvl




Overview
Explore in AI Chat

Overview

APPS.BEN_WFREP_ELCTBL_CHC_INFO_VW is a reporting view in Oracle E-Business Suite Advanced Benefits (Oracle HRMS) that consolidates electable choice information generated during a benefits action. Its name reflects its intended role: it supports the Workflow Reporting (WFREP) layer used to present electable choice data for a person participating in a benefit action. The view flattens the relationship between benefit actions, batch electable choice records, and the program, plan, and option definitions that describe the choice presented to the participant.

The view is defined as a UNION of multiple SELECT branches, each distinguished by an ord_num literal (1, 2, 3). Each branch resolves electable choices at a different level of the benefits hierarchy: the program level, the plan level, and the option-in-plan level. This union design produces a single, presentation-ready result set spanning multiple levels of the benefits structure, which is precisely what a workflow-driven or reporting consumer requires.

In the context of Oracle EBS 12.1.1 and 12.2.2, this is a read-only, APPS-owned object with no user-maintained data. It is consumed by reporting and integration routines rather than used for transactional processing.

Underlying Base Objects

The view is defined over the following documented base objects, all synonyms in the APPS schema:

  • BEN_BATCH_ELCTBL_CHC_INFO — the core electable choice information table, holding the actual choice records per person and benefit action, including enrollment dates, type, and flags.
  • BEN_BENEFIT_ACTIONS — the benefit action header, joined on benefit_action_id and used to constrain rows by process_date.
  • BEN_PGM_F — the program definition, supplying the program name and effective date bounds.
  • BEN_PL_F — the plan definition, supplying plan names and effective date bounds.
  • BEN_OPT_F — the option definition, used in the option-in-plan branch to supply option names.
  • BEN_OIPL_F — the option-in-plan definition, referenced in the view's metadata as a base object.
  • HR_GENERAL — the HRMS utility package, used to decode lookup codes into their display meanings.

Joins are made on identifiers such as benefit_action_id, pgm_id, and pl_id, with effective-date predicates applied between the benefit action's process date and each definition's effective start and end dates. Null handling with outer joins and NVL allows the branch to degrade gracefully when a program is absent.

Key Columns

  • ord_num — literal ordering indicator distinguishing the program (1), plan (2), and option (3) level rows.
  • benefit_action_id — the benefit action to which the choice belongs.
  • person_id — the participant whose electable choice is reported.
  • pgm_name, pl_name, opt_name — the program, plan, and option names, populated per branch.
  • electable_choice_name — the human-readable choice label, concatenating program and plan where both exist.
  • enrt_cvg_strt_dt, enrt_perd_strt_dt, enrt_perd_end_dt — enrollment coverage and period start/end dates.
  • erlst_deenrt_dt — the earliest de-enrollment date.
  • dflt_enrt_dt — the default enrollment date.
  • enrt_typ_cycl_cd_name — decoded BEN_ENRT_TYP_CYCL lookup; this is the column targeted by the search term ben_enrt_typ_cycl.
  • comp_lvl_cd_name — decoded BEN_COMP_LVL compensation level.
  • mndtry_flag, dflt_flag — indicators for mandatory and defaulted choices.

Common Use Cases and Queries

Typical uses include workflow reporting on participant choices, reconciliation of batch electable choice records, and extraction of enrollment type cycle data by person and action.

  • Filtering by enrollment type cycle to audit specific enrollment cycles:
SELECT benefit_action_id, person_id, electable_choice_name,
       enrt_typ_cycl_cd_name, enrt_cvg_strt_dt, mndtry_flag
FROM   apps.ben_wfrep_elctbl_chc_info_vw
WHERE  enrt_typ_cycl_cd_name = 'Annual Enrollment'
ORDER  BY benefit_action_id, person_id, ord_num;
  • Retrieving all electable choices for a given person and action:
SELECT ord_num, pgm_name, pl_name, opt_name,
       electable_choice_name, dflt_flag
FROM   apps.ben_wfrep_elctbl_chc_info_vw
WHERE  person_id = :p_person_id
AND    benefit_action_id = :p_action_id
ORDER  BY ord_num;

Because the view already decodes lookups and concatenates choice labels, it is well-suited for direct reporting consumption without additional joins, and it must always be queried through the APPS schema because it is APPS-owned.