Results for “ben_cm_typ_d”

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

Overview

BEN_CM_TYP_D is a denormalized reporting view owned by the APPS schema in Oracle Advanced Benefits (BEN). It exposes configuration data for benefit communication types (CM types), which define the printable and electronic communications — such as enrollment confirmations, statements, and letters — generated during benefits processing. The view consolidates the base communication type definition table BEN_CM_TYP_F with decoded lookup meanings, formula names, parent communication type names, and audit user information, so that reporting tools and downstream integrations can query human-readable values without performing multiple joins.

The view carries the standard EBS effective-dating columns (EFFECTIVE_START_DATE and EFFECTIVE_END_DATE) inherited from BEN_CM_TYP_F, and filters formula references by matching the communication type's effective start date to the formula's effective date range. This design ensures that the formula names reported are those in force at the time the communication type row became effective.

Underlying Base Objects

The view is defined over the following documented base objects:

• BEN_CM_TYP_F — the primary table holding communication type definitions. It supplies CM_TYP_ID, name, description, flags, and foreign keys to lookups and formulas.
• FF_FORMULAS_F — referenced three times (aliases CM_TYP, TO_BE_SENT_DT_B, and INSPN_RQD_B) to resolve formula names for the communication type, the "to be sent date" rule, and the inspection-required rule.
• HR_LOOKUPS — joined six times against lookup types BEN_CM_USG, BEN_PC_KIT, BEN_TO_BE_SENT_DT, BEN_RCPENT, and YES_NO, translating coded values into MEANING text.
• FND_USER — resolves LAST_UPDATED_BY into the user name.
• BEN_CM_TYP_F (self-join) — via PARNT_CM_TYP to retrieve the parent communication type name.
• HR_API — an ETRM-documented referenced package used by Advanced Benefits utilities associated with this view's data.

All lookup and self-joins are outer joins, so a communication type row is returned even when optional lookups or parent references are null.

Key Columns

• CM_TYP_ID, ROW_ID — primary identifier and row locator for the communication type.
• NAME, SHRT_NAME, DESC_TXT — the communication type's display name, short name, and description.
• FORMULA_NAME — the formula associated with the communication type (CM_TYP_RL).
• INSPN_RQD.MEANING and INSPN_RQD_B.FORMULA_NAME — the decoded Yes/No value of the INSPN_RQD_FLAG and the formula driving the inspection-required rule.
• TO_BE_SENT_DT.MEANING, TO_BE_SENT_DT_B.FORMULA_NAME — decoded send-date basis and its formula.
• CM_USG.MEANING, PC_KIT.MEANING, RCPENT.MEANING, WHNVR_TRGRD.MEANING, TRK_MLG.MEANING — decoded usage, print kit, recipient, trigger, and mileage-tracking lookups.
• PARNT_CM_TYP.NAME — the parent communication type, where one exists.
• MX_NUM_AVLBL_VAL, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY — maximum available value, effective dates, and audit columns.

Common Use Cases and Queries

Because the user search term was inspn_rqd, the most relevant scenario is identifying communication types that require inspection and the formula that governs that requirement:

• Listing all active communication types with their decoded usage, recipient, and inspection flag.
• Auditing which formula implements the inspection-required rule for a given communication type.
• Feeding configuration extracts for benefits setup migration or documentation.

Sample query:

SELECT cm_typ_id, name, inspn_rqd.meaning inspn_rqd_meaning, inspn_rqd_b.formula_name inspn_formula, cm_usg.meaning usage, recipient.meaning recipient FROM apps.ben_cm_typ_d WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date AND inspn_rqd.meaning = 'Yes' ORDER BY name;

Analysts should note the view is a reporting convenience only; DML against it is not supported, and all maintenance must occur through the Advanced Benefits setup forms that write to BEN_CM_TYP_F.