Search Results pa_spawned_program_statuses_u1

Overview

PA.PA_SPAWNED_PROGRAM_STATUSES is a transactional status-tracking table in the Oracle Projects (PA) schema. It records the execution status of spawned concurrent programs — that is, programs submitted programmatically by a parent process rather than invoked directly by a user. In Oracle EBS Release 12.1.1 and 12.2.2, the only spawned program documented in Oracle Projects is the Overtime Calculation program, which is launched from the Distribute Labor Costs process. The Distribute Labor Costs process writes rows into this table and subsequently reads them to monitor whether the spawned Overtime Calculation program has completed, thereby coordinating parent and child concurrent requests.

The table resides in the APPS_TS_SEED tablespace and is registered as FND Design Data under the same name. Heuristically, based on its foreign-key and column structure, the object models as a standalone or link-style construct: it does not reference other database objects, and its identity is a composite of the parent request and the spawned program. It functions as an association between a parent concurrent request and a child program execution, with status and audit attributes attached to that association.

Key Information Stored

The table is defined by a composite primary key, PA_SPAWNED_PROGRAM_STATUSES_PK, comprising REQUEST_ID and PROGRAM_ID. A unique index, PA_SPAWNED_PROGRAM_STATUSES_U1, enforces the same two-column combination and is the business-key candidate most often targeted by lookups. The most significant columns are:

• REQUEST_ID (NUMBER 15) — identifier of the concurrent request associated with the spawned program; part of the primary key and unique index.
• PROGRAM_ID (NUMBER 15) — identifier of the spawned concurrent program; part of the primary key and unique index.
• PROGRAM_APPLICATION_ID (NUMBER 15) — application owning the spawned program, used to resolve the program definition.
• PROGRAM_UPDATE_DATE (DATE) — timestamp associated with the program's status update, used by the parent process to gauge progress.
• LAST_UPDATE_DATE (DATE) — standard audit column recording the most recent modification.
• LAST_UPDATED_BY (NUMBER 15) — user who last modified the row.
• CREATION_DATE (DATE) — standard audit column recording row insertion.
• CREATED_BY (NUMBER 15) — user who created the row.
• LAST_UPDATE_LOGIN (NUMBER 15) — login context of the last update.

All columns are documented as Standard Who Columns; only REQUEST_ID and PROGRAM_ID participate in the unique business key.

Common Use Cases and Queries

The primary use case is operational monitoring: determining whether the Overtime Calculation program spawned by Distribute Labor Costs has finished. A typical query joins this table to concurrent request tables via REQUEST_ID and filters by PROGRAM_ID.

• Locating status rows for a specific parent request: SELECT * FROM PA.PA_SPAWNED_PROGRAM_STATUSES WHERE REQUEST_ID = :p_request_id;
• Confirming existence of a spawned-program record using the unique key columns: SELECT COUNT(*) FROM PA.PA_SPAWNED_PROGRAM_STATUSES WHERE REQUEST_ID = :p_request_id AND PROGRAM_ID = :p_program_id;
• Audit and staleness checks using LAST_UPDATE_DATE and PROGRAM_UPDATE_DATE to identify long-running or stalled spawned programs.
• Reporting on all rows generated within a period for reconciliation of payroll and labor cost runs.

Related Objects

The metadata records no outbound dependencies: PA.PA_SPAWNED_PROGRAM_STATUSES does not reference any database object. It is referenced by the APPS synonym PA_SPAWNED_PROGRAM_STATUSES. Logical operational joins include:

• FND_CONCURRENT_REQUESTS — joined on REQUEST_ID to retrieve request phase, status, and completion text.
• FND_CONCURRENT_PROGRAMS — joined on PROGRAM_ID and PROGRAM_APPLICATION_ID to resolve program names.
• PA_DISTRIBUTE_LABOR_COSTS / PA_OVERTIME_CALC processes — the parent and spawned processes that produce and consume these rows.
• FND_USER — joined on CREATED_BY or LAST_UPDATED_BY for audit attribution.

Because the object is a leaf with no child references, integrity is maintained by the parent process rather than by declarative foreign keys.