Declare
@company as nvarchar(25) = '%',
@item nvarchar(25) = 'A1PM550'

BEGIN

DECLARE @result NVARCHAR(25)='Ok'
DECLARE @itemUM AS nvarchar(25)
DECLARE @storageTemplate nvarchar(25)
-- NUSTATOMOS GALIMOS PAKLAIDOS
DECLARE @delta_EA_PL_volume NUMERIC(19,5)=0.2	--Kasparas: is 0.05 i 0.2. Dėl nevienodai sudėtų dėžių ant paletės paklaida gaunasi didesnė nei 0.05.
DECLARE @delta_EA_PL_weight NUMERIC(19,5)=0.2	--Kasparas: is 0.05 i 0.2. Lygina be paletes su su palete svorius, del to per mazai 0.05 ir nesutampa su check raportais.
DECLARE @delta_EA_TB_volume NUMERIC(19,5)=0.05
DECLARE @delta_EA_TB_weight NUMERIC(19,5)=0.05
DECLARE @delta_EA_SB_volume NUMERIC(19,5)=0.05
DECLARE @delta_EA_SB_weight NUMERIC(19,5)=0.05
DECLARE @delta_KG_PL_volume NUMERIC(19,5)=0.2
DECLARE @delta_KG_PL_weight NUMERIC(19,5)=0.2

-- SUDAROMAS ANALIZUOJAMU PREKIU SARASAS
SELECT
	@itemUM=ISNULL(ium.QUANTITY_UM,'')
	,@storageTemplate= i.STORAGE_TEMPLATE
FROM ITEM i
LEFT JOIN ITEM_UNIT_OF_MEASURE ium
ON i.COMPANY=ium.COMPANY
	AND i.ITEM=ium.ITEM
	AND ium.SEQUENCE=1
WHERE i.COMPANY like @company
	AND i.ITEM like @item

-- PATIKRINAMA AR APRASYTAS PREKES VIENETAS (KINTAMO SVORIO IR VIENETINES)
IF @result='Ok'
	BEGIN
		SELECT @result='Error'
		FROM ITEM_UNIT_OF_MEASURE ium_e WITH (NOLOCK)
		WHERE ium_e.COMPANY=@company
			AND ium_e.ITEM=@item
			AND (ISNULL(ium_e.LENGTH,0)=0 OR ISNULL(ium_e.WIDTH,0)=0 OR ISNULL(ium_e.HEIGHT,0)=0 OR ISNULL(ium_e.WEIGHT,0)=0 )
	END

-- VIENETINES PREKES TURINCIOS SB
IF @itemUM LIKE '%EA%' AND @storageTemplate LIKE '%SB%' AND @result='Ok'
	BEGIN
		--TIKRINAMA AR SUTAMPA TURIS IR SVORIS TARP VIENETO IR MAZOS PAKUOTES
		SELECT @result='Error'
		FROM ITEM_UNIT_OF_MEASURE ium_e WITH (NOLOCK)
		LEFT JOIN ITEM_UNIT_OF_MEASURE ium_s WITH (NOLOCK)
		ON ium_e.COMPANY=ium_s.COMPANY
			AND ium_e.ITEM=ium_s.ITEM
			AND ium_s.QUANTITY_UM='SB'
	WHERE ium_e.COMPANY like @company
			AND ium_e.ITEM=@item
			AND ium_e.SEQUENCE=1
			AND( ISNULL(ium_e.LENGTH,0)=0 OR ISNULL(ium_e.WIDTH,0)=0 OR ISNULL(ium_e.HEIGHT,0)=0 OR ISNULL(ium_e.WEIGHT,0)=0
				OR ISNULL(ium_s.LENGTH,0)=0 OR ISNULL(ium_s.WIDTH,0)=0 OR ISNULL(ium_s.HEIGHT,0)=0 OR ISNULL(ium_s.WEIGHT,0)=0
				OR ABS(1 - ISNULL(ium_e.LENGTH,0) * ISNULL(ium_e.WIDTH,0) * ISNULL(ium_e.HEIGHT,0) * ium_s.CONVERSION_QTY/(ISNULL(ium_s.LENGTH,0) * ISNULL(ium_s.WIDTH,0) * ISNULL(ium_s.HEIGHT,0))) > @delta_EA_PL_volume
				OR ABS(1- ISNULL(ium_e.WEIGHT,0)* ium_s.CONVERSION_QTY/ISNULL(ium_s.WEIGHT,0)) > @delta_EA_PL_weight)
	END
