CREATE VIEW dbo.DEPO_FIS_mALZEME_LISTESI_KARGO
AS
SELECT        STL.STOCKREF, STL.LOGICALREF AS STLLOGREF, 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 DURUMC1, CASE WHEN STF.CAMPAIGNCODE = '' OR
                         STF.CAMPAIGNCODE IS NULL OR
                         STF.CAMPAIGNCODE = 'B' THEN 'BEKLEMEDE' WHEN STF.CAMPAIGNCODE = 'H' THEN 'HAZIRLANIYOR' WHEN STF.CAMPAIGNCODE = 'K' THEN 'KONTROL EDILIYOR' WHEN STF.CAMPAIGNCODE = 'T' THEN 'TAMAMLANDI' ELSE
                          'BILINMIYOR' END AS DURUMC2, CASE WHEN STF.UGIRTRACKINGNO = '' OR
                         STF.UGIRTRACKINGNO IS NULL OR
                         STF.UGIRTRACKINGNO = 'B' THEN 'BEKLEMEDE' WHEN STF.UGIRTRACKINGNO = 'H' THEN 'HAZIRLANIYOR' WHEN STF.UGIRTRACKINGNO = 'K' THEN 'KONTROL EDILIYOR' WHEN STF.UGIRTRACKINGNO = 'T' THEN 'TAMAMLANDI'
                          ELSE 'BILINMIYOR' END AS DURUMC3, 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, ITM.SPECODE5 AS ISONR, ITM.CODE AS ITMCODE, ITM.NAME AS ITMNAME, ISNULL(GNT.ONHAND, 0) AS STOCK, ITM.SPECODE4 AS MARKA, ITM.STGRPCODE AS TIP, 
                         ITM.SPECODE AS RAF, ITM.SPECODE5 AS BLOK, ITM.GROUPNR AS BCOLOR, ITM.LOGICALREF AS REF, CASE WHEN STL.CPACODE = '' OR
                         STL.CPACODE IS NULL OR
                         STL.CPACODE = 'B' THEN 'BEKLEMEDE' WHEN STL.CPACODE = 'K' THEN 'KONTROL EDILDI' WHEN STL.CPACODE = 'T' THEN 'TAMAMLANDI' WHEN STL.CPACODE = 'L' THEN 'KAYIP' ELSE 'BILINMIYOR' END AS SATIRDURUM,
                          STL.AMOUNT, 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 PRINTC1, STF.CANCELEXP AS PRINTC2, STF.UNDOEXP AS PRINTC3, STL.SPECODE2, 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, INV.FICHENO AS INVNO
FROM            dbo.LG_011_07_STLINE AS STL LEFT OUTER JOIN
                         dbo.LG_011_CLCARD AS CLC ON CLC.LOGICALREF = STL.CLIENTREF LEFT OUTER JOIN
                         dbo.LG_011_07_STFICHE AS STF ON STL.STFICHEREF = STF.LOGICALREF LEFT OUTER JOIN
                         dbo.LG_011_07_INVOICE AS INV ON STL.INVOICEREF = INV.LOGICALREF LEFT OUTER JOIN
                         dbo.LG_011_ITEMS AS ITM ON STL.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                         dbo.LV_011_07_GNTOTST AS GNT ON STL.STOCKREF = GNT.STOCKREF AND GNT.INVENNO = - 1
WHERE        (STL.TRCODE IN (7, 8)) AND (STF.CANCELLED = 0) AND (STL.LINETYPE = 0) AND (STL.CANCELLED = 0) AND (STF.CYPHCODE <> '1') AND (STF.CYPHCODE <> 'D') AND (STF.DOCODE NOT LIKE '%BAKIYE%') AND 
                         (STF.DOCODE NOT LIKE '%BAKIYE%')