Results for “quald_bnf_flag_m”

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

Overview

The view BENBV_ELIG_CBR_QUALD_BNF_V is a business-oriented (BV) view owned by the APPS schema within the Oracle Advanced Benefits (BEN) module. Its purpose is to identify which COBRA qualified beneficiary is either included in or excluded from a given eligibility profile. In Oracle EBS 12.1.1 and 12.2.2, eligibility profiles drive enrollment eligibility for programs, plans, and plan types, and COBRA processing requires precise control over which qualified beneficiaries are subject to a given eligibility rule. This view exposes that relationship in a flattened, reporting-friendly form.

The view is a read-only construct, defined with a WITH READ ONLY clause, and is intended for query and reporting purposes rather than transactional data entry. It applies row-level security by filtering on the business group through the HR security profile, positioning it firmly as a reporting and integration artifact rather than a base data-entry object.

Underlying Base Objects

The view is defined over the synonym BEN_ELIG_CBR_QUALD_BNF_F, which is the underlying base table storing the association between an eligibility profile and a COBRA qualified beneficiary, along with the include/exclude flag. The dependency HR_BIS is an APPS package used for business intelligence and security functions.

• BEN_ELIG_CBR_QUALD_BNF_F — the base table holding the eligibility profile to qualified beneficiary mapping rows.
• HR_BIS — supplies GET_SEC_PROFILE_BG_ID for business group security filtering and BIS_DECODE_LOOKUP for decoding the Yes/No lookup.

The view adds a security predicate: WHERE ECQ.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, ECQ.BUSINESS_GROUP_ID), ensuring users only see rows for their secured business group, while falling back to all rows when no security profile is set.

Key Columns

• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-effective range governing when the row is valid.
• ORDR_NUM — ordering sequence for the record.
• "_DF:ECQ" — a descriptive flexfield reference column ('_DF:BEN:BEN_ELIG_CBR_QUALD_BNF_F:ECQ').
• QUALD_BNF_FLAG — the core flag indicating whether the qualified beneficiary is included or excluded (Y/N).
• QUALD_BNF_FLAG_M — the decoded meaning of the flag, produced via HR_BIS.BIS_DECODE_LOOKUP('YES_NO', ECQ.QUALD_BNF_FLAG). This is the column users target when searching for quald_bnf_flag_m.
• ELIG_CBR_QUALD_BNF_ID — the primary identifier of the mapping record.
• ELIGY_PRFL_ID — foreign key to the eligibility profile.
• BUSINESS_GROUP_ID — the business group (security key).
• PTIP_ID — plan type in program identifier.
• PGM_ID — program identifier.
• WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE.

Common Use Cases and Queries

A typical use case is reporting which COBRA qualified beneficiaries are included or excluded for a given eligibility profile, using the decoded flag for readability rather than the raw Y/N value. Reporting users prefer QUALD_BNF_FLAG_M because it presents the lookup meaning directly.

Sample query to list inclusion/exclusion rules for a profile:

• SELECT eligy_prfl_id, quald_bnf_flag, quald_bnf_flag_m, ordr_num, ptip_id, pgm_id FROM apps.benbv_elig_cbr_quald_bnf_v WHERE eligy_prfl_id = :p_profile_id ORDER BY ordr_num;

Filtering active rows for the current date and business group:

• SELECT * FROM apps.benbv_elig_cbr_quald_bnf_v WHERE business_group_id = :p_bg_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

Because the view enforces business group security automatically, downstream reports and integrations can rely on it without re-implementing security logic, while the base table BEN_ELIG_CBR_QUALD_BNF_F remains the appropriate target for any data manipulation operations.