Results for “value_rule”

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

Overview

The view APPS.BEN_ACTL_PREM_D is a denormalized, display-oriented view in Oracle Advanced Benefits (BEN). It presents the rows of the base table BEN_ACTL_PREM_F together with the human-readable meanings of the many coded columns that table carries. In Advanced Benefits, an "actual premium" record stores the resolved monetary value of a premium for a participant, plan, or option over an effective date range, together with the calculation rules, rounding rules, limits, assignment, payer, and partial-month handling that produced it.

Because the underlying table stores foreign keys and lookup codes, it is not directly suitable for reporting. BEN_ACTL_PREM_D resolves those codes through HR_LOOKUPS, resolves formula identifiers through FF_FORMULAS_F, resolves the user who last updated the row through FND_USER_VIEW, and resolves the plan and option names through BEN_PL_F, BEN_OIPL_F, and BEN_OPT_F. The result is a flat, report-friendly projection suitable for extracts, conversions, and ad hoc analysis. The view is owned by APPS and is marked VALID. It is documented for both EBS 12.1.1 and 12.2.2, with the same object name and owner in each release.

Underlying Base Objects

The view is defined over the following documented referenced objects: BEN_ACTL_PREM_F, BEN_OIPL_F, BEN_OPT_F, BEN_PL_F, and FF_FORMULAS_F (all synonyms in the APPS schema); the views FND_CURRENCIES_VL, FND_USER_VIEW, and HR_LOOKUPS; and the HR_API package. BEN_ACTL_PREM_F is the driving table. BEN_PL_F, BEN_OIPL_F, and BEN_OPT_F supply plan and option descriptive names. FF_FORMULAS_F supplies the formula names associated with partial-month methods, rounding, upper and lower limits, and value calculation. HR_LOOKUPS is joined repeatedly, once per coded column, and FND_CURRENCIES_VL resolves currency. Nearly all joins are outer joins, so a record is retained even when a related lookup or name is not found.

Key Columns

• ACTL_PREM_ID — Primary identifier of the actual premium record; the key for joining back to BEN_ACTL_PREM_F.
• EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — The date range for which the premium value applies.
• NAME — The user-defined name of the actual premium record.
• VAL — The resolved premium value, expressed in the currency identified by UOM.NAME (resolved from FND_CURRENCIES_VL). This is the column most often sought when users search for premium or flat amounts.
• CR_LKBK_VAL — The carry-over/look-back value retained from a prior period.
• UPR_LMT_VAL, LWR_LMT_VAL — Upper and lower limit values applied to the premium, with their formulas exposed as UPR_LMT.FORMULA_NAME and LWR_LMT.FORMULA_NAME.
• PRTL_MTH.MEANING, PRTL_MTH2.FORMULA_NAME — Partial-month determination method and its formula.
• RNDG.MEANING, RNDG2.FORMULA_NAME — Rounding rule and its formula.
• VAL_CALC.FORMULA_NAME — Formula used to calculate the value.
• RT_TYP.MEANING, CALC_MTHD.MEANING, PRDCT.MEANING, ACTY_REF.MEANING — Rate type, calculation method, product, and activity reference.
• ASNMT.MEANING, ASNMT_LVL.MEANING, ACTL_PREM.MEANING, PREM_PYR.MEANING, PRSPCTV_R_RTSPCTV.MEANING — Assignment, assignment level, premium type, premium payer, and perspective.
• PLN.NAME, OPT.NAME — Plan and option names.
• USER_NAME, CREATION_DATE, LAST_UPDATE_DATE — Audit columns.

Common Use Cases and Queries

The view is typically used for premium reconciliation, benefit cost reporting, and data migration extracts where coded values must be shown in readable form. A common query retrieves the calculated premium value and its context for a date range:

• Filtering by EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to isolate premiums for a plan year.
• Selecting PLN.NAME, OPT.NAME, ACTL_PREM.MEANING, VAL, and UOM.NAME to produce a cost summary.
• Grouping by ACTL_PREM.MEANING or PREM_PYR.MEANING to total premiums by type or payer.
• Comparing VAL against UPR_LMT_VAL and LWR_LMT_VAL to verify that limits were enforced.

Typical SQL: SELECT apr.actl_prem_id, apr.name, apr.val, apr.uom_name, apr.effective_start_date, apr.effective_end_date FROM apps.ben_actl_prem_d apr WHERE TRUNC(SYSDATE) BETWEEN apr.effective_start_date AND apr.effective_end_date;. Because the view performs numerous outer joins across lookup and formula tables, queries should be constrained on the effective dates and, where possible, on ACTL_PREM_ID or plan identifiers to avoid full scans of the underlying Advanced Benefits tables.