实现库房批次管理，先进先出原则（一次难忘的找bug经历）

新加一个物资台账功能。

上午设计表结构，下午测试数据。

原则是保证物资清关的时候，一个PO单据可以分批次收实现批次管理功能，而且发货要保证先进先出的原则。

当天下午开始写存储过程，逻辑挺简单的：

ALTER PROCEDURE [dbo].[SP_INV_SelectWarehouseAccount] 
	@dbname	 nvarchar(100),
    @Receive_sn  nvarchar(100),
	@ischs char(1),
	@error nvarchar(max) output
AS
  BEGIN TRANSACTION
  

  
	  ------------------------------------------------------------------------------------------------------------------------
	  -----------                                 收料单插入物资台账                                               -----------
	  ------------------------------------------------------------------------------------------------------------------------
	  DECLARE 
		@PO_SN as nvarchar(100),
		@Batch_No as nvarchar(2) ='00',
		@Item_Code nvarchar(100);
		
	  
  	 ----通过Receiving_SN查询到po单号   (前台只能选一个po单 获取一个就可以了)
	 select top(1) @PO_SN = PO_Code from  dbo.INV_Receivedet    where [email protected] and [email protected]_sn;--REC1810018 	 
	 
	
	 select @Batch_No = MAX(Batch_No), @Item_Code = Item_Code 
				 from INV_WarehouseAccount 
					where PO_SN in(select distinct po_sn from  dbo.INV_Receivedet where [email protected] and [email protected]_sn)
					and Item_Code  in(select Material_Code  from  dbo.INV_Receivedet where [email protected] and [email protected]_sn)
					group by Item_Code;
					
	--判断库房台账是否有此单据的物资,如果没此PO单据的话执行语句1,否则2				
	IF (@Batch_No='00')
		--1
		BEGIN
			insert into	INV_WarehouseAccount
					(DB_Center,PO_SN,Item_Code,Batch_No,Warehouse_SN,Bin_SN,UOM_SN,Dept_SN,Ini_Qty,Receive_Qty,Issue_Qty,Return_Qty,INV_Balance_Qty,Check_Qty,Price,USD_Price,Status) 
			SELECT 
				A.DB_Center,A.PO_Code,A.Material_Code AS ITEM_CODE, 1 Batch_No,B.Warehouse_SN,C.Bin_Code,a.Convert_UOM,
				(select dept.dept_sn from dbo.BAS_Department dept,BAS_Employee emp,dbo.INV_Receive det where dept.Dept_SN=emp.Dept_SN and emp.Employee_Name=det.Receiving_By and det.Receiving_SN=a.Receiving_SN)dept_sn,
				0 Ini_Qty,A.Quantity receive_Qty,0 issue_Qty,0 return_Qty,0+A.Quantity INV_Balance_Qty,0 Check_Qty,
				A.Unit_Price, A.Unit_Price*5.8 USD_price,'O'status
			  FROM  dbo.INV_ReceiveDet A,dbo.BAS_Warehouse B,dbo.INV_Bin C
					 WHERE A.DB_Center=B.DB_Center
					 AND A.DB_Center=C.DB_Center
					 AND A.Bin_SN=C.Bin_SN
					 AND C.Warehouse_SN=B.Warehouse_SN
					 AND  exists(select 1 from  dbo.INV_Receive det where  a.Receiving_SN=det.Receiving_SN AND [email protected]_SN)
					 and [email protected]_sn
					 ORDER BY A.Receiving_SN;
			
		END
	ELSE
		--2
		
		 insert into dbo.INV_WarehouseAccount_temp	--插入临时表给同一单号有不同物资不同时收只用max batchno会有问题,下边采取临时表插入
		  select  max(Batch_No),  Item_Code 				
				 from INV_WarehouseAccount 
					where PO_SN in(select distinct po_sn from  dbo.INV_Receivedet where [email protected] and [email protected]_sn)
					and Item_Code  in(select Material_Code  from  dbo.INV_Receivedet where [email protected] and [email protected]_sn)
					group by Item_Code;
					
		
		--DECLARE @i as int =1,
		--		@countNum as int;	
			
		--select @countNum = count(*) from INV_WarehouseAccount_temp;
				
		--WHILE @i<[email protected] 
		BEGIN
			insert into INV_WarehouseAccount     
			  SELECT 
				A.DB_Center,A.PO_Code,A.Material_Code AS ITEM_CODE, (SELECT TT.BATCH_NO+1 FROM INV_WarehouseAccount_temp TT WHERE TT.ITEM_CODE=A.Material_Code)  Batch_No,B.Warehouse_SN,C.Bin_Code,a.Convert_UOM,
				(select dept.dept_sn from dbo.BAS_Department dept,BAS_Employee emp,dbo.INV_Receive det where dept.Dept_SN=emp.Dept_SN and emp.Employee_Name=det.Receiving_By and det.Receiving_SN=a.Receiving_SN)dept_sn,
				0 Ini_Qty,A.Quantity receive_Qty,0 issue_Qty,0 return_Qty,0+A.Quantity INV_Balance_Qty,0 Check_Qty,
				A.Unit_Price, A.Unit_Price*5.8 USD_price,'O'status
			  FROM  dbo.INV_ReceiveDet A,dbo.BAS_Warehouse B,dbo.INV_Bin C
					 WHERE A.DB_Center=B.DB_Center
					 AND A.DB_Center=C.DB_Center
					 AND A.Bin_SN=C.Bin_SN
					 AND C.Warehouse_SN=B.Warehouse_SN
					 AND  exists(select 1 from  dbo.INV_Receive det where  a.Receiving_SN=det.Receiving_SN AND [email protected]_SN)
					 and [email protected]_sn
					 and a.Material_Code in(select item_code from INV_WarehouseAccount_temp aa where a.Material_Code = AA.ITEM_CODE)
					 ORDER BY A.Receiving_SN;
					 --清除临时表
				delete from INV_WarehouseAccount_temp;
		    
	END
		
					
		UPDATE 	T 
			SET		T.Batch_No =
			CASE 
				WHEN T.Batch_No=1 THEN '01'
				WHEN T.Batch_No=2 THEN '02'
				WHEN T.Batch_No=3 THEN '03'
				WHEN T.Batch_No=4 THEN '04'
				WHEN T.Batch_No=5 THEN '05'
				WHEN T.Batch_No=6 THEN '06'
				WHEN T.Batch_No=7 THEN '07'
				WHEN T.Batch_No=8 THEN '08'
				WHEN T.Batch_No=9 THEN '09'
			END
		 FROM INV_WarehouseAccount T
		  WHERE T.PO_SN [email protected]_SN;			

			
			
	 COMMIT TRANSACTION;
	 	 

			
	BEGIN TRANSACTION
if @@ERROR=0
begin
	set @error=''
	commit
end
else
begin
	if @ischs='Y'
		set @error='保存失败，请联系系统管理员'
	if @ischs='N'
		set @error='Fail To Save, Please Contact Administrator!'
	rollback
end










GO