CREATE VIEW dbo.DEPO_FIS_KARGO_ISLEMLERI
AS
SELECT        STF.LOGICALREF, CASE WHEN (STF.SHPAGNCOD = '' OR
                         STF.SHPAGNCOD IS NULL OR
                         STF.SHPAGNCOD = 'B') AND (STF.TRACKNR = '' OR
                         STF.TRACKNR IS NULL OR
                         STF.TRACKNR = 'B') AND (STF.SHPTYPCOD = '' OR
                         STF.SHPTYPCOD IS NULL OR
                         STF.SHPTYPCOD = 'B') THEN 'BEKLEMEDE' WHEN (STF.SHPAGNCOD = 'T' AND STF.TRACKNR = 'T' AND STF.SHPTYPCOD = 'T') AND STF.TRADINGGRP = 'T' THEN 'TAMAMLANDI' WHEN (STF.SHPAGNCOD = 'T' AND 
                         STF.TRACKNR = 'T' AND STF.SHPTYPCOD = 'T') THEN 'KONTROL EDILECEK' ELSE 'HAZIRLANIYOR' END AS DURUM, CASE WHEN STF.SHPAGNCOD = '' OR
                         STF.SHPAGNCOD IS NULL OR
                         STF.SHPAGNCOD = 'B' THEN 'BEKLEMEDE' WHEN STF.SHPAGNCOD = 'H' THEN 'HAZIRLANIYOR' WHEN STF.SHPAGNCOD = 'K' THEN 'KONTROL EDILIYOR' WHEN STF.SHPAGNCOD = 'T' THEN 'TAMAMLANDI' ELSE 'BILINMIYOR'
                          END AS DURUMA, CASE WHEN STF.TRACKNR = '' OR
                         STF.TRACKNR IS NULL OR
                         STF.TRACKNR = 'B' THEN 'BEKLEMEDE' WHEN STF.TRACKNR = 'H' THEN 'HAZIRLANIYOR' WHEN STF.TRACKNR = 'K' THEN 'KONTROL EDILIYOR' WHEN STF.TRACKNR = 'T' THEN 'TAMAMLANDI' ELSE 'BILINMIYOR' END AS DURUMB,
                          CASE WHEN STF.SHPTYPCOD = '' OR
                         STF.SHPTYPCOD IS NULL OR
                         STF.SHPTYPCOD = 'B' THEN 'BEKLEMEDE' WHEN STF.SHPTYPCOD = 'H' THEN 'HAZIRLANIYOR' WHEN STF.SHPTYPCOD = 'K' THEN 'KONTROL EDILIYOR' WHEN STF.SHPTYPCOD = 'T' THEN 'TAMAMLANDI' ELSE 'BILINMIYOR'
                          END AS DURUMC, STF.DATE_, STF.FICHENO, CLC.CODE, CLC.DEFINITION_, { fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(STF.GENEXP1, ' ') }, 
                         STF.GENEXP2) }, ' ') }, STF.GENEXP3) }, ' ') }, STF.GENEXP4) }, ' ') }, STF.GENEXP5) }, ' ') }, STF.GENEXP6) } AS GENEXP, CONVERT(VARCHAR, STF.CAPIBLOCK_CREADEDDATE, 8) AS OLUSTURMA, 
                         STF.SITEID + STF.SENDCNT + STF.PROFILEID AS STAPRINT, STF.SITEID AS PRINTA, STF.SENDCNT AS PRINTB, STF.PROFILEID AS PRINTC, STF.TRADINGGRP, 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.INVNO, STF.LOGICALREF AS STFREF, STF.INVOICEREF AS INVREF, 
                         STF.CONTROLINFO AS KARGO, KRG.ENTEGRENO, KRG.REF
FROM            dbo.LG_011_07_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        (CLC.CITY <> 'DIYARBAKIR') AND (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%')