if exists(select 1 from sysobjects where name='pr_pc_price_flow_effect_rpx')
  drop procedure pr_pc_price_flow_effect_rpx
go

create procedure pr_pc_price_flow_effect_rpx 
              @branch_no   varchar(6), 
              @item_no     varchar(20),
              @new_price   numeric(16,4),
              @old_price   numeric(16,4),
              @branch_flag varchar(10),
              @price_type  char(1),
              @new_price2  numeric(16,4),
              @old_price2  numeric(16,4),
              @new_price3  numeric(16,4),
              @old_price3  numeric(16,4),
              @new_price4  numeric(16,4),
              @old_price4  numeric(16,4),
              @new_vip1    numeric(16,4),
              @old_vip1    numeric(16,4),
              @new_vip2    numeric(16,4),
              @old_vip2    numeric(16,4),
              @new_vip3    numeric(16,4),
              @old_vip3    numeric(16,4),
              @new_vip4    numeric(16,4),
              @old_vip4    numeric(16,4),
              @new_vip5    numeric(16,4),
              @old_vip5    numeric(16,4)
as
begin

  declare
  @px_comb_item_no    char(20)          ,--关联调价捆绑商品
  @px_item_no         char(20)          ,--关联调价商品
  @px_item_qty        numeric(16,4)     ,--成份数量
  @px_relation_px     char(1)           ,--关联调价标志
  @g_branch_no        char(6)           ,
  @update_date        char(10)          ,
  @ls_com_flag        char(1)           ,
  @branch_no_temp     char(6)


select @g_branch_no = sys_var_value from t_sys_system where lower(sys_var_id)='g_branch_no'
if @g_branch_no is null     select @g_branch_no = '000'

select @update_date=convert(char(10), getdate(), 121)

 declare rpx_cursor cursor for
      select h.comb_item_no, h.item_no, h.item_qty, h.relation_px
       from  t_bd_item_combsplit h  
       where h.item_no = @item_no  

  open rpx_cursor
  
  fetch rpx_cursor into  @px_comb_item_no, @px_item_no, @px_item_qty,@px_relation_px

  while @@fetch_status=0 
  begin
     if  @px_relation_px='1' --是关联调价商品
       begin
