Results for “coverage_name”

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

Overview

The view BEN_CVG_AMT_CALC_MTHD_D belongs to the Oracle Advanced Benefits (BEN) product family and is owned by the APPS schema. It is a denormalized, descriptive ("D") view built over the coverage amount calculation method entity, combining the base transactional table with translated lookup meanings and formula references so that consumers can read human-readable descriptions rather than raw codes. In ETRM it carries the annotation "Retrofitted," indicating it was reconstructed to reflect the current 12.1.1 / 12.2.2 data model after a schema upgrade.

The view is significant because it exposes the ENTR_VAL_AT_ENRT lookup meaning, which answers the precise question "enter value at enrollment" — that is, whether the participant must supply a coverage amount when electing the plan, and under what rules. This makes the view a natural reporting layer for benefits configuration analysis, self-service enrollment troubleshooting, and integrations that need to interpret calculation method rules without decoding lookup codes.

Underlying Base Objects

The view is defined primarily over BEN_CVG_AMT_CALC_MTHD_F, the base table holding coverage amount calculation method definitions, and joins outward to several supporting objects:

• BEN_CVG_AMT_CALC_MTHD_F — driving table; supplies row identity, effective dates, name, increment, minimum/maximum values, unit of measure, default value, and all lookup codes.
• HR_LOOKUPS — joined repeatedly (outer-joined on LOOKUP_CODE and a fixed LOOKUP_TYPE) to resolve codes such as BEN_RNDG, BEN_BNDRY_PERD, BEN_BNFT_TYP, BEN_CVG_MLT, BEN_RT_TYP, and the ENTR_VAL_AT_ENRT type into their MEANING values.
• FF_FORMULAS_F — resolves formula identifiers for rounding, value calculation, and lower/upper limit rules to FORMULA_NAME.
• BEN_COMP_LVL_FCTR, BEN_PL_F, BEN_OIPL_F, BEN_OPT_F — resolved to compensation level factor names, plan names, and the concatenated "plan-option" label.
• FND_USER — outer-joined to return the USER_NAME of the last updater.
• HR_API — referenced as a package dependency for the benefits API layer.

All joins to the descriptive objects are outer joins, so a calculation method row is always returned even when its lookup or formula is not yet defined.

Key Columns

• CVG_AMT_CALC_MTHD_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — primary identity and date-tracked validity of the calculation method.
• ENTR_VAL_AT_ENRT.MEANING — the resolved "enter value at enrollment" indicator meaning, the column most relevant to the originating search term.
• INCRMT_VAL, MX_VAL, MN_VAL, DFLT_VAL, LWR_LMT_VAL, UPR_LMT_VAL, VAL — numeric boundaries and defaults governing the amount a participant may enter.
• NO_MX_VAL_DFND.MEANING, NO_MN_VAL_DFND.MEANING, VAL_OVRID_ALWD.MEANING, DFLT.MEANING — flags indicating whether maximum/minimum values are undefined, whether value override is allowed, and default behavior.
• RNDG.MEANING, RNDG_RL.FORMULA_NAME, VAL_CALC.FORMULA_NAME, LWR_LMT.FORMULA_NAME, UPR_LMT.FORMULA_NAME — rounding and calculation rules expressed as readable formula names.
• UOM, NNMNTRY.MEANING, BNDRY_PERD.MEANING, BNFT_TYP.MEANING, CVG_MLT.MEANING, RT_TYP.MEANING — unit of measure and classification meanings.
• COMP_LVL_FCTR.NAME, PL.NAME, and the combined OIPL_PL.NAME || '-' || OIPL_OPT.NAME — organizational context (compensation level factor, plan, plan-option).
• LAST_UPDATE_DATE, FUSER.USER_NAME — audit trail.

Common Use Cases and Queries

Typical uses include auditing how enrollment amounts are governed, validating plan configuration before go-live, and feeding downstream reporting. A query to locate methods requiring a value at enrollment follows:

SELECT calc.NAME,
       calc.ENTR_VAL_AT_ENRT.MEANING  enter_value_flag,
       calc.DFLT_VAL,
       calc.MN_VAL,
       calc.MX_VAL,
       calc.UOM
FROM   APPS.BEN_CVG_AMT_CALC_MTHD_D calc
WHERE  calc.ENTR_VAL_AT_ENRT.MEANING IS NOT NULL
AND    SYSDATE BETWEEN calc.EFFECTIVE_START_DATE AND calc.EFFECTIVE_END_DATE;

A second scenario maps each calculation method to its plan and formula rules for configuration review:

SELECT calc.NAME,
       calc.PL.NAME                  plan_name,
       calc.VAL_CALC.FORMULA_NAME    value_formula,
       calc.RNDG.MEANING             rounding_rule,
       calc.BNDRY_PERD.MEANING       boundary_period
FROM   APPS.BEN_CVG_AMT_CALC_MTHD_D calc
WHERE  calc.LAST_UPDATE_DATE > SYSDATE - 30
ORDER  BY calc.LAST_UPDATE_DATE DESC;

Because the view resolves all descriptive lookups, it is preferable to querying BEN_CVG_AMT_CALC_MTHD_F directly for any read-only reporting or integration requirement involving enrollment value entry rules.