Search Results ben_tcs_stmt_cat




Overview
Explore in AI Chat

Overview

BEN_TCS_STMT_CAT is a table in the BEN schema (Advanced Benefits module) within Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to identify all compensation categories that are present within a given Total Compensation Statement. The table acts as an intersection or association entity that links a statement definition to the compensation categories it exposes, and it carries ordering and display attributes that control how those categories appear on the statement output. The table is documented in ETRM 12.2.2 as VALID with 12 columns.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone. Because the table resolves a many-to-many relationship between statements and categories while also carrying its own descriptive attributes (ordering, display name, audit columns), it may be modeled as a link between BEN_TCS_STMT and BEN_TCS_CAT, or alternatively as a satellite-style association table if the ordering and display attributes are treated as dependent descriptive context. This classification is a modeling suggestion derived from the foreign key structure, not a prescribed physical design.

Key Information Stored

The table holds identifiers and presentation attributes that tie compensation categories to a statement. The most significant columns documented are:

  • STMT_CAT_ID — the surrogate primary key, uniquely identifying each statement-category association row. It is the key of the BEN_TCS_STMT_CAT_PK constraint.
  • STMT_ID — foreign key to BEN_TCS_STMT, identifying the parent statement definition. It also participates in the unique index BEN_TCS_STMT_CAT_U1.
  • CAT_ID — foreign key to BEN_TCS_CAT, identifying the compensation category included in the statement.
  • ORDR_NUM — controls the sequence or display order of the category within the statement.
  • DISPLAY_NAME — the label shown for the category on the rendered statement.
  • BUSINESS_GROUP_ID — the business group (operating unit) context for the row, supporting multi-tenant data separation.
  • OBJECT_VERSION_NUMBER — optimistic locking column used by the ADF/BC4J layer for concurrent update control.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard EBS audit columns recording who created and last modified the row and when.

The unique index BEN_TCS_STMT_CAT_U1 covers STMT_ID and CAT_ID, enforcing that a given category is associated with a given statement only once. This makes (STMT_ID, CAT_ID) the effective business key, while STMT_CAT_ID remains the surrogate primary key.

Common Use Cases and Queries

Typical usage centers on Total Compensation Statement configuration and reporting: determining which categories appear on a statement, in what order, and under what display label. A common query joins the association to its parent tables:

  • List categories for a statement: SELECT sc.STMT_CAT_ID, sc.STMT_ID, sc.CAT_ID, sc.ORDR_NUM, sc.DISPLAY_NAME FROM BEN.BEN_TCS_STMT_CAT sc WHERE sc.STMT_ID = :stmt_id ORDER BY sc.ORDR_NUM;
  • Join to the category definition to resolve names: SELECT sc.STMT_ID, c.CAT_ID, sc.DISPLAY_NAME FROM BEN.BEN_TCS_STMT_CAT sc, BEN.BEN_TCS_CAT c WHERE sc.CAT_ID = c.CAT_ID AND sc.STMT_ID = :stmt_id;
  • Business group-scoped reporting: filter on BUSINESS_GROUP_ID to restrict results to a single operating unit.

These patterns support statement configuration validation, audit of category ordering, and data extracts feeding the compensation statement generation process.

Related Objects

The table is directly related to its parent entities through documented foreign keys, and indirectly to the objects that consume statement definitions:

  • BEN_TCS_STMT — parent statement definition; joined via STMT_ID.
  • BEN_TCS_CAT — parent compensation category; joined via CAT_ID.
  • BEN_TCS_STMT_CAT_PK — primary key constraint on STMT_CAT_ID.
  • BEN_TCS_STMT_CAT_U1 — unique index on STMT_ID and CAT_ID enforcing the business key.
  • Other BEN Total Compensation Statement tables (such as statement item and category detail objects) that share the STMT_ID or CAT_ID lineage and feed statement rendering.

Because the FK structure is limited to two parent tables, the dependency footprint is narrow; most downstream effects flow through the statement and category definitions rather than directly from this association table.