------------------------------------------------------------

 if @branch_no = @g_branch_no or @branch_no = 'ALL' 
     begin
        update t_bd_item_info 
          set price=@new_price*@px_item_qty,  base_price=@new_price2*@px_item_qty,  sale_price=@new_price3*@px_item_qty,  scheme_price=@new_price4*@px_item_qty,  update_date=@update_date ,
              vip_price = @new_vip1*@px_item_qty, vip_price2=@new_vip2*@px_item_qty,  vip_price3=@new_vip3*@px_item_qty, vip_price4=@new_vip4*@px_item_qty, vip_price5=@new_vip5*@px_item_qty
               where (item_no = @px_comb_item_no)
       end
       
       if @g_branch_no = '000'
       begin
          if @branch_no = 'All'
          begin
	      -- 先插入不存表中的各机构的商品 
	     declare branch_cursor cursor for
	      select distinct branch_no from t_bd_branch_info where property = 0 or property = 1 or property = 2		
	      open branch_cursor
	      fetch branch_cursor into @branch_no_temp
	      while @@fetch_status=0
	      begin

               IF exists(select * from t_pc_branch_price where(branch_no=@branch_no_temp and item_no=@px_comb_item_no )) -- 不存在
                     UPDATE t_pc_branch_price 
                        SET price=@new_price*@px_item_qty,  base_price=@new_price2*@px_item_qty,  sale_price=@new_price3*@px_item_qty , scheme_price=@new_price4*@px_item_qty, 
                            vip_price=@new_vip1*@px_item_qty, vip_price2=@new_vip2*@px_item_qty,  vip_price3=@new_vip3*@px_item_qty , vip_price4=@new_vip4*@px_item_qty, vip_price5=@new_vip5*@px_item_qty
                        WHERE branch_no=@branch_no_temp and item_no=@px_comb_item_no
                ELSE
                     INSERT INTO t_pc_branch_price 
                              ( branch_no, item_no, price, base_price, sale_price,vip_price,scheme_price,com_flag,vip_price2,vip_price3, vip_price4,vip_price5 )  
                         VALUES (@branch_no_temp, @px_comb_item_no, @new_price*@px_item_qty, @new_price2*@px_item_qty, @new_price3*@px_item_qty, @new_vip1*@px_item_qty, @new_price4*@px_item_qty, '0',@new_vip2*@px_item_qty, @new_vip3*@px_item_qty, @new_vip4*@px_item_qty, @new_vip5*@px_item_qty)
             fetch branch_cursor into @branch_no_temp
          end 

          deallocate  branch_cursor
       end
       
    else

          begin
               if exists(select '1' from t_pc_branch_price where (item_no = @px_comb_item_no) and branch_no=@branch_no) --是否存在
		  begin
                    UPDATE t_pc_branch_price 
                      SET price=@new_price*@px_item_qty ,base_price=@new_price2*@px_item_qty ,sale_price=@new_price3*@px_item_qty , scheme_price=@new_price4*@px_item_qty , 
                          vip_price=@new_vip1*@px_item_qty ,vip_price2=@new_vip2*@px_item_qty ,vip_price3=@new_vip3*@px_item_qty , vip_price4=@new_vip4*@px_item_qty ,vip_price5=@new_vip5*@px_item_qty
                      WHERE branch_no=@branch_no and item_no=@px_comb_item_no
                    end

                else
                   begin
                     INSERT INTO t_pc_branch_price 
                            ( branch_no, item_no, price, base_price, sale_price,vip_price,scheme_price,com_flag,vip_price2,vip_price3, vip_price4,vip_price5 )  
                          VALUES (@branch_no ,@px_comb_item_no ,@new_price*@px_item_qty , @new_price2*@px_item_qty , @new_price3*@px_item_qty ,@new_vip1*@px_item_qty ,@new_price4*@px_item_qty ,'0',@new_vip2*@px_item_qty ,@new_vip3*@px_item_qty ,@new_vip4*@px_item_qty ,@new_vip5*@px_item_qty )
                   end
          end 
       end


       else --if (@branch_no = @g_branch_no or @branch_no = 'ALL') and @g_branch_no <> '000' --分部
       begin
          if (@branch_flag <> @g_branch_no) -- 非分部本地的调价单(即总部的调价单)
            select @ls_com_flag = '1'
          else
            select @ls_com_flag = '0'

          if  exists(select '1' from t_pc_branch_price where (item_no = @px_comb_item_no) and branch_no=@branch_no)--@g_branch_no) --是否存在
             begin
              update t_pc_branch_price 
                 set price=@new_price*@px_item_qty ,sale_price=@new_price2*@px_item_qty ,base_price=@new_price3*@px_item_qty ,scheme_price=@new_price4*@px_item_qty ,com_flag=@ls_com_flag,
                     vip_price=@new_vip1*@px_item_qty ,vip_price2=@new_vip2*@px_item_qty ,vip_price3=@new_vip3*@px_item_qty ,vip_price4=@new_vip4*@px_item_qty ,vip_price5=@new_vip5*@px_item_qty 
                 where (item_no = @px_comb_item_no) and (branch_no = @g_branch_no)
            end
         else
            begin
               INSERT INTO t_pc_branch_price  
                      ( branch_no, item_no, price, base_price, sale_price,vip_price,scheme_price,com_flag,vip_price2,vip_price3, vip_price4,vip_price5 )  
               VALUES (@branch_no,@px_comb_item_no,@new_price*@px_item_qty , @new_price2*@px_item_qty , @new_price3*@px_item_qty ,@new_vip1*@px_item_qty ,@new_price4*@px_item_qty , @ls_com_flag*@px_item_qty ,@new_vip2*@px_item_qty ,@new_vip3*@px_item_qty ,@new_vip4*@px_item_qty ,@new_vip5*@px_item_qty )
            end
     end 
 
------------------------------------------------------------
    end

        fetch rpx_cursor into  @px_comb_item_no, @px_item_no, @px_item_qty,@px_relation_px

  end

end


--close rpx_cursor
deallocate  rpx_cursor
go

