Results for “new_val_date”

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

Overview

BEN_CWB_AUDIT is a table in the BEN schema (Oracle Advanced Benefits) that stores audit trail records for Compensation Workbench (CWB) activity. Compensation Workbench allows managers and administrators to allocate, adjust, and publish compensation components such as salaries, bonuses, and awards across an organization hierarchy. Because CWB involves iterative planning, plan changes, and publish cycles, Oracle maintains detailed audit rows that capture what changed, when it changed, and who made the change.

Each row in BEN_CWB_AUDIT represents a discrete change event tied to a person's CWB record (indicated by GROUP_PER_IN_LER_ID referencing BEN_CWB_PERSON_INFO), a plan or plan group, and an audit type. The table is populated by the CWB application logic whenever a tracked field is modified, providing downstream reconciliation, compliance, and reporting capabilities.

From a Data Vault modeling perspective, the ETRM mined classification identifies this table as standalone (no inbound dependent relationships from the FK perspective). In Data Vault terms, this behavior resembles a satellite attached to the person-level CWB record: it carries descriptive change attributes, effectivity-style timestamps, and audit metadata, but does not itself act as a hub or link driving additional dependencies. This is a heuristic modeling suggestion rather than a mandated design.

Key Information Stored

The table contains 26 documented columns in the 12.2.2 physical schema. The most significant are:

• CWB_AUDIT_ID — surrogate primary key, supported by unique index BEN_CWB_AUDIT_PK. This is the only unique index documented, and no separate business-key unique index appears in the metadata.
• GROUP_PER_IN_LER_ID — foreign key to BEN_CWB_PERSON_INFO; identifies the person/CWB line affected by the change.
• GROUP_PL_ID and PL_ID — plan and plan-group identifiers scoping the change.
• GROUP_OIPL_ID — originating or organization-level plan identifier.
• AUDIT_TYPE_CD — classification of the change event (e.g., value edit, recalc, publish).
• OLD_VAL_VARCHAR / NEW_VAL_VARCHAR, OLD_VAL_NUMBER / NEW_VAL_NUMBER, and OLD_VAL_DATE / NEW_VAL_DATE — typed before/after value pairs, allowing heterogeneous data types to be tracked in one table.
• SUPPORTING_INFORMATION — free-form context explaining the change.
• CHANGE_MADE_BY_PERSON_ID — who performed the change.
• LF_EVT_OCRD_DT and DATE_STAMP — life-event occurrence and audit timestamps.
• REQUEST_ID — links to the concurrent request that generated the change.
• OBJECT_VERSION_NUMBER — optimistic locking column maintained by the framework.
• CREATION_DATE / CREATED_BY / LAST_UPDATE_DATE / LAST_UPDATED_BY / LAST_UPDATE_LOGIN — standard "Who" audit columns populated by the Oracle Application Object Library.

Common Use Cases and Queries

Typical scenarios include investigating why a compensation number differs between cycles, verifying who approved a late change, and producing SOX/audit evidence for compensation publishing. Because old/new values are stored under separate typed columns, queries should coalesce the different types:

• Reconstruct a person's compensation change history by joining back to BEN_CWB_PERSON_INFO on GROUP_PER_IN_LER_ID and ordering by DATE_STAMP.
• Trace all changes made within a concurrent request by filtering on REQUEST_ID — useful for batch reprocessing and troubleshooting.
• Report changes by plan or plan group using PL_ID / GROUP_PL_ID to isolate specific compensation components.
• Identify stale or conflicting updates by comparing OBJECT_VERSION_NUMBER across rows for the same person and plan.

A representative query pattern:

SELECT a.cwb_audit_id, a.group_per_in_ler_id, a.pl_id,
       a.audit_type_cd,
       COALESCE(a.old_val_varchar, TO_CHAR(a.old_val_number), TO_CHAR(a.old_val_date,'YYYY-MM-DD')) old_value,
       COALESCE(a.new_val_varchar, TO_CHAR(a.new_val_number), TO_CHAR(a.new_val_date,'YYYY-MM-DD')) new_value,
       a.change_made_by_person_id, a.date_stamp
FROM   ben.ben_cwb_audit a
WHERE  a.group_per_in_ler_id = :person_info_id
ORDER  BY a.date_stamp DESC;

Related Objects

• BEN_CWB_PERSON_INFO — the documented foreign key target via GROUP_PER_IN_LER_ID; the core per-person CWB record this audit table enriches.
• BEN_CWB_AUDIT_PK — the primary key index on CWB_AUDIT_ID; must be considered for any high-volume join or range scan.
• BEN_PL_F and dependent plan tables — referenced logically by PL_ID and GROUP_PL_ID to resolve plan names.
• FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to identify the process that produced the change.
• PER_ALL_PEOPLE_F — joined on CHANGE_MADE_BY_PERSON_ID to resolve the acting user.
• BEN_CWB_DETAIL / BEN_CWB_LINE — related CWB detail structures that frequently need to be compared against audit output for reconciliation.
• FND_AUDIT tables and standard WHO columns — provide the framework-level audit context mirrored by CREATION_DATE and LAST_UPDATE_DATE.