Search Results ben_dpnt_cvg_strt




Overview
Explore in AI Chat

Overview

APPS.BENBV_LER_CHG_DPNT_CVG_V is a business-group-secured read-only view in the Oracle E-Business Suite Benefits (BEN) module. It exposes configuration data for the Dependent Coverage change rules defined at the Life Event Reason (LER) level. In the Benefits setup model, a life event (such as marriage, birth, or loss of coverage) can permit a participant to add, remove, or modify dependent coverage. The rules that govern which dependent coverage options are available when a specific life event reason occurs are stored in the base table BEN_LER_CHG_DPNT_CVG_F. This view surfaces those rules in a flattened, user-friendly format by decoding the underlying lookup codes into their display meanings and by exposing the descriptive flexfield marker.

The view is intended for reporting, integration, and analytics rather than for transactional data entry. It is named with the "BENBV" prefix convention used for Benefits business views, and it enforces multi-tenancy through the business group security profile. The object is documented in ETRM for both Oracle EBS 12.1.1 and 12.2.2, and the definition is consistent across those releases.

Underlying Base Objects

The view is defined over a single base object: BEN_LER_CHG_DPNT_CVG_F, which is accessed through a SYNONYM. No joins to other tables are present; all logic is contained within the SELECT list and the WHERE clause.

  • BEN_LER_CHG_DPNT_CVG_F — the base configuration table storing one row per dependent coverage change rule attached to a life event reason. Accessed via synonym and aliased as LDC.
  • HR_BIS — a package referenced in the SELECT list. Two of its elements are used: the function BIS_DECODE_LOOKUP, which translates stored lookup codes into their lookup meanings, and the function GET_SEC_PROFILE_BG_ID, which returns the business group from the user's security profile.

The WHERE clause applies the predicate LDC.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, LDC.BUSINESS_GROUP_ID). This restricts returned rows to the business group of the querying user when a security profile is set, while allowing all business groups when no profile is defined. The view carries the WITH READ ONLY clause, so no DML is permitted through it.

Key Columns

The columns fall into four logical groups: date ranges, code/decode pairs, identifiers, and WHO audit columns.

The literal '_DF:BEN:BEN_LER_CHG_DPNT_CVG_F:LDC' in the SELECT list signals the presence of a descriptive flexfield defined on the base table, enabling tools to render DFF segments.

Common Use Cases and Queries

Typical uses include auditing life event dependent coverage rules, validating that a life event reason is correctly configured to permit adding or removing dependents, and feeding downstream integrations or extracts with decoded rule values.

Sample query listing rules for a given life event reason with decoded values:

  • SELECT ler_chg_dpnt_cvg_id, ler_id, cvg_eff_strt_cd, add_rmv_cvg_cd, effective_start_date, effective_end_date FROM apps.benbv_ler_chg_dpnt_cvg_v WHERE ler_id = :p_ler_id;
  • SELECT ler_chg_dpnt_cvg_id, ler_id, cvg_eff_strt_cd FROM apps.benbv_ler_chg_dpnt_cvg_v WHERE cvg_eff_strt_cd = 'SOME_CODE';
  • SELECT COUNT(*) FROM apps.benbv_ler_chg_dpnt_cvg_v WHERE effective_end_date = TO_DATE('4712/12/31','YYYY/MM/DD');

Because the view applies the business group security predicate automatically, no additional BUSINESS_GROUP_ID filter is required. Organizations should account for effective-dating: queries for current configuration should constrain on the effective date range to avoid returning historical rule versions.