Search Results ben_plip_f




Overview
Explore in AI Chat

Overview

BEN_PLIP_F is a core configuration table in the Oracle Advanced Benefits (BEN) module, delivered as part of Oracle E-Business Suite releases 12.1.1 and 12.2.2. The table name denotes a "Plan in Program" (PLIP) definition: it records the incidence, or occurrence, of a specific benefit plan within a benefit program. Where BEN_PGM_F defines the program and BEN_PL_F defines the plan, BEN_PLIP_F captures the operational rules that govern how that plan behaves when offered under that particular program — eligibility application, enrollment defaults, coverage limits, rates, and enrollment method controls.

The table is date-tracked using EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, reflecting the datetrack (DT) pattern common to Oracle HRMS and Benefits date-effective entities. Its primary key, BEN_PLIP_F_PK, is a composite surrogate key on (PLIP_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE), where PLIP_ID is a system-generated identifier unique to each plan-in-program row.

The ETRM metadata classifies this object heuristically as "standalone" within a Data Vault modeling scheme. In practice, BEN_PLIP_F functions as a satellite attached to the plan and program hubs (BEN_PL_F and BEN_PGM_F), holding the descriptive, date-effective attributes that qualify the association. This classification is offered only as a modeling suggestion; the physical implementation is a standard EBS datetracked table, not a Data Vault construct.

Key Information Stored

BEN_PLIP_F holds 100 documented columns. The most significant are described below.

The unique index BEN_PLIP_F_PK (PLIP_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) is the business-key candidate; there is no separate natural unique key such as (PGM_ID, PL_ID) documented, though prior rows are versioned by the effectivity dates.

Common Use Cases and Queries

Typical usages include eligibility analysis, enrollment method auditing, coverage-limit reporting, and remediation of configuration drift. A representative query returns the active plans for a program as of the current date:

  • SELECT plip_id, pgm_id, pl_id, plip_stat_cd, enrt_mthd_cd, mn_cvg_amt, mx_cvg_alwd_amt FROM ben.ben_plip_f WHERE pgm_id = :p_pgm AND SYSDATE BETWEEN effective_start_date AND effective_end_date AND plip_stat_cd = 'A';
  • Joining to BEN_PL_F for plan names: SELECT p.name, p.pl_id, i.enrt_mthd_cd FROM ben.ben_pl_f p, ben.ben_plip_f i WHERE p.pl_id = i.pl_id AND i.pgm_id = :p_pgm;
  • Coverage-limit exceptions: filter where MX_CVG_ALWD_AMT is populated but MX_CVG_RL is null, or where MN_CVG_AMT exceeds MX_CVG_ALWD_AMT.
  • Default-enrollment auditing: select rows where DFLT_ENRT_CD or AUTO_ENRT_MTHD_RL is set, to verify automatic enrollment behavior before open enrollment.
  • Combined-plan validation: self-join on CMBN_PLIP_ID to identify linked or restricted plan pairs.

Because these attributes drive the enrollment engine, any query that prepares an open-enrollment extract should filter on effective dates to select the version in force.

Related Objects

The most significant related objects in the BEN schema are:

  • BEN_PGM_F — parent program definition; joined on PGM_ID.
  • BEN_PL_F — parent plan definition; joined on PL_ID.
  • BEN_PLIP_F (self) — joined on CMBN_PLIP_ID for combined/limited plan relationships.
  • BEN_PGM_ENRT_RL_F — program-level enrollment rules that complement the plan-in-program controls.
  • BEN_PL_TYP_F and BEN_PL_F — plan type context reached via PL_ID.
  • BEN_PER_PLIP_F — person-level enrollment records generated from this plan-in-program configuration.
  • BEN_ELIG_* eligibility definition tables, referenced indirectly through the eligibility flags.
  • Open enrollment and enrollment-posting concurrent programs read BEN_PLIP_F to derive valid choices at processing time.

Foreign key values are resolved against BEN_PGM_F (PGM_ID), BEN_PL_F (PL_ID), and the HR business group (BUSINESS_GROUP_ID), while the CMBN_PLIP_ID self-reference links compatible plan occurrences. Collectively, these relationships establish BEN_PLIP_F as the central configuration point for how each plan behaves inside each program.