How to retrieve inventory values using SQL statements?

Last changed in 02/12/2020

Along with technological advances, version 10 of the PRIMAVERA ERP brings with it some important structural changes, such as:

The separation of Logistics and Cash Management into different modules;
A complete redesign of the Inventory module.

In this article we explore, through some examples, how to obtain inventory values using SQL statements in order to better understand how the tables are structured.

The values to be obtained refer to:

Stocks
Cost
Valuations
Variations

First of all, please bear in mind that all movements that integrate in the Inventory module are required to be registered in the INV_Origens table. This table will hold all the necessary information regarding the origin of each movement:

The module and document that originated it;
The original values in the movement currency;
The original quantities in the movement unit.

Example 1: Stock movements

Stock movements are recorded in the INV_Movimentos table and records are correctly sorted using the Data and NumRegisto fields. The Data field indicates when the movement occurs, while the NumRegisto field is a sequential field of type Identity. There is also another field that stores the date when the movement is actually recorded, the DataIntegracao field.

Example of retrieving a stock statement for article 'A0001' from the DEMO company:

/* Stock movements (INV_Movimentos) */
SELECT		orig.Documento, m.Data, m.DataIntegracao, m.NumRegisto, m.Artigo, m.Armazem, m.Localizacao, m.Lote, m.EstadoStock, m.TipoMovimento, orig.Transferencia
	,	m.Id, m.IdOrigem, orig.IdChave1, orig.IdChave2, orig.IdChave3
	,	orig.AplicaValorizacao

	--	Moved quantity (base unit)
	,	Quantidade			= FORMAT(m.Quantidade, '0.00')

	--	Stock
	,	Stock_Anterior			= FORMAT(m.Stock_Anterior, '0.00')
	,	Stock_Actual			= FORMAT(m.Stock_Actual, '0.00')
	,	StockArm_Anterior		= FORMAT(m.StockArm_Anterior, '0.00')
	,	StockArm_Actual			= FORMAT(m.StockArm_Actual, '0.00')
	,	StockLoc_Anterior		= FORMAT(m.StockLoc_Anterior, '0.00')
	,	StockLoc_Actual			= FORMAT(m.StockLoc_Actual, '0.00')
	,	StockLot_Anterior		= FORMAT(m.StockLot_Anterior, '0.00')
	,	StockLot_Actual			= FORMAT(m.StockLot_Actual, '0.00')
	,	StockArmLot_Anterior		= FORMAT(m.StockArmLot_Anterior, '0.00')
	,	StockArmLot_Actual		= FORMAT(m.StockArmLot_Actual, '0.00')
	,	StockLocLot_Anterior		= FORMAT(m.StockLocLot_Anterior, '0.00')
	,	StockLocLot_Actual		= FORMAT(m.StockLocLot_Actual, '0.00')

	--	Inventory
	,	Existencias_Anterior		= FORMAT(m.Existencias_Anterior, '0.00')
	,	Existencias_Actual		= FORMAT(m.Existencias_Actual, '0.00')
	,	ExistenciasArm_Anterior		= FORMAT(m.ExistenciasArm_Anterior, '0.00')
	,	ExistenciasArm_Actual		= FORMAT(m.ExistenciasArm_Actual, '0.00')
	,	ExistenciasLoc_Anterior		= FORMAT(m.ExistenciasLoc_Anterior, '0.00')
	,	ExistenciasLoc_Actual		= FORMAT(m.ExistenciasLoc_Actual, '0.00')
	,	ExistenciasGrpCst_Anterior	= FORMAT(m.ExistenciasGrpCst_Anterior, '0.00')
	,	ExistenciasGrpCst_Actual	= FORMAT(m.ExistenciasGrpCst_Actual, '0.00')
	,	ExistenciasLot_Anterior		= FORMAT(m.ExistenciasLot_Anterior, '0.00')
	,	ExistenciasLot_Actual		= FORMAT(m.ExistenciasLot_Actual, '0.00')
	,	ExistenciasArmLot_Anterior	= FORMAT(m.ExistenciasArmLot_Anterior, '0.00')
	,	ExistenciasArmLot_Actual	= FORMAT(m.ExistenciasArmLot_Actual, '0.00')
	,	ExistenciasLocLot_Anterior	= FORMAT(m.ExistenciasLocLot_Anterior, '0.00')
	,	ExistenciasLocLot_Actual	= FORMAT(m.ExistenciasLocLot_Actual, '0.00')
	,	ExistenciasGrpCstLot_Anterior	= FORMAT(m.ExistenciasGrpCstLot_Anterior, '0.00')
	,	ExistenciasGrpCstLot_Actual	= FORMAT(m.ExistenciasGrpCstLot_Actual, '0.00')
