do $$
begin
	if exists 
	(
		select * from pg_proc p
		where p.proname = 'sp_dropmedicalrequest'
	)
	then
		drop function public.sp_dropmedicalrequest(uuid);
	end if;
end;
$$ language plpgsql;
CREATE or Replace Function public.sp_dropmedicalrequest
(
 "MedicalRequestID" uuid
)
RETURNS void As 
$$
declare _ID uuid;
begin
	

update IES.T_MEDICAL_EXPERTISE_SCHET 
set MedicalRequest = null
where MedicalRequest = "MedicalRequestID";



delete 
from  IES.T_MEDICAL_REQUEST_SCHET
where MedicalRequest = "MedicalRequestID";

delete from IES.T_MEDICAL_REQEST_SUBITEM mrsi
using IES.T_MEDICAL_REQUEST_ITEM mri
where mri.MedicalRequest = "MedicalRequestID" and mri.MedicalRequestItemID = mrsi.MedicalRequestItem;

delete
from IES.T_MEDICAL_DESCRIPTION md
using IES.T_MEDICAL_REQUEST_ITEM mri 
where mri.MedicalRequest = "MedicalRequestID" and mri.MedicalRequestItemID = md.MedicalRequestItem;


Drop Table If Exists SSA;
Create Temp Table SSA as
  -- Запоминаем реестры в которые попали записи из этого счета
	
select mri.SchetSluchAccomplished
from IES.T_MEDICAL_REQUEST_ITEM mri
where mri.MedicalRequest = "MedicalRequestID";

delete 
from IES.T_MEDICAL_REQUEST_ITEM 
where MedicalRequest = "MedicalRequestID";
	
--	

	   for _ID in select _ID from SSA  order by _ID
			loop
				select  from ssp_setexpertisestatus(_ID);		
						
			end loop;

--
Drop Table If Exists SSA;
end;
$$ Language plpgsql;