drop procedure if exists public.sp_DeleteSchetZaps;
CREATE PROCEDURE public.sp_DeleteSchetZaps 
	(
		"SchetID" uuid
	)
as $$
begin
	-- Создание временных таблиц T_SCHET_ZAP
create temp table ZapToDelete 
	as
	SELECT SchetZapID
	from AKUZ.T_SCHET_ZAP sz
	where sz.Schet = "SchetID";
create temp table SluchToDelete
	as
	SELECT SchetSluchID
	from AKUZ.T_SCHET_SLUCH SS
    JOIN ZapToDelete STD ON STD.SchetZapID = SS.SchetZap;
create temp table USLToDelete
	as
	select su.SchetUslID
    FROM AKUZ.T_SCHET_USL su
	JOIN SluchToDelete ss on ss.SchetSluchID = su.SchetSluch;
create temp table ZSLToDelete
	as
	SELECT z_sl.SchetZSlID 
	FROM AKUZ.T_SCHET_Z_SL z_sl
	JOIN ZapToDelete STD ON STD.SchetZapID = z_sl.SchetZap;

CREATE INDEX IX_SCHET_ZAP_TMP ON ZapToDelete(SchetZapID);
CREATE INDEX IX_SCHET_SLUCH_TMP ON SluchToDelete(SchetSluchID);
CREATE INDEX IX_SCHET_USL_TMP ON USLToDelete(SchetUslID);
CREATE INDEX IX_SCHET_ZSL_TMP ON ZSLToDelete(SchetZSlID);


