Search Results ben_cm_typ_trgr_f_pk




Overview
Explore in AI Chat

Overview

BEN_CM_TYP_TRGR_F is a table in the BEN (Advanced Benefits) product schema within Oracle E-Business Suite, documented as valid in ETRM releases 12.1.1 and 12.2.2. The table stores the type of communication trigger, functioning as a configuration store that defines which communication types are driven by a given communication trigger. It sits at the intersection of the communication type and communication trigger dimensions of the Advanced Benefits communication framework, and it governs how participant communications are generated when lifecycle events or other conditions are satisfied.

The object is date-tracked via EFFECTIVE_START_DATE and EFFECTIVE_END_DATE, and it carries the standard BEN audit columns along with a multi-tenant BUSINESS_GROUP_ID and an OBJECT_VERSION_NUMBER for optimistic locking. Mined from its foreign key structure, the heuristic Data Vault classification for this object is satellite-leaning: it primarily holds descriptive, time-varying attributes attached to a parent business key rather than acting as a hub or a connecting link.

Key Information Stored

The table's primary key, BEN_CM_TYP_TRGR_F_PK, is composed of CM_TYP_TRGR_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. CM_TYP_TRGR_ID is the surrogate identifier for each dated row, while the effective date pair establishes the validity window required for date-tracked configuration.

The most functionally significant columns are:

  • CM_TRGR_ID – foreign key to BEN_CM_TRGR, identifying the communication trigger to which the row belongs.
  • CM_TYP_ID – identifies the communication type associated with the trigger.
  • CM_TYP_TRGR_RL – the relationship or role indicator that qualifies how the type and trigger interact.
  • BUSINESS_GROUP_ID – the operating unit or business group that owns the configuration row, supporting multi-tenant partitioning.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE – define the date range during which the configuration is active.

The remaining columns include 30 descriptive flexfield attributes (CTT_ATTRIBUTE_CATEGORY through CTT_ATTRIBUTE30) used for customer-specific extensibility, plus standard audit columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE, and OBJECT_VERSION_NUMBER. The business-key candidate is the primary key itself; there is no separate unique index documented beyond BEN_CM_TYP_TRGR_F_PK.

Common Use Cases and Queries

Typical uses involve resolving the active communication types for a trigger as of a given date, and diagnosing why a participant communication was or was not generated. A common pattern joins the trigger table to this configuration table, filtering on the effective date window:

  • SELECT ct.cm_trgr_id, ct.cm_typ_id, ct.cm_typ_trgr_rl FROM ben.ben_cm_typ_trgr_f ct WHERE ct.cm_trgr_id = :p_trigger_id AND TRUNC(SYSDATE) BETWEEN ct.effective_start_date AND ct.effective_end_date AND ct.business_group_id = :p_bg_id;
  • Reporting on configured trigger-to-type mappings by business group.
  • Auditing changes over time using EFFECTIVE_START_DATE to reconstruct historical configuration.
  • Reading descriptive flexfield attributes (CTT_ATTRIBUTE1–30) for locale- or plan-specific customization.

Related Objects

The documented foreign key relationship connects this table to BEN_CM_TRGR on CM_TRGR_ID. Because the table also carries CM_TYP_ID, the communication type definition table (BEN_CM_TYP) is functionally related through that column. Additional related configuration objects include the communication type and trigger setup entities used by the Advanced Benefits communication engine, along with the standard BEN date-tracked base tables that share the EFFECTIVE_START_DATE/EFFECTIVE_END_DATE pattern. Query performance is best served by joining on CM_TRGR_ID and constraining by BUSINESS_GROUP_ID and the effective date range.