FROM		INV_Movimentos	m	WITH(NOLOCK)
INNER JOIN	INV_Origens	orig	WITH(NOLOCK)	ON m.IdOrigem = orig.Id
WHERE		m.Artigo	= 'A0001'
ORDER BY	m.Data, m.NumRegisto

Current stock quantities can now be retrieved from the INV_ValoresActuaisStock table, as shown in the following example:

/* Current stock quantities (INV_ValoresActuaisStock) */
SELECT		Artigo, Armazem, Localizacao, Lote, EstadoStock, DataStock

	--	Current stock at the location (base unit)
	,	Stock = FORMAT(Stock, '0.00')

	--	Identifier of the last movement that originated the current stock
	,	IdMovimentoStock

	--	Record identifier
	,	Id

FROM		INV_ValoresActuaisStock WITH(NOLOCK)
WHERE		Artigo = 'A0001'
ORDER BY	Armazem, Localizacao, Lote

Example 2: Unit cost calculation movements

Costing Movements are recorded in the INV_Custeio table and, like in the previous example, records are correctly sorted using the Data and NumRegisto fields. The Data field indicates when the movement occurs, while the NumRegisto field is a sequential field of type Identity. There is also another field that stores the date when the movement is actually recorded, the DataIntegracao field.

Example of retrieving a cost statement for article 'A0001' from the DEMO company:

