if exists(select 1 from sysobjects where name='tr_ms_sheet_master')
  drop Trigger tr_ms_sheet_master
go


create trigger tr_ms_sheet_master on t_ms_sheet_master  
for update 
as
begin
  if not update(approve_flag) return

  declare
    @sheet_no  		char(18) ,--工作单号
    @trans_no      	char(2)         ,--库存操作方式
    @db_no        	char(1)      ,--出入库方式
    @trade_type		char(1)	,--门店类型
    @branch_no       	char(6)        ,--本店代码
    @d_branch_no       	char(6)        ,--店代码
    @branch_no2         char(6)      ,--配料仓库
    @bom_branch		char(6)	     ,--冲减原料仓库
    @voucher_no      	char(18)         ,--原始凭证号
    @approve_flag   	char(1)      ,--审批标志  --0-未审核，1-审核
    @oper_date       	datetime   ,--操作日期
    @work_date       	datetime   ,--审核日期
    @valid_date       	datetime   ,--订单有效期
    @oper_id         	char(4),
    @confirm_man     	char(4),
    @sale_way       	char(1),
    @item_no         	char(20)        ,--货品代码
    @real_qty          	numeric(20,8)   ,--数量
    @orgi_price      	numeric(20,8)   ,--进货单价
    @valid_price     	numeric(20,8)   ,--零售单价
    @tax             	numeric(5,4),
    @item_valid_date    datetime,
    @sale_price 	numeric(20,8),  --入库时的零售价
    @stock_qty  	numeric(20,8),  --库存数量
    @stock_cost  	numeric(20,8),  --成本价格
    @tax_amt            numeric(20,8),   --税额
    @comb_item_no       char(20)        ,--货品代码   
    @com_real_qty       numeric(20,8)   ,--数量
    @com_price     	numeric(20,8)   ,
    @subitem_no       char(20)        ,--货品代码   
    @sub_qty       numeric(20,8),   --数量
    @branch_no_d    char(6), --明细仓库
    @sysvar_value   varchar(80), --系统参数	
    @in_price      numeric(20,8)  --配方原料进价

    declare co_cur_1 cursor for
    select a.sheet_no,a.trans_no,a.db_no,a.branch_no,a.d_branch_no,isnull(a.voucher_no,''),
           a.approve_flag,a.comb_item_no,a.com_item_qty,a.price,a.oper_date,a.work_date,a.oper_id,a.confirm_man,a.bom_branch_no
      from inserted a
     where a.approve_flag = '1' --单据数量不得为零

   
 
    open co_cur_1
    fetch co_cur_1 into
        @sheet_no,@trans_no,@db_no,@branch_no,@branch_no2,@voucher_no,
        @approve_flag,@comb_item_no,@com_real_qty,@com_price,@oper_date,@work_date,@oper_id,@confirm_man,@bom_branch
    
    while @@fetch_status=0
    begin

	  select @trade_type = trade_type From t_bd_branch_info where branch_no = left(@branch_no,3)
 
      if @trans_no='MS' --裱花入库单
      begin

     	 --单品入库
      	select @sale_price = null
        select @stock_cost=null
      	select @sale_price = sale_price from t_bd_item_info where item_no = @comb_item_no
      	select @stock_qty = null
      	select @stock_qty = stock_qty, @stock_cost = avg_cost from t_im_branch_stock where branch_no = @branch_no and item_no = @comb_item_no
	    select @stock_qty = @stock_qty + @real_qty

        if @stock_qty is null
	    select @stock_qty = 0

        if @stock_cost is null or @stock_cost=0
           select @stock_cost = @com_price
            
       	Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,
	 	sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,oper_id, pre_qnty,acc_date,sale_price)
      	values(@sheet_no,@work_date,@comb_item_no,@com_real_qty,@com_price,@stock_cost,
	   	@com_real_qty*@stock_cost,@branch_no,'',@trans_no,'+','', @oper_id,@stock_qty , null, @sale_price )
      	if @@error != 0 goto myerror
		
      end    
	
      if @trans_no='MC' --单店裱花入库单
      begin

     	 --单品入库
      	select @sale_price = null
        select @stock_cost=null
      	select @sale_price = sale_price from t_bd_item_info where item_no = @comb_item_no
      	select @stock_qty = null
      	select @stock_qty = stock_qty, @stock_cost = avg_cost from t_im_branch_stock where branch_no = @branch_no and item_no = @comb_item_no
	    select @stock_qty = @stock_qty + @real_qty

        if @stock_qty is null
	       select @stock_qty = 0

        if @stock_cost is null or @stock_cost=0
           select @stock_cost = @com_price
            
       	
		Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,
	 	sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,oper_id, pre_qnty,acc_date,sale_price)
      	values(@sheet_no,@work_date,@comb_item_no,@com_real_qty,@com_price,@stock_cost,
	   	@com_real_qty*@stock_cost,@branch_no,'',@trans_no,'+','', @oper_id,@stock_qty , null, @sale_price )
      	if @@error != 0 goto myerror

         --根据设置决定是否减少原材料库存。
	     if @trade_type = '3' 
		 begin
		   select @sysvar_value =  sys_var_value from t_sys_system where lower(sys_var_id) ='bom_stock_' + left(@branch_no,3)
	      if @bom_branch is null 
		      select @bom_branch= sys_var_value from t_sys_system where lower(sys_var_id) ='bom_stock_branch_' + left(@branch_no,3)
	     end 
	     else
	     begin
	 	   select @sysvar_value =  sys_var_value from t_sys_system where lower(sys_var_id) ='bom_stock' 
		   if @bom_branch is null 
		      select @bom_branch= sys_var_value from t_sys_system where lower(sys_var_id) ='bom_stock_branch'
	     end
	    If @sysvar_value = '1' 
        begin
		   DECLARE detail_cur cursor for 
		      select item_no,real_qty,orgi_price,valid_price,tax,sale_price
		       from t_ms_sheet_detail
		       where ( sheet_no = @sheet_no ) and  (real_qty<>0 )  --单据数量不得为零
		   open detail_cur
    		fetch detail_cur into @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price
    		while @@fetch_status=0
    		begin
                 	SELECT  @d_branch_no = t_bd_item_bom_detail.branch_no
			   FROM t_bd_item_bom_detail, t_bd_item_bom_master  
			   WHERE ( t_bd_item_bom_master.comb_item_no = t_bd_item_bom_detail.comb_item_no ) and  
   			   ( t_bd_item_bom_master.bom_no = t_bd_item_bom_detail.bom_no ) and    			
  			    ( t_bd_item_bom_master.is_default = '1' )     AND 
			    t_bd_item_bom_detail.item_no = @item_no AND  
			   t_bd_item_bom_detail.comb_item_no = @comb_item_no

			    select @stock_qty = null
    			select @stock_cost = null
    			select @stock_qty = stock_qty,@stock_cost=avg_cost from t_im_branch_stock where branch_no = @d_branch_no and item_no = @item_no
		
    			if @stock_qty is null
				select @stock_qty = 0

     			 --写流水
      			select @stock_qty = @stock_qty - @real_qty
               	 if @stock_cost is null or @stock_cost=0
      			select @stock_cost = @valid_price
   
      			Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,sale_price,
          			sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,oper_id, pre_qnty)
        		values(@sheet_no,@work_date,@item_no,@real_qty,@valid_price,@stock_cost,@sale_price,
          			@real_qty*@valid_price,@bom_branch,'',@trans_no,'-','',@oper_id,@stock_qty)
      			if @@error != 0 goto myerror      
		
		      fetch detail_cur into  @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price
  		   end

  		  close detail_cur
  		  deallocate detail_cur	
	    end
      end    

      if @trans_no='MB' --现烤工入库
      begin

	    DECLARE detail_cur cursor for 
	      select item_no,real_qty,orgi_price,valid_price,tax,sale_price,branch_no
	       from t_ms_sheet_detail
	       where ( sheet_no = @sheet_no ) and  (real_qty<>0 )  --单据数量不得为零
	    open detail_cur
    	fetch detail_cur into @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@branch_no_d
    	while @@fetch_status=0
    	begin

          --写流水
          if not @real_qty=0
          begin
            select @stock_cost = avg_cost from t_im_branch_stock where branch_no = @branch_no and item_no = @item_no
	
           if   @stock_cost is null or @stock_cost=0
                select @stock_cost = @valid_price
	       select @stock_qty = @stock_qty + @real_qty
	   
	       Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,
	             sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,
		     oper_id, pre_qnty,sale_price)
	           values(@sheet_no,@work_date,@item_no,@real_qty,@valid_price,@stock_cost,
	             @real_qty*@valid_price,@branch_no,'',@trans_no,'+','',
		     @oper_id,@stock_qty , @sale_price )
	        if @@error != 0 goto myerror
	      end 
	
          --按收货价更新商品进价
          if(select sys_var_value from t_sys_system where lower(sys_var_id)='pi_change_inprice')='1'
          begin
            update t_bd_item_info set price=@valid_price where item_no=@item_no and price <> @valid_price
            if @@error != 0 goto myerror
            
            --更新机构进价
            update t_pc_branch_price set price=@valid_price where item_no=@item_no and price <> @valid_price and branch_no=left(@branch_no,3)
          end 

           --根据设置决定是否减少原材料库存。
	      if @trade_type = '3' 
		   begin
			   select @sysvar_value =  sys_var_value from t_sys_system where lower(sys_var_id) ='bom_stock_' + left(@branch_no,3)
			   if @bom_branch is null 
			      select @bom_branch= sys_var_value from t_sys_system where lower(sys_var_id) ='bom_stock_branch_' + left(@branch_no,3)
		   end 
	       else
		   begin
	 		   select @sysvar_value =  sys_var_value from t_sys_system where lower(sys_var_id) ='bom_stock' 
			   if @bom_branch is null 
			      select @bom_branch= sys_var_value from t_sys_system where lower(sys_var_id) ='bom_stock_branch'
		   end
	       If @sysvar_value = '1' 
           begin
		      --插入对应BOM表的出库记录
	          --根据BOM配方中的“默认仓库”扣减库存
       		  
		     declare cur_bom cursor for
		       SELECT t_bd_item_bom_detail.item_no, t_bd_item_bom_detail.unit_qty,branch_no
		         FROM t_bd_item_bom_detail, t_bd_item_bom_master  
		        WHERE ( t_bd_item_bom_master.comb_item_no = t_bd_item_bom_detail.comb_item_no ) and  
		           ( t_bd_item_bom_master.bom_no = t_bd_item_bom_detail.bom_no ) and  
		           ( ( t_bd_item_bom_detail.comb_item_no = @item_no  ) AND  
		           ( t_bd_item_bom_master.is_default = '1' ) )    
		
		     open cur_bom
		     fetch cur_bom into  @subitem_no,@sub_qty,@d_branch_no
		     while @@fetch_status=0
		     begin
	                --应该取原材料售价及成本价（出项价取成本价还是按比例分摊父商品进项价？，还是取原料成本价吧）
			    select @sale_price = null
	            select @in_price = 0
			    select @sale_price = sale_price,@in_price=price from t_bd_item_info where item_no = @subitem_no
			    select @stock_qty = null
	  	        select @stock_cost = null
			    select @stock_qty = stock_qty, @stock_cost = avg_cost 
			     from t_im_branch_stock where branch_no = @d_branch_no and item_no = @subitem_no
			
			    if @stock_qty is null
				   select @stock_qty = 0
	                
	            if (@stock_cost is null) or (@stock_cost =0)
	                select @stock_cost = @in_price
	
			    select @stock_qty = @stock_qty - @real_qty
	
		        --if @branch_no_d is not null
			    --	select @d_branch_no = @branch_no_d
	
			    Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,
		             sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,
			     oper_id, pre_qnty,acc_date,sale_price)
		           values(@sheet_no,@work_date,@subitem_no,@real_qty*@sub_qty,@stock_cost,@stock_cost,
		             @real_qty*@sub_qty*@stock_cost,@bom_branch,'',@trans_no,'-','',
			     @oper_id,@stock_qty , null, @sale_price )
		    	if @@error != 0 goto myerror
	
		  	    fetch cur_bom into  @subitem_no,@sub_qty,@d_branch_no
		      end
		      close cur_bom
	  	      deallocate cur_bom
	       end

		  select @sale_price = null
	
		  fetch detail_cur into  @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@branch_no_d
	  	end
	
	    close detail_cur
	    deallocate detail_cur	
     end
    
     if @trans_no='ML' or @trans_no = 'ME' --领料单/领料出库单
    begin
	   DECLARE detail_cur cursor for 
	   select item_no,real_qty,orgi_price,valid_price,tax,sale_price,branch_no
	    from t_ms_sheet_detail
	    where ( sheet_no = @sheet_no ) and  (real_qty<>0 )  --单据数量不得为零
	open detail_cur
    	fetch detail_cur into @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@d_branch_no
    	while @@fetch_status=0
    	begin
                 
		select @stock_qty = null
    		select @stock_cost = null
    		select @stock_qty = stock_qty,@stock_cost=avg_cost from t_im_branch_stock where branch_no = @d_branch_no and item_no = @item_no

    		if @stock_qty is null
			select @stock_qty = 0

     		 --写流水
      		select @stock_qty = @stock_qty - @real_qty
               if @stock_cost is null or @stock_cost=0
      		select @stock_cost = @valid_price
               
      		Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,sale_price,
          		sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,oper_id, pre_qnty)
        	values(@sheet_no,@work_date,@item_no,@real_qty,@valid_price,@stock_cost,@sale_price,
          		@real_qty*@valid_price,@branch_no,@branch_no2,@trans_no,'-','',@oper_id,@stock_qty)
      		if @@error != 0 goto myerror 
	    
		
		fetch detail_cur into  @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@d_branch_no
  	end

  	close detail_cur
  	deallocate detail_cur
      end 
    
    if @trans_no='RE'  --领料退回单
      begin
	DECLARE detail_cur cursor for 
	   select item_no,real_qty,orgi_price,valid_price,tax,sale_price,branch_no
	    from t_ms_sheet_detail
	    where ( sheet_no = @sheet_no ) and  (real_qty<>0 )  --单据数量不得为零
	open detail_cur
    	fetch detail_cur into @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@d_branch_no
    	while @@fetch_status=0
    	begin
                 
		select @stock_qty = null
    		select @stock_cost = null
    		select @stock_qty = stock_qty,@stock_cost=avg_cost from t_im_branch_stock where branch_no = @d_branch_no and item_no = @item_no

    		if @stock_qty is null
			select @stock_qty = 0

     		 --写流水
      		select @stock_qty = @stock_qty - @real_qty
               if @stock_cost is null or @stock_cost=0
      		select @stock_cost = @valid_price
               
      		Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,sale_price,
          		sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,oper_id, pre_qnty)
        	values(@sheet_no,@work_date,@item_no,@real_qty,@valid_price,@stock_cost,@sale_price,
          		@real_qty*@valid_price,@branch_no,@branch_no2,@trans_no,'+','',@oper_id,@stock_qty)
      		if @@error != 0 goto myerror 
	    
		
		fetch detail_cur into  @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@d_branch_no
  	end

  	close detail_cur
  	deallocate detail_cur
      end 


      if @trans_no='MF' or @trans_no='MA'  --配料单 和 配料补发料
      begin
	   DECLARE detail_cur cursor for 
	   select item_no,real_qty,orgi_price,valid_price,tax,sale_price,branch_no
	    from t_ms_sheet_detail
	    where ( sheet_no = @sheet_no ) and  (real_qty<>0 )  --单据数量不得为零
	   open detail_cur
    	fetch detail_cur into @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@d_branch_no
    	while @@fetch_status=0
    	begin
                 
		    select @stock_qty = null
    		select @stock_cost = null
    		select @stock_qty = stock_qty,@stock_cost=avg_cost from t_im_branch_stock where branch_no = @d_branch_no and item_no = @item_no

    		if @stock_qty is null
			select @stock_qty = 0

     		 --写流水
      		select @stock_qty = @stock_qty - @real_qty
               if @stock_cost is null or @stock_cost=0
      		select @stock_cost = @valid_price
               
      		Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,sale_price,
          		sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,oper_id, pre_qnty)
        	values(@sheet_no,@work_date,@item_no,@real_qty,@valid_price,@stock_cost,@sale_price,
          		@real_qty*@valid_price,@branch_no,'',@trans_no,'-','',@oper_id,@stock_qty)
      		if @@error != 0 goto myerror 
		   fetch detail_cur into  @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@d_branch_no
  	   end

  	   close detail_cur
  	   deallocate detail_cur
    end 

   if @trans_no='MT'  --配料退料 
   begin
	    DECLARE detail_cur cursor for 
	    select item_no,real_qty,orgi_price,valid_price,tax,sale_price,branch_no
	    from t_ms_sheet_detail
	    where ( sheet_no = @sheet_no ) and  (real_qty<>0 )  --单据数量不得为零
	    open detail_cur
    	fetch detail_cur into @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@d_branch_no
    	while @@fetch_status=0
    	begin      
		    select @stock_qty = null
    		select @stock_cost = null
    		select @stock_qty = stock_qty,@stock_cost=avg_cost from t_im_branch_stock where branch_no = @d_branch_no and item_no = @item_no
    		if @stock_qty is null
			   select @stock_qty = 0
     		 --写流水
      		select @stock_qty = @stock_qty + @real_qty
               if @stock_cost is null or @stock_cost=0
      		select @stock_cost = @valid_price
      		Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,sale_price,
          		sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,oper_id, pre_qnty)
        	values(@sheet_no,@work_date,@item_no,@real_qty,@valid_price,@stock_cost,@sale_price,
          		@real_qty*@valid_price,@branch_no,'',@trans_no,'+','',@oper_id,@stock_qty)
      		if @@error != 0 goto myerror 
		   fetch detail_cur into  @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@d_branch_no
  	   end

  	   close detail_cur
  	   deallocate detail_cur
     end 
      
	 --现烤入库退回单，冲减BOM库存 gengyh 20100510  
	  if @trans_no='MK' --现烤工入库退回单
	  begin
	
		DECLARE detail_cur cursor for 
		   select item_no,real_qty,orgi_price,valid_price,tax,sale_price,branch_no
		    from t_ms_sheet_detail
		    where ( sheet_no = @sheet_no ) and  (real_qty<>0 )  --单据数量不得为零
		open detail_cur
	    fetch detail_cur into @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@branch_no_d
	    while @@fetch_status=0
	    begin
	
	          --写流水
	          if not @real_qty=0 
	          begin
	            select @stock_cost = avg_cost from t_im_branch_stock where branch_no = @branch_no and item_no = @item_no
		
	           if   @stock_cost is null or @stock_cost=0
	                select @stock_cost = @valid_price
				select @stock_qty = @stock_qty - @real_qty
			   	Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,
			        	sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,
					oper_id, pre_qnty,sale_price)
			       	values(@sheet_no,@work_date,@item_no,@real_qty,@valid_price,@stock_cost,
			         	 @real_qty*@valid_price,@branch_no,'',@trans_no,'-','',
				 	 @oper_id,@stock_qty , @sale_price )
		    if @@error != 0 goto myerror
		  end 
		
	          --按收货价更新商品进价
	          if(select sys_var_value from t_sys_system where lower(sys_var_id)='pi_change_inprice')='1'
	          begin
	            update t_bd_item_info set price=@valid_price where item_no=@item_no and price <> @valid_price
	            if @@error != 0 goto myerror
	            
	            --更新机构进价
	            update t_pc_branch_price set price=@valid_price where item_no=@item_no and price <> @valid_price and branch_no=left(@branch_no,3)
	          end 
	
	         --根据设置决定是否减少原材料库存。
	      if @trade_type = '3' 
		   begin
			   select @sysvar_value =  sys_var_value from t_sys_system where lower(sys_var_id) ='bom_stock_' + left(@branch_no,3)
			   if @bom_branch is null 
			      select @bom_branch= sys_var_value from t_sys_system where lower(sys_var_id) ='bom_stock_branch_' + left(@branch_no,3)
		   end 
	       else
		   begin
	 		   select @sysvar_value =  sys_var_value from t_sys_system where lower(sys_var_id) ='bom_stock' 
			   if @bom_branch is null 
			      select @bom_branch= sys_var_value from t_sys_system where lower(sys_var_id) ='bom_stock_branch'
		   end
		 If @sysvar_value = '1' 
	         begin
			  --插入对应BOM表的出库记录
			  declare cur_bom cursor for
			    SELECT t_bd_item_bom_detail.item_no, t_bd_item_bom_detail.unit_qty,branch_no
			      FROM t_bd_item_bom_detail, t_bd_item_bom_master  
			     WHERE ( t_bd_item_bom_master.comb_item_no = t_bd_item_bom_detail.comb_item_no ) and  
			           ( t_bd_item_bom_master.bom_no = t_bd_item_bom_detail.bom_no ) and  
			           ( ( t_bd_item_bom_detail.comb_item_no = @item_no  ) AND  
			           ( t_bd_item_bom_master.is_default = '1' ) )    
			
			  open cur_bom
			  fetch cur_bom into  @subitem_no,@sub_qty,@d_branch_no
			  while @@fetch_status=0
			  begin
	                        --应该取原材料售价及成本价（出项价取成本价还是按比例分摊父商品进项价？，还是取原料成本价吧）
				select @sale_price = null
	                        select @in_price = 0
				select @sale_price = sale_price,@in_price=price from t_bd_item_info where item_no = @subitem_no
				select @stock_qty = null
		  	        select @stock_cost = null
				select @stock_qty = stock_qty, @stock_cost = avg_cost 
				  from t_im_branch_stock where branch_no = @d_branch_no and item_no = @subitem_no
				
				if @stock_qty is null
					select @stock_qty = 0
	                        
	                        if (@stock_cost is null) or (@stock_cost =0)
	                               select @stock_cost = @in_price
					select @stock_qty = @stock_qty + @real_qty		
					Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,
				             sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,
					     oper_id, pre_qnty,acc_date,sale_price)
				           values(@sheet_no,@work_date,@subitem_no,@real_qty*@sub_qty,@stock_cost,@stock_cost,
				             @real_qty*@sub_qty*@stock_cost,@bom_branch,'',@trans_no,'+','',
		   			     @oper_id,@stock_qty , null, @sale_price )
			    	if @@error != 0 goto myerror
		
			  	fetch cur_bom into  @subitem_no,@sub_qty,@d_branch_no
			  end
			  close cur_bom
		  	  deallocate cur_bom
			end
	
			  select @sale_price = null
		
			fetch detail_cur into  @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@branch_no_d
		  	end
		
		 close detail_cur
		 deallocate detail_cur	
	      end
