CREATE view [dbo].[BM_211_STOKAYLIK] as
SELECT TOP 100 PERCENT  tarih, ambar,'0' [isyeri],
  CASE ITEMS2.CARDTYPE
               WHEN 1 THEN
                   'TICARI MAL'
               WHEN 2 THEN
                   'KARMA KOLI'
               WHEN 3 THEN
                   'DEPOZITOLU MAL'
               WHEN 4 THEN
                   'SABIT KIYMET'
               WHEN 10 THEN
                   'HAMMADDE'
               WHEN 11 THEN
                   'YARI MAMUL'
               WHEN 12 THEN
                   'MAMUL'
               WHEN 13 THEN
                   'TUKETIM MALI'
               WHEN 20 THEN
                   'GENEL MALZEME SINIFI'
               WHEN 21 THEN
                   'TABLOLU MALZEME SINIFI'
               ELSE
                   CONVERT(VARCHAR(20), CARDTYPE)
           END AS mal_tur,
ITEMS2.CODE mal_kod, ITEMS2.NAME mal_ad ,BIRIM.CODE birim ,

       ISNULL(
       (
           SELECT SUM(ONHAND)
           FROM LV_211_01_STINVTOT STINVTOT
           WHERE STINVTOT.STOCKREF = ITEMS2.LOGICALREF
                 AND STINVTOT.INVENNO = ambarnr
               --  AND STINVTOT.DATE_ <= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(m, 0, tarih)), 0)
     AND  YEAR(STINVTOT.DATE_) * 100 + MONTH(STINVTOT.DATE_)<=tarih
       ),
       0
             ) stok

FROM (
SELECT DISTINCT tarih ,whouse.NR ambarnr, WHOUSE.NAME ambar,MALZEMELER.STOCKREF  FROM (
select DISTINCT  YEAR(STLINE.DATE_) * 100 + MONTH(STLINE.DATE_) tarih from LG_211_01_STLINE STLINE
WHERE YEAR(STLINE.DATE_) * 100 + MONTH(STLINE.DATE_) <=YEAR(GETDATE())*100+MONTH(GETDATE())
 ) AS T
  CROSS JOIN  L_CAPIWHOUSE WHOUSE
  CROSS JOIN  LG_211_ITEMS ITEMS
  LEFT JOIN
  (
     SELECT STLINE.STOCKREF ,   STLINE.SOURCEINDEX

    FROM LG_211_01_STLINE STLINE WITH (NOLOCK)
        LEFT JOIN LG_211_01_STFICHE STFIC
            ON STLINE.STFICHEREF = STFIC.LOGICALREF
    WHERE (
              (STFIC.TRCODE IN ( 2, 3, 4, 7, 8, 9, 35, 36, 37, 38, 39 ))
              OR (STFIC.CLIENTREF = 0)
              OR (STFIC.TRCODE IN ( 1, 5, 6, 10, 26, 30, 31, 32, 33, 34 ))
              OR (STFIC.CLIENTREF = 0)
              OR (STFIC.TRCODE IN ( 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 50, 51 ))
          )
          AND (STFIC.CANCELLED = 0)
          AND STLINE.LINETYPE IN ( 0, 1, 5, 6, 8, 9, 10 )
          AND
          (
              (STFIC.STATUS IN ( 0, 1 ))
              OR (STFIC.TRCODE IN ( 11, 12, 13, 14, 25, 26, 50, 51 ))
          )
          AND
          (
              (STFIC.STATUS IN ( 0, 1 ))
              OR (STFIC.TRCODE = 25)
          )
          AND
          (
              (STFIC.PRODSTAT = 0)
              OR (STFIC.TRCODE IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ))
          )
          AND (STFIC.DEVIR IN ( 0, 1 ))
  ) as MALZEMELER ON MALZEMELER.STOCKREF=ITEMS.LOGICALREF AND MALZEMELER.SOURCEINDEX=NR
  WHERE WHOUSE.FIRMNR=211
  --ORDER BY tarih
  ) AS T2 LEFT JOIN LG_211_ITEMS ITEMS2 ON STOCKREF=ITEMS2.LOGICALREF
    LEFT OUTER JOIN LG_211_UNITSETL BIRIM WITH (NOLOCK)
            ON BIRIM.UNITSETREF = ITEMS2.UNITSETREF
               AND BIRIM.MAINUNIT = 1 
   order by tarih
GO