CREATE VIEW dbo.FIS_HESABI_AYSONU_AKTARIM_SUBE
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, ABS(ROUND(QQ.BAKIYE, 2)) 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        REV
                               FROM            dbo.[FIS_HESABI_CARI_BAKIYELER_SUBE])) TT