Search Results ben_per_cm_f

Overview

BEN_PER_CM_F is a transactional table in the BEN (Advanced Benefits) schema of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. It stores the communications records created for a person as the direct result of a benefits communications trigger firing. In other words, when the Benefits engine evaluates a communications trigger — for example, a life event, an enrollment action, or a scheduled processing cycle — and determines that a communication (such as a statement, letter, or notification) must be generated for a participant, the resulting communication instance is persisted here.

The table follows the standard Oracle EBS date-tracked (datetrack) design pattern. Every row is uniquely identified by the composite primary key BEN_PER_CM_F_PK, composed of PER_CM_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. The presence of EFFECTIVE_START_DATE and EFFECTIVE_END_DATE means historical versions of a communication record are retained, and DATE_TRACK logic must be applied when querying current data. From a Data Vault modeling perspective, the metadata classifies this table as satellite-leaning: it records descriptive, time-variant attributes about a communication event and references an upstream parent, rather than acting purely as a hub or link.

Key Information Stored

The table contains 54 documented columns. The most significant are summarized below; the remaining PCM_ATTRIBUTE1 through PCM_ATTRIBUTE30 columns are descriptive flexfield segments reserved for customer-defined extensions.

• PER_CM_ID — Surrogate identifier for the person communication record; part of the composite primary key.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — Date-track columns forming the remainder of the primary key and defining the validity window of each row.
• PERSON_ID — The person (participant) for whom the communication was created.
• CM_TYP_ID — The communication type that was triggered, driving the content and delivery format.
• BUSINESS_GROUP_ID — The HR business group (enterprise) that owns the record, used for multi-tenant filtering.
• RQSTBL_UNTL_DT — The date up to which the communication remains requestable or actionable.
• LF_EVT_OCRD_DT — The life event occurred date that provided the trigger context.
• LER_ID — The life event record associated with the communication.
• PER_IN_LER_ID — Foreign key to BEN_PER_IN_LER, linking the communication to the person's life event participation record.
• BNF_PERSON_ID / DPNT_PERSON_ID / PRTT_ENRT_ACTN_ID — Related person and participant enrollment action references that identify beneficiary, dependent, and action context.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — Concurrency and audit columns identifying the concurrent request and program that created or updated the row.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the OAF/BC4J layer.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Oracle EBS who-columns for audit and row-level security.

The only documented unique index is BEN_PER_CM_F_PK; there are no additional documented business-key candidates beyond the date-tracked primary key.

Common Use Cases and Queries

Typical reporting and support scenarios include auditing which communications were generated for a participant, reconciling communication volumes against trigger processing, and diagnosing why a participant did or did not receive a communication. Because the table is date-tracked and contains flexfield segments, most queries filter on the current effective row and on PERSON_ID or CM_TYP_ID.

A representative current-record query is:

SELECT per_cm_id, person_id, cm_typ_id, cm.NAME,
       lf_evt_ocrd_dt, rqstbl_untl_dt
FROM   ben.ben_per_cm_f f,
       ben.ben_cm_typ cm
WHERE  f.cm_typ_id = cm.cm_typ_id
AND    f.person_id = :p_person_id
AND    TRUNC(SYSDATE) BETWEEN f.effective_start_date
                          AND f.effective_end_date;

Additional use cases include exception reports identifying communications past their RQSTBL_UNTL_DT, volume trending by CM_TYP_ID, and joins to enrollment action data via PRTT_ENRT_ACTN_ID to confirm that a communication was driven by the correct participant action.

Related Objects

• BEN_PER_IN_LER — Foreign parent referenced by BEN_PER_CM_F.PER_IN_LER_ID; supplies the life event participation context for the communication.
• BEN_PER_CM_F_PK / BEN_PER_CM_F_N1 — Primary and supporting indexes used to resolve current effective rows.
• PER_ALL_PEOPLE_F — Joined on PERSON_ID (and BNF_PERSON_ID / DPNT_PERSON_ID) to resolve participant names.
• BEN_CM_TYP — Communication type lookup joined on CM_TYP_ID for descriptive text and format.
• BEN_PER_LER / BEN_LER — Life event records joined on LER_ID to obtain trigger details.
• BEN_PRTT_ENRT_ACTN — Participant enrollment action joined on PRTT_ENRT_ACTN_ID.
• BEN_PERSON_COMMUNICATION — Closely related communication object used in the Benefits communications processing package.
• BEN_COMMUNICATION_UTILITY (and related BEN communications PL/SQL APIs) — Program units that create rows in this table when triggers fire.

Oracle proprietary and confidential. This summary is derived from the ETRM 12.2.2 metadata for BEN.BEN_PER_CM_F.