Search Results hr_audits_pk




Overview
Explore in AI Chat

Overview

The HR.HR_AUDITS table is a core audit trail object within the Oracle E-Business Suite Human Resources (HRMS) module, specifically for versions 12.1.1 and 12.2.2. It serves as a centralized repository for recording metadata about transactions performed on DateTracked tables, such as those for employees, assignments, and salaries. This table does not store the changed data values itself; instead, it logs the context of the change—who made it, when, and on which record. The data is populated not by standard application forms, but by running the dedicated audit reporting package PY_AUDIT_REP_PKG, which extracts and transforms change history from the shadow tables associated with DateTracked entities. Its primary role is to support compliance, troubleshooting, and historical reporting on HR data changes.

Key Information Stored

The table's columns capture the essential dimensions of an audited transaction. The primary key, AUDIT_ID, is a unique sequence-generated identifier. Critical context is provided by TABLE_NAME, PRIMARY_KEY, and PRIMARY_KEY_VALUE, which pinpoint the exact database row that was modified. The transaction's nature is defined by TRANSACTION_TYPE (I/U/D) and a human-readable TRANSACTION description. Temporal context is captured via TIMESTAMP (the change time) and the DateTrack-specific EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. User and session context is stored in USER_NAME, SESSION_ID, and CURRENT_SESSION_ID. Finally, COMMIT_ID and SEQUENCE_ID are used to order and group transactions within a single logical commit unit, preserving transactional integrity in the audit log.

Common Use Cases and Queries

A primary use case is generating reports of all changes made to a specific employee's records over a period. This is crucial for audits and resolving data discrepancies. Another common scenario is identifying all users who modified a particular table, such as PER_ALL_PEOPLE_F, on a given day. Sample queries often join HR_AUDITS with HR_AUDIT_COLUMNS to get both the transaction context and the detailed old/new values. A foundational query pattern is:

  • Tracking changes to a specific employee: SELECT * FROM hr.hr_audits WHERE table_name = 'PER_ALL_ASSIGNMENTS_F' AND primary_key_value = :emp_id ORDER BY timestamp;
  • Summarizing audit activity: SELECT user_name, table_name, transaction_type, COUNT(*) FROM hr.hr_audits WHERE timestamp > TRUNC(SYSDATE-7) GROUP BY user_name, table_name, transaction_type;

These queries are typically executed as part of custom audit reports or data validation scripts.

Related Objects

HR_AUDITS has a direct, documented foreign key relationship with the HR_AUDIT_COLUMNS table. The HR_AUDITS_PK primary key column (AUDIT_ID) is referenced by the foreign key column of the same name in HR_AUDIT_COLUMNS. This is a one-to-many relationship where a single header record in HR_AUDITS links to multiple detail records in HR_AUDIT_COLUMNS, which store the actual column-level changes. The table's population is solely dependent on the package PY_AUDIT_REP_PKG, as noted in the ETRM. Furthermore, the table's design data originates from the PAY.HR_AUDITS FND Design layer object, indicating its cross-module relevance between HR and Payroll.