
ALTER FUNCTION [dbo].[FNMDLYATAKDURUM] ( @SUBEID int, @ODANO nvarchar(5), @TARIH smalldatetime)
RETURNS TABLE
AS
RETURN(
SELECT TODA.ODANO AS ODANO, YATAKNO,YTKDURUMU,TODA.SUBEID AS SUBEID, ODADURUMU,
CASE WHEN HUYATAKNO IS NOT NULL  
THEN 1
ELSE 0
END AS DOLU, MDLYATAKKODU,TODA.BIRIMID
FROM TYATAK  (NOLOCK) INNER JOIN 
          TODA ON TYATAK.ODANO=TODA.ODANO AND TYATAK.SUBEID=TODA.SUBEID LEFT JOIN
	(SELECT SUBEID, HUODANO, HUYATAKNO FROM HUYGISLEM (NOLOCK) 
	INNER JOIN HESAPISL (NOLOCK) ON HUYGISLEM.HESAPISLID = HESAPISL.HESAPISLID
	INNER JOIN HDOSYA (NOLOCK)  ON HESAPISL.HDOSYAID = HDOSYA.HDOSYAID
	WHERE HDOSYA.SUBEID = @SUBEID AND HUYGISLEM.DURUM IN(1,2) AND 
	(DBO.TARIHAL(ITARIH) = DBO.TARIHAL(@TARIH) /*AND ITARIH <@TARIH + 1*/) AND IPTAL = 0 AND ONAYLI = 1 AND TEKLIF = 0
	GROUP BY SUBEID, HUODANO, HUYATAKNO) AS AKTUYATAK ON AKTUYATAK.SUBEID = TYATAK.SUBEID AND HUODANO = TODA.ODANO AND HUYATAKNO = TYATAK.YATAKNO
WHERE (TYATAK.ODANO = @ODANO OR @ODANO IS NULL) AND TYATAK.SUBEID = @SUBEID
and
NOT EXISTS (SELECT * FROM DBO.FNYATAKDURUM(1,null,@TARIH,0) where ODADURUMU='Aktif' AND YTKDURUMU='Aktif' and DOLU=1 and ODANO=TODA.ODANO AND YATAKNO=TYATAK.YATAKNO) ) 

GO


