CREATE VIEW dbo.MALZEME_LISTESI_HIZLI AS SELECT        *
FROM            ((SELECT        UML.CODE, ITM.CODE AS CODE2, CASE WHEN ITM.[NAME] IS NULL THEN ITS.ITEMNAME ELSE CONCAT(CONCAT(ITM.[NAME], ' '), ITM.[NAME3]) END AS ITEMNAME, UML.ITEMNAME_EN, 
                                                     CASE WHEN ITM.STGRPCODE IS NULL THEN ITS.GROUPCODE ELSE ITM.STGRPCODE END AS GROUPCODE, ISNULL(PRD.PRODUCERCODE, '') AS PROCODE, ISNULL(PRD2.PRODUCERCODE, '') AS PROCODE2, 
                                                     UML.FIRMCODE, UML.ALIMFIYAT, UML.ALIMKUR, UML.LASTPRICE, UML.LASTCURRENCY, UML.SALEPRICE, ITS.LINEEXP1, ITS.LINEEXP2, UML.IHRSAT, UML.IHRCURRENCY, UML.SALECURRENCY, 
                                                     UML.ORTALAMA, UML.STOCK, UML.EKSIK, UML.ADET2022, UML.FAT2022, UML.ADET2021, UML.FAT2021, MSG.DURUM, MSG.MIKTAR, ISNULL
                                                         ((SELECT        COUNT(*) AS Expr1
                                                             FROM            dbo.FICHE_LINES AS FCL
                                                             WHERE        (STATUS NOT IN (1, 2)) AND (ITEMREF = ITS.REF)), 0) AS SATIR, MSG.SCOMP, ITS.BCOLOR, UML.SONIKI, UML.ITOP, ITS.REF, CASE WHEN ITM.ACTIVE = 1 OR UML.PRICE IS NULL THEN 'YOK' ELSE 'VAR' END AKTIF, 
                                                     ITM.LOGICALREF, ISNULL(UML.[AIDE2022], 0) AS [AIDE2022], ITS.BENZER, XT.ETIKET_ACIKLAMA, XT.MODEL_BILGI, UML.ADET2020, ROUND(UML.KAMPANYATL, 2) AS KAMPANYATL, 
                                                     ROUND(UML.KAMPANYAUSD, 2) AS KAMPANYAUSD, SP.TOPLAM_SIPARIS AS AMOUNT, SP.KULLANICILAR AS KULL, ITM.EXPCTGNO, ITM.UETDSUNDEF, ITS.ALTERNATIF_FIYAT, ITS.ALTERNATIF_FIRMA
                            FROM            dbo.ITEMS AS ITS LEFT OUTER JOIN
                                                     dbo.[1aUNI_malzeme_listesi] AS UML ON UML.REF = ITS.REF LEFT OUTER JOIN
                                                     dbo.MALZEME_URETICI_KOD_LISTESI AS PRD ON PRD.REF = ITS.REF AND PRD.SIRA = 1 AND PRD.STS = 1 LEFT OUTER JOIN
                                                     dbo.MALZEME_URETICI_KOD_LISTESI AS PRD2 ON PRD2.REF = ITS.REF AND PRD2.SIRA = 1 AND PRD2.STS = 2 LEFT OUTER JOIN
                                                     UNIVERSALDB.dbo.LG_011_ITEMS AS ITM ON ITM.LOGICALREF = ITS.LOGICALREF LEFT OUTER JOIN
                                                     dbo.MALZEME_SIPARIS_GECMISI AS MSG ON MSG.ITEMREF = ITS.REF AND MSG.ORD = 1 LEFT OUTER JOIN
                                                     UNIVERSALDB.dbo.LG_XT1001_011 AS XT ON XT.PARLOGREF = ITM.LOGICALREF LEFT OUTER JOIN
                                                     UNIVERSALDB.dbo.STOK_RAPOR_SIPARISLER_TOPLAM_DEGER AS SP ON SP.STOCKREF = ITS.LOGICALREF AND SP.STOCKREF <> 0
                            WHERE        ITS.SIL = 0 AND (UML.CODE IS NOT NULL AND UML.CODE <> '' OR
                                                     ITM.CODE IS NOT NULL AND ITM.CODE <> ''))
                         UNION ALL
                         (SELECT        '' AS CODE, ITM.CODE AS CODE2, CONCAT(CONCAT(ITM.[NAME], ' '), ITM.NAME3) AS ITEMNAME, '' AS ITEMNAME_EN, STGRPCODE AS GROUPCODE, '' AS PROCODE, '' AS PROCCODE2, '' AS FIRMCODE, 
                                                   0 AS ALIMFIYAT, '' AS ALIMKUR, 0 AS LASTPRICE, '' AS LASTCURRENCY, ISNULL(PRI.PRICE, 0) AS SALEPRICE, '' AS LINEEXP1, '' AS LINEEXP2, 0 AS IHRSAT, '' AS IHRCURRENCY, 
                                                   CASE WHEN PRI.CURRENCY = 1 THEN 'USD' WHEN PRI.CURRENCY = 13 THEN 'CNY' WHEN PRI.CURRENCY = 20 THEN 'EUR' WHEN PRI.CURRENCY = 160 THEN 'TL' ELSE 'USD' END AS SALECURRENCY, 
                                                   ISNULL(ORT.ORTALAMA, 0) AS ORTALAMA, ISNULL(GNT.ONHAND - GNT.RESERVED - GNT.TEMPIN, 0) AS STOCK, ISNULL(ORT.ORT_EKSIK, 0) AS EKSIK, ISNULL(TFS22.ADET, 0) AS ADET2022, ISNULL(TFS22.FAT, 0) 
                                                   AS FAT2022, ISNULL(TFS21.ADET, 0) AS ADET2021, ISNULL(TFS21.FAT, 0) AS FAT2021, MSG.DURUM, MSG.MIKTAR, 0 AS SATIR, MSG.SCOMP, '' AS BCOLOR, ISNULL(SSM.SONIKI, 0) AS SONIKI, 
                                                   ISNULL(SSM.TOPLAM, 0) AS ITOP, 0 AS REF, 'EKSIK' AS AKTIF, ITM.LOGICALREF, ISNULL(UML.[AIDE2022], 0) AS [AIDE2022], '' AS BENZER, XT.ETIKET_ACIKLAMA, XT.MODEL_BILGI, ISNULL(TFS20.ADET, 0) 
                                                   AS ADET2020, ROUND(UML.KAMPANYATL, 2) AS KAMPANYATL, ROUND(UML.KAMPANYAUSD, 2) AS KAMPANYAUSD, SP.TOPLAM_SIPARIS AS AMOUNT, SP.KULLANICILAR AS KULL, ITM.EXPCTGNO, 
                                                   ITM.UETDSUNDEF, NULL AS ALTERNATIF_FIYAT, NULL AS ALTERNATIF_FIRMA
                          FROM            [UNIVERSALDB].[dbo].[LG_011_ITEMS] AS ITM LEFT OUTER JOIN
                                                   dbo.[1aUNI_malzeme_listesi] AS UML ON UML.REF = ITM.LOGICALREF LEFT OUTER JOIN
                                                   UNIVERSALDB.dbo.LV_011_08_GNTOTST AS GNT ON GNT.STOCKREF = ITM.LOGICALREF AND GNT.INVENNO = - 1 LEFT OUTER JOIN
                                                   UNIVERSALDB.dbo.LG_011_PRCLIST AS PRI ON PRI.CARDREF = ITM.LOGICALREF AND PRI.PTYPE = 2 AND PRI.PRIORITY = 0 LEFT OUTER JOIN
                                                   UNIVERSALDB.dbo.[00001_SATIS_IADELER] AS SSM ON SSM.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                                                   dbo.TOPLAM_SATIS_ADETLERI_VE_FATURA_SAYILARI_22 AS TFS22 ON TFS22.STOCKREF = ITM.LOGICALREF AND TFS22.YEAR_ = 2025 LEFT OUTER JOIN
                                                   dbo.TOPLAM_SATIS_ADETLERI_VE_FATURA_SAYILARI_21 AS TFS21 ON TFS21.STOCKREF = ITM.LOGICALREF AND TFS21.YEAR_ = 2024 LEFT OUTER JOIN
                                                   dbo.TOPLAM_SATIS_ADETLERI_VE_FATURA_SAYILARI_20 AS TFS20 ON TFS20.STOCKREF = ITM.LOGICALREF AND TFS20.YEAR_ = 2023 LEFT OUTER JOIN
                                                   UNIVERSALDB.dbo.[1aUNI_ortalama_eksik] AS ORT ON ORT.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                                                   dbo.MALZEME_SIPARIS_GECMISI AS MSG ON MSG.LOGICALREF = ITM.LOGICALREF AND MSG.ORD = 1 LEFT OUTER JOIN
                                                   UNIVERSALDB.dbo.LG_XT1001_011 AS XT ON XT.PARLOGREF = ITM.LOGICALREF LEFT OUTER JOIN
                                                   UNIVERSALDB.dbo.STOK_RAPOR_SIPARISLER_TOPLAM_DEGER AS SP ON SP.STOCKREF = ITM.LOGICALREF AND SP.STOCKREF <> 0
                          WHERE        ITM.CODE NOT IN
                                                       (SELECT        CODE2
                                                         FROM            [IHR].[dbo].[MALZEME_LISTESI_ENSON]
                                                         WHERE        CODE2 IS NOT NULL AND CODE2 <> '') AND ITM.ACTIVE = 0 AND ITM.[LOGICALREF] IN
                                                       (SELECT DISTINCT CARDREF
                                                         FROM            [UNIVERSALDB].[dbo].[LG_011_PRCLIST]
                                                         WHERE        PTYPE = 2))) QQ