Results for “pl_typ_name”

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

Overview

BEN_PER_CM_USG_D is a denormalized reporting view owned by the APPS schema in Oracle E-Business Suite Advanced Benefits (BEN). It flattens the relationship between a person's enrollment period and the communication usage records that drive benefits communication delivery, exposing descriptive names for the various foreign key attributes held on the underlying usage table. In releases 12.1.1 and 12.2.2, this view is used primarily for reporting, diagnostics, and data extraction rather than transactional processing. Its column set and joins are documented in the ETRM repository as VALID, meaning the object compiles cleanly and is safe for query-based reporting. The view does not carry any business logic for benefit eligibility or rate calculation; instead, it resolves surrogate identifiers into meaningful English descriptions, which makes it suitable for ad-hoc queries and extracts consumed by operational reporting, reconciliation, and integration scripts.

Underlying Base Objects

The view text joins eleven documented objects, all accessed through synonyms in the APPS schema. The driving table is BEN_PER_CM_USG_F, which stores person-level communication usage records and is aliased PCU. It joins to BEN_CM_TYP_USG_F (CTU), the communication type usage definition, which in turn supplies the foreign keys to BEN_LER_F (LER), BEN_PGM_F (PGM), BEN_PL_F (PL), BEN_PL_TYP_F (PL_TYP), BEN_ACTN_TYP (ACTN), and BEN_ENRT_PERD (EP). BEN_PER_CM_F (PCM) links the usage record back to a specific person communication entry, and BEN_PER_IN_LER (PIL) supplies the person-in-life-event-record context. FND_USER (FUSER) is outer-joined on LAST_UPDATED_BY to resolve the updating user's login name. The joins are predominantly outer joins so that usage rows are retained even when a referenced communication type component is absent. Effective date ranges are validated across each referenced object, and BEN_PER_IN_LER rows with status codes of VOIDD or BCKDT are deliberately excluded, or permitted only when the status is null.

Key Columns

• ROW_ID — the ROWID of the underlying BEN_PER_CM_USG_F row, used as a stable handle for the record.
• PER_CM_USG_ID — primary identifier of the person-level communication usage record.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — date-effective boundaries of the usage row.
• LER_NAME — life event name resolved from BEN_LER_F.
• PGM_NAME — program name resolved from BEN_PGM_F.
• PL_NAME — plan name resolved from BEN_PL_F.
• PL_TYP_NAME — the plan type name resolved from BEN_PL_TYP_F; this is the column users target when searching for "pl_typ_name" and explains why the view surfaces a plan-type description rather than only an ID.
• ACTN_NAME — action type name from BEN_ACTN_TYP.
• STRT_DT / END_DT — the enrollment period start and end dates from BEN_ENRT_PERD.
• LAST_UPDATE_DATE / LAST_UPDATED_BY — audit columns, with the user name resolved through FND_USER.

Common Use Cases and Queries

Typical usage includes auditing which communications were generated for a person against a specific plan type, troubleshooting missing or voided life-event enrollment records, and feeding downstream reporting extracts. A representative query filtering on the plan type name is:

SELECT per_cm_usg_id, ler_name, pgm_name, pl_name, pl_typ_name, actn_name, strt_dt, end_dt, last_updated_by FROM apps.ben_per_cm_usg_d WHERE pl_typ_name = 'Medical' AND effective_start_date >= SYSDATE - 90 ORDER BY effective_start_date DESC;

A second pattern reports usage by program and action across a date band, joining the view to employee or assignment data through the person communication identifier. Because the view relies on effective-dated joins, queries should always constrain EFFECTIVE_START_DATE and EFFECTIVE_END_DATE when a point-in-time result is required. The exclusion of VOIDD and BCKDT person-in-life-event rows means the view reflects only active enrollment context, which is usually the desired behavior for operational reporting but should be confirmed before using the view for historical audit purposes.