How to get the Details of scheduled HCM Extracts Process
Requirement- Most of the time consultants have a requirement to see all the scheduled HCM Extract process and because of missing the roles in UI not able to see the details. In application we have different places to run and view the extract, sometimes that confuse consultants to identify where they can get the complete details. This article will help to get all the details using BI Publisher.
- To view the HCM Extract schedule using UI we have to go to DATA Exchange > View Extract Results
- To check the extract schedule we can select the schedule check box to get the details.
- Now if user is not having the “Human Capital Management Integration Specialist” will not able to see the DATA_EXCHANGE area to view these details, that’s where we can use the below query or Report to get the details.
Below query can be used to get the HCM schedule extracts.
/*SQL Query for scheduled HCM Extracts:*/
SELECTpfv.base_flow_name Base_flow_name
,
pfi.instance_name,
(SELECTTo_char(Min(fti1.creation_date),'DD-MON-YYYY')
FROM pay_flow_task_instancesfti1
WHERE pfi.root_flow_instance_id=fti1.flow_instance_id)
FIRST_SCHEDULE_DATE,
To_char(fti.scheduled_date,'DD-MON-YYYY')
Current_Schedule_Date,
fti.status
CURRENT_SCHEDULE_STATUS,
(SELECTListagg(fp.parameter_name
|| '='
|| pv.flow_param_value,',')
withinGROUP(ORDERBYfp.parameter_name)
FROM pay_flow_parameters_vlfp,
pay_flow_param_valuespv
WHERE 1 = 1
ANDfp.base_flow_id=pfv.base_flow_id
ANDpv.flow_instance_id=pfi.flow_instance_id
ANDfp.base_flow_parameter_id=pv.base_flow_parameter_id
ANDflow_param_valueISNOTNULL
ANDpfi.instance_name=pfi.instance_name)
Flow_parameters_Latest_Run,
fti.created_by
Schedule_Created_By
FROM pay_flow_task_instancesfti,
pay_flow_tasks_vlft,
pay_flow_instancespfi,
pay_flows_vlpfv
WHERE 1 = 1
ANDpfv.base_flow_id=pfi.base_flow_id
ANDfti.base_flow_task_id=ft.base_flow_task_id
ANDpfi.flow_instance_id=fti.flow_instance_id
ANDfti.scheduled_dateISNOTNULL
ANDfti.status='SCHEDULED'
ANDpfi.status='SCHEDULED'
ORDER BYflow_nameDESC;
Note- Using above query prepare Data Model and Generate system generated report.