Search Results ben_cwb_group_hrchy




Overview
Explore in AI Chat

Overview

BEN_CWB_GROUP_HRCHY is a table within the BEN (Advanced Benefits) product module of Oracle E-Business Suite, holding group hierarchy definitions used by the Compensation Workbench (CWB) feature set. The table appears in both Oracle EBS 12.1.1 and 12.2.2, and is documented as VALID in the BEN schema. Its stated description is "Group Hierarchy," reflecting its role as the structural map that defines reporting relationships between manager and employee persons within a benefits or compensation workbench context.

From a Data Vault modeling perspective, the mined relationship classification for this object is standalone. This heuristic suggests the table behaves as an independent structural entity rather than a pure hub, link, or satellite. In practice it functions closest to a link-style association table, capturing the pairwise relationship (manager reporting to employee, within a group level) between two persons, while carrying descriptive audit attributes.

Key Information Stored

The documented physical schema for 12.2.2 defines nine columns. The table's core is captured by the composite primary key, which is also the sole unique index and business-key candidate, BEN_CWB_GROUP_HRCHY_PK (MGR_PER_IN_LER_ID, EMP_PER_IN_LER_ID).

  • MGR_PER_IN_LER_ID — the person identifier for the manager side of the hierarchy relationship; part of the composite primary key.
  • EMP_PER_IN_LER_ID — the person identifier for the employee (subordinate) side; the second component of the composite primary key.
  • LVL_NUM — the numeric level within the group hierarchy, indicating depth or tier of the relationship.
  • LAST_UPDATE_DATE — the date and time the row was last modified; standard EBS audit column.
  • LAST_UPDATED_BY — the user ID of the last person to update the record.
  • LAST_UPDATE_LOGIN — the login identifier associated with the last update.
  • CREATED_BY — the user ID that created the record.
  • CREATION_DATE — the date and time the record was first created.
  • OBJECT_VERSION_NUMBER — the optimistic locking column used to manage concurrent updates.

The primary key here is composite and business-meaningful (two person IDs), rather than a single surrogate sequence-generated key. The remaining columns are the standard five "WHO" audit columns plus LVL_NUM and OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

This table is primarily consumed when building or reporting on manager-subordinate rollups for Compensation Workbench and Advanced Benefits processing. Typical scenarios include reconstructing the hierarchy for a given set of persons, determining the depth of a subordinate beneath a manager, and validating that reporting lines are consistent.

A common query pattern joins the manager and employee person identifiers back to a person/group source to resolve names and levels:

  • Retrieve all subordinates beneath a specific manager: SELECT EMP_PER_IN_LER_ID, LVL_NUM FROM BEN.BEN_CWB_GROUP_HRCHY WHERE MGR_PER_IN_LER_ID = :p_mgr_id;
  • Determine the direct relationship between two persons: SELECT LVL_NUM FROM BEN.BEN_CWB_GROUP_HRCHY WHERE MGR_PER_IN_LER_ID = :p_mgr AND EMP_PER_IN_LER_ID = :p_emp;
  • Audit recent changes: query on LAST_UPDATE_DATE and LAST_UPDATED_BY to trace hierarchy modifications.
  • Hierarchy depth reporting: group by LVL_NUM to profile the distribution of reporting levels.

Because OBJECT_VERSION_NUMBER is present, any programmatic DML should honor optimistic locking to avoid overwriting concurrent updates.

Related Objects

The documented FK structure classifies this object as standalone, meaning no explicit foreign keys were mined in the metadata. Relationships are therefore logical rather than enforced at the database level. The most significant associations are:

  • Person/Assignment objects — MGR_PER_IN_LER_ID and EMP_PER_IN_LER_ID logically reference person identifiers within the BEN person structures, enabling name resolution.
  • Other BEN_CWB_* tables — companion Compensation Workbench tables that store group definitions and workbench content referenced alongside the hierarchy.
  • BEN group tables — the group context within which hierarchy levels (LVL_NUM) are defined.
  • FND audit references — LAST_UPDATED_BY and CREATED_BY resolve against the FND user tables.

Consumers should treat the hierarchy as a logical join to person data rather than relying on declared foreign keys, given the standalone classification.