Search Results pay_payroll_action

Overview

HR.PAY_TEMP_OBJECT_ACTIONS is a transactional table in the Oracle E-Business Suite payroll schema that records the execution state of individual database objects processed by a payroll run. Each row represents one object-action pair, capturing the object type, the object identifier, the payroll action it belongs to, and the processing status of that object at a given point in the payroll execution lifecycle. The table is owned by the HR schema and carries the FND Design Data identifier PAY.PAY_TEMP_OBJECT_ACTIONS, indicating that the underlying AOL/Forms definition resides in the PAY application while the physical object is stored under HR.

The table functions primarily as a work-tracking and parallelization scaffold for the payroll engine. Rather than persisting final payroll results, it holds intermediate execution details — including the chunk number and action sequence — that allow the payroll process manager to distribute work across threads and determine the physical ordering of events. The name prefix PAY_TEMP_ signals that rows are typically transient, retained only for the duration of a payroll action or a short diagnostic window.

From a Data Vault modeling perspective, the documented foreign-key structure (a single reference from PAYROLL_ACTION_ID to PAY_PAYROLL_ACTIONS) and the absence of downstream dependents suggest this table is best treated as a standalone structure — effectively a satellite-like log of execution events keyed by its own surrogate OBJECT_ACTION_ID, rather than a true hub or link. This classification is a heuristic mined from the FK graph and should be validated against the actual payroll execution architecture before use in a formal warehouse design.

Key Information Stored

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

• OBJECT_ACTION_ID — Surrogate primary key (NUMBER(15)) enforced by PAY_TEMP_OBJECT_ACTIONS_PK. It uniquely identifies each object-action record.
• OBJECT_ID — NUMBER(15) foreign key to the database object identified by OBJECT_TYPE; the target changes depending on the type of object being processed.
• OBJECT_TYPE — VARCHAR2(30) describing the class of object referenced by OBJECT_ID (for example, an assignment, element entry, or run result).
• PAYROLL_ACTION_ID — NUMBER foreign key to PAY_PAYROLL_ACTIONS, tying each record to its parent payroll action.
• ACTION_STATUS — VARCHAR2 holding the valid processing status of the object action; the primary state indicator for the row.
• CHUNK_NUMBER — NUMBER(15) used to group events into chunks for parallelization across payroll threads.
• ACTION_SEQUENCE — NUMBER(15) absolute sequence number establishing the physical order of events.
• OBJECT_VERSION_NUMBER — System-generated version incremented on each row update, supporting optimistic locking.

Business-key candidates are not strongly enforced: the supporting indexes PAY_TEMP_OBJECT_ACTIONS_N1 (OBJECT_TYPE, OBJECT_ID), N50 (PAYROLL_ACTION_ID, CHUNK_NUMBER), and N51 (PAYROLL_ACTION_ID, OBJECT_TYPE, OBJECT_ID) are all NONUNIQUE. The composite of PAYROLL_ACTION_ID plus OBJECT_TYPE plus OBJECT_ID is the closest practical business identifier for a given object within a run, but the schema does not enforce its uniqueness.

Common Use Cases and Queries

Typical scenarios include monitoring the progress of a payroll action, diagnosing objects that failed or stalled in a given status, and analyzing how work was chunked across parallel processes.

To review the objects processed by a specific payroll action:

SELECT OBJECT_ACTION_ID, OBJECT_TYPE, OBJECT_ID, ACTION_STATUS,
       CHUNK_NUMBER, ACTION_SEQUENCE
FROM   HR.PAY_TEMP_OBJECT_ACTIONS
WHERE  PAYROLL_ACTION_ID = :p_action_id
ORDER  BY ACTION_SEQUENCE;

To count objects by status for a run (a quick health check):

SELECT ACTION_STATUS, COUNT(*)
FROM   HR.PAY_TEMP_OBJECT_ACTIONS
WHERE  PAYROLL_ACTION_ID = :p_action_id
GROUP  BY ACTION_STATUS;

To inspect the parallelization layout, grouping by chunk:

SELECT CHUNK_NUMBER, COUNT(*) objects, MIN(ACTION_SEQUENCE) first_seq,
       MAX(ACTION_SEQUENCE) last_seq
FROM   HR.PAY_TEMP_OBJECT_ACTIONS
WHERE  PAYROLL_ACTION_ID = :p_action_id
GROUP  BY CHUNK_NUMBER
ORDER  BY CHUNK_NUMBER;

Reporting use cases include throughput analysis of payroll runs, identifying object types that disproportionately fail, and verifying that chunk distribution is balanced. Because the table is transient, queries should target currently active or recently completed payroll actions.

Related Objects

The documented dependencies center on the payroll action hierarchy:

• HR.PAY_PAYROLL_ACTIONS — Referenced by the PAYROLL_ACTION_ID foreign key; the parent table describing each payroll action. This is the primary join partner.
• HR.PAY_TEMP_OBJECT_ACTIONS# — The underlying base-table synonym/object referenced by the table in the dependency metadata.
• PAY_TEMP_OBJECT_ACTIONS_PK — Primary key index on OBJECT_ACTION_ID.
• PAY_TEMP_OBJECT_ACTIONS_N1 — Nonunique index on OBJECT_TYPE, OBJECT_ID; supports lookups by target object.
• PAY_TEMP_OBJECT_ACTIONS_N50 — Nonunique index on PAYROLL_ACTION_ID, CHUNK_NUMBER; supports chunk-oriented queries.
• PAY_TEMP_OBJECT_ACTIONS_N51 — Nonunique index on PAYROLL_ACTION_ID, OBJECT_TYPE, OBJECT_ID; supports object-level lookups within a run.

The table does not reference any additional database objects beyond PAY_PAYROLL_ACTIONS, and no other documented objects reference it besides its own base table. Join columns of interest are PAYROLL_ACTION_ID (to the parent action) and OBJECT_TYPE plus OBJECT_ID (to the processed entity). All objects reside in the HR schema under tablespace APPS_TS_TX_DATA, with indexes in APPS_TS_TX_IDX.