Search Results ben_tcs_asg_stmt




Overview
Explore in AI Chat

Overview

BEN_TCS_ASG_STMT is a core table within the Oracle Advanced Benefits (BEN) module that identifies the statements generated for a specific assignment, for a specific person, over a defined statement period. In the Oracle E-Benefits architecture, the "TCS" prefix refers to Total Compensation Statement functionality, which allows organizations to communicate the full value of an employee's compensation and benefits package. This table acts as the assignment-level bridge between the statement header definition and the per-period statement data, recording each individualized statement produced for an employee's assignment.

From a Data Vault modeling perspective, the mined foreign key structure suggests that BEN_TCS_ASG_STMT should be classified as a link table. It connects the statement definition (BEN_TCS_STMT) with the person-period statement generation record (BEN_TCS_PER_STMT_PERD), while being referenced downstream by detail items in BEN_TCS_PER_ITEM. This linking role is consistent with its purpose of associating assignments to statements across a reporting period.

Key Information Stored

The table contains 13 documented columns. The most significant are:

The unique-index candidate BEN_TCS_ASG_STMT_U1 on ASG_STMT_ID reinforces it as the durable business key reference for downstream detail rows.

Common Use Cases and Queries

Typical reporting scenarios include identifying which employees received a statement, tracking email delivery, and reconciling statement line items. Common query patterns join ASG_STMT_ID across related tables:

  • Listing all statements for a given period:
SELECT a.ASG_STMT_ID, a.ASSIGNMENT_NUMBER, a.EMAIL_STATUS
FROM   BEN.BEN_TCS_ASG_STMT a
WHERE  a.PER_STMT_PERD_ID = :period_id;
  • Retrieving rendered statement XML for a person:
SELECT a.ASG_STMT_ID, a.XML_STMT_CLOB
FROM   BEN.BEN_TCS_ASG_STMT a
WHERE  a.ASSIGNMENT_ID = :assignment_id;
  • Reconciling detail items back to their assignment statement:
SELECT i.ASG_STMT_ID, i.*
FROM   BEN.BEN_TCS_PER_ITEM i
WHERE  i.ASG_STMT_ID = :asg_stmt_id;

These are frequently used in benefits administration dashboards, audit reports, and total compensation statement reconciliation.

Related Objects

The FK metadata identifies the key related objects that this table interacts with:

  • BEN_TCS_STMT — Referenced via STMT_ID; the parent statement template/definition.
  • BEN_TCS_PER_STMT_PERD — Referenced via PER_STMT_PERD_ID; person-period statement record.
  • BEN_TCS_PER_ITEM — References this table via ASG_STMT_ID; holds the itemized detail lines that make up each statement.
  • Assignment and person context (PER_ALL_ASSIGNMENTS_F, PER_ALL_PEOPLE_F) join on ASSIGNMENT_ID for employee-level reporting.

Together these objects form the Total Compensation Statement generation chain in Oracle Advanced Benefits, with BEN_TCS_ASG_STMT serving as the pivotal assignment-level linkage.