CREATE VIEW dbo.STOK_RAPOR_YOLDA
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, CASE WHEN SAL.DATE_ > MSG.CREATE_DATE THEN 0 ELSE ISNULL(MSG.MIKTAR, 0) END AS YOLDA, ITM.B2CCODE AS SHELFLIFE, ITM.LOGICALREF, CASE WHEN PLS.STOCKREF IS NULL OR
                  ISNULL(GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED, 0) > 0 THEN '' ELSE 'A+' END AS APLUS, ITM.UETDSUNDEF, MSF.PRICE, MSF.CURRENCY, MSF.DEFINITION_, ITM.ORGLOGOID, 
                  CASE WHEN ISNULL(GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED, 0) > 0 THEN NULL ELSE CAST
                      ((SELECT TOP (1) ORDERDATE
                        FROM      dbo.STOK_RAPOR_SIPARISLER
                        WHERE   (DEL_ = 0) AND (ORDERDATE IS NOT NULL) AND (ORDERDATE <> '') AND (STOCKREF = ITM.LOGICALREF)
                        ORDER BY ORDERDATE DESC) AS DATETIME) END AS ORDERDATE, ISNULL(SBT.GunSay, 0) AS GunSay
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
                  IHR.dbo.MALZEME_SIPARIS_GECMISI AS MSG ON MSG.LOGICALREF = ITM.LOGICALREF AND MSG.ORD = 1 LEFT OUTER JOIN
                  dbo.STR_APLUS_SECIM_SIRALI AS PLS ON PLS.STOCKREF = ITM.LOGICALREF AND PLS.SIRA = 1 LEFT OUTER JOIN
                  IHR.dbo.IHR_MALZEME_SON_FIYAT_FIRMA AS MSF ON MSF.LOGICALREF = ITM.LOGICALREF LEFT OUTER JOIN
                  dbo.SON_BITIS_TARIHI AS SBT ON SBT.STOCKREF = ITM.LOGICALREF
WHERE  (ITM.ACTIVE = 0) AND (ITM.LOGICALREF IN
                      (SELECT CARDREF
                       FROM      dbo.LG_011_PRCLIST
                       WHERE   (PTYPE = 2) AND (ACTIVE = 0)))