Results for “average_allocated”

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

Overview

BEN_CWB_REPT_COMP_COMPARISON_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the BEN (Advanced Benefits) module. It supports the Compensation Report Comparison feature within Compensation Workbench (CWB), presenting a manager-and-plan-level reconciliation of allocated compensation against eligibility and budget. The view is documented as VALID in ETRM for both 12.1.1 and 12.2.2.

The view aggregates data across a manager's reporting hierarchy, comparing the number of eligible employees against those who received a valid allocation, and comparing total allocated amounts against the derived budget. This makes it the primary SQL source for the Compensation Comparison report, which compensation administrators use to validate worksheet completion and identify funding variances before final approval cycles.

Because the view exposes MANAGER_FULL_NAME — the column implied by the search term "manager_full_name" — it is commonly queried to drive manager-facing reporting, personalization of CWB dashboards, and downstream extracts that must identify approvers or budget owners by their full legal name rather than by PERSON_ID.

Underlying Base Objects

The view is defined over a nested inline query joining a broad set of BEN and PER synonyms, plus the HR_API package and the HR_LOOKUPS and PER_BUSINESS_GROUPS_PERF views. The documented referenced objects are:

• BEN_CWB_HRCHY — the CWB hierarchy table that relates manager and employee eligible-choice populations through LVL_NUM.
• BEN_PIL_ELCTBL_CHC_POPL and BEN_ELIG_PER_ELCTBL_CHC — eligibility and electable-choice populations, filtered by ELIG_FLAG to count only eligible participants.
• BEN_ENRT_PERD and BEN_ENRT_RT — enrollment period header and rate/unit data supplying ASND_LF_EVT_DT and allocation amounts.
• BEN_PL_F, BEN_OIPL_F, BEN_OPT_F — plan, option-in-plan, and option definitions for PLAN_NAME and currency derivation.
• BEN_PER_IN_LER and BEN_ACTY_BASE_RT_F — person-in-LER and activity base rate references.
• PER_ALL_PEOPLE_F — the person source for MANAGER_NAME and MANAGER_FULL_NAME.
• HR_LOOKUPS and PER_BUSINESS_GROUPS_PERF — lookup meanings (RATE_UNITS) and business group/legislation context (COUNTRY).
• HR_API — the HR API package referenced within the view logic.

Key Columns

The projection exposes the following principal columns:

• MANAGER_ID and MANAGER_NAME — the manager's PERSON_ID and concatenated first, last, and suffix.
• MANAGER_FULL_NAME — the manager's full name from PER_ALL_PEOPLE_F.FULL_NAME, the alignment column for manager-directed searches.
• PLAN_NAME, COUNTRY — concatenated plan and option name, and the business group legislation code.
• ASND_LF_EVT_DT — the assigned life event date used to scope the reporting period.
• ELIGIBLE_EMPLOYEES and ELIGIBLE_EMPS_W_VAL — headcount of eligible employees versus those with an allocated value.
• PCT_W_VAL — the percentage of eligible employees with a value, rounded to one decimal place.
• TOTAL_ALLOCATED, UNITS, AVERAGE_ALLOCATED — total allocated amount, the rate unit or plan currency, and the mean allocation per valued employee.
• BUDGET and UNUSED_BUDGET — derived from PRESERVE_CD, using BUDGET_AMOUNT when 'A', otherwise BUDGET_PERCENT times ELIGIBLE_SALARIES divided by 100, less total allocated.
• ENRT_PERD_ID — the enrollment period identifier for period-specific filtering.

All divisions use DECODE guards against zero denominators, so the view returns safe numeric output even where eligibility or allocation counts are null.

Common Use Cases and Queries

Typical scenarios include verifying allocation coverage per manager, reconciling budget consumption across plans, and extracting manager full names for notification or approval routing. Because MANAGER_FULL_NAME is a plain column, it can be filtered or ordered directly.

Example: retrieve budget status for a specific manager by full name.

• SELECT manager_full_name, plan_name, country, eligible_employees, eligible_emps_w_val, pct_w_val, total_allocated, budget, unused_budget FROM apps.ben_cwb_rept_comp_comparison_v WHERE UPPER(manager_full_name) LIKE UPPER('%SMITH%') AND enrt_perd_id = :p_enrt_perd_id ORDER BY plan_name;

Example: identify plans where allocated amounts exceed budget.

• SELECT manager_full_name, plan_name, units, total_allocated, budget, unused_budget FROM apps.ben_cwb_rept_comp_comparison_v WHERE unused_budget < 0 AND enrt_perd_id = :p_enrt_perd_id ORDER BY unused_budget;

Example: aggregate allocation coverage by plan and country.

• SELECT plan_name, country, SUM(eligible_employees) eligible, SUM(eligible_emps_w_val) valued, ROUND(AVG(pct_w_val),1) avg_pct FROM apps.ben_cwb_rept_comp_comparison_v GROUP BY plan_name, country;

Where the documentation excerpt is truncated, column semantics beyond those listed should be confirmed against the APPS view definition in the target instance.