DROP PROCEDURE IF EXISTS uspTanevValtasAlkalmazottTorlesKereses
GO
	CREATE PROCEDURE uspTanevValtasAlkalmazottTorlesKereses
		 @pKovTanevId INT
	AS
	BEGIN
		SET NOCOUNT ON;

		SELECT a.ID
			,a.ID AS AlkalmazottId
			,fel.C_NYOMTATASINEV AS AlkalmazottNev
			,fel.C_OKTATASIAZONOSITO AS OktatasiAzonosito
			,m.C_ALKALMAZASKEZDETE AS AlkalmazasKezdete
			,m.C_ALKALMAZASMEGSZUNESE AS AlkalmazasVege
		FROM T_ALKALMAZOTT_OSSZES a
		INNER JOIN T_FELHASZNALO_OSSZES fel ON fel.ID = a.ID
			AND fel.TOROLT = 'F'
			AND fel.C_TANEVID = a.C_ALTANEVID
		INNER JOIN T_TANEV_OSSZES t ON t.ID = @pKovTanevId
			AND t.C_KOVETKEZO = 'T'
			AND t.TOROLT = 'F'
		INNER JOIN T_MUNKAUGYIADATOK_OSSZES m ON m.C_ALKALMAZOTTID = a.ID
			AND m.C_TANEVID = a.C_ALTANEVID
			AND m.TOROLT = 'F'
		LEFT JOIN T_ORARENDIORA_OSSZES oo ON oo.C_TANARID = a.ID
			AND oo.TOROLT = 'F'
			AND oo.C_TANEVID = a.C_ALTANEVID
		LEFT JOIN T_FOGLALKOZAS_OSSZES f ON f.C_TANARID = a.ID
			AND f.TOROLT = 'F'
			AND f.C_TANEVID = a.C_ALTANEVID
		WHERE a.C_ALTANEVID = @pKovTanevId
			AND C_ALKALMAZASMEGSZUNESE <= DATEADD(DAY, - 1, t.C_KEZDONAP)
			AND oo.ID IS NULL
			AND f.ID IS NULL
			AND a.TOROLT = 'F'
	END
GO