Results for “dpnt_cvg_eligy_prfl”

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

Overview

The view APPS.BEN_ELIG_MRTL_STAT_CVG_D is a reporting and integration object within the Oracle EBS Advanced Benefits (BEN) module, an integral component of Oracle Human Resources. Its designation as a "D" (detail) view, combined with its underlying foreign key relationships, indicates that it serves as a denormalized, user-friendly query interface for data concerning the eligibility of dependents and beneficiaries based on marital status and coverage. The view is owned by the APPS schema and is documented in FND Design Data as BEN.BEN_ELIG_MRTL_STAT_CVG_D, with a status of VALID in Oracle EBS 12.1.1 and 12.2.2. Primarily, it enables technical consultants and developers to retrieve descriptive labels and meanings—such as MRTL_STAT_MEANING and DPNT_CVG_ELIGY_PRFL_NAME—alongside numeric identifiers, thereby abstracting the complexity of the underlying normalized tables. The view is subject to the standard Oracle warning that it is for internal use only and is not supported for direct access by custom applications, except through standard Oracle Applications programs. Nonetheless, its structure makes it a valuable resource for custom reporting, data extraction, and troubleshooting of eligibility profile configurations without needing to manually join multiple base tables.

Underlying Base Objects

According to the documented dependencies, BEN_ELIG_MRTL_STAT_CVG_D is defined over several underlying objects: BEN_DPNT_CVG_ELIGY_PRFL_F, BEN_ELIG_MRTL_STAT_CVG_F, FF_FORMULAS_F, FND_USER, HR_API (a PL/SQL package), and HR_LOOKUPS (a view). In the Oracle EBS schema, these are referenced as synonyms owned by APPS. Specifically, BEN_ELIG_MRTL_STAT_CVG_F is the primary base table that stores the effective-dated eligibility rules linking marital status and coverage options. The view joins it to BEN_DPNT_CVG_ELIGY_PRFL_F to resolve the foreign key to a dependent coverage eligibility profile, thereby exposing the profile name. Additional joins to HR_LOOKUPS translate lookup codes into human-readable meanings for marital status and coverage start/through options. The inclusion of FF_FORMULAS_F and the HR_API package indicates that the view may also incorporate fast formula logic for eligibility determination, though the view itself is read-only data. The FND_USER synonym is likely used to resolve the LAST_UPDATED_BY column, while the HR_API package may be referenced for security or date-tracked logic in the view's WHERE clause or functions.

Key Columns

• ROW_ID (ROWID): The unique physical row identifier, used for direct row access and standard Oracle EBS auditing.
• ELIG_MRTL_STAT_CVG_ID (NUMBER): The primary key from the base table BEN_ELIG_MRTL_STAT_CVG_F, uniquely identifying each eligibility record.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE (DATE): The date-tracked validity range of the eligibility rule, critical for point-in-time queries and historical analysis.
• DPNT_CVG_ELIGY_PRFL_NAME (VARCHAR2(240)): The name of the dependent coverage eligibility profile that defines the criteria for dependents to be eligible for a coverage. This is the most frequently queried descriptive column, as it directly answers the user's search term.
• MRTL_STAT_MEANING (VARCHAR2(80)): The user-friendly meaning of the marital status lookup code (e.g., "Married", "Single") that a participant must have to satisfy the eligibility rule.
• CVG_STRT_MEANING and CVG_THRU_MEANING (VARCHAR2(80)): The meanings of the coverage start and coverage through lookup codes, indicating the effective period of coverage relative to the eligibility event.
• CVG_STRT_NAME and CVG_THRU_NAME (VARCHAR2(80)): The actual lookup codes or names corresponding to the start and through options, useful for filtering by code.
• LAST_UPDATE_DATE and LAST_UPDATED_BY: Standard Who columns for auditing the last modification of the record, with LAST_UPDATED_BY being a foreign key to FND_USER.USER_ID.

Common Use Cases and Queries

This view is typically used in custom reports to list eligibility profiles by marital status and coverage, to audit who last updated a rule, or to export configuration data for migration. A common query to find all profiles associated with a specific marital status might be:

SELECT elig_mrtl_stat_cvg_id,
       dpnt_cvg_eligy_prfl_name,
       mrtl_stat_meaning,
       cvg_strt_meaning,
       cvg_thru_meaning
FROM   apps.ben_elig_mrtl_stat_cvg_d
WHERE  mrtl_stat_meaning = 'Married'
AND    TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Another scenario is to identify recently modified records by joining to FND_USER:

SELECT d.dpnt_cvg_eligy_prfl_name,
       d.mrtl_stat_meaning,
       u.user_name,
       d.last_update_date
FROM   apps.ben_elig_mrtl_stat_cvg_d d,
       applsys.fnd_user u
WHERE  d.last_updated_by = u.user_id
AND    d.last_update_date > SYSDATE - 30;

Because the view consolidates lookup meanings and profile names, it is also useful for data validation when configuring new eligibility profiles, ensuring that only valid marital statuses and coverage options are linked. However, any custom code should adhere to Oracle's warning and avoid direct DML, as the view is read-only and intended for query purposes only.