Search Results prev_sal




Overview
Explore in AI Chat

Overview

BEN_CWB_RPT_DETAIL is a table in the BEN (Advanced Benefits) schema of Oracle E-Business Suite, present and validated in both 12.1.1 and 12.2.2. It stores the outcome detail of the Compensation Workbench post-process run: one or more rows per person processed, capturing the compensation decisions that were evaluated, along with any errors or warnings raised during processing. It is therefore best understood as the diagnostic and audit layer that sits between the Compensation Workbench staging structures and the downstream transactional objects (pay proposals, element entries, and benefit actions) that are actually written when a post-process run succeeds.

Because the table carries a surrogate primary key together with business identifiers and a large number of descriptive and state columns, a Data Vault modelling heuristic suggests classifying it as a satellite-like structure attached to the Compensation Workbench person and rate hubs, rather than as a pure hub or link. The mined FK structure is classified as standalone, meaning the table does not itself act as a parent for other constraints and functions as a leaf-level detail store.

Key Information Stored

The documented physical schema contains 165 columns. The most significant are:

  • CWB_RPT_DETAIL_ID — the surrogate primary key, enforced by the unique index CWB_RPT_DETAIL_PK. This is the only unique index documented, so it is the sole business-key candidate identified in the metadata.
  • PERSON_ID and FULL_NAME / EMPLOYEE_NUMBER — identify the person whose compensation was processed.
  • PL_ID, OIPL_ID, and GROUP_PER_IN_LER_ID — associate the row with the plan, the plan-in-organization, and the grouped person-in-lear relationship used by Compensation Workbench.
  • AMOUNT, UNITS, ADJUSTED_AMOUNT, BASE_SALARY, ELIG_SALARY, and NEW_SAL — the monetary measures evaluated or produced by the run.
  • PERCENT_OF_ELIG_SAL, FTE_FACTOR, and CONVERSION_FACTOR — the proration and currency-conversion factors applied when deriving the posted values.
  • STATUS_CD — the processing state of the row, typically distinguishing successfully applied records from those that failed or remained unprocessed.
  • ERROR_OR_WARNING_TEXT — the message text surfaced to the user when validation or posting encounters a problem; this is the primary diagnostic column in the table.
  • BENEFIT_ACTION_ID, PERSON_RATE_ID, PAY_PROPOSAL_ID, PAY_BASIS_ID, and ELEMENT_ENTRY_ID — the foreign keys linking each detail row to the resulting benefit action, person rate, salary proposal, pay basis, and element entry created by the run.
  • AMOUNT_POSTED and ELMNT_PROCESSING_TYPE — indicate whether the amount was written downstream and how it was processed.
  • PRIOR_/POSTED_/PROPOSED_ column families for job, position, grade, group, and FLEX1 through FLEX30 — capture before-and-after assignment attribute values when an assignment change results from the run.

Common Use Cases and Queries

The table is most often queried to reconcile a Compensation Workbench post-process run: confirming which persons were processed, which rows produced errors, and which amounts ultimately reached pay proposals and element entries.

  • Error triage. Retrieve all failed rows for a run so that administrators can correct data and reprocess: 
    SELECT person_id, full_name, employee_number, status_cd, error_or_warning_text
FROM   ben.ben_cwb_rpt_detail
WHERE  status_cd <> 'SUCCESS'
ORDER BY full_name;
  • Amount reconciliation. Compare the computed amount against the amount actually posted, joined to the resulting pay proposal, to detect discrepancies.
  • Assignment change audit. Report prior versus proposed job, grade, and position for rows where ASSIGNMENT_CHANGED_FLAG is set, using the PRIOR_/PROPOSED_ column families.
  • Plan-level analytics. Aggregate AMOUNT and ADJUSTED_AMOUNT by PL_ID, PLAN, or organization for management reporting on the cycle.
  • Downstream verification. Join to PER_PAY_PROPOSALS and BEN_BENEFIT_ACTIONS to validate that each detail row produced the expected transactional record.

Because the table is a report detail store, it is generally read-only from a functional standpoint; typical DBA activity is limited to retention and purge management and to indexing the frequently filtered columns such as PERSON_ID, PL_ID, and STATUS_CD.

Related Objects

  • BEN_BENEFIT_ACTIONS — joined on BENEFIT_ACTION_ID; records the benefit action created or affected by the processed compensation change.
  • BEN_CWB_PERSON_RATES — joined on PERSON_RATE_ID; the rate record for the person that the post process evaluates.
  • BEN_CWB_PERSON_INFO — joined on GROUP_PER_IN_LER_ID; the grouped person information underlying the plan eligibility and processing.
  • PER_PAY_PROPOSALS — joined on PAY_PROPOSAL_ID; the salary proposal generated from the approved compensation decision.
  • PER_PAY_BASES — joined on PAY_BASIS_ID; the pay basis that governs how the proposal amount is expressed.
  • BEN_CWB_RPT_HEADER (or equivalent run-level table) — the parent run record against which detail rows are grouped for report presentation.
  • PER_ALL_PEOPLE_F — joined on PERSON_ID for current person attributes not denormalized into FULL_NAME and EMPLOYEE_NUMBER.
  • PAY_ELEMENT_ENTRIES_F — joined on ELEMENT_ENTRY_ID or ELEMENT_ENTRY_VALUE_ID to verify the payroll element entry produced by the run.

These relationships make BEN_CWB_RPT_DETAIL the connective record between Compensation Workbench planning data and the transactional benefit, salary, and payroll objects that persist the final outcome.