CREATE OR REPLACE FUNCTION sp_DispDirectFileDel
(
	"FileID" uuid
)
RETURNS void AS
$BODY$
begin
	
	DELETE 
	FROM ISZL.T_DISP_DIRECT_STATUS s
	using ISZL.T_DISP_DIRECT d 
	left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
	WHERE 
	d.DispDirectFile = "FileID"
	and s.DispDirect = d.DispDirectID
	and ddf.DispDirectFactID is null;

	delete
	FROM ISZL.T_DISP_DIRECT_INTERVIEW i
	using ISZL.T_DISP_DIRECT d
	left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
	WHERE d.DispDirectFile = "FileID" 
	and i.DispDirect = d.DispDirectID
	and ddf.DispDirectFactID is null;

	DELETE 
	FROM ISZL.T_DISP_DIRECT_INFO i
	using ISZL.T_DISP_DIRECT d
	left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
	WHERE d.DispDirectFile = "FileID"
	and i.DispDirect = d.DispDirectID
	and ddf.DispDirectFactID is null;
	
	DELETE 
	FROM ISZL.T_DISP_DIRECT_ANKETA1 a1
	using ISZL.T_DISP_DIRECT_QUESTIONING q
	join ISZL.T_DISP_DIRECT d on q.DispDirect = d.DispDirectID
	left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
	WHERE d.DispDirectFile = "FileID"
	and a1.DiapDirectQuestioningID = q.DiapDirectQuestioningID
	and ddf.DispDirectFactID is null;
	
	DELETE 
	FROM ISZL.T_DISP_DIRECT_ANKETA2 a2
	using ISZL.T_DISP_DIRECT_QUESTIONING q
	join ISZL.T_DISP_DIRECT d on q.DispDirect = d.DispDirectID
	left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
	WHERE d.DispDirectFile = "FileID"
	and a2.DiapDirectQuestioningID = q.DiapDirectQuestioningID
	and ddf.DispDirectFactID is null;
	
	delete
	FROM ISZL.T_DISP_DIRECT_QUESTIONING q
	using ISZL.T_DISP_DIRECT d
	left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
	WHERE d.DispDirectFile = "FileID"
	and q.DispDirect = d.DispDirectID
	and ddf.DispDirectFactID is null;

	DELETE 
	FROM ISZL.T_PATIENT_CONTACT pc
	using ISZL.T_DISP_DIRECT d
	left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
	WHERE d.DispDirectFile = "FileID"
	and pc.DispDirect = d.DispDirectID
	and ddf.DispDirectFactID is null;
	
	delete
	FROM ISZL.T_DISP_DIRECT_FACT_DESTINATION fd
	using ISZL.T_DISP_DIRECT_FACT f
	join ISZL.T_DISP_DIRECT d on f.DispDirect = d.DispDirectID
	left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
	WHERE d.DispDirectFile = "FileID"
	and f.DispDirectFactID = fd.DispDirectFact
	and ddf.DispDirectFactID is null;

	delete
	FROM ISZL.T_DISP_DIRECT_FACT f
	using ISZL.T_DISP_DIRECT d
	left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
	WHERE d.DispDirectFile = "FileID"
	and f.DispDirect = d.DispDirectID
	and ddf.DispDirectFactID is null;

	delete
	FROM ISZL.T_DISP_DIRECT d
	using ISZL.T_DISP_DIRECT d1
	left join ISZL.T_DISP_DIRECT_FACT ddf on d1.DispDirectID = ddf.DispDirect
	WHERE DispDirectFile = "FileID"
	and d.dispdirectid  = d1.dispdirectid
	and ddf.DispDirectFactID is null;

	if (
		select count(*) FROM ISZL.T_DISP_DIRECT d 
		left join ISZL.T_DISP_DIRECT_FACT ddf on d.DispDirectID = ddf.DispDirect
		WHERE DispDirectFile = "FileID" and ddf.DispDirectFactID is not null
		) = 0 then
		begin 
			DELETE 
			FROM ISZL.T_DISP_DIRECT_FILE f
			WHERE FileId = "FileID";
		end;
	end if;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_DispDnInfoDel(uuid) OWNER TO postgres;