SET ARITHABORT ON 
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FatIslDovizKur]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FatIslDovizKur]
go

SET QUOTED_IDENTIFIER OFF 
go
SET ANSI_NULLS ON 
go

CREATE FUNCTION [dbo].[FatIslDovizKur]
(@Tarih AS   DATETIME, 
 @Kur AS     FLOAT, 
 @hdid AS    INT, 
 @Tarihli AS BIT
)
RETURNS TABLE
AS
     RETURN
     SELECT TITEM.SIRANO, 
            TITEM.SIRANO1, 
            TITEM.SIRANO2, 
            TITEM.SIRANO3, 
            TITEM.SIRANO4, 
            TITEM.SIRANO5, 
            TITEM.SIRANO6, 
            HESAPISL.IPTAL, 
            HESAPISL.HESAPISLID, 
            HESAPISL.ITARIH, 
            HESAPISL.HDOSYAID, 
            TITEM.ITEMID, 
            TITEM.ITEMADI, 
            TITEM.ITEMADI1, 
            TITEM.ITEMADI2, 
            TITEM.ITEMADI3, 
            TITEM.ITEMADI4, 
            TITEM.ITEMADI5, 
            TITEM.ITEMADI6, 
            TITEM.ITEMKODU, 
            TITEM.ITEMKODU1, 
            TITEM.ITEMKODU2, 
            TITEM.ITEMKODU3, 
            TITEM.ITEMKODU4, 
            TITEM.ITEMKODU5, 
            TITEM.ITEMKODU6, 
            HESAPISL.KRMHFATISLID, 
            HESAPISL.OZELHFATISLID, 
            HESAPISL.HSTKDVORAN, 
            HESAPISL.KRMKDVORAN, 
            HESAPISL.TABLOTIPI, 
            TITEM.ITEMTURUID, 
            UYGBLM.BOLUMTURU, 
            TTIPADI AS CISLEM, 
            HESAPISL.ADET, 
            HESAPISL.KRMTUTAR * @Kur AS KRMTUTAR, 
			HESAPISL.KRMTUTAR * @Kur AS DUZELTMESIZKRMTUTAR,
            (HESAPISL.HIKRMNETTUTAR + HESAPISL.HSTKATKITUTAR) * @Kur AS NETKRMTUTAR, 
            HESAPISL.HIKRMNETTUTAR * @Kur AS NETKRMTUTAR2, 
			HESAPISL.HIKRMNETTUTAR * @Kur AS DUZELTMESIZNETKRMTUTAR2,
            HESAPISL.OZELTUTAR * @Kur AS HSTTUTAR, 
            HESAPISL.HIHSTNETTUTAR * @Kur AS NETHSTTUTAR, 
            HESAPISL.HSTKATKITUTAR * @Kur AS HSTKATKITUTAR, 
            HESAPISL.SGTUTAR * @Kur AS SGTUTAR, 
			HESAPISL.SGTUTAR * @Kur AS DUZELTMESIZSGTUTAR, 
            HESAPISL.HISGNETTUTAR * @Kur AS NETSGTUTAR, 
			HESAPISL.HISGNETTUTAR * @Kur AS DUZELTMESIZNETSGTUTAR, 
            UYGBLM.SRMMRKID, 
            HDDRK.SRMMRKID AS HDDRSRMMRKID, 
            UYGDRK.SRMMRKID AS UYGDRSRMMRKID, 
            ISTDRK.SRMMRKID AS ISTDRSRMMRKID, 
            HESAPISL.HSTODEMEID, 
            HESAPISL.OZELODENDI, 
            TITEM.BARKOD, 
            TITEMGRUP.AYYIHESAPID, 
            TITEMGRUP.AYYDHESAPID, 
            TITEMGRUP.YTYIHESAPID, 
            TITEMGRUP.YTYDHESAPID, 
            TITEMGRUP.GELIRISTUYGDR, 
            HDOSYA.HDYATIYOR, 
            HESAPISL.SGKDVORAN, 
            HESAPISL.SGFATID, 
            HESAPISL.BAZFYTBIRIM, 
            COALESCE(HDSBBLMIG.AYYIHESAPID, HDSBBLM.AYYIHESAPID, HDBLMIG.AYYIHESAPID, HDBLM.AYYIHESAPID) AS HDBLMAYYIHESAPID, 
            COALESCE(HDSBBLMIG.AYYDHESAPID, HDSBBLM.AYYDHESAPID, HDBLMIG.AYYDHESAPID, HDBLM.AYYDHESAPID) AS HDBLMAYYDHESAPID, 
            COALESCE(HDSBBLMIG.YTYIHESAPID, HDSBBLM.YTYIHESAPID, HDBLMIG.YTYIHESAPID, HDBLM.YTYIHESAPID) AS HDBLMYTYIHESAPID, 
            COALESCE(HDSBBLMIG.YTYDHESAPID, HDSBBLM.YTYDHESAPID, HDBLMIG.YTYDHESAPID, HDBLM.YTYDHESAPID) AS HDBLMYTYDHESAPID, 
            COALESCE(HDSBBLMIG.INDHESAPID, HDSBBLM.INDHESAPID, HDBLMIG.INDHESAPID, HDBLM.INDHESAPID) AS HDBLMINDHESAPID, 
            COALESCE(UYGSBBLMIG.AYYIHESAPID, UYGSBBLM.AYYIHESAPID, UYGBLMIG.AYYIHESAPID, UYGBLM.AYYIHESAPID) AS UYGBLMAYYIHESAPID, 
            COALESCE(UYGSBBLMIG.AYYDHESAPID, UYGSBBLM.AYYDHESAPID, UYGBLMIG.AYYDHESAPID, UYGBLM.AYYDHESAPID) AS UYGBLMAYYDHESAPID, 
            COALESCE(UYGSBBLMIG.YTYIHESAPID, UYGSBBLM.YTYIHESAPID, UYGBLMIG.YTYIHESAPID, UYGBLM.YTYIHESAPID) AS UYGBLMYTYIHESAPID, 
            COALESCE(UYGSBBLMIG.YTYDHESAPID, UYGSBBLM.YTYDHESAPID, UYGBLMIG.YTYDHESAPID, UYGBLM.YTYDHESAPID) AS UYGBLMYTYDHESAPID, 
            COALESCE(UYGSBBLMIG.INDHESAPID, UYGSBBLM.INDHESAPID, UYGBLMIG.INDHESAPID, UYGBLM.INDHESAPID) AS UYGBLMINDHESAPID, 
            COALESCE(CKURUMGRUPITEMGRUP.HSTAYYIHESAPID,CARIGRUP.HSTAYYIHESAPID) AS HSTAYYIHESAPID, 
			COALESCE(CKURUMGRUPITEMGRUP.HSTAYYDHESAPID,CARIGRUP.HSTAYYDHESAPID) AS HSTAYYDHESAPID, 
			COALESCE(CKURUMGRUPITEMGRUP.HSTYTYIHESAPID,CARIGRUP.HSTYTYIHESAPID) AS HSTYTYIHESAPID, 
			COALESCE(CKURUMGRUPITEMGRUP.HSTYTYDHESAPID,CARIGRUP.HSTYTYDHESAPID) AS HSTYTYDHESAPID,
			COALESCE(CKURUMGRUPITEMGRUP.KRMAYYIHESAPID,CARIGRUP.KRMAYYIHESAPID) AS KRMAYYIHESAPID,
			COALESCE(CKURUMGRUPITEMGRUP.KRMAYYDHESAPID,CARIGRUP.KRMAYYDHESAPID) AS KRMAYYDHESAPID,
			COALESCE(CKURUMGRUPITEMGRUP.KRMYTYIHESAPID,CARIGRUP.KRMYTYIHESAPID) AS KRMYTYIHESAPID, 
			COALESCE(CKURUMGRUPITEMGRUP.KRMYTYDHESAPID,CARIGRUP.KRMYTYDHESAPID) AS KRMYTYDHESAPID,
			COALESCE(SKURUMGRUPITEMGRUP.HSTAYYIHESAPID,SGKRMGRUP.HSTAYYIHESAPID) AS SGHSTAYYIHESAPID, 
			COALESCE(SKURUMGRUPITEMGRUP.HSTAYYDHESAPID,SGKRMGRUP.HSTAYYDHESAPID) AS SGHSTAYYDHESAPID, 
			COALESCE(SKURUMGRUPITEMGRUP.HSTYTYIHESAPID,SGKRMGRUP.HSTYTYIHESAPID) AS SGHSTYTYIHESAPID, 
			COALESCE(SKURUMGRUPITEMGRUP.HSTYTYDHESAPID,SGKRMGRUP.HSTYTYDHESAPID) AS SGHSTYTYDHESAPID, 
			COALESCE(SKURUMGRUPITEMGRUP.KRMAYYIHESAPID,SGKRMGRUP.KRMAYYIHESAPID) AS SGKRMAYYIHESAPID, 
			COALESCE(SKURUMGRUPITEMGRUP.KRMAYYDHESAPID,SGKRMGRUP.KRMAYYDHESAPID) AS SGKRMAYYDHESAPID, 
			COALESCE(SKURUMGRUPITEMGRUP.KRMYTYIHESAPID,SGKRMGRUP.KRMYTYIHESAPID) AS SGKRMYTYIHESAPID, 
			COALESCE(SKURUMGRUPITEMGRUP.KRMYTYDHESAPID,SGKRMGRUP.KRMYTYDHESAPID) AS SGKRMYTYDHESAPID, 
            HDOSYA.MDLTEDAVITURU, 
            HDOSYA.ICMALID, 
            HDOSYA.SGICMALID, 
            TITEMGRUP.OKCKISIMNO
     FROM HESAPISL WITH(NOLOCK)
          INNER JOIN TITEM WITH(NOLOCK) ON HESAPISL.ITEMID = TITEM.ITEMID
          INNER JOIN TITEMGRUP WITH(NOLOCK) ON TITEM.ITEMGRUPID = TITEMGRUP.ITEMGRUPID
          INNER JOIN HDOSYA WITH(NOLOCK) ON HESAPISL.HDOSYAID = HDOSYA.HDOSYAID
          INNER JOIN TTABLOTIPI WITH(NOLOCK) ON HESAPISL.TABLOTIPI = TTABLOTIPI.TABLOTIPI
          LEFT JOIN TBOLUM HDBLM WITH(NOLOCK) ON HDOSYA.BOLUMID = HDBLM.BOLUMID
          LEFT JOIN TBOLUMITEMGRUP HDBLMIG WITH(NOLOCK) ON HDOSYA.BOLUMID = HDBLMIG.BOLUMID
                                                        AND TITEM.ITEMGRUPID = HDBLMIG.ITEMGRUPID
          LEFT JOIN SUBEBOLUM HDSBBLM WITH(NOLOCK) ON HDOSYA.SUBEID = HDSBBLM.SUBEID
                                                   AND HDOSYA.BOLUMID = HDSBBLM.BOLUMID
          LEFT JOIN SUBEBOLUMITEMGRUP HDSBBLMIG WITH(NOLOCK) ON HDOSYA.SUBEID = HDSBBLMIG.SUBEID
                                                             AND HDOSYA.BOLUMID = HDSBBLMIG.BOLUMID
                                                             AND TITEM.ITEMGRUPID = HDSBBLMIG.ITEMGRUPID
          LEFT JOIN CARI WITH(NOLOCK) ON HDOSYA.HKCARIID = CARI.CARIID
          LEFT JOIN CARIGRUP WITH(NOLOCK) ON CARI.CARIGRUPID = CARIGRUP.CARIGRUPID
		  LEFT JOIN TKURUMGRUPITEMGRUP CKURUMGRUPITEMGRUP WITH(NOLOCK) ON CKURUMGRUPITEMGRUP.KURUMGRUPID=CARIGRUP.CARIGRUPID
          LEFT JOIN TDRKIMLIK HDDRK WITH(NOLOCK) ON HDOSYA.DRID = HDDRK.DRID
          LEFT JOIN TDRKIMLIK UYGDRK WITH(NOLOCK) ON HESAPISL.UYGDRID = UYGDRK.DRID
          LEFT JOIN TBOLUM UYGBLM WITH(NOLOCK) ON HESAPISL.UYGBOLUMID = UYGBLM.BOLUMID
          LEFT JOIN TBOLUMITEMGRUP UYGBLMIG WITH(NOLOCK) ON HDOSYA.BOLUMID = UYGBLMIG.BOLUMID
                                                         AND TITEM.ITEMGRUPID = UYGBLMIG.ITEMGRUPID
          LEFT JOIN SUBEBOLUM UYGSBBLM WITH(NOLOCK) ON HDOSYA.SUBEID = UYGSBBLM.SUBEID
                                                    AND HESAPISL.UYGBOLUMID = UYGSBBLM.BOLUMID
          LEFT JOIN SUBEBOLUMITEMGRUP UYGSBBLMIG WITH(NOLOCK) ON HDOSYA.SUBEID = UYGSBBLMIG.SUBEID
                                                              AND HESAPISL.UYGBOLUMID = UYGSBBLMIG.BOLUMID
                                                              AND TITEM.ITEMGRUPID = UYGSBBLMIG.ITEMGRUPID
          LEFT JOIN TDRKIMLIK ISTDRK WITH(NOLOCK) ON ISTDRK.DRID = HESAPISL.ISTDRID
          LEFT JOIN CARI SGKRM WITH(NOLOCK) ON SGKRM.CARIID = HDOSYA.HDSGKURUMID
          LEFT JOIN CARIGRUP SGKRMGRUP WITH(NOLOCK) ON SGKRM.CARIGRUPID = SGKRMGRUP.CARIGRUPID
		  LEFT JOIN TKURUMGRUPITEMGRUP SKURUMGRUPITEMGRUP WITH(NOLOCK) ON SKURUMGRUPITEMGRUP.KURUMGRUPID=SGKRMGRUP.CARIGRUPID
     WHERE HESAPISL.HDOSYAID = CASE
                                       WHEN @hdid <> -1
                                       THEN @hdid
                                       ELSE HESAPISL.HDOSYAID
                                   END
           AND (HESAPISL.IPTAL = 0)
           AND (HESAPISL.ONAYLI = 1)
           AND (HESAPISL.TEKLIF = 0)
           AND (TITEM.GELIRDISI = 0)
go
