Results for “ovrdn_flag”

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

Overview

BENBV_ELIG_CVRD_DPNT_V is an APPS-owned database view within the Oracle Advanced Benefits (BEN) product family. Its documented purpose is to identify those persons designated as dependents by a participant. In Oracle EBS 12.1.1 and 12.2.2, the view functions as a reporting and integration surface over the eligibility-covered-dependent entity, exposing both the surrogate identifiers required by downstream processes and the descriptive flexfield reference needed to retrieve user-defined dependent attributes. Because the underlying structure is date-effective, the view is suited to point-in-time and as-of reporting on dependent coverage, pending coverage, and overridden coverage relationships. The view is defined with a WITH READ ONLY clause, so it is intended strictly for query access rather than DML.

Underlying Base Objects

The view is defined over BEN_ELIG_CVRD_DPNT_F, the date-effective base table that stores covered dependent records, referenced here through a synonym. It also invokes the HR_BIS package, specifically the BIS_DECODE_LOOKUP function, to translate coded flag values into readable meanings. A WHERE clause restricts rows by business group, using NVL(HR_BIS.GET_SEC_PROFILE_BG_ID, PDP.BUSINESS_GROUP_ID), which enforces security profile-based business group filtering when a session-level value is available. The effective date columns on the base table support the standard Oracle date-tracking model, with EFFECTIVE_START_DATE and EFFECTIVE_END_DATE bounding each row.

Key Columns

The view exposes several categories of columns. Effective dating is represented by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, while PROGRAM_UPDATE_DATE records the last programmatic change. Coverage timing is captured by CVG_STRT_DT and CVG_THRU_DT, and the override window is captured by OVRDN_THRU_DT. The override status is carried by OVRDN_FLAG, which is the column most relevant to the user's search term, with its decoded equivalent exposed as OVRDN_FLAG_M via HR_BIS.BIS_DECODE_LOOKUP('YES_NO', ...). The CVG_PNDG_FLAG and CVG_PNDG_FLAG_M columns similarly indicate whether coverage is pending. The virtual column "_DF:PDP" provides the descriptive flexfield context BEN_ELIG_CVRD_DPNT_F:PDP. Identifier columns include ELIG_CVRD_DPNT_ID, DPNT_PERSON_ID, PER_IN_LER_ID, BUSINESS_GROUP_ID, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PRTT_ENRT_RSLT_ID, and ELIG_PER_ELCTBL_CHC_ID. Standard WHO columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE) are also present.

Common Use Cases and Queries

Typical uses include benefits reconciliation, dependent audit reports, and integration extracts that feed carrier or payroll systems. The OVRDN_FLAG and OVRDN_FLAG_M columns support audits of manually overridden dependent coverage, a frequent control requirement. A representative query returning override status for a participant's dependents is:

• SELECT d.DPNT_PERSON_ID, d.ELIG_CVRD_DPNT_ID, d.CVG_STRT_DT, d.CVG_THRU_DT, d.OVRDN_FLAG, d.OVRDN_FLAG_M, d.OVRDN_THRU_DT FROM APPS.BENBV_ELIG_CVRD_DPNT_V d WHERE d.BUSINESS_GROUP_ID = :p_bg_id AND TRUNC(SYSDATE) BETWEEN d.EFFECTIVE_START_DATE AND d.EFFECTIVE_END_DATE AND d.OVRDN_FLAG = 'Y';
• SELECT d.ELIG_CVRD_DPNT_ID, d.DPNT_PERSON_ID, d.CVG_PNDG_FLAG_M FROM APPS.BENBV_ELIG_CVRD_DPNT_V d WHERE d.PRTT_ENRT_RSLT_ID = :p_enrt_rslt_id ORDER BY d.CVG_STRT_DT;
• SELECT d.DPNT_PERSON_ID, d.OVRDN_FLAG_M, d.OVRDN_THRU_DT FROM APPS.BENBV_ELIG_CVRD_DPNT_V d WHERE d.PER_IN_LER_ID = :p_ler_id;

Because security profile filtering is applied inside the view, queries executed under a restricted responsibility automatically return only the business groups the session is authorized to see.