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

create procedure pr_pc_price_flow_effect 
as
begin

  declare 
  @trans_no            char(2)           ,--交易编号
  @branch_no           varchar(6)           ,--仓库编号
  @sheet_no            char(18)          ,--单据编号
  @item_no             char(20)          ,--货品代码
  @valid_flag          char(1)           ,--生效状态
  @g_branch_no         char(6)           ,--系统仓库编号
  @is_distributed      char(1),           --分布式版
  @update_date         char(10)          ,--龙飞收款机接口 改t_bd_item_info.other3
  @old_price            numeric(16,4)     ,--原进价
  @new_price            numeric(16,4)     ,--新进价
  @old_price2           numeric(16,4)     ,--原批发价
  @new_price2           numeric(16,4)     ,--新批发价
  @old_price3           numeric(16,4)     ,--原售价
  @new_price3           numeric(16,4)     ,--新售价
  @old_price4           numeric(16,4)     ,--原配送价
  @new_price4           numeric(16,4)     ,--新配送价
  @branch_flag         varchar(10)        ,--调价制单机构
  @ls_com_flag         char(1)            ,--传输标志
  @price_type	       char(1)		  ,--价格类型
  @g_stock_no          char(6)            ,--系统仓库编号
  @g_stock_type	       char(1)		  ,--门店类型
  @old_vip1           numeric(16,4)     ,--原会员价1
  @new_vip1           numeric(16,4)     ,--新会员价1
  @old_vip2           numeric(16,4)     ,--原会员价2
  @new_vip2           numeric(16,4)     ,--新会员价2
  @old_vip3           numeric(16,4)     ,--原会员价3
  @new_vip3           numeric(16,4)     ,--新会员价3
  @old_vip4           numeric(16,4)     ,--原会员价4
  @new_vip4           numeric(16,4)     ,--新会员价4
  @old_vip5           numeric(16,4)     ,--原会员价5
  @new_vip5           numeric(16,4)     , --新会员价5
  @branch_list        varchar(255),     --调价标识
  @item_price         numeric(16,4),
  @item_base_price    numeric(16,4),
  @item_sale_price    numeric(16,4),
  @item_scheme_price  numeric(16,4),
  @item_vip_price     numeric(16,4),
  @item_vip_price2    numeric(16,4),
  @item_vip_price3    numeric(16,4),
  @item_vip_price4    numeric(16,4),
  @item_vip_price5    numeric(16,4),
  @branch_no_px       char(6),
  @branch_list2       varchar(255)
  
  --设置过程运行标志
  if not exists(select sys_var_value from t_sys_system where lower(sys_var_id)='change_price')
	  insert into t_sys_system(sys_var_id,sys_var_name) values('change_price','change_price')
  else
	  return
 
  select @update_date=convert(char(10), getdate(), 121)
	  
  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 @g_stock_no = sys_var_value from t_sys_system where lower(sys_var_id)='g_stock_no'
  if @g_stock_no is null     select @g_stock_no = '000001'

  select @g_stock_type = trade_type from t_bd_branch_info where branch_no = @g_stock_no

  select @is_distributed = sys_var_value from t_sys_system where lower(sys_var_id)='is_distributed'
  if @is_distributed is null     select @is_distributed = '0'

  --对于分部，以机构当前的价格修正商品信息表
  if @g_branch_no <> '000' and @g_stock_type <> '7' and  @g_stock_type <> '8' 
  begin
  	update t_bd_item_info set 
		price=t_pc_branch_price.price,
		base_price=t_pc_branch_price.base_price,
		sale_price=t_pc_branch_price.sale_price,
		vip_price=t_pc_branch_price.vip_price,
	scheme_price=t_pc_branch_price.scheme_price,
	vip_price2=t_pc_branch_price.vip_price2,
	vip_price3=t_pc_branch_price.vip_price3,
	vip_price4=t_pc_branch_price.vip_price4,
	vip_price5=t_pc_branch_price.vip_price5
		from t_bd_item_info,t_pc_branch_price
		where t_bd_item_info.item_no=t_pc_branch_price.item_no
		  and t_pc_branch_price.branch_no=@g_branch_no
		  and (t_bd_item_info.price <> t_pc_branch_price.price 
			or t_bd_item_info.base_price <> t_pc_branch_price.base_price 
			or t_bd_item_info.sale_price <> t_pc_branch_price.sale_price 
			or t_bd_item_info.vip_price <> t_pc_branch_price.vip_price 
			or t_bd_item_info.scheme_price <> t_pc_branch_price.scheme_price   )
		if @@error != 0 

		   return
  end

  if @g_stock_type = '7'or @g_stock_type = '8' 
  begin	
  	update t_bd_item_info set 
		price=t_pc_branch_price.price,
		base_price=t_pc_branch_price.base_price,
		sale_price=t_pc_branch_price.sale_price,
		vip_price=t_pc_branch_price.vip_price,
	scheme_price=t_pc_branch_price.scheme_price,
	vip_price2=t_pc_branch_price.vip_price2,
	vip_price3=t_pc_branch_price.vip_price3,
	vip_price4=t_pc_branch_price.vip_price4,
	vip_price5=t_pc_branch_price.vip_price5
		from t_bd_item_info,t_pc_branch_price
		where t_bd_item_info.item_no=t_pc_branch_price.item_no
		  and ( t_pc_branch_price.branch_no=@g_branch_no )
		  and (t_bd_item_info.price <> t_pc_branch_price.price 
			or t_bd_item_info.base_price <> t_pc_branch_price.base_price 
			or t_bd_item_info.sale_price <> t_pc_branch_price.sale_price 
			or t_bd_item_info.vip_price <> t_pc_branch_price.vip_price 
			or t_bd_item_info.scheme_price <> t_pc_branch_price.scheme_price   )
		if @@error != 0 
		   return

	  update t_bd_item_info set 
		price=t_pc_branch_price.price,
		base_price=t_pc_branch_price.base_price,
		sale_price=t_pc_branch_price.sale_price,
		vip_price=t_pc_branch_price.vip_price,
	scheme_price=t_pc_branch_price.scheme_price,
	vip_price2=t_pc_branch_price.vip_price2,
	vip_price3=t_pc_branch_price.vip_price3,
	vip_price4=t_pc_branch_price.vip_price4,
	vip_price5=t_pc_branch_price.vip_price5
		from t_bd_item_info,t_pc_branch_price
		where t_bd_item_info.item_no=t_pc_branch_price.item_no
		  and (t_pc_branch_price.branch_no=@g_stock_no)
		  and (t_bd_item_info.price <> t_pc_branch_price.price 
			or t_bd_item_info.base_price <> t_pc_branch_price.base_price 
			or t_bd_item_info.sale_price <> t_pc_branch_price.sale_price 
			or t_bd_item_info.vip_price <> t_pc_branch_price.vip_price 
			or t_bd_item_info.scheme_price <> t_pc_branch_price.scheme_price   )
		if @@error != 0 
		   return
  end
--          and t_bd_item_info.branch_price='1'


  -- valid_flag ( '0':未生效, '1':已生效 , '2':已更新仓库价格表但未生效)
   if @g_branch_no = '000'
   	declare priceflow_cursor cursor for
	   select distinct a.trans_no, rtrim(a.branch_no), a.sheet_no,
					   a.valid_flag, b.item_no, b.new_price,b.old_price, a.branch_flag, b.price_type,
			   b.new_price2,b.old_price2,b.new_price3,b.old_price3,b.new_price4,b.old_price4,
			   b.new_vip1,b.old_vip1,b.new_vip2,b.old_vip2,b.new_vip3,b.old_vip3,b.new_vip4,b.old_vip4,b.new_vip5,b.old_vip5,
			   a.branch_list,a.branch_list2
		 from t_pc_price_flow_master a, 
			  t_pc_price_flow_detail b   
		where ( a.sheet_no = b.sheet_no ) 
		  and ( a.approve_flag = '1') and ( a.trans_no = 'PX') 
		  and ( a.valid_flag = '0')  and ( a.valid_date <= getdate())
		order by a.sheet_no 
  else
	 declare priceflow_cursor cursor for
	   select distinct a.trans_no, rtrim(a.branch_no), a.sheet_no,
					   a.valid_flag, b.item_no, b.new_price,b.old_price, a.branch_flag,b.price_type,
			   b.new_price2,b.old_price2,b.new_price3,b.old_price3,b.new_price4,b.old_price4,
			   b.new_vip1,b.old_vip1,b.new_vip2,b.old_vip2,b.new_vip3,b.old_vip3,b.new_vip4,b.old_vip4,b.new_vip5,b.old_vip5,
			   a.branch_list,a.branch_list2
		 from t_pc_price_flow_master a, 
			  t_pc_price_flow_detail b   
		where ( a.sheet_no = b.sheet_no ) 
		  and ( a.approve_flag = '1') and ( a.trans_no = 'PX' ) 
		  and ( a.valid_flag = '0' or a.valid_flag = '2')
		  and ( a.valid_date <= getdate())
		order by a.sheet_no 


  open priceflow_cursor
  fetch priceflow_cursor into @trans_no, @branch_no, @sheet_no,
						   @valid_flag, @item_no, @new_price,@old_price,@branch_flag,@price_type,
			   @new_price2,@old_price2,@new_price3,@old_price3,@new_price4,@old_price4,
				   @new_vip1,@old_vip1,@new_vip2,@old_vip2,@new_vip3,@old_vip3,@new_vip4,@old_vip4,@new_vip5,@old_vip5,@branch_list,@branch_list2
				   
  select @branch_no_px=@branch_no
  while @@fetch_status=0
  begin
   if @branch_no = rtrim(@branch_no)
   if @g_branch_no = rtrim(@g_branch_no)
   select @branch_list2 = isnull(@branch_list2,'')
   if len(rtrim(@branch_list2)) > 0  select @branch_list2 = @branch_list2 + '|'	
   if @trans_no='PX'  -- 进价\零售价\会员价\批发价\配送价 Add By Huangsc 2006.03.07
	   begin
	   	
	  	if (@branch_no = @g_branch_no or @branch_no = 'ALL') and (charindex('|'+rtrim(@g_branch_no)+'|',@branch_list2) > 0) --and len(rtrim(@branch_list2)) = 0 --(len(rtrim(@branch_list2)) = 0  or charindex('|'+rtrim(@g_branch_no)+'|',@branch_list2) > 0)
				begin
					
					update t_bd_item_info 
			   		set  price = (case substring(@branch_list,1,1) when '1' then @new_price else price end),
			   				base_price = (case substring(@branch_list,2,1) when '1' then @new_price2 else base_price end),
			   				sale_price = (case substring(@branch_list,3,1) when '1' then @new_price3 else sale_price end),
			   				scheme_price = (case substring(@branch_list,4,1) when '1' then @new_price4 else scheme_price end),
			   				update_date=@update_date ,
			   				vip_price = (case substring(@branch_list,5,1) when '1' then @new_vip1 else vip_price end),
			   				vip_price2 = (case substring(@branch_list,6,1) when '1' then @new_vip2 else vip_price2 end),
			   				vip_price3 = (case substring(@branch_list,7,1) when '1' then @new_vip3 else vip_price3 end),
			   				vip_price4 = (case substring(@branch_list,8,1) when '1' then @new_vip4 else vip_price4 end),
			   				vip_price5 = (case substring(@branch_list,9,1) when '1' then @new_vip5 else vip_price5 end)
			 		where (item_no = @item_no)
		   		end
				--取商品档案中各价格
				select @item_price = price,@item_base_price = base_price,@item_sale_price = sale_price,@item_scheme_price = scheme_price,
					@item_vip_price = vip_price,@item_vip_price2 = vip_price2,@item_vip_price3 = vip_price3,@item_vip_price4 = vip_price4,
					@item_vip_price5 = vip_price5
				from t_bd_item_info where item_no = @item_no
				if @g_branch_no = '000'
					begin
						if @branch_no = 'ALL' or len(substring(@branch_list2,2,6) ) > 0 or len(rtrim(@branch_no)) = 3
							begin
								-- 先插入不存表中的各机构的商品
								if  len(rtrim(@branch_no)) = 3 and rtrim(@branch_no) <> 'ALL'
								begin
									declare branch_cursor cursor for
									select distinct branch_no from t_bd_branch_info where trade_type in('0','2','1','4','3','7','8','5','6') --and left(branch_no,3) = rtrim(@branch_no)
								end
								else
								begin
									declare branch_cursor cursor for
									select distinct branch_no from t_bd_branch_info where trade_type in('0','1','4','3','7','8','5','6','2')
								end
								--	
							open branch_cursor
							fetch branch_cursor into @branch_no
							while @@fetch_status=0
			  					begin
			  						--IF len(substring(@branch_list2,2,6) ) > 0 
			  					--	begin
			  	
				  					--if charindex('|'+rtrim(@branch_no)+'|',@branch_list2) <= 0 and len(rtrim(@branch_list2)) > 0
									if charindex(rtrim(@branch_no)+'|',@branch_list2) <= 0 and len(rtrim(@branch_list2)) > 0
				  			  		goto no_exist
				  		
									IF exists(select * from t_pc_branch_price where(branch_no=@branch_no and item_no=@item_no )) -- 不存在
										UPDATE t_pc_branch_price 
										SET price = (case substring(@branch_list,1,1) when '1' then @new_price else price end),
										base_price = (case substring(@branch_list,2,1) when '1' then @new_price2 else base_price end),
										sale_price = (case substring(@branch_list,3,1) when '1' then @new_price3 else sale_price end),
										scheme_price = (case substring(@branch_list,4,1) when '1' then @new_price4 else scheme_price end),
										vip_price = (case substring(@branch_list,5,1) when '1' then @new_vip1 else vip_price end),
										vip_price2 = (case substring(@branch_list,6,1) when '1' then @new_vip2 else vip_price2 end),
										vip_price3 = (case substring(@branch_list,7,1) when '1' then @new_vip3 else vip_price3 end),
										vip_price4 = (case substring(@branch_list,8,1) when '1' then @new_vip4 else vip_price4 end),
										vip_price5 = (case substring(@branch_list,9,1) when '1' then @new_vip5 else vip_price5 end)
			  							WHERE branch_no=@branch_no and item_no=@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,@item_no,
			  							(case substring(@branch_list,1,1) when '1' then @new_price else @item_price end),
			  							(case substring(@branch_list,2,1) when '1' then @new_price2 else @item_base_price end),
			  							(case substring(@branch_list,3,1) when '1' then @new_price3 else @item_sale_price end),
			  							(case substring(@branch_list,5,1) when '1' then @new_vip1 else @item_vip_price end),
			  							(case substring(@branch_list,4,1) when '1' then @new_price4 else @item_scheme_price end),
			  							'0',
			  							(case substring(@branch_list,6,1) when '1' then @new_vip2 else @item_vip_price2 end),
			  							(case substring(@branch_list,7,1) when '1' then @new_vip3 else @item_vip_price3 end),
			  							(case substring(@branch_list,8,1) when '1' then @new_vip4 else @item_vip_price4 end),
			  							(case substring(@branch_list,9,1) when '1' then @new_vip5 else @item_vip_price5 end))
			  						no_exist:
									fetch branch_cursor into @branch_no
								end 
								deallocate  branch_cursor
							end
						else
							begin
								if exists(select '1' from t_pc_branch_price where (item_no = @item_no) and branch_no=@branch_no) --是否存在
									begin
										UPDATE t_pc_branch_price 
										SET price = (case substring(@branch_list,1,1) when '1' then @new_price else price end),
										base_price = (case substring(@branch_list,2,1) when '1' then @new_price2 else base_price end),
										sale_price = (case substring(@branch_list,3,1) when '1' then @new_price3 else sale_price end),
										scheme_price = (case substring(@branch_list,4,1) when '1' then @new_price4 else scheme_price end),
										vip_price = (case substring(@branch_list,5,1) when '1' then @new_vip1 else vip_price end),
										vip_price2 = (case substring(@branch_list,6,1) when '1' then @new_vip2 else vip_price2 end),
										vip_price3 = (case substring(@branch_list,7,1) when '1' then @new_vip3 else vip_price3 end),
										vip_price4 = (case substring(@branch_list,8,1) when '1' then @new_vip4 else vip_price4 end),
										vip_price5 = (case substring(@branch_list,9,1) when '1' then @new_vip5 else vip_price5 end)
			  							WHERE branch_no=@branch_no and item_no=@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,@item_no,
			  							(case substring(@branch_list,1,1) when '1' then @new_price else @item_price end),
			  							(case substring(@branch_list,2,1) when '1' then @new_price2 else @item_base_price end),
			  							(case substring(@branch_list,3,1) when '1' then @new_price3 else @item_sale_price end),
			  							(case substring(@branch_list,5,1) when '1' then @new_vip1 else @item_vip_price end),
			  							(case substring(@branch_list,4,1) when '1' then @new_price4 else @item_scheme_price end),
			  							'0',
			  							(case substring(@branch_list,6,1) when '1' then @new_vip2 else @item_vip_price2 end),
			  							(case substring(@branch_list,7,1) when '1' then @new_vip3 else @item_vip_price3 end),
			  							(case substring(@branch_list,8,1) when '1' then @new_vip4 else @item_vip_price4 end),
			  							(case substring(@branch_list,9,1) when '1' then @new_vip5 else @item_vip_price5 end))
									end
		 					end
				end
			else if (@branch_no = @g_branch_no or @branch_no = 'ALL'or len(substring(@branch_list2,2,6) ) > 0 or len(rtrim(@branch_no)) = 3) and @g_branch_no <> '000' --分部
				begin

                    			 if  len(rtrim(@branch_no)) = 3 and rtrim(@branch_no) <> 'ALL'
						begin
						declare branch_cursor cursor for
						select distinct branch_no from t_bd_branch_info where trade_type in('0','1','4','3','7','8','5','6') and left(branch_no,3) = rtrim(@branch_no)
						end	
                   			  else
						begin
						declare branch_cursor cursor for
							select distinct branch_no from t_bd_branch_info where branch_no=@g_branch_no --改为门店编号20181215 为all情况下
					end
				
		     		open branch_cursor
				fetch branch_cursor into @branch_no
				while @@fetch_status=0
			 	 begin
			  	      if charindex('|'+rtrim(@branch_no)+'|',@branch_list2) <= 0 and len(rtrim(@branch_list2)) > 0
				  	  goto no_exist1

	                 
				    	if (@branch_flag <> @g_branch_no) -- 非分部本地的调价单(即总部的调价单)
						select @ls_com_flag = '1'
			 		else
			 			begin
							select @ls_com_flag = '0'
							update t_bd_item_info 
					   			set price = (case substring(@branch_list,1,1) when '1' then @new_price else price end),
				   				base_price = (case substring(@branch_list,2,1) when '1' then @new_price2 else base_price end),
				   				sale_price = (case substring(@branch_list,3,1) when '1' then @new_price3 else sale_price end),
				   				scheme_price = (case substring(@branch_list,4,1) when '1' then @new_price4 else scheme_price end),
				   				update_date=@update_date ,
				   				vip_price = (case substring(@branch_list,5,1) when '1' then @new_vip1 else vip_price end),
				   				vip_price2 = (case substring(@branch_list,6,1) when '1' then @new_vip2 else vip_price2 end),
				   				vip_price3 = (case substring(@branch_list,7,1) when '1' then @new_vip3 else vip_price3 end),
				   				vip_price4 = (case substring(@branch_list,8,1) when '1' then @new_vip4 else vip_price4 end),
				   				vip_price5 = (case substring(@branch_list,9,1) when '1' then @new_vip5 else vip_price5 end)
				 			where (item_no = @item_no)
					end
					
					if  exists(select '1' from t_pc_branch_price where (item_no = @item_no) and branch_no=@branch_no) --是否存在
						begin
						UPDATE t_pc_branch_price 
							SET price = (case substring(@branch_list,1,1) when '1' then @new_price else price end),
							base_price = (case substring(@branch_list,2,1) when '1' then @new_price2 else base_price end),
							sale_price = (case substring(@branch_list,3,1) when '1' then @new_price3 else sale_price end),
							scheme_price = (case substring(@branch_list,4,1) when '1' then @new_price4 else scheme_price end),
							vip_price = (case substring(@branch_list,5,1) when '1' then @new_vip1 else vip_price end),
							vip_price2 = (case substring(@branch_list,6,1) when '1' then @new_vip2 else vip_price2 end),
							vip_price3 = (case substring(@branch_list,7,1) when '1' then @new_vip3 else vip_price3 end),
							vip_price4 = (case substring(@branch_list,8,1) when '1' then @new_vip4 else vip_price4 end),
							vip_price5 = (case substring(@branch_list,9,1) when '1' then @new_vip5 else vip_price5 end)
				  		WHERE branch_no=@branch_no and item_no=@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,@item_no,
			  				(case substring(@branch_list,1,1) when '1' then @new_price else @item_price end),
				  			(case substring(@branch_list,2,1) when '1' then @new_price2 else @item_base_price end),
				  			(case substring(@branch_list,3,1) when '1' then @new_price3 else @item_sale_price end),
							(case substring(@branch_list,5,1) when '1' then @new_vip1 else @item_vip_price end),
				  			(case substring(@branch_list,4,1) when '1' then @new_price4 else @item_scheme_price end),
				  			'0',
				  			(case substring(@branch_list,6,1) when '1' then @new_vip2 else @item_vip_price2 end),
				  			(case substring(@branch_list,7,1) when '1' then @new_vip3 else @item_vip_price3 end),
				  			(case substring(@branch_list,8,1) when '1' then @new_vip4 else @item_vip_price4 end),
				  			(case substring(@branch_list,9,1) when '1' then @new_vip5 else @item_vip_price5 end))
					
					end
                                   no_exist1:
					fetch branch_cursor into @branch_no
			end	
	                        deallocate  branch_cursor
                               end 
						
	 end

	-- 更新生效标志(总部:(编码为总部-->设为'1',编码为分部-->设为'2',编码为'ALL'-->设为'2')
	--             (分部:(全部设为'1'))
	if @g_branch_no = '000'
	begin
	   if @branch_no = 'ALL' 
		  update t_pc_price_flow_master
			 set valid_flag = '2' 
		   where sheet_no = @sheet_no
	   else
	   begin  
		  if @branch_no = @g_branch_no 
			 update t_pc_price_flow_master
				set valid_flag = '1' 
			  where sheet_no = @sheet_no
		  else
			 update t_pc_price_flow_master
				set valid_flag = '2' 
			  where sheet_no = @sheet_no
	   end
	 end 
	else 
	   update t_pc_price_flow_master
		  set valid_flag = '1' 
		where sheet_no = @sheet_no
	

	if @@error != 0 
	begin
	   deallocate  priceflow_cursor
	   delete t_sys_system where lower(sys_var_id)='change_price'
	   return
	end
	
    --Add By Huangsc 2006.10.19 关联调价
    exec pr_pc_price_flow_effect_rpx  @branch_no_px, @item_no, @new_price, @old_price,@branch_flag ,
         @price_type ,@new_price2 ,@old_price2 ,@new_price3 ,@old_price3 ,@new_price4 ,@old_price4 ,
         @new_vip1 ,@old_vip1 ,@new_vip2 ,@old_vip2 ,@new_vip3 ,@old_vip3 ,@new_vip4 ,@old_vip4 ,
         @new_vip5 ,@old_vip5 

	fetch priceflow_cursor into @trans_no, @branch_no, @sheet_no,
		 @valid_flag, @item_no, @new_price,@old_price,@branch_flag,@price_type,
	 @new_price2,@old_price2,@new_price3,@old_price3,@new_price4,@old_price4,
	 @new_vip1,@old_vip1,@new_vip2,@old_vip2,@new_vip3,@old_vip3,@new_vip4,@old_vip4,@new_vip5,@old_vip5,@branch_list,@branch_list2
  end 

  deallocate  priceflow_cursor 

  delete t_sys_system where lower(sys_var_id)='change_price'
   
   /***********删除失效的100+1商品***2004-07-16*********/
   delete from t_rm_spec_price 
		   where special_type='9' and (branch_no='000' Or branch_no = 'ALL') and 
				 (convert(char(10),getdate(),121) > convert(char(10),end_date,121))

end 
go
