CREATE VIEW dbo.[MALZEME_LISTESI_ENSON]
AS
SELECT        UML.CODE, ITM.CODE AS CODE2, CASE WHEN ITM.[NAME] IS NULL THEN ITS.ITEMNAME ELSE ITM.[NAME] 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 ITS.ACTV = 1 THEN 'YOK' ELSE 'VAR' END AS AKTIF
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 AND ITM.ACTIVE = 0 LEFT OUTER JOIN
                         dbo.MALZEME_SIPARIS_GECMISI AS MSG ON MSG.ITEMREF = ITS.REF AND MSG.ORD = 1