-- VIENETINES PREKES TURINCIOS TB
IF @itemUM LIKE '%EA%' AND @storageTemplate LIKE '%TB%' AND @result='Ok'
	BEGIN
		--TIKRINAMA AR SUTAMPA TURIS IR SVORIS TARP VIENETO IR MAZOS PAKUOTES
		SELECT @result='Error'
		FROM ITEM_UNIT_OF_MEASURE ium_e WITH (NOLOCK)
		LEFT JOIN ITEM_UNIT_OF_MEASURE ium_t WITH (NOLOCK)
		ON ium_e.COMPANY=ium_t.COMPANY
			AND ium_e.ITEM=ium_t.ITEM
			AND ium_t.QUANTITY_UM='TB'
		WHERE ium_e.COMPANY like @company
			AND ium_e.ITEM=@item
			AND ium_e.SEQUENCE=1
			AND (ISNULL(ium_e.LENGTH,0)=0 OR ISNULL(ium_e.WIDTH,0)=0 OR ISNULL(ium_e.HEIGHT,0)=0 OR ISNULL(ium_e.WEIGHT,0)=0
				OR ISNULL(ium_t.LENGTH,0)=0 OR ISNULL(ium_t.WIDTH,0)=0 OR ISNULL(ium_t.HEIGHT,0)=0 OR ISNULL(ium_t.WEIGHT,0)=0
				OR ABS(1 - ISNULL(ium_e.LENGTH,0) * ISNULL(ium_e.WIDTH,0) * ISNULL(ium_e.HEIGHT,0) * ium_t.CONVERSION_QTY/(ISNULL(ium_t.LENGTH,0) * ISNULL(ium_t.WIDTH,0) * ISNULL(ium_t.HEIGHT,0))) > @delta_EA_PL_volume
				OR ABS(1- ISNULL(ium_e.WEIGHT,0)* ium_t.CONVERSION_QTY/ISNULL(ium_t.WEIGHT,0))>@delta_EA_PL_weight)
	END
-- VIENETINES PREKES TURINCIOS PL
IF @itemUM LIKE '%EA%' AND @storageTemplate LIKE '%PL%' AND @result='Ok'
	BEGIN
		--TIKRINAMA AR SUTAMPA TURIS IR SVORIS TARP VIENETO IR MAZOS PAKUOTES
		SELECT @result='Error'
			FROM ITEM_UNIT_OF_MEASURE ium_e WITH (NOLOCK)
			LEFT JOIN ITEM_UNIT_OF_MEASURE ium_p WITH (NOLOCK)
			ON ium_e.COMPANY=ium_p.COMPANY
				AND ium_e.ITEM=ium_p.ITEM
				AND ium_p.QUANTITY_UM='PL'
		WHERE ium_e.COMPANY like @company
			AND ium_e.ITEM=@item
			AND ium_e.SEQUENCE=1
			AND (ISNULL(ium_e.LENGTH,0)=0 OR ISNULL(ium_e.WIDTH,0)=0 OR ISNULL(ium_e.HEIGHT,0)=0 OR ISNULL(ium_e.WEIGHT,0)=0
				OR ISNULL(ium_p.LENGTH,0)=0 OR ISNULL(ium_p.WIDTH,0)=0 OR ISNULL(ium_p.HEIGHT,0)=0 OR ISNULL(ium_p.WEIGHT,0)=0
				OR ABS(1 - ISNULL(ium_e.LENGTH,0) * ISNULL(ium_e.WIDTH,0) * ISNULL(ium_e.HEIGHT,0) * ium_p.CONVERSION_QTY/(ISNULL(ium_p.LENGTH,0) * ISNULL(ium_p.WIDTH,0) * ISNULL(ium_p.HEIGHT,0))) > @delta_EA_PL_volume
				OR ABS(1- ISNULL(ium_e.WEIGHT,0)* ium_p.CONVERSION_QTY/(ISNULL(ium_p.WEIGHT,0)-25) )>@delta_EA_PL_weight)
	END

