Como obter Stock e PCM à data através de instruções SQL?
Além dos avanços tecnológicos, a versão 10 do ERP PRIMAVERA traz algumas mudanças estruturais importantes, tais como: Neste artigo vamos explorar, através de alguns exemplos, como obter valores de stock à data inventário recorrendo a instruções de SQL. Para saber mais informações sobre a estrutura de tabelas deste módulo, recomendamos a consulta do artigo. Os movimentos de stock ficam registados na tabela INV_Movimentos, sendo que a correta ordenação dos registos é efetuada pelos campos Data e NumRegisto. O campo Data indica quando ocorre o movimento, enquanto que o campo NumRegisto é um campo sequencial do tipo Identity. Existe ainda o campo DataIntegracao que guarda a data em que o movimento é registado. Os valores de stock calculados são mantidos em vários campos conforme a sua categoria, existindo para tal as categorias Stock_ e Existencias_. A primeira refere-se ao valor de stocks por estado, sendo que o valor calculado é baseado no estado do campo EstadoStock. As colunas Existencias representam o valor calculado para todos os estados, cujo parâmetro "Existências" está assinalado na respetiva ficha, ou seja, ignora todo o stock que se encontra em estados virtuais, como os previstos. Além das categorias, os campos têm um sufixo ARM_ ARMLOT_ LOT_ LOC_ LOCLOT_, que indicam se se tratam de stock calculado ao Armazém, Lote ou Localização ou a conjugação de dois Armazém e Lote ou Localização Lote. Existem também colunas sem sufixo que representam o stock global, independentemente das categorias anteriores. Sempre que se pretende obter stock a uma determinada categoria, é necessário definir o seu filtro e escolher a coluna correspondente do stock a obter. Por exemplo, se pretender obter o stock de existências para o Armazém A1 e Lote L01 deverá ser obtido o valor da coluna ExistenciasArmLot_Actual e este deve ser colocado na cláusula Where o Armazém e lote: Os movimentos de custeio ficam registados na tabela INV_Custeio e, tal como no exemplo anterior, a correta ordenação dos registos é efetuada pelos campos Data e NumRegisto. O campo Data indica quando ocorre o movimento, enquanto que o campo NumRegisto é um campo sequencial do tipo Identity. Existe ainda o campo DataIntegracao que guarda a data em que o movimento é registado. Os valores de custeio calculados são mantidos em dois tipos de campos conforme a sua categoria, existindo para isso os campos CustoGrpCst e CustoGrpCstLot. O primeiro permite obter o custo do artigo de uma forma global ao grupo de custo, independentemente de tratarem lotes. Já o segundo campo permite obter o custo ao grupo de custos e Lote, sendo que apenas estará preenchido quando o artigo e o grupo de custos tratarem lotes, ou seja, quando o campo Lote da tabela apresentar um valor diferente. Nos exemplos referidos anteriormente foram apenas apresentadas formas de como obter o stock ou o custeio de forma independente. No entanto, muitas vezes pretende-se obter o valor de inventário a uma determinada data que representa as existências multiplicadas pelo preço de custo médio nessa data. Para se obter este valor por artigo é necessário ligar as duas tabelas conforme o seguinte exemplo:Exemplo 1: Stock por estado de stock e existências
SELECT Artigo, Armazem, Lote
FROM INV_Movimentos
WHERE Armazem = 'A1' AND Lote = 'L01'
Exemplo de como obter vários valores de stock para um determinado estado de stock:
--- Obter Stock ao ESTADO de STOCK ---
-- Stock global do estado DISP
SELECT A.Artigo, STK.EstadoStock, STK.Stock_Actual
FROM Artigo A
OUTER APPLY
(
SELECT TOP 1 M.* FROM INV_Movimentos M
WHERE M.Artigo = A.Artigo
AND M.EstadoStock = 'DISP'
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
-- Stock do armazém A1 do estado DISP
SELECT A.Artigo, STK.EstadoStock, STK.Armazem, STK.StockArm_Actual
FROM Artigo A
OUTER APPLY
(
SELECT TOP 1 M.* FROM INV_Movimentos M
WHERE M.Artigo = A.Artigo
AND M.EstadoStock = 'DISP' AND M.Armazem = 'A1'
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
-- Stock do armazém A1 e Lote L01 do estado DISP
SELECT A.Artigo, STK.EstadoStock, STK.Armazem, STK.Lote, STK.StockArmLot_Actual
FROM Artigo A
OUTER APPLY
(
SELECT TOP 1 M.* FROM INV_Movimentos M
WHERE M.Artigo = A.Artigo
AND M.EstadoStock = 'DISP' AND M.Armazem = 'A1' AND Lote = 'L01'
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
-- Stock do armazém A1, Localização A1, Lote L01 do estado DISP
SELECT A.Artigo, STK.EstadoStock, STK.Armazem, STK.Lote,STK.Localizacao, STK.StockLocLot_Actual
FROM Artigo A
OUTER APPLY
(
SELECT TOP 1 M.* FROM INV_Movimentos M
WHERE M.Artigo = A.Artigo
AND M.EstadoStock = 'DISP' AND M.Armazem = 'A1' AND Lote = 'L01' AND M.Localizacao = 'A1'
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
Exemplo de como obter vários valores de existências:
--- Obter Stock de existências de STOCK (todos os estados que contam para existências) ---
-- Stock global de existências
SELECT A.Artigo, STK.Existencias_Actual
FROM Artigo A
OUTER APPLY
(
SELECT TOP 1 M.* FROM INV_Movimentos M
WHERE M.Artigo = A.Artigo
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
-- Stock do armazém A1 de existências
SELECT A.Artigo, STK.Armazem, STK.ExistenciasArm_Actual
FROM Artigo A
OUTER APPLY
(
SELECT TOP 1 M.* FROM INV_Movimentos M
WHERE M.Artigo = A.Artigo
AND M.Armazem = 'A1'
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
-- Stock do armazém A1 e Lote L01 de existências
SELECT A.Artigo, STK.Armazem, STK.Lote, STK.ExistenciasArmLot_Actual
FROM Artigo A
OUTER APPLY
(
SELECT TOP 1 M.* FROM INV_Movimentos M
WHERE M.Artigo = A.Artigo
AND M.Armazem = 'A1' AND Lote = 'L01'
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
-- Stock do armazém A1, Localização A1, Lote L01 de existências
SELECT A.Artigo, STK.Armazem, STK.Lote,STK.Localizacao, STK.ExistenciasLocLot_Actual
FROM Artigo A
OUTER APPLY
(
SELECT TOP 1 M.* FROM INV_Movimentos M
WHERE M.Artigo = A.Artigo
AND M.Armazem = 'A1' AND Lote = 'L01' AND M.Localizacao = 'A1'
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
Exemplo de como obter vários valores de existências ao Grupo de Custo associado ao Armazém:
-- Stock do do Grupo de Custo GLOBAL
SELECT A.Artigo, STK.GrupoCustos, STK.ExistenciasGrpCst_Actual
FROM Artigo A
OUTER APPLY
(
SELECT TOP 1 M.*, Arm.GrupoCustos FROM INV_Movimentos M
INNER JOIN Armazens Arm on Arm.Armazem = M.Armazem
WHERE M.Artigo = A.Artigo
AND Arm.GrupoCustos = 'GLOBAL'
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
-- Stock do do Grupo de Custo GLOBAL e Lote L01 (Caso o Grupo de Custo tenha tratamento ao LOTE)
SELECT A.Artigo, STK.GrupoCustos, STK.Lote, STK.ExistenciasGrpCstLot_Actual
FROM Artigo A
OUTER APPLY
(
SELECT TOP 1 M.*, Arm.GrupoCustos FROM INV_Movimentos M
INNER JOIN Armazens Arm on Arm.Armazem = M.Armazem
WHERE M.Artigo = A.Artigo
AND Arm.GrupoCustos = 'GLOBAL' AND M.Lote = 'L01'
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
Exemplo 2: Movimentos de custeio
Exemplo de como obter o custo global e ao lote:
--- Obter o custo para o Grupo de Custo GLOBAL
SELECT A.Artigo,STK.GrupoCustos, STK.CustoGrpCstMBase_Actual
FROM Artigo A
OUTER APPLY
(
SELECT TOP 1 M.* FROM INV_Custeio M
WHERE M.Artigo = A.Artigo
AND M.GrupoCustos = 'GLOBAL'
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
--- Obter o custo para o Grupo de Custo GLOBAL e Lote L01
SELECT A.Artigo,STK.GrupoCustos, STK.Lote, STK.CustoGrpCstLotMBase_Actual
FROM Artigo A
OUTER APPLY
(
SELECT TOP 1 M.* FROM INV_Custeio M
WHERE M.Artigo = A.Artigo
AND M.GrupoCustos = 'GLOBAL' AND Lote = 'L01'
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
Exemplo 3: Valor de inventário à data
SELECT STK.Artigo,STK.GrupoCustos, STK.Lote, STK.Existencias, CUST.Custo, ValorInventario = STK.Existencias * CUST.Custo
FROM INV_GruposCustos G
OUTER APPLY
(
SELECT TOP 1 M.Artigo, M.Lote, M.Data, Arm.GrupoCustos, Existencias = CASE WHEN A.TratamentoLotes = 1 AND G.ValorizacaoLote = 1 THEN M.ExistenciasGrpCstLot_Actual ELSE M.ExistenciasGrpCst_Actual END
FROM INV_Movimentos M
INNER JOIN Artigo A ON A.Artigo = M.Artigo
INNER JOIN Armazens Arm on Arm.Armazem = M.Armazem
WHERE G.Grupo = Arm.GrupoCustos
AND M.Data <= '2019-01-01'
ORDER BY M.Data DESC, M.NumRegisto DESC
) STK
OUTER APPLY
(
SELECT TOP 1 Custo = CASE WHEN M.Lote= '' THEN M.CustoGrpCstMBase_Actual ELSE M.CustoGrpCstLotMBase_Actual END
FROM INV_Custeio M
INNER JOIN Artigo A ON A.Artigo = M.Artigo
WHERE (M.Artigo = STK.Artigo AND M.GrupoCustos = STK.GrupoCustos AND ((M.Lote = STK.Lote AND G.ValorizacaoLote =1 ) OR G.ValorizacaoLote = 0))
AND M.Data <= STK.Data
ORDER BY M.Data DESC, M.NumRegisto DESC
) CUST
login para deixar a sua opinião.