Search Results ben_prmry_care_prvdr_d




Overview
Explore in AI Chat

Overview

BEN_PRMRY_CARE_PRVDR_D is a denormalized reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the BEN (Advanced Benefits) product family. The view exposes primary care provider records captured against participant enrollment results, combining data from the primary care provider entity with plan names, lookup meanings, and audit user information. Its design purpose is to present a flattened, human-readable projection of primary care provider assignments so that concurrent programs, Oracle Business Intelligence Publisher reports, and integration extracts do not need to join the underlying tables themselves.

The view carries a DISTINCT clause, which indicates that the join path across enrollment results, eligible covered dependents, and plan definitions can produce duplicate rows that must be collapsed before presentation. The _D suffix follows standard Oracle EBS naming convention for a documented database view intended for read-only reporting use.

Underlying Base Objects

The view is defined over several base objects, all referenced through APPS synonyms unless otherwise noted. The driving table is BEN_PRMRY_CARE_PRVDR_F, aliased PPR, which stores the effective-dated primary care provider assignment per participant. It is left-outer-joined in several directions.

  • BEN_PRTT_ENRT_RSLT_F (aliases PRTT_ENRT_RSLT and DPNT_RSLT) — the participant enrollment result, the core transaction record in Advanced Benefits. The view reads both the participant's own enrollment result and the dependent's enrollment result.
  • BEN_ELIG_CVRD_DPNT_F — eligible covered dependent records, linking a dependent to a participant enrollment result.
  • BEN_PL_F (aliases PL and DPNT_PL) — plan definitions, used to resolve the plan name for both the participant and the dependent.
  • HR_LOOKUPS — supplies the meaning of the PRMRY_CARE_PRVDR_TYP lookup type.
  • FND_USER_VIEW — resolves the LAST_UPDATED_BY user identifier into a displayable user name.
  • HR_API — this package appears in the documented base object list for the view's dependency chain, generally through date-track or user-name resolution routines.

All joins except the driving table and the lookup join are written as outer joins, so primary care provider rows are retained even where the associated enrollment result, dependent, or plan record is absent.

Key Columns

  • ROW_ID — the ROWID of the BEN_PRMRY_CARE_PRVDR_F row, useful for identifying the exact underlying record.
  • PRMRY_CARE_PRVDR_ID — the surrogate primary key of the primary care provider record.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-track boundaries that define the record's validity window.
  • NAME — the primary care provider's name.
  • EXT_IDENT — an external identifier for the provider, typically a network or registry reference.
  • PRMRY_CARE_PRVDR_TYP_MEANING — the decoded meaning of the provider type lookup code.
  • PLAN_NAME — derived via DECODE on PRTT_ENRT_RSLT_ID: when the participant enrollment result is null, the dependent's plan name is returned; otherwise the participant's plan name is returned.
  • LAST_UPDATE_DATE — audit timestamp of the last modification.
  • LAST_UPDATED_BY — the user identifier, resolved through FND_USER_VIEW to produce USER_NAME.

The search term prtt_enrt_rslt maps directly to the BEN_PRTT_ENRT_RSLT_F join, which is the pivot of this view's structure and its principal filter column.

Common Use Cases and Queries

This view is typically consumed for benefits reporting on provider network assignments, audit reviews of who changed provider data, and extracts feeding carrier or network interfaces. A simple query returning current provider assignments with plan context follows:

SELECT prmry_care_prvdr_id, name, ext_ident, prmry_care_prvdr_typ_meaning, plan_name
FROM apps.ben_prmry_care_prvdr_d
WHERE effective_end_date = TO_DATE('4712-12-31','YYYY-MM-DD');

To locate provider records associated with a specific enrollment result, query against the enrollment result joined back to the view through the provider table, or filter by plan name and effective dates. Because the view already resolves lookup meanings and user names, it removes the need for lookup joins in downstream reports. Note that the DISTINCT clause implies a possible performance cost on large data sets; restricting by EFFECTIVE_START_DATE or PRMRY_CARE_PRVDR_TYP_MEANING is advisable.