Search Results pa_pay_audit_u1

Overview

PA.PA_PAY_AUDIT_ALL is a transaction data table in the Oracle Projects (PA) schema that stores an audit trail of payroll interface activity generated by the Interface Payroll Process in Oracle Projects. Each row records the payroll runs processed for a given operating unit, payroll, batch, and time period, capturing both the payroll-side identifiers and the Projects-side interface run identifiers that tie the two legs of the integration together. In Oracle EBS 12.1.1 and 12.2.2 the table resides in the APPS_TS_TX_DATA tablespace with a PCTFREE of 10, and is registered under FND Design Data as PA.PA_PAY_AUDIT_ALL with a status of VALID.

From a modeling perspective, the ETRM heuristic classifies this object as standalone in a Data Vault sense; it does not behave as a classic hub, link, or satellite, but rather as a functionally keyed audit/control table. Its business identity is defined entirely by the unique index PA_PAY_AUDIT_U2, and it maintains only one documented foreign key (to PER_TIME_PERIODS), which supports the standalone classification.

Key Information Stored

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

• ORG_ID – Operating unit identifier in multi-organization installations; part of the PA_PAY_AUDIT_U2 business key.
• INTERFACE_RUN_ID – Unique identifier generated in Oracle Projects when the Interface Payroll Process executes; this is the solitary column in the unique index PA_PAY_AUDIT_U1 and functions as the closest equivalent to a surrogate primary key.
• PAYROLL_ID – Foreign key to PAY_ALL_PAYROLLS_F; part of PA_PAY_AUDIT_U2.
• BATCH_ID – Identifier of the payroll batch; part of PA_PAY_AUDIT_U2.
• TIME_PERIOD_ID – Foreign key to PER_TIME_PERIODS in Oracle Payroll; part of PA_PAY_AUDIT_U2.
• RUN_SEQUENCE – Automatically generated sequence number; part of PA_PAY_AUDIT_U2.
• INT_EXT_INDICATOR – Indicates whether the source pay element is Internal or External; part of PA_PAY_AUDIT_U2.
• PAY_PERIOD_START_DATE / PAY_PERIOD_END_DATE – Boundaries of the pay period being audited.
• SOURCE_START_DATE / SOURCE_END_DATE – Earned-period boundaries used for retroactive amounts.
• PAYROLL_STATUS_FLAG – Status of the payroll run at the time of the audit record.
• PREVIOUS_RUN_ID – Self-referencing key to a prior Interface Payroll execution, enabling chain-of-runs tracing.
• Standard Who columns – LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID.

The two unique indexes together express the table's business identity: PA_PAY_AUDIT_U1 on INTERFACE_RUN_ID guarantees that each Projects-side interface execution is recorded once, while PA_PAY_AUDIT_U2 on (ORG_ID, PAYROLL_ID, INT_EXT_INDICATOR, TIME_PERIOD_ID, BATCH_ID, RUN_SEQUENCE) enforces uniqueness across the payroll-side composite key.

Common Use Cases and Queries

Typical uses include auditing payroll transfer activity, reconciling Projects labour costs against Payroll runs, detecting duplicate or missing interface executions, and tracing retroactive adjustments via PREVIOUS_RUN_ID. A common query pattern retrieves all interface runs for a given operating unit and pay period:

• SELECT interface_run_id, payroll_id, batch_id, run_sequence, payroll_status_flag FROM pa.pa_pay_audit_all WHERE org_id = :p_org_id AND pay_period_start_date BETWEEN :p_from AND :p_to ORDER BY interface_run_id;
• SELECT a.interface_run_id, a.previous_run_id, a.time_period_id, p.period_name FROM pa.pa_pay_audit_all a, per_time_periods p WHERE a.time_period_id = p.time_period_id AND a.org_id = :p_org;
• Duplicate-detection: SELECT interface_run_id, COUNT(*) FROM pa.pa_pay_audit_all GROUP BY interface_run_id HAVING COUNT(*) > 1; (should always return zero given PA_PAY_AUDIT_U1).

Reporting use cases include cost-transfer reconciliation reports, retro-pay analysis for a specific SOURCE_START_DATE window, and payroll status dashboards driven by PAYROLL_STATUS_FLAG. Because the table is _ALL, queries must be filtered by ORG_ID without relying on a VPD-enabled synonym, or use the corresponding MO view if one exists.

Related Objects

• PER_TIME_PERIODS – Referenced via PA_PAY_AUDIT_ALL.TIME_PERIOD_ID = PER_TIME_PERIODS.TIME_PERIOD_ID; the only documented foreign key.
• PA.PAY_ALL_PAYROLLS_F – Referenced via PAYROLL_ID; supplies payroll definitions and names.
• PA.PA_PAYROLL_RUNS_ALL / Interface Payroll Process – Conceptual source that generates INTERFACE_RUN_ID values.
• PA.PA_COST_DISTRIBUTION_LINES_ALL – Downstream cost lines produced by the same interface run; join by INTERFACE_RUN_ID where available.
• PER_TIME_PERIODS dependent views and payroll reporting views that surface time-period context.
• HR_ORGANIZATION_UNITS / FND_OPERATING_UNITS – Provide operating-unit attributes for ORG_ID.
• FND_CONCURRENT_REQUESTS – Resolved via REQUEST_ID for traceability of the originating concurrent job.
• PA_PAY_AUDIT_ALL self-reference – PREVIOUS_RUN_ID links to the same table to chain successive interface runs.