-- Delete rows from T_SCHET_LS_116
DELETE FROM AKUZ.T_SCHET_LS_116
WHERE SchetSluch116 IN (
	SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_MUR_92
DELETE FROM AKUZ.T_SCHET_MUR_92
WHERE SchetSluch92 IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_COMP_USL
DELETE FROM AKUZ.T_COMP_USL
WHERE SchetSluch92 IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_MED_PROD_92
DELETE FROM AKUZ.T_SCHET_MED_PROD_92
WHERE SchetSluch92 IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_SLUCH_LINK_F014
DELETE FROM AKUZ.T_SCHET_SLUCH_LINK_F014
WHERE SchetUsl IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_SLUCH_LINK_F014
DELETE FROM AKUZ.T_SCHET_SLUCH_LINK_F014
WHERE SchetSluch IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_NAPR
DELETE FROM AKUZ.T_SCHET_NAPR
WHERE SchetUsl IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_NAPR
DELETE FROM AKUZ.T_SCHET_NAPR
WHERE SchetSluch IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_LEK_PR
DELETE FROM AKUZ.T_SCHET_LEK_PR
WHERE SchetOnkUsl IN (
    SELECT OU.SchetOnkUslID
    FROM AKUZ.T_SCHET_ONK_USL OU
    JOIN USLToDelete usl ON USL.SchetUslID = OU.SchetUsl 
);

-- Delete rows from T_SCHET_ONK_USL
DELETE FROM AKUZ.T_SCHET_ONK_USL
WHERE SchetUsl IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_USL_92
DELETE FROM AKUZ.T_SCHET_USL_92
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_USL_93
DELETE FROM AKUZ.T_SCHET_USL_93
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_USL_94
DELETE FROM AKUZ.T_SCHET_USL_94
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_USL_95
DELETE FROM AKUZ.T_SCHET_USL_95
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_USL_82
DELETE FROM AKUZ.T_SCHET_USL_82
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_USL_116
DELETE FROM AKUZ.T_SCHET_USL_116
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_USL_06
DELETE FROM AKUZ.T_SCHET_USL_06
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_USL_66
DELETE FROM AKUZ.T_SCHET_USL_66
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_USL_60
DELETE FROM AKUZ.T_SCHET_USL_60
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_MED_DEV
DELETE FROM AKUZ.T_SCHET_MED_DEV
WHERE SchetUsl IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_MR_USL_N
DELETE FROM AKUZ.T_SCHET_MR_USL_N
WHERE SchetUsl IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_USL
DELETE FROM AKUZ.T_SCHET_USL
WHERE SchetUslID IN (
    SELECT SchetUslID
    FROM USLToDelete
);

-- Delete rows from T_SCHET_LS	
DELETE FROM AKUZ.T_SCHET_LS
WHERE DvSchetSluch16 IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_LS_82	
DELETE FROM AKUZ.T_SCHET_LS_82
WHERE SchetSluch82 IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_LS_92	
DELETE FROM AKUZ.T_SCHET_LS_92
WHERE SchetSluch92 IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_KSLP	
DELETE FROM AKUZ.T_SCHET_KSLP
WHERE SchetSluch IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_SL_KOEF
DELETE FROM AKUZ.T_SCHET_SL_KOEF
WHERE SchetKsgKpg IN (
    SELECT SchetKsgKpgID
    FROM AKUZ.T_SCHET_KSG_KPG kslp
	JOIN SluchToDelete ss on ss.SchetSluchID = kslp.SchetSluch
);

-- Delete rows from T_SCHET_KSG_KPG
DELETE FROM AKUZ.T_SCHET_KSG_KPG
WHERE SchetSluch IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_LP_CONTR
DELETE FROM AKUZ.T_SCHET_LP_CONTR
WHERE SchetInj IN (
    SELECT inj.SchetInjID
    FROM AKUZ.T_SCHET_INJ inj
    JOIN AKUZ.T_SCHET_LEK_PR slp ON inj.SchetLekPr = slp.SchetLekPrID
    JOIN AKUZ.T_SCHET_ONK_USL onk_usl ON slp.SchetOnkUsl = onk_usl.SchetOnkUslID
    JOIN AKUZ.T_SCHET_ONK_SL onk_sl on onk_sl.SchetOnkSlID = onk_usl.SchetOnkSl
    JOIN SluchToDelete ss on ss.SchetSluchID = onk_sl.SchetSluch
);

-- Delete rows from T_SCHET_INJ
DELETE FROM AKUZ.T_SCHET_INJ
WHERE SchetLekPr IN (
    SELECT slp.SchetLekPrID
    FROM AKUZ.T_SCHET_LEK_PR slp
    JOIN AKUZ.T_SCHET_ONK_USL onk_usl on onk_usl.SchetOnkUslID = slp.SchetOnkUsl
    JOIN AKUZ.T_SCHET_ONK_SL onk_sl on onk_sl.SchetOnkSlID = onk_usl.SchetOnkSl
    JOIN SluchToDelete ss on ss.SchetSluchID = onk_sl.SchetSluch
);

-- Delete rows from T_SCHET_LEK_PR
DELETE FROM AKUZ.T_SCHET_LEK_PR
WHERE SchetOnkUsl IN (
    SELECT SchetOnkUslID
    FROM AKUZ.T_SCHET_ONK_USL onk_usl
	JOIN AKUZ.T_SCHET_ONK_SL onk_sl on onk_sl.SchetOnkSlID = onk_usl.SchetOnkSl
	JOIN SluchToDelete ss on ss.SchetSluchID = onk_sl.SchetSluch
);

-- Delete rows from T_SCHET_ONK_USL
DELETE FROM AKUZ.T_SCHET_ONK_USL
WHERE SchetOnkSl IN (
    SELECT SchetOnkSlID
    FROM AKUZ.T_SCHET_ONK_SL onk_sl
	JOIN SluchToDelete ss on ss.SchetSluchID = onk_sl.SchetSluch
);

-- Delete rows from T_SCHET_B_DIAG
DELETE FROM AKUZ.T_SCHET_B_DIAG
WHERE SchetOnkSl IN (
    SELECT SchetOnkSlID
    FROM AKUZ.T_SCHET_ONK_SL onk_sl
	JOIN SluchToDelete ss on ss.SchetSluchID = onk_sl.SchetSluch
);

-- Delete rows from T_SCHET_B_PROT
DELETE FROM AKUZ.T_SCHET_B_PROT
WHERE SchetOnkSl IN (
    SELECT SchetOnkSlID
    FROM AKUZ.T_SCHET_ONK_SL onk_sl
	JOIN SluchToDelete ss on ss.SchetSluchID = onk_sl.SchetSluch
);

-- Delete rows from T_SCHET_CONS
DELETE FROM AKUZ.T_SCHET_CONS
WHERE SchetSluch IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_ONK_SL
DELETE FROM AKUZ.T_SCHET_ONK_SL
WHERE SchetSluch IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_SLUCH_66_ONMK_SL
DELETE FROM AKUZ.T_SCHET_SLUCH_66_ONMK_SL
WHERE SchetSluch66 IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_SLUCH_66_REAB_SL
DELETE FROM AKUZ.T_SCHET_SLUCH_66_REAB_SL
WHERE SchetSluch66 IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_NAZ
DELETE FROM AKUZ.T_SCHET_NAZ
WHERE SchetSluch IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);
	
-- Delete rows from T_SCHET_SANK
DELETE FROM AKUZ.T_SCHET_SANK
WHERE SchetSluch IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_LEK_DOSE_SL
DELETE FROM AKUZ.T_SCHET_LEK_DOSE_SL
WHERE LekPrSl IN (
    SELECT SchetLekPrSlID
    FROM AKUZ.T_SCHET_LEK_PR_SL lp
	JOIN SluchToDelete ss on ss.SchetSluchID = lp.SchetSluch
);

