CREATE VIEW dbo.DEPO_FIS_MALZEME_LISTESI_TAMAMLANANLAR
AS
SELECT        STL.STOCKREF, STL.LOGICALREF AS STLLOGREF, STF.LOGICALREF, 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, 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, STL.CPACODE, STF.SHPAGNCOD, STF.TRACKNR, STF.SHPTYPCOD, STF.UGIRTRACKINGNO, STF.CAMPAIGNCODE, CASE WHEN STL.AMOUNT = ISNULL(GNT.ONHAND, 0) OR
                         ISNULL(GNT.ONHAND, 0) = 0 THEN '**' ELSE '' END AS SON
FROM            dbo.LG_011_08_STLINE AS STL LEFT OUTER JOIN
                         dbo.LG_011_CLCARD AS CLC ON CLC.LOGICALREF = STL.CLIENTREF LEFT OUTER JOIN
                         dbo.LG_011_08_STFICHE AS STF ON STL.STFICHEREF = STF.LOGICALREF LEFT OUTER JOIN
                         dbo.LG_011_ITEMS AS ITM ON STL.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                         dbo.LV_011_08_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.SHPAGNCOD = 'T') OR
                         (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.TRACKNR = 'T') OR
                         (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.SHPTYPCOD = 'T')