SET ARITHABORT ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE FUNCTION [dbo].[FatIslDoviz]
(@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 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 * 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, 
            (HESAPISL.HIKRMNETTUTAR + 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, 
            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, 
            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, 
            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, 
            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, 
            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, 
            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, 
            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
          LEFT JOIN DOVIZISL HDVZISL WITH(NOLOCK) ON HDVZISL.KODU = @HesapDoviz
                                        AND HDVZISL.TARIH = @Tarih
          LEFT JOIN DOVIZISL FDVZISL WITH(NOLOCK) ON FDVZISL.KODU = @FatDoviz
                                        AND FDVZISL.TARIH = @Tarih
     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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

