sp_inventoryadjustreport.sql

(Java+SQL)-大型企业JAVA的ERP系统

CREATE PROCEDURE sp_InventoryAdjustReport -- 库存调节表
        @loginName varchar(30) 
	,@ip varchar(20) 
	,@machineName varchar(30) 
	,@wearhouseId varchar(10) -- 仓库编码
	,@beginDate datetime    -- 开始时间
	,@endDate datetime    -- 结束时间				
AS DECLARE 
	 @error_var int 
	,@rowcount_var int
	,@wearhouseName varchar(30)  -- 仓库名称	
        ,@productId varchar(10)    -- 产品编码	
	,@productName varchar(30)  -- 产品名称
        ,@stockQty float           -- 现有库存量
        ,@lastStockQty float        -- 上月库存量                
	,@inQty float              -- 本月进货入帐
        ,@salesQty  float          -- 本月销售
        ,@removeQty float          -- 本月调拨
	,@adjustQty float          -- 调整
        ,@countNum int             -- 记录数
                                
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT 
	IF @error_var <> 0 RETURN -102 
	IF @rowcount_var = 0 RETURN -103 

SELECT @stockQty = 0;
SELECT @inQty = 0;
SELECT @removeQty = 0;
SELECT @salesQty = 0;



-- 创建临时表
CREATE TABLE #Tmp_InventoryAdjustReport 
(        
	wearhouseId varchar(10)   -- 仓库编码		
	,wearhouseName varchar(30)   -- 仓库编码			
        ,productId varchar(10)    -- 产品编码	
	,productName varchar(30)  -- 产品名称
        ,lastStockQty float        -- 上月库存量                
	,inQty float              -- 本月进货入帐
        ,salesQty  float          -- 本月销售
	,adjustQty float          -- 调整
	,beginDate datetime         -- 开始时间  
	,endDate datetime         -- 结束时间  
        ,countNum int             -- 记录数
)   

IF (@wearhouseId is not null)
	BEGIN
		DECLARE product_cur CURSOR FOR 
		SELECT DISTINCT LocationState.productId productId FROM LocationState,ProductLocation
		 WHERE LocationState.locationId = ProductLocation.locationId
		   AND ProductLocation.wearhouseId = @wearhouseId
	END
IF (@wearhouseId is null)
	BEGIN
		DECLARE product_cur CURSOR FOR 
		SELECT DISTINCT LocationState.productId productId FROM LocationState
	END
   
OPEN product_cur

FETCH NEXT FROM product_cur
INTO @productId


WHILE @@FETCH_STATUS = 0
BEGIN
IF (@wearhouseId is not null)
	BEGIN
		  -- 仓库名称
		    SELECT @wearhouseName = wearhouseName
		      FROM Wearhouse
		     WHERE wearhouseId = @wearhouseId  
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 
		    
		  -- 品名
		    SELECT @productName = productName
		      FROM Production
		     WHERE productId = @productId  
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 
		    
                  -- 本月进货入帐
		    SELECT @inQty = sum(quantity)
		      FROM Inventory
		     WHERE productId = @productId
		       AND wearhouseId = @wearhouseId		     
		       AND inventoryDate >= @beginDate
		       AND inventoryDate <= @endDate		       
		       AND status = 1 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

                  -- 本月销售
		    SELECT @salesQty = sum(quantity)
		      FROM Inventory
		     WHERE productId = @productId
		       AND wearhouseId = @wearhouseId		     		     
		       AND inventoryDate >= @beginDate
		       AND inventoryDate <= @endDate		       
		       AND status = 0 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    

		  -- 现有库存量
		    SELECT @stockQty = sum(quantity)
		      FROM LocationState,ProductLocation
		     WHERE LocationState.locationId = ProductLocation.locationId
		       AND ProductLocation.wearhouseId = @wearhouseId
		       AND LocationState.productId = @productId
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 

		  -- 本月调整库存
		    SELECT @adjustQty = sum(quantity)
		      FROM Inventory
		     WHERE productId = @productId
		       AND wearhouseId = @wearhouseId		     		     		     
		       AND inventoryDate >= @beginDate
		       AND inventoryDate <= @endDate		       
		       AND status = 3 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

                  -- 本月调拨出库
		    SELECT @removeQty = sum(quantity)
		      FROM Inventory
		     WHERE productId = @productId
		       AND wearhouseId = @wearhouseId		     		     		     
		       AND inventoryDate >= @beginDate
		       AND inventoryDate <= @endDate		       
		       AND status = 2 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

		  -- 上月库存量
		    SELECT @lastStockQty = @stockQty - ((@inQty + @adjustQty) - (@salesQty + @removeQty));

                    INSERT INTO #Tmp_InventoryAdjustReport(wearhouseId,wearhouseName,productId,productName,lastStockQty,inQty,salesQty,adjustQty,beginDate,endDate)	          
                                                    VALUES(@wearhouseId,@wearhouseName,@productId,@productName,@lastStockQty,@inQty,@salesQty,@adjustQty,@beginDate,@endDate)	            
	END

IF (@wearhouseId is null)
	BEGIN
		  -- 品名
		    SELECT @productName = productName
		      FROM Production
		     WHERE productId = @productId  
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 
		    
                  -- 本月进货入帐
		    SELECT @inQty = sum(quantity)
		      FROM Inventory
		     WHERE productId = @productId
		       AND inventoryDate >= @beginDate
		       AND inventoryDate <= @endDate		       
		       AND status = 1 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

                  -- 本月销售
		    SELECT @salesQty = sum(quantity)
		      FROM Inventory
		     WHERE productId = @productId
		       AND inventoryDate >= @beginDate
		       AND inventoryDate <= @endDate		       
		       AND status = 0 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   		    

		  -- 现有库存量
		    SELECT @stockQty = sum(quantity)
		      FROM LocationState
		     WHERE productId = @productId
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END 

		  -- 本月调整库存
		    SELECT @adjustQty = sum(quantity)
		      FROM Inventory
		     WHERE productId = @productId
		       AND inventoryDate >= @beginDate
		       AND inventoryDate <= @endDate		       
		       AND status = 3 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

                  -- 本月调拨出库
		    SELECT @removeQty = sum(quantity)
		      FROM Inventory
		     WHERE productId = @productId
		       AND inventoryDate >= @beginDate
		       AND inventoryDate <= @endDate		       
		       AND status = 2 
		    IF @@Error <> 0 BEGIN ROLLBACK RETURN -102 END   

		  -- 上月库存量
		    SELECT @lastStockQty = @stockQty - ((@inQty + @adjustQty) - (@salesQty + @removeQty));

                    INSERT INTO #Tmp_InventoryAdjustReport(productId,productName,lastStockQty,inQty,salesQty,adjustQty,beginDate,endDate)	          
                                                    VALUES(@productId,@productName,@lastStockQty,@inQty,@salesQty,@adjustQty,@beginDate,@endDate)	            
	END
	    
	FETCH NEXT FROM product_cur
        INTO @productId
END

CLOSE product_cur

DEALLOCATE product_cur

SELECT @countNum  = count(*)
FROM #Tmp_InventoryAdjustReport

UPDATE #Tmp_InventoryAdjustReport
   SET countNum = @countNum

SELECT wearhouseId,wearhouseName,productId,productName,lastStockQty,
       inQty,salesQty,adjustQty,beginDate,endDate,countNum
  FROM #Tmp_InventoryAdjustReport ORDER BY productId
RETURN 0


















GO