You are currently viewing SQL Query to fetch HCM Data Loader Load Summary
SQL Query to fetch HCM Data Loader Load Summary
- SQL Query to fetch HCM Data Loader Load Summary
Total Visits: 11

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.