Results for “ben_per_cm_usg_f”

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

Overview

BEN_PER_CM_USG_F is a transactional table owned by the BEN schema within the Oracle Advanced Benefits (BEN) module. Its documented description identifies it as the repository of "communication usages for a person," meaning it records the association between an individual and the communication materials assigned to them during benefits processing, such as enrollment confirmation statements, life event notification letters, or plan-specific correspondence generated by the Oracle Advanced Benefits communication engine. Each row captures a single usage instance of a communication deliverable for a given person within a business group, subject to the effective-dating model that governs most BEN entities.

The ETRM metadata classifies this object heuristically as a standalone structure under the Data Vault classification heuristic mined from its foreign-key footprint. From a dimensional modeling perspective, this suggests the table behaves less like a pure hub or link and more like a self-contained satellite-style record that carries descriptive attributes of the communication usage event without serving as a central integration point for other entities. The 47 documented columns include a substantial descriptive attribute block (PCU_ATTRIBUTE1 through PCU_ATTRIBUTE30), reinforcing that interpretation.

Key Information Stored

The primary key of the table is BEN_PER_CM_USG_F_PK, composed of PER_CM_USG_ID, EFFECTIVE_START_DATE, and EFFECTIVE_END_DATE. PER_CM_USG_ID is the surrogate identifier for the usage record, while the two date columns form the effective-dating component that supports Oracle's standard date-tracked versioning: multiple versions of the same usage record can coexist, with only the currently effective range active at any point in time.

The most operationally significant columns include:

• PER_CM_USG_ID — surrogate primary key for the usage record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — effective-dating range bounding each version of the record.
• BUSINESS_GROUP_ID — enterprise business group scoping the record, central to multi-tenant style filtering in HRMS-based applications.
• CM_TYP_USG_ID — foreign reference to the communication type usage definition, identifying which type of communication was issued.
• PER_CM_ID — reference to the person-level communication record that this usage belongs to.
• PCU_ATTRIBUTE_CATEGORY and PCU_ATTRIBUTE1–PCU_ATTRIBUTE30 — a descriptive flexfield block used by implementers to capture client-specific contextual data.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the OAF and BC4J frameworks to prevent concurrent update collisions.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE — standard WHO columns for audit lineage.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context, identifying which request or batch process created or last modified the row.

No additional unique indexes beyond the composite primary key are documented, so the effective-dated surrogate triplet is the only business-key candidate identified.

Common Use Cases and Queries

Typical queries against BEN_PER_CM_USG_F are historic and reporting-oriented: auditing which communications were generated for an employee during a life event, reconciling communication output against enrollment activity, or troubleshooting why a participant did not receive a given letter. A representative pattern selects current rows only by constraining the effective window:

• SELECT per_cm_usg_id, per_cm_id, cm_typ_usg_id, effective_start_date FROM ben.ben_per_cm_usg_f WHERE business_group_id = :p_bg_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
• Join to person-level tables via PER_CM_ID to attribute usages to named employees.
• Filter by CM_TYP_USG_ID to isolate a specific communication type, useful for compliance reporting on required notices.
• Trace origin using REQUEST_ID and PROGRAM_ID when investigating batch-generated correspondence.

Related Objects

Because the FK footprint is heuristic and the table is classified standalone, integration points are primarily logical rather than enforced. The principal related objects are:

• BEN_PER_CM_F — the parent person-communication table, joined via PER_CM_ID.
• BEN_CM_TYP_USG_F — the communication type usage definition, joined via CM_TYP_USG_ID.
• PER_ALL_PEOPLE_F — via the person referenced through BEN_PER_CM_F, for employee identity resolution.
• HR_ALL_ORGANIZATION_UNITS — via BUSINESS_GROUP_ID for business group naming.
• FND_CONCURRENT_REQUESTS — via REQUEST_ID for concurrent program traceability.
• BEN_PER_CM_USG_F_PK — the enforcing primary key index.

Because the table is effective-dated, joins should always include date-range predicates to avoid duplicate historical versions in results.