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, 
                      dbo.TITEM.ITEMADI1, dbo.TITEM.ITEMADI2, dbo.TITEM.ITEMADI3, dbo.TITEM.ITEMADI4, dbo.TITEM.ITEMADI5, dbo.TITEM.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, 
                      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,
                      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.SUBEBOLUM HDSBBLM ON dbo.HDOSYA.SUBEID = HDSBBLM.SUBEID AND dbo.HDOSYA.BOLUMID = HDSBBLM.BOLUMID LEFT OUTER JOIN
                      dbo.SUBEBOLUMITEMGRUP HDSBBLMIG ON dbo.HDOSYA.SUBEID = HDSBBLMIG.SUBEID AND dbo.HDOSYA.BOLUMID = HDSBBLMIG.BOLUMID AND dbo.TITEM.ITEMGRUPID = HDSBBLMIG.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.SUBEBOLUM UYGSBBLM ON dbo.HDOSYA.SUBEID = UYGSBBLM.SUBEID AND dbo.HESAPISL.UYGBOLUMID = UYGSBBLM.BOLUMID LEFT OUTER JOIN
                      dbo.SUBEBOLUMITEMGRUP UYGSBBLMIG ON dbo.HDOSYA.SUBEID = UYGSBBLMIG.SUBEID AND dbo.HESAPISL.UYGBOLUMID = UYGSBBLMIG.BOLUMID AND dbo.TITEM.ITEMGRUPID = UYGSBBLMIG.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
