drop function if exists staging2.run_post_generic_validation;
CREATE OR REPLACE FUNCTION staging2.run_post_generic_validation()
   RETURNS void AS $$
   declare
   	f record;
   	trx_record_count int;
   begin
	   for f in select distinct file_syspk from fw_core.fw_jobctl_file_runschedule 
		where latest_run_schedule_flag = 1 and end_status != 'error' and end_status_note = 'stg1_completed'
   		loop
    	select count(*) from transactional.test_instance where file_syspk = f.file_syspk into trx_record_count;
    	if trx_record_count > 0 then
    		update fw_core.fw_jobctl_runschedule_jobstep set end_status_note = 'generic_post_validation_failed', end_status='error';
    	exit;
    	end if;
    end loop;
end;
$$  LANGUAGE plpgsql;

select staging2.run_post_generic_validation()


drop function if exists staging2.run_trx_post_validation;
CREATE OR REPLACE FUNCTION staging2.run_trx_post_validation()
   RETURNS void AS $$
   declare
   	f record;
   	trx_record_count int;
   begin
	   for f in select distinct file_syspk from fw_core.fw_jobctl_file_runschedule 
		where latest_run_schedule_flag = 1 and end_status != 'error' and end_status_note = 'trx_completed'
   		loop
    	select count(*) from transactional.test_instance where file_syspk = f.file_syspk into trx_record_count;
    	if trx_record_count = 0 then
    		update fw_core.fw_jobctl_runschedule_jobstep set end_status_note = 'trx_post_validation_failed', end_status='error';
    	exit;
    	end if;
    end loop;
end;
$$  LANGUAGE plpgsql;

select staging2.run_trx_post_validation()