drop function if exists staging2.fn_IHTHSL_RUN;
CREATE OR REPLACE FUNCTION staging2.fn_IHTHSL_RUN()
   RETURNS void AS $$
   declare
   	f record;
    w_file_syspk int;
    status text;
   begin

/****************************************************************
******* ForeWarn  Licensing and / or usage Terms and Conditions  ******
ForeWarn. The NextGen Insights Solution Platform

Copyright  2021 COMPEGENCE. All Rights Reserved

ForeWarn is a product of COMPEGENCE. 
To be used only with a valid license from COMPEGENCE

www.compegence.com  info@compegence.com
****************************************************************/

   for f in select file_syspk from fw_core.fw_jobctl_file_runschedule 
   			where end_status like 'stg1-completed' and file_mnemonic = 'IHTHSL'  order by file_syspk
    loop 
    select f.file_syspk into w_file_syspk;
    perform fw_core.fn_get_function_exception('staging2.fn_IHTHSL_HSL_Block(20,1,''IHTHSL'',''IHTHSL_HSL'','||w_file_syspk||')');
    select e.status from fw_core.db_run_status e where file_syspk = w_file_syspk and file_sheet_mnemonic ='IHTHSL_HSL' order by error_timestamp desc into status;
    if status = 'success' then
perform fw_core.fn_get_function_exception('staging2.fn_IHTHSL_HSL_TRX(20,1,''IHTHSL'',''IHTHSL_HSL'','||w_file_syspk||')');
 end if;
        end loop;
   
end;
$$  LANGUAGE plpgsql;