begin tran

EXEC STD_DropTempTable '#TempDocsInserir'
GO
EXEC STD_DropTempTable '#tmpArtigo'
GO 

EXEC STD_DropTempTable '#tmpArtigoCalc'
GO 

EXEC STD_DropTempTable '#tmpLinhasInternos'
GO 

EXEC STD_DropTempTable '#tmpINV_ValoresActuaisStock'
GO 

EXEC STD_DropTempTable '#tmpArtigo'
GO 

SELECT * INTO #tmpArtigo FROM Artigo
WHERE TratamentoLotes = 1

SELECT Id= NEWID(), A.* INTO #tmpINV_ValoresActuaisStock
FROM 
(
        SELECT DISTINCT  m.Artigo, Armazem, Localizacao, Lote, EstadoStock, DataStock = CONVERT(datetime,NULL),IdMovimentoStock = CONVERT(uniqueidentifier,NULL),Stock = CONVERT(DECIMAL(28,10),0)
        FROM INV_Movimentos m INNER JOIN #tmpArtigo tmp ON tmp.Artigo = m.Artigo
) A

UPDATE         stk
               SET            stk.DataStock                  =       mov.Data
                       ,       stk.IdMovimentoStock   =       mov.Id
                       ,       Stock                                 =       CASE    WHEN ISNULL(mov.Armazem, '') = '' AND ISNULL(mov.Localizacao, '') = ''
                                                                                                   THEN ISNULL(mov.StockLoc_Actual, 0)
                                                                                                   ELSE
                                                                                                           CASE    WHEN ISNULL(mov.Localizacao, '') <> ''
                                                                                                                          THEN
                                                                                                                                  CASE    WHEN mov.Lote = '<L01>'
                                                                                                                                                 THEN mov.StockLoc_Actual 
                                                                                                                                                 ELSE mov.StockLocLot_Actual
                                                                                                                                  END 
                                                                                                                          ELSE 
                                                                                                                                  CASE    WHEN ISNULL(mov.Armazem, '') <> ''
                                                                                                                                                 THEN
                                                                                                                                                         CASE    WHEN tmp.TratamentoLotes = 0 THEN mov.StockArm_Actual
                                                                                                                                                                        ELSE mov.StockArmLot_Actual
                                                                                                                                                         END 
                                                                                                                                  END
                                                                                                           END
                                                                                     END
        FROM           #tmpINV_ValoresActuaisStock stk               WITH (NOLOCK)
        INNER JOIN     #tmpArtigo tmp  on tmp.Artigo = stk.Artigo
        OUTER APPLY (
                                      SELECT         TOP 1 mv.*
                                      FROM           INV_Movimentos mv WITH(READPAST)
                                      WHERE          stk.Artigo                                    = mv.Artigo
                                                     AND ISNULL(stk.Armazem, '')            = ISNULL(mv.Armazem, '')
                                                     AND ISNULL(stk.Localizacao, '')        = ISNULL(mv.Localizacao, '')
                                                     AND ISNULL(stk.Lote, '')               = ISNULL(mv.Lote, '')
                                                     AND ISNULL(stk.EstadoStock, '')        = ISNULL(mv.EstadoStock, '')
                                      ORDER BY       [Data] DESC, [NumRegisto] DESC
                               ) mov 

        --- Artigos com tratamento de lotes e com stock em <L01> 
        SELECT distinct tmp.Artigo 
        INTO #tmpArtigoCalc
        FROM #tmpINV_ValoresActuaisStock tmp
        CROSS APPLY
        (
               SELECT TOP 1 TmpCross.Artigo FROM #tmpINV_ValoresActuaisStock TmpCross
               INNER JOIN #tmpArtigo tmpArt  on tmpArt.Artigo = TmpCross.Artigo
               WHERE tmpArt.TratamentoLotes = 1 AND (TmpCross.Lote = '<L01>' AND TmpCross.Stock<>0)
               AND TmpCross.Artigo = tmp.Artigo
        ) A
        WHERE tmp.Lote ='<L01>'
        --Order by tmp.Artigo, tmp.Armazem, tmp.Lote




        ---- Atualizacao ----

        update INV_Movimentos
        SET Lote = 'L01'
        WHERE Lote = '<L01>'
        AND Artigo IN (SELECT Artigo FROM #tmpArtigoCalc)
        
        update c
        SET c.Lote = 'L01'
        from INV_Custeio c
        inner join INV_GruposCustos gc on gc.Grupo = c.GrupoCustos
        WHERE c.Lote = '<L01>' and gc.ValorizacaoLote = 1
        AND c.Artigo IN (SELECT Artigo FROM #tmpArtigoCalc)

        DELETE FROM INV_ValoresActuaisStock
        WHERE Lote = '<L01>'
        AND Artigo IN (SELECT Artigo FROM #tmpArtigoCalc)
        
        UPDATE v
        SET v.Lote = 'L01'
        FROM INV_ValoresActuaisCusteio v 
        inner join INV_GruposCustos gc on gc.Grupo = v.GrupoCustos
        WHERE Lote = '<L01>' and gc.ValorizacaoLote = 1
        AND Artigo IN (SELECT Artigo FROM #tmpArtigoCalc)


commit

--Artigos Alterados
SELECT * from #tmpArtigoCalc