Search Results ben_cm_dlvry_med_typ




Overview
Explore in AI Chat

Overview

BEN_CM_DLVRY_MED_TYP is a configuration table in the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, released under the BEN schema in both 12.1.1 and 12.2.2. The table stores the set of communication delivery medium types available to the application — the actual channels, such as print, e-mail, fax, or web, by which benefits communications are dispatched to participants. It is a foundational lookup and setup entity that supports the broader benefits communication framework, including the generation, scheduling, and delivery of enrollment notices, confirmation statements, and other plan-related correspondence.

From a Data Vault modeling perspective, the table's foreign-key structure classifies it heuristically as satellite-leaning. This is a modeling suggestion rather than a physical designation: the table carries a single parent reference to BEN_CM_DLVRY_MTHD_TYP plus a large set of descriptive and audit attributes, which is characteristic of a descriptive satellite attached to a delivery-method key rather than a hub or a link entity. Because it originates as an EBS transactional setup table, it uses a surrogate primary key and standard EBS audit columns rather than Data Vault hash keys.

Key Information Stored

The documented physical schema for 12.2.2 contains 43 columns. The most significant are:

  • CM_DLVRY_MED_TYP_ID — the surrogate primary key, enforced by the unique index BEN_CM_DLVRY_MED_TYP_PK. All downstream references resolve through this column.
  • CM_DLVRY_MED_TYP_CD — the business-key candidate: the user-visible code that identifies a delivery medium type within a business group. This is the column exposed in lookups and setup forms.
  • BUSINESS_GROUP_ID — the multi-tenant discriminator that scopes each row to a specific business group, enabling the same medium type to be configured independently per enterprise structure.
  • CM_DLVRY_MTHD_TYP_ID — the foreign key to BEN_CM_DLVRY_MTHD_TYP, linking each medium to its parent delivery method type and establishing the delivery hierarchy used by the communications engine.
  • RQD_FLAG — indicates whether selection of the medium is mandatory for the associated delivery method.
  • DFLT_FLAG — identifies the default medium applied when no explicit selection is made.
  • CMD_ATTRIBUTE_CATEGORY and CMD_ATTRIBUTE1 through CMD_ATTRIBUTE30 — the descriptive flexfield segments, providing 30 extensible columns plus a context category for customer-specific configuration without schema change.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard EBS who-columns used for audit, interfaces, and change detection.
  • OBJECT_VERSION_NUMBER — the optimistic locking column used by the OAF-based forms to prevent concurrent update conflicts.

Common Use Cases and Queries

The table is primarily consumed during benefits communication setup and during runtime resolution of delivery channels. Typical operational queries include retrieving all active media for a given business group, or resolving the default medium for a delivery method:

  • Setup validation: SELECT CM_DLVRY_MED_TYP_CD, RQD_FLAG, DFLT_FLAG FROM BEN.BEN_CM_DLVRY_MED_TYP WHERE BUSINESS_GROUP_ID = :p_bg_id ORDER BY CM_DLVRY_MED_TYP_CD;
  • Default resolution: SELECT CM_DLVRY_MED_TYP_ID FROM BEN.BEN_CM_DLVRY_MED_TYP WHERE CM_DLVRY_MTHD_TYP_ID = :p_mthd AND DFLT_FLAG = 'Y' AND BUSINESS_GROUP_ID = :p_bg_id;
  • Extensibility reporting: queries against the CMD_ATTRIBUTE segments surface customer-defined attributes for integration or extract programs.
  • Change auditing and reconciliation: LAST_UPDATE_DATE comparisons against the delivery method parent to detect configuration drift between environments.

Related Objects

  • BEN_CM_DLVRY_MTHD_TYP — the parent lookup table; joined on BEN_CM_DLVRY_MED_TYP.CM_DLVRY_MTHD_TYP_ID = BEN_CM_DLVRY_MTHD_TYP.CM_DLVRY_MTHD_TYP_ID. This is the only documented foreign key.
  • BEN_CM_DLVRY_MTHD_TYP_PK — the primary-key index definition on the parent table, relevant when validating referential integrity.
  • BEN_CM_DLVRY_MED_TYP_PK — the unique index on CM_DLVRY_MED_TYP_ID that anchors all joins from dependent communications tables.
  • BEN_CM_DLVRY_MTHD_TYP.CM_DLVRY_MTHD_TYP_ID — the referenced column set for the documented FK, frequently materialized in reporting views.
  • Benefits communication setup and delivery concurrent programs, which read the RQD_FLAG and DFLT_FLAG columns to determine channel selection behavior.
  • OAF-based Benefits setup pages that maintain rows via the object version number for concurrency control.