Alpha Baking

The table that holds the current inventory by pallet for Assistics is ASTStockMaster. The problem is that it does not use the items and packs the Adage does. So you need to find the Warehouse Material ID. The below code will find that value. You can then use that to update inventory in ASTStockMaster.

DECLARE @Whs CHAR(4), @Item VARCHAR(9),@Pack VARCHAR(9),@MaterialID INT, @WhsMatID INT

SET @Whs='LCSF'

SET @Item='85049'

SET    @Pack=''

SELECT @MaterialID=amm.materialId

FROM ASTMaterialMaster amm

WHERE amm.itemKey=@Item

AND amm.packKey=@Pack

SELECT @MaterialID

SELECT @WhsMatID=awm.warehouseMaterialId

FROM ASTWarehouseMaterial awm

WHERE awm.materialId=@MaterialID

AND awm.warehouseKey=@Whs

SELECT @WhsMatID

SELECT CAST(SUM(asm.quantity) AS INT) TotalQty

FROM ASTStockMaster asm

WHERE asm.warehouseMaterialId=@WhsMatID

--Note that if you are changing the inventory to fix a Case/Each conversion, you will need to nultiply all pallets by the same factor

--Otherwise you will specify the PalletID to update a single pallet.

BEGIN TRAN UPDATE  asm SET asm.quantity=asm.quantity*10

FROM ASTStockMaster asm

WHERE asm.warehouseMaterialId=@WhsMatID

SELECT CAST(SUM(asm.quantity) AS INT) TotalQty

FROM ASTStockMaster asm

WHERE asm.warehouseMaterialId=@WhsMatID

--rollback

--commit