-- _Brk_B2B_vSiraliCahar View

DECLARE @SQL as varchar(4000)

SET @SQL = 'SELECT ROW_NUMBER() OVER (ORDER BY TARIH, INC_KEY_NUMBER) AS sira,* FROM TBLCAHAR WITH(NOLOCK)' 

IF Object_ID('_Brk_B2B_vSiraliCahar') IS NULL
    SET @SQL = 'CREATE VIEW [dbo].[_Brk_B2B_vSiraliCahar] AS ' + @SQL
ELSE    
    SET @SQL = 'ALTER VIEW [dbo].[_Brk_B2B_vSiraliCahar] AS ' + @SQL

EXEC(@SQL)

-- _Brk_B2B_fCariBakiye_Tursuz_Projesiz Function

DECLARE @SQL2 as varchar(4000)

SET @SQL2 = '(@carikod VARCHAR(15), @tarih DATETIME, @sira INT, @SUBEKODU INT,@MERKEZSUBEOLARAKCALIS int) RETURNS FLOAT (30) AS BEGIN DECLARE @bak FLOAT(30); SET @bak = 0; SELECT @bak = SUM(ISNULL(borc, 0) - ISNULL(alacak, 0)) FROM _Brk_B2B_vSiraliCahar(NOLOCK) WHERE CARI_KOD = @carikod 
--AND SUBE_KODU = @SUBEKODU 
AND (CASE
         WHEN @MERKEZSUBEOLARAKCALIS = 1 AND (SELECT COUNT(*) FROM dbo.TBLSUBELER WHERE SUBE_KODU <> -1 and SUBE_KODU <> 32767 and MERKEZMI=''E'' and SUBE_KODU=@SUBEKODU) > 0 THEN
             ''TRUE''
         ELSE
          (CASE
            WHEN SUBE_KODU = @SUBEKODU  THEN
             ''TRUE''
            ELSE
             ''FALSE''
          END)
       END) = ''TRUE'' 
AND sira <= @sira RETURN @bak END' 

IF Object_ID('_Brk_B2B_fCariBakiye_Tursuz_Projesiz') IS NULL
    SET @SQL2 = 'CREATE FUNCTION [dbo].[_Brk_B2B_fCariBakiye_Tursuz_Projesiz] ' + @SQL2
ELSE    
    SET @SQL2 = 'ALTER FUNCTION [dbo].[_Brk_B2B_fCariBakiye_Tursuz_Projesiz] ' + @SQL2

EXEC(@SQL2)

-- _Brk_B2B_fCariHareketRaporu_Tarih_Tursuz_Projesiz Function

DECLARE @SQL3 as varchar(4000)

SET @SQL3 = '(@CARIKOD VARCHAR(50),@tarih1 DATETIME, @tarih2 DATETIME, @SUBEKODU INT, @MERKEZSUBEOLARAKCALIS int) RETURNS TABLE AS RETURN (SELECT CARI_KOD, DATEADD(DAY,-1,@tarih1) AS TARIH, '''' AS BELGE_NO, ''Devreden'' AS ACIKLAMA, DATEADD(DAY,-1,@tarih1) AS VADE_TARIHI, CASE  WHEN SUM(BORC - ALACAK) > 0 THEN SUM(BORC - ALACAK)  ELSE 0 END AS BORC, CASE  WHEN SUM(BORC - ALACAK) < 0 THEN SUM(ALACAK - BORC)  ELSE 0 END AS ALACAK, SUM(BORC - ALACAK) AS BAKIYE FROM TBLCAHAR WITH(NOLOCK) WHERE TARIH < @tarih1 AND CARI_KOD = @CARIKOD 
--AND SUBE_KODU = @SUBEKODU 
AND (CASE
         WHEN @MERKEZSUBEOLARAKCALIS = 1 AND (SELECT COUNT(*) FROM dbo.TBLSUBELER WHERE SUBE_KODU <> -1 and SUBE_KODU <> 32767 and MERKEZMI=''E'' and SUBE_KODU=@SUBEKODU) > 0 THEN
             ''TRUE''
         ELSE
          (CASE
            WHEN SUBE_KODU = @SUBEKODU THEN
             ''TRUE''
            ELSE
             ''FALSE''
          END)
       END) = ''TRUE'' 
GROUP BY CARI_KOD UNION ALL SELECT CARI_KOD, TARIH, BELGE_NO, dbo.TRK(ACIKLAMA) AS ACIKLAMA, VADE_TARIHI, BORC, ALACAK, dbo._Brk_B2B_fCariBakiye_Tursuz_Projesiz(_Brk_B2B_vSiraliCahar.CARI_KOD, _Brk_B2B_vSiraliCahar.TARIH, _Brk_B2B_vSiraliCahar.sira, @SUBEKODU, @MERKEZSUBEOLARAKCALIS) BAKIYE FROM _Brk_B2B_vSiraliCahar WHERE TARIH BETWEEN @tarih1 AND @tarih2 
--AND SUBE_KODU = @SUBEKODU 
AND (CASE
         WHEN @MERKEZSUBEOLARAKCALIS = 1 AND (SELECT COUNT(*) FROM dbo.TBLSUBELER WHERE SUBE_KODU <> -1 and SUBE_KODU <> 32767 and MERKEZMI=''E'' and SUBE_KODU=@SUBEKODU) > 0 THEN
             ''TRUE''
         ELSE
          (CASE
            WHEN SUBE_KODU = @SUBEKODU  THEN
             ''TRUE''
            ELSE
             ''FALSE''
          END)
       END) = ''TRUE'' 
AND CARI_KOD = @CARIKOD)' 

IF Object_ID('_Brk_B2B_fCariHareketRaporu_Tarih_Tursuz_Projesiz') IS NULL
    SET @SQL3 = 'CREATE FUNCTION [dbo].[_Brk_B2B_fCariHareketRaporu_Tarih_Tursuz_Projesiz] ' + @SQL3
ELSE    
    SET @SQL3 = 'ALTER FUNCTION [dbo].[_Brk_B2B_fCariHareketRaporu_Tarih_Tursuz_Projesiz] ' + @SQL3

EXEC(@SQL3)