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     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, 
                      dbo.TITEM.ITEMADI + CASE WHEN @Tarihli = 1 THEN  '(' + CONVERT(NVARCHAR(10),dbo.HESAPISL.ITARIH, 104) + ')' ELSE '' END AS ITEMADI, 
                      dbo.TITEM.ITEMADI1 + CASE WHEN @Tarihli = 1 THEN  '(' + CONVERT(NVARCHAR(10),dbo.HESAPISL.ITARIH, 104) + ')' ELSE '' END AS ITEMADI1, 
                      dbo.TITEM.ITEMADI2 + CASE WHEN @Tarihli = 1 THEN  '(' + CONVERT(NVARCHAR(10),dbo.HESAPISL.ITARIH, 104) + ')' ELSE '' END AS ITEMADI2,  
                      dbo.TITEM.ITEMADI3 + CASE WHEN @Tarihli = 1 THEN  '(' + CONVERT(NVARCHAR(10),dbo.HESAPISL.ITARIH, 104) + ')' ELSE '' END AS ITEMADI3, 
                      dbo.TITEM.ITEMADI4 + CASE WHEN @Tarihli = 1 THEN  '(' + CONVERT(NVARCHAR(10),dbo.HESAPISL.ITARIH, 104) + ')' ELSE '' END AS ITEMADI4,
                      dbo.TITEM.ITEMADI5 + CASE WHEN @Tarihli = 1 THEN  '(' + CONVERT(NVARCHAR(10),dbo.HESAPISL.ITARIH, 104) + ')' ELSE '' END AS ITEMADI5,
                      dbo.TITEM.ITEMADI6 + CASE WHEN @Tarihli = 1 THEN  '(' + CONVERT(NVARCHAR(10),dbo.HESAPISL.ITARIH, 104) + ')' ELSE '' END 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 * @Kur AS KRMTUTAR, 
                      (dbo.HESAPISL.HIKRMNETTUTAR + dbo.HESAPISL.HSTKATKITUTAR) * @Kur AS NETKRMTUTAR, 
                      dbo.HESAPISL.HIKRMNETTUTAR * @Kur AS NETKRMTUTAR2, 
                      dbo.HESAPISL.OZELTUTAR * @Kur AS HSTTUTAR, 
                      dbo.HESAPISL.HIHSTNETTUTAR * @Kur AS NETHSTTUTAR,
                      dbo.HESAPISL.HSTKATKITUTAR * @Kur AS HSTKATKITUTAR, 
                      dbo.HESAPISL.SGTUTAR * @Kur AS SGTUTAR, 
                      dbo.HESAPISL.HISGNETTUTAR * @Kur AS 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
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
