Results for “effective_date_option”

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

Overview

The PQH_SS_APPROVAL_HISTORY table resides in the HR schema and belongs to the PQH — Public Sector HR product family within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the approval audit trail for self-service transactions routed through Oracle Approval Management (AME) or the standard workflow approval engine. Each row represents a discrete approval action — submission, approval, rejection, or return — applied to a transactional record, thereby capturing the "who, what, and when" of the approval lifecycle. This makes the table indispensable for audit reporting, self-service diagnostics, and reconstructing the sequence of events that led to a transaction's final state.

From a data modeling perspective, the FK structure is satellite-leaning. The table records descriptive attributes (action type, comment, timestamps) keyed to a parent transaction history entity, a pattern consistent with a Data Vault satellite attached to a transaction hub. It is a transactional history table rather than a master reference table.

Key Information Stored

The table is composed of 17 columns. The most significant columns include:

• APPROVAL_HISTORY_ID — surrogate primary key uniquely identifying each approval event.
• TRANSACTION_HISTORY_ID — foreign key linking the approval action to the parent self-service transaction (references AR_TRANSACTION_HISTORY_ALL). This is a strong business-key candidate for joining back to the transaction record.
• TRANSACTION_EFFECTIVE_DATE — the effective date of the underlying transaction at the time of the approval action.
• ACTION — the approval action taken (e.g., APPROVE, REJECT, RETURN).
• USER_NAME — the approver or actor, referencing FND_USER.
• TRANSACTION_ITEM_TYPE and TRANSACTION_ITEM_KEY — identify the item type and key subject to approval.
• EFFECTIVE_DATE_OPTION — controls dating behavior of the action.
• USER_COMMENT — free-text justification supplied by the approver.
• NOTIFICATION_ID — references the workflow notification generated for the action.
• ORIG_SYSTEM and ORIG_SYSTEM_ID — identify the source system, with ORIG_SYSTEM_ID referencing HZ_ORIG_SYSTEMS_B.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit WHO columns populated by the EBS framework.

Common Use Cases and Queries

Typical scenarios include auditing a self-service transaction's full approval chain, reporting on approver turnaround times, and diagnosing stalled or rejected workflows. A representative query retrieving approval actions for a specific transaction:

SELECT approval_history_id, transaction_history_id, action, user_name,
  user_comment, creation_date
FROM hr.pqh_ss_approval_history
WHERE transaction_history_id = :p_history_id
ORDER BY creation_date;

Reporting on approver volume by action:

SELECT user_name, action, COUNT(*)
FROM hr.pqh_ss_approval_history
WHERE creation_date BETWEEN :start_dt AND :end_dt
GROUP BY user_name, action;

Joining to FND_USER enriches the report with the approver's display name, while joining to the parent transaction history reconstructs the full lifecycle of the document under approval.

Related Objects

• FND_USER — joined via PQH_SS_APPROVAL_HISTORY.USER_NAME to resolve approver identities.
• AR_TRANSACTION_HISTORY_ALL — joined via TRANSACTION_HISTORY_ID to reconcile the parent transaction record.
• HZ_ORIG_SYSTEMS_B — joined via ORIG_SYSTEM_ID to resolve source system registration.
• WF_NOTIFICATIONS — referenced indirectly through NOTIFICATION_ID for workflow detail.
• PQH_SS_TRANSACTION_HISTORY — the principal parent history table supplying transaction context.

Together these objects support end-to-end reporting on the self-service approval lifecycle across the HR and Public Sector HR modules.