CREATE VIEW dbo.WEBAPI_HLL270138GA
AS
SELECT        ITM.LOGICALREF AS Ref, ITM.CODE AS SicilKodu, LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(dbo.RepOzel(dbo.InitCap({ fn CONCAT({ fn CONCAT({ fn CONCAT(ITM.SPECODE2, ' ') }, dbo.RepLhRh(ITM.NAME)) }, 
                         dbo.RepLhRh(ITM.NAME3)) })), '(' + dbo.RepLhRh(ITM.NAME3) + ')', ''), '(4 Lu Pakette)', ''), '(2 LI PAKET)', ''), '(10 LU PAKET)', ''))) AS SicilAdi, ITM.SPECODE4 AS Marka, ITM.STGRPCODE AS UrunTipi, '' AS KampanyaFiyati, 
                         CASE WHEN (GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED) > 0 THEN 'VAR' ELSE 'YOK' END AS StokDurumu, ISNULL(TLF.TL * 0.7 * 0.9 * 1.2 * 2.67, 0) AS ListeFiyati, 
                         CAST(ISNULL(CASE WHEN ITM.PURCHDEDUCTCODE = '' THEN '0' ELSE ITM.PURCHDEDUCTCODE END, '0') AS FLOAT) AS MaliyetFiyati, ISNULL(TLF.TL * 0.7 * 0.9 * 1.1, 0) AS Nakit, 'TL' AS Kur, 
                         { fn CONCAT({ fn CONCAT('https://universaloto.com/foto/photos/', dbo.formatNumber(ITM.LOGICALREF)) }, '.jpg') } AS Resim, ITM.SPECODE2 AS Arac, CASE WHEN (GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED) 
                         > 10 THEN 10 ELSE ISNULL((GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED), 0) END AS StokAdeti, ISNULL(MAM.KAT1, '') AS Kategori1, 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, 
                         ITM.CAPIBLOCK_CREADEDDATE AS Eklenme, ITM.CAPIBLOCK_MODIFIEDDATE AS Guncellenme
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 (ITM.NAME NOT LIKE 'TAMPON%') AND (ITM.NAME NOT LIKE '%Davlumbaz%') AND (ITM.NAME NOT LIKE 'Disli%') AND (ITM.NAME NOT LIKE '%Basamak%') AND 
                         (NOT (MAM.KAT1 = 'Civic')) AND (GNT.ONHAND > 0) OR
                         (ITM.ACTIVE = 0) AND (TLF.TL IS NOT NULL) AND (ITM.NAME NOT LIKE 'TAMPON%') AND (ITM.NAME NOT LIKE '%Davlumbaz%') AND (ITM.NAME NOT LIKE 'Disli%') AND (ITM.NAME NOT LIKE '%Basamak%') AND 
                         (GNT.ONHAND > 0) AND (NOT (ITM.NAME LIKE 'MUHAFAZA KARTER%'))