--现烤入库退回单，冲减BOM库存 gengyh 20100510 end

      if @trans_no = 'MR'   --生产入库单
      begin
	DECLARE detail_cur cursor for 
	   select item_no,real_qty,orgi_price,valid_price,tax,sale_price,branch_no
	    from t_ms_sheet_detail
	    where ( sheet_no = @sheet_no ) and  (real_qty<>0 )  --单据数量不得为零
	open detail_cur
    	fetch detail_cur into @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@d_branch_no
    	while @@fetch_status=0
    	begin
                 
		select @stock_qty = null
    		select @stock_cost = null
    		select @stock_qty = stock_qty,@stock_cost=avg_cost from t_im_branch_stock where branch_no = @d_branch_no and item_no = @item_no

    		if @stock_qty is null
			select @stock_qty = 0

     		 --写流水
      		select @stock_qty = @stock_qty + @real_qty
               if @stock_cost is null or @stock_cost=0
      		select @stock_cost = @valid_price
               
      		Insert into t_im_flow(voucher_no,oper_date, item_no,real_qty,price,cost_price,sale_price,
          		sheet_amt,branch_no,d_branch_no,trans_no,db_no,supcust_no,oper_id, pre_qnty)
        	values(@sheet_no,@work_date,@item_no,@real_qty,@valid_price,@stock_cost,@sale_price,
          		@real_qty*@valid_price,@branch_no,'',@trans_no,'+','',@oper_id,@stock_qty)
      		if @@error != 0 goto myerror 
	    
		
		fetch detail_cur into  @item_no,@real_qty,@orgi_price,@valid_price,@tax,@sale_price,@d_branch_no
  	end

  	close detail_cur
  	deallocate detail_cur
      end

      fetch co_cur_1 into
        @sheet_no,@trans_no,@db_no,@branch_no,@branch_no2,@voucher_no,
        @approve_flag,@comb_item_no,@com_real_qty,@com_price,@oper_date,@work_date,@oper_id,@confirm_man,@bom_branch
  end

  close co_cur_1
  deallocate co_cur_1

  return

myerror:
  close co_cur_1
  deallocate co_cur_1
  return
end
go