Results for “acty_ref”

4 results




Overview
Explore in AI Chat

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

Overview

APPS.BEN_ACTL_PREM_D is a reporting view in the Oracle E-Business Suite Advanced Benefits (BEN) module. It presents the effective-dated rows of the Actual Premiums entity, enriching the underlying transactional table BEN_ACTL_PREM_F with decoded, human-readable values drawn from HR_LOOKUPS, FF_FORMULAS_F, FND_USER_VIEW, and the benefit plan hierarchy. The "_D" suffix follows the standard EBS convention denoting a "descriptive" or denormalized view intended for inquiry, reporting, and integration rather than for transactional DML. In Oracle EBS 12.1.1 and 12.2.2 the view is owned by APPS and is queried through the standard APPS schema synonym exposed to reporting tools such as Oracle Reports, BI Publisher, and third-party extract routines.

The user search term "prspctv_r_rtspctv" corresponds directly to a lookup-based column in this view. The column prspctv_r_rtsptv_cd on the base table is decoded against the HR_LOOKUPS lookup type BEN_PRSPCTV_R_RTSPCTV, which denotes whether an actual premium is prospective, retrospective, or both. This lookup controls the timing behavior of premium calculation and is frequently surfaced in benefit reporting.

Underlying Base Objects

The view is defined primarily over BEN_ACTL_PREM_F, which supplies the actual premium records including effective dates, values, limits, and code columns. It joins outward with outer joins to the following documented objects:

  • BEN_ACTL_PREM_F (synonym) — the driving table; actual premium definitions effective-dated by row.
  • BEN_PL_F, BEN_OIPL_F, BEN_OPT_F (synonyms) — plan, option-in-plan, and option hierarchy that the premium is associated with.
  • FF_FORMULAS_F (synonym) — supplies formula names for partial-month calculation, rounding, upper/lower limit, and value calculation.
  • FND_CURRENCIES_VL (view) — currency unit of measure for the premium amount.
  • FND_USER_VIEW (view) — the user name of the last person to update the record.
  • HR_LOOKUPS (view) — decoded meaning for numerous code columns including the prospectivity/retrospectivity flag.
  • HR_API (package) — referenced by the view's dependency chain, typically for lookup security or flexfield access.

Key Columns

Principal columns exposed by the view include:

  • actl_prem_id — primary key of the actual premium record.
  • effective_start_date / effective_end_date — date-effective range.
  • val — the actual premium amount or value.
  • prspctv_r_rtspctv.meaning — decoded prospectivity/retrospectivity flag (the user's search term).
  • upr_lmt_val, lwr_lmt_val — upper and lower limit values, with accompanying formula names.
  • calc_mthd.meaning, prdct.meaning, rndg.meaning, asnmt.meaning, asnmt_lvl.meaning, actl_prem.meaning, prem_pyr.meaning, rt_typ.meaning, acty_ref.meaning — decoded lookup meanings.
  • uom.NAME — currency name from FND_CURRENCIES_VL.
  • fuser.user_name — last updated by user name.
  • pln.NAME, opt.NAME — plan and option names.

Common Use Cases and Queries

Typical usage includes validating premium configurations per plan, reconciling calculated premiums against formula output, and auditing prospectivity/retrospectivity behavior. A representative query is:

SELECT actl_prem_id, name, val, prspctv_r_rtsptv_cd, effective_start_date FROM apps.ben_actl_prem_d WHERE pln.name = :p_plan AND TRUNC(SYSDATE) BETWEEN effective_start_date AND NVL(effective_end_date, TRUNC(SYSDATE));

Because the view uses outer joins throughout, records with missing lookup values will still appear with NULL meanings, so reports should guard against NULLs on decoded columns.