Search Results pa_txn_upgrade_temp

Overview

PA_TXN_UPGRADE_TEMP is a staging and transformation table in the Oracle Projects (PA) schema, used during the Oracle E-Business Suite upgrade and migration cycle to consolidate and re-map project transaction data. It supports the upgrade of project transaction records—expenditures, commitments, and related cost distributions—from legacy release structures into the target 12.1.1 or 12.2.2 data model. Because upgrade processing frequently requires intermediate storage of mapping keys before final validation and commit, this table exists as a transient work area rather than a permanent transactional entity.

Within the documented ETRM 12.2.2 definition the object is owned by PA and contains six physical columns: ROW_ID, PK1_ID, WORKER_ID, PK2_ID, TXN_SRC, and REFERENCE1. A heuristic Data Vault classification mined from the foreign key structure identifies this object as standalone, meaning it participates in no parent-child link relationships of its own. In Data Vault modeling terms, it is therefore best treated as a satellite-like staging artifact rather than a hub or link, and it should not be expected to carry durable business keys of its own.

Key Information Stored

The column set is deliberately narrow and intended for key and source tracking during upgrade processing:

• ROW_ID — the surrogate identifier for each staging row. This is also the only documented foreign key column, referencing CS_SYSTEMS_ALL_B_TEMP.ROW_ID, which ties the staging record to the source-system or upgrade-source registry.
• PK1_ID — the first key component, typically the identifier of the primary project transaction record being converted.
• PK2_ID — the second key component, generally the related detail or distribution-level identifier paired with PK1_ID to form a composite business key.
• WORKER_ID — identifies the concurrent worker or processing unit assigned to a given row, enabling parallel execution and restartability during upgrade.
• TXN_SRC — the transaction source code, indicating which subsystem or legacy origin produced the transaction.
• REFERENCE1 — a free-form reference attribute used to carry the source-side identifier or external reference needed for reconciliation after conversion.

Because the documented schema exposes no unique index on the business columns, PK1_ID and PK2_ID function as business-key candidates only; ROW_ID remains the only guaranteed surrogate key. TXN_SRC and REFERENCE1 are descriptive attributes and should not be relied upon as unique identifiers.

Common Use Cases and Queries

Typical usage arises during upgrade validation rather than steady-state reporting. Consultants reconcile staging rows against the registry of source systems, verify that every legacy transaction received a PK1_ID/PK2_ID mapping, and confirm completeness before the final transaction load. A representative reconciliation query joins the staging table to the source-system registry:

• Row counts grouped by TXN_SRC to confirm all legacy transaction sources were processed:
  SELECT txn_src, COUNT(*) FROM pa.pa_txn_upgrade_temp GROUP BY txn_src;
• Orphan detection, identifying staging rows whose registry entry is missing:
  SELECT t.* FROM pa.pa_txn_upgrade_temp t LEFT JOIN cs_systems_all_b_temp c ON t.row_id = c.row_id WHERE c.row_id IS NULL;
• Worker distribution checks, to balance and troubleshoot parallel upgrade workers:
  SELECT worker_id, COUNT(*) FROM pa.pa_txn_upgrade_temp GROUP BY worker_id;
• Completeness checks confirming every row has both key components populated:
  SELECT COUNT(*) FROM pa.pa_txn_upgrade_temp WHERE pk1_id IS NULL OR pk2_id IS NULL;

Because the table is a temporary upgrade artifact, it is typically emptied or dropped after the upgrade completes; queries against it are generally run only within the upgrade window.

Related Objects

The documented foreign key relationship provides the only confirmed dependency:

• CS_SYSTEMS_ALL_B_TEMP — joined on PA_TXN_UPGRADE_TEMP.ROW_ID = CS_SYSTEMS_ALL_B_TEMP.ROW_ID; supplies the source-system context for each staging row.

By product context, the staging rows ultimately feed the core Projects transaction entities that the upgrade populates, most significantly the project transaction and expenditure tables in the PA schema (for example PA_EXPENDITURES_ALL and its distribution and cost detail tables), along with the project and task definition tables (PA_PROJECTS_ALL, PA_TASKS) that supply the PK1_ID / PK2_ID key components. The upgrade driver concurrent programs and the PA transaction validation APIs consume this table during migration. These additional relationships are inferred from product context and are not documented as foreign keys on PA_TXN_UPGRADE_TEMP itself.