/* Unit Cost Calculation (INV_Custeio) */
SELECT		TipoLancamento			= case m.TipoLancamentoCusteio when 1 then 'ENC' when 2 then 'DES' when 3 then 'VPT' else 'Normal' end
	,	orig.Documento, m.Data, m.DataIntegracao, m.NumRegisto, m.Artigo, m.GrupoCustos, m.Lote, m.TipoMovimento, orig.Transferencia, m.TransferenciaValor

	--	Quantidade movimentada
	,	Quantidade			= FORMAT(m.Quantidade, '0.00')
		
	--	Valores movimentados na moeda base
	,	ValorUnitarioMBase		= FORMAT(m.ValorUnitarioMBase, '0.00')
	,	ValorAdicionalMBase		= FORMAT(m.ValorAdicionalMBase, '0.00')
	,	ValorAbaterMBase		= FORMAT(m.ValorAbaterMBase, '0.00')
		
	--	Valores movimentados na moeda alternativa
	,	ValorUnitarioMAlt		= FORMAT(m.ValorUnitarioMAlt, '0.00')
	,	ValorAdicionalMAlt		= FORMAT(m.ValorAdicionalMAlt, '0.00')
	,	ValorAbaterMAlt			= FORMAT(m.ValorAbaterMAlt, '0.00')
		
	--	Valores de custeio na moeda base
	,	CustoGrpCstMBase_Anterior	= FORMAT(m.CustoGrpCstMBase_Anterior, '0.00')
	,	CustoGrpCstMBase_Actual		= FORMAT(m.CustoGrpCstMBase_Actual, '0.00')
	,	CustoGrpCstLotMBase_Anterior	= FORMAT(m.CustoGrpCstLotMBase_Anterior, '0.00')
	,	CustoGrpCstLotMBase_Actual	= FORMAT(m.CustoGrpCstLotMBase_Actual, '0.00')
		
	--	Valores de custeio na moeda alternativa
	,	CustoGrpCstMAlt_Anterior	= FORMAT(m.CustoGrpCstMAlt_Anterior, '0.00')
	,	CustoGrpCstMAlt_Actual		= FORMAT(m.CustoGrpCstMAlt_Actual, '0.00')
	,	CustoGrpCstLotMAlt_Anterior	= FORMAT(m.CustoGrpCstLotMAlt_Anterior, '0.00')
	,	CustoGrpCstLotMAlt_Actual	= FORMAT(m.CustoGrpCstLotMAlt_Actual, '0.00')
		
	--	Valores originais movimentados na moeda base
	,	ValorUnitarioOrigMBase		= FORMAT(m.ValorUnitarioOrigMBase, '0.00')
	,	ValorAdicionalOrigMBase		= FORMAT(m.ValorAdicionalOrigMBase, '0.00')
	,	ValorAbaterOrigMBase		= FORMAT(m.ValorAbaterOrigMBase, '0.00')
		
	--	Valores originais movimentados na moeda alteranativa
	,	ValorUnitarioOrigMAlt		= FORMAT(m.ValorUnitarioOrigMAlt, '0.00')
	,	ValorAdicionalOrigMAlt		= FORMAT(m.ValorAdicionalOrigMAlt, '0.00')
	,	ValorAbaterOrigMAlt		= FORMAT(m.ValorAbaterOrigMAlt, '0.00')
		
	--	Id's associados ao registo
	,	m.Id, m.IdMovimentoCusteioOrig, m.IdOrigem, m.IdMovimentoStock, m.IdValorizacao, orig.IdChave1, orig.IdChave2				
		
	--	Valores relacionados
	,	orig.AplicaValorizacao
	,	orig.ValorUnitario, orig.ValorAdicional, orig.ValorAbater
	,	orig.ActualizaPCU

FROM		INV_Custeio	m	WITH(NOLOCK)	
INNER JOIN	INV_Origens	orig	WITH(NOLOCK)	ON m.IdOrigem = orig.Id
WHERE		m.Artigo	= 'A0001'
ORDER BY	m.Data, m.NumRegisto

Current unit costing values can now be retrieved from table INV_ValoresActuaisCusteio, as shown in the following example:

/* Current calculated unit cost values (INV_ValoresActuaisCusteio) */
SELECT		Artigo, GrupoCustos, Lote

	--	Unit cost values in the base curency				
	,	CustoGrpCstMBase	= FORMAT(CustoGrpCstMBase, '0.00')						
	,	CustoGrpCstLotMBase	= FORMAT(CustoGrpCstLotMBase, '0.00')

	--	Unit cost values in the alternative curency
	,	CustoGrpCstMAlt		= FORMAT(CustoGrpCstMAlt, '0.00')						
	,	CustoGrpCstLotMAlt	= FORMAT(CustoGrpCstLotMAlt, '0.00')

	--	Unit cost values date
	,	DataCusteio

FROM		INV_ValoresActuaisCusteio WITH(NOLOCK) 
WHERE		Artigo = 'A0001'
ORDER BY	DataCusteio

Example 3: Outgoing movements valuation

All stock issue movement are valuated and assigned the a cost value on the movement date (CMVMC). This valuation is applied to all outgoing movement except for transfers to a location that belongs to the same cost group.

The following example shows how valuations are obtained for all stock issue movements of article 'A0001' in the DEMO company. This example brings together other tables, such as the INV_Movimentos, which will generate the outgoing movement itself and the INV_Custeio, which relates the valuation to the unit cost calculation movement from which the cost is taken.