-- VIENETINES PREKES TURINCIOS PL IR TB
IF @itemUM LIKE '%EA%' AND @storageTemplate LIKE '%PL%' AND  @storageTemplate LIKE '%TB%' AND @result='Ok'
	BEGIN
		--TIKRINAMA AR SUTAMPA TURIS IR SVORIS TARP VIENETO IR MAZOS PAKUOTES
		SELECT @result='Error'
			FROM ITEM_UNIT_OF_MEASURE ium_e WITH (NOLOCK)
			LEFT JOIN ITEM_UNIT_OF_MEASURE ium_p WITH (NOLOCK)
			ON ium_e.COMPANY=ium_p.COMPANY
				AND ium_e.ITEM=ium_p.ITEM
				AND ium_p.QUANTITY_UM='PL'
			LEFT JOIN ITEM_UNIT_OF_MEASURE ium_t WITH (NOLOCK)
			ON ium_e.COMPANY like ium_t.COMPANY
				AND ium_e.ITEM=ium_t.ITEM
				AND ium_t.QUANTITY_UM='TB'
		WHERE ium_e.COMPANY like @company
			AND ium_e.ITEM=@item
			AND ium_e.SEQUENCE=1
			AND (ISNULL(ium_t.LENGTH,0)=0 OR ISNULL(ium_t.WIDTH,0)=0 OR ISNULL(ium_t.HEIGHT,0)=0 OR ISNULL(ium_t.WEIGHT,0)=0
				OR ISNULL(ium_p.LENGTH,0)=0 OR ISNULL(ium_p.WIDTH,0)=0 OR ISNULL(ium_p.HEIGHT,0)=0 OR ISNULL(ium_p.WEIGHT,0)=0
				OR ABS(1 - ISNULL(ium_t.LENGTH,0) * ISNULL(ium_t.WIDTH,0) * ISNULL(ium_t.HEIGHT,0) * (ium_p.CONVERSION_QTY/ISNULL(ium_t.CONVERSION_QTY,1))/(ISNULL(ium_p.LENGTH,0) * ISNULL(ium_p.WIDTH,0) * ISNULL(ium_p.HEIGHT,0))) > @delta_EA_PL_volume
				OR ABS(1- ISNULL(ium_t.WEIGHT,0)* (ium_p.CONVERSION_QTY/ISNULL(ium_t.CONVERSION_QTY,1))/(ISNULL(ium_p.WEIGHT,0)-25))>@delta_EA_PL_weight)

	END

-- KINTAMO SVORIO PREKES TURINCIOS PL
IF @itemUM LIKE '%KG%' AND @storageTemplate LIKE '%PL%' AND @result='Ok'
	BEGIN
		--TIKRINAMA AR SUTAMPA TURIS IR SVORIS TARP VIENETO IR MAZOS PAKUOTES
		SELECT @result='Error'
			FROM ITEM_UNIT_OF_MEASURE ium_e WITH (NOLOCK)
			LEFT JOIN ITEM_UNIT_OF_MEASURE ium_p WITH (NOLOCK)
			ON ium_e.COMPANY=ium_p.COMPANY
				AND ium_e.ITEM=ium_p.ITEM
				AND ium_p.QUANTITY_UM='PL'
		WHERE ium_e.COMPANY like @company
			AND ium_e.ITEM=@item
			AND ium_e.SEQUENCE=1
			AND (ISNULL(ium_e.LENGTH,0)=0 OR ISNULL(ium_e.WIDTH,0)=0 OR ISNULL(ium_e.HEIGHT,0)=0 OR ISNULL(ium_e.WEIGHT,0)=0
				OR ISNULL(ium_p.LENGTH,0)=0 OR ISNULL(ium_p.WIDTH,0)=0 OR ISNULL(ium_p.HEIGHT,0)=0 OR ISNULL(ium_p.WEIGHT,0)=0
				OR ABS(1 - ISNULL(ium_e.LENGTH,0) * ISNULL(ium_e.WIDTH,0) * ISNULL(ium_e.HEIGHT,0) * ium_p.CONVERSION_QTY/(ISNULL(ium_p.LENGTH,0) * ISNULL(ium_p.WIDTH,0) * ISNULL(ium_p.HEIGHT,0))) > @delta_KG_PL_volume
				OR ABS(1- ISNULL(ium_e.WEIGHT,0)* ium_p.CONVERSION_QTY/(ISNULL(ium_p.WEIGHT,0)-25))>@delta_KG_PL_weight)
	END
select @result
END