Search Results ben_tcs_all_objects_in_cat_u1

Overview

BEN.BEN_TCS_ALL_OBJECTS_IN_CAT is a transactional table in the Oracle E-Business Suite Benefits (BEN) schema that stores the membership assignments between compensation categories and the objects they contain. Specifically, it captures two distinct relationships: the association of a compensation category with its compensation sub-categories, and the association of a compensation category with individual compensation items. Records in this table effectively define which sub-categories and which items participate in a given category, giving the Compensation Foundation and Total Compensation Statement (TCS) features the hierarchy needed to resolve category contents.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes reside separately in APPS_TS_TX_IDX. Based on its structure — a single surrogate primary key surrounded almost entirely by foreign key references and a small set of descriptive attributes — it is best understood as a link (junction) table. The mined Data Vault classification of "standalone" is a heuristic suggestion only; in practice this object functions as a many-to-many association table that resolves category-to-item and category-to-subcategory relationships.

Key Information Stored

• ALL_OBJECTS_IN_CAT_ID (NUMBER(15)) — System-generated surrogate primary key, enforced by the unique index BEN_TCS_ALL_OBJECTS_IN_CAT_U1. This is the single documented business-key candidate and the column most commonly used as a join target.
• CAT_ID (NUMBER(15)) — Foreign key to BEN_TCS_CAT; identifies the compensation category to which the object belongs.
• ITEM_ID (NUMBER(15)) — Identifies the specific compensation item present in the category.
• SUBCAT_ID (NUMBER(15)) — Identifies the compensation sub-category present in the category, supporting the category-to-subcategory leg of the relationship.
• COL_IN_CAT_ID (NUMBER(15)) — Foreign key to BEN_TCS_COL_IN_CAT, linking the record to a column definition within the category.
• ROW_IN_CAT_ID (NUMBER(15)) — Foreign key to BEN_TCS_ROW_IN_CAT, linking the record to a row definition within the category.
• STMT_GENERATED_FLAG (VARCHAR2) — Indicates whether the row was generated by a statement process, useful for distinguishing system-generated versus manually defined memberships.
• OBJECT_VERSION_NUMBER — Optimistic locking column that increments on each update.
• Standard Who columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide full audit lineage.

Three supporting non-unique indexes (N1 on ITEM_ID, N2 on SUBCAT_ID, N3 on CAT_ID) confirm that the table is queried predominantly by item, sub-category, and category respectively.

Common Use Cases and Queries

The primary reporting scenario is enumerating the contents of a compensation category. A typical pattern joins the junction table to BEN_TCS_CAT and filters on a category:

SELECT a.ALL_OBJECTS_IN_CAT_ID,
       a.CAT_ID, a.ITEM_ID, a.SUBCAT_ID,
       a.STMT_GENERATED_FLAG
FROM   BEN.BEN_TCS_ALL_OBJECTS_IN_CAT a
WHERE  a.CAT_ID = :p_cat_id;

Because the table drives compensation hierarchy resolution, it is frequently consulted when diagnosing Total Compensation Statement output, validating that items are correctly attached to a category, and auditing which rows were statement-generated. Administrators investigating missing or duplicated items in a category use the ITEM_ID index (N1) to scan by item; hierarchical reporting uses the SUBCAT_ID index (N2) to enumerate sub-category membership. Direct DML against this table is generally discouraged in favor of the supported Benefits setup and TCS concurrent processes.

Related Objects

• BEN_TCS_CAT — Referenced via CAT_ID; the parent category definition.
• BEN_TCS_COL_IN_CAT — Referenced via COL_IN_CAT_ID; column definitions within a category.
• BEN_TCS_ROW_IN_CAT — Referenced via ROW_IN_CAT_ID; row definitions within a category.
• BEN_TCS_CAT_ITEM_HRCHY — Child table referencing ALL_OBJECTS_IN_CAT_ID; stores item hierarchy details for category members.

These four objects constitute the immediate dependency footprint documented for the table and should be joined through the foreign keys listed above when constructing TCS reporting queries.