CREATE VIEW dbo.B2B_PRODUCT_LIST
AS
SELECT        ISNULL(ITM.LOGICALREF, 0) AS Ref, ITM.CODE AS SicilKodu, CASE WHEN KR.LOGICALREF IS NULL THEN { fn CONCAT({ fn CONCAT('https://universaloto.com/foto/photos/', dbo.formatNumber(ITM.LOGICALREF)) }, '.jpg') 
                         } ELSE '' END AS Resim, ISNULL(GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED, 0) AS StokAdeti, ITM.PRODUCERCODE, { fn CONCAT(ITM.NAME, ITM.NAME3) } AS Aciklama
FROM            dbo.LG_011_ITEMS AS ITM LEFT OUTER JOIN
                         dbo.LV_011_07_GNTOTST AS GNT ON GNT.STOCKREF = ITM.LOGICALREF AND GNT.INVENNO = - 1 LEFT OUTER JOIN
                         dbo.UNI_TL_FIYAT AS TLF ON TLF.CARDREF = ITM.LOGICALREF LEFT OUTER JOIN
                         dbo.KirikResimler AS KR ON KR.LOGICALREF = ITM.LOGICALREF
WHERE        (ITM.ACTIVE = 0) AND (TLF.TL IS NOT NULL)