Progress with Redwood: Redwood Resources for Oracle Cloud HCM - Go Here
fa_fusion_soainfra.wftaskhistory returns multi tasknumber after join with objectid of hrc_txn_header
select wftask.taskid,
wftask.tasknumber,
hth.transaction_id,
irv.requisition_number,
ic.candidate_number,
wftask_hist.updatedbydisplayname updated_by_display_name,
wftask_hist.ASSIGNEESDISPLAYNAME assignees_display_name,
wftask_hist.state,
wftask_hist.substate,
from hrc_txn_header hth,
hrc_txn_data htd,
fa_fusion_soainfra.wftask wftask,
fa_fusion_soainfra.wftaskhistory wftask_hist
where hth.transaction_id = htd.transaction_id
and htd.status not in ('ABORTED')
and to_char(hth.object_id) = to_char(wftask.identificationkey)
and wftask.taskid = wftask_hist.taskid
and ( wftask_hist.VERSIONREASON in ('TASK_VERSION_REASON_INITIATED','TASK_VERSION_REASON_ROUTED','TASK_VERSION_REASON_REASSIGNED','TASK_VERSION_REASON_INFO_REQUESTED','TASK_VERSION_REASON_INFO_SUBMITTED')