Search Results ben_tcs_stmt_perd_pk

Overview

BEN_TCS_STMT_PERD is a table in the BEN (Advanced Benefits) product schema of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. Its documented purpose is to identify an employee statement for a given period. In practice, the table acts as the period-level anchor for the Total Compensation Statement (TCS) feature: each row defines one statement period window belonging to a parent statement, and downstream compensation hierarchy, category, and personal item records are attached to that period via the STMT_PERD_ID surrogate key.

The ETRM metadata records this object as VALID with 14 documented columns in the 12.2.2 physical schema, owned by BEN. The heuristic Data Vault classification mined from the foreign key structure is standalone, meaning the table has no outbound foreign keys other than to its own parent statement; this should be treated as a modeling suggestion rather than a definitive architectural statement. Its effective role resembles a link between a statement (BEN_TCS_STMT) and the detailed compensation content that references the period.

Key Information Stored

The table is keyed by a system-generated surrogate, with a business-key unique index layered on top:

• STMT_PERD_ID — surrogate primary key, enforced by BEN_TCS_STMT_PERD_PK. This is the column referenced by all child tables.
• STMT_ID — foreign key to BEN_TCS_STMT, identifying the parent statement. Also covered by the unique index BEN_TCS_STMT_PERD_U1 (documented as STMT_PERD_ID, though the relationship data maps STMT_ID as a foreign key to BEN_TCS_STMT).
• START_DATE and END_DATE — the effective date range of the statement period.
• DISPLAY_PERIOD — the period label presented to the employee on the statement.
• STMT_GENERATED_FLAG — indicates whether the statement has been generated for this period.
• AVAILABILITY_DATE — the date the statement period becomes available to the employee.
• XCHG_RATE_DATE — the exchange rate date used when converting compensation amounts.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle framework.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns.

The surrogate key should be used for joins; the STMT_ID plus date range combination supplies the business meaning.

Common Use Cases and Queries

Typical reporting retrieves the periods for a statement to reconstruct statement timelines or to drive period-specific compensation detail queries:

• List all periods for a statement: SELECT stmt_perd_id, display_period, start_date, end_date FROM ben.ben_tcs_stmt_perd WHERE stmt_id = :p_stmt_id ORDER BY start_date;
• Identify periods still pending generation: filter on stmt_generated_flag plus the availability_date window.
• Join to detail tables using STMT_PERD_ID to produce compensation breakdowns per period, or join upward to BEN_TCS_STMT for statement-level attributes.
• Audit queries that trace creation and last update metadata for compliance reporting on statement release dates.

Related Objects

• BEN_TCS_STMT — parent; joined on BEN_TCS_STMT_PERD.STMT_ID = BEN_TCS_STMT.STMT_ID.
• BEN_TCS_CAT_SUBCAT_HRCHY — category/subcategory hierarchy detail, joined on STMT_PERD_ID.
• BEN_TCS_CAT_ITEM_HRCHY — category item hierarchy detail, joined on STMT_PERD_ID.
• BEN_TCS_PER_STMT_PERD — person-level statement period records, joined on STMT_PERD_ID.
• BEN_TCS_PER_ITEM — person-level compensation items, joined on STMT_PERD_ID.

These five dependents constitute the primary integration surface for the table; any purge or archive of statement periods must account for them to preserve referential integrity.