CREATE VIEW dbo.STOK_RAPOR_HAREKETSIZ
AS
SELECT        ITM.CODE AS OEM1, ITM.PRODUCERCODE AS OEM2, { fn CONCAT({ fn CONCAT(ITM.NAME, ' ') }, ITM.NAME3) } AS ACIKLAMA, ISNULL(GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED, 0) AS GSTOK, ISNULL(TFS1.ADET, 0) 
                         AS SATIS2024, ISNULL(TFS2.ADET, 0) AS SATIS2023, ISNULL(TFS3.ADET, 0) AS SATIS2022, ISNULL(SSM.AIDE2022, 0) AS BUYILIADE, ISNULL(SSM.TOPLAM, 0) AS TOPIADE, ISNULL(SAL.AMOUNT, 0) AS SONADET, 
                         ISNULL(SAL.DATE_, 0) AS SONTARIH, ISNULL(SAL2.DATE_, 0) AS ILKTARIH, ISNULL(ASA.SATIS, 0) AS SONSATIS, ISNULL(NULLIF (MSG.MIKTAR, 0), ISNULL(ORF_SUM.ORF_TOTAL, 0)) AS YOLDA, ITM.B2CCODE AS SHELFLIFE, 
                         ITM.LOGICALREF, ITM.UETDSUNDEF, MSF.PRICE, MSF.CURRENCY, MSF.DEFINITION_, ITM.ORGLOGOID
FROM            dbo.LG_011_ITEMS AS ITM LEFT OUTER JOIN
                         dbo.LV_011_08_GNTOTST AS GNT ON GNT.STOCKREF = ITM.LOGICALREF AND GNT.INVENNO = - 1 LEFT OUTER JOIN
                         IHR.dbo.toplam_satis_adetleri1 AS TFS1 ON TFS1.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                         IHR.dbo.toplam_satis_adetleri2 AS TFS2 ON TFS2.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                         IHR.dbo.toplam_satis_adetleri3 AS TFS3 ON TFS3.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                         dbo.toplam_iade_adetleri AS SSM ON SSM.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                         dbo.son_alimlar AS SAL ON SAL.STOCKREF = ITM.LOGICALREF AND SAL.ORDER_ = 1 LEFT OUTER JOIN
                         dbo.son_alimlar AS SAL2 ON SAL2.STOCKREF = ITM.LOGICALREF AND SAL2.ORDER2_ = 1 LEFT OUTER JOIN
                         dbo.yillik_satis_adetleri AS ASA ON ASA.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                         IHR.dbo.MALZEME_SIPARIS_GECMISI AS MSG ON MSG.LOGICALREF = ITM.LOGICALREF AND MSG.ORD = 1 LEFT OUTER JOIN
                         dbo.aylik_ortalama_satis AS AOS ON AOS.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                         IHR.dbo.IHR_MALZEME_SON_FIYAT_FIRMA AS MSF ON MSF.LOGICALREF = ITM.LOGICALREF LEFT OUTER JOIN
                         dbo.TUM_YILLAR_SATIS_ADETLERI AS TMS ON TMS.LOGICALREF = ITM.LOGICALREF LEFT OUTER JOIN
                             (SELECT        STOCKREF, SUM(AMOUNT) AS ORF_TOTAL
                               FROM            dbo.LG_011_08_ORFLINE
                               WHERE        (TRCODE = 2) AND (CANCELLED = 0) AND (STATUS = 1)
                               GROUP BY STOCKREF) AS ORF_SUM ON ORF_SUM.STOCKREF = ITM.LOGICALREF
WHERE        (ITM.ACTIVE = 0) AND (ITM.LOGICALREF IN
                             (SELECT        CARDREF
                               FROM            dbo.LG_011_PRCLIST
                               WHERE        (PTYPE = 2) AND (ACTIVE = 0))) AND (ASA.SATIS IS NULL) AND (ITM.LOSTFACTOR <> 1) AND (ISNULL(GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED, 0) > 0) AND (CAST(ITM.CAPIBLOCK_CREADEDDATE AS DATE) 
                         < CAST(DATEADD(YEAR, - 1, GETDATE()) AS DATE)) AND (TMS.AMOUNT IS NOT NULL) AND (TMS.AMOUNT > 0)