Results for “step_history_id”

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

Overview

PQH_SS_VALUE_HISTORY is a table owned by the HR schema and belongs to the PQH — Public Sector HR product family within Oracle E-Business Suite (validated across 12.1.1 and 12.2.2). Its documented description, "Self Service Transaction History – Item Value History," indicates that it stores the historical, per-item value snapshots associated with self-service transactions. In practice, the table functions as an audit and change-tracking store: whenever an item value within a self-service transaction (for example, a position, assignment, or budget-related value entered through a Public Sector HR flow) is created, modified, or approved, a row is written here to preserve the value as it existed at that point in the transaction lifecycle.

The ETRM metadata classifies this object heuristically as standalone under the Data Vault perspective. In dimensional or Data Vault modeling terms, this suggests treatment as a satellite-style history table: it records descriptive attributes (the value, its datatype, and its prior state) keyed to the parent transaction value, rather than acting as a pure hub or a link between two business entities. It carries its own business-key composite through the unique index, with a single foreign key to the transaction value parent.

Key Information Stored

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

• TRANSACTION_VALUE_ID — Foreign key to HR_API_TRANSACTION_VALUES; identifies the parent transaction value whose history is being recorded. It also participates in the primary key and the unique business key.
• STEP_HISTORY_ID and APPROVAL_HISTORY_ID — Together with TRANSACTION_VALUE_ID, these form the composite primary key PQH_SS_VALUE_HISTORY_PK and the unique business-key candidate, tying each history row to a specific step and approval event.
• NAME — The name of the item/value being tracked.
• VALUE — The current or resulting value of the item at the time the history row was written.
• ORIGINAL_VALUE — The prior value before the change, enabling before/after comparisons.
• DATATYPE — The datatype of the stored value, indicating how VALUE and ORIGINAL_VALUE should be interpreted.
• CREATED_BY, CREATION_DATE — Standard audit columns recording who created the history row and when.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit trail columns for the most recent modification.

The surrogate/business key is composite rather than a single generated identifier; the unique index PQH_SS_VALUE_HISTORY_PK on (TRANSACTION_VALUE_ID, STEP_HISTORY_ID, APPROVAL_HISTORY_ID) is the authoritative uniqueness constraint.

Common Use Cases and Queries

Typical uses center on audit reporting and value reconciliation. Analysts retrieve the full change history of a transaction value, compare ORIGINAL_VALUE to VALUE to detect deltas, and drive approval or step-level reporting. A representative pattern:

• Joining to HR_API_TRANSACTION_VALUES on TRANSACTION_VALUE_ID to obtain the parent transaction context.
• Filtering by STEP_HISTORY_ID or APPROVAL_HISTORY_ID to isolate values captured at a given workflow step or approval action.
• Filtering by NAME or DATATYPE to report on specific item attributes.
• Constructing before/after comparisons using VALUE versus ORIGINAL_VALUE, and ordering by CREATION_DATE for chronological trends.

Sample SQL: SELECT v.NAME, h.VALUE, h.ORIGINAL_VALUE, h.CREATION_DATE FROM hr.pqh_ss_value_history h JOIN hr.hr_api_transaction_values v ON v.transaction_value_id = h.transaction_value_id WHERE h.approval_history_id = :approval_id ORDER BY h.creation_date;

Related Objects

The documented foreign-key relationship anchors this table to its parent:

• HR_API_TRANSACTION_VALUES — referenced via PQH_SS_VALUE_HISTORY.TRANSACTION_VALUE_ID; the primary parent object supplying transaction value context.
• The step and approval history objects implied by STEP_HISTORY_ID and APPROVAL_HISTORY_ID, which supply the workflow-step and approval-event context for each history row.
• Self-service transaction APIs in the PQH/Public Sector HR flows that write rows here as items are created, changed, or approved.
• Standard EBS audit reporting views and concurrent programs that surface item value change history for compliance and troubleshooting.