CREATE VIEW dbo.WEBAPI_DGR210404I0
AS
SELECT        ITM.LOGICALREF AS Ref, ITM.CODE AS SicilKodu, dbo.RepOzel(dbo.InitCap({ fn CONCAT({ fn CONCAT({ fn CONCAT(ISNULL(ITM.SPECODE2, ''), ' ') }, ISNULL(dbo.RepLhRh(ITM.NAME), '')) }, ISNULL(dbo.RepLhRh(ITM.NAME3), '')) 
                         })) AS SicilAdi, ITM.SPECODE4 AS Marka, ITM.STGRPCODE AS UrunTipi, CASE WHEN (ISNULL(GNT.ONHAND, 0) - ISNULL(GNT.TEMPIN, 0) - ISNULL(GNT.RESERVED, 0)) > 0 THEN 'VAR' ELSE 'YOK' END AS StokDurumu, 
                         CASE WHEN ISNULL(TLF.TL, 0) BETWEEN 0 AND 200 THEN ISNULL(TLF.TL, 0) * 2.50 WHEN ISNULL(TLF.TL, 0) BETWEEN 201 AND 500 THEN ISNULL(TLF.TL, 0) * 2.00 WHEN ISNULL(TLF.TL, 0) BETWEEN 501 AND 
                         1000 THEN ISNULL(TLF.TL, 0) * 1.65 WHEN ISNULL(TLF.TL, 0) BETWEEN 1001 AND 2500 THEN ISNULL(TLF.TL, 0) * 1.50 WHEN ISNULL(TLF.TL, 0) BETWEEN 2501 AND 4000 THEN ISNULL(TLF.TL, 0) 
                         * 1.40 WHEN ISNULL(TLF.TL, 0) BETWEEN 4001 AND 7000 THEN ISNULL(TLF.TL, 0) * 1.30 WHEN ISNULL(TLF.TL, 0) BETWEEN 7001 AND 10000 THEN ISNULL(TLF.TL, 0) * 1.25 WHEN ISNULL(TLF.TL, 0) BETWEEN 10001 AND 
                         20000 THEN ISNULL(TLF.TL, 0) * 1.20 WHEN ISNULL(TLF.TL, 0) > 20000 THEN ISNULL(TLF.TL, 0) * 1.20 ELSE 0 END AS ListeFiyati, 'TL' AS Kur, 'ADET' AS Birim, ITM.VAT AS Kdv, CASE WHEN KR.LOGICALREF IS NULL 
                         THEN CONCAT('https://universaloto.com/foto/photos/', ISNULL(dbo.formatNumber(ITM.LOGICALREF), '0'), '.jpg') ELSE '' END AS Resim, ITM.SPECODE2 AS Arac, CASE WHEN (ISNULL(GNT.ONHAND, 0) - ISNULL(GNT.TEMPIN, 0) 
                         - ISNULL(GNT.RESERVED, 0)) > 10 THEN 10 ELSE (ISNULL(GNT.ONHAND, 0) - ISNULL(GNT.TEMPIN, 0) - ISNULL(GNT.RESERVED, 0)) END AS StokAdeti, ISNULL(MAM.KAT1, '') AS Model, ITM.GTIPCODE AS Kategori, 
                         ISNULL(MAM.KAT1, '') AS Kategori1, ROW_NUMBER() OVER (ORDER BY ITM.CAPIBLOCK_CREADEDDATE) AS SIRA, ISNULL(MAM.KAT2, '') AS Kategori2, ISNULL(MAM.KAT3, '') AS Kategori3, ISNULL(MAM.KAT4, '') AS Kategori4, 
ISNULL(MAM.KAT5, '') AS Kategori5, ISNULL(MAM.KAT6, '') AS Kategori6, ISNULL(MAM.KAT7, '') AS Kategori7, ISNULL(MAM.KAT8, '') AS Kategori8, ISNULL(MAM.KAT9, '') AS Kategori9, ISNULL(MAM.KAT10, '') AS Kategori10, 
ISNULL(MAM.KAT11, '') AS Kategori11, ISNULL(MAM.KAT12, '') AS Kategori12, ISNULL(MAM.KAT13, '') AS Kategori13, ISNULL(MAM.KAT14, '') AS Kategori14
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 LEFT OUTER JOIN
                         dbo.KirikResimler AS KR ON KR.LOGICALREF = ITM.LOGICALREF
WHERE        ITM.ACTIVE = 0 AND TLF.TL IS NOT NULL;