Search Results pa_pay_audit_all




Overview
Explore in AI Chat

Overview

PA_PAY_AUDIT_ALL is a transaction and audit table in the Oracle Projects (PA) schema. It stores the details of all payroll batches that are interfaced to Oracle Projects. When payroll data is transferred from Oracle Payroll (or an external payroll system) into Oracle Projects for cost distribution, the batch-level control information for each interface run is recorded here, providing a durable audit record of what was sent, when, and with what status.

The heuristic Data Vault classification for this object is standalone, derived from its foreign key structure. This is a modeling suggestion only: the table's only documented outbound foreign key is to PER_TIME_PERIODS, meaning it does not behave as a junction or link between two business hubs in the classic Data Vault sense. Functionally it is closest to a satellite or audit-log construct, since it captures attribute-level state (dates, status flags, request identifiers) rather than pure relationships.

Key Information Stored

The table carries 19 documented columns. The most significant are:

  • INTERFACE_RUN_ID — the surrogate primary key candidate, enforced by the unique index PA_PAY_AUDIT_U1. It identifies a single interface run and is the column most commonly used to tie audit rows back to a specific process execution.
  • ORG_ID — the operating unit (business group) that owns the batch; central to multi-org security and reporting.
  • PAYROLL_ID — identifies the payroll from which the batch originated.
  • BATCH_ID — the payroll batch identifier within the source payroll system.
  • INT_EXT_INDICATOR — distinguishes internal (Oracle Payroll) from external payroll sources.
  • TIME_PERIOD_ID — the payroll period; this is the FK to PER_TIME_PERIODS and is part of the second unique index.
  • PAY_PERIOD_START_DATE / PAY_PERIOD_END_DATE — the payroll period boundaries as delivered by the source.
  • SOURCE_START_DATE / SOURCE_END_DATE — the actual date range of the source assignment or timecard data included in the batch.
  • RUN_SEQUENCE — the sequence number for the batch; combined with the other keys, it disambiguates repeated runs of the same payroll and period.
  • PREVIOUS_RUN_ID — the interface run that preceded this one, enabling chained audit and rerun tracking.
  • PAYROLL_STATUS_FLAG — the processing status of the batch (e.g., whether it was successfully interfaced).
  • REQUEST_ID — the concurrent program request that produced the run, linking to the standard EBS requests table.
  • STANDARD WHO COLUMNS — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

The second unique index, PA_PAY_AUDIT_U2 (ORG_ID, PAYROLL_ID, INT_EXT_INDICATOR, TIME_PERIOD_ID, BATCH_ID, RUN_SEQUENCE), is the business-key candidate: it represents the natural uniqueness of a payroll batch per operating unit, payroll, period, and run sequence. INTERFACE_RUN_ID is the technical surrogate key.

Common Use Cases and Queries

The primary use case is reconciliation and audit: confirming which payroll batches were successfully interfaced to Projects and identifying batches that failed or were rerun. A typical query joins to the standard concurrent request table to display the program and completion status:

SELECT a.interface_run_id, a.org_id, a.payroll_id, a.batch_id,
    a.time_period_id, a.payroll_status_flag, a.request_id
  FROM pa_pay_audit_all a
 WHERE a.org_id = :p_org_id
   AND a.pay_period_start_date >= :p_start
 ORDER BY a.creation_date DESC;

A second common pattern detects reruns by tracing the PREVIOUS_RUN_ID chain, while a third aggregates batch counts and failed statuses by payroll and period for operational dashboards. Reporting on distinct INT_EXT_INDICATOR values helps distinguish internal versus third-party payroll feeds. Because the table is commonly used for point-in-time audit, reports frequently filter on PAY_PERIOD_END_DATE and CREATION_DATE to bound the population.

Related Objects

  • PER_TIME_PERIODS — the only documented FK target, joined on PA_PAY_AUDIT_ALL.TIME_PERIOD_ID = PER_TIME_PERIODS.TIME_PERIOD_ID. Provides payroll period names and dates for reporting.
  • PA_PAYROLL_COSTS / cost distribution tables — the pay elements actually interfaced; the audit row provides the batch header context.
  • PA_EXPENDITURES_ALL — receives the resulting expenditure items created from the payroll batches audited here.
  • FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to expose the concurrent program name, phase, and status of the interface run.
  • PA_PAYROLL_BATCHES — the batch definition against which the audit row's BATCH_ID is interpreted.
  • HR_ALL_ORGANIZATION_UNITS — resolved via ORG_ID to obtain the operating unit name for reports.
  • PA_INTERFACE_RUNS / interface control objects — where INTERFACE_RUN_ID and PREVIOUS_RUN_ID are referenced for run lineage.
  • Oracle Payroll (PAY) assignment and run tables — the upstream source for externally and internally interfaced batches.

Because PA_PAY_AUDIT_ALL is a standalone audit table with a single documented outbound FK, integrity relies on application logic rather than an extensive relational constraint network; queries should therefore validate referenced identifiers against the source payroll and concurrent request tables when precision is required.