Search Results ben_cmbn_plip_f




Overview
Explore in AI Chat

Overview

BEN_CMBN_PLIP_F is a table in the BEN schema (Oracle Advanced Benefits) in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the definition of combinations of plans within a program that may provide credits to enrolling participants. In other words, when a participant enrolls in a specific set of plans offered by a program, the combination defined in this table can trigger a credit — typically a flex credit or similar benefit credit — that is applied to the participant's enrollment.

The table carries the standard EBS effective-dating columns (EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) and the standard WHO/audit columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER), indicating that it is a date-tracked, non-transactional setup/definition entity rather than a transactional record. The documented primary key BEN_CMBN_PLIP_F_PK is composed of CMBN_PLIP_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, so the same combination identifier can have multiple effective-dated versions. Under the heuristic Data Vault classification provided in the metadata, this object is a standalone — that is, it is modeled as a hub-like standalone definition table with no mined foreign-key links to other hubs from its FK structure, and its business attributes behave like satellite data over the effective-dated key. This classification should be treated as a modeling suggestion rather than a strict dependency statement.

Key Information Stored

The table contains 43 documented columns. The most significant of the documented columns are the following:

  • CMBN_PLIP_ID — the primary identifier for a plan-in-program combination. It is part of the composite primary key and is the surrogate key used to reference a combination from dependent configuration.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the effective-dating pair. Together with CMBN_PLIP_ID, they form the primary key and the unique business-key candidate recorded in BEN_CMBN_PLIP_F_PK.
  • BUSINESS_GROUP_ID — the HR business group (legislative/operating unit) that owns the combination definition, providing the multi-tenant scoping used across BEN configuration tables.
  • PGM_ID — the identifier of the program in which the plan combination is defined. This ties the combination to a specific benefit program.
  • NAME — the user-visible name of the combination, used for identification and reporting.
  • CPL_ATTRIBUTE_CATEGORY — the descriptive flexfield context category for the combination-of-plans (CPL) attribute group.
  • CPL_ATTRIBUTE1 … CPL_ATTRIBUTE30 — the 30 attribute columns belonging to the CPL descriptive flexfield. These are customer-defined and are typically used to store additional combination-level information required for credit rules or integration.
  • OBJECT_VERSION_NUMBER — the optimistic-locking version, incremented on each update, used by the BEN forms and APIs to prevent lost updates.
  • LAST_UPDATE_DATE / LAST_UPDATED_BY / LAST_UPDATE_LOGIN / CREATED_BY / CREATION_DATE — standard audit and WHO columns maintained by the EBS framework and used in reporting and data reconciliation.

The primary key is the surrogate combination of CMBN_PLIP_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The unique index BEN_CMBN_PLIP_F_PK is the documented business-key candidate; because the object is classified as standalone, no additional foreign-key-derived business keys are asserted in the metadata.

Common Use Cases and Queries

This table is primarily consumed by benefit program configuration and reporting. Typical scenarios include:

  • Enrolling credit determination. During enrollment processing, the combination of plans a participant elects is matched against the effective-dated combinations defined here to determine whether a credit applies.
  • Configuration review and auditing. Functional analysts list all combinations for a given program to confirm credit-eligible plan groupings and their effective dates.
  • Descriptive flexfield reporting. The CPL_ATTRIBUTE columns are frequently surfaced in client-specific reports and extracts.

A representative query retrieving the currently effective combinations for a program is:

  • SELECT cmbn_plip_id, name, pgm_id, effective_start_date, effective_end_date
  • FROM ben.ben_cmbn_plip_f
  • WHERE pgm_id = :p_pgm_id
  • AND business_group_id = :p_business_group_id
  • AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;

Because the table is effective-dated, any query intended for "current" data must always apply the date predicate; omitting it returns every historical and future version of each combination. For data warehousing, the table is normally extracted with its effective dates intact so that slowly changing dimensions can be built over the combination key.

Related Objects

The documented metadata classifies BEN_CMBN_PLIP_F as standalone, with the only indexed relationship being the primary key BEN_CMBN_PLIP_F_PK. Consistent with that classification, the following are the most significant related objects and the join columns typically used:

  • BEN_PGM_F — the benefit program definition; joined via PGM_ID (and BUSINESS_GROUP_ID) to identify the program that owns the combination.
  • BEN_PL_F — the plan definition; plan-in-program combinations ultimately reference the plans offered through the program, joined through the program/plan association.
  • BEN_PGM_PL_F — the program-to-plan association; used to resolve which plans belong to the program referenced by PGM_ID.
  • BEN_CMBN_PLIP_RL — the association table linking a combination to the specific plan-in-program entries that make up the combination.
  • BEN_ELIG_CREDIT_F and the credit/flex-credit definition tables — the credit objects whose applicability is driven by the plan combinations defined here.
  • BEN_CMBN_PLIP_F_PK — the unique index enforcing the composite key and serving as the business-key candidate for the object.
  • BEN_CPL_DF / BEN descriptive flexfield metadata — the flexfield definitions that give meaning to CPL_ATTRIBUTE_CATEGORY and CPL_ATTRIBUTE1 through CPL_ATTRIBUTE30.
  • HR_ALL_ORGANIZATION_UNITS — joined via BUSINESS_GROUP_ID where the business group organization must be resolved for reporting.

These relationships should be validated against the specific configuration in the target instance, since the documented metadata does not assert formal foreign keys for this standalone object.