CREATE VIEW dbo.WEBAPI_CMP350175I
AS
SELECT        ITM.LOGICALREF AS Ref, ITM.CODE AS SicilKodu, { fn CONCAT({ fn CONCAT(ITM.NAME, ' ') }, ITM.NAME3) } AS SicilAdi, ISNULL(ITM.SPECODE4, '') AS Marka, ISNULL(ITM.STGRPCODE, '') AS UrunTipi, '0' AS KampanyaFiyati, 
                         CASE WHEN ISNULL((GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED), 0) > 0 THEN 'VAR' ELSE 'YOK' END AS StokDurumu, CASE WHEN ISNULL((TLF.USD * 0.77 * 1.2 * 0.88), 0) 
                         <= TLF.USD300 THEN ISNULL(ROUND(TLF.USD * 0.77 * 1.2 * 0.88 * 2.15, 3), 0) WHEN ISNULL((TLF.USD * 0.77 * 1.2 * 0.88), 0) > TLF.USD300 AND ISNULL((TLF.USD * 0.77 * 1.2 * 0.88), 0) 
                         <= TLF.USD500 THEN ISNULL(ROUND(TLF.USD * 0.77 * 1.2 * 0.88 * 1.85, 3), 0) WHEN ISNULL((TLF.USD * 0.77 * 1.2 * 0.88), 0) > TLF.USD500 AND ISNULL((TLF.USD * 0.77 * 1.2 * 0.88), 0) 
                         <= TLF.USD1000 THEN ISNULL(ROUND(TLF.USD * 0.77 * 1.2 * 0.88 * 1.65, 3), 0) WHEN ISNULL((TLF.USD * 0.77 * 1.2 * 0.88), 0) > TLF.USD1000 AND ISNULL((TLF.USD * 0.77 * 1.2 * 0.88), 0) 
                         <= TLF.USD1750 THEN ISNULL(ROUND(TLF.USD * 0.77 * 1.2 * 0.88 * 1.50, 3), 0) ELSE ISNULL(ROUND(TLF.USD * 0.77 * 1.2 * 0.88 * 1.45, 3), 0) END AS ListeFiyati, ROUND(ISNULL(TLF.USD * 1.01, 0), 3) AS MaliyetFiyati, 
                         'TL' AS Kur, 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, ITM.SPECODE2 AS Arac, 
                         CASE WHEN ISNULL((GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED), 0) > 10 THEN 10 ELSE ISNULL((GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED), 0) END AS StokAdeti, ITM.EXPCTGNO AS Barcode
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_USD_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.USD IS NOT NULL)