-- Delete rows from T_SCHET_LEK_PR_SL
DELETE FROM AKUZ.T_SCHET_LEK_PR_SL
WHERE SchetSluch IN (
    SELECT SchetSluchID
	FROM SluchToDelete
);

-- Delete rows from T_SCHET_FILE_SLUCH
DELETE from AKUZ.T_SCHET_FILE_SLUCH sfs
	WHERE sfs.Schet = "SchetID";

--DECLARE @TSS TABLE (ID UNIQUEIDENTIFIER)

--INSERT @TSS
--SELECT ss.SchetSluchID 
--	FROM [AKUZ].T_SCHET_SLUCH ss
--	JOIN #SchetToDelete STD ON STD.SchetZapID = SS.SchetZap

DELETE from AKUZ.T_DV_SCHET_SLUCH_73 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_DV_SCHET_SLUCH_16 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_MR_SCHET_SLUCH_16 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_XR_SCHET_SLUCH_16 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_PD_SCHET_SLUCH_16 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_PD_SCHET_SLUCH_73 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_EB_SCHET_SLUCH_16 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_03 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_66 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_44 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_60 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_83 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_82 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_90 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_92 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_93 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_94 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_95 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_116 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH_173 WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);
DELETE from AKUZ.T_SCHET_SLUCH WHERE SchetSluchID IN (SELECT SchetSluchID FROM SluchToDelete);

--DELETE FROM @TSS

--INSERT @TSS
--SELECT z_sl.SchetZSlID 
--FROM [AKUZ].T_SCHET_Z_SL z_sl
--	JOIN #SchetToDelete STD ON STD.SchetZapID = z_sl.SchetZap


DELETE from AKUZ.T_SCHET_Z_SL_03 WHERE SchetZSlID IN (SELECT SchetZSlID FROM ZSLToDelete);
DELETE from AKUZ.T_SCHET_Z_SL_92 WHERE SchetZSlID IN (SELECT SchetZSlID FROM ZSLToDelete);
DELETE from AKUZ.T_SCHET_Z_SL_93 WHERE SchetZSlID IN (SELECT SchetZSlID FROM ZSLToDelete);
DELETE from AKUZ.T_SCHET_Z_SL_95 WHERE SchetZSlID IN (SELECT SchetZSlID FROM ZSLToDelete);
DELETE from AKUZ.T_SCHET_Z_SL_173 WHERE SchetZSlID IN (SELECT SchetZSlID FROM ZSLToDelete);
DELETE from AKUZ.T_SCHET_Z_SL_116 WHERE SchetZSlID IN (SELECT SchetZSlID FROM ZSLToDelete);
DELETE from AKUZ.T_SCHET_Z_SL_44 WHERE SchetZSlID IN (SELECT SchetZSlID FROM ZSLToDelete);
DELETE from AKUZ.T_SCHET_Z_SL WHERE SchetZSlID IN (SELECT SchetZSlID FROM ZSLToDelete);

-- Delete rows from T_SCHET_ZAP_90
DELETE FROM AKUZ.T_SCHET_ZAP_90 sz90
	where sz90.SchetZapID in (select SchetZapID from ZapToDelete);

-- Delete rows from T_SCHET_ZAP_92
DELETE FROM AKUZ.T_SCHET_ZAP_92 sz92
	where sz92.SchetZapID in (select SchetZapID from ZapToDelete);

-- Delete rows from T_SCHET_ZAP_93
DELETE FROM AKUZ.T_SCHET_ZAP_93 sz93
	where sz93.SchetZapID in (select SchetZapID from ZapToDelete);

-- Delete rows from T_SCHET_ZAP_94
DELETE FROM AKUZ.T_SCHET_ZAP_94 sz94
	where sz94.SchetZapID in (select SchetZapID from ZapToDelete);

-- Delete rows from T_SCHET_ZAP_95
DELETE FROM AKUZ.T_SCHET_ZAP_95 sz95
	where sz95.SchetZapID in (select SchetZapID from ZapToDelete);

-- Delete rows from T_SCHET_ZAP
DELETE FROM AKUZ.T_SCHET_ZAP sz
	where sz.SchetZapID in (select SchetZapID from ZapToDelete);

DELETE from AKUZ.T_SCHET_DOKT WHERE Schet = "SchetID";

DELETE FROM AKUZ.T_SCHET_PACK sp
	WHERE sp.Schet = "SchetID";

DELETE from AKUZ.T_SCHET_QUEUE_ERROR WHERE Schet = "SchetID";

drop table ZapToDelete;
drop table SluchToDelete;
drop table USLToDelete;
drop table ZSLToDelete;
	
end; $$ LANGUAGE plpgsql;