CREATE VIEW dbo.STOK_RAPOR_KARA_LISTE
AS
SELECT *
FROM     ((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(MSG.MIKTAR, 0) AS YOLDA, ITM.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
                   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
                   WHERE   (ITM.LOSTFACTOR = 1) AND ITM.[B2CCODE] <> 'K')
                  UNION ALL
                  (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(MSG.MIKTAR, 0) AS YOLDA, ITM.SHELFLIFE, ITM.LOGICALREF, CASE WHEN PLS.STOCKREF IS NULL THEN '' ELSE 'A+' END AS APLUS, 
                                     ITM.UETDSUNDEF
                   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
                   WHERE   (ITM.LOSTFACTOR <> 1) AND (ITM.ACTIVE = 1) AND ITM.[NAME] NOT LIKE '%ROTIL %' AND ITM.[NAME] NOT LIKE 'ROT %' AND ITM.[NAME] NOT LIKE 'PISTON %' AND ITM.[NAME] NOT LIKE 'SEKMAN %' AND 
                                     ITM.[NAME] NOT LIKE 'ROT %' AND ITM.[NAME] NOT LIKE 'MERKEZ DEB %' AND ITM.[NAME] NOT LIKE 'SALINCAK %' AND ITM.[NAME] NOT LIKE 'BALATA %' AND ITM.[NAME] NOT LIKE 'POMPA YAG %' AND 
                                     ITM.[NAME] NOT LIKE 'FILTRE YAG %' AND ITM.[NAME] NOT LIKE 'FILTRE YAKIT %' AND ITM.[NAME] NOT LIKE 'FILTRE HAVA %' AND ITM.[B2CCODE] <> 'K' AND (ITM.LOGICALREF NOT IN
                                         (SELECT CARDREF
                                          FROM      dbo.LG_011_PRCLIST
                                          WHERE   (PTYPE = 2) AND ACTIVE = 0)))) QQ