Results for “all_r_any_cd”

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

Overview

BEN_CM_TYP_USG_F is a date-tracked (non-FND) configuration table in the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the defined usages of communication types — that is, which communication types are permitted or required for specific benefits enrollment activities and enrollment periods. Each row represents a single, effective-dated usage association that governs how the Benefits engine selects and delivers communications during plan enrollment, life-event processing, and related activities.

The table is owned by the BEN schema and is classified as VALID in the ETRM 12.2.2 repository. Its heuristic Data Vault classification — mined from the foreign-key structure — is link. This suggests modeling BEN_CM_TYP_USG_F as a link table whose purpose is to resolve many-to-many associations between communication types, enrollment periods, and action types, rather than as a hub (which would carry the natural business key alone) or a pure satellite. The 51-column physical schema includes a standard attribute overflow block (CTU_ATTRIBUTE1 through CTU_ATTRIBUTE30), indicating the table is also a Descriptive Flexfield (DFF) enabled entity.

Key Information Stored

The surrogate primary key is a composite: CM_TYP_USG_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE, enforced by the unique index BEN_CM_TYP_USG_F_PK. CM_TYP_USG_ID is the system-generated identifier that users typically search for, and it is the value surfaced in the ETRM as the object's business-key candidate alongside the two date columns that implement the table's date-tracking strategy.

The most significant payload columns are:

• BUSINESS_GROUP_ID — the operating business group (HR security) that owns the row.
• CM_TYP_ID — the communication type being used; the central foreign reference to the communication type definition.
• CM_USG_RL — the usage rule describing how the communication type is applied.
• ALL_R_ANY_CD — the "all / any" indicator controlling whether all listed entities or any one of them satisfies the usage.
• DESCR_TEXT — descriptive text explaining the usage.
• ENRT_PERD_ID — foreign key to BEN_ENRT_PERD, tying the usage to a specific enrollment period.
• ACTN_TYP_ID — foreign key to BEN_ACTN_TYP, tying the usage to a life-event or benefits action type.
• LER_ID, PGM_ID, PL_ID, PL_TYP_ID — the organizational context (legal employer, program, plan, plan type) within which the usage applies.
• CTU_ATTRIBUTE_CATEGORY and CTU_ATTRIBUTE1–30 — the descriptive flexfield context and attribute segments.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, OBJECT_VERSION_NUMBER — standard WHO audit columns supporting concurrency and change tracking.

Common Use Cases and Queries

Because CM_TYP_USG_ID is a surrogate identifier, the most frequent lookup is a direct key fetch, optionally restricted to the currently active effective date range:

• Point-in-time retrieval: SELECT * FROM ben.ben_cm_typ_usg_f WHERE cm_typ_usg_id = :p_id AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
• Usage listing by communication type: join CM_TYP_ID to the communication type entity to enumerate every configured usage and its date range.
• Enrollment-period reporting: join ENRT_PERD_ID to BEN_ENRT_PERD to report which communication usages are active for an open enrollment window.
• Life-event configuration audit: join ACTN_TYP_ID to BEN_ACTN_TYP to verify which communications fire on each action type.
• Organizational scope checks: filter by PGM_ID, PL_ID, or PL_TYP_ID to confirm a usage is limited to the correct program or plan.
• DFF reporting: group or filter by CTU_ATTRIBUTE_CATEGORY to expose client-specific attributes captured for each usage.

Note that because the table is date-tracked, queries should always constrain on effective dates to avoid returning superseded versions of the same usage.

Related Objects

The following objects are the most significant dependencies, derived from the documented foreign-key and primary-key relationships:

• BEN_ACTN_TYP — referenced via BEN_CM_TYP_USG_F.ACTN_TYP_ID; supplies the action type that triggers the usage.
• BEN_ENRT_PERD — referenced via BEN_CM_TYP_USG_F.ENRT_PERD_ID; defines the enrollment period context.
• BEN_CM_TYP_F — the communication type definition referenced through CM_TYP_ID; supplies the communication type itself.
• BEN_PGM_F, BEN_PL_F, BEN_PL_TYP_F — the program, plan, and plan type entities referenced through PGM_ID, PL_ID, and PL_TYP_ID, providing organizational scope.
• BEN_LER_F — the legal employer reference reached via LER_ID.
• BEN_CM_TYP_USG_F_PK — the unique index and primary-key constraint that enforces uniqueness of CM_TYP_USG_ID together with the effective date range.

No public PL/SQL API is documented specifically for this table in the ETRM excerpt; maintenance is performed through the Advanced Benefits setup UI and the standard date-tracked DML conventions.