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
--,ill.logical_line_id
,ill.validated_loaded_status
,ids.data_set_name
,ill.imported_status
,ids.request_id
,ill.chunk_id
,ill.ui_user_key
--,ill.data_set_bus_obj_id
,SUBSTR(hdfl.TEXT, INSTR(hdfl.TEXT, '|', 1, 1) + 1, INSTR(hdfl.TEXT, '|', 1, 2) - INSTR(hdfl.TEXT, '|', 1, 1) - 1) BusinessObject
,ill.source_system_owner
,ill.source_system_id
--,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.
