Search Results emp_per_in_ler_id




Overview
Explore in AI Chat

Overview

BEN.BEN_CWB_GROUP_HRCHY is a transactional table in the Oracle E-Business Suite Benefits (BEN) schema that stores the managerial hierarchy associated with a Compensation Workbench (CWB) group plan. Compensation Workbench is the module used to distribute compensation allocation budgets to managers for activities such as merit increases, bonuses, and stock awards. The hierarchy defined in this table determines the reporting relationship chain — which manager may allocate to which subordinate — within a given plan iteration.

The table is classified heuristically as standalone in Data Vault terms, meaning it is neither a pure hub, link, nor satellite. Because it embeds both a manager reference and an employee reference, it behaves functionally like a link between two person/assignment life-event entities, carrying the level depth as a descriptive attribute. Modelers should treat it as a link-with-attribute pattern for analytical purposes.

Key Information Stored

The table contains nine documented columns. The most significant are the two identifiers and the level indicator:

The composite primary key BEN_CWB_GROUP_HRCHY_PK is defined over (MGR_PER_IN_LER_ID, EMP_PER_IN_LER_ID), which is also the sole business-key candidate and unique index. Two non-unique indexes exist: BEN_CWB_GROUP_HRCHY_FK1 on EMP_PER_IN_LER_ID, and BEN_CWB_GROUP_HRCHY_N1 on (EMP_PER_IN_LER_ID, LVL_NUM), optimizing subordinate lookups and level-filtered queries.

Common Use Cases and Queries

Typical uses include reconstructing a manager's direct and indirect reports for a CWB plan, validating that a manager has authority over an employee before permitting an allocation, and reporting on hierarchy depth for governance analysis.

  • Direct reports of a manager: SELECT EMP_PER_IN_LER_ID, LVL_NUM FROM BEN.BEN_CWB_GROUP_HRCHY WHERE MGR_PER_IN_LER_ID = :mgr;
  • Locating the manager of a specific employee: SELECT MGR_PER_IN_LER_ID FROM BEN.BEN_CWB_GROUP_HRCHY WHERE EMP_PER_IN_LER_ID = :emp;
  • Filtering by hierarchy depth: use the BEN_CWB_GROUP_HRCHY_N1 index with WHERE EMP_PER_IN_LER_ID = :emp AND LVL_NUM = 1;
  • Reporting joins to PER_ALL_PEOPLE_F and BEN_PER_IN_LER via the life-event identifiers.

Related Objects

The table is referenced by BEN.BEN_CWB_GROUP_HRCHY#, the managed object type generated for it. Based on the life-event identifiers, meaningful joins include:

  • BEN.BEN_PER_IN_LER — source of the person life-event records referenced by both identifier columns.
  • PER_ALL_PEOPLE_F — person attributes for reporting.
  • BEN.BEN_CWB_GROUP_PLANS — the group plan this hierarchy serves.
  • PER_ALL_ASSIGNMENTS_F — assignment context for managers and employees.
  • BEN.BEN_CWB_GROUP_SETUP — configuration parameters governing the group.

Together these objects enable end-to-end Compensation Workbench hierarchy processing and reporting within Oracle EBS 12.1.1 and 12.2.2.