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

CREATE PROCEDURE public.sp_dropmedicalrequest(IN "MedicalRequestID" text)
 LANGUAGE plpgsql
AS $procedure$
BEGIN

    update IES.T_SCHET s
    set MedicalRequestRemek = null
    where s.MedicalRequestRemek = "MedicalRequestID"::uuid;

    delete 
    from IES.T_MEDICAL_REQUEST_SCHET mrs
    where mrs.MedicalRequest = "MedicalRequestID"::uuid;

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

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

    delete 
    from IES.T_MEDICAL_ERROR me
    using IES.T_MEDICAL_REQUEST_ITEM mri
    where mri.MedicalRequest = "MedicalRequestID"::uuid and mri.MedicalRequestItemID = me.MedicalRequestItem;

    delete 
    from IES.T_MEDICAL_REQUEST_ITEM 
    where MedicalRequest = "MedicalRequestID"::uuid;

    delete
    from IES.T_MEDICAL_REQUEST_EXPERT
    where MedicalRequest = "MedicalRequestID"::uuid;

END;
$procedure$;
