Search Results ben_tcs_all_objects_in_ca_pk

Overview

BEN_TCS_ALL_OBJECTS_IN_CAT is a table owned by the BEN schema within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. The table defines the structural relationships that exist between compensation categories, their associated sub-categories, and individual compensation items configured in the Total Compensation Setup (TCS) framework. In practical terms, it acts as the catalog of objects that belong to a compensation category, mapping the hierarchy used by Oracle Advanced Benefits when presenting and processing compensation information for participants.

The table carries a primary key constraint, BEN_TCS_ALL_OBJECTS_IN_CA_PK, defined on the ALL_OBJECTS_IN_CAT_ID column, and a unique index, BEN_TCS_ALL_OBJECTS_IN_CAT_U1, also on ALL_OBJECTS_IN_CAT_ID, which serves as the business-key candidate. From a data modeling perspective, the heuristic Data Vault classification supplied in the metadata describes this table as standalone rather than a classic hub, link, or satellite. This classification reflects the presence of foreign key references from the table to surrounding configuration tables, indicating it functions as a relationship-bearing entity within the compensation setup structure rather than as a pure reference table or descriptive satellite.

Key Information Stored

The documented physical schema contains thirteen columns. The most significant for functional and reporting purposes are the following:

• ALL_OBJECTS_IN_CAT_ID — the surrogate primary key and unique business-key candidate that uniquely identifies each membership or relationship record within a compensation category.
• CAT_ID — foreign key to BEN_TCS_CAT, identifying the parent compensation category to which the relationship belongs.
• ITEM_ID — reference to the compensation item associated with the category, where a direct item-to-category mapping exists.
• SUBCAT_ID — identifies the compensation sub-category involved in the relationship, allowing category-to-sub-category hierarchies to be modeled.
• COL_IN_CAT_ID — foreign key to BEN_TCS_COL_IN_CAT, linking the record to a specific column defined within the category.
• ROW_IN_CAT_ID — foreign key to BEN_TCS_ROW_IN_CAT, linking the record to a specific row defined within the category.
• STMT_GENERATED_FLAG — indicates whether statement-related generation logic has produced content for this object within the category.
• OBJECT_VERSION_NUMBER — supports optimistic locking during concurrent updates performed through the application layer.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE record creation and change history for compliance and troubleshooting.

The distinction between the surrogate key (ALL_OBJECTS_IN_CAT_ID) and the business identifiers (CAT_ID, ITEM_ID, SUBCAT_ID, COL_IN_CAT_ID, ROW_IN_CAT_ID) is important when designing integrations, since the latter combination reflects the functional identity of the relationship.

Common Use Cases and Queries

This table is typically queried during the configuration review and reporting of compensation structures. A common pattern retrieves all objects within a given category:

• Join BEN_TCS_ALL_OBJECTS_IN_CAT to BEN_TCS_CAT on CAT_ID to list the membership of each compensation category.
• Join to BEN_TCS_COL_IN_CAT and BEN_TCS_ROW_IN_CAT on COL_IN_CAT_ID and ROW_IN_CAT_ID to render the category matrix that drives compensation display.
• Join to BEN_TCS_CAT_ITEM_HRCHY on ALL_OBJECTS_IN_CAT_ID to traverse the item hierarchy associated with each catalog entry.

Typical SQL follows the form: SELECT a.*, c.NAME FROM BEN_TCS_ALL_OBJECTS_IN_CAT a, BEN_TCS_CAT c WHERE a.CAT_ID = c.CAT_ID AND a.SUBCAT_ID IS NOT NULL. Reporting use cases include validating that every compensation item is correctly assigned to a category or sub-category, auditing STMT_GENERATED_FLAG values prior to total compensation statement runs, and building interfaces that replicate Total Compensation Setup structures into downstream analytics environments.

Related Objects

The following objects are most relevant when working with BEN_TCS_ALL_OBJECTS_IN_CAT, based on the documented foreign key relationships:

• BEN_TCS_CAT — the parent compensation category table, joined on CAT_ID.
• BEN_TCS_COL_IN_CAT — the column definitions within a category, joined on COL_IN_CAT_ID.
• BEN_TCS_ROW_IN_CAT — the row definitions within a category, joined on ROW_IN_CAT_ID.
• BEN_TCS_CAT_ITEM_HRCHY — the item hierarchy table, which references this table through ALL_OBJECTS_IN_CAT_ID and is the primary dependent object.
• BEN_TCS_CAT_ITEM and related BEN_TCS configuration tables that consume the catalog structure for compensation item presentation.

Because this table is designated standalone in the Data Vault classification, it should be treated as a configuration relationship table whose integrity depends on the referenced category, column, and row definitions. Changes to those parent configurations can cascade logically into the records held here, making coordinated maintenance of the Total Compensation Setup essential.