Results for “pay_stat_trans_audit”

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

Overview

PAY_STAT_TRANS_AUDIT is a Payroll (PAY) module table owned by the HR schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the transaction history of payroll-related records to satisfy statutory audit requirements. The table captures a durable, append-only record of changes applied to employee payroll data, including changes initiated through employee self-service. For the US localization, for example, it retains the details of W-4 withholding changes made when an employee logs in through Self Service and modifies their records. Because the table preserves historical values independently of the live payroll record, it functions as an audit trail rather than an operational transaction table. The object is marked VALID and exposes 57 documented columns in the ETRM 12.2.2 physical schema.

Under a heuristic Data Vault classification mined from the documented foreign key structure, PAY_STAT_TRANS_AUDIT is satellite-leaning. This classification is a modeling suggestion: the table records descriptive, time-stamped attributes about a parent transaction rather than acting as a hub of business entities or a link resolving many-to-many relationships. The self-referencing foreign key on TRANSACTION_PARENT_ID reinforces the satellite character, since audit rows can be related hierarchically to a parent transaction record within the same table.

Key Information Stored

The primary key is the surrogate identifier STAT_TRANS_AUDIT_ID, enforced by the PAY_STAT_TRANS_AUDIT_PK constraint. This column uniquely identifies each audit row and should be treated as a system-generated surrogate key rather than a business key. Business context is supplied by PERSON_ID and ASSIGNMENT_ID, which associate the audited change with a specific employee and assignment. TRANSACTION_TYPE and TRANSACTION_SUBTYPE classify the nature of the recorded event, while TRANSACTION_DATE and TRANSACTION_EFFECTIVE_DATE distinguish when the transaction was captured from when it takes effect.

TRANSACTION_PARENT_ID is a self-referencing foreign key used to relate an audit entry to its parent transaction, supporting hierarchical audit relationships. SOURCE1 through SOURCE5, each accompanied by a corresponding SOURCE1_TYPE through SOURCE5_TYPE, provide flexible attribution for the origin of the change. AUDIT_INFORMATION_CATEGORY plus AUDIT_INFORMATION1 through AUDIT_INFORMATION30 form a wide, generic attribute set used to store the detail of the audited change — for US localization, the W-4 values captured at the time of modification. BUSINESS_GROUP_ID scopes the row to a specific business group. Standard WHO columns are present: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN, along with OBJECT_VERSION_NUMBER for optimistic locking and TITLE for a descriptive label. Critically, the documented metadata lists no unique business-key index beyond the primary key, so the surrogate key is the sole documented unique identifier.

Common Use Cases and Queries

The principal use case is audit and compliance reporting: retrieving the history of changes made to an employee's payroll attributes for a given period. A typical query filters by person and transaction date:

• SELECT stat_trans_audit_id, transaction_type, transaction_subtype, transaction_date, audit_information_category, audit_information1 FROM hr.pay_stat_trans_audit WHERE person_id = :p_person_id AND transaction_date BETWEEN :p_from AND :p_to ORDER BY transaction_date;
• Trace a specific self-service change by joining on the surrogate key and walking TRANSACTION_PARENT_ID to reconstruct the transaction lineage.
• Report W-4 change activity for US localization by filtering on TRANSACTION_TYPE/TRANSACTION_SUBTYPE and reading AUDIT_INFORMATION columns.
• Reconcile self-service modifications against payroll results by joining to assignment and person reference tables.

Because the table is append-only, reporting queries should be read-only and scoped by BUSINESS_GROUP_ID and date range to control volume.

Related Objects

• PAY_STAT_TRANS_AUDIT (self-reference): TRANSACTION_PARENT_ID references PAY_STAT_TRANS_AUDIT, establishing the parent-child audit relationship.
• PER_ALL_PEOPLE_F / PER_PERSON_NAMES_F: joined via PERSON_ID to resolve employee identity.
• PER_ALL_ASSIGNMENTS_F: joined via ASSIGNMENT_ID to resolve assignment context.
• HR_ALL_ORGANIZATION_UNITS: joined via BUSINESS_GROUP_ID to resolve business group scoping.
• PAY_PEOPLE_GROUPS and PAY_ELEMENT_TYPES_F: referenced indirectly when interpreting the payroll transaction type stored in the audit row.
• Payroll Self Service (SSHR) and the Payroll W-4 update flows are the principal application entry points that populate this table.