Results for “ben_cwb_summary”

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

Overview

BEN_CWB_SUMMARY is a transaction and reporting table within the Oracle Advanced Benefits (BEN) module. It stores summarized compensation and benefit calculation results for person groups processed through the Compensation Workbench (CWB) cycle. Each row represents the aggregated outcome of a CWB run scope for a group of persons defined by a person-in-lear relationship, capturing counts, salary values, worksheet values, budget values, and derived compensation totals for both "direct" and "all" population scopes.

The table resides in the BEN schema and is documented as VALID in Oracle EBS 12.1.1 and 12.2.2. Its primary key is BEN_CWB_SUMMARY_PK, defined on the SUMMARY_ID column. Under a heuristic Data Vault classification mined from the foreign-key structure, the object is modeled as a standalone structure — neither a pure hub, link, nor satellite — since the documented FK dependency (GROUP_PER_IN_LER_ID to BEN_CWB_PERSON_INFO) does not imply a multi-parent hub-and-link pattern. This classification should be treated as a modeling suggestion rather than a normative data model.

Key Information Stored

The table contains 44 documented columns. The most significant are summarized below.

• SUMMARY_ID — Surrogate primary key (BEN_CWB_SUMMARY_PK). This is the column referenced by the search term "summary_id"; it is system-generated and is the sole documented unique index, making it a business-key candidate only in the technical sense of uniqueness.
• GROUP_PER_IN_LER_ID — Foreign key to BEN_CWB_PERSON_INFO, identifying the person-in-lear group scope for the summary.
• GROUP_PL_ID and GROUP_OIPL_ID — Identifiers for the plan and option-in-plan group context associated with the summary.
• PERSON_ID — The person for whom the summary row was generated.
• LF_EVT_OCRD_DT — The life event occurred date driving the calculation.
• STATUS — Processing status of the summary record.
• ELIG_COUNT_DIRECT / ELIG_COUNT_ALL — Eligible participant counts for direct and all populations.
• EMP_RECV_COUNT_DIRECT / EMP_RECV_COUNT_ALL — Counts of employees receiving benefits.
• TOT_COMP_VAL_DIRECT / TOT_COMP_VAL_ALL — Total compensation values.
• WS_VAL_DIRECT / WS_VAL_ALL — Worksheet values for direct and all scopes.
• BDGT_VAL_DIRECT and BDGT_ISS_VAL_DIRECT — Budget and budget-issued values.
• REC_VAL_DIRECT, REC_MN_VAL_DIRECT, REC_MX_VAL_DIRECT — Recommendation values (typical, minimum, maximum).
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE — Standard WHO audit columns.

Common Use Cases and Queries

Typical usage centers on compensation cycle reporting, budget analysis, and eligibility reconciliation. A common pattern joins the summary to its source person information:

• Retrieve all summary rows for a specific person-in-lear group: SELECT s.SUMMARY_ID, s.PERSON_ID, s.ELIG_COUNT_ALL, s.TOT_COMP_VAL_ALL FROM BEN.BEN_CWB_SUMMARY s WHERE s.GROUP_PER_IN_LER_ID = :p_group_id AND s.STATUS = 'PROCESSED';
• Compare direct versus all-population budget consumption: SELECT BDGT_VAL_DIRECT, BDGT_ISS_VAL_DIRECT, BDGT_VAL_ALL, BDGT_ISS_VAL_ALL FROM BEN.BEN_CWB_SUMMARY WHERE PERSON_ID = :p_person_id;
• Trend worksheet and recommendation values by life event date using LF_EVT_OCRD_DT as the reporting period key.
• Reconcile eligibility counts (ELIG_COUNT_DIRECT vs ELIG_COUNT_ALL) against downstream enrollment extracts to validate CWB processing.

Reports should generally filter on STATUS and GROUP_PER_IN_LER_ID to scope results efficiently, and use the WHO columns to identify when a summary was last recalculated.

Related Objects

• BEN_CWB_PERSON_INFO — Referenced via GROUP_PER_IN_LER_ID; supplies person-level context for each summary row.
• BEN_CWB_SUMMARY_PK — The primary key index on SUMMARY_ID, used for direct row lookups.
• BEN_CWB_GROUP_PL and BEN_CWB_GROUP_OIPL — Logical parents of GROUP_PL_ID and GROUP_OIPL_ID, defining the plan and option-in-plan grouping.
• PER_ALL_PEOPLE_F — Joined on PERSON_ID to resolve person names and assignments for reporting.
• FF_PER_GBL_DFNTN or BEN plan/option tables — Frequently joined indirectly through plan and option identifiers for descriptive reporting.
• BEN_CWB_* concurrent processes — Populate and refresh rows in this table during CWB cycle execution; summary records are typically recalculated rather than manually maintained.