Results for “ben_rndg”

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

Overview

APPS.BENBV_CWB_PL_DSGN_V is a business-intelligence style database view in the Oracle EBS Advanced Benefits (OLTP) module family. It exposes the Compensation Workbench (CWB) plan design configuration — the set of rules, currency attributes, rounding behaviors, enrollment periods, approval modes, and budget tolerances that govern how a compensation plan operates within a plan year. The view is a denormalized, human-readable projection of the underlying BEN_CWB_PL_DSGN base entity, enriched with descriptive names and decoded lookup values obtained through package function calls rather than conventional joins.

The "BV" naming convention indicates a view intended for reporting and discovery layers (typically consumed by Oracle Business Intelligence or custom extracts) rather than for transactional processing. Its principal value is that it resolves surrogate identifiers — plan, option-in-plan (OIPL), and option IDs — into their effective-dated names at the point in time of the life event, and translates coded values into their meaning. This makes the view directly suitable for compensation analysts and integration developers who need plan design context without reconstructing the effective-dating and lookup logic themselves.

Underlying Base Objects

ETRM documents three referenced base objects for this view:

• BEN_CWB_PL_DSGN (SYNONYM) — the primary source of all plan design attributes. The view aliases this object as CWB and selects nearly every column directly from it, including dates, currency units of measure, rounding codes, and tolerance percentages.
• BEN_BIS_UTILS (PACKAGE) — supplies the effective-dated name-resolution functions. GET_PL_NAME, GET_OIPL_NAME, GET_GROUP_PL_NAME, GET_GROUP_OIPL_NAME, and GET_OPT_NAME each take an identifier, a business group, and a life-event date, returning the name valid as of that date. This ensures the view reports names consistent with the effective date of the plan design record.
• HR_BIS (PACKAGE) — provides BIS_DECODE_LOOKUP, used to translate stored lookup codes into their displayed meanings.

The reliance on package functions rather than standard table joins is significant: the view inherits the effective-dating semantics embedded in those PL/SQL APIs. The rounding decode is passed the lookup type 'BEN_RNDG', which correlates directly with the user's search term.

Key Columns

The view exposes date, monetary, and coded configuration columns. Notable groups include:

• Effective-dating columns: LF_EVT_OCRD_DT (life event occurred date), EFFECTIVE_DATE, UPD_START_DT, and UPD_END_DT anchor the temporal context of each row.
• Plan identity: PL_NAME, OIPL_NAME, GROUP_PL_NAME, GROUP_OIPL_NAME, and OPT_NAME are resolved via BEN_BIS_UTILS.
• Period columns: ENRT_PERD_START_DT and ENRT_PERD_END_DT (enrollment), YR_PERD_START_DT and YR_PERD_END_DT, WTHN_YR_START_DT and WTHN_YR_END_DT, PERF_REVW_STRT_DT, and DATA_FREEZE_DATE.
• Rounding codes: WS_RNDG_CD, DIST_BDGT_RNDG_CD, WS_BDGT_RNDG_CD, RSRV_RNDG_CD, ELIG_SAL_RNDG_CD, and MISC1/MISC2/MISC3_RNDG_CD, each decoded through HR_BIS.BIS_DECODE_LOOKUP against the 'BEN_RNDG' lookup type.
• Currency and tolerance: numerous *_NNMNTRY_UOM columns (worksheet, distributed budget, worksheet budget, reserve, eligible salary, miscellaneous, statutory salary, rec, total comp, other comp) plus BDGT_OVER_BUDGET_TOLERANCE_PCT and WS_OVER_BUDGET_TOLERANCE_PCT.
• Plan parameters: PL_UOM, PL_ORDR_NUM, OIPL_ORDR_NUM, PL_XCHG_RATE, PL_ANNULIZATION_FACTOR, OPT_COUNT, APPROVAL_MODE, LEGISLATION_CODE, SALARY_CHANGE_REASON, and PRSRV_BDGT_CD.

Because the rounding columns resolve to the BEN_RNDG lookup, a query filtering on a particular rounding behavior should reference the decoded (DISP) column rather than the code.

Common Use Cases and Queries

Typical uses include auditing plan configuration across business groups, validating rounding and tolerance settings before a compensation cycle, and feeding BI extracts. Because the view calls PL/SQL functions, queries should constrain on date and business-group columns to limit function execution overhead.

SELECT pl_name, oipl_name, opt_name,
       yr_perd_start_dt, yr_perd_end_dt,
       ws_rndg_cd, effective_date
  FROM apps.benbv_cwb_pl_dsgn_v
 WHERE lf_evt_ocrd_dt = :p_life_event_date;

To list plans that apply a specific worksheet rounding rule, filter on the decoded rounding column (the second BEN_RNDG decode column) or use the lookup meaning obtained from FND_LOOKUP_VALUES where lookup_type = 'BEN_RNDG'. A third pattern validates tolerance percentages against corporate policy by selecting BDGT_OVER_BUDGET_TOLERANCE_PCT and WS_OVER_BUDGET_TOLERANCE_PCT filtered by legislation code. In all cases, remember the view is read-only and the effective-dated names reflect LF_EVT_OCRD_DT, so results for historical life events may differ from current configuration.