CREATE OR REPLACE FUNCTION sp_deleteschetzaps("SchetID" uuid)
RETURNS void AS
$BODY$
Begin
	-- удаление записей	
	delete from akuz.T_SCHET_SLUCH_LINK_F014
	where SchetSluch in
	(
		select ss.SchetSluchID from akuz.T_SCHET_SLUCH ss
		inner join 
			akuz.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
	);

	delete from AKUZ.T_SCHET_LS_116 sls116 
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss 
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = sls116.SchetSluch116
	);

	delete from AKUZ.T_SCHET_MUR_92 mur92
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss 
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = mur92.SchetSluch92
	);

	delete from AKUZ.T_SCHET_SLUCH_LINK_F014 suF014
	where exists
	(
		select* from AKUZ.T_SCHET_USL su 
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = su.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and su.SchetUslID = suF014.SchetUsl
	);
		
	delete from AKUZ.T_SCHET_SLUCH_LINK_F014 ssF014
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = ssF014.SchetSluch
	);

	delete from AKUZ.T_SCHET_NAPR suNapr
	where exists
	(
		select* from AKUZ.T_SCHET_USL su 
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = su.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and su.SchetUslID = suNapr.SchetUsl
	);
		
	delete from AKUZ.T_SCHET_NAPR suNapr
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = suNapr.SchetSluch
	);

	delete from AKUZ.T_SCHET_LEK_PR lek_rp
	where exists
	(
		select* from AKUZ.T_SCHET_ONK_USL onk_usl
		inner join 
			AKUZ.T_SCHET_USL su 
			on su.SchetUslID = onk_usl.SchetUsl
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = su.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and lek_rp.SchetOnkUsl = onk_usl.SchetOnkUslID
	);

	delete from AKUZ.T_SCHET_ONK_USL suOnk
	where exists
	(
		select* from AKUZ.T_SCHET_USL su
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = su.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and su.SchetUslID = suOnk.SchetUsl
	);
	
	delete from AKUZ.T_SCHET_USL_92 su92
	where exists
	(
		select* from AKUZ.T_SCHET_USL su
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = su.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and su.SchetUslID = su92.SchetUslID
	);
	
	delete from akuz.T_SCHET_USL_82
	where SchetUslId in 
	(
		select su.SchetUslID 
		from akuz.T_SCHET_USL su
		inner join 
			akuz.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = su.SchetSluch
		inner join 
			akuz.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
	);

	delete from AKUZ.T_SCHET_USL_116 su116
	where exists
	(
		select* from AKUZ.T_SCHET_USL su
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = su.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and su.SchetUslID = su116.SchetUslID
	);
	
	delete from AKUZ.T_SCHET_USL_66 su66
	where exists
	(
		select* from AKUZ.T_SCHET_USL su 
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = su.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and su.SchetUslID = su66.SchetUslID
	);
	
	delete from AKUZ.T_SCHET_USL_60 su60
	where exists
	(
		select* from AKUZ.T_SCHET_USL su 
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = su.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and su.SchetUslID = su60.SchetUslID
	);

	delete from AKUZ.T_SCHET_MED_DEV sumd
	where exists
	(
		select* from AKUZ.T_SCHET_USL su 
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = su.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and su.SchetUslID = sumd.SchetUsl
	);
	
	delete from AKUZ.T_SCHET_MR_USL_N mrn
	where exists
	(
		select* from AKUZ.T_SCHET_USL su 
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = su.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and su.SchetUslID = mrn.SchetUsl
	);
	
	delete from akuz.T_SCHET_USL
	where SchetSluch in
	(
		select ss.SchetSluchID 
		from akuz.T_SCHET_SLUCH ss
		inner join 
			akuz.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
	);
	
	delete from akuz.T_SCHET_LS 
	where  DvSchetSluch16 in 
	(
		select ss.SchetSluchID 
		from akuz.T_SCHET_SLUCH ss
		inner join 
			akuz.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
	);

	delete from AKUZ.T_SCHET_LS_82 ls82 
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss 
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = ls82.SchetSluch82
	);
	
	delete from AKUZ.T_COMP_USL сu
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss 
		inner join 
			AKUZ.T_SCHET_ZAP sz on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = сu.SchetSluch92
	);
	
	delete from AKUZ.T_SCHET_LS_92 ls92
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss 
		inner join 
			AKUZ.T_SCHET_ZAP sz on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = ls92.SchetSluch92
	);

	delete from AKUZ.T_SCHET_KSLP kslp 
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss 
		inner join 
			AKUZ.T_SCHET_ZAP sz on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = kslp.SchetSluch
	);

	delete from AKUZ.T_SCHET_SL_KOEF ksg_k
	where exists
	(
		select* from AKUZ.T_SCHET_KSG_KPG kslp
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = kslp.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and kslp.SchetKsgKpgID = ksg_k.SchetKsgKpg
	);

	delete from AKUZ.T_SCHET_KSG_KPG ksg 
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = ksg.SchetSluch
	);

	delete from AKUZ.T_SCHET_LEK_PR lek_rp
	where exists
	(
		select* from AKUZ.T_SCHET_ONK_USL onk_usl
		inner join 
			AKUZ.T_SCHET_ONK_SL onk_sl 
			on onk_sl.SchetOnkSlID = onk_usl.SchetOnkSl
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = onk_sl.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and lek_rp.SchetOnkUsl = onk_usl.SchetOnkUslID
	);

	delete from AKUZ.T_SCHET_ONK_USL onk_usl
	where exists
	(
		select* from AKUZ.T_SCHET_ONK_SL onk_sl
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = onk_sl.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and onk_sl.SchetOnkSlID = onk_usl.SchetOnkSl
	);

	delete from AKUZ.T_SCHET_B_DIAG onk_b_diag
	where exists
	(
		select* from AKUZ.T_SCHET_ONK_SL onk_sl
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = onk_sl.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and onk_sl.SchetOnkSlID = onk_b_diag.SchetOnkSl
	);

	delete from AKUZ.T_SCHET_B_PROT onk_b_prot
	where exists
	(
		select* from AKUZ.T_SCHET_ONK_SL onk_sl
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = onk_sl.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and onk_sl.SchetOnkSlID = onk_b_prot.SchetOnkSl
	);
		
	delete from AKUZ.T_SCHET_CONS cons
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = cons.SchetSluch
	);

	delete from AKUZ.T_SCHET_ONK_SL onk_sl 
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = onk_sl.SchetSluch
	);
	
	delete from AKUZ.T_SCHET_NAZ naz 
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss 
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = naz.SchetSluch
	);

	delete from AKUZ.T_SCHET_SANK sank
	where exists
	(
		select* from AKUZ.T_SCHET_SLUCH ss 
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = sank.SchetSluch
	);

	delete from AKUZ.T_SCHET_LEK_DOSE_SL ld
	where exists
	(
		select* 
		from AKUZ.T_SCHET_LEK_PR_SL lp
		inner join 
			AKUZ.T_SCHET_SLUCH ss 
			on ss.SchetSluchID = lp.SchetSluch
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ld.LekPrSl = lp.SchetLekPrSlID
	);

	delete from AKUZ.T_SCHET_LEK_PR_SL lp
	where exists
	(
		select* 
		from AKUZ.T_SCHET_SLUCH ss 			
		inner join 
			AKUZ.T_SCHET_ZAP sz 
			on sz.SchetZapID = ss.SchetZap
		where sz.Schet = "SchetID"
		and ss.SchetSluchID = lp.SchetSluch
	);
	
	delete from AKUZ.T_SCHET_FILE_SLUCH sfs where sfs.Schet = "SchetID";

	Drop Table If Exists tss;
	Create Temp Table tss
	as
	select ss.SchetSluchID as ID
	from akuz.T_SCHET_SLUCH ss
	inner join 
		akuz.T_SCHET_ZAP sz 
		on sz.SchetZapID = ss.SchetZap
	where sz.Schet = "SchetID";

	delete from akuz.T_DV_SCHET_SLUCH_73 where SchetSluchID in (select id from tss);
	delete from akuz.T_DV_SCHET_SLUCH_16 where SchetSluchID in (select id from tss);
	delete from akuz.T_MR_SCHET_SLUCH_16 where SchetSluchID in (select id from tss);
	delete from akuz.T_XR_SCHET_SLUCH_16 where SchetSluchID in (select id from tss);
	delete from akuz.T_PD_SCHET_SLUCH_16 where SchetSluchID in (select id from tss);
	delete from akuz.T_PD_SCHET_SLUCH_73 where SchetSluchID in (select id from tss);
	delete from akuz.T_EB_SCHET_SLUCH_16 where SchetSluchID in (select id from tss);
	delete from akuz.T_SCHET_SLUCH_03 where SchetSluchID in (select id from tss);
	delete from akuz.T_SCHET_SLUCH_66 where SchetSluchID in (select id from tss);
	delete from akuz.T_SCHET_SLUCH_44 where SchetSluchID in (select id from tss);
	delete from akuz.T_SCHET_SLUCH_60 where SchetSluchID in (select id from tss);
	delete from akuz.T_SCHET_SLUCH_83 where SchetSluchID in (select id from tss);
	delete from akuz.T_SCHET_SLUCH_82 where SchetSluchID in (select id from tss);
	delete from akuz.T_SCHET_SLUCH_92 where SchetSluchID in (select id from tss);
	delete from akuz.T_SCHET_SLUCH_116 where SchetSluchID in (select id from tss);
	delete from akuz.T_SCHET_SLUCH where SchetSluchID in (select id from tss);

	truncate table tss;

	insert into tss(ID)
	select z_sl.SchetZSlID 
	from AKUZ.T_SCHET_Z_SL z_sl
	inner join 
		AKUZ.T_SCHET_ZAP sz 
		on sz.SchetZapID = z_sl.SchetZap
		where sz.Schet = "SchetID";

	delete from AKUZ.T_SCHET_Z_SL_03 where SchetZSlID in (select id from tss);
	delete from AKUZ.T_SCHET_Z_SL_92 where SchetZSlID in (select id from tss);
	delete from AKUZ.T_SCHET_Z_SL_173 where SchetZSlID in (select id from tss);
	delete from AKUZ.T_SCHET_Z_SL_116 where SchetZSlID in (select id from tss);
	delete from AKUZ.T_SCHET_Z_SL_44 where SchetZSlID in (select id from tss);
	delete from AKUZ.T_SCHET_Z_SL where SchetZSlID in (select id from tss);
	
	delete from AKUZ.T_SCHET_ZAP_92 sz92
	where exists(select * from AKUZ.T_SCHET_ZAP sz where sz.SchetZapID = sz92.SchetZapID and sz.Schet = "SchetID");
	
	delete from akuz.T_SCHET_ZAP where Schet = "SchetID";
	
	delete from akuz.T_SCHET_DOKT where Schet = "SchetID";

	delete from akuz.T_SCHET_PACK where Schet = "SchetID";

	delete from akuz.T_SCHET_QUEUE_ERROR where Schet = "SchetID";
End;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_deleteschetzaps(uuid)
  OWNER TO postgres;



