CREATE VIEW dbo.WEBAPI_SYR340164I
AS
SELECT     ITM.LOGICALREF AS Ref, ITM.CODE AS SicilKodu, dbo.RepOzel(dbo.InitCap({ fn CONCAT({ fn CONCAT(dbo.RepLhRh(ITM.NAME), ' ') }, dbo.RepLhRh(ITM.NAME3)) })) AS SicilAdi, ITM.SPECODE4 AS Marka, ITM.GTIPCODE AS AnaKategori, ITM.STGRPCODE AS Kategori, 
                  CASE WHEN (GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED) > 0 THEN 'VAR' ELSE 'YOK' END AS StokDurumu, CASE WHEN ISNULL(NULLIF (ITM.GTIPCODE, ''), 'DIGER') = 'AYDINLATMA' THEN TLF.TL * 0.7 * 1.2 ELSE TLF.TL * 0.73 * 1.2 END AS ListeFiyati, 'TL' AS Kur, 
                  { fn CONCAT({ fn CONCAT('https://universaloto.com/foto/photos/', dbo.formatNumber(ITM.LOGICALREF)) }, '.jpg') } AS Resim, ITM.SPECODE2 AS Arac, ISNULL(MAM.KAT1, '') AS Model, CASE WHEN (GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED) 
                  > 10 THEN 10 ELSE ISNULL((GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED), 0) END AS StokAdeti, ITM.VAT AS Kdv, 'Adet' AS Birim
FROM        dbo.LG_011_ITEMS AS ITM LEFT OUTER JOIN
                  dbo.LV_011_08_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.MALZEME_ARAC_MODELLERI AS MAM ON MAM.LOGREF = ITM.LOGICALREF
WHERE     (ITM.ACTIVE = 0) AND (TLF.TL IS NOT NULL) AND (LEN(ITM.NAME) > 5)