Search Results ben_cm_typ_trgr_d




Overview
Explore in AI Chat

Overview

The BEN_CM_TYP_TRGR_D view is a denormalized reporting object within the Oracle EBS Advanced Benefits (BEN) module, owned by the APPS schema and holding a VALID status in 12.1.1 and 12.2.2. It presents a flattened, user-friendly representation of the configuration that links communication types (CM_TYP), communication triggers (CM_TRGR), and the formulas that govern when a communication should be generated. In the Benefits architecture, these three constructs form the rules engine that determines which participant communications are produced and when — for example, a "Life Event" communication type triggered by "Marriage," driven by a specific Oracle Fast Formula.

During normal configuration, this information is stored across multiple normalized tables. The _D suffix on the view signals it is a descriptive/denormalized view intended to expose human-readable names and descriptions rather than just numeric foreign keys. Application developers, technical consultants, and support analysts use it to audit communication configuration, build ad hoc reports, and troubleshoot why a given communication was or was not generated.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, BEN_CM_TYP_TRGR_D is defined against the following base objects (all resolved through APPS synonyms):

  • BEN_CM_TYP_TRGR_F — the primary fact table joining communication types to their triggers; it supplies CM_TYP_TRGR_ID, the effective dates, LAST_UPDATED_BY, and the association to the trigger formula.
  • BEN_CM_TRGR — the communication trigger definition, providing DESC_TXT and trigger identity.
  • BEN_CM_TYP_F — the communication type definition, providing the NAME used for the type.
  • FF_FORMULAS_F — the Fast Formulas repository, supplying FORMULA_NAME for the trigger rule.
  • FND_USER — the applications user table, resolving LAST_UPDATED_BY to a user identity.

The joins are versioned. The view filters on CTT.EFFECTIVE_START_DATE BETWEEN the effective date ranges of both the trigger (CM_TYP) and the trigger-formula definition, ensuring the descriptive attributes returned are valid as of the row's effective start date. All joined tables use Oracle outer-join syntax (the legacy (+) operator), so a communication type-trigger combination will still appear even when a related trigger, type, formula, or user record is missing.

Key Columns

  • ROW_ID — the ROWID of the underlying BEN_CM_TYP_TRGR_F row, a unique row locator.
  • CM_TYP_TRGR_ID — the primary table identifier for the type-to-trigger association.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-enabled range during which the association is in force.
  • CM_TYP_TRGR_NAME (FORMULA_NAME) — the Fast Formula name that implements the trigger rule logic.
  • CM_TRGR_NAME (DESC_TXT) — the descriptive text of the communication trigger.
  • CM_TYP_NAME (NAME) — the communication type name.
  • LAST_UPDATE_DATE / LAST_UPDATED_BY — audit columns; the latter is resolved to an FND_USER identity.

Common Use Cases and Queries

Typical uses include auditing which formula is attached to a trigger/type combination, listing all active trigger associations for a communication type, and identifying recently modified configuration for change-control purposes.

SELECT cm_typ_trgr_id,
       cm_typ_name,
       cm_trgr_name,
       cm_typ_trgr_name,
       effective_start_date,
       effective_end_date
FROM   apps.ben_cm_typ_trgr_d
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
ORDER  BY cm_typ_name, cm_trgr_name;

To trace formula usage:

SELECT cm_typ_trgr_name, cm_trgr_name, cm_typ_name
FROM   apps.ben_cm_typ_trgr_d
WHERE  cm_typ_trgr_name = 'BEN_LIFE_EVENT_TRIGGER';

Because the view is subject to effective-dating, queries should always constrain on a reference date (typically SYSDATE) to avoid returning multiple historical versions of the same association.