CREATE VIEW dbo.DEPO_FIS_KARGO_ISLEMLERI_GUNLUK
AS
SELECT        CODE, DEFINITION_, OLUSTURMA, CLCREF, ADRES, CITY, TOWN, TELNRS1, KARGO, ADET, ENTNO, REF
FROM            (SELECT        CLC.CODE, CLC.DEFINITION_, CONVERT(VARCHAR, MAX(STF.CAPIBLOCK_CREADEDDATE), 8) AS OLUSTURMA, CLC.LOGICALREF AS CLCREF, { fn CONCAT({ fn CONCAT(CLC.ADDR1, ' ') }, CLC.ADDR2) } AS ADRES, 
                                                    UPPER(CLC.CITY) AS CITY, UPPER(CLC.TOWN) AS TOWN, CLC.TELNRS1, STF.CONTROLINFO AS KARGO, COUNT(*) AS ADET, ISNULL(KRG.ENTEGRENO, '') AS ENTNO, KRG.REF
                          FROM            dbo.LG_011_08_STFICHE AS STF LEFT OUTER JOIN
                                                    dbo.LG_011_CLCARD AS CLC ON CLC.LOGICALREF = STF.CLIENTREF LEFT OUTER JOIN
                                                    dbo.KARGOLAR_LISTE AS KRG ON KRG.IRSREF = STF.LOGICALREF
                          WHERE        (STF.TRCODE IN (7, 8)) AND (STF.CANCELLED = 0) AND (STF.BILLED = 1) AND (STF.CYPHCODE <> '1') AND (STF.CYPHCODE <> 'D') AND (STF.DOCODE NOT LIKE '%BAKIYE%') AND 
                                                    (STF.DOCODE NOT LIKE '%BAKIYE%') AND (CLC.CITY <> 'DIYARBAKIR') AND (STF.DATE_ = CAST(GETDATE() AS DATE))
                          GROUP BY CLC.CODE, CLC.DEFINITION_, CLC.LOGICALREF, CLC.ADDR1, CLC.ADDR2, CLC.CITY, CLC.TOWN, CLC.TELNRS1, STF.CONTROLINFO, KRG.ENTEGRENO, KRG.REF) AS QQ