HCM Extract – Query to get last extract run date

While creating an HCM extract based on a BIP report, there is a common requirement where the BIP should retrieve “Changed Only” data. This can be achieved using a BIP report data model which is based on globalReportsDataModel (/shared/Human Capital Management/Payroll/Data Model).

Use the below query in the data set to extract last run date of HCM extract and based on this date create a filter on last_update_date of the standard table from which we are extracting the data.

WITH pLastRunDate AS
(SELECT MAX(pfi.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM pay_flows pf
,pay_flow_instances pfi
WHERE 1=1
AND pf.base_flow_id = pfi.base_flow_id
AND pfi.LAST_UPDATE_DATE < sysdate
AND pf.base_flow_name ='XX Custom Integration Extract' -- - give the name of your HCM extract
AND pfi.status = 'COMPLETED'
)