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.

Nitin Maheshwari

Add comment