Results for “benbv_opt_v”

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

Overview

BENBV_OPT_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the BEN (Advanced Benefits) product family and is documented in the ETRM reference for both releases with a VALID status. The view identifies compensation alternatives — typically coverage and investment choices — that are provided by one or more benefit plans. In functional terms, it exposes the option definitions maintained through the Benefits configuration UI, translating the underlying translated-table structure into a denormalized, reporting-friendly projection.

The view is intended for reporting and integration rather than transaction processing. It is defined WITH READ ONLY, so consumers cannot perform DML through it. Its primary role is to supply the list of options available under a plan or program, together with their required-period enrollment rules, mapping table associations, and legislation context, allowing downstream reports, extracts, and interfaces to resolve option identifiers into human-readable descriptions.

Underlying Base Objects

The view is defined over a single base table, BEN_OPT_F, accessed in the ETRM metadata through the BEN_OPT_F synonym. BEN_OPT_F is the date-tracked (effective-dated) table that stores option definitions for Advanced Benefits, and the view alias OPT is carried through every selected column.

Two packages are referenced in the SELECT statement. HR_BIS is invoked through its BIS_DECODE_LOOKUP function to translate lookup codes into their display meanings, and through GET_SEC_PROFILE_BG_ID to enforce business-group security. BEN_BIS_UTILS is invoked through GET_GROUP_OPT_NAME to resolve the parent group option name for a given GROUP_OPT_ID and effective date. A descriptive flexfield reference, '_DF:BEN:BEN_OPT_F:OPT', is embedded so that the DFF segments defined on BEN_OPT_F remain available to the consuming tool.

Referenced base objects documented in the ETRM metadata are BEN_BIS_UTILS (PACKAGE), BEN_OPT_F (SYNONYM), and HR_BIS (PACKAGE). The business-group predicate, OPT.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, OPT.BUSINESS_GROUP_ID), means the view automatically filters rows to the business group of the session, or returns all business groups when no security profile is set.

Key Columns

• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracked validity range of each option definition.
• NAME / SHORT_CODE / SHORT_NAME — the option's display name and abbreviated identifiers used in reports.
• RQD_PERD_ENRT_NENRT_RL, RQD_PERD_ENRT_NENRT_UOM, RQD_PERD_ENRT_NENRT_VAL — the required period for enrollment and non-enrollment, expressed as a rule, unit of measure, and value; the UOM column is accompanied by a decoded _M column produced via the BEN_RQD_PERD_ENRT_NENRT_TM_UOM lookup.
• COMPONENT_REASON — the reason associated with the component, decoded through the PROPOSAL_REASON lookup.
• MAPPING_TABLE_NAME / MAPPING_TABLE_PK_ID — the mapping table and its primary key identifier, with a decoded description from the BEN_MAPPING_OPTION_TABLE lookup.
• GROUP_OPTION — the parent group option name, resolved by BEN_BIS_UTILS.GET_GROUP_OPT_NAME from GROUP_OPT_ID.
• LEGISLATION_CODE / LEGISLATION_SUBGROUP — the legislative jurisdiction governing the option.
• INVK_WV_OPT_FLAG — a YES/NO flag decoded via the YES_NO lookup, indicating the invoicing or waiver characteristic of the option.
• OPT_ID, BUSINESS_GROUP_ID, CMBN_PTIP_OPT_ID, MAPPING_TABLE_PK_ID, GROUP_OPT_ID — identifier columns used to join to other Benefits entities.
• WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, and CREATION_DATE.

Common Use Cases and Queries

Typical uses include validating plan-option configuration prior to an enrollment period, populating option lists in a custom interface, and auditing the mapping tables assigned to coverage and investment alternatives.

SELECT name, short_code, group_option,
       rqd_perd_enrt_nenrt_val, rqd_perd_enrt_nenrt_uom_m
FROM   apps.benbv_opt_v
WHERE  legislation_code = 'US'
AND    SYSDATE BETWEEN effective_start_date AND effective_end_date
ORDER  BY name;

A second pattern joins the view to plan-level data via OPT_ID to list the options offered by a specific plan:

SELECT o.name, o.short_name, o.component_reason_m
FROM   apps.benbv_opt_v o
WHERE  o.business_group_id = :p_bg_id
AND    o.group_opt_id IS NOT NULL;

Because the view is read-only and enforces business-group security through HR_BIS, it is safe to expose to reporting schemas and BI Publisher data models without risk of modification of the underlying BEN_OPT_F records.