Results for “hr_efc_workers_pk”

12 results




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

Overview

HR.HR_EFC_WORKERS is a transactional table in the Oracle E-Business Suite Human Resources (HR) schema. It is registered in FND Design Data as PER.HR_EFC_WORKERS and holds exactly one row for each parallel worker instance of a processing component. In practice the table functions as the runtime registry for Enterprise Foundation Component (EFC) parallel processing: whenever a processing component is split into concurrent worker processes, a corresponding row records the existence, sequence number, and current execution status of that worker, together with the highest primary-key position it has successfully committed. This makes the table the anchor point for restart, recovery, and progress monitoring of long-running HR batch operations.

From a Data Vault modeling perspective, the metadata heuristic classifies this object as satellite-leaning. The classification is a modeling suggestion only: the table carries descriptive, state-bearing attributes (status, worker number, restart columns, SPID) attached to a parent component, which is characteristic of satellite behavior, while the foreign keys to HR_EFC_PROCESS_COMPONENTS and HR_EFC_ACTIONS also give it link-like reach into the wider EFC process graph.

Key Information Stored

  • EFC_WORKER_ID — NUMBER(15). The surrogate primary key, defined by the unique index HR_EFC_WORKERS_PK. Callers searching for "hr_efc_workers_pk" are looking for this constraint and column.
  • EFC_PROCESS_COMPONENT_ID — NUMBER(15). Foreign key to HR_EFC_PROCESS_COMPONENTS; identifies the parent processing component whose work this worker instance is executing.
  • EFC_ACTION_ID — NUMBER(15). Foreign key to HR_EFC_ACTIONS; identifies the specific action the worker is performing.
  • WORKER_NUMBER — NUMBER(15). The worker process number within the parent component. Together with EFC_PROCESS_COMPONENT_ID it forms the business-key candidate HR_EFC_WORKERS_UK01.
  • WORKER_PROCESS_STATUS — VARCHAR2(30). Indicates how far this worker instance has reached in its execution; the principal column for monitoring and restart logic.
  • PK1 through PK5 — the first through fifth columns of the primary key of the last row successfully modified and committed. PK1 is NUMBER(15); PK2 through PK5 are VARCHAR2(100). These drive chunked restart of partitioned or range-driven processing.
  • SPID — VARCHAR2. Populated from the v$process value when SQL tracing is enabled and WORKER_PROCESS_STATUS is not 'C' (completed).
  • Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE provide audit and concurrency tracking consistent with EBS conventions.

Common Use Cases and Queries

The most frequent requirement is to determine how far a parallel EFC job has progressed and which workers are still active. Because WORKER_PROCESS_STATUS carries an 'C' value for completed workers, filtering on status yields an immediate view of outstanding work:

  • Active worker listing: SELECT w.EFC_WORKER_ID, w.EFC_PROCESS_COMPONENT_ID, w.WORKER_NUMBER, w.WORKER_PROCESS_STATUS, w.SPID FROM HR.HR_EFC_WORKERS w WHERE w.WORKER_PROCESS_STATUS <> 'C';
  • Component-level rollup: group by EFC_PROCESS_COMPONENT_ID with COUNT(*) and MIN/MAX WORKER_NUMBER to size the parallelism actually instantiated for a component.
  • Restart positioning: read PK1 through PK5 for a given worker to determine the last committed key position, then resume processing from that point rather than from the beginning.
  • Tracing diagnostics: join SPID to v$session and v$process to correlate a worker row with its database session during performance investigations.
  • Join to parent context: join EFC_PROCESS_COMPONENT_ID to HR_EFC_PROCESS_COMPONENTS and EFC_ACTION_ID to HR_EFC_ACTIONS to report which business action a stalled worker belongs to.

Because the table is a live control structure, queries against it are typically read-only; direct DML by custom code is not advisable and can corrupt restart logic.

Related Objects

  • HR.HR_EFC_PROCESS_COMPONENTS — parent of the EFC_PROCESS_COMPONENT_ID foreign key; defines the processing component each worker serves.
  • HR.HR_EFC_ACTIONS — parent of the EFC_ACTION_ID foreign key; defines the action being executed.
  • HR.HR_EFC_WORKER_AUDITS — child table referencing EFC_WORKER_ID; stores audit records for worker activity.
  • Index HR_EFC_WORKERS_PK — unique index on EFC_WORKER_ID in APPS_TS_TX_IDX, enforcing the surrogate primary key.
  • Index HR_EFC_WORKERS_UK01 — unique index on EFC_PROCESS_COMPONENT_ID and WORKER_NUMBER, enforcing the business key.
  • Database views v$process and v$session — referenced indirectly through the SPID column for tracing and session correlation.

The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, and all data access flows through standard EBS processing components rather than public APIs.