SQL Query to find scheduled HCM Extracts
SQL Query to find scheduled HCM Extracts

 1,102 total views

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.

Want to learn Fusion Technical tools? Checkout this article

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 desc

Tip: 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.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply