

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,3) AND   
 (ITARIH>=CONVERT(smalldatetime,CONVERT(DATE,@TARIH)) AND ITARIH < CONVERT(smalldatetime,CONVERT(DATE,@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 AKTUYATAK.HUODANO IS  NULL
--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