You are currently viewing SQL Query to pull the HDL Loaded and Error Records
SQL Query to pull the HDL Loaded and Error Records

In this article, we will look into pulling the HDL loaded and error records.

When we invoke the HDL, it internally divides the batch into chunks and processes them. If for some reason one chunk is struck or failed and if we need to check the employees processed within the specific chunk, there isn’t any functionality currently to look at that info.

We can pull this info from the HDL backend tables if the batch is not purged.

SQL Query to pull loaded and error records:

SELECT ids.ucm_content_id
	,SUBSTR(hdfl.TEXT, INSTR(hdfl.TEXT, '|', 1, 1) + 1, INSTR(hdfl.TEXT, '|', 1, 2) - INSTR(hdfl.TEXT, '|', 1, 1) - 1) BusinessObject
--,hdfl.text	-- to pull the actual data line records
FROM fusion.hrc_dl_logical_lines ill
	,fusion.hrc_dl_data_set_bus_objs idsbo
	,fusion.hrc_dl_data_sets ids
	,fusion.hrc_dl_file_rows hdfr
	,fusion.hrc_dl_file_lines hdfl
WHERE ids.data_set_id = idsbo.data_set_id
	AND idsbo.data_set_bus_obj_id = ill.data_set_bus_obj_id
	--AND     ill.validated_loaded_status IN ('LOADED_ERROR','ERROR','UNPROCESSED') -- to pull only error or unprocessed records
	--AND ill.parent_logical_line_id = 0 -- to pull only parent records
	AND hdfr.logical_line_id = ill.logical_line_id
	AND hdfl.line_id = hdfr.line_id
	AND ids.ucm_content_id = 'UCMFA01040123'
ORDER BY ill.chunk_id ASC
	,ill.processing_order ASC

If we only need the parent records like Worker instead of the individual sub objects like personname, personaddress then uncomment the line ill.parent_logical_line_id = 0 and it should only fetch the parent record.