sql to get the HCM extract failure list for specific dates

Summary:

Need to create report which gives extract failure details.

Code Snippet (add any code snippets that support your topic, if applicable):

Im trying below sql to get the extract failure details, but its not returning correct data.

select * from (
select
pfi.instance_name
,(SELECT base_flow_name FROM pay_flows_vl WHERE base_flow_id = pfi.base_flow_id) flow_name
,to_char(fti.SCHEDULED_DATE,'MM/DD/YYYY') Current_Schedule_Date
,(select to_char(min(fti1.CREATION_DATE),'MM/DD/YYYY') from pay_flow_instances pfi1, PAY_FLOW_TASK_INSTANCES fti1
where pfi1.instance_name = NVL(trim(substr(pfi.instance_name,1,instr(pfi.instance_name,':',1)-1)),pfi.instance_name)