Search Results ben_comp_obj_cache_fk1

Overview

BEN.BEN_COMP_OBJ_CACHE is a transactional table in the Oracle E-Business Suite Benefits (BEN) schema that stores the compensation object cache generated during execution of the Manage Life Events process. The table acts as a transient staging area in which the eligible compensation objects — plans, programs, and plan types — associated with a participant's life event are materialized so that downstream processing does not need to repeatedly re-derive them through the full eligibility engine. Each row is scoped to a business group and stamped with an effective date and a run timestamp, allowing multiple concurrent or successive runs to coexist without collision.

The object is registered under FND Design Data as BEN.BEN_COMP_OBJ_CACHE and is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2. Physically it resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10, and its indexes are stored in APPS_TS_TX_IDX. Heuristic Data Vault classification, derived from the foreign-key structure documented in the metadata, identifies this object as standalone. In Data Vault modeling terms, this suggests it is best treated as a satellite-like or reference construct rather than as a true hub or link; it does not participate as a parent of other business entities and instead records run-scoped attributes keyed by its own surrogate identifier.

Key Information Stored

The table contains 16 documented columns. The most significant are:

• COMP_OBJ_CACHE_ID — NUMBER(15), system-generated surrogate primary key. It is the single column of the unique index BEN_COMP_OBJ_CACHE_PK, which is the only unique business-key candidate documented for this object.
• BUSINESS_GROUP_ID — NUMBER(15), foreign key to HR_ORGANIZATION_UNITS, identifying the business group (legislative data group) under which the cache row was produced. It is indexed non-uniquely by BEN_COMP_OBJ_CACHE_FK1.
• EFFECTIVE_DATE — DATE, the effective date against which the compensation object was evaluated.
• TIMESTAMP — DATE, the run timestamp that distinguishes one Manage Life Events execution from another.
• MODE_CD — VARCHAR2(30), the mode under which the cache was built.
• NO_PLANS — VARCHAR2(30), indicator relating to plan availability for the cached row.
• NO_PROGRAMS — VARCHAR2(30), indicator relating to program availability for the cached row.
• PGM_ID — foreign key to BEN_PGM_F, the cached program.
• PL_ID — foreign key to BEN_PL_F, the cached plan.
• PL_TYP_ID — foreign key to BEN_PL_TYP_F, the cached plan type.
• OBJECT_VERSION_NUMBER — NUMBER(15), incremented on each update for optimistic locking.
• Standard WHO columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical diagnostic and reporting scenarios include verifying what objects were cached during a particular life event run, reconciling plan and program coverage, and diagnosing stale or orphaned cache rows. Because the table is run-scoped, most queries filter on BUSINESS_GROUP_ID together with a timestamp or effective date.

Sample pattern: retrieve the cached compensation objects for a business group on a given run:

• SELECT c.COMP_OBJ_CACHE_ID, c.EFFECTIVE_DATE, c.TIMESTAMP, c.MODE_CD, c.PGM_ID, c.PL_ID, c.PL_TYP_ID FROM BEN.BEN_COMP_OBJ_CACHE c WHERE c.BUSINESS_GROUP_ID = :p_bg_id AND c.EFFECTIVE_DATE = :p_eff_date ORDER BY c.COMP_OBJ_CACHE_ID;

Sample pattern: join the cache to the plan and program definitions to describe the cached objects:

• SELECT c.COMP_OBJ_CACHE_ID, p.PL_NAME, pgm.PGM_NAME FROM BEN.BEN_COMP_OBJ_CACHE c, BEN.BEN_PL_F p, BEN.BEN_PGM_F pgm WHERE c.PL_ID = p.PL_ID(+) AND c.PGM_ID = pgm.PGM_ID(+) AND c.COMP_OBJ_CACHE_ID = :p_id;

Sample pattern: locate rows for a specific plan type within the most recent run timestamp:

• SELECT * FROM BEN.BEN_COMP_OBJ_CACHE WHERE BUSINESS_GROUP_ID = :p_bg_id AND PL_TYP_ID = :p_pl_typ_id AND TIMESTAMP = (SELECT MAX(TIMESTAMP) FROM BEN.BEN_COMP_OBJ_CACHE WHERE BUSINESS_GROUP_ID = :p_bg_id);

Related Objects

The FK relationship metadata documents one child table referencing this object, with additional dependencies declared through lookups on the cached entities:

• BEN.BEN_COMP_OBJ_CACHE_ROW — child table; joins on BEN_COMP_OBJ_CACHE_ROW.COMP_OBJ_CACHE_ID = BEN_COMP_OBJ_CACHE.COMP_OBJ_CACHE_ID, holding the decomposed rows of the cache entry.
• HR_ORGANIZATION_UNITS — referenced by BUSINESS_GROUP_ID, providing the business group context.
• BEN.BEN_PGM_F — referenced by PGM_ID, the program definition.
• BEN.BEN_PL_F — referenced by PL_ID, the plan definition.
• BEN.BEN_PL_TYP_F — referenced by PL_TYP_ID, the plan type definition.
• FND_USER — referenced by CREATED_BY and LAST_UPDATED_BY for WHO column attribution.
• FND_LOGINS — referenced by LAST_UPDATE_LOGIN for session attribution.
• Manage Life Events concurrent program — the process that populates and consumes this cache during a run.

Queries that resolve BEN_COMP_OBJ_CACHE by its primary key (BEN_COMP_OBJ_CACHE_PK) are index-driven and inexpensive; queries that scan by business group or effective date rely on the FK index and are typically bounded by run scope.