/* Outgoing movements valuations (INV_Valorizacoes) */
SELECT		[Doc.]		= orig.Documento, orig.Transferencia, v.Data, v.DataValor, v.Quantidade

	--	Cost value in the base currency
	,	CustoMBase	= FORMAT(v.CustoMBase, '0.00')

	--	Cost value in the alternative currency
	,	CustoMAlt	= FORMAT(v.CustoMAlt, '0.00')

	--	Cost group
	,	GrupoCustos	= c.GrupoCustos

	--	Valuated document
	,	[Doc. Valor]	= isnull(origc.Documento, '')

	--	Identifier of the stock issue movement to valuate
	,	v.IdMovimentoStock

	--	Identifier of the unit cost calculation movement to valuate in the outgoing movement
	,	v.IdMovimentoCusteio

	--	Other identifiers
	,	v.IdCustoPadrao, v.IdOrigem, v.Id

FROM		INV_Valorizacoes	v	WITH(NOLOCK) 
INNER JOIN	INV_Origens		orig	WITH(NOLOCK)	ON v.IdOrigem = orig.Id
INNER JOIN	INV_Movimentos		m	WITH(NOLOCK)	ON v.IdMovimentoStock = m.id		
LEFT JOIN	INV_Custeio		c	WITH(NOLOCK)	ON v.IdMovimentoCusteio = c.Id
LEFT JOIN	INV_Origens		origc	WITH(NOLOCK)	ON c.IdOrigem = origc.id
WHERE		m.Artigo = 'A0001'
ORDER BY	v.Data, m.NumRegisto

Example 4: Calculated unit cost changes associated with valuations

Changes to the average cost have to be reflected in the valuations applied to output documents. After being implemented, these valuations will not modified, only positive and negative variations can be added.

These variations are recorded in the INV_Variacoes table. The following example shows how to retrieve all variations applied to item issues movements of article 'A0001'.

/* Calculated unit cost variations associated with valuations (INV_Variacoes) */
SELECT	--	Reference cost group, date and document
		[Doc.Ref.]				= origref.Documento, r.DataRef, GrupoCustosRef = cstref.GrupoCustos
		
	--	Variation cost group, date and document
	,	[Doc.Var.] = origvar.Documento, r.DataVariacao
		
	--	Variation cost group, date and document
	,	[Doc.Val.] = origval.Documento, DataValorizacao = val.Data, GrupoCustosVal = cstvar.GrupoCustos

	--	Valuation value in the base currency
	,	ValorMBase = FORMAT(r.ValorMBase, '0.00')
		
	--	Valuation value in the alternative currency
	,	ValorMAlt = FORMAT(r.ValorMAlt, '0.00')
		
	--	Identifier of the reference calculated unit cost movement (e.g. VFA)
	,	r.IdCusteioRef
		
	--	Identifier of the calculated unit cost movement that generated the variation (e.g. VPT/ENC/DES)
	,	r.IdCusteioVariacao
		
	--	Valuation identifier
	,	r.IdValorizacao
	,	r.Id

FROM		INV_Variacoes		r	WITH(NOLOCK) 
INNER JOIN	INV_Custeio		cstref	WITH(NOLOCK)	ON r.IdCusteioRef = cstref.Id
INNER JOIN	INV_Origens		origref	WITH(NOLOCK)	ON cstref.IdOrigem = origref.Id
INNER JOIN	INV_Custeio		cstvar	WITH(NOLOCK)	ON r.IdCusteioVariacao = cstvar.Id
INNER JOIN	INV_Origens		origvar	WITH(NOLOCK)	ON cstvar.IdOrigem = origvar.Id
LEFT JOIN	INV_Valorizacoes	val	WITH(NOLOCK)	ON r.IdValorizacao = val.Id
LEFT JOIN	INV_Movimentos		movval	WITH(NOLOCK)	ON val.IdMovimentoStock = movval.Id
INNER JOIN	INV_Origens		origval	WITH(NOLOCK)	ON val.IdOrigem = origval.Id
WHERE		cstref.Artigo = 'A0001'
ORDER BY	r.DataVariacao, val.Data

Bookmark or share this article

Esta página foi útil?

Obrigado pelo seu voto.

login para deixar a sua opinião.

Obrigado pelo seu feedback. Iremos analisá-lo para continuarmos a melhorar!