How to obtain Stock and PCM at that time from SQL instructions?

Besides the technological advances, the version 10 of the PRIMAVERA ERP brings a few important structural changes, such as:

• Separating the modules Logistic and Treasury;
• A total restructuring of the Inventory module that includes saving the stock and cost on the date.

On this article we will explore, using a few examples, how to obtain stock values at the inventory date, by using SQL instructions.

To learn more about the table structure in this module, we recommend that you see this article.

Example 1: Stock by stock and stock state

The stock movements are recorded on the table INV_Movimentos, and the correct order of the records is performed by the fields Data and NumRegisto. The field Data specifies when the movement occurs, and the field NumRegisto is a sequential field of the type Identity. There is also the field DataIntegracao that saves the date the movement is recorded.

The calculated stock values are kept in several fields according to its category, and for that, the categories Stock_ and Existencias_ exist. The first one refers to the stock value by state, and the calculated value is based on the field state EstadoStock. The columns Existencias represent the calculated value for all states, and the parameter "Stocks" is marked on the corresponding file, that is, it ignores all stock on virtual states, as the ones expected.

Besides the categories, the fields have the suffix ARM_ ARMLOT_ LOT_ LOC_ LOCLOT_, that specifies if it handles stock calculated to the Warehouse, Lot or Locations or a combination of both Warehouse Lot or Location Lot. There are also columns with no suffix that represent the global stock, regardless of the previous categories.

Every time you wish to get stock to a specific category, it is necessary to define its filter and select the corresponding stock column to obtain. For example, if you wish to get the stock for Warehouse A1 and Lot L01, you must retrieve the value of the column ExistenciasArmLot_Actual and it should be placed in the clause Where Warehouse and lot:

SELECT Artigo, Armazem, Lote 

FROM INV_Movimentos

WHERE Armazem = 'A1' AND Lote = 'L01'

Example of how to retrieve several stock values to a specific stock state:

--- Retrieve Stock from the STOCK STATE ---
-- Global stock of DISP state 
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 for Warehouse A1 of DISP state 
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 for Warehouse A1 and Lot L01 of DISP state 
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 for Warehouse A1, Location A1, Lot L01 of DISP state 
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

Example of how to retrieve several stock values:

--- Retrieve STOCK (all states that count to stock) ---
-- Global stock
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 for Warehouse A1
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 for Warehouse A1 and Lot L01
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 for Warehouse A1, Location A1, Lot L01
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

Example of how to retrieve several stock values for the Cost Group associated to the Warehouse:

-- GLOBAL Cost Group Stock
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 

-- GLOBAL Cost Group Stock and Lot L01 (In case the Cost Group has LOT handling) 
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 

Example 2: Cost movements

The cost movements are recorded on the table INV_Custeio and, like on the previous example, and the correct order of the records is performed by the fields Data and NumRegisto. The field Data specifies when the movement occurs, and the field NumRegisto is a sequential field of the type Identity. There is also the field DataIntegracao that saves the date the movement is recorded.

The calculated cost values are kept in two types of fields according its category, and for that, the categories CustoGrpCst and CustoGrpCstLot exist. The first one allows to obtain the item cost in a global way for the cost group, regardless of handling lots. The second one allows to retrieve the cost for the cost group and Lot, and it will only be filled in when the item and the cost group handle lots, that is, when the Lot field of the table presents a different value.

Example of how to retrieve the global and lot cost:

--- Get the cost for the GLOBAL Cost Group 
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 

---  Get the cost for the GLOBAL Cost Group and Lot 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 

Example 3: Date inventory value

In the previous examples, we only presented ways of obtaining stock or cost independently. However, we may wish to obtain the inventory value to a specific date that represents the stocks multiplied by the average cost price on that date.

To obtain that value by item, it is necessary to connect the two tables according to the following example:

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