Search Results ben_actl_prem_vrbl_rt_d




Overview
Explore in AI Chat

Overview

BEN_ACTL_PREM_VRBL_RT_D is a reporting and inquiry view owned by the APPS schema in Oracle E-Business Suite, belonging to the Advanced Benefits (BEN) product family. It is classified in the ETRM repository as a VIEW with VALID status and is documented as a "retrofitted" object, meaning it was originally introduced in Oracle EBS 11i and subsequently ported forward into the 12.1.1 and 12.2.2 code lines without a redesign of its underlying definition. The view exposes a denormalized, user-friendly projection of the association between an actual premium and a variable rate profile, resolving internal numeric identifiers into the descriptive names that administrators and report authors expect. Because it joins the transactional premium-rate intersection to the translated/name-bearing parent entities and to FND_USER, it is typically consumed through the BEN_ADV_BENEFITS responsibility, through Oracle Discoverer/BI Publisher reports, and through customer-written concurrent programs or interfaces that need to display "actual premium – variable rate profile" combinations without exposing raw ID values to end users.

Underlying Base Objects

The view is defined over four documented base objects, each accessed through an APPS-private synonym: BEN_ACTL_PREM_VRBL_RT_F, BEN_ACTL_PREM_F, BEN_VRBL_RT_PRFL_F, and FND_USER. The driving table in the view text is BEN_ACTL_PREM_VRBL_RT_F (aliased APV), the core date-tracked intersection of an actual premium with a variable rate profile; this table supplies the primary key ACTL_PREM_VRBL_RT_ID together with the effective dating columns and the audit columns. BEN_ACTL_PREM_F (aliased ACTL_PREM) is joined on ACTL_PREM_ID to supply the actual premium NAME, while BEN_VRBL_RT_PRFL_F (aliased VRBL_RT_PRFL) is joined on VRBL_RT_PRFL_ID to supply the variable rate profile NAME. FND_USER is joined on LAST_UPDATED_BY to resolve the updating user. All three of the non-driving joins are written with the Oracle outer-join operator (+), so the view preserves rows of BEN_ACTL_PREM_VRBL_RT_F even when a parent premium, profile, or user record is missing or inaccessible, with the dependent name returning NULL rather than suppressing the row.

Key Columns

The view exposes the following documented columns. ROW_ID returns APV.ROWID and serves as the unique row identifier for tools and update-capable blocks. ACTL_PREM_VRBL_RT_ID is the surrogate primary key of the underlying intersection record. EFFECTIVE_START_DATE and EFFECTIVE_END_DATE provide the date-tracking envelope required by the BEN date-tracked datetrack model, allowing point-in-time queries. ACTL_PREM_NAME is the resolved name of the actual premium from BEN_ACTL_PREM_F, and VRBL_RT_PRFL_NAME is the resolved name of the variable rate profile from BEN_VRBL_RT_PRFL_F. LAST_UPDATE_DATE and LAST_UPDATED_BY are the audit columns, with LAST_UPDATED_BY presented as the FND_USER identifier rather than the resolved user name. Note that the ETRM column listing shows these eight names, which should be matched positionally against the SELECT list of the stored view text.

Common Use Cases and Queries

The view is most often used for validation and reconciliation of premium-to-rate-profile configuration, for auditing who last touched a rate association, and as the lookup source for LOV-style queries in customizations. A representative query listing current associations with their descriptive names is:

  • SELECT row_id, actl_prem_vrbl_rt_id, actl_prem_name, vrbl_rt_prfl_name, effective_start_date, effective_end_date, last_update_date, last_updated_by FROM apps.ben_actl_prem_vrbl_rt_d ORDER BY actl_prem_name, vrbl_rt_prfl_name;

  • SELECT d.actl_prem_name, d.vrbl_rt_prfl_name, d.last_update_date, u.user_name FROM apps.ben_actl_prem_vrbl_rt_d d, applsys.fnd_user u WHERE d.last_updated_by = u.user_id AND d.last_update_date >= SYSDATE - 30; — used to audit recent rate-profile changes.

  • SELECT * FROM apps.ben_actl_prem_vrbl_rt_d WHERE effective_start_date <= :p_effective_date AND effective_end_date >= :p_effective_date (+1/86400) AND actl_prem_name = :p_premium; — a point-in-time lookup driven by a premium name.

Because the view resolves names through outer joins to the name-bearing tables, it is read-only by nature and should be used for inquiry and reporting only; DML must be directed to BEN_ACTL_PREM_VRBL_RT_F through the standard BEN business APIs rather than through this view.