Results for “ben_cm_typ_f_vl”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BEN_CM_TYP_F_VL is a localized (MLS-compliant) view owned by the APPS schema within the BEN — Advanced Benefits product of Oracle E-Business Suite. It exposes the definition of communication types used by the Advanced Benefits communication framework to classify, schedule, and deliver participant communications such as enrollment notices, confirmation letters, and follow-up correspondence.

The "_VL" suffix indicates that this is a translation view. It joins the transactional base entity table to its translation table for the current session language, determined by USERENV('LANG'). This design filters the multi-row language result set down to a single translatable description per record, which makes the view well suited for concurrent programs, AME/BEN workflows, BI Publisher data templates, and OAF-based or custom reporting layers that must present readable, language-specific communication type definitions rather than raw IDs. Because it derives from the same underlying entity as BEN_CM_TYP_F, the view preserves the full attribute surface (DDF-style CCT_ATTRIBUTE columns plus standard WHO columns) while adding the translated NAME.

Underlying Base Objects

The ETRM documentation records two referenced base objects, both accessed through synonyms: BEN_CM_TYP_F and BEN_CM_TYP_F_TL.

• BEN_CM_TYP_F — the primary communication type definition table. It holds the non-translatable, versioned attributes including effective dating, usage code, recipient code, parent communication type, mailing/kit behavior flags, and the DDF attribute columns.
• BEN_CM_TYP_F_TL — the translation table, which stores the language-specific NAME (and, where used, description text) keyed by language.

The join is performed on CM_TYP_ID and EFFECTIVE_START_DATE, with an additional predicate TL.LANGUAGE = USERENV('LANG'). The parallel join on effective start date confirms that this table is date-tracked, meaning multiple effective-dated versions of a communication type may coexist and the view returns the applicable row for the language and date combination present in the base records.

Key Columns

• CM_TYP_ID — primary identifier for the communication type; the principal foreign key into downstream communication setup and delivery tables.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-effective boundaries governing which version of the definition is valid.
• NAME — the translatable, user-facing communication type name sourced from BEN_CM_TYP_F_TL.
• CM_USG_CD — the usage code that classifies how the communication type is intended to be consumed; this is the column most directly associated with the user's search term.
• CM_TYP_RL — the rule or selection logic reference associated with the communication type.
• RCPENT_CD — the recipient code identifying the target audience of the communication.
• PARNT_CM_TYP_ID — self-referencing parent communication type, supporting hierarchical grouping of communications.
• WHNVR_TRGRD_FLAG, TRK_MLG_FLAG, INSPN_RQD_FLAG — behavioral flags controlling trigger behaviour, mailing tracking, and inspection requirements.
• PC_KIT_CD, MX_NUM_AVLBL_VAL, TO_BE_SENT_DT_CD, TO_BE_SENT_DT_RL, INSPN_RQD_RL — kit, availability, and scheduling attributes.
• BUSINESS_GROUP_ID — multi-tenant/operating-unit style segregation key.
• CCT_ATTRIBUTE_CATEGORY and CCT_ATTRIBUTE1–30 — descriptive flexfield context and segment columns.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — audit and optimistic-locking columns.

Common Use Cases and Queries

Typical usage includes resolving CM_USG_CD values to readable names for reporting, driving BI Publisher communication letter templates, and validating communication setup during configuration or data migration. A representative query filtering by usage code is:

• SELECT cm_typ_id, name, cm_usg_cd, rcpent_cd, effective_start_date, effective_end_date FROM apps.ben_cm_typ_f_vl WHERE cm_usg_cd = :p_usage AND SYSDATE BETWEEN effective_start_date AND NVL(effective_end_date, SYSDATE);
• SELECT cm_typ_id, name, shrt_name, cm_usg_cd FROM apps.ben_cm_typ_f_vl WHERE business_group_id = :p_bg_id ORDER BY name; — for populating LOVs and lookup lists.
• SELECT v.name, v.cm_usg_cd FROM apps.ben_cm_typ_f_vl v, apps.ben_cm_typ_f_tl t WHERE v.cm_typ_id = t.cm_typ_id AND t.language = USERENV('LANG'); — illustrative of the underlying translation join.

Because the view already restricts to the session language, developers should not add redundant LANGUAGE predicates when joining further. Queries against BEN_CM_TYP_F_VL should account for effective dating to avoid returning superseded rows.