Results for “ben_enrt_used_info_v”

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

Overview

The view APPS.BEN_ENRT_USED_INFO_V is a reporting and integration construct within the Oracle E-Business Suite Advanced Benefits (BEN) module. It is documented as a retrofitted object, meaning its definition was aligned to support the 12.2.2 data model and online patching requirements while retaining backward compatibility with 12.1.1 installations. The view consolidates enrollment information for an eligible person's electable choice, resolving the pre-tax and after-tax benefit values, rates, and benefit provider pools into a single denormalized result set.

The view is designed to answer a specific business question: for a given ELIG_PER_ELCTBL_CHC_ID, what benefit amount and rate is actually in use, distinguishing between situations where an explicit enrollment benefit record exists and situations where only a default rate applies. This makes the view suitable for reporting on current elections, validating enrollment results, and feeding downstream payroll or carrier integration processes.

Underlying Base Objects

The view text references four documented base objects, all exposed to the APPS schema as synonyms:

• BEN_ELIG_PER_ELCTBL_CHC — the driving table, aliased EPE. It supplies the eligibility, plan, program, option, and person-in-life-event context.
• BEN_PER_IN_LER — aliased PIL, providing the person-in-life-event relationship used to tie eligibility to a life event.
• BEN_ENRT_BNFT — aliased B, holding the actual enrolled benefit (value and identifier) where an enrollment exists.
• BEN_ENRT_RT — aliased four times as R1, R2, RB1, and RB2, supplying the pre-tax and after-tax rates for both the non-enrolled and enrolled paths.

All joins are outer joins to EPE, so a row is returned for every electable choice even when no enrollment or rate record exists. The rate joins filter on TX_TYP_CD of PRETAX or AFTERTAX, on DSPLY_ON_ENRT_FLAG = 'Y', and on SPCL_RT_ENRT_RT_ID IS NULL, thereby excluding special rates from the standard used-information result.

Key Columns

• ELIG_PER_ELCTBL_CHC_ID — primary linkage key to the electable choice.
• PL_TYP_ID, PL_ID, OIPL_ID, PGM_ID, PTIP_ID, PLIP_ID — plan type, plan, option-in-plan, program, plan type in program, and plan in program identifiers.
• BNFT_VAL — the enrolled benefit value from BEN_ENRT_BNFT.
• PRE_TAX_VAL and AFTR_TAX_VAL — resolved pre-tax and after-tax amounts, using the default value when an enrolled record exists and the rate value otherwise.
• NET_VAL — the arithmetic sum of the pre-tax and after-tax components, treating NULLs as zero.
• PRE_TAX_RT_ID, AFTR_TAX_RT_ID — the effective rate identifiers.
• PRE_TAX_RT_POOL_ID, AFTR_TAX_RT_POOL_ID — the decreasing benefit provider pool identifiers.
• ENRT_BNFT_ID — the enrollment benefit identifier, NULL when no enrollment exists.
• DFLT_FLAG, PER_IN_LER_ID, PRTT_ENRT_RSLT_ID, BUSINESS_GROUP_ID, MYROW.

Common Use Cases and Queries

The view is typically queried to report current elections with resolved amounts, or to audit which rate was applied. A representative query retrieves the pre-tax and after-tax values for a specific electable choice:

• SELECT elig_per_elctbl_chc_id, bnft_val, pre_tax_val, aftr_tax_val, net_val FROM apps.ben_enrt_used_info_v WHERE elig_per_elctbl_chc_id = :p_id;
• Listing all electable choices for a plan: ... WHERE pl_id = :p_plan AND dflt_flag = 'Y';
• Identifying rows using a specific rate: ... WHERE pre_tax_rt_id = :p_rt OR aftr_tax_rt_id = :p_rt;

Because the definition excludes rows where SPCL_RT_ENRT_RT_ID is populated, the view does not surface special-rate enrollments; those must be obtained directly from BEN_ENRT_RT. Queries should therefore be scoped to standard-rate reporting, and any comparison against special rates should be performed against the base table.