Approvals are where business controls, compliance, and user experience intersect. When a change request, purchase, or HR action stalls, knowing exactly who it has now, who already acted, and why it failed is critical for unblocking the process, meeting SLAs, and proving auditability.
In Fusion Apps, that trail is split across Transaction Console (HRC_* tables) and SOA workflow (WF* tables).
This guide explains what you can reliably retrieve with SQL (current assignees and history), why future approvers are not queryable from database tables, and provides a production-ready query to extract approver context for troubleshooting and reporting.
Why identify approvers?
- Ensure operational continuity by locating the current approver to unblock time-critical transactions and meet SLAs.
- Satisfy audit and compliance by reconstructing who acted (approve, reject, reassign) and when.
- Accelerate root-cause analysis by correlating Transaction Console errors and SOA workflow tasks.
Why you can’t get future approvers via SQL
Even though BPM Worklist can visualize the remaining approval route, the list of next/future approvers is not stored in a relational table. Oracle states that there is no database table where SQL can be performed to get future approvers and rules—the information is held at SOA level in XML and not exposed for SQL queries (see References).
Bottom line: You can reliably query who has it now and who acted before. You cannot reliably produce a future approver list from SQL. Use BPM Worklist UI, rule simulation, or functional tests for “what‑if” visibility.
What you can query
- Transaction Console (Fusion schema):
- HRC_TXN_HEADER / HRC_TXN_DATA — transaction metadata and payload pointers.
- HRC_TXN_ERROR — fault timestamps and error context.
- FND_CONSOLE_TRANSACTION_INFO & FND_CONSOLE_ISSUE — link transactions to workflow instances and capture base error message and fault payload.
- SOA Workflow (FA_FUSION_SOAINFRA):
- WFTASK — live task: state, outcome, assignees display name, assignment timestamps.
- WFASSIGNEE — per-task assignees, for granular identity.
- WFTASKHISTORY — task action trail (approve, reject, reassign, withdraw).
- Financials (when populated):
- FUN_WF_HISTORY — summary outcome and history by transaction.
SQL: Identify current approvers and the approval trail
The following query joins Transaction Console, SOA workflow task and history, and console issues. It uses ANSI joins and optional filters for module, status, date range, and transaction id.
/* Identify current approvers and approval trail for Fusion transactions */
SELECT
txnh.transaction_id,
txnh.module_identifier,
txnh.subject,
txnh.subject_id,
txnh.object,
txnh.object_id,
txnd.status AS txn_status,
txnd.state AS txn_state,
txnd.created_by,
txnd.creation_date,
txnd.last_update_date,
txnd.last_updated_by,
— Live workflow task (current approver context)
wft.tasknumber,
wft.title AS wf_title,
wft.state AS wf_state, — e.g., ASSIGNED, COMPLETED
wft.outcome AS wf_outcome, — e.g., APPROVE, REJECT
wft.assigneesdisplayname,
wft.assigneddate,
wft.approvers,
wft.compositeinstanceid,
— Per-task assignee details (optional, if granted)
wfa.assignee AS assignee_login, — adjust to your environment
wfa.displayname AS assignee_name, — adjust to your environment
— Task history (who acted, what action, when)
wfh.action AS history_action,
wfh.updateddate AS history_date,
wfh.assignee AS history_actor, — adjust per schema
wfh.comments AS history_comments,
— Console / error context
txne.fault_date,
fci.base_error_message AS error_details,
fci.fault_payload
FROM fusion.hrc_txn_header txnh
JOIN fusion.hrc_txn_data txnd
ON txnd.transaction_id = txnh.transaction_id
LEFT JOIN fusion.hrc_txn_error txne
ON txne.transaction_id = txnh.transaction_id
/* Map to console transaction + issues (error details, payload) */
LEFT JOIN fusion.fnd_console_transaction_info fcti
ON fcti.transaction_id = txnh.transaction_id
LEFT JOIN fusion.fnd_console_issue fci
ON fci.console_transaction_id = fcti.console_transaction_id
AND fci.base_error_message IS NOT NULL
/* Live task for the transaction (identificationkey holds the txn id) */
LEFT JOIN fa_fusion_soainfra.wftask wft
ON TO_CHAR(txnh.transaction_id) = wft.identificationkey
/* Current assignees (granular view) */
LEFT JOIN fa_fusion_soainfra.wfassignee wfa
ON wfa.taskid = wft.taskid
/* All actions taken on this task */
LEFT JOIN fa_fusion_soainfra.wftaskhistory wfh
ON wfh.taskid = wft.taskid
WHERE 1 = 1
/* Optional filters */
AND (:p_module IS NULL OR txnh.module_identifier = :p_module)
AND (:p_status IS NULL OR txnd.status = :p_status)
AND (:p_from_date IS NULL OR txnd.creation_date >= :p_from_date)
AND (:p_to_date IS NULL OR txnd.creation_date < :p_to_date)
AND (:p_transaction_id IS NULL OR txnh.transaction_id = :p_transaction_id)
ORDER BY txnh.transaction_id, wft.tasknumber, wfh.updateddate;
FAQ
- Can I list who comes next in the approval route?
- No—future approvers are computed dynamically by BPM rules and held in SOA XML, not in a SQL-queryable table.
- Can I mirror the “planned route” from BPM Worklist?
- The visualization is generated from SOA/BPM metadata at runtime and is not normalized into Fusion schema tables for SQL reporting.
References
- Oracle MOS Doc ID 2869770.1: Is There a Way to Check the Future Approvers and Rules for In-Progress Approval Workflow?: https://support.oracle.com/knowledge/Oracle%20Cloud/2869770_1.html
- Fusion HCM Knowledgebase: SQL to find pending approval transactions (tables overview and joins): https://fusionhcmknowledgebase.com/2020/06/sql-query-to-find-pending-approval-transactions/
- Oracle Community: Query to get Fusion Workflow Approvals (current assignee via WFTASK + Transaction Console linkage): https://community.oracle.com/customerconnect/discussion/788342/query-to-get-fusion-workflow-approvals
- Oracle Docs: FUN_WF_HISTORY (Financials approval history table): https://docs.oracle.com/en/cloud/saas/financials/25a/oedmf/funwfhistory-10422.html
- Oracle Forums: SOA Infra tables and relationships (WFTASK, WFASSIGNEE, WFTASKHISTORY): https://forums.oracle.com/ords/apexds/post/soa-infra-tables-5988
- Oracle Community: SQL to get current and next/future approvers — limitation confirmed: https://community.oracle.com/customerconnect/discussion/811909/sql-query-to-get-current-and-next-future-approvers-on-job-requisition-and-offer-approvals