Search Results ben_cwb_pl_dsgn




Overview
Explore in AI Chat

Overview

BEN_CWB_PL_DSGN is a core configuration table within the Oracle Advanced Benefits (BEN) module, storing "CWB Plan Design" records. CWB refers to the Compensation Workbench, the component of Advanced Benefits that allows administrators and managers to allocate compensation, budgets, and awards across a plan year. This table captures the design-time definitions that govern how a specific plan is configured for use within Compensation Workbench, including enrollment periods, plan-year periods, currency and rounding attributes, budgeting behavior, and workflow editing rules.

In the 12.1.1 and 12.2.2 releases, BEN_CWB_PL_DSGN is maintained entirely in the BEN schema and is documented as VALID. Its physical schema is substantial, containing 104 columns, reflecting the breadth of configuration options available to Compensation Workbench implementers. The primary key, BEN_CWB_PL_DSGN_PK, is a composite of PL_ID, LF_EVT_OCRD_DT, and OIPL_ID, which ties each design record to a plan, a life-event occurrence date, and an associated option-in-plan identifier. Based on the documented relationships, a heuristic Data Vault classification of a standalone structure is suggested, though the composite key and foreign keys to period tables could support satellite or link modeling depending on the downstream reporting architecture.

Key Information Stored

The table's most significant columns fall into several functional groups. Identity and period anchoring are provided by PL_ID (the plan), OIPL_ID (the option in plan), and LF_EVT_OCRD_DT (life-event occurred date), which together form the primary key. Effective dating and naming are supplied by EFFECTIVE_DATE and NAME. Enrollment and year boundaries are captured through ENRT_PERD_ID, YR_PERD_ID, ENRT_PERD_START_DT, ENRT_PERD_END_DT, YR_PERD_START_DT, YR_PERD_END_DT, WTHN_YR_START_DT, and WTHN_YR_END_DT.

Compensation and budgeting behavior are governed by USES_BDGT_FLAG, PL_UOM, PL_ORDR_NUM, OIPL_ORDR_NUM, PL_XCHG_RATE, ACTUAL_FLAG, and AUTO_DISTR_FLAG. Workflow and validation controls include APPROVAL_MODE, WS_AMT_EDIT_CD, WS_AMT_EDIT_ENF_CD_FOR_NULLS, WS_OVER_BUDGET_EDIT_CD, WS_OVER_BUDGET_TOLERANCE_PCT, BDGT_OVER_BUDGET_EDIT_CD, and BDGT_OVER_BUDGET_TOLERANCE_PCT. Audit columns capture LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER. The unique index BEN_CWB_PL_DSGN_PK is the business-key candidate, while the surrogate composite key enables precise identification of each design record.

Common Use Cases and Queries

Typical reporting scenarios include identifying the active plan design for a given plan and period, auditing budget tolerance thresholds, and validating that workflow edit rules are configured consistently across plans. A common query pattern joins the design table to plan and period tables:

  • List designs by plan: SELECT PL_ID, NAME, EFFECTIVE_DATE FROM BEN.BEN_CWB_PL_DSGN WHERE PL_ID = :p_plan_id;
  • Check enrollment period alignment: SELECT p.PL_ID, d.NAME, d.ENRT_PERD_START_DT, d.ENRT_PERD_END_DT FROM BEN.BEN_CWB_PL_DSGN d, BEN.BEN_ENRT_PERD p WHERE d.ENRT_PERD_ID = p.ENRT_PERD_ID;
  • Audit budget tolerances: SELECT PL_ID, WS_OVER_BUDGET_EDIT_CD, WS_OVER_BUDGET_TOLERANCE_PCT, BDGT_OVER_BUDGET_EDIT_CD FROM BEN.BEN_CWB_PL_DSGN WHERE USES_BDGT_FLAG = 'Y';

These queries support Compensation Workbench administrators who must confirm that plan designs are correctly scoped before an active enrollment or allocation cycle begins.

Related Objects

BEN_CWB_PL_DSGN references and is referenced by several transactional and configuration objects within Advanced Benefits. The documented foreign keys tie the design to BEN_ENRT_PERD via ENRT_PERD_ID and to BEN_YR_PERD via YR_PERD_ID. Additional significant related objects include BEN_PL (the parent plan definition), BEN_PL_F (plan formula), and the option-in-plan structures represented by OIPL_ID. Because Compensation Workbench relies on these design records when processing allocations, any changes to BEN_CWB_PL_DSGN propagate to the downstream allocation and budget calculation logic, making careful maintenance of the composite primary key and fiscal period references essential for data integrity.