Search Results hr_audits




Overview
Explore in AI Chat

Overview

The HR_AUDITS table is a core audit trail mechanism within the Oracle E-Business Suite (EBS) Payroll module (PAY). It serves as the primary repository for recording transactional changes made to audited HR and payroll data. Its role is to capture a historical record of modifications, including inserts, updates, and deletes, by logging entries from associated shadow tables. This functionality is critical for compliance, data integrity verification, troubleshooting, and reconstructing the state of key information at a specific point in time. The table is owned by the HR schema and is a validated object in both EBS 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to capture the essential metadata of each audited transaction. Its primary key is the AUDIT_ID column, a unique sequence-generated identifier for each audit event. As indicated by the foreign key relationship, the USER_NAME column stores the identifier of the FND_USER who performed the transaction, linking the change to a specific system user. While the provided metadata does not list all columns, typical audit tables in this context also store the name of the audited base table (e.g., PER_ALL_PEOPLE_F), the type of operation performed (INSERT, UPDATE, DELETE), a transaction identifier, and timestamps for when the change was made and committed. Each AUDIT_ID in HR_AUDITS acts as a header record for detailed column-level changes.

Common Use Cases and Queries

The primary use case is forensic analysis and reporting on data changes. Common queries involve identifying all modifications made to a specific employee record or within a particular date range. For example, to audit changes to person records for a given individual, one might join HR_AUDITS to HR_AUDIT_COLUMNS on AUDIT_ID, filtering on the base table name and a business key like PERSON_ID. Another typical pattern is to generate a report of all payroll-related updates performed by a specific user. Sample SQL often follows this structure:

  • SELECT ha.audit_id, ha.user_name, ha.table_name, ha.transaction_type, ha.timestamp FROM hr_audits ha WHERE ha.table_name = 'PER_ALL_ASSIGNMENTS_F' AND ha.timestamp BETWEEN :p_start_date AND :p_end_date ORDER BY ha.timestamp DESC;

This data is essential for resolving data discrepancies, supporting audit requests, and understanding the sequence of changes leading to a particular payroll result.

Related Objects

HR_AUDITS is centrally linked to several key objects. The HR_AUDIT_COLUMNS table holds the detailed before-and-after values for columns changed in each transaction, linked directly via the AUDIT_ID foreign key. The FND_USER table is referenced to provide the full name and description for the USER_NAME stored in the audit header. The table's purpose implies relationships with the various shadow tables (e.g., shadow versions of PER_ALL_PEOPLE_F, PAY_PAYROLLS_F) from which audit records are sourced. The primary key constraint HR_AUDITS_PK enforces uniqueness on the AUDIT_ID column, ensuring the integrity of the audit trail.