Search Results ben_tcs_cat_item_hrchy




Overview
Explore in AI Chat

Overview

BEN.BEN_TCS_CAT_ITEM_HRCHY is a transactional table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. Its documented purpose is to identify the hierarchical relationship between compensation items and compensation categories. In practical terms, the table stores the parent/child structure that governs how total compensation statement (TCS) categories and their constituent items are organized, aggregated, and reported to plan participants.

The table resides in the BEN schema and is classified as VALID in the ETRM repository. The provided relationship data assigns a heuristic Data Vault classification of standalone. From a dimensional modeling perspective, this classification suggests that the table may be best treated as an independent structure rather than a conventional hub, link, or satellite, though the presence of multiple foreign keys pointing to statement, period, category-row, and all-objects tables indicates it functions as an associative entity within the TCS configuration model. Analysts building a data vault or warehouse layer around Advanced Benefits should note this suggestion but validate it against the actual grain of CAT_ID, ITEM_ID, and LVL_NUM.

Key Information Stored

BEN_TCS_CAT_ITEM_HRCHY contains eight documented columns. The most significant are described below.

  • CAT_ID — Identifies the compensation category to which the hierarchy relationship belongs. This is a leading component of the primary key and a candidate business key.
  • ITEM_ID — Identifies the compensation item placed within the category. Together with CAT_ID it defines the parent/child membership.
  • LVL_NUM — The level number of the item within the category hierarchy, defining depth or position. It completes the composite primary key.
  • STMT_ID — Foreign key to BEN_TCS_STMT, linking the hierarchy entry to a specific total compensation statement definition.
  • STMT_PERD_ID — Foreign key to BEN_TCS_STMT_PERD, associating the hierarchy with a statement period.
  • ROW_IN_CAT_ID — Foreign key to BEN_TCS_ROW_IN_CAT, tying the item to its configured row within the category.
  • ALL_OBJECTS_IN_CAT_ID — Foreign key to BEN_TCS_ALL_OBJECTS_IN_CAT, relating the entry to the broader set of objects included in the category.
  • CNTR_CD — A control or context code column, typically used to qualify the hierarchy record.

The documented primary key is BEN_TCS_CAT_ITEM_HRCHY_PK, defined on the composite of (CAT_ID, ITEM_ID, LVL_NUM). These three columns represent the natural business-key candidates, while the remaining columns carry descriptive or referential context. No separate surrogate sequence key is documented.

Common Use Cases and Queries

Typical usage centers on reporting and troubleshooting the configuration of total compensation statements. Common scenarios include validating that every compensation item is attached to the correct category, resolving which statement or statement period a hierarchy record belongs to, and reconstructing the ordering of items displayed on a participant statement.

A representative query joins the hierarchy to its statement and period parents:

  • SELECT h.CAT_ID, h.ITEM_ID, h.LVL_NUM, h.STMT_ID, h.STMT_PERD_ID FROM BEN.BEN_TCS_CAT_ITEM_HRCHY h WHERE h.STMT_ID = :p_stmt_id ORDER BY h.CAT_ID, h.LVL_NUM;

Another frequent pattern inspects item placement by level, aggregating counts per category to detect configuration gaps or duplicate placements. Reporting extracts used for compensation statement reconciliation should always constrain by STMT_PERD_ID so that hierarchy rows are read in the correct period context.

Related Objects

The following objects are most significant based on the documented foreign-key relationships and the surrounding TCS configuration model:

  • BEN_TCS_STMT — parent statement definition, joined via STMT_ID.
  • BEN_TCS_STMT_PERD — statement period, joined via STMT_PERD_ID.
  • BEN_TCS_ROW_IN_CAT — category row configuration, joined via ROW_IN_CAT_ID.
  • BEN_TCS_ALL_OBJECTS_IN_CAT — full object set for the category, joined via ALL_OBJECTS_IN_CAT_ID.
  • Compensation category and item definition tables referenced through CAT_ID and ITEM_ID, which supply the descriptive names and attributes used in reporting joins.