Results for “strt_dt”

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

Overview

BEN_PER_CM_PCU_LOV_V is an APPS-owned database view within the Oracle Advanced Benefits (BEN) module of Oracle E-Business Suite, valid in releases 12.1.1 and 12.2.2. It functions as a List of Values (LOV) source for the communication type usage (PCU) setup screens, presenting pre-joined, user-friendly descriptive names in place of raw foreign key identifiers. Rather than exposing internal numeric IDs such as LER_ID, PGM_ID, or CM_TYP_ID directly to the forms layer, the view resolves these into human-readable names drawn from the life event reason, program, plan, plan type, action type, and formula entities.

The view is most commonly encountered when a user searches on the enrollment period start date attribute, internally named STRT_DT. Reports, concurrent programs, and form-based LOVs query this view to display the communication type usage combinations available for a given effective date. Its design reflects the standard EBS pattern of combining a transactional usage table with descriptive lookups and a session-driven effective date filter, ensuring the caller only sees records valid as of the current session context.

Underlying Base Objects

The view text is defined over nine referenced objects, all owned by APPS and accessed through synonyms or views:

• BEN_CM_TYP_USG_F — the driving table, holding communication type usage rows and their foreign keys to life event reason, program, plan, plan type, action type, enrollment period, and formula.
• BEN_LER_V — a view supplying the life event reason name (LER_NAME).
• BEN_PGM_F — supplies the program name (PGM_NAME).
• BEN_PL_F — supplies the plan name (PL_NAME).
• BEN_PL_TYP_F — supplies the plan type name (PT_NAME).
• BEN_ACTN_TYP — supplies the action type name (ACTN_NAME).
• FF_FORMULAS_F — supplies the formula (rule) name (RL_NAME).
• BEN_ENRT_PERD — supplies the enrollment period STRT_DT and END_DT.
• FND_SESSIONS — provides the session effective date used in the WHERE clause.

All joins from BEN_CM_TYP_USG_F to the descriptive tables are outer joins, so a usage row still appears even when an optional reference is unpopulated. The filter predicates compare FND.EFFECTIVE_DATE against the NVL of each object's effective start and end dates, a datetrack-style pattern that returns only rows current as of the session date, with FND.SESSION_ID tied to USERENV('SESSIONID').

Key Columns

• STRT_DT and END_DT — the start and end dates of the associated enrollment period (BEN_ENRT_PERD). STRT_DT is the attribute users most frequently search on.
• LER_NAME, PGM_NAME, PL_NAME, PT_NAME, ACTN_NAME, RL_NAME — descriptive names for the life event reason, program, plan, plan type, action type, and formula/rule references.
• CM_TYP_USG_ID — primary identifier of the communication type usage row; used as the LOV return value.
• CM_TYP_ID — foreign key to the communication type definition.
• BUSINESS_GROUP_ID — the business group that owns the usage row, supporting multi-organization security filtering.

Common Use Cases and Queries

Typical scenarios include displaying valid communication type usages in a benefits configuration form, filtering available communications for an enrollment window, and driving ad hoc reporting on enrollment period coverage. A representative query searching on the start date is shown below.

• SELECT cm_typ_usg_id, ler_name, pgm_name, pl_name, pt_name, actn_name, rl_name, strt_dt, end_dt FROM ben_per_cm_pcu_lov_v WHERE strt_dt >= :p_from_date ORDER BY strt_dt;
• SELECT ler_name, pl_name, strt_dt FROM ben_per_cm_pcu_lov_v WHERE business_group_id = :p_bg_id AND sysdate BETWEEN strt_dt AND end_dt;
• SELECT DISTINCT rl_name FROM ben_per_cm_pcu_lov_v WHERE cm_typ_id = :p_cm_typ_id;

Because the view already enforces session-based effective dating, callers should not add redundant datetrack predicates on the base tables. Oracle Proprietary, Confidential Information.