Search Results elig_cvrd_dpnt

Overview

APPS.BEN_PRMRY_CARE_PRVDR_D is a reporting and integration view in the Oracle E-Business Suite Benefits module (available in 12.1.1 and 12.2.2). It presents the primary care provider records stored in the BEN_PRMRY_CARE_PRVDR_F table, enriched with the denormalized labels, plan identifiers, and lookup meanings required for downstream consumption. The _D suffix indicates a "descriptive" or denormalized view: rather than exposing raw foreign keys, the view resolves them into human-readable names — the plan name, the primary care provider type meaning, and the FND user name of the last updater. It also handles the case in which a provider is associated with a dependent rather than directly with a plan participant, dynamically selecting the correct plan name via a DECODE on PRTT_ENRT_RSLT_ID. This makes the view suitable for concurrent programs, OAF pages, and ad-hoc reporting where the raw ID-based table would require extensive joins.

Underlying Base Objects

The view is defined over several Benefits and FND objects joined with outer-join ((+)) semantics throughout, so primary care provider rows are preserved even when optional associations are missing:

• BEN_PRMRY_CARE_PRVDR_F — the driving table, holding the effective-dated provider records.
• BEN_PRTT_ENRT_RSLT_F — the participant enrollment result; supplies the participant-level plan (PL).
• BEN_ELIG_CVRD_DPNT_F — the eligible covered dependent, linking the provider to a dependent.
• BEN_PRTT_ENRT_RSLT_F (DPNT_RSLT) — a second, aliased instance of the enrollment result table used for the dependent's own enrollment.
• BEN_PL_F — referenced twice, as PL (participant plan) and DPNT_PL (dependent plan).
• HR_LOOKUPS — resolves PRMRY_CARE_PRVDR_TYP_CD against lookup type BEN_PRMRY_CARE_PRVDR_TYP.
• FND_USER_VIEW — resolves the last updater's USER_ID to a user name.

Date-bounded join conditions ensure each related row is effective at the time of the provider record's EFFECTIVE_START_DATE. HR_API is listed as a referenced object, employed by the underlying table's date-tracking logic.

Key Columns

• ROW_ID — the ROWID of the provider row, giving a unique identifier for update-capable UI layers.
• PRMRY_CARE_PRVDR_ID — primary key of the provider record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — datetrack validity window of the provider entry.
• NAME, EXT_IDENT — the provider's display name and external identifier.
• MEANING — decoded provider type from HR_LOOKUPS.
• DECODE(...) — the plan name, sourced from DPNT_PL.NAME when PRTT_ENRT_RSLT_ID is null, otherwise PL.NAME.
• LAST_UPDATE_DATE, FUSER.USER_NAME — audit columns for traceability.

Common Use Cases and Queries

The view supports reporting on dependent primary care providers, benefit enrollment audits, and integration extracts. Because it includes the dpnt_pl alias for dependent plan resolution, it is commonly queried when reporting on dependents covered under a plan.

• Listing active providers for a participant's plan in a given period.
• Auditing provider type assignments across dependents.
• Feeding external carrier or claims systems with provider names and identifiers.

Sample query:

SELECT name, ext_ident, meaning, effective_start_date, effective_end_date
FROM apps.ben_prmry_care_prvdr_d
WHERE effective_start_date <= SYSDATE
  AND effective_end_date >= SYSDATE
ORDER BY name;

Because every join beyond the base table is outer, the view returns all provider rows regardless of whether a plan, dependent, or enrollment result exists, which is essential when validating incomplete benefit configurations.