CREATE VIEW dbo.STOK_RAPOR_KRITIK
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, 
                  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
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.uc_aylik_satislar AS ASA ON ASA.STOCKREF = ITM.LOGICALREF AND ISNULL(GNT.ONHAND - GNT.TEMPIN - GNT.RESERVED, 0) - 2 <= ISNULL(ASA.SATIS, 0) 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
                      (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 (ISNULL(ASA.SATIS, 0) > 0) AND (ITM.LOSTFACTOR <> 1)