Search Results per_cm

Overview

APPS.BEN_PER_CM_PRVDD_D is a reporting and inquiry view in the Oracle EBS Advanced Benefits (OLTP) schema. It presents the provisioning history of a participant's benefit communication — that is, the record of a communication that has been (or is scheduled to be) delivered to a person, together with the delivery method, delivery medium, inspection and resend information, and the associated life event and address context. The view denormalizes a number of lookup codes into their translated meanings using HR_LOOKUPS, and joins to FND_USER_VIEW to resolve the identity of the last updater. It is intended for reporting, extracts, and integration consumers that require a human-readable, flattened representation of the base provisioning table BEN_PER_CM_PRVDD_F.

Underlying Base Objects

The view is defined over eight documented base objects and a set of lookup views:

• BEN_PER_CM_PRVDD_F — the driving table; holds the per-communication provisioning (delivery) rows, keyed by PER_CM_PRVDD_ID with effective dating via EFFECTIVE_START_DATE and EFFECTIVE_END_DATE.
• BEN_PER_CM_F — the participant communication record that the provisioning row belongs to (PER_CM_ID), supplying request-until date, life event occurrence date, communication type, and life event reason.
• BEN_CM_TYP_F — supplies the communication type name (CM_TYP).
• BEN_LER_F — supplies the life event reason name (LER_NAME).
• BEN_PER_IN_LER — the participant life event record, joined via PER_IN_LER_ID and BUSINESS_GROUP_ID; its PER_IN_LER_STAT_CD is used to suppress voided or backdated events unless the item has already been sent.
• PER_ADDRESSES — supplies the formatted delivery address lines.
• FND_USER_VIEW — resolves LAST_UPDATED_BY to a user name.
• HR_LOOKUPS — decoded values for BEN_PER_CM_PRVDD_STAT, BEN_DLVRY_MED, BEN_DLVRY_MTHD, BEN_RESND_RSN, YES_NO (requested and inspection-required flags).

HR_API is referenced as a supporting object (typically for API-driven validation or derived logic used in the underlying form/entity), though it is not directly joined in the view text.

Key Columns

• ROW_ID, PER_CM_PRVDD_ID — row identifier and primary key of the provisioning record.
• EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — date-tracked validity of the provisioning row.
• CM_TYP — name of the benefit communication type.
• INSTNC_NUM — instance number of the communication occurrence.
• TO_BE_SENT_DT, SENT_DT — the scheduled and actual send dates; SENT_DT null indicates an outstanding delivery.
• INSPN_RQD_MEANING, RQSTD_MEANING — decoded Yes/No flags for inspection required and requested.
• CM_DLVRY_MTHD_MEANING, CM_DLVRY_MED_MEANING, DLVRY_INSTN_TXT — delivery method, medium, and delivery instruction text.
• RESND_RSN, RESND_CMNT_TXT — resend reason and comment.
• ADDRESS — concatenated address line, city, region, and postal code.
• RQSTBL_UNTIL_DT, LF_EVT_OCRD_DT, LER_NAME — request-until date, life event occurrence date, and life event reason name.
• LAST_UPDATE_DATE, LAST_UPDATED_BY — audit columns, the latter resolved to a user name.

Common Use Cases and Queries

Typical uses include tracking outstanding communications, auditing delivery outcomes, and feeding downstream mail or notification processes.

Outstanding deliveries by communication type:

• SELECT per_cm_prvdd_id, cm_typ, instnc_num, to_be_sent_dt, cm_dlvry_mthd_meaning FROM apps.ben_per_cm_prvdd_d WHERE sent_dt IS NULL AND to_be_sent_dt <= SYSDATE ORDER BY to_be_sent_dt;

Recent resends with reasons:

• SELECT per_cm_prvdd_id, cm_typ, resnd_rsn, resnd_cmnt_txt, sent_dt FROM apps.ben_per_cm_prvdd_d WHERE resnd_rsn IS NOT NULL AND sent_dt >= SYSDATE - 30 ORDER BY sent_dt DESC;

Audit of updater activity:

• SELECT per_cm_prvdd_id, cm_typ, last_updated_by, last_update_date FROM apps.ben_per_cm_prvdd_d WHERE last_update_date >= TRUNC(SYSDATE);

Because the view applies effective dating and excludes voided or backdated life events unless the item is already sent, query results naturally reflect the current, reportable state of each provisioning record.