Search Results ben_comp_obj_cache




Overview
Explore in AI Chat

Overview

BEN.BEN_COMP_OBJ_CACHE is a table within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, documented across releases 12.1.1 and 12.2.2. Per the ETRM metadata, its description is "Compensation object cache, by run date." The object therefore functions as a transient or semi-persistent staging cache that stores evaluated compensation objects — plans, programs, and plan types — as they are resolved for a specific processing run identified by an effective date. Advanced Benefits relies heavily on in-memory and cached evaluation of eligibility, enrollment, and rate objects; this table persists those computed results so that subsequent stages of the same run, or downstream batch processes, can reuse them without re-evaluation.

The heuristic Data Vault classification mined from the foreign key structure is standalone. This indicates that the table has no outgoing foreign keys to other hubs or satellites and is instead referenced by children (specifically BEN_COMP_OBJ_CACHE_ROW). In Data Vault modeling terms, this positions the table closest to a hub or a run-scoped driver, with subordinate detail held in dependent tables. Modelers should treat it as an independent, run-anchored entity rather than a link between business entities.

Key Information Stored

The documented physical schema contains 16 columns. The surrogate primary key is COMP_OBJ_CACHE_ID, enforced by the unique index BEN_COMP_OBJ_CACHE_PK, which is also the only documented business-key candidate. The most significant columns are:

  • COMP_OBJ_CACHE_ID — surrogate primary key and the join key to BEN_COMP_OBJ_CACHE_ROW.
  • EFFECTIVE_DATE — the run date anchoring each cached evaluation snapshot, consistent with the "by run date" description.
  • BUSINESS_GROUP_ID — the HR business group (operating unit) owning the compensation data, enabling multi-tenant partitioning of cache entries.
  • MODE_CD — the mode code governing how compensation objects were processed (for example, a run or simulation mode).
  • NO_PLANS, NO_PROGRAMS — cached aggregate counts of plans and programs resolved for the run.
  • PGM_ID — identifier of the program captured in the cache.
  • PL_ID, PL_TYP_ID — the plan identifier and its plan type, linking the cache entry to the specific benefit plan configuration.
  • TIMESTAMP — a processing timestamp supporting staleness detection and purge logic.
  • OBJECT_VERSION_NUMBER — the standard EBS optimistic locking column.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO audit columns.

Common Use Cases and Queries

Typical usage centers on diagnostics and reconciliation of benefits runs. A DBA or functional analyst may enumerate cache headers for a given business group and run date to determine what was evaluated and how many plans or programs were resolved. A representative query is:

  • SELECT c.COMP_OBJ_CACHE_ID, c.EFFECTIVE_DATE, c.BUSINESS_GROUP_ID, c.MODE_CD, c.NO_PLANS, c.NO_PROGRAMS, 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_effective_date;
  • Joining to the child table to inspect individual cached rows: SELECT h.COMP_OBJ_CACHE_ID, r.* FROM BEN.BEN_COMP_OBJ_CACHE h, BEN.BEN_COMP_OBJ_CACHE_ROW r WHERE h.COMP_OBJ_CACHE_ID = r.COMP_OBJ_CACHE_ID;
  • Purging stale cache entries by run date to reclaim space after a processing cycle, using TIMESTAMP or EFFECTIVE_DATE as the cutoff.

Because the table is an internal cache, it is not typically surfaced in end-user reports, but it is frequently referenced during performance troubleshooting when Advanced Benefits processes run longer than expected or produce inconsistent results.

Related Objects

The documented foreign key relationship identifies one dependent table, and related BEN objects in the compensation evaluation path are commonly joined:

  • BEN.BEN_COMP_OBJ_CACHE_ROW — child table referencing COMP_OBJ_CACHE_ID; holds the per-row detail for each cached compensation object.
  • BEN.BEN_PGM — program definition referenced conceptually by PGM_ID.
  • BEN.BEN_PL — plan definition referenced by PL_ID.
  • BEN.BEN_PL_TYP — plan type referenced by PL_TYP_ID.
  • HR.HR_ALL_ORGANIZATION_UNITS or PER_BUSINESS_GROUPS — resolves BUSINESS_GROUP_ID to a named business group.
  • FND_LOG_MESSAGES / concurrent request tables — correlate cache population with the batch run that created it.

Together these objects support the evaluation, caching, and audit of Advanced Benefits compensation processing.