SET ARITHABORT ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE  FUNCTION [dbo].[FatIslDovizTRH] (
	@Tarih as datetime, 
	@HesapDoviz as nvarchar(10), 
	@idx1 as int, 
	@FatDoviz as nvarchar(10), 
	@idx2 as int,
	@hdid as int)  
RETURNS table AS  
return
SELECT     dbo.TITEM.SIRANO, dbo.TITEM.SIRANO1, dbo.TITEM.SIRANO2, dbo.TITEM.SIRANO3, dbo.TITEM.SIRANO4, dbo.TITEM.SIRANO5, dbo.TITEM.SIRANO6, 
                      dbo.HESAPISL.IPTAL, dbo.HESAPISL.HESAPISLID, dbo.HESAPISL.ITARIH, dbo.HESAPISL.HDOSYAID, dbo.TITEM.ITEMID, 

                      CONVERT(NVARCHAR(10), dbo.HESAPISL.ITARIH, 104) + ' - ' + dbo.TITEM.ITEMADI  AS ITEMADI,
                      CONVERT(NVARCHAR(10), dbo.HESAPISL.ITARIH, 104) + ' - ' + dbo.TITEM.ITEMADI1  AS ITEMADI1,
                      CONVERT(NVARCHAR(10), dbo.HESAPISL.ITARIH, 104) + ' - ' + dbo.TITEM.ITEMADI2  AS ITEMADI2,
                      CONVERT(NVARCHAR(10), dbo.HESAPISL.ITARIH, 104) + ' - ' + dbo.TITEM.ITEMADI3  AS ITEMADI3,
                      CONVERT(NVARCHAR(10), dbo.HESAPISL.ITARIH, 104) + ' - ' + dbo.TITEM.ITEMADI4  AS ITEMADI4,
                      CONVERT(NVARCHAR(10), dbo.HESAPISL.ITARIH, 104) + ' - ' + dbo.TITEM.ITEMADI5  AS ITEMADI5,
                      CONVERT(NVARCHAR(10), dbo.HESAPISL.ITARIH, 104) + ' - ' + dbo.TITEM.ITEMADI6  AS ITEMADI6, 

                      dbo.TITEM.ITEMKODU, dbo.TITEM.ITEMKODU1, 
                      dbo.TITEM.ITEMKODU2, dbo.TITEM.ITEMKODU3, dbo.TITEM.ITEMKODU4, dbo.TITEM.ITEMKODU5, dbo.TITEM.ITEMKODU6, dbo.HESAPISL.KRMHFATISLID, 
                      dbo.HESAPISL.OZELHFATISLID, dbo.HESAPISL.HSTKDVORAN, dbo.HESAPISL.KRMKDVORAN, dbo.HESAPISL.TABLOTIPI, dbo.TITEM.ITEMTURUID, 
                      UYGBLM.BOLUMTURU, TTIPADI AS CISLEM, dbo.HESAPISL.ADET, 
                      dbo.HESAPISL.KRMTUTAR *              
                      CASE WHEN @HesapDoviz = 'TL' THEN 1 
                                ELSE CASE @idx1 WHEN 1 THEN ISNULL(HDVZISL.KUR1,0) WHEN 2 THEN ISNULL(HDVZISL.KUR2,0) WHEN 3 THEN ISNULL(HDVZISL.KUR3,0) 
                                                               WHEN 4 THEN ISNULL(HDVZISL.KUR4,0) 
                                         ELSE 0 END 
                      END / 
                      CASE WHEN @FatDoviz = 'TL' THEN 1 
                                 ELSE CASE @idx2 WHEN 1 THEN ISNULL(FDVZISL.KUR1,0) WHEN 2 THEN ISNULL(FDVZISL.KUR2,0) WHEN 3 THEN ISNULL(FDVZISL.KUR3,0) 
                                                                WHEN 4 THEN ISNULL(FDVZISL.KUR4,0) 
                                          ELSE 0 END 
                      END KRMTUTAR, 
                      (dbo.HESAPISL.HIKRMNETTUTAR + dbo.HESAPISL.HSTKATKITUTAR) * 
                      CASE WHEN @HesapDoviz = 'TL' THEN 1 
                                ELSE CASE @idx1 WHEN 1 THEN ISNULL(HDVZISL.KUR1,0) WHEN 2 THEN ISNULL(HDVZISL.KUR2,0) WHEN 3 THEN ISNULL(HDVZISL.KUR3,0) 
                                                               WHEN 4 THEN ISNULL(HDVZISL.KUR4,0) 
                                         ELSE 0 END 
                      END / 
                      CASE WHEN @FatDoviz = 'TL' THEN 1 
                                 ELSE CASE @idx2 WHEN 1 THEN ISNULL(FDVZISL.KUR1,0) WHEN 2 THEN ISNULL(FDVZISL.KUR2,0) WHEN 3 THEN ISNULL(FDVZISL.KUR3,0) 
                                                                WHEN 4 THEN ISNULL(FDVZISL.KUR4,0) 
                                          ELSE 0 END 
                      END NETKRMTUTAR, 
                      dbo.HESAPISL.HIKRMNETTUTAR * 
                      CASE WHEN @HesapDoviz = 'TL' THEN 1 
                                ELSE CASE @idx1 WHEN 1 THEN ISNULL(HDVZISL.KUR1,0) WHEN 2 THEN ISNULL(HDVZISL.KUR2,0) WHEN 3 THEN ISNULL(HDVZISL.KUR3,0) 
                                                               WHEN 4 THEN ISNULL(HDVZISL.KUR4,0) 
                                         ELSE 0 END 
                      END / 
                      CASE WHEN @FatDoviz = 'TL' THEN 1 
                                 ELSE CASE @idx2 WHEN 1 THEN ISNULL(FDVZISL.KUR1,0) WHEN 2 THEN ISNULL(FDVZISL.KUR2,0) WHEN 3 THEN ISNULL(FDVZISL.KUR3,0) 
                                                                WHEN 4 THEN ISNULL(FDVZISL.KUR4,0) 
                                          ELSE 0 END 
                      END NETKRMTUTAR2, 
                      dbo.HESAPISL.OZELTUTAR * 
                      CASE WHEN @HesapDoviz = 'TL' THEN 1 
                                ELSE CASE @idx1 WHEN 1 THEN ISNULL(HDVZISL.KUR1,0) WHEN 2 THEN ISNULL(HDVZISL.KUR2,0) WHEN 3 THEN ISNULL(HDVZISL.KUR3,0) 
                                                               WHEN 4 THEN ISNULL(HDVZISL.KUR4,0) 
                                         ELSE 0 END 
                      END / 
                      CASE WHEN @FatDoviz = 'TL' THEN 1 
                                 ELSE CASE @idx2 WHEN 1 THEN ISNULL(FDVZISL.KUR1,0) WHEN 2 THEN ISNULL(FDVZISL.KUR2,0) WHEN 3 THEN ISNULL(FDVZISL.KUR3,0) 
                                                                WHEN 4 THEN ISNULL(FDVZISL.KUR4,0) 
                                          ELSE 0 END 
                      END HSTTUTAR, 
                      dbo.HESAPISL.HIHSTNETTUTAR * 
                      CASE WHEN @HesapDoviz = 'TL' THEN 1 
                                ELSE CASE @idx1 WHEN 1 THEN ISNULL(HDVZISL.KUR1,0) WHEN 2 THEN ISNULL(HDVZISL.KUR2,0) WHEN 3 THEN ISNULL(HDVZISL.KUR3,0) 
                                                               WHEN 4 THEN ISNULL(HDVZISL.KUR4,0) 
                                         ELSE 0 END 
                      END / 
                      CASE WHEN @FatDoviz = 'TL' THEN 1 
                                 ELSE CASE @idx2 WHEN 1 THEN ISNULL(FDVZISL.KUR1,0) WHEN 2 THEN ISNULL(FDVZISL.KUR2,0) WHEN 3 THEN ISNULL(FDVZISL.KUR3,0) 
                                                                WHEN 4 THEN ISNULL(FDVZISL.KUR4,0) 
                                          ELSE 0 END 
                      END NETHSTTUTAR,
                      dbo.HESAPISL.HSTKATKITUTAR * 
                      CASE WHEN @HesapDoviz = 'TL' THEN 1 
                                ELSE CASE @idx1 WHEN 1 THEN ISNULL(HDVZISL.KUR1,0) WHEN 2 THEN ISNULL(HDVZISL.KUR2,0) WHEN 3 THEN ISNULL(HDVZISL.KUR3,0) 
                                                               WHEN 4 THEN ISNULL(HDVZISL.KUR4,0) 
                                         ELSE 0 END 
                      END / 
                      CASE WHEN @FatDoviz = 'TL' THEN 1 
                                 ELSE CASE @idx2 WHEN 1 THEN ISNULL(FDVZISL.KUR1,0) WHEN 2 THEN ISNULL(FDVZISL.KUR2,0) WHEN 3 THEN ISNULL(FDVZISL.KUR3,0) 
                                                                WHEN 4 THEN ISNULL(FDVZISL.KUR4,0) 
                                          ELSE 0 END 
                      END HSTKATKITUTAR, 
                      dbo.HESAPISL.SGTUTAR *
                      CASE WHEN @HesapDoviz = 'TL' THEN 1 
                                ELSE CASE @idx1 WHEN 1 THEN ISNULL(HDVZISL.KUR1,0) WHEN 2 THEN ISNULL(HDVZISL.KUR2,0) WHEN 3 THEN ISNULL(HDVZISL.KUR3,0) 
                                                               WHEN 4 THEN ISNULL(HDVZISL.KUR4,0) 
                                         ELSE 0 END 
                      END / 
                      CASE WHEN @FatDoviz = 'TL' THEN 1 
                                 ELSE CASE @idx2 WHEN 1 THEN ISNULL(FDVZISL.KUR1,0) WHEN 2 THEN ISNULL(FDVZISL.KUR2,0) WHEN 3 THEN ISNULL(FDVZISL.KUR3,0) 
                                                                WHEN 4 THEN ISNULL(FDVZISL.KUR4,0) 
                                          ELSE 0 END 
                      END SGTUTAR, 
                      dbo.HESAPISL.HISGNETTUTAR *
                      CASE WHEN @HesapDoviz = 'TL' THEN 1 
                                ELSE CASE @idx1 WHEN 1 THEN ISNULL(HDVZISL.KUR1,0) WHEN 2 THEN ISNULL(HDVZISL.KUR2,0) WHEN 3 THEN ISNULL(HDVZISL.KUR3,0) 
                                                               WHEN 4 THEN ISNULL(HDVZISL.KUR4,0) 
                                         ELSE 0 END 
                      END / 
                      CASE WHEN @FatDoviz = 'TL' THEN 1 
                                 ELSE CASE @idx2 WHEN 1 THEN ISNULL(FDVZISL.KUR1,0) WHEN 2 THEN ISNULL(FDVZISL.KUR2,0) WHEN 3 THEN ISNULL(FDVZISL.KUR3,0) 
                                                                WHEN 4 THEN ISNULL(FDVZISL.KUR4,0) 
                                          ELSE 0 END 
                      END NETSGTUTAR, 
                      UYGBLM.SRMMRKID, 
                      HDDRK.SRMMRKID AS HDDRSRMMRKID, UYGDRK.SRMMRKID AS UYGDRSRMMRKID, ISTDRK.SRMMRKID AS ISTDRSRMMRKID, dbo.HESAPISL.HSTODEMEID, 
                      dbo.HESAPISL.OZELODENDI, dbo.TITEM.BARKOD, dbo.TITEMGRUP.AYYIHESAPID, dbo.TITEMGRUP.AYYDHESAPID, dbo.TITEMGRUP.YTYIHESAPID, 
                      dbo.TITEMGRUP.YTYDHESAPID, dbo.TITEMGRUP.GELIRISTUYGDR, dbo.HDOSYA.HDYATIYOR, dbo.HESAPISL.SGKDVORAN, dbo.HESAPISL.SGFATID, 
                      dbo.HESAPISL.BAZFYTBIRIM,  
                      CASE WHEN HDBLMIG.AYYIHESAPID IS NOT NULL THEN HDBLMIG.AYYIHESAPID ELSE HDBLM.AYYIHESAPID END AS HDBLMAYYIHESAPID, 
                      CASE WHEN HDBLMIG.AYYDHESAPID IS NOT NULL THEN HDBLMIG.AYYDHESAPID ELSE HDBLM.AYYDHESAPID END AS HDBLMAYYDHESAPID, 
                      CASE WHEN HDBLMIG.YTYIHESAPID IS NOT NULL THEN HDBLMIG.YTYIHESAPID ELSE HDBLM.YTYIHESAPID END AS HDBLMYTYIHESAPID, 
                      CASE WHEN HDBLMIG.YTYDHESAPID IS NOT NULL THEN HDBLMIG.YTYDHESAPID ELSE HDBLM.YTYDHESAPID END AS HDBLMYTYDHESAPID, 
                      CASE WHEN HDBLMIG.INDHESAPID IS NOT NULL THEN HDBLMIG.INDHESAPID ELSE HDBLM.INDHESAPID END AS HDBLMINDHESAPID, 
                      CASE WHEN UYGBLMIG.AYYIHESAPID IS NOT NULL THEN UYGBLMIG.AYYIHESAPID ELSE UYGBLM.AYYIHESAPID END AS UYGBLMAYYIHESAPID, 
                      CASE WHEN UYGBLMIG.AYYDHESAPID IS NOT NULL THEN UYGBLMIG.AYYDHESAPID ELSE UYGBLM.AYYDHESAPID END AS UYGBLMAYYDHESAPID, 
                      CASE WHEN UYGBLMIG.YTYIHESAPID IS NOT NULL THEN UYGBLMIG.YTYIHESAPID ELSE UYGBLM.YTYIHESAPID END AS UYGBLMYTYIHESAPID, 
                      CASE WHEN UYGBLMIG.YTYDHESAPID IS NOT NULL THEN UYGBLMIG.YTYDHESAPID ELSE UYGBLM.YTYDHESAPID END AS UYGBLMYTYDHESAPID, 
                      CASE WHEN UYGBLMIG.INDHESAPID IS NOT NULL THEN UYGBLMIG.INDHESAPID ELSE UYGBLM.INDHESAPID END AS UYGBLMINDHESAPID, 
                      dbo.CARIGRUP.HSTAYYIHESAPID, dbo.CARIGRUP.HSTAYYDHESAPID, dbo.CARIGRUP.HSTYTYIHESAPID, dbo.CARIGRUP.HSTYTYDHESAPID, dbo.CARIGRUP.KRMAYYIHESAPID, 
                      dbo.CARIGRUP.KRMAYYDHESAPID, dbo.CARIGRUP.KRMYTYIHESAPID, dbo.CARIGRUP.KRMYTYDHESAPID, 
                      SGKRMGRUP.HSTAYYIHESAPID AS SGHSTAYYIHESAPID, SGKRMGRUP.HSTAYYDHESAPID AS SGHSTAYYDHESAPID, 
                      SGKRMGRUP.HSTYTYIHESAPID AS SGHSTYTYIHESAPID, SGKRMGRUP.HSTYTYDHESAPID AS SGHSTYTYDHESAPID, 
                      SGKRMGRUP.KRMAYYIHESAPID AS SGKRMAYYIHESAPID, SGKRMGRUP.KRMAYYDHESAPID AS SGKRMAYYDHESAPID, 
                      SGKRMGRUP.KRMYTYIHESAPID AS SGKRMYTYIHESAPID, SGKRMGRUP.KRMYTYDHESAPID AS SGKRMYTYDHESAPID, dbo.HDOSYA.MDLTEDAVITURU, 
                      dbo.HDOSYA.ICMALID, dbo.HDOSYA.SGICMALID, dbo.TITEMGRUP.OKCKISIMNO
