CREATE VIEW dbo.FIS_HESABI_AYSONU_AKTARIM
AS
SELECT        *
FROM            (SELECT        QQ.CLIENTREF, CC.CODE AS [FIRMA KODU], CC.DEFINITION_ AS [FIRMA ÜNVANI], UPPER(CC.CITY) AS [SEHIR], UPPER(CC.SPECODE2) AS PLASIYER, CASE WHEN LEN(CC.CODE) <= 10 AND ISNULL
                                                        ((SELECT        TOP 1 BAKIYE
                                                            FROM            dbo.FIS_HESABI_CARI_BAKIYELER AS FHC
                                                            WHERE        FHC.REV = CC.CODE AND FHC.CODE NOT LIKE '%SB%' AND FHC.CODE NOT LIKE '%2S%' AND FHC.CODE NOT LIKE '%3S%' AND FHC.CODE NOT LIKE '%SU%' AND FHC.CODE NOT LIKE '%SB%' AND 
                                                                                     FHC.CODE NOT LIKE '%ITH%' AND FHC.CODE NOT LIKE '%S.%' AND FHC.CODE NOT LIKE '%-S%' AND FHC.CODE NOT LIKE '%GS%' AND FHC.CODE NOT LIKE '%M%' AND 
                                                                                     FHC.CODE NOT LIKE '%Ü%'
                                                            ORDER BY BAKIYE DESC), 0) > 0 THEN ISNULL
                                                        ((SELECT        TOP 1 ABS(BAKIYE)
                                                            FROM            dbo.FIS_HESABI_CARI_BAKIYELER AS FHC
                                                            WHERE        FHC.REV = CC.CODE AND FHC.CODE NOT LIKE '%SB%' AND FHC.CODE NOT LIKE '%2S%' AND FHC.CODE NOT LIKE '%3S%' AND FHC.CODE NOT LIKE '%SU%' AND FHC.CODE NOT LIKE '%SB%' AND 
                                                                                     FHC.CODE NOT LIKE '%ITH%' AND FHC.CODE NOT LIKE '%S.%' AND FHC.CODE NOT LIKE '%-S%' AND FHC.CODE NOT LIKE '%GS%' AND FHC.CODE NOT LIKE '%M%' AND 
                                                                                     FHC.CODE NOT LIKE '%Ü%'
                                                            ORDER BY BAKIYE DESC), 0) WHEN LEN(CC.CODE) > 10 AND QQ.BAKIYE < 0 THEN ABS(QQ.BAKIYE) ELSE 0 END AS [ALACAK], CASE WHEN LEN(CC.CODE) <= 10 AND ISNULL
                                                        ((SELECT        TOP 1 BAKIYE
                                                            FROM            dbo.FIS_HESABI_CARI_BAKIYELER AS FHC
                                                            WHERE        FHC.REV = CC.CODE AND FHC.CODE NOT LIKE '%SB%' AND FHC.CODE NOT LIKE '%2S%' AND FHC.CODE NOT LIKE '%3S%' AND FHC.CODE NOT LIKE '%SU%' AND FHC.CODE NOT LIKE '%SB%' AND 
                                                                                     FHC.CODE NOT LIKE '%ITH%' AND FHC.CODE NOT LIKE '%S.%' AND FHC.CODE NOT LIKE '%-S%' AND FHC.CODE NOT LIKE '%GS%' AND FHC.CODE NOT LIKE '%M%' AND 
                                                                                     FHC.CODE NOT LIKE '%Ü%'
                                                            ORDER BY BAKIYE ASC), 0) < 0 THEN
                                                        (SELECT        TOP 1 ABS(BAKIYE)
                                                          FROM            dbo.FIS_HESABI_CARI_BAKIYELER AS FHC
                                                          WHERE        FHC.REV = CC.CODE AND FHC.CODE NOT LIKE '%SB%' AND FHC.CODE NOT LIKE '%2S%' AND FHC.CODE NOT LIKE '%3S%' AND FHC.CODE NOT LIKE '%SU%' AND FHC.CODE NOT LIKE '%SB%' AND 
                                                                                    FHC.CODE NOT LIKE '%ITH%' AND FHC.CODE NOT LIKE '%S.%' AND FHC.CODE NOT LIKE '%-S%' AND FHC.CODE NOT LIKE '%GS%' AND FHC.CODE NOT LIKE '%M%' AND 
                                                                                    FHC.CODE NOT LIKE '%Ü%'
                                                          ORDER BY BAKIYE ASC) WHEN LEN(CC.CODE) > 10 AND QQ.BAKIYE > 0 THEN ABS(QQ.BAKIYE) ELSE 0 END AS [BORÇ], ABS(QQ.BAKIYE) AS BAKIYE, 
                                                    CASE WHEN QQ.BAKIYE > 0 THEN 'ALACAK' WHEN QQ.BAKIYE = 0 THEN '' ELSE 'BORÇ' END AS DURUM
                          FROM            (SELECT        CLIENTREF, SUM(ALACAK) - SUM(BORC) AS BAKIYE
                                                    FROM            ((SELECT        CLIENTREF, TRNET AS BORC, 0 AS ALACAK
                                                                                FROM            LG_011_08_CLFLINE
                                                                                WHERE        [SIGN] = 0 AND CANCELLED = 0)
                                                                              UNION ALL
                                                                              (SELECT        CLIENTREF, 0 AS BORC, TRNET AS ALACAK
                                                                               FROM            LG_011_08_CLFLINE
                                                                               WHERE        [SIGN] = 1 AND CANCELLED = 0)) KK
                         GROUP BY CLIENTREF) AS QQ LEFT OUTER JOIN
LG_011_CLCARD AS CC ON CC.LOGICALREF = QQ.CLIENTREF
WHERE        CC.ACTIVE = 0 AND REPLACE(SUBSTRING(CC.CODE, 0, 11), ' ', '') IN
                             (SELECT DISTINCT REV
                               FROM            dbo.FIS_HESABI_CARI_BAKIYELER) AND CC.LOGICALREF NOT IN (875, 1197, 1939, 1753, 2219, 2531, 261, 2456) AND REPLACE(SUBSTRING(CC.CODE, 0, 11), ' ', '') NOT IN ('67,000,001', '35,000,182', 
                         '34,000,273', '34,000,234', '34,000,472', '34,000,387', '34,000,212', '34,000,145', '34,000,145', '35,000,188/M.')) TT
WHERE        TT.[FIRMA KODU] NOT IN
                             (SELECT        FHC.CODE
                               FROM            dbo.FIS_HESABI_CARI_BAKIYELER AS FHC
                               WHERE        LEN(FHC.CODE) > 10 AND FHC.BAKIYE = 0) AND REPLACE(SUBSTRING(TT.[FIRMA KODU], 0, 11), ' ', '') NOT IN
                             (SELECT DISTINCT REV
                               FROM            dbo.FIS_HESABI_CARI_BAKIYELER AS FHC
                               WHERE        LEN(FHC.CODE) > 10 AND FHC.BAKIYE BETWEEN - 5 AND 5)