Results for “ssp_temp_affected_rows”

50+ results




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

Overview

SSP_TEMP_AFFECTED_ROWS is a transient staging table owned by the SSP schema within Oracle E-Business Suite. It is documented in the ETRM repository for both release 12.1.1 and 12.2.2 as an Oracle-internal object, with the formal description stating "For internal Oracle use only." Its name indicates that it holds a working set of row identifiers affected by a bulk business process — typically the set of maternity records selected for a downstream operation such as recalculation, status change, or deletion — so that the concurrent program can iterate, lock, and clean up its target rows in a controlled, restartable fashion.

The table is not exposed through any user-facing form or concurrent program parameter, and it carries no seeded or permanent business data. It functions purely as an intra-process workspace. Under a heuristic Data Vault classification derived from its foreign-key structure, SSP_TEMP_AFFECTED_ROWS is modeled as a standalone entity — the metadata records no dependent or referencing relationships, and the single foreign key (MATERNITY_ID) points outward to SSP_MATERNITIES rather than being claimed by another child table. In practice this means the table behaves like a disposable staging set rather than a persistent hub, link, or satellite in a rigorous Data Vault sense.

Key Information Stored

The documented physical schema records four columns, and each is significant to the table's operation:

  • MATERNITY_ID — A foreign key to SSP_MATERNITIES. This is the business-key candidate that identifies the maternity record affected by the current process run. It is the column most commonly used as a join predicate when staging data is merged back to the parent record.
  • PIW_ID — Identifies the associated process information workspace (PIW) instance, correlating the staged rows with the specific execution of the governing SSP process.
  • LOCKED — A flag indicating whether the referenced maternity row has been locked by the process, supporting row-level concurrency control and preventing conflicting updates during execution.
  • P_DELETING — A control flag marking rows as part of a deletion scope, allowing the process to distinguish records scheduled for removal from those retained.

No surrogate primary key, unique index, or additional constraints are documented in the ETRM metadata, which is consistent with a temporary working table. If a surrogate key exists physically, it is not part of the documented definition and should be treated as an internal implementation detail rather than a stable interface.

Common Use Cases and Queries

Because the object is internal, direct querying is generally limited to diagnostics, reconciliation, and performance troubleshooting of SSP concurrent processes. Support engineers may inspect the staged set when a process appears to lock the wrong records or fails to complete:

  • Counting staged rows for a given process workspace: SELECT PIW_ID, COUNT(*) FROM SSP.SSP_TEMP_AFFECTED_ROWS GROUP BY PIW_ID;
  • Identifying locked records: SELECT MATERNITY_ID FROM SSP.SSP_TEMP_AFFECTED_ROWS WHERE LOCKED = 'Y';
  • Detecting stale staging rows left behind after an aborted run, by comparing MATERNITY_ID values against SSP_MATERNITIES.
  • Verifying deletion scope: filtering on P_DELETING to confirm which records a purge program intends to remove.

Standard reporting should not be built on this table, since its contents are transient and may be truncated or repopulated by any subsequent process execution. Any operational reporting should query SSP_MATERNITIES directly and use SSP_TEMP_AFFECTED_ROWS only for reconciliation during incident analysis.

Related Objects

The following objects are the most significant in relation to SSP_TEMP_AFFECTED_ROWS:

  • SSP_MATERNITIES — The parent table joined on SSP_TEMP_AFFECTED_ROWS.MATERNITY_ID = SSP_MATERNITIES.MATERNITY_ID; this is the only documented foreign key.
  • SSP process information workspace tables — The PIW_ID column correlates staged rows with the workspace instance driving the process.
  • SSP concurrent programs and PL/SQL packages — The population, locking, and cleanup routines that insert into and delete from this table are internal to SSP processing logic.
  • SSP maternity-related child entities — Dependent records in the SSP maternity model that must be handled consistently with the staged set.

Given the sparse relationship data, administrators should rely on the SSP product documentation and trace files rather than attempting to infer further dependencies from this table alone.