FROM         dbo.HESAPISL INNER JOIN
                      dbo.TITEM ON dbo.HESAPISL.ITEMID = dbo.TITEM.ITEMID INNER JOIN
                      dbo.TITEMGRUP ON dbo.TITEM.ITEMGRUPID = dbo.TITEMGRUP.ITEMGRUPID INNER JOIN
                      dbo.HDOSYA ON dbo.HESAPISL.HDOSYAID = dbo.HDOSYA.HDOSYAID INNER JOIN
                      dbo.TTABLOTIPI ON dbo.HESAPISL.TABLOTIPI = dbo.TTABLOTIPI.TABLOTIPI LEFT OUTER JOIN
                      dbo.TBOLUM HDBLM ON dbo.HDOSYA.BOLUMID = HDBLM.BOLUMID LEFT OUTER JOIN
                      dbo.TBOLUMITEMGRUP HDBLMIG ON dbo.HDOSYA.BOLUMID = HDBLMIG.BOLUMID AND dbo.TITEM.ITEMGRUPID = HDBLMIG.ITEMGRUPID LEFT OUTER JOIN
                      dbo.CARI ON dbo.HDOSYA.HKCARIID = dbo.CARI.CARIID LEFT OUTER JOIN
                      dbo.CARIGRUP ON dbo.CARI.CARIGRUPID = dbo.CARIGRUP.CARIGRUPID LEFT OUTER JOIN
                      dbo.TDRKIMLIK HDDRK ON dbo.HDOSYA.DRID = HDDRK.DRID LEFT OUTER JOIN
                      dbo.TDRKIMLIK UYGDRK ON dbo.HESAPISL.UYGDRID = UYGDRK.DRID LEFT OUTER JOIN
                      dbo.TBOLUM UYGBLM ON dbo.HESAPISL.UYGBOLUMID = UYGBLM.BOLUMID LEFT OUTER JOIN
                      dbo.TBOLUMITEMGRUP UYGBLMIG ON dbo.HDOSYA.BOLUMID = UYGBLMIG.BOLUMID AND dbo.TITEM.ITEMGRUPID = UYGBLMIG.ITEMGRUPID LEFT OUTER JOIN
                      dbo.TDRKIMLIK ISTDRK ON ISTDRK.DRID = dbo.HESAPISL.ISTDRID LEFT OUTER JOIN
                      dbo.CARI SGKRM ON SGKRM.CARIID = dbo.HDOSYA.HDSGKURUMID LEFT OUTER JOIN
                      dbo.CARIGRUP SGKRMGRUP ON SGKRM.CARIGRUPID = SGKRMGRUP.CARIGRUPID LEFT OUTER JOIN
                      dbo.DOVIZISL HDVZISL ON HDVZISL.KODU = @HesapDoviz AND HDVZISL.TARIH = @Tarih LEFT OUTER JOIN 
                      dbo.DOVIZISL FDVZISL ON FDVZISL.KODU = @FatDoviz AND FDVZISL.TARIH = @Tarih 
WHERE dbo.HESAPISL.HDOSYAID = CASE WHEN @hdid <> -1 THEN @hdid ELSE dbo.HESAPISL.HDOSYAID END AND 
      (dbo.HESAPISL.IPTAL = 0) AND (dbo.HESAPISL.ONAYLI = 1) AND (dbo.HESAPISL.TEKLIF = 0) AND 
      (dbo.TITEM.GELIRDISI = 0)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

