You are currently viewing SQL Query to fetch HCM Data Loader Load Summary
SQL Query to fetch HCM Data Loader Load Summary

In this article, we will look into how to fetch the HCM Data Loader load summary details.

We do the data uploads using HCM Data Loader/Spreadsheet Loader and we can check the status on the Import and Load data screen under Data Exchange.

SQL Query for HDL Summary:

SELECT ds.request_id, 
               ds.ucm_content_id, 
               ds.data_set_name, 
               Round(( Extract(day FROM( Nvl(hst.processend, sysdate) - 
                                         hst.processstart )) * 
                       60 * 24 
                       + 
                               Extract(hour FROM( Nvl(hst.processend, sysdate) - 
                                                  hst.processstart )) 
                               * 
                               60 + Extract(minute FROM( Nvl(hst.processend, 
                                                         sysdate) - 
                                                         hst.processstart )) + 
                       Extract( 
                             second FROM( Nvl (hst.processend, sysdate) - 
                                          hst.processstart )) / 
                       60 
                     ), 3) 
               "ProcessTime", 
               To_char(ds.creation_date, 'DD-MON-YYYY') 
               creation_date, 
               ds.created_by "Created By", 
               Substr(hst.executinginstance, 54, 23) 
                      "ESS_DISPATCHER", 
               ds.imported_status, 
               ds.loaded_status, 
               ds.import_lines_success_count, 
               ds.import_lines_error_count, 
               ds.import_lines_total_count, 
               ds.import_success_count, 
               ds.import_error_count, 
               ds.loaded_count, 
               ds.error_count, 
               (SELECT PAR.param_value 
                FROM   hrc_dl_process_params par 
                WHERE  ds.request_id = par.request_id 
                       AND par.param_name = 'LOAD_MULTI_THREAD_NUMBER') 
                      "Threads for Load", 
               (SELECT PAR1.param_value 
                FROM   hrc_dl_process_params par1 
                WHERE  ds.request_id = par1.request_id 
                       AND par1.param_name = 'ORA_IMPORT_MULTITHREAD_NUMBER') 
                      "Threads for Import", 
               (SELECT PAR3.param_value 
                FROM   hrc_dl_process_params par3 
                WHERE  ds.request_id = par3.request_id 
                       AND par3.param_name = 'LOAD_CHUNK_SIZE') 
                      "Load Group Size" 
        FROM   hrc_dl_data_sets ds, 
               fusion_ora_ess.request_history hst 
        WHERE  hst.requestid = ds.request_id 
        --AND        ds.ucm_content_Id  IN (nvl(:p_ucm_cid, ds.ucm_content_Id))
        ORDER  BY ds.creation_date DESC

Sample output will look like:

image 19 1024x486 - SQL Query to fetch HCM Data Loader Load Summary
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.