CREATE VIEW dbo.MALZEME_RAPORU
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, PRC.CREATEDBY, 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 = PRC.ITEMREF)
                                 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 = PRC.ITEMREF)
                                 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 = PRC.ITEMREF)), 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 = PRC.ITEMREF)
                                 ORDER BY FCL.REF DESC), '') AS SCOMP, ISNULL(ITM.BCOLOR, '') AS BCOLOR, ISNULL(SSM.SONIKI, 0) AS SONIKI, ISNULL(SSM.TOPLAM, 0) AS ITOP, ITM.REF
FROM            dbo.PRCLIST AS PRC LEFT OUTER JOIN
                         dbo.ITEMS AS ITM ON PRC.ITEMREF = ITM.REF LEFT OUTER JOIN
                         UNIVERSALDB.dbo.LG_011_ITEMS AS ITT ON ITM.LOGICALREF = ITT.LOGICALREF 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 = PRC.ITEMREF AND PRD.SIRA = 1 LEFT OUTER JOIN
                         dbo.MALZEMELER_SON_FIYATLAR AS MSF ON MSF.ITEMREF = PRC.ITEMREF LEFT OUTER JOIN
                         dbo.MALZEMELER_ACIKLAMA_VE_ALIM_FIYATLARI AS MAF ON MAF.ITEMREF = PRC.ITEMREF 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 LEFT OUTER JOIN
                         dbo.TOPLAM_SATIS_ADETLERI_VE_FATURA_SAYILARI_21 AS TFS21 ON TFS21.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                         UNIVERSALDB.dbo.[1aUNI_ortalama_eksik] AS ORT ON ORT.STOCKREF = ITM.LOGICALREF
WHERE        (PRC.PRIORITY_ = 1)