CREATE VIEW dbo.MALZEME_LISTESI_ESLESTIRME
AS
SELECT        ITM.CODE, ISNULL(ITT.CODE, '') AS CODE2, CASE WHEN ITT.[NAME] IS NULL THEN ITM.ITEMNAME ELSE ITT.[NAME] END AS ITEMNAME, ITM.ITEMNAME_EN, CASE WHEN ITT.STGRPCODE IS NULL 
                         THEN ITM.GROUPCODE ELSE ITT.STGRPCODE END AS GROUPCODE, ISNULL(PRD.PRODUCERCODE, '') AS PROCODE, ISNULL(CMP.DEFINITION_, '') AS FIRMCODE, ISNULL(PRC.PRICE, 0) AS ALIMFIYAT, 
                         CASE WHEN PRC.CURRENCY = 1 THEN 'USD' WHEN PRC.CURRENCY = 13 THEN 'CNY' WHEN PRC.CURRENCY = 20 THEN 'EUR' WHEN PRC.CURRENCY = 160 THEN 'TL' ELSE 'USD' END AS ALIMKUR, ISNULL
                             ((SELECT        PRICE
                                 FROM            dbo.PRCLIST
                                 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, ISNULL
                             ((SELECT        TOP (1) OST.DURUM
                                 FROM            dbo.FICHE_LINES AS FCL LEFT OUTER JOIN
                                                          dbo.ORDER_STATUS AS OST ON OST.REF = FCL.STATUS
                                 WHERE        (FCL.STATUS NOT IN (1, 2)) AND (FCL.ITEMREF = ITM.REF)
                                 ORDER BY FCL.REF DESC), '') AS DURUM, ISNULL
                             ((SELECT        TOP (1) AMOUNT
                                 FROM            dbo.FICHE_LINES AS FCL
                                 WHERE        (STATUS NOT IN (1, 2)) AND (ITEMREF = ITM.REF)
                                 ORDER BY REF DESC), 0) AS MIKTAR, ISNULL
                             ((SELECT        COUNT(*) AS Expr1
                                 FROM            dbo.FICHE_LINES AS FCL
                                 WHERE        (STATUS NOT IN (1, 2)) AND (ITEMREF = ITM.REF)), 0) AS SATIR, ISNULL
                             ((SELECT        TOP (1) CPN.DEFINITION_
                                 FROM            dbo.FICHE_LINES AS FCL LEFT OUTER JOIN
                                                          dbo.COMPANIES AS CPN ON CPN.REF = FCL.COMPANYREF
                                 WHERE        (FCL.STATUS NOT IN (1, 2)) AND (FCL.ITEMREF = ITM.REF)
                                 ORDER BY FCL.REF DESC), '') AS SCOMP, ITM.BCOLOR, ISNULL(SSM.SONIKI, 0) AS SONIKI, ISNULL(SSM.TOPLAM, 0) AS ITOP, ITM.REF
FROM            dbo.ITEMS AS ITM LEFT OUTER JOIN
                         UNIVERSALDB.dbo.LG_011_ITEMS AS ITT ON ITM.LOGICALREF = ITT.LOGICALREF LEFT OUTER JOIN
                         dbo.PRCLIST AS PRC ON PRC.ITEMREF = ITM.REF AND PRC.ORDER_ =
                             (SELECT        MAX(ORDER_) AS Expr1
                               FROM            dbo.PRCLIST
                               WHERE        (ITEMREF = ITM.REF)) LEFT OUTER JOIN
                         dbo.COMPANIES AS CMP ON CMP.REF = PRC.COMPANYREF LEFT OUTER JOIN
                         dbo.MALZEME_URETICI_KOD_LISTESI AS PRD ON PRD.REF = ITM.REF AND PRD.SIRA = 1 LEFT OUTER JOIN
                         dbo.MALZEMELER_SON_FIYATLAR AS MSF ON MSF.ITEMREF = ITM.REF 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
                         UNIVERSALDB.dbo.[1aUNI_ortalama_eksik] AS ORT ON ORT.STOCKREF = ITM.LOGICALREF
WHERE        (ITM.LOGICALREF = 0)