Results for “benbv_cm_typ_v”

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

Overview

The BENBV_CM_TYP_V view is a descriptive, read-only view owned by the APPS schema within the BEN — Advanced Benefits product of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. As documented in the ETRM metadata, the view identifies all communications provided to persons by the plan sponsor. In functional terms, it exposes the definition of communication types — the configurable categories that govern how letters, statements, enrollment confirmations, and other participant communications are generated and delivered within Oracle Advanced Benefits.

The view is intended for reporting and integration rather than for direct data manipulation. It is defined WITH READ ONLY, meaning it cannot be used as a target for DML statements. Reporting tools such as Oracle Business Intelligence (OBIEE), Discoverer, and BI Publisher, as well as inbound integration routines, commonly query this view to resolve communication-type definitions into human-readable values without navigating the underlying translated base tables manually.

Underlying Base Objects

Per the documented metadata, BENBV_CM_TYP_V is defined over the following base objects:

• BEN_CM_TYP_F (SYNONYM) — the core, non-translated table holding communication type definitions.
• BEN_CM_TYP_F_TL (SYNONYM) — the translated (language) table supplying the NAME column.
• HR_BIS (PACKAGE) — the Business Intelligence System utility package, referenced here via BIS_DECODE_LOOKUP to translate coded values into their descriptive equivalents.

The view joins BEN_CM_TYP_F (aliased CCT) to BEN_CM_TYP_F_TL (aliased TL) on both CM_TYP_ID and EFFECTIVE_START_DATE, with the additional predicate TL.LANGUAGE = USERENV('LANG'). This ensures that only the language-appropriate translation row is returned for the current session, and that effective-dated records are matched correctly. The join pattern reflects the standard EBS date-tracked (datetracked) architecture, where communication types are effective-dated rather than simply flagged active.

Key Columns

The view exposes the following significant columns, organized by purpose:

• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the datetrack range during which the communication type is valid.
• NAME — the translated display name of the communication type, sourced from BEN_CM_TYP_F_TL.
• DESC_TXT / SHRT_NAME — the long description and short code name.
• CM_TYP_RL — the communication type rule linkage.
• MX_NUM_AVLBL_VAL — the maximum number of available values for the type.
• TO_BE_SENT_DT_CD and its decoded counterpart TO_BE_SENT_DT_CD_M — the "to be sent" date code, with the decoded display value produced by BIS_DECODE_LOOKUP('BEN_TO_BE_SENT_DT', ...).
• RCPENT_CD / RCPENT_CD_M — the recipient code and its decoded description.
• CM_USG_CD / CM_USG_CD_M — the communication usage code and decoding.
• PC_KIT_CD / PC_KIT_CD_M — the print/kit code and decoding.
• WHNVR_TRGRD_FLAG, TRK_MLG_FLAG, INSPN_RQD_FLAG (and their _M decoded columns) — Yes/No indicators decoded via BIS_DECODE_LOOKUP('YES_NO', ...) for whenever-triggered, tracking-mailing, and inspection-required behavior.
• CM_TYP_ID, BUSINESS_GROUP_ID, PARNT_CM_TYP_ID — primary key, business group identifier, and parent communication type identifier supporting hierarchical definitions.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE — standard WHO audit columns.

The column "_DF:CCT" is a descriptive flexfield reference, indicating a DFF context defined on the BEN_CM_TYP_F entity.

Common Use Cases and Queries

Typical scenarios include reconciling communication type definitions during implementations, building LOV-based reports for enrollment correspondence, and validating setup before communication generation cycles run.

SELECT cm_typ_id,
       name,
       shrt_name,
       rcpent_cd_m,
       cm_usg_cd_m,
       whnvr_trgrd_flag_m
FROM   apps.benbv_cm_typ_v
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
ORDER  BY name;

To list only parent communication types (those without a parent), a filter on PARNT_CM_TYP_ID IS NULL is applied:

SELECT cm_typ_id, name, desc_txt
FROM   apps.benbv_cm_typ_v
WHERE  parnt_cm_typ_id IS NULL;

Because the view is read-only and already performs the language join and code decoding, it is the preferred source for reporting queries, avoiding the need to replicate BIS_DECODE_LOOKUP logic against the base tables. Joining BENBV_CM_TYP_V to communication delivery or participant tables on CM_TYP_ID allows reports to present fully translated communication definitions alongside transactional records.