In this article we will look into the query to find out all scheduled HCM Extracts from backend tables. This query would provide all scheduled extracts from the instance irrespective of who scheduled it, although we might not have access to check it on the application due to security profiles.
This query also provides information of the current run parameters for extract, date on which the extract is first scheduled and who scheduled the extract, extract name and the scheduled flow instance name.
SQL Query for scheduled HCM Extracts:
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)
and pfi1.FLOW_INSTANCE_ID = fti1.FLOW_INSTANCE_ID) FIRST_SCHEDULE_DATE
,fti.CREATED_BY Schedule_Created_By
,fti.status CURRENT_SCHEDULE_STATUS
, (select listagg(fp.PARAMETER_NAME || '=' || pv.flow_param_value, ',') within group ( order by fp.PARAMETER_NAME)
FROM pay_flow_instances fi
,pay_flows_vl f
,pay_flow_parameters_vl fp
,pay_flow_param_values pv
WHERE f.base_flow_id = fi.base_flow_id
AND fp.base_flow_id = f.base_flow_id
AND pv.flow_instance_id = fi.flow_instance_id
AND fp.base_flow_parameter_id = pv.base_flow_parameter_id
and FLOW_PARAM_VALUE is not null
and fi.instance_name = pfi.instance_name) Flow_parameters_Latest_Run
from PAY_FLOW_TASK_INSTANCES fti
,pay_flow_tasks_vl ft
,pay_flow_instances pfi
where fti.status = 'SCHEDULED'
and pfi.status = 'SCHEDULED'
and fti.base_flow_task_id = ft.base_flow_task_id
and pfi.FLOW_INSTANCE_ID = fti.FLOW_INSTANCE_ID
and fti.SCHEDULED_DATE IS NOT NULL
) order by flow_name, Current_Schedule_Date descTip: This query will be handy to find out the current scheduled integrations when there is an issue and want to find out who scheduled it so you can reach out to them for help.
