Results for “benbv_elig_dsbld_prte_v”

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

Overview

BENBV_ELIG_DSBLD_PRTE_V is a Business Intelligence (BI) view owned by the APPS schema within the BEN – Advanced Benefits product family in Oracle E-Business Suite. It is delivered as a read-only view (defined with WITH READ ONLY) and is catalogued in the ETRM documentation with status VALID. Its purpose is to identify which disabled criteria are included or excluded from a given eligibility profile. In Advanced Benefits, an eligibility profile defines the rules that determine whether a person or assignment qualifies for a program, plan, or plan option. One of the criteria types available in that rule engine is the "Registered Disabled" criterion, which is stored in the underlying base table and surfaced through this view. The view therefore plays a reporting and integration role: it exposes the disabled-criterion configuration for each eligibility profile in a decoded, presentation-ready format, allowing downstream reports, extracts, and interfaces to interpret whether a disability criterion is used as an inclusion or an exclusion rule. Because it is a read-only BI view, it is intended strictly for query and extraction, not for DML. Information returned is filtered by the caller's security profile through HR_BIS.GET_SEC_PROFILE_BG_ID, so only business groups visible to the querying session are returned.

Underlying Base Objects

The view is defined over the synonym BEN_ELIG_DSBLD_PRTE_F, the Advanced Benefits table that stores eligibility profile disabled criteria, and the package HR_BIS, which provides decoding and security utilities. In the view text, HR_BIS.BIS_DECODE_LOOKUP is invoked twice, once to translate the DSBLD_CD value against the REGISTERED_DISABLED lookup type, and once to translate EXCLD_FLAG against the YES_NO lookup type. The view also references HR_BIS.GET_SEC_PROFILE_BG_ID in its WHERE clause: WHERE EDB.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, EDB.BUSINESS_GROUP_ID). This pattern enforces business-group level security, returning rows only for the session's secured business group, or all business groups when no security profile value is resolved. Each row in the base table corresponds to one disabled criterion line attached to an eligibility profile.

Key Columns

• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-effective range for the criterion record, supporting date-tracked queries.
• ORDR_NUM — Sequence/order number of the criterion line within the eligibility profile.
• "_DF:EDB" — Descriptive flexfield column marker ('_DF:BEN:BEN_ELIG_DSBLD_PRTE_F:EDB') exposing the DFF context for the base table.
• DSBLD_CD — The stored registered-disabled lookup code.
• DSBLD_CD_M — The decoded, user-facing meaning of the disabled code via BIS_DECODE_LOOKUP.
• EXCLD_FLAG — Flag indicating whether the disabled criterion is excluded from (or included in) the eligibility profile.
• EXCLD_FLAG_M — The decoded YES_NO meaning of the exclusion flag.
• ELIG_DSBLD_PRTE_ID — Primary key identifying the disabled-criterion row.
• ELIGY_PRFL_ID — Foreign key to the parent eligibility profile.
• BUSINESS_GROUP_ID — Owning business group, used for security filtering.
• WHO columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE) — Standard audit columns.

Common Use Cases and Queries

Typical uses include auditing which eligibility profiles reference the registered-disabled criterion, extracting inclusion/exclusion rules for benefits configuration reports, and validating profile setup during implementations or upgrades. Because the view already decodes lookup values and enforces security, it is preferable to querying the base table directly for reporting purposes.

Retrieve all disabled criteria for a specific eligibility profile:

SELECT eligy_prfl_id, ordr_num, dsbld_cd_m, excld_flag_m
FROM apps.benbv_elig_dsbld_prte_v
WHERE eligy_prfl_id = :p_profile_id
ORDER BY ordr_num;

List exclusion-based disabled criteria across all accessible business groups:

SELECT elig_dsbld_prte_id, eligy_prfl_id, dsbld_cd_m
FROM apps.benbv_elig_dsbld_prte_v
WHERE excld_flag = 'Y';

Date-effective query as of a given date:

SELECT eligy_prfl_id, dsbld_cd_m, excld_flag_m
FROM apps.benbv_elig_dsbld_prte_v
WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;