Search Results hr_wip_transactions_u2

Overview

HR.HR_WIP_TRANSACTIONS is a transaction staging and state-tracking table owned by the HR schema in Oracle E-Business Suite. As documented in ETRM, it "stores the transaction information and state," functioning as a work-in-progress repository for Human Resources transactions that are processed through Oracle Workflow. The table is classified under FND Design Data as PER.HR_WIP_TRANSACTIONS, confirming it belongs to the People (PER) product family, and it resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10.

A critical documented characteristic is that the table is not used after IRC E RUP1 (the IRC European Release Update Pack 1). This means it is a legacy artifact retained for historical data and backward compatibility, not an active transactional component in Oracle EBS 12.1.1 or 12.2.2 processing. Consultants encountering this table should treat it as read-only historical data rather than a target for new development.

The ETRM metadata assigns a heuristic Data Vault classification of standalone, meaning the table references no other database objects via foreign keys. In Data Vault modeling terms, this suggests the table behaves as an isolated hub-like structure keyed on TRANSACTION_ID, with its descriptive attributes (STATE, SUB_STATE, DML_MODE, VO_CACHE) functioning as satellite-style payload. Because no foreign keys are documented, integration with other objects occurs through application logic rather than referential constraints.

Key Information Stored

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

• TRANSACTION_ID — NUMBER(15), the surrogate primary key (HR_WIP_TRANSACTIONS_PK) and the column behind unique index HR_WIP_TRANSACTIONS_U1.
• ITEM_TYPE and ITEM_KEY — the Oracle Workflow item type and item key identifying the workflow instance associated with the transaction. Together these form the business-key candidate enforced by unique index HR_WIP_TRANSACTIONS_U2, making them the strongest natural key in the table.
• STATE and SUB_STATE — the current transaction state and an Auto/Manual sub-state indicator. STATE is part of nonunique index HR_WIP_TRANSACTIONS_N1 and is the primary driver for querying in-flight transactions.
• CREATOR_USER_ID — NUMBER(10), the user who created the transaction; the second indexed column in HR_WIP_TRANSACTIONS_N1.
• FUNCTION_ID — identifier of the function that originated or processes the transaction.
• VO_CACHE — CLOB holding a serialized view object collection; a LOB segment (SYS_IL0000202793C00008$$) supports it in APPS_TS_TX_DATA.
• CONTEXT_DISPLAY_TEXT — VARCHAR2(240) descriptive context for the transaction.
• DML_MODE — indicates whether the transaction represents an INSERT or UPDATE operation.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — the standard WHO audit columns present on all EBS transactional tables.

Common Use Cases and Queries

Because the table predates IRC E RUP1 and is documented as unused thereafter, typical usage is forensic rather than operational. Common scenarios include auditing orphaned or stale transactions, reconstructing what workflow activity occurred, and identifying which users initiated transactions in a given period.

To inspect transactions by state and creator:

• SELECT TRANSACTION_ID, ITEM_TYPE, ITEM_KEY, STATE, SUB_STATE, DML_MODE, CREATOR_USER_ID, CREATION_DATE FROM HR.HR_WIP_TRANSACTIONS WHERE STATE = :state ORDER BY CREATION_DATE DESC;

To locate a transaction by its workflow business key — a pattern that exploits HR_WIP_TRANSACTIONS_U2:

• SELECT TRANSACTION_ID, STATE, FUNCTION_ID, CONTEXT_DISPLAY_TEXT FROM HR.HR_WIP_TRANSACTIONS WHERE ITEM_TYPE = :item_type AND ITEM_KEY = :item_key;

Reporting against the table should generally avoid selecting VO_CACHE unless the serialized payload is genuinely required, since CLOB retrieval is expensive and rarely meaningful outside its originating application context.

Related Objects

The documented dependency information lists no outgoing references and identifies the following as dependent on HR.HR_WIP_TRANSACTIONS:

• APPS.HR_WIP_TRANSACTIONS — the APPS-schema synonym or view through which EBS application code accesses the table; queries in EBS should target this layer rather than the HR base table directly.
• APPS.HR_WIP_TRANSACTIONS_WHO — the WHO-triggered companion object that populates the standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) on DML.

Because the table carries no foreign keys, joins to Oracle Workflow tables such as WF_ITEMS or WF_ITEM_ACTIVITY_STATUSES must be constructed manually on ITEM_TYPE and ITEM_KEY. Similarly, joins to PER_ALL_PEOPLE_F on CREATOR_USER_ID are application-level rather than enforced relationships.