Search Results ben_cwb_wksht_grp




Overview
Explore in AI Chat

Overview

BEN_CWB_WKSHT_GRP is a table within the BEN (Advanced Benefits) product module of Oracle E-Business Suite, documented in ETRM as "Worksheet Group." It resides in the BEN schema and is marked VALID in both Oracle EBS 12.1.1 and 12.2.2. The table stores the definitional records that group worksheets used within the Oracle Advanced Benefits compensation workbench and related worksheet processing flows. Each row represents a worksheet group, identified by the surrogate primary key CWB_WKSHT_GRP_ID, and carries descriptive, ordering, status, and descriptive-flexfield attributes that control how worksheets are presented and organized to end users.

The ETRM relationship metadata classifies this object heuristically as standalone under the Data Vault modeling suggestion. In practice, this suggests the table functions primarily as a reference or lookup dimension rather than as a transactional hub or link. It anchors worksheet group definitions that are subsequently referenced by worksheet and worksheet item tables through the CWB_WKSHT_GRP_ID value. The classification should be treated as a modeling guideline, not a constraint on how the table is queried in reporting or extraction scenarios.

Key Information Stored

The documented physical schema for 12.2.2 lists 45 columns. The most significant columns for functional and technical use are:

  • CWB_WKSHT_GRP_ID — surrogate primary key, enforced by unique index BEN_CWB_WKSHT_GRP_PK; the join key for all dependent worksheet objects.
  • WKSHT_GRP_CD — business key code identifying the worksheet group; a natural candidate for business-key uniqueness and external references.
  • LABEL — user-facing display label for the worksheet group.
  • BUSINESS_GROUP_ID — enterprise business group partitioning column, essential for multi-organization and security-filtered queries.
  • PL_ID — plan identifier linking the group to a benefits plan context.
  • ORDR_NUM — display or processing sequence number for ordering groups.
  • STATUS_CD — lifecycle status of the group definition.
  • HIDDEN_CD — controls whether the group is hidden from user presentation.
  • CWG_ATTRIBUTE_CATEGORY and CWG_ATTRIBUTE1 through CWG_ATTRIBUTE30 — descriptive flexfield (DFF) columns for extending the worksheet group definition without schema change.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle Applications framework.

The unique index BEN_CWB_WKSHT_GRP_PK on CWB_WKSHT_GRP_ID is the only documented unique constraint; WKSHT_GRP_CD combined with BUSINESS_GROUP_ID is a practical business-key candidate for reconciliation queries.

Common Use Cases and Queries

Typical scenarios include validating worksheet group configuration, reporting active versus inactive groups, and joining group definitions to worksheet detail records. A common query pattern filters by business group and status:

  • SELECT CWB_WKSHT_GRP_ID, WKSHT_GRP_CD, LABEL, ORDR_NUM, STATUS_CD, HIDDEN_CD FROM BEN.BEN_CWB_WKSHT_GRP WHERE BUSINESS_GROUP_ID = :p_bg_id AND STATUS_CD = 'A' ORDER BY ORDR_NUM;
  • Extracting DFF values: select CWG_ATTRIBUTE_CATEGORY plus the relevant CWG_ATTRIBUTEn columns for integrations or downstream reporting.
  • Audit and delta extracts: filter on LAST_UPDATE_DATE for incremental ETL loads into a data warehouse.
  • Reconciliation: join to worksheet dependency tables on CWB_WKSHT_GRP_ID to detect orphaned or misconfigured groups.

Because the table is small and definitional, it is frequently cached or materialized as a dimension in benefits reporting models.

Related Objects

As a standalone object, BEN_CWB_WKSHT_GRP is referenced by other BEN tables that carry CWB_WKSHT_GRP_ID as a foreign key. The most significant related objects include:

  • BEN_CWB_WKSHT_GRP_PK — the primary key constraint/index defining the row identity.
  • Worksheet definition and worksheet item tables in the BEN CWB family that reference CWB_WKSHT_GRP_ID, establishing parent-child associations between groups and their worksheets.
  • BEN_PL_F and plan-related tables joined via PL_ID to resolve the plan context of each group.
  • FND_FLEX_VALUES / FND_FLEX_VALUE_SETS — used to resolve CWG_ATTRIBUTE_CATEGORY and DFF attribute values where a flexfield value set is attached.
  • HR_ALL_ORGANIZATION_UNITS / business group tables joined via BUSINESS_GROUP_ID for secure, organization-scoped reporting.
  • FND_USER — joined via CREATED_BY, LAST_UPDATED_BY, or LAST_UPDATE_LOGIN for audit trail reporting.

These relationships should be confirmed against the actual foreign-key definitions in the target environment, since the ETRM metadata classifies this table as standalone.