Search Results resnd_rsn

Overview

BEN_PER_CM_PRVDD_D is an APPS-owned reporting view within the Oracle EBS Advanced Benefits (BEN) module. Its name decomposes as a "per communication provided" detail view: it consolidates records from the BEN_PER_CM_PRVDD_F base table (which stores the individual communications provided to a person under a life event) and joins them to the parent communication record in BEN_PER_CM_F. In the context of Oracle EBS 12.1.1 and 12.2.2, the view serves as a denormalized, presentation-friendly layer that resolves raw coded columns into human-readable meanings via HR_LOOKUPS. This makes it suitable for concurrent-program extracts, XML/BI Publisher reports, Oracle Discoverer workbooks, and ad-hoc SQL used by benefits administrators to audit the delivery of communications such as enrollment confirmations, life-event notifications, and required-communication reminders.

Underlying Base Objects

The view is defined over a mixture of base tables and lookup views, joined predominantly with outer joins so that communications without a fully populated parent record still appear. The documented referenced objects are BEN_CM_TYP_F, BEN_LER_F, BEN_PER_CM_F, BEN_PER_CM_PRVDD_F, BEN_PER_IN_LER, FND_USER_VIEW, HR_API, HR_LOOKUPS, and PER_ADDRESSES.

• BEN_PER_CM_PRVDD_F — the driving table, aliased PCD, holding each provided communication, its instance number, sent date, delivery method code, and status.
• BEN_PER_CM_F — the parent person communication (PER_CM), supplying the requested date and life-event occurred date; joined on PER_CM_ID.
• BEN_CM_TYP_F — the communication type lookup, providing CM_TYP name.
• BEN_LER_F and BEN_PER_IN_LER — the life-event reason and its status, used to filter out voided or backdated events.
• PER_ADDRESSES — supplies the delivery address concatenation.
• HR_LOOKUPS — referenced multiple times (aliases CM_DLVRY_MTHD, CM_DLVRY_MED, RQSTD, RESND_RSN, INSPN_RQD, PER_CM_PRVDD_STAT) to decode codes such as BEN_DLVRY_MTHD, BEN_DLVRY_MED, and BEN_RESND_RSN.
• FND_USER_VIEW — resolves LAST_UPDATED_BY to a user name.
• HR_API — the HR API package, referenced for the business-group security/context filter applied through the PER_IN_LER join.

Key Columns

• CM_DLVRY_MTHD_MEANING — the decoded meaning of the BEN_DLVRY_MTHD lookup, identifying whether the communication was delivered by email, print, or other method. This is the column most commonly searched for as "cm_dlvry_mthd_meaning."
• CM_DLVRY_MED_MEANING — the decoded delivery medium (BEN_DLVRY_MED).
• CM_TYP — the communication type name.
• TO_BE_SENT_DT and SENT_DT — scheduled versus actual sent dates, enabling delivery-timeliness analysis.
• RQSTD_MEANING and INSPN_RQD_MEANING — YES_NO lookups indicating whether the communication was requested and whether inspection is required.
• RESND_RSN, RESND_CMNT_TXT — resend reason and comments.
• DLVRY_INSTN_TXT, ADDRESS — delivery instruction and concatenated address lines.
• INSTNC_NUM, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — instance and date-effective tracking.
• LER_NAME — the life-event reason associated with the communication.

Common Use Cases and Queries

Typical uses include auditing communications by delivery method, reconciling scheduled against sent dates, and reporting on life-event notification activity. Because the lookup joins are outer joins, codes lacking a matching lookup row are not dropped.

SELECT cm_typ,
       cm_dlvry_mthd_meaning,
       COUNT(*) AS total
FROM   apps.ben_per_cm_prvdd_d
GROUP  BY cm_typ, cm_dlvry_mthd_meaning;

SELECT per_cm_prvdd_id, cm_typ, to_be_sent_dt, sent_dt
FROM   apps.ben_per_cm_prvdd_d
WHERE  cm_dlvry_mthd_meaning = 'Email'
AND    sent_dt BETWEEN :p_from AND :p_to;

When querying, restrict by business group and effective dates where appropriate, and note that lookup codes such as BEN_DLVRY_MTHD drive the meaning columns returned.