Results for “cbr_elig_perd_strt_dt”

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

Overview

BENBV_CBR_QUALD_BNF_V is an APPS-owned database view in the Oracle Advanced Benefits (BEN) module, available in E-Business Suite 12.1.1 and 12.2.2. It exposes information about whether a person has elected to participate in COBRA coverage and captures the person's COBRA ineligibility date. The view is designated as read-only (defined with a WITH READ ONLY clause), making it a reporting and integration surface rather than a maintenance object. Its name prefix BENBV follows the Oracle convention for a Benefits view, distinguishing it from the underlying transactional table BEN_CBR_QUALD_BNF.

The view is primarily consumed by concurrent programs, extracts, and custom reports that need to determine COBRA eligibility outcomes for qualified beneficiaries. Because the user search was for cbr_elig_perd_strt_dt, the view is the correct starting point for identifying the COBRA eligibility period start date for a qualified beneficiary.

Underlying Base Objects

The view's SQL selects exclusively from BEN_CBR_QUALD_BNF, accessed through an APPS synonym, aliased as CQB. A secondary dependency is the HR_BIS package, invoked as HR_BIS.BIS_DECODE_LOOKUP to translate coded values into descriptive meanings at runtime.

• BEN_CBR_QUALD_BNF — the base table holding qualified beneficiary records and COBRA eligibility/ineligibility data.
• HR_BIS — supplies lookup decoding for the ineligibility reason and the qualified beneficiary flag.

A critical structural feature is the business group security predicate: WHERE CQB.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, CQB.BUSINESS_GROUP_ID). This restricts rows to the business group resolved from the session's security profile, or returns all business groups when no profile value is set. The view also embeds a descriptive flexfield token '_DF:BEN:BEN_CBR_QUALD_BNF_F:CQB' that surfaces the DFF context for the base record.

Key Columns

• CBR_ELIG_PERD_STRT_DT — the start date of the COBRA eligibility period for the qualified beneficiary. This is the column matched by the search term.
• CBR_ELIG_PERD_END_DT — the end date of the COBRA eligibility period.
• CBR_INELG_RSN_CD — the coded reason for COBRA ineligibility; CBR_INELG_RSN_CD_M provides the decoded meaning via HR_BIS.
• QUALD_BNF_FLAG — indicates whether the person is a qualified beneficiary, with QUALD_BNF_FLAG_M supplying the YES/NO description.
• CBR_QUALD_BNF_ID — primary surrogate key for the qualified beneficiary record.
• QUALD_BNF_PERSON_ID and CVRD_EMP_PERSON_ID — the qualified beneficiary and the covered employee person identifiers.
• PGM_ID, PTIP_ID, PL_TYP_ID — identifiers for the program, plan type in program, and plan type associated with the election.
• BUSINESS_GROUP_ID — the business group used for the security filter.
• WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE) — standard audit tracking fields.

Common Use Cases and Queries

Typical scenarios include COBRA compliance reporting, qualified beneficiary audits, and integration extracts that feed downstream eligibility systems. A representative query listing eligibility periods for a business group follows:

SELECT cbr_elig_perd_strt_dt,
       cbr_elig_perd_end_dt,
       quald_bnf_person_id,
       cvrd_emp_person_id,
       quald_bnf_flag_m,
       cbr_inelg_rsn_cd_m
FROM   apps.benbv_cbr_quald_bnf_v
WHERE  TRUNC(cbr_elig_perd_strt_dt) = TRUNC(SYSDATE);

To find beneficiaries whose COBRA eligibility window is currently open:

SELECT cbr_quald_bnf_id,
       quald_bnf_person_id,
       cbr_elig_perd_strt_dt,
       cbr_elig_perd_end_dt
FROM   apps.benbv_cbr_quald_bnf_v
WHERE  SYSDATE BETWEEN cbr_elig_perd_strt_dt AND cbr_elig_perd_end_dt
AND    quald_bnf_flag = 'Y';

Because the view is read-only and enforces business group security, it is safe for ad hoc reporting. Note that decoding functions execute per row, so high-volume extracts benefit from narrowing on the eligibility date columns or person identifiers before aggregation should be considered.