Search Results ben_plip_f_pk

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.

• PLIP_ID — surrogate primary key component; the unique identifier for the plan-in-program relationship.
• PGM_ID — foreign key to the benefit program (BEN_PGM_F) under which the plan is offered.
• PL_ID — foreign key to the benefit plan (BEN_PL_F) being included.
• BUSINESS_GROUP_ID — the HR business group (legislation/operating unit) owning the row; a standard EBS multi-tenancy key.
• PLIP_STAT_CD and DFLT_FLAG — status of the plan-in-program and whether it is the default selection.
• ENRT_CD, ENRT_MTHD_CD, DFLT_ENRT_CD, and AUTO_ENRT_MTHD_RL — enrollment code, enrollment method, default enrollment, and the automatic enrollment method rules.
• ELIG_APLS_FLAG, ALWS_UNRSTRCTD_ENRT_FLAG, PRTN_ELIG_OVRID_ALWD_FLAG, TRK_INELIG_PER_FLAG — eligibility and enrollability indicators controlling who may participate.
• MN_CVG_AMT, MX_CVG_ALWD_AMT, MX_CVG_INCR_ALWD_AMT, MX_CVG_MLT_INCR_NUM — minimum and maximum coverage amounts, allowed increments, and multiples.
• CMBN_PLIP_ID — self-referencing pointer linking plans that must be combined or restricted together.
• ORDR_NUM, SHORT_CODE, SHORT_NAME, IVR_IDENT — ordering and display/IVR identifiers.
• LEGISLATION_CODE and LEGISLATION_SUBGROUP — legislative context constraining the plan-in-program.
• Audit columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER, and a 30-column CPP_ATTRIBUTE descriptor flexfield set.

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.