Results for “ler_chg_dpnt_cvg_rl”

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

Overview

BENBV_LER_CHG_DPNT_CVG_V is a read-only view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the BEN (Advanced Benefits) product. It identifies the valid life event reasons that permit a participant to add or remove dependent coverage. Functionally, the view represents the intersection of dependent eligibility profiles and life event reasons — that is, the configuration rules that determine when a dependent can be added to or removed from plan coverage in response to a qualifying life event.

As part of the BENBV (Benefits views) family, the object is intended for reporting and integration rather than for transactional data entry. It exposes decoded lookup values through calls to HR_BIS.BIS_DECODE_LOOKUP, allowing report authors and interfaces to retrieve human-readable meanings for the various code columns without joining directly to lookup tables. The view is defined WITH READ ONLY and applies row-level security through HR_BIS.GET_SEC_PROFILE_BG_ID, restricting results to the business group associated with the querying user's security profile. This makes it suitable for secure, multi-business-group reporting in both 12.1.1 and 12.2.2 environments.

Underlying Base Objects

The view is defined over a single base object, BEN_LER_CHG_DPNT_CVG_F, which is the life event reason / change dependent coverage rules table in Advanced Benefits. The documented metadata lists BEN_LER_CHG_DPNT_CVG_F as a synonym referenced by the view, along with the HR_BIS package. HR_BIS is not a data source but a PL/SQL package used in the view's SQL; its BIS_DECODE_LOOKUP function resolves code values to their translated meanings, and GET_SEC_PROFILE_BG_ID returns the business group identifier for the current security context.

The WHERE clause of the view is: WHERE LDC.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, LDC.BUSINESS_GROUP_ID). This NVL construct means that when a security profile business group is resolved, only rows for that business group are returned; otherwise, all business groups are visible. The flexfield structure associated with the underlying table is BEN_LER_CHG_DPNT_CVG_F, surfaced through the descriptive flexfield column referenced as "_DF:LDC".

Key Columns

• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effective range controlling when the life event change dependent coverage rule is active.
• LER_CHG_DPNT_CVG_RL — The life event reason rule or rule list governing whether the dependent change is permitted.
• ADD_RMV_CVG_CD / ADD_RMV_CVG_CD_M — Code and decoded meaning indicating whether the life event allows adding coverage, removing coverage, or both. Decoded via lookup type BEN_ADD_RMV.
• CVG_EFF_STRT_CD / CVG_EFF_STRT_CD_M — Code and meaning for the coverage effective start rule. Decoded via lookup BEN_DPNT_CVG_STRT.
• CVG_EFF_END_CD / CVG_EFF_END_CD_M — Code and meaning for the coverage effective end rule. Decoded via lookup BEN_DPNT_CVG_END.
• LER_CHG_DPNT_CVG_CD / LER_CHG_DPNT_CVG_CD_M — Code and decoded meaning for the life event reason change dependent coverage classification. Decoded via lookup BEN_LER_CHG_DPNT_CVG.
• LER_CHG_DPNT_CVG_ID — Primary identifier for the underlying rule row.
• BUSINESS_GROUP_ID, PGM_ID, PL_ID, LER_ID, PTIP_ID — Foreign keys identifying the business group, program, plan, life event reason, and participant eligibility profile respectively.
• WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE (audit information).

Common Use Cases and Queries

Typical uses include auditing which life events permit dependent additions or removals, validating configuration during benefits implementation, and feeding downstream reports or extracts that require decoded meanings.

Sample query listing all valid life event / dependent coverage rules:

SELECT LER_CHG_DPNT_CVG_ID,
       LER_ID,
       ADD_RMV_CVG_CD_M,
       CVG_EFF_STRT_CD_M,
       CVG_EFF_END_CD_M,
       EFFECTIVE_START_DATE,
       EFFECTIVE_END_DATE
FROM   APPS.BENBV_LER_CHG_DPNT_CVG_V
ORDER  BY LER_ID;

A query to inspect only those rules that allow adding dependents:

SELECT LER_CHG_DPNT_CVG_ID,
       LER_ID,
       PGM_ID,
       PL_ID,
       ADD_RMV_CVG_CD_M
FROM   APPS.BENBV_LER_CHG_DPNT_CVG_V
WHERE  ADD_RMV_CVG_CD_M LIKE '%Add%'
AND    SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE, SYSDATE);

Because the view applies business group security automatically, no additional security predicate is required in reporting SQL; the effective date columns should, however, always be constrained for point-in-time accuracy.