Search Results ben_tcs_cat_subcat_hrchy




Overview
Explore in AI Chat

Overview

BEN_TCS_CAT_SUBCAT_HRCHY is a table in the BEN schema of Oracle Advanced Benefits (product module BEN). As documented in the ETRM metadata for release 12.1.1 and 12.2.2, its purpose is to serve as a hierarchy table for navigating the sub-categories within a category. In practical terms, the table stores the parent-child relationships that define how benefit sub-categories (SUB_CAT_ID) roll up under a category (CAT_ID) for a given statement or plan configuration, tied to a statement period. It is a structural or navigational object rather than a transactional one: it does not record enrollments, elections, or costs, but instead describes the shape of the benefit category tree that other Benefits processes traverse.

Mined from its foreign key structure, the heuristic Data Vault classification of this object is standalone. This means the table does not cleanly resolve to a hub, link, or satellite under the Data Vault modeling heuristic, and is best treated as a standalone hierarchy or reference structure rather than as a core integration actor in a Data Vault build.

Key Information Stored

The documented physical schema for 12.2.2 lists six columns. The most significant of these are:

  • STMT_ID — The statement identifier, anchoring the hierarchy to a particular benefit statement or configuration context.
  • CAT_ID — The category identifier; the top-level node of the hierarchy.
  • SUB_CAT_ID — The sub-category identifier; the child node positioned beneath the category.
  • LVL_NUM — The level number, indicating the depth of the sub-category within the hierarchy.
  • ROW_IN_CAT_ID — A reference to a row in the category, used to place the entry; foreign-keyed to BEN_TCS_ROW_IN_CAT.
  • STMT_PERD_ID — The statement period identifier; foreign-keyed to BEN_TCS_STMT_PERD, tying the row to a valid period.

The surrogate primary key as documented is BEN_TCS_CAT_SUBCAT_HRCHY_PK, composed of (STMT_ID, CAT_ID, SUB_CAT_ID, LVL_NUM). No separate unique business-key index is documented in the supplied metadata; the composite primary key effectively serves as the uniqueness constraint, combining the statement, category, sub-category, and level. The combination of STMT_ID, CAT_ID, and SUB_CAT_ID represents the natural business identifier of a hierarchy edge, while LVL_NUM adds positional disambiguation.

Common Use Cases and Queries

Typical usage centers on reconstructing or validating the category-to-sub-category navigation tree for a given statement and period. A common query retrieves the full hierarchy for a statement:

  • Select CAT_ID, SUB_CAT_ID, LVL_NUM from BEN_TCS_CAT_SUBCAT_HRCHY where STMT_ID = :stmt_id order by CAT_ID, LVL_NUM;
  • Join to BEN_TCS_ROW_IN_CAT on ROW_IN_CAT_ID to resolve the category row detail for each hierarchy entry.
  • Join to BEN_TCS_STMT_PERD on STMT_PERD_ID to constrain the hierarchy to a specific statement period.

Reporting scenarios include validating that every sub-category is reachable through a defined level, auditing hierarchy depth (via LVL_NUM) for a category, and feeding downstream Benefits eligibility or rate processing that depends on ordered sub-category navigation.

Related Objects

The FK structure identifies two directly referenced objects, both significant dependencies:

  • BEN_TCS_ROW_IN_CAT — referenced via ROW_IN_CAT_ID.
  • BEN_TCS_STMT_PERD — referenced via STMT_PERD_ID.

In addition, the category and sub-category identifiers (CAT_ID, SUB_CAT_ID) imply logical dependence on the standard Benefits category and sub-category definition objects, and the STMT_ID ties into the benefit statement configuration used across the BEN TCS family of tables. Together these relationships position BEN_TCS_CAT_SUBCAT_HRCHY as the navigational glue between statement period setup, category rows, and the sub-category tree consumed by Advanced Benefits processing.