SQL Query to get the HDL file from staging tables

Normally when we load the data using HCM Data Loader aka HDL, the File Delete Option would be defaulted to ‘Y’ and the input zip file would be deleted post processing of that file.

However out of my enthusiasm, I searched for options to get that HDL file from staging tables and finally figured out a way to get it using BI Report. Below is the SQL that need to be used on Data Model to get the HDL file as output.

SELECT fl.TEXT
 ,bo.data_file_name
FROM fusion.hrc_dl_file_lines fl
 ,fusion.hrc_dl_data_set_bus_objs bo
 ,fusion.hrc_dl_data_sets ds
WHERE ds.ucm_content_id = 'UCMFA00014838' --Replace this with the actual contentid
 AND fl.data_set_bus_obj_id = bo.data_set_bus_obj_id
 AND ds.data_set_id = bo.data_set_id
ORDER BY fl.seq_num ASC