drop function if exists sp_MtrSchetLoadisAdditional_Bulk_3_3;
drop procedure if exists sp_MtrSchetLoadisAdditional_Bulk_3_3;
create procedure sp_MtrSchetLoadisAdditional_Bulk_3_3
(
	"SchetID" uuid default null::uuid
)
language plpgsql
as $procedure$
begin
	insert into ies.t_schet_sluch_sank (schetsluchsankid, schetsluchaccomplished, s_code, s_sum, s_tip, s_osn, s_com, s_ist, date_act, num_act, schetsluch)
		select 	
			sank."SchetSluchSankID" as schetsluchsankid,
			zsl.schetsluchaccomplishedid as schetsluchaccomplished,
			sank."S_CODE" as s_code,
			sank."S_SUM" as s_sum,
			sank."S_TIP" as s_tip,
			(select f014.f014denyreasonid from ies.t_f014_deny_reason f014 where f014.kod = sank."S_OSN"
														and f014.datebeg <= coalesce(sank."DATE_ACT", zsl.date_z_2)
														and (f014.dateend is null or f014.dateend >= coalesce(sank."DATE_ACT", zsl.date_z_2)) 
														limit 1) as s_osn,
			sank."S_COM" as s_com,
			sank."S_IST" as s_ist,
			sank."DATE_ACT" as date_act,
			sank."NUM_ACT" as num_act,
			null as schetsluch
			from vcscheme."TEMP_SANK_MTR" sank
			join ies.t_schet_sluch_accomplished zsl on zsl.idcase = sank."IDCASE"
			join ies.t_schet_zap sz on sz.schetzapid = zsl.schetzap
			where sz.schet = "SchetID"
				and not exists (select * from ies.t_schet_sluch_sank sss where sss.s_code = sank."S_CODE" and sss.schetsluchaccomplished = zsl.schetsluchaccomplishedid);
			
	insert into ies.t_schet_sluch_sank_exp (schetsluchsankexpid, schetsluchsank, f004expert)
		select 
			gen_random_uuid() as schetsluchsankexpid,
			sss.schetsluchsankid as schetsluchsank,
			codeexp."CODE_EXP" as f004expert
			from vcscheme."TEMP_CODE_EXP_MTR" codeexp
			join vcscheme."TEMP_SANK_MTR" sank on sank."IDCASE" = codeexp."IDCASE" and sank."S_CODE" = codeexp."S_CODE"
			join ies.t_schet_sluch_sank sss on sank."SchetSluchSankID" = sss.schetsluchsankid
			join ies.t_schet_sluch_accomplished zsl on zsl.schetsluchaccomplishedid = sss.schetsluchaccomplished
			join ies.t_schet_zap sz on sz.schetzapid = zsl.schetzap
			where sz.schet = "SchetID"
				and not exists (select * from ies.t_schet_sluch_sank_exp see where see.f004expert = codeexp."CODE_EXP" and sss.schetsluchsankid = see.schetsluchsankexpid);
end;
$procedure$;