CREATE VIEW dbo.MALZEME_LISTESI
AS
SELECT        ITM.CODE, ITM.ITEMNAME_EN, ISNULL
                             ((SELECT        DEFINITION_
                                 FROM            dbo.COMPANIES AS CMP
                                 WHERE        (REF =
                                                              (SELECT        TOP (1) COMPANYREF
                                                                FROM            dbo.PRCLIST AS PRC
                                                                WHERE        (ITEMREF = ITM.REF)
                                                                ORDER BY REF DESC))), '') AS FIRMCODE, ISNULL
                             ((SELECT        PRICE
                                 FROM            dbo.PRCLIST
                                 WHERE        (REF = MSF2.REF)), 0) AS ALIMFIYAT, ISNULL
                             ((SELECT        CASE WHEN CURRENCY = 1 THEN 'USD' WHEN CURRENCY = 13 THEN 'CNY' WHEN CURRENCY = 20 THEN 'EUR' WHEN CURRENCY = 160 THEN 'TL' ELSE 'USD' END AS Expr1
                                 FROM            dbo.PRCLIST AS PRCLIST_1
                                 WHERE        (REF = MSF2.REF)), 0) AS ALIMKUR, ISNULL
                             ((SELECT        PRICE
                                 FROM            dbo.PRCLIST AS PRCLIST_2
                                 WHERE        (REF = MSF.REF)), 0) AS LASTPRICE, ISNULL
                             ((SELECT        CASE WHEN CURRENCY = 1 THEN 'USD' WHEN CURRENCY = 13 THEN 'CNY' WHEN CURRENCY = 20 THEN 'EUR' WHEN CURRENCY = 160 THEN 'TL' ELSE 'USD' END AS Expr1
                                 FROM            dbo.PRCLIST AS PRCLIST_1
                                 WHERE        (REF = MSF.REF)), 0) AS LASTCURRENCY, ISNULL(PRI.PRICE, 0) AS SALEPRICE, ITM.LINEEXP1, ITM.LINEEXP2, MSF.IHRSAT, 
                         CASE WHEN MSF.IHRCURRENCY = 1 THEN 'USD' WHEN MSF.IHRCURRENCY = 13 THEN 'CNY' WHEN MSF.IHRCURRENCY = 20 THEN 'EUR' WHEN MSF.IHRCURRENCY = 160 THEN 'TL' ELSE 'USD' END 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, ITM.BCOLOR, ISNULL(SSM.SONIKI, 0) AS SONIKI, ISNULL(SSM.TOPLAM, 0) AS ITOP, ITM.REF, ISNULL(SSM.AIDE2022, 0) AS AIDE2022, 
                         ITM.REF AS Expr1, ISNULL(TFS20.ADET, 0) AS ADET2020, ITM.KAMPANYATL, ITM.KAMPANYAUSD, PRI.PRICE
FROM            dbo.ITEMS AS ITM LEFT OUTER JOIN
                         dbo.MALZEMELER_SON_IKI_FIYAT AS MSF2 ON MSF2.ITEMREF = ITM.REF AND MSF2.SIRA = 1 LEFT OUTER JOIN
                         dbo.MALZEMELER_SON_IKI_FIYAT AS MSF ON MSF.ITEMREF = ITM.REF AND MSF.SIRA = 2 LEFT OUTER JOIN
                         dbo.COMPANIES AS CMP ON CMP.REF = MSF2.COMPANYREF 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 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