SELECT dp.person_number "Person Number",
n.display_name "Worker Name",
txnh.module_identifier "UI Process",
wft.creator "Requestor",
wft.assigneesdisplayname
||','
||wft.assignees "Current Assignee,User,Type",
wft.assigneddate "Assigned Date",
wft.approvers "Approved By",
wft.title "Notification Title",
wft.tasknumber "Task Number",
txnd.status "HCM Transaction Status",
wft.state "BPM Task State",
txnh.object
FROM fusion.per_all_people_f dp,
fusion.per_person_names_f_v n,
fusion.per_all_assignments_m asg,
fusion.hrc_txn_header txnh,
fusion.hrc_txn_data txnd,
hcm_fusion_soainfra.WFTASK wft
WHERE dp.person_id =n.person_id
AND asg.person_id =n.person_id
AND LENGTH(asg.assignment_type)=1
AND asg.assignment_id =txnh.object_id
AND wft.identificationkey =TO_CHAR(txnh.transaction_id)
AND txnh.object ='PER_ALL_ASSIGNMENTS_M'
AND txnh.transaction_id =txnd.transaction_id
AND sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.effective_latest_change='Y'
AND sysdate BETWEEN dp.effective_start_date AND dp.effective_end_date
AND sysdate BETWEEN n.effective_start_date AND n.effective_end_date
AND txnd.status IN ('PENDING','ERROR')
UNION
SELECT dp.person_number "Person Number",
n.display_name "Worker Name",
txnh.module_identifier "UI Process",
wft.creator "Requestor",
wft.assigneesdisplayname
||','
||wft.assignees "Current Assignee,User,Type",
wft.assigneddate "Assigned Date",
wft.approvers "Approved By",
wft.title "Notification Title",
wft.tasknumber "Task Number",
txnd.status "HCM Transaction Status",
wft.state "BPM Task State",
txnh.object
FROM fusion.per_all_people_f dp,
fusion.per_person_names_f_v n,
fusion.per_all_assignments_m asg,
fusion.hrc_txn_header txnh,
fusion.hrc_txn_data txnd,
hcm_fusion_soainfra.WFTASK wft
WHERE dp.person_id =n.person_id
AND asg.person_id =n.person_id
AND LENGTH(asg.assignment_type)=1
AND asg.period_of_service_id =txnh.object_id
AND wft.identificationkey =TO_CHAR(txnh.transaction_id)
AND txnh.object ='PER_PERIODS_OF_SERVICE'
AND txnh.transaction_id =txnd.transaction_id
AND sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.effective_latest_change='Y'
AND sysdate BETWEEN dp.effective_start_date AND dp.effective_end_date
AND sysdate BETWEEN n.effective_start_date AND n.effective_end_date
AND txnd.status IN ('PENDING','ERROR')
UNION
SELECT 'Create Worker' "Person Number",
'Create Worker' "Worker Name",
txnh.module_identifier "UI Process",
wft.creator "Requestor",
wft.assigneesdisplayname
||','
||wft.assignees "Current Assignee,User,Type",
wft.assigneddate "Assigned Date",
wft.approvers "Approved By",
wft.title "Notification Title",
wft.tasknumber "Task Number",
txnd.status "HCM Transaction Status",
wft.state "BPM Task State",
txnh.object
FROM fusion.hrc_txn_header txnh,
fusion.hrc_txn_data txnd,
hcm_fusion_soainfra.WFTASK wft
WHERE wft.identificationkey =TO_CHAR(txnh.transaction_id)
AND txnh.object ='PER_ALL_PEOPLE_F'
AND txnh.transaction_id =txnd.transaction_id
AND txnd.status IN ('PENDING','ERROR');
