Results for “erlst_deenrt_dt”

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

Overview

BEN_WFREP_ELCTBL_CHC_INFO_VW is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined under the BEN (Advanced Benefits) product. Its name indicates its purpose: it consolidates electable choice information for use by the Workflow Reporting (WFREP) subsystem, presenting, for each person and benefit action, the electable choice — program, plan, or option — that the person is eligible to elect. In the Oracle EBS 12.1.1 and 12.2.2 environments, the view is a read-only, VALID database object that places a denormalized, report-friendly interface over the raw batch electable choice data stored in the transaction tables.

The view is structured as a UNION of three branches, distinguished by an ORD_NUM discriminator (1, 2, 3). Branch 1 returns program-level electable choices, branch 2 returns plan-level choices, and branch 3 returns option-level choices. This design allows a single query to retrieve all electable choices across the three levels of the benefits hierarchy without the caller needing to know which level applies. The view also decodes internal lookup codes into their display names, making the output directly consumable by reports and workflows.

Underlying Base Objects

The view is defined over the following documented base objects, all referenced through APPS synonyms: BEN_BATCH_ELCTBL_CHC_INFO, BEN_BENEFIT_ACTIONS, BEN_PGM_F, BEN_PL_F, BEN_OIPL_F, BEN_OPT_F, and the HR_GENERAL package.

• BEN_BATCH_ELCTBL_CHC_INFO (BEC) — the central driving table, holding per-person batch electable choice records, including enrollment dates, eligibility flags, and the program/plan/option foreign keys.
• BEN_BENEFIT_ACTIONS (BFT) — supplies the BENEFIT_ACTION_ID and PROCESS_DATE used to correlate the election to a specific benefit action and to constrain effective-dated lookups.
• BEN_PGM_F (PGM) — the program definition table. In branch 1, PGM.NAME is aliased as both PL_NAME and OPT_NAME and returned as ELECTABLE_CHOICE_NAME.
• BEN_PL_F (PLN) — the plan definition table. In branch 2, PLN.NAME supplies PL_NAME, OPT_NAME, and, where no program exists, ELECTABLE_CHOICE_NAME.
• BEN_OPT_F (OPT) — the option definition table, referenced by branch 3 for option-level electable choices.
• BEN_OIPL_F — the other-input plan table, referenced in the full union for the OIPL_ID branch.
• HR_GENERAL — the package supplying DECODE_LOOKUP, used to translate BEN_ENRT_TYP_CYCL and BEN_COMP_LVL codes into descriptive names.

Joins are effective-dated: the benefit action's PROCESS_DATE is bounded between each base table's EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, ensuring the correct definition version is reported at the time of the action.

Key Columns

• ORD_NUM — discriminator identifying the hierarchy level: 1 = program, 2 = plan, 3 = option.
• BENEFIT_ACTION_ID — foreign key to the benefit action that triggered the election.
• PERSON_ID — the person for whom the electable choice applies.
• PGM_NAME, PL_NAME, OPT_NAME — the resolved names of the program, plan, and option, populated according to the branch.
• ELECTABLE_CHOICE_NAME — the composite display name of the choice; at plan level it is formed as PGM.NAME || ' - ' || PLN.NAME when both exist.
• ENRT_CVG_STRT_DT, ENRT_PERD_STRT_DT, ENRT_PERD_END_DT — enrollment coverage start, enrollment period start, and enrollment period end dates.
• ERLST_DEENRT_DT, DFLT_ENRT_DT — earliest de-enrollment date and default enrollment date.
• ENRT_TYP_CYCL_CD_NAME, COMP_LVL_CD_NAME — decoded lookup meanings for enrollment type/cycle and compensation level.
• MNDTRY_FLAG, DFLT_FLAG — indicators of whether the choice is mandatory and whether it is a default.

Common Use Cases and Queries

The view is typically queried when an administrator or report must display the full set of electable choices associated with a person's benefit action, particularly in workflow-driven enrollment reporting. A basic query returning all choices for a person is:

SELECT ord_num, benefit_action_id, pgm_name, pl_name, opt_name,
       electable_choice_name, mndtry_flag, dflt_flag
FROM   apps.ben_wfrep_elctbl_chc_info_vw
WHERE  person_id = :p_person_id
ORDER  BY ord_num, electable_choice_name;

To restrict output to plan-level electable choices only, add WHERE ord_num = 2. To retrieve mandatory or defaulted elections for a benefit action, filter on MNDTRY_FLAG = 'Y' or DFLT_FLAG = 'Y'. Because the view decodes cycle and compensation codes, it can be used directly as a data source in Oracle Reports and BI Publisher templates without further lookup resolution. Standard practice is to always supply a PERSON_ID or BENEFIT_ACTION_ID predicate, since the union scans the batch electable choice table in full and the search term "pl_name" frequently appears when developers inspect the view text to confirm how the plan name column is derived.