42 lines
1.5 KiB
PL/PgSQL
42 lines
1.5 KiB
PL/PgSQL
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() |