Results for “hr_audit_columns”

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

Overview

HR_AUDIT_COLUMNS is a transactional detail table owned by the HR schema and classified under the PAY (Payroll) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the column-level changes captured against specific audit transactions. Where the parent audit record in HR_AUDITS identifies a business event or row-level change, HR_AUDIT_COLUMNS decomposes that event into the individual columns affected, retaining both the old and new values. This design gives Oracle EBS a granular, historically traceable view of edits made to audited HR and payroll entities without requiring dedicated shadow columns on each audited table.

From a data modeling perspective, the object exhibits a satellite-leaning classification (heuristic, mined from its foreign key structure). It is not a hub: it does not carry a standalone business key. It is not a link: it does not resolve a many-to-many relationship between independent hubs. Instead, it hangs off the HR_AUDITS parent as descriptive, time-versioned context — the signature of a satellite. The heuristic should be treated as a modeling suggestion, not a binding classification, but it is consistent with the table's role as an audit detail store.

Key Information Stored

The documented physical schema comprises five columns. The most significant are:

• AUDIT_ID — The foreign key to HR_AUDITS. This is the principal joining column and groups each row into its parent audit transaction. It is the anchor of the satellite relationship.
• COLUMN_ID — A numeric identifier for the column affected. Together with AUDIT_ID it supports ordering and disambiguation of multiple column changes within one audit event.
• COLUMN_NAME — The name of the audited column whose value changed. This is the closest thing to a business-key candidate in the table, identifying which attribute of the audited entity was modified.
• OLD_VALUE — The value held by the column before the audited transaction. Essential for before/after comparison and rollback analysis.
• NEW_VALUE — The value written by the audited transaction. Paired with OLD_VALUE, it forms the delta that defines the change.

No surrogate primary key beyond the implied AUDIT_ID/COLUMN_ID combination is documented, and no unique index is listed in the supplied metadata. The composite of AUDIT_ID and COLUMN_ID functions as the effective row identifier.

Common Use Cases and Queries

The primary use case is after-the-fact change auditing: reconstructing who changed what, when, and from which value to which value. Typical reporting scenarios include payroll element entry corrections, assignment attribute changes, and compliance investigations where a historical trail is required.

A representative query joins the detail to its parent to recover the audit context:

• SELECT c.AUDIT_ID, c.COLUMN_NAME, c.OLD_VALUE, c.NEW_VALUE FROM hr.hr_audit_columns c WHERE c.AUDIT_ID = :audit_id ORDER BY c.COLUMN_ID;

A second pattern filters for changes to a specific attribute, for example retrieving all historical modifications to a given column name across a date range derived from the parent audit:

• SELECT c.AUDIT_ID, c.COLUMN_NAME, c.OLD_VALUE, c.NEW_VALUE FROM hr.hr_audit_columns c, hr.hr_audits a WHERE c.AUDIT_ID = a.AUDIT_ID AND c.COLUMN_NAME = :column_name;

Analysts also use the table to count the frequency of changes per column, producing a picture of which attributes are most volatile in a business process.

Related Objects

The relationships documented in the ETRM metadata are narrow: HR_AUDIT_COLUMNS references HR_AUDITS via AUDIT_ID. That single foreign key is the most significant dependency and should always be used when joining the detail to its transaction header. Because the table is a satellite hanging from HR_AUDITS, any object that consumes HR_AUDITS directly — audit inquiry forms, audit reports, and the underlying audit capture logic — transitively depends on HR_AUDIT_COLUMNS for its column-level detail.

Beyond HR_AUDITS, related objects are contextually inferred rather than FK-documented. These include the audited base tables within the HR and PAY schemas whose changes generate the audit records, and any reporting views built over the audit pair. When building joins, restrict the relationship to the documented HR_AUDITS.AUDIT_ID link and treat all other associations as logical rather than enforced by the database.