Search Results ben_per_cm_d




Overview
Explore in AI Chat

Overview

The BEN_PER_CM_D view is a denormalized reporting object within the Oracle Advanced Benefits (BEN) module, owned by the APPS schema and registered as VALID in Oracle EBS 12.1.1 and 12.2.2. It presents benefit participant communication records — specifically the "communication" or "person communication" entity (PER_CM) — enriched with descriptive attributes resolved from related master and transactional tables. Rather than requiring report authors to join six or more base objects manually, the view delivers a flattened, human-readable projection suitable for concurrent program extracts, OAF/Forms customizations, and ad hoc SQL. The view exposes the identifying key (PER_CM_ID) alongside effective dating, the life event that triggered the communication, the beneficiary and dependent person names, the communication type name, and the requestable-until date. Because it resolves surrogate foreign keys into meaningful names, it is well suited to operational reporting and integration payloads where the consuming system expects formatted person and type names rather than IDs.

Underlying Base Objects

The view is defined over six documented objects, all resolved through the APPS synonym layer except FND_USER_VIEW, which is a view:

  • BEN_PER_CM_F — the primary table (aliased PCM), providing PER_CM_ID, effective dates, life event date, requestable-until date, and the foreign keys to person, life event, and type.
  • BEN_PER_IN_LER — the person-in-life-event assignment (aliased PIL), used to derive the current life event occurrence context.
  • BEN_LER_F — the life event reason definition (aliased LER), supplying LER_NAME.
  • BEN_CM_TYP_F — the communication type definition (aliased CM_TYP), supplying CM_TYP_NAME.
  • PER_ALL_PEOPLE_F — joined twice (BNF_PERSON and DPNT_PERSON) to supply beneficiary and dependent full names.
  • FND_USER_VIEW — supplies the LAST_UPDATED_BY user name via LAST_UPDATED_BY = USER_ID.

All joins to the descriptive tables are outer joins (+), meaning a PER_CM row with unresolved foreign keys will still appear, with NULL names. The join to BEN_PER_IN_LER includes a filter excluding person-in-life-event rows whose status is 'VOIDD' or 'BCKDT' (or NULL), ensuring only active life event assignments are considered.

Key Columns

  • ROW_ID — the ROWID of BEN_PER_CM_F, useful for row-level identification.
  • PER_CM_ID — primary key of the participant communication record.
  • EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-effective window of the record.
  • LF_EVT_OCRD_DT — the date the triggering life event occurred.
  • LER_NAME — the life event reason name.
  • BNF_PERSON_NAME — full name of the beneficiary person.
  • DPNT_PERSON_NAME — full name of the dependent person. This is the column most users retrieve when searching for "dpnt_person_name."
  • CM_TYP_NAME — the communication type name.
  • RQSTBL_UNTL_DT — the date until which the communication remains requestable.
  • LAST_UPDATE_DATE / LAST_UPDATED_BY — audit columns; LAST_UPDATED_BY is resolved to a username.

Common Use Cases and Queries

Typical uses include beneficiary/dependent communication extracts, life event notification reports, and data reconciliation prior to interfaces. A representative query selecting dependent names for a given life event reason:

  • SELECT PER_CM_ID, DPNT_PERSON_NAME, BNF_PERSON_NAME, CM_TYP_NAME, LF_EVT_OCRD_DT FROM APPS.BEN_PER_CM_D WHERE LER_NAME = 'Marriage' AND DPNT_PERSON_NAME IS NOT NULL ORDER BY LF_EVT_OCRD_DT DESC;
  • SELECT PER_CM_ID, CM_TYP_NAME, RQSTBL_UNTL_DT FROM APPS.BEN_PER_CM_D WHERE RQSTBL_UNTL_DT >= SYSDATE;
  • SELECT PER_CM_ID, DPNT_PERSON_NAME FROM APPS.BEN_PER_CM_D WHERE EFFECTIVE_START_DATE <= SYSDATE AND NVL(EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE;

Consumers should apply effective-date predicates when point-in-time accuracy is required, and be aware that outer joins can yield NULL names where the corresponding person or definition record does not exist.