CREATE PROCEDURE spx_GetRunningInventory_Material
	(
		@itemcode varchar(30),
		@div int,
		@invdate varchar(10)
	)
AS
IF @itemcode = '' OR @itemcode IS Null
	BEGIN	
		SET @itemcode = '%%'
	END

DECLARE @begdate varchar(10)
SELECT TOP 1 @begdate = CONVERT(varchar(10),invdate,101) FROM InventoryTransactions WHERE invdate <= @invdate ORDER BY invdate DESC

SELECT
	Items.Code AS itemcode,
	Items.[Description] AS itemdesc,
	(SELECT ISNULL(SUM(quantity),0) FROM InventoryTransactions WHERE itemnum = Items.Code AND invdate = @begdate)
	- (SELECT ISNULL(SUM([Lbs Mtl 1]),0) FROM [4D_Romulus].dbo.[PLANT SURVEY] WHERE [Code Mtl1] = Items.Code AND [Date] > @begdate AND [Date] <= @invdate)
	- (SELECT ISNULL(SUM([Lbs Mtl 2]),0) FROM [4D_Romulus].dbo.[PLANT SURVEY] WHERE [Code Mtl 2] = Items.Code AND [Date] > @begdate AND [Date] <= @invdate)
	- (SELECT ISNULL(SUM([LbsMtl3]),0) FROM [4D_Romulus].dbo.[PLANT SURVEY] WHERE [CodeMtl3] = Items.Code AND [Date] > @begdate AND [Date] <= @invdate)
	- (SELECT ISNULL(SUM([LbsMtl4]),0) FROM [4D_Romulus].dbo.[PLANT SURVEY] WHERE [CodeMtl4] = Items.Code AND [Date] > @begdate AND [Date] <= @invdate)
	- (SELECT ISNULL(SUM([LbsMtl5]),0) FROM [4D_Romulus].dbo.[PLANT SURVEY] WHERE [CodeMtl5] = Items.Code AND [Date] > @begdate AND [Date] <= @invdate)
	- (SELECT ISNULL(SUM([LbsMtl6]),0) FROM [4D_Romulus].dbo.[PLANT SURVEY] WHERE [CodeMtl6] = Items.Code AND [Date] > @begdate AND [Date] <= @invdate)
	+ (SELECT ISNULL(SUM(RecvDetDet.RecvDetQty),0) FROM RecvDetDet LEFT OUTER JOIN PODetail ON PODetail.PODetIdx = RecvDetDet.RecvDetPODetIdx WHERE	PODetail.PODetItemNum = Items.Code AND RecvDetDet.RecvDetDate > @begdate AND RecvDetDet.RecvDetDate <= @invdate)
	+ (SELECT ISNULL(SUM(InventoryAdjustments.quantity),0) FROM InventoryAdjustments INNER JOIN ItemMaster ON InventoryAdjustments.itemindex = ItemMaster.ItemIdx WHERE ItemMaster.Code = Items.Code AND InventoryAdjustments.itemclass = 1 AND InventoryAdjustments.adjdate > @begdate AND InventoryAdjustments.adjdate <= @invdate) 
	AS iteminv,
	Units.UnitMstrDesc AS itemunits,
	Items.CostInclFreight AS CurrCost
FROM 
	ItemMaster Items
	LEFT OUTER JOIN UnitMstr Units ON Units.UnitMstrIdx = Items.Units
WHERE 	
	Items.Code LIKE @itemcode 
	AND Items.Class = 1
	AND Items.DefaultDiv = @div
	AND Items.inactive = 0
ORDER BY 
	CAST(Items.Code as int)
GO