DROP PROCEDURE IF EXISTS public.sp_dropmedicalexpertise;
DROP FUNCTION IF EXISTS public.sp_dropmedicalexpertise;

CREATE PROCEDURE public.sp_dropmedicalexpertise("Id" text)
LANGUAGE plpgsql
AS $body$
BEGIN

    UPDATE ies.t_smo_act smo
    SET medicalexpertise = NULL
    WHERE smo.medicalexpertise = "Id"::uuid;
   
    DELETE FROM ies.t_medical_expertise_error mee
    USING ies.t_medical_expertise_item mei, ies.t_medical_expertise_schet mes
    WHERE mee.medicalexpertiseitem = mei.medicalexpertiseitemid
      AND mei.medicalexpertiseschet = mes.medicalexpertiseschetid
      AND mes.medicalexpertise = "Id"::uuid;
   
    DELETE FROM ies.t_medical_expertise_item mei
    USING ies.t_medical_expertise_schet mes
    WHERE mei.medicalexpertiseschet = mes.medicalexpertiseschetid
      AND mes.medicalexpertise = "Id"::uuid;
   
    DELETE FROM ies.t_medical_expertise_group meg
    USING ies.t_medical_expertise_schet mes
    WHERE meg.medicalexpertisereestr = mes.medicalexpertiseschetid
      AND mes.medicalexpertise = "Id"::uuid;
   
    DELETE FROM ies.t_medical_request_schet mrs
    USING ies.t_medical_expertise_schet mes
    WHERE mrs.medicalexpertiseschet = mes.medicalexpertiseschetid
      AND mes.medicalexpertise = "Id"::uuid;
   
    DELETE FROM ies.t_medical_expertise_schet mes
    WHERE mes.medicalexpertise = "Id"::uuid;
   
    DELETE FROM ies.t_medical_expertise_criteria mec
    WHERE mec.medicalexpertise = "Id"::uuid;
   
    DELETE FROM ies.t_medical_expertise me
    WHERE me.medicalexpertiseid = "Id"::uuid;

END
$body$;