CREATE PROCEDURE CorrectExpirationStock  @WarehouseCode VARCHAR(3) = NULL 
As
	DECLARE @ACode Varchar(3)  
	Declare @KCode Varchar(20)        
	Declare @Differ Float             
	Declare @ExpKQty Real             
	Declare @Exp_Date SmallDateTime   
	Declare Cursor_Select Cursor For  
	Select T1.A_Code, T1.K_code ,     
	Case                              
	When Anbar.Mojodi <0 Then SumKQty
	Else                              
	SumKQty - Anbar.Mojodi           
	end as Differ                     
	From (                            
	Select K_Code, A_code, SUM(K_Qty) SumKQty From Exp_Date 
	Group By K_Code, A_Code) As T1                          
	Inner Join Anbar On Anbar.K_Code = T1.K_Code And Anbar.A_Code = T1.A_Code 
	Where Anbar.Mojodi < T1.SumKQty                                           
	And T1.A_Code = ISNULL(@WarehouseCode, T1.A_Code)                                                        
	Open Cursor_Select                                                        
	Fetch Next From Cursor_Select Into @ACode, @KCode, @Differ                
	While @@FETCH_STATUS = 0                                                  
	Begin                                                                     
	Declare Cursor_ExpDate Cursor For                                       
	Select K_Qty, Exp_Date From Exp_Date Where A_Code = @ACode              
	And K_Code = @KCode                                                     
	Order By Exp_Date                                                       
	Open Cursor_ExpDate                                                     
	Fetch Next From Cursor_ExpDate Into @ExpKQty,@Exp_Date                  
	While @Differ > 0                                                       
	Begin                                                                   
		While @@FETCH_STATUS = 0                                              
		Begin                                                                 
		IF @Differ > @ExpKQty                                               
		Begin                                                               
			Update Exp_Date Set K_Qty = 0 Where K_Code = @KCode               
			And A_Code = @ACode  And Exp_Date = @Exp_Date                     
			set @Differ = @Differ - @ExpKQty                                  
		End                                                                 
		Else                                                                
		Begin                                                               
			Update Exp_Date Set K_Qty = @ExpKQty - @Differ Where K_Code = @KCode 
			And A_Code = @ACode  And Exp_Date = @Exp_Date                     
			Set @Differ = 0                                                   
			Break                                                             
		End                                                                 
		Fetch Next From Cursor_ExpDate Into @ExpKQty,@Exp_Date              
		End                                                                   
	End                                                                       
	Close Cursor_ExpDate                                                      
	Deallocate Cursor_ExpDate                                                 
	Fetch Next From Cursor_Select Into @ACode, @KCode, @Differ                
	End                                                                       
	Close Cursor_Select                                                       
	Deallocate Cursor_Select 
