ALTER   proc [dbo].[spCreateContainerFill]
(@containerID as nvarchar(64),
 @lotNum as bigint,
 @customerID as int = 1164,
 @fillDateTime as datetime
 )
as
BEGIN   
	set nocount on		
		BEGIN		
			SELECT min(cs.purchorderNum) as PurchorderNum, cs.productID, (cs.purchOrderQty / p.weight) AS QtyOrder, (cs.qtyFilled / p.weight) AS QtyFilled, isActive, p.weight
					FROM CustomerServicePO cs
							INNER JOIN products p on cs.productID = p.productID
							INNER JOIN ContainerFillHistory cf on cs.customerID = cf.customerID
					WHERE SUBSTRING(@containerID, 1, 6) = cs.productID AND isActive = 1
					GROUP BY cs.purchorderNum, cs.customerID, cs.productID, cs.qtyFilled, cs.purchOrderQty, isActive, p.weight
			
			INSERT ContainerFillHistory(containerID, lotID, productID, customerID, fillDateTime, purchorderNum, qtyFilled)
			VALUES (@containerID, @lotNum, SUBSTRING(@containerID, 1, 6), @customerID, @fillDateTime, ???PurchorderNum, ???QtyFilled) 
			
			UPDATE cs
				SET isActive =
				( CASE
					WHEN (QtyOrder < QtyFilled) THEN 0
					END
				), QtyFilled = QtyFilled +1, cs.qtyFilled = QtyFilled * p.weight
			WHERE min(cs.purchorderNum) = PurchorderNum					
		END	
END

ALTER   proc [dbo].[spCreateContainerFill]
(@containerID as nvarchar(64),
 @lotNum as bigint,
 @customerID as int = 1164,
 @fillDateTime as datetime
 
 
 )
as
BEGIN
    declare @checkcontainerID bigint
    declare @QtyOrder int
    
	set nocount on
	
		BEGIN
			INSERT ContainerFillHistory(containerID, lotID, customerID, productID, fillDateTime, PurchorderNum, qtyFilled) 			
			SELECT @containerID, @lotNum, @CustomerID, SUBSTRING(@containerID, 1, 6), @fillDateTime, min(cs.purchorderNum) as PurchorderNum, (cs.qtyFilled / p.weight) AS QtyFilled
					FROM CustomerServicePO cs
							INNER JOIN products p on cs.productID = p.productID
							INNER JOIN ContainerFillHistory cf on cs.customerID = cf.customerID
					WHERE SUBSTRING(@containerID, 1, 6) = cs.productID AND isActive = 1 AND cs.CustomerID = @CustomerID
					GROUP BY cs.purchorderNum, cs.customerID, cs.productID, cs.qtyFilled, cs.purchOrderQty, isActive, p.weight
			
			UPDATE cs
				SET isActive =
				( CASE
					WHEN (cs.purchOrderQty < QtyFilled) THEN 0
					
					END
				), QtyFilled = QtyFilled +1, cs.qtyFilled = QtyFilled * p.weight
			WHERE min(cs.purchorderNum) = PurchorderNum	
		END	
END