Results for “ben_tcs_col_in_cat_u1”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BEN.BEN_TCS_COL_IN_CAT is a configuration table in the Oracle E-Business Suite Advanced Benefits (BEN) module. It stores the definition of individual columns that appear within a Total Compensation Statement (TCS) category. Each row represents one compensation category column and captures its display behavior, ordering, chart inclusion, and contributor mapping. The table functions as the metadata layer that drives the layout of the Total Compensation Statement, allowing administrators to control which compensation components are shown, how they are labeled, and how they are aggregated.

In Oracle EBS 12.1.1 and 12.2.2, the BEN schema supports benefits administration, and this table is a child of the category definition table BEN_TCS_CAT. The ETRM metadata classifies BEN.BEN_TCS_COL_IN_CAT heuristically as a standalone data object. In Data Vault modeling terms, it may be treated as a satellite-like or supporting detail table attached to the BEN_TCS_CAT hub, since it holds descriptive and configuration attributes dependent on the parent category. The classification is a modeling suggestion derived from foreign key structure rather than a prescribed design.

Key Information Stored

The table contains 13 documented columns. The most significant are listed below.

• COL_IN_CAT_ID (NUMBER, mandatory): System-generated primary key. It is also the column of the unique index BEN_TCS_COL_IN_CAT_U1, making it the confirmed business-key candidate and the PK of BEN_TCS_COL_IN_CAT_PK.
• CAT_ID (NUMBER, mandatory): Foreign key to BEN_TCS_CAT. Identifies the parent compensation category to which the column belongs.
• COMP_TYPE_CD (VARCHAR2, 30): Compensation type code referencing the BEN_TCS_COMP_TYPE lookup. Determines the nature of the compensation being displayed.
• ORDR_NUM (NUMBER, 15): Sequence number controlling the display order of the column within its category.
• INCLUDE_IN_CHART_FLAG (VARCHAR2, 30): Indicates whether the column is included in the compensation chart.
• DISPLAY_NAME (VARCHAR2, 240): The column header used when a custom category column is displayed.
• CONTRIBUTOR_TYPE_CD (VARCHAR2, 30): Contributor type code referencing the BEN_TCS_CAT_CONTRBRS lookup, defining who contributed to the compensation amount.
• OBJECT_VERSION_NUMBER: System-generated value that increments by one on each update, supporting optimistic locking.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE: Standard Who columns capturing audit and user context for row creation and maintenance.

Common Use Cases and Queries

Administrators and implementers query this table to verify category column configuration, troubleshoot statement layout issues, and build custom reports on compensation statement structure. A typical retrieval joins the column definitions to their parent category and to lookup values.

• List all columns for a given category ordered for display: SELECT COL_IN_CAT_ID, DISPLAY_NAME, COMP_TYPE_CD, ORDR_NUM, INCLUDE_IN_CHART_FLAG FROM BEN_TCS_COL_IN_CAT WHERE CAT_ID = :p_cat_id ORDER BY ORDR_NUM;
• Identify columns excluded from the chart: SELECT * FROM BEN_TCS_COL_IN_CAT WHERE INCLUDE_IN_CHART_FLAG = 'N';
• Join to BEN_TCS_CAT to report category and column names together, using CAT_ID as the join key.
• Troubleshoot missing or misordered statement lines by reviewing ORDR_NUM and DISPLAY_NAME for a specific CAT_ID.
• Audit configuration changes using the Who columns and OBJECT_VERSION_NUMBER.

Related Objects

The most significant object relationships for BEN.BEN_TCS_COL_IN_CAT are documented through its foreign keys and referencing tables.

• BEN_TCS_CAT: Parent table. Joined on BEN_TCS_COL_IN_CAT.CAT_ID = BEN_TCS_CAT.CAT_ID.
• BEN_TCS_ALL_OBJECTS_IN_CAT: References this table through COL_IN_CAT_ID, linking category columns to the objects included in the category.
• BEN_TCS_COMP_TYPE (lookup): Referenced by COMP_TYPE_CD.
• BEN_TCS_CAT_CONTRBRS (lookup): Referenced by CONTRIBUTOR_TYPE_CD.
• FND_USER: Referenced by LAST_UPDATED_BY and CREATED_BY.
• FND_LOGINS: Referenced by LAST_UPDATE_LOGIN.

The unique index BEN_TCS_COL_IN_CAT_U1 secures COL_IN_CAT_ID, while the nonunique index BEN_TCS_COL_IN_CAT_N1 supports lookups by CAT_ID. Both reside in the APPS_TS_TX_IDX tablespace, with data stored in APPS_TS_TX_DATA.