CREATE VIEW dbo.GENEL_EKSIK_SIPARIS
AS
SELECT   ITM.CODE AS [1. OEM], ITM.PRODUCERCODE AS [2. OEM], ITM.NAME AS [1. ÜRÜN AÇIKLAMASI], ITM.NAME3 AS [2. ÜRÜN AÇIKLAMASI], 
                         ISNULL(GNT.ONHAND - GNT.RESERVED - GNT.TEMPIN, 0) AS [GÜNCEL STOK], ISNULL(SA.ADET2021, 0) AS [2022 SATIS], ISNULL(SA.ADET2022, 0) AS [2023 SATIS], ISNULL(SI.TOPLAM, 0) 
                         AS [IADE RAKAMI], ISNULL(SAL.AMOUNT, 0) AS [SON ALIM], ISNULL(CONVERT(VARCHAR(10), SAL.DATE_, 104), CAST('1900-01-01' AS DATE)) AS [ALIM TARIHI], ISNULL(PRC.PRICE, 0) 
                         * ISNULL
                             ((SELECT   TOP (1) RATES1
                                 FROM         dbo.L_DAILYEXCHANGES
                                 WHERE     (CRTYPE = PRC.CURRENCY)
                                 ORDER BY EDATE DESC), 1) AS [SATIS FIYATI], SS.DATE_ AS [SON SATIS]
FROM         dbo.LG_011_ITEMS AS ITM WITH (NOLOCK) LEFT OUTER JOIN
                         dbo.LG_011_PRCLIST AS PRC WITH (NOLOCK) ON PRC.CARDREF = ITM.LOGICALREF AND PRC.PTYPE = 2 LEFT OUTER JOIN
                         dbo.LV_011_08_GNTOTST AS GNT WITH (NOLOCK) ON GNT.STOCKREF = ITM.LOGICALREF AND GNT.INVENNO = - 1 LEFT OUTER JOIN
                         dbo.[00001_SATIS_ADETLERI] AS SA WITH (NOLOCK) ON SA.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                         dbo.[00001_SATIS_IADELER] AS SI WITH (NOLOCK) ON SI.STOCKREF = ITM.LOGICALREF LEFT OUTER JOIN
                         dbo.[01_SON_SATISLAR] AS SS WITH (NOLOCK) ON SS.STOCKREF = ITM.LOGICALREF AND SS.ORDER_ = 1 LEFT OUTER JOIN
                         dbo.[00001_SON_ALIM_LISTESI] AS SAL WITH (NOLOCK) ON SAL.STOCKREF = ITM.LOGICALREF AND SAL.ORDER_ = 1
WHERE     (ITM.ACTIVE = 0) AND (ITM.NAME <> '') AND (ITM.CODE <> 'TM.001') AND (ITM.CODE NOT LIKE 'ZZY%') AND (PRC.PRICE > 0)