Search Results ben_tcs_per_item_pk

Overview

BEN_TCS_PER_ITEM is a table in the BEN schema (Advanced Benefits) within Oracle EBS 12.1.1 and 12.2.2. Per the ETRM metadata, its purpose is to identify the compensation item for a person — in other words, it stores the individual line items that make up a person's total compensation statement. Each row links a person's compensation statement structure to a specific compensation ITEM_ID, storing amount-driving attributes such as unit of measure. This makes it a core detail table in the Total Compensation Statement (TCS) subsystem of Advanced Benefits, sitting between the statement header/assignment records and the stored item values.

Data Vault classification (heuristic): The metadata classifies this table as standalone. From a Data Vault modeling perspective, BEN_TCS_PER_ITEM behaves as a link entity — it resolves relationships between a compensation statement, a statement period, a statement assignment, a person, an assignment, and a compensation item, rather than acting as a pure descriptive hub or satellite.

Key Information Stored

The table contains 15 documented columns. The most significant include:

• PER_ITEM_ID — The surrogate primary key, enforced by BEN_TCS_PER_ITEM_PK and also by unique index BEN_TCS_PER_ITEM_U1. This is the single business-key candidate documented for the table.
• ITEM_ID — The compensation item being reported for the person (for example, salary, bonus, or a benefit plan contribution).
• PERSON_ID — The person to whom the compensation item applies.
• ASSIGNMENT_ID — The assignment context for the compensation item, distinguishing items across multiple assignments.
• ASG_STMT_ID — Foreign key to BEN_TCS_ASG_STMT, tying the item to the specific assignment-level statement instance.
• STMT_ID — Foreign key to BEN_TCS_STMT, identifying the parent compensation statement.
• STMT_PERD_ID — Foreign key to BEN_TCS_STMT_PERD, identifying the statement period under which the item was calculated.
• UOM and NNMNTRY_UOM — Unit of measure and non-monetary unit of measure, defining how the item quantity or value is expressed.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle framework.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide standard WHO audit tracking.

Common Use Cases and Queries

Typical uses involve extracting and reconciling compensation statement line items, and joining the item header to its associated values.

• List items for a person: SELECT PER_ITEM_ID, ITEM_ID, UOM, STMT_PERD_ID FROM BEN_TCS_PER_ITEM WHERE PERSON_ID = :p_person;
• Retrieve item values: join to BEN_TCS_PER_ITEM_VALUE on PER_ITEM_ID to obtain stored monetary/quantity values.
• Statement-to-item reporting: join BEN_TCS_STMT and BEN_TCS_STMT_PERD on STMT_ID and STMT_PERD_ID to report all items within a given statement period.
• Assignment-level analysis: filter or group by ASSIGNMENT_ID and ASG_STMT_ID to analyze compensation across multiple assignments.

Related Objects

• BEN_TCS_PER_ITEM_VALUE — References BEN_TCS_PER_ITEM.PER_ITEM_ID; holds the stored values for each item.
• BEN_TCS_STMT — Referenced via STMT_ID; the parent compensation statement.
• BEN_TCS_ASG_STMT — Referenced via ASG_STMT_ID; the assignment-level statement.
• BEN_TCS_STMT_PERD — Referenced via STMT_PERD_ID; the statement period.

These relationships confirm BEN_TCS_PER_ITEM as the connecting detail entity within the Total Compensation Statement data model.