Search Results sys_il0000394806c00019

Overview

BEN_TCS_STMT is the master definition table for Total Compensation Statements (TCS) within the Oracle Advanced Benefits (BEN) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. A row in this table identifies a single compensation statement, which is the top-level container describing how a Total Compensation Statement is composed from compensation categories and compensation items. The statement setup rows drive the configuration that determines which assignment populations receive which statement content, along with presentation-level attributes such as welcome text, estimate indicators, and email delivery options.

The table resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, consistent with transaction-data tables that support ongoing maintenance. No Data Vault classification was supplied in the metadata; based purely on the foreign-key topology, where BEN_TCS_STMT acts as the referenced parent for eight dependent child tables, this would naturally model as a hub surrounded by link and satellite structures in a Data Vault design. In EBS terms it functions as the primary setup entity of the TCS module.

Key Information Stored

The primary key is STMT_ID, a system-generated NUMBER(15) surrogate key and the sole business-key candidate appearing in the unique index BEN_TCS_STMT_U1. No natural business key is enforced by a unique index, so NAME is not reliably unique and STMT_ID remains the join key of record across all dependent tables.

• STMT_ID — system-generated primary key; the surrogate identifier referenced by all child tables.
• NAME — VARCHAR2(240) display name of the statement, used in setup screens and reporting.
• EE_PROFILE_ID — foreign key to BEN_ELIGY_PRFL_F; the eligibility profile that determines which employees receive the statement. Indexed non-uniquely via BEN_TCS_STMT_N1.
• BUSINESS_GROUP_ID — foreign key to HR_ORGANIZATION_UNITS; the enterprise/business group owning the row.
• STAT_CD — statement status, referencing the BEN_TCS_STMT_STATUS lookup value set.
• SHOW_WLCM_PG_CD — flag controlling whether a welcome page is displayed, referencing BEN_TCS_SHOW_WLC_PG.
• MSG_TEXT — CLOB holding the welcome message text, supported by the LOB index SYS_IL0000394806C00008$$.
• INDICATE_ESTIMATE_FLAG — controls whether estimated amounts are marked with an icon.
• STMT_GENERATED_FLAG — indicates whether a statement has been generated for this setup.
• EMAIL_PASSWORD_PROTECTED, EMAIL_CC_MANAGER, EMAIL_SUBJECT, EMAIL_CONTENT, EMAIL_SAMPLE_ADDRESS — email delivery configuration for the statement notification.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — standard Who columns and optimistic-locking version counter.

Common Use Cases and Queries

Typical usage centers on identifying active statement definitions, retrieving welcome and email content for statement rendering, and joining to eligibility profiles to scope the recipient population. A common pattern is to select all defined statements for a business group and inspect their status:

• SELECT stmt_id, name, stat_cd, ee_profile_id FROM ben.ben_tcs_stmt WHERE business_group_id = :p_bg_id AND stat_cd = 'ACTIVE';
• Join to BEN_ELIGY_PRFL_F on EE_PROFILE_ID to report the eligibility profile name against each statement.
• Query MSG_TEXT and EMAIL_CONTENT to audit the welcome and email messaging maintained for each statement.
• Check STMT_GENERATED_FLAG and STMT_GENERATED_FLAG together with the statement period tables to reconcile whether a statement has been produced for a given cycle.
• Reporting extracts joining BEN_TCS_STMT to BEN_TCS_STMT_CAT and BEN_TCS_STMT_PERD to list the categories, items, and periods configured for each statement.

Related Objects

BEN_TCS_STMT is referenced by eight child tables through the shared STMT_ID column, forming the TCS configuration hierarchy:

• BEN_TCS_STMT_CAT (STMT_ID) — categories that make up the statement.
• BEN_TCS_CAT_ITEM_HRCHY (STMT_ID) — hierarchy linking categories to compensation items.
• BEN_TCS_PER_ITEM (STMT_ID) — periods associated with items on the statement.
• BEN_TCS_PER_STMT_PERD (STMT_ID) — periods assigned at statement level.
• BEN_TCS_STMT_PERD (STMT_ID) — statement period definitions.
• BEN_TCS_STMT_TASK (STMT_ID) — task assignments tied to the statement.
• BEN_TCS_ASG_STMT (STMT_ID) — assignment-level statement records.
• BEN_TCS_REPORT_DETAILS (STMT_ID) — report detail definitions for the statement.

Parent references include BEN_ELIGY_PRFL_F via EE_PROFILE_ID and HR_ORGANIZATION_UNITS via BUSINESS_GROUP_ID, which anchor the statement to eligibility and enterprise ownership.