Q5/先进先出220513.txt

ALTER PROCEDURE [dbo].[p_outware_cost_fifo_procedure](        
           @arg_scid int,        
           @arg_mtrlwareid int,       
           @arg_date datetime,        
           @arg_mod int,       
           @arg_outwareid int,       
           @arg_mtrlwaremxid int,       
           @arg_rslt int out,        
           @arg_msg varchar(255) out)        
           AS        
           BEGIN        
                   
           	SET @arg_rslt = 0        
           	SET @arg_msg = ''        
                   
           	DECLARE @error int        
           	DECLARE @rowcount int        
           	        
           	SET @error = null        
           	SET @rowcount = null       
           	       
           	DECLARE @cur1_outwareid int       
           	DECLARE @cur1_printid int       
           	DECLARE @cur1_qty decimal(23,10)       
           	DECLARE @cur1_curqty decimal(23,10)       
           	DECLARE @cur1_if_inware int       
           	       
           	DECLARE @cur_new_mtrlwaremxid int       
           	DECLARE @cur_new_usable_qty decimal(23,10)       
           	DECLARE @cur_new_inprice decimal(23,10)  
			DECLARE @cur_new_inprice_notax decimal(23,10)  
           	DECLARE @cur_new_outqty decimal(23,10)       
           	       
           	        
           	        
           	        
           	IF @arg_mod=1 --正数进仓单 或 负数出仓单  仓审 恢复入库批出仓数量       
           	BEGIN     
           	    
           	 --先判断一下是否要继续执行存储过程 优化速度yyuu    
           	   DECLARE @rowcount_inmx int     
           	   select  @rowcount_inmx=COUNT(*) from u_mtrlware_inmx       
                          Where inwaredate >=@arg_date  and  outqty>0 and scid =@arg_scid  and mtrlwareid=@arg_mtrlwareid    
           	       
           	   -- IF @rowcount_inmx = 0        
           	  -- BEGIN     
           	    -- return 1    
           	 --  END    
           	       
           	       
           	      
           	       
           	       
                update u_mtrlware_inmx set u_mtrlware_inmx.outqty = case when  u_mtrlware_inmx.outqty - isnull(u_temp.sumqty,0) <0 then 0 else u_mtrlware_inmx.outqty - ISNULL(u_temp.sumqty,0) end       
                
                from u_mtrlware_inmx inner join       
                 (       
                    Select  mtrlwaremxid ,SUM(qty) as sumqty from u_outwaremx_mtrlwaremx        
                    where Mtrlwaremxid  in         
                         (select  Mtrlwaremxid  from u_mtrlware_inmx       
                          Where inwaredate >=@arg_date  and  outqty>0 and scid =@arg_scid  and mtrlwareid=@arg_mtrlwareid )       
                    group by mtrlwaremxid       
                  ) u_temp on u_temp.mtrlwaremxid=u_mtrlware_inmx.mtrlwaremxid       
                         
                  SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;        
           			IF @error <> 0        
           			BEGIN        
           				SET @arg_msg = '未知原因,更新库存入仓批已出仓数失败1'        
           	            RAISERROR(@arg_msg, 18, 1)       
           			END        
           			       
           			       
           		--删除	u_outwaremx_mtrlwaremx        
           			       
           			delete u_outwaremx_mtrlwaremx from u_outwaremx_mtrlwaremx        
                    where Mtrlwaremxid  in         
                         (select  Mtrlwaremxid  from u_mtrlware_inmx       
                          Where inwaredate >=@arg_date   and scid =@arg_scid  and mtrlwareid=@arg_mtrlwareid )       
           			       
           		 SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;        
           			IF @error <> 0        
           			BEGIN        
           				SET @arg_msg = '未知原因,删除成本组成明细1'        
           	            RAISERROR(@arg_msg, 18, 1)       
           			END 	       
           			       
           			delete u_outwaremx_mtrlwaremx where mtrlwaremxid=@arg_mtrlwaremxid       
           			 SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;        
           			           IF @error <> 0        
           			           BEGIN        
           				         SET @arg_msg = '未知原因,删除成本组成明细mtrlwaremxid'        
           	                     RAISERROR(@arg_msg, 18, 1)       
           			           END 	       
           			       
           			       
           			       
           			       
           			       
           	END         
           	        
           	        
           	IF @arg_mod=2 --正数出仓单 或 负数进仓单 仓审 恢复入库批出仓数量       
           	BEGIN        
                 
               update u_mtrlware_inmx set u_mtrlware_inmx.outqty =case when  u_mtrlware_inmx.outqty - isnull(u_temp.sumqty,0) <0 then 0 else u_mtrlware_inmx.outqty - ISNULL(u_temp.sumqty,0) end       
                
               from u_mtrlware_inmx inner join       
                    (       
                    
                               Select  mtrlwaremxid ,isnull(SUM(qty),0) as sumqty from       
                                (           
                                     select u_outwaremx_mtrlwaremx.mtrlwaremxid  as mtrlwaremxid,       
                                     isnull(SUM(u_outwaremx_mtrlwaremx.qty ),0)  as qty       
                                     from u_outwaremx inner Join u_outware on u_outware.scid =u_outwaremx.scid and  u_outware.outwareid  =u_outwaremx.outwareid       
                                     inner join u_outwaremx_mtrlwaremx on u_outwaremx_mtrlwaremx.scid =u_outwaremx.scid and u_outwaremx_mtrlwaremx.outwareid  =u_outwaremx.outwareid and u_outwaremx_mtrlwaremx.printid   =u_outwaremx.printid       
                                     where u_outwaremx_mtrlwaremx.if_inware=0 and u_outwaremx.qty >0 and u_outwaremx.scid =@arg_scid and mtrlwareid =@arg_mtrlwareid and u_outware.outdate >=@arg_date and (flag=1 or u_outware.outwareid=@arg_outwareid)       
                                     group by mtrlwaremxid       
                                            
                                     union all       
                      
                                     select u_outwaremx_mtrlwaremx.mtrlwaremxid  as mtrlwaremxid,       
                                     isnull(SUM(u_outwaremx_mtrlwaremx.qty ),0)  as qty       
                                     from u_inwaremx inner Join u_inware on u_inware.scid =u_inwaremx.scid and  u_inware.inwareid  =u_inwaremx.inwareid       
                                     inner join u_outwaremx_mtrlwaremx on u_outwaremx_mtrlwaremx.scid =u_inwaremx.scid and u_outwaremx_mtrlwaremx.outwareid  =u_inwaremx.inwareid and u_outwaremx_mtrlwaremx.printid   =u_inwaremx.printid       
                                     where  u_outwaremx_mtrlwaremx.if_inware=1 and  u_inwaremx.qty <0 and u_inwaremx.scid =@arg_scid and mtrlwareid =@arg_mtrlwareid and u_inware.indate >=@arg_date and (flag=1 or u_inware.inwareid=@arg_outwareid)       
                                     group by mtrlwaremxid       
                                 ) as v_zuhe_outwaremx  group by mtrlwaremxid       
                     
                    ) u_temp on u_temp.mtrlwaremxid=u_mtrlware_inmx.mtrlwaremxid       
                         
                  SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;        
           			IF @error <> 0        
           			BEGIN        
           				SET @arg_msg = '未知原因,更新库存入仓批已出仓数失败2'        
           	            RAISERROR(@arg_msg, 18, 1)       
           			END        
           			       
           			       
           		--删除	u_outwaremx_mtrlwaremx 	       
           			       
           			delete u_outwaremx_mtrlwaremx         
                     from u_outwaremx inner Join u_outware on u_outware.scid =u_outwaremx.scid and  u_outware.outwareid  =u_outwaremx.outwareid       
                     inner join u_outwaremx_mtrlwaremx on u_outwaremx_mtrlwaremx.scid =u_outwaremx.scid and u_outwaremx_mtrlwaremx.outwareid  =u_outwaremx.outwareid and u_outwaremx_mtrlwaremx.printid   =u_outwaremx.printid       
                     where u_outwaremx_mtrlwaremx.if_inware=0 and u_outwaremx.qty >0 and u_outwaremx.scid=@arg_scid  and mtrlwareid =@arg_mtrlwareid and u_outware.outdate >=@arg_date and (flag=1 or u_outware.outwareid=@arg_outwareid)       
           			       
           			SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;        
           			      IF @error <> 0        
           			      BEGIN        
           				     SET @arg_msg = '未知原因,删除成本组成明细3'        
           	                 RAISERROR(@arg_msg, 18, 1)       
           			      END 	       
           			             
           			         
           			delete u_outwaremx_mtrlwaremx            
           			from u_inwaremx inner Join u_inware on u_inware.scid =u_inwaremx.scid and  u_inware.inwareid  =u_inwaremx.inwareid       
                     inner join u_outwaremx_mtrlwaremx on u_outwaremx_mtrlwaremx.scid =u_inwaremx.scid and u_outwaremx_mtrlwaremx.outwareid  =u_inwaremx.inwareid and u_outwaremx_mtrlwaremx.printid   =u_inwaremx.printid       
                     where  u_outwaremx_mtrlwaremx.if_inware=1 and  u_inwaremx.qty <0  and u_inwaremx.scid =@arg_scid  and mtrlwareid =@arg_mtrlwareid and u_inware.indate >=@arg_date and (flag=1 or u_inware.inwareid=@arg_outwareid)       
           			       
           			SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;        
           			      IF @error <> 0        
           			      BEGIN        
           				     SET @arg_msg = '未知原因,删除成本组成明细4'        
           	                 RAISERROR(@arg_msg, 18, 1)       
           			      END 	  			       
           			       
         		       
           			       
           	END         
           	        
                
           	--双重游标算出仓成本        
           	DECLARE cur1 CURSOR LOCAL FOR        
           	       
                 select       
                    u_temp1.outwareid as outwareid,       
                    u_temp1.printid  as printid,       
                    u_temp1.qty   as qty,       
                    u_temp1.curqty as curqty,       
                    u_temp1.if_inware as if_inware       
                 from        
                
                 (       
                
                 select u_outwaremx.outwareid  as outwareid,       
                u_outwaremx.printid  as printid,       
                u_outwaremx.qty   as qty,       
                isnull(u_temp.curqty,0) as curqty,       
                u_outware.outdate as chdate,       
                0 as If_inware       
                  from u_outwaremx  inner join u_outware on u_outware.scid= u_outwaremx.scid and u_outware.outwareid = u_outwaremx.outwareid       
                 
                  left join       
                 (       
                 select scid,outwareid,printid  ,isnull(SUM(qty),0) as curqty from u_outwaremx_mtrlwaremx       
                 where u_outwaremx_mtrlwaremx.if_inware=0  group by scid,outwareid ,printid       
                 ) u_temp on u_outwaremx.scid =u_temp.scid and u_outwaremx.outwareid =u_temp.outwareid and u_outwaremx.printid  =u_temp.printid       
                 where  u_outwaremx.scid =@arg_scid and u_outwaremx.mtrlwareid =@arg_mtrlwareid and       
                u_outwaremx.qty - isnull(u_temp.curqty,0) >0 and u_outwaremx.qty>0 and u_outware.flag =1       
                       
                       
                       
                  union all       
                 
                   select u_inwaremx.inwareid   as outwareid,       
                   u_inwaremx.printid  as printid,       
                   abs(u_inwaremx.qty)   as qty,       
                   isnull(u_temp.curqty,0) as curqty,       
                   u_inware.indate as chdate,       
                   1 as If_inware       
                        
                   from u_inwaremx  inner join u_inware on u_inware.scid= u_inwaremx.scid and u_inware.inwareid = u_inwaremx.inwareid       
                 
                   left join       
                     (       
                       select scid,outwareid,printid  ,isnull(SUM(qty),0) as curqty from u_outwaremx_mtrlwaremx       
                       where u_outwaremx_mtrlwaremx.if_inware=1  group by scid,outwareid ,printid       
                     ) u_temp on u_inwaremx.scid =u_temp.scid and u_inwaremx.inwareid =u_temp.outwareid and u_inwaremx.printid  =u_temp.printid       
                       where  u_inwaremx.scid =@arg_scid and u_inwaremx.mtrlwareid =@arg_mtrlwareid and       
                      abs(  u_inwaremx.qty) - isnull(u_temp.curqty,0) >0 and u_inwaremx.qty<0 and u_inware.flag =1             
                       
                       
                     ) as u_temp1  order by u_temp1.chdate;        
           	       
           	       
           	OPEN cur1;        
           	set @cur1_outwareid =0       
           	set @cur1_printid =0       
           	set @cur1_qty =0       
           	set @cur1_curqty =0       
           	set @cur1_if_inware =0       
           	FETCH cur1 INTO @cur1_outwareid,@cur1_printid,@cur1_qty,@cur1_curqty,@cur1_if_inware;        
           	WHILE @@FETCH_STATUS = 0 AND @cur1_qty <> 0        
           	BEGIN        
           	       
           	   DECLARE @remain_cur1_qty decimal(23,10)       
           	   set @remain_cur1_qty= @cur1_qty -  @cur1_curqty --计算还差多少数量未分配       
           	       
           	   DECLARE cur_new CURSOR LOCAL FOR       
           	           select mtrlwaremxid,inqty - outqty as usable_qty ,inprice,inprice_notax ,outqty         
           	           from u_mtrlware_inmx        
           	           where inqty - outqty >0 and scid=@arg_scid and mtrlwareid=@arg_mtrlwareid       
           	           order by inwaredate , Mtrlwaremxid ;       
           	          
           	   OPEN cur_new;        
           	   set @cur_new_mtrlwaremxid=0       
           	   set @cur_new_usable_qty =0       
           	   set @cur_new_inprice =0     
			   set @cur_new_inprice_notax = 0
           	   set @cur_new_outqty =0       
           	   FETCH cur_new INTO @cur_new_mtrlwaremxid,@cur_new_usable_qty,@cur_new_inprice,@cur_new_inprice_notax,@cur_new_outqty;        
           	   WHILE @@FETCH_STATUS = 0 AND @remain_cur1_qty > 0        
           	   BEGIN        
           	                   
           	            DECLARE @NOW_QTY decimal(23,10)       
                       	IF @remain_cur1_qty<=@cur_new_usable_qty         
           	            BEGIN        
           	               set @NOW_QTY=@remain_cur1_qty       
           	               set @remain_cur1_qty=0       
           	            END       
           	            ELSE       
           	            BEGIN       
           	               set @NOW_QTY=@cur_new_usable_qty       
           	               set @remain_cur1_qty=@remain_cur1_qty - @cur_new_usable_qty       
           	            END       
           	                   
           	                 --更新库存入仓明细已占用数       
           	                 update u_mtrlware_inmx  set outqty=outqty + isnull(@NOW_QTY,0) where mtrlwaremxid=@cur_new_mtrlwaremxid       
           	                 SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;        
           			                IF @error <> 0        
           			                BEGIN        
           				              SET @arg_msg = '未知原因,更新库存入仓明细已占用数失败'        
           	                        RAISERROR(@arg_msg, 18, 1)       
           			                END        
           			                 
           			         --插入成本组成表明细       
           			         INSERT INTO u_outwaremx_mtrlwaremx       
                              (scid,       
                               outwareid,       
                               printid,       
                               mtrlwaremxid,       
                               qty,       
                               price, 
							   price_notax,
                               If_inware)       
                              VALUES       
                              (       
                               @arg_scid,       
                               @cur1_outwareid,       
                               @cur1_printid,       
                               @cur_new_mtrlwaremxid,       
                               @NOW_QTY,       
                               @cur_new_inprice,   
							   @cur_new_inprice_notax,
                               @cur1_if_inware       
                              )       
           			         SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;        
           			                IF @error <> 0        
           			                BEGIN        
           				              SET @arg_msg = '未知原因,插入成本组成表明细失败'        
           	                        RAISERROR(@arg_msg, 18, 1)       
           			                END          
           			          	       
           	          
           	        set @cur_new_mtrlwaremxid=0       
           	        set @cur_new_usable_qty =0       
           	        set @cur_new_inprice =0   
					set @cur_new_inprice_notax = 0
           	        set @cur_new_outqty =0       
           	        FETCH cur_new INTO @cur_new_mtrlwaremxid,@cur_new_usable_qty,@cur_new_inprice,@cur_new_inprice_notax,@cur_new_outqty;        
           	   END       
           	   CLOSE cur_new;        
           	   DEALLOCATE cur_new;       
           	       
           	       
           	--通过成本组成表明细 计算 u_outwaremx 或 u_inwaremx 的 cost       
           	  IF @cur1_if_inware = 0        
               BEGIN        
                       
                update u_outwaremx set  u_outwaremx.cost=u_temp.amt / u_temp.qty 
					,u_outwaremx.costamt = u_temp.amt 
					,u_outwaremx.cost_notax= u_temp.amt_notax / u_temp.qty 
					,u_outwaremx.costamt_notax = u_temp.amt_notax   
                from u_outwaremx inner join        
                (       
                 select scid,outwareid,printid,
					isnull(SUM(qty),0) as qty,
					isnull(SUM(qty * price),0) as amt,
					isnull(SUM(qty * price_notax),0) as amt_notax
					from u_outwaremx_mtrlwaremx       
                 where  if_inware=0 and scid=@arg_scid and outwareid =@cur1_outwareid and printid=@cur1_printid       
                 group by scid,outwareid,printid       
                ) u_temp on u_temp.scid=u_outwaremx.scid and u_temp.outwareid =u_outwaremx.outwareid and u_temp.printid  =u_outwaremx.printid       
                where u_temp.qty <>0 and u_outwaremx.scid=@arg_scid and u_outwaremx.outwareid =@cur1_outwareid and u_outwaremx.printid  =@cur1_printid       
                 SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;        
           			                IF @error <> 0        
           			                BEGIN        
           				              SET @arg_msg = '未知原因,计算出仓明细成本失败1'        
           	                        RAISERROR(@arg_msg, 18, 1)       
           			                END          
                      
                 
               END          
               ELSE  -- @cur1_if_inware = 1       
           	  BEGIN       
           	           
                update u_inwaremx set  u_inwaremx.cost=u_temp.amt / u_temp.qty ,u_inwaremx.costamt =u_temp.amt  * (-1)         
                from u_inwaremx inner join        
                (       
                 select scid,outwareid,printid,isnull(SUM(qty),0) as qty,isnull(SUM(qty * price),0) as amt from u_outwaremx_mtrlwaremx       
                 where  if_inware=1 and scid=@arg_scid and outwareid =@cur1_outwareid and printid=@cur1_printid       
                 group by scid,outwareid,printid       
                ) u_temp on u_temp.scid=u_inwaremx.scid and u_temp.outwareid =u_inwaremx.inwareid and u_temp.printid  =u_inwaremx.printid       
                where u_temp.qty <>0 and u_inwaremx.scid=@arg_scid and u_inwaremx.inwareid =@cur1_outwareid and u_inwaremx.printid  =@cur1_printid       
                 SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;        
           			                IF @error <> 0        
           			                BEGIN        
           				              SET @arg_msg = '未知原因,计算出仓明细成本失败2'        
           	                        RAISERROR(@arg_msg, 18, 1)       
           			                END          
           	  END       
           	       
           	       
           	set @cur1_outwareid =0       
           	set @cur1_printid =0       
           	set @cur1_qty =0       
           	set @cur1_curqty =0       
           	set @cur1_if_inware =0       
           	FETCH cur1 INTO @cur1_outwareid,@cur1_printid,@cur1_qty,@cur1_curqty,@cur1_if_inware;        
           	END       
           	CLOSE cur1;        
           	DEALLOCATE cur1;        
           	        
          --更新库存表 成本 成本金额q      
                
            update u_mtrlware  set  u_mtrlware.cost=case when u_temp.qty=0 then u_mtrlware.cost else  u_temp.amt / u_temp.qty end 
					,u_mtrlware.wareamt =u_temp.amt  
					,u_mtrlware.cost_notax =case when u_temp.qty=0 then u_mtrlware.cost_notax else  u_temp.amt_notax / u_temp.qty end 
					,u_mtrlware.wareamt_notax = u_temp.amt_notax
                from u_mtrlware left join        
                (       
                
            select scid,mtrlwareid ,isnull(sum(inqty - outqty),0) as qty ,isnull(sum((inqty - outqty) * inprice),0) as amt, isnull(sum((inqty - outqty) * inprice_notax),0) as amt_notax             
           	           from u_mtrlware_inmx        
           	           where inqty - outqty >=0 and scid=@arg_scid and mtrlwareid=@arg_mtrlwareid  	        
                group by scid,mtrlwareid      
           	 ) u_temp on u_temp.scid =u_mtrlware.scid  and u_temp.mtrlwareid =u_mtrlware.mtrlwareid      
           	 where u_temp.qty >=0 and u_mtrlware.scid=@arg_scid and u_mtrlware.mtrlwareid=@arg_mtrlwareid      
           	 SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT;        
           			                IF @error <> 0        
           			                BEGIN        
           				              SET @arg_msg = '未知原因,更新库存表成本失败'        
           	                        RAISERROR(@arg_msg, 18, 1)       
           			                END          
           	       
           	       
           	       
           END        

GO