Search Results ben_cm_typ_f_pk

Overview

BEN_CM_TYP_F is the Advanced Benefits (BEN) communication type definition table in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the master reference definitions for communications that can be generated for participants in a benefits program, such as enrollment confirmation letters, COBRA notices, evidence-of-insurability requests, and other participant-facing correspondence. Each row defines a communication type together with the scheduling behavior, mailing requirement, and functional rules that the Benefits engine applies when the communication is produced.

The table is date-tracked. It uses the primary key BEN_CM_TYP_F_PK, composed of CM_TYP_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, so that a single logical communication type can carry multiple dated versions while preserving history for past-dated benefits transactions. The table owns 57 documented columns, including a dedicated Descriptive Flexfield block and a set of WHO audit columns.

Based on the mined foreign-key structure, the data-vault classification is standalone. Under a heuristic Data Vault model this would be treated as an independent hub-like reference entity keyed on CM_TYP_ID, without a structural parent-child relationship to other hubs. That classification is a modeling suggestion derived from the absence of FK dependencies, not a documented architectural statement.

Key Information Stored

• CM_TYP_ID — surrogate identifier for the communication type; first component of the composite primary key and the principal join key to dependent tables.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the date-tracking components of the primary key, delimiting the validity window of each versioned definition.
• NAME / SHRT_NAME / DESC_TXT — the display name, short name, and long description presented in setup and lookup windows.
• BUSINESS_GROUP_ID — the business group that owns the definition, providing the multi-tenant partitioning key.
• CM_USG_CD / CM_TYP_RL — the communication usage code and the usage rule that determine when and to whom the communication applies.
• PARNT_CM_TYP_ID — self-referencing pointer to a parent communication type, used to organize related communications into hierarchies.
• WHNVR_TRGRD_FLAG / TRK_MLG_FLAG — flags indicating whether the communication is triggered whenever the event occurs and whether mailing is tracked.
• TO_BE_SENT_DT_CD / TO_BE_SENT_DT_RL — the code and rule governing calculation of the send date.
• INSPN_RQD_FLAG / INSPN_RQD_RL — flag and rule indicating whether an inspection or similar response is required.
• RCPENT_CD — the recipient code identifying the default recipient of the communication.
• PC_KIT_CD / MX_NUM_AVLBL_VAL — plan/kit assignment code and the maximum number of available values permitted.
• INACTIVE_FLAG — controls whether the definition remains selectable.
• CCT_ATTRIBUTE_CATEGORY and CCT_ATTRIBUTE1 through CCT_ATTRIBUTE30 — the Descriptive Flexfield context and segment columns for customer-defined extensions.
• OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the concurrency-control and WHO audit columns.

Common Use Cases and Queries

The most frequent requirement is validating the list of currently active communication types for a business group. Because the table is date-tracked, every query should constrain the effective window using SYSDATE:

• List active communication types: SELECT cm_typ_id, name, shrt_name FROM ben.ben_cm_typ_f WHERE business_group_id = :p_bg_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date AND inactive_flag = 'N';
• Resolve a communication type at a historical date by substituting a literal or bind for SYSDATE, which is essential for retroactive benefits reporting.
• Review interface behavior: select WHNVR_TRGRD_FLAG, TRK_MLG_FLAG, TO_BE_SENT_DT_CD, RCPENT_CD, and INSPN_RQD_FLAG to confirm scheduling and mailing rules before enabling a new letter.
• Retrieve Descriptive Flexfield content by selecting CCT_ATTRIBUTE_CATEGORY and the CCT_ATTRIBUTE1–CCT_ATTRIBUTE30 columns for the relevant context.
• Inventory parent-child organization using PARNT_CM_TYP_ID joined back to CM_TYP_ID.
• Audit reference-data changes by filtering on LAST_UPDATE_DATE for a reporting period, using OBJECT_VERSION_NUMBER to detect concurrent updates.

Related Objects

The documented metadata classifies BEN_CM_TYP_F as standalone, so no enforced foreign keys were mined. The following are the significant dependent and referencing objects in the Advanced Benefits schema, joined on the columns identified above:

• BEN_CM_TYP_F — self-referencing join: PARNT_CM_TYP_ID = CM_TYP_ID, used to resolve communication hierarchies.
• BEN_CM_TYP_F_PK — the composite unique index on CM_TYP_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE that enforces primary-key integrity.
• BEN_CM_TYP_TL — translatable text for communication type names and descriptions, joined on CM_TYP_ID with matching effective dates.
• BEN_PERSON_COMMUNICATION — participant communication records that reference the defined CM_TYP_ID.
• BEN_CM_TYP_RL_F — the rule definitions associated with CM_TYP_RL and the communication type.
• BEN_CM_TYP_F (CCT_ATTRIBUTE block) — Descriptive Flexfield setup defined against CCT_ATTRIBUTE_CATEGORY, resolved through the standard flexfield views.
• FND_LOOKUP_VALUES — validates CM_USG_CD, RCPENT_CD, and TO_BE_SENT_DT_CD against the corresponding lookup types.
• HR_ALL_ORGANIZATION_UNITS — resolves BUSINESS_GROUP_ID to its owning business group.