if exists(select 1 from sysobjects where name='f_get_descry')
  drop function f_get_descry
go

--解密储值卡函数

create   function f_get_descry(@as_source varchar(50))
returns  decimal(16,4)
as
begin 
if @as_source is null or @as_source='' return 0
declare @i int,@ll_len int
declare @ls_text varchar(50),@ls_result varchar(50)
select @ls_text=ltrim(rtrim(@as_source))
select @ll_len=len(@ls_text)
select @ls_result=''
select @i=1
while @i<=@ll_len
begin
select @ls_result=@ls_result+char(ascii(substring(@ls_text,@i,1)) - (2 * (@ll_len - @i - 2)))
select @i=@i+1
end
select @ls_result = reverse(@ls_result)
return cast(@ls_result as decimal(16,4))/10000
end
go

if exists(select 1 from sysobjects where name='f_get_encrypt')
  drop function f_get_encrypt
go
--加密
create    function [dbo].[f_get_encrypt](@as_source varchar(50))
returns  varchar(50)
as
begin 
	if @as_source is null or @as_source='' return NULL

	declare @i int,@ll_len int
	declare @ls_text varchar(50),@ls_result varchar(50)

	select @ls_text=ltrim(rtrim(@as_source))	
	select @ll_len=len(@ls_text)
	select @ls_text = reverse(@ls_text)
	select @ls_result=''
	set @i=1
	while @i<=@ll_len
	begin
		select @ls_result=@ls_result+char(ascii(substring(@ls_text,@i,1)) + (2 * (@ll_len - @i - 2)))
		select @i=@i+1
	end

	return @ls_result
end
go

if exists(select 1 from sysobjects where name = 'fn_plan_include_branch_clsno')
  drop function fn_plan_include_branch_clsno
go



--返回促销单包含某区域的记录数
create function fn_plan_include_branch_clsno(@plan_no varchar(20), @branch_clsno varchar(6))
returns int
as
begin
	declare @count int
	select @count = count(a.plan_no)
	from t_rm_plan_branch a, t_bd_branch_info b 
	where a.branch_no = b.branch_no 
	and a.plan_no = @plan_no
	and b.branch_clsno = @branch_clsno

	return @count
end
GO

if exists(select 1 from sysobjects where name = 'tr_pc_price_flow_master')
  drop trigger tr_pc_price_flow_master
go

create trigger tr_pc_price_flow_master on t_pc_price_flow_master 
for update 
as
begin
  if not update(approve_flag) return

  declare 
  @sheet_no         char(18)        ,--单据编号
  @branch_no        char(6)        ,--仓库编号
  @trans_no         char(2)        ,--交易编号
  @oper_date       datetime   ,--操作日期
  @oper_id         char(4),
  @work_date       datetime,  --审核日期
  @supcust_no     char(8), --供应商客户代码
  @sale_way       char(1),
  @item_no          char(20)        ,--货品代码
  @price_type     char(1)     ,--类型
  @start_date     datetime  ,--日期1
  @end_date     datetime  ,--日期2
  @week     char(7)  ,--星期
  @begin_time     char(8)  ,--时间1
  @end_time     char(8) ,--时间2
  @old_price      numeric(16,4)     ,--进货单价
  @new_price        numeric(16,4)     ,--零售单价
  @discount         numeric(5,4)    ,--折扣
  @buy_qty      numeric(16,4)   ,--客单限量
  @limited_qty      numeric(16,4)   ,--卖场限量
  @reach_val         numeric(16,2)         ,--其它1
  @add_val         numeric(16,2)         ,--其它2
  @mother1         char(20)         ,--其它1
  @mother2         char(20)         ,--其它2
  @other3         char(20),--其它3
  @i_sort_no         int     , --序号  
  @sort_no           char(5),--序号
  @s_newsheetno	 	char(18),
  @branch_id 	char(2),
  @vip_type	char(2),
  @dec_rate	numeric(16,4),
  @branch_list 	varchar(2000),
  @reach_vip	numeric(16,2), --'PH'促销单的限定积分积分
  @group_no	char(2),
  @trade_type   char(1),
  @cls_flag 	char(1),
  @cls_list 	char(10),

  @cls_no    varchar(20),
  @pp_no     varchar(20),
  @num_flag  char(1),
  @send_val  numeric(16,2),
  @ppno      varchar(20)

  select @i_sort_no=0
  declare priceflow_cur cursor for
    select distinct m.trans_no, m.branch_no, m.sheet_no,m.supcust_no,m.oper_id,d.item_no,d.price_type, d.start_date,d.end_date,d.week,d.begin_time,d.end_time, d.old_price,
      d.new_price, discount,d.buy_qty,d.limited_qty,isnull(m.vip_type,''),isnull(m.reach_val,0),isnull(m.add_val,0),isnull(dec_rate,0),isnull(branch_list,''),isnull(m.reach_vip,0),isnull(d.group_no,''),
      m.cls_flag,m.cls_list,m.work_date,m.other1,m.cls_no,m.pp_no,m.num_flag,m.send_val,d.pp_no
      from inserted m, t_pc_price_flow_detail d   
     where ( m.sheet_no = d.sheet_no ) and  
           (m.approve_flag = '1') 

  open priceflow_cur
  fetch priceflow_cur into @trans_no, @branch_no, @sheet_no, @supcust_no,@oper_id,@item_no, @price_type, @start_date, @end_date, @week, @begin_time, @end_time, @old_price, 
    @new_price, @discount, @buy_qty,@limited_qty,@vip_type,@reach_val,@add_val,@dec_rate,@branch_list,@reach_vip,@group_no,
    @cls_flag,@cls_list,@work_date,@mother1,@cls_no,@pp_no,@num_flag,@send_val,@ppno

  while @@fetch_status=0
  begin
    select @i_sort_no=@i_sort_no+1
    select @sort_no=convert(char(5),@i_sort_no)    

    if @trans_no='PS' or @trans_no='PN' or @trans_no='PH'or @trans_no='PU' or @trans_no='PE' or @trans_no = 'PA' or @trans_no = 'PG' or @trans_no = 'PL' or @trans_no = 'PY' or 
       @trans_no='PV' or @trans_no='PM' or @trans_no='PK'or @trans_no='PP' or @trans_no = 'P1' or @trans_no = 'P2' or @trans_no = 'P3' or @trans_no = 'P4'or @trans_no = 'P5'
    begin
      if @begin_time is null  select @begin_time=''
      if @end_time is null  select @end_time=''

      if len(@branch_no) = 6 --单个机构调价
	      begin
	          if @trans_no='PN'  or @trans_no='PE' --固定时间特价 超量购买特价
			     insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
		               sale_qty, start_date, end_date, week, begin_time, end_time,sheet_no,limited_qty,sort_no,dec_rate)  
		             values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, @discount, 
		               @buy_qty, @start_date, @end_date, @week, @begin_time, @end_time,@sheet_no,@limited_qty,@sort_no,@dec_rate) 
			  else if @trans_no='PU'  --类别促销折扣
				       IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and vip_type = @vip_type and @begin_time is null))
				                  begin   --begin Exist , Update
				                       if (@discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
				                           delete from t_rm_spec_price where (item_no = @item_no and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type)
				                       else --更新特价信息（有效数据）
					                   update t_rm_spec_price set
					                          discount = @discount,
					                          start_date = @start_date,  end_date = @end_date, 
					                          begin_time = @begin_time, end_time = @end_time, 
					                          sheet_no = @sheet_no,week =@week
					                          where (item_no=@item_no and special_type = @price_type and 
				                                         branch_no = @branch_no  and vip_type = @vip_type)  
			          		   end   --end Exist , Update
		           		 else  --No Exist, Insert
				 		   begin                      
				 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
				                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate)  
				                    		values(@branch_no, @item_no, @price_type,@vip_type, 0, 0, @discount, 
				                           		0, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate)  
					           end
			 else if @trans_no='P2'  --类别+品牌折扣
				       IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no and vip_type = @vip_type and @begin_time is null))
				                  begin   --begin Exist , Update
				                       if (@discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
				                           delete from t_rm_spec_price where (item_no = @item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type)
				                       else --更新特价信息（有效数据）
					                   update t_rm_spec_price set
					                          discount = @discount,
					                          start_date = @start_date, end_date = @end_date, 
					                          begin_time = @begin_time, end_time = @end_time, 
					                          sheet_no = @sheet_no,week =@week
					                          where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and 
				                                         branch_no = @branch_no and vip_type = @vip_type)  
			          		   end   --end Exist , Update
		           		 else  --No Exist, Insert
				 		   begin                      
				 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
				                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
				                    		values(@branch_no, @item_no, @price_type,@vip_type, 0, 0, @discount, 
				                           		0, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
					           end
			  else if @trans_no='P5'  --类别+品牌折扣
				       IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no and vip_type = @vip_type and @begin_time is null))
				                  begin   --begin Exist , Update
				                       		  update t_rm_spec_price set
						                          discount = @discount,
						                          start_date = @start_date, end_date = @end_date, 
						                          begin_time = @begin_time, end_time = @end_time, 
						                          sheet_no = @sheet_no,week =@week
					                          where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and 
				                                         branch_no = @branch_no and vip_type = @vip_type)  
			          		   end   --end Exist , Update
		           		 else  --No Exist, Insert
				 		   begin                      
				 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
				                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
				                    		values(@branch_no, @item_no, @price_type,@vip_type, 0, 0, @discount, 
				                           		0, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
					           end
	
			  else if @trans_no='P3'  --类别+品牌N数N价促销
		         begin
	                   	delete from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and sheet_no<>@sheet_no and special_type=@price_type and branch_no=@branch_no and vip_type=@vip_type)
	 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
	                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
	                    		values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, 0, 
	                           		@buy_qty, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
		           end
	
			 else if @trans_no='P4'  --类别+品牌N数N折促销
		         begin
	                   	delete from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and sheet_no<>@sheet_no and special_type=@price_type and branch_no=@branch_no and vip_type=@vip_type)
	 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
	                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
	                    		values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, 0, 
	                           		@buy_qty, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
		           end	
		    
		      else if @trans_no='PS' or @trans_no='PH' or @trans_no = 'PA' or @trans_no = 'PG' or @trans_no = 'PL' or @trans_no = 'PY' or @trans_no='PV' or @trans_no='PM' or @trans_no='PK'or @trans_no='PP' or @trans_no = 'P1'
				         begin
			           	if @trans_no='PH' select @price_type='9'
						IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type and @begin_time is null) )
				            begin   --begin Exist , Update
				              if (@new_price < 0 and @discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
				                delete from t_rm_spec_price where (item_no = @item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type)
				              else -- 更新特价信息（有效数据）
				                update t_rm_spec_price set
				                    special_type = @price_type, old_price = @old_price,
				                    spe_price = @new_price, discount = @discount,
				                    sale_qty = @buy_qty, start_date = @start_date,  end_date = @end_date, 
				                    week = @week, 
				                    begin_time = @begin_time, end_time = @end_time,
				                    sheet_no = @sheet_no,limited_qty=@limited_qty,sort_no=@sort_no,cls_flag = @cls_flag,cls_list = @cls_list,
									cls_no=@cls_no,pp_no=@pp_no,num_flag=@num_flag,send_val=@send_val
				                  where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type)  
				            end ---- end Exist , Update
	            		   	else  -- No Exist, Insert
           				 	begin
							If @trans_no='PH' and @add_val = 0 and @new_price <> 0 
							   begin
								select @add_val = @new_price
							   end
							if @trans_no = 'PG' 
							   begin
								set @sort_no = @group_no
							   end
					
					                insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
					                  sale_qty, start_date, end_date, week, begin_time, end_time, sheet_no,limited_qty,sort_no,reach_val,add_val,dec_rate,reach_vip,cls_flag,cls_list,other1,cls_no,pp_no,num_flag,send_val)   
					                values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, @discount, 
					                  @buy_qty, @start_date, @end_date, @week, @begin_time, @end_time, @sheet_no,@limited_qty,@sort_no,@reach_val,@add_val,@dec_rate,@reach_vip,@cls_flag,@cls_list,@mother1,@cls_no,@pp_no,@num_flag,@send_val)   
					  	end 
	     	 	end 
		end

      if @branch_list='' and len(@branch_no) = 3 --总部/分公司/办事处/配送中心调价
	      begin
	        declare branch_cur cursor for
	        --select branch_no from t_bd_branch_info where property='2' and ( trade_type = '3' or  trade_type = '4'or  trade_type = '5' or  trade_type = '7' or  trade_type = '8') 
	      	select branch_no from t_bd_branch_info where  (trade_type = '3' or  trade_type = '4'or  trade_type = '5' or  trade_type = '7' or  trade_type = '8') and left(branch_no,3) = @branch_no --modified by zhangmz 20090205
	        open branch_cur
	        fetch branch_cur into @branch_no
	        while @@fetch_status=0
	        	begin
	            if @trans_no='PN' or @trans_no='PE' --特价
	               insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type, old_price, spe_price, discount,
	                  sale_qty, start_date, end_date, week, begin_time, end_time,  sheet_no,limited_qty,sort_no,dec_rate)  
	                values(@branch_no, @item_no, @price_type, @vip_type, @old_price, @new_price, @discount, 
	                  @buy_qty, @start_date, @end_date, @week, @begin_time, @end_time, @sheet_no,@limited_qty,@sort_no,@dec_rate)   
	
    		    else if @trans_no='PU'  --类别促销折扣
		       		IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
	                  		begin   -- begin Exist , Update
			                       	if (@discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
			                           delete from t_rm_spec_price where (item_no = @item_no and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type)
			                       	else -- 更新特价信息（有效数据）
				                   update t_rm_spec_price set
				                          discount = @discount,
				                          start_date = @start_date,  end_date = @end_date, 
				                          begin_time = @begin_time, end_time = @end_time, 
							  									sheet_no = @sheet_no,week=@week
				                          where (item_no=@item_no and special_type = @price_type and 
			                                         branch_no = @branch_no  and vip_type = @vip_type)  
	          				end   -- end Exist , Update
		          	else  -- No Exist, Insert
			 		  begin                      
			 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
			                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate)  
			                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
			                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate)  
				          end
				 else if @trans_no='P2'  --类别+品牌折扣
		       		IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
	                  		begin   -- begin Exist , Update
			                       	if (@discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
			                           delete from t_rm_spec_price where (item_no = @item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type)
			                       	else -- 更新特价信息（有效数据）
				                   update t_rm_spec_price set
				                          discount = @discount,
				                          start_date = @start_date, end_date = @end_date, 
				                          begin_time = @begin_time, end_time = @end_time, 
							  sheet_no = @sheet_no,week=@week
				                          where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and 
			                                         branch_no = @branch_no and vip_type = @vip_type)  
	          				end   -- end Exist , Update
		          	else  -- No Exist, Insert
			 		  begin
			 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
			                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
			                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
			                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate,@ppno)  
				          end
				else if @trans_no='P5'  --类别+品牌折扣
		       		IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
	                  		begin   -- begin Exist , Update
			                       	
				                   update t_rm_spec_price set
				                          discount = @discount,
				                          start_date = @start_date, end_date = @end_date, 
				                          begin_time = @begin_time, end_time = @end_time, 
							              sheet_no = @sheet_no,week=@week
				                    where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and 
			                                         branch_no = @branch_no and vip_type = @vip_type)  
	          				end   -- end Exist , Update
		          	else  -- No Exist, Insert
			 		  begin
			 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
			                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
			                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
			                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate,@ppno)  
				          end

    		    else if @trans_no='P3'  --类别+品牌N数N价促销
		            begin
	                   	delete from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and sheet_no<>@sheet_no and special_type=@price_type and branch_no=@branch_no and vip_type=@vip_type)
	 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
	                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
	                    		values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, 0, 
	                           		@buy_qty, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
		            end

				else if @trans_no='P4'  --类别+品牌N数N价促销
			            begin
		                   	delete from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and sheet_no<>@sheet_no and special_type=@price_type and branch_no=@branch_no and vip_type=@vip_type)
		 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
		                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
		                    		values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, 0, 
		                           		@buy_qty, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
			            end           	     

           	     else if @trans_no='PS' or @trans_no='PH' or @trans_no = 'PA' or @trans_no = 'PG' or @trans_no = 'PL' or @trans_no = 'PY' or  @trans_no='PV' or @trans_no='PM' or @trans_no='PK'or @trans_no='PP' or @trans_no = 'P1'
			          begin
			
			               if @trans_no='PH' select @price_type='9'
			
			               IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type and @begin_time is null))
			               begin 
			                    if (@new_price <= 0 and @discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
			                        delete from t_rm_spec_price where (item_no = @item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type)
			                    else --更新特价信息（有效数据）
			                         update t_rm_spec_price set
			                             special_type = @price_type, old_price = @old_price,
			                             spe_price = @new_price, discount = @discount,
			                             sale_qty = @buy_qty, start_date = @start_date, end_date = @end_date,
			                             week = @week, 
			                             begin_time = @begin_time, end_time = @end_time, 
			                             sheet_no = @sheet_no,limited_qty=@limited_qty,sort_no=@sort_no,cls_flag = @cls_flag,cls_list = @cls_list,
									     cls_no=@cls_no,pp_no=@pp_no,num_flag=@num_flag,send_val=@send_val
			                           where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and special_type <>'4' and vip_type = @vip_type)  
			            end 
               	     else
			            begin
					    If @trans_no='PH' and @add_val = 0 and @new_price <> 0 
						begin
							select @add_val = @new_price
						end
					    if @trans_no = 'PG' 
						begin
							set @sort_no = @group_no
						end
			
			                    insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
			                        sale_qty, start_date, end_date, week, begin_time, end_time, sheet_no,limited_qty,sort_no,reach_val,add_val,dec_rate,reach_vip,cls_flag,cls_list,other1,cls_no,pp_no,num_flag,send_val)   
			                      values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, @discount, 
			                        @buy_qty, @start_date, @end_date, @week, @begin_time, @end_time,@sheet_no,@limited_qty,@sort_no,@reach_val,@add_val,@dec_rate,@reach_vip,@cls_flag,@cls_list,@mother1,@cls_no,@pp_no,@num_flag,@send_val)   
			             end
            end
            fetch branch_cur into @branch_no
         end 
        deallocate  branch_cur 
      end 
     
      if @branch_list='' and len(@branch_no) = 3 and @branch_no = 'ALL'--所有门店
	      begin
		select @trade_type = trade_type from t_bd_branch_info where branch_no = (select sys_var_value from t_sys_system where sys_var_id = 'g_branch_no')
		if @trade_type = '1'--办事处所有门店不包含总部的
			begin
				declare branch_cur_all_bsc cursor for
				select branch_no from t_bd_branch_info where branch_no like (select sys_var_value from t_sys_system where sys_var_id = 'g_branch_no') + '%'
			        open branch_cur_all_bsc
			        fetch branch_cur_all_bsc into @branch_no
			        while @@fetch_status=0
			        begin
			            if @trans_no='PN' or @trans_no='PE' --特价
			               insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type, old_price, spe_price, discount,
			                  sale_qty, start_date, end_date, week, begin_time, end_time,  sheet_no,limited_qty,sort_no,dec_rate)  
			                values(@branch_no, @item_no, @price_type, @vip_type, @old_price, @new_price, @discount, 
			                  @buy_qty, @start_date, @end_date, @week, @begin_time, @end_time, @sheet_no,@limited_qty,@sort_no,@dec_rate)   
			
		    		    else if @trans_no='PU'  --类别促销折扣
				       		IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
			                  		begin   -- begin Exist , Update
					                       	if (@discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
					                           delete from t_rm_spec_price where (item_no = @item_no and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type)
					                       	else -- 更新特价信息（有效数据）
						                   update t_rm_spec_price set
						                          discount = @discount,
						                          start_date = @start_date,  end_date = @end_date, 
						                          begin_time = @begin_time, end_time = @end_time, 
									  									sheet_no = @sheet_no,week=@week
						                          where (item_no=@item_no and special_type = @price_type and 
					                                         branch_no = @branch_no  and vip_type = @vip_type)  
			          			end   -- end Exist , Update
				          	else  -- No Exist, Insert
					 		  begin                      
					 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
					                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate)  
					                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
					                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate)  
			    			 end
						 else if @trans_no='P2'  --类别+品牌折扣
				       		IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
			                  		begin   -- begin Exist , Update
					                       	if (@discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
					                           delete from t_rm_spec_price where (item_no = @item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type)
					                       	else -- 更新特价信息（有效数据）
						                   update t_rm_spec_price set
						                          discount = @discount,
						                          start_date = @start_date, end_date = @end_date, 					         
	                							  begin_time = @begin_time, end_time = @end_time, 
									  			  sheet_no = @sheet_no,week=@week
						                          where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and 
					                                         branch_no = @branch_no and vip_type = @vip_type)  
			          			end   -- end Exist , Update
				          	else  -- No Exist, Insert
					 		  begin                      
					 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
					                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
					                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
					                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate,@ppno)  
							    end
						else if @trans_no='P5'  --类别+品牌折扣
				       		IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
			                  		begin   -- begin Exist , Update
					                    
						                   update t_rm_spec_price set
						                          discount = @discount,
						                          start_date = @start_date, end_date = @end_date, 					         
	                							  begin_time = @begin_time, end_time = @end_time, 
									  			  sheet_no = @sheet_no,week=@week
						                          where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and 
					                                         branch_no = @branch_no and vip_type = @vip_type)  
			          			end   -- end Exist , Update
				          	else  -- No Exist, Insert
					 		  begin                      
					 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
					                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
					                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
					                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate,@ppno)  
							    end


		    		    else if @trans_no='P3'  --类别+品牌N数N价促销
					         begin
				                   	delete from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and sheet_no<>@sheet_no and special_type=@price_type and branch_no=@branch_no and vip_type=@vip_type)
				 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
				                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
				                    		values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, 0, 
				                           		@buy_qty, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
					           end
					
						else if @trans_no='P4'  --类别+品牌N数N价促销
					         begin
				                   	delete from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and sheet_no<>@sheet_no and special_type=@price_type and branch_no=@branch_no and vip_type=@vip_type)
				 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
				                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
				                    		values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, 0, 
				                           		@buy_qty, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
					           end		           	     

		           	     else if @trans_no='PS' or @trans_no='PH' or @trans_no = 'PA' or @trans_no = 'PG' or @trans_no = 'PL' or @trans_no='PY' or  @trans_no='PV' or @trans_no='PM' or @trans_no='PK'or @trans_no='PP' or @trans_no = 'P1'
					          begin
					
					               if @trans_no='PH' select @price_type='9'
					
						               IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type and @begin_time is null))
						               begin 
						                    if (@new_price <= 0 and @discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
						                        delete from t_rm_spec_price where (item_no = @item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type)
						                    else --更新特价信息（有效数据）
						                         update t_rm_spec_price set
						                             special_type = @price_type, old_price = @old_price,
						                             spe_price = @new_price, discount = @discount,
						                             sale_qty = @buy_qty, start_date = @start_date, end_date = @end_date,
						                             week = @week, 
						                             begin_time = @begin_time, end_time = @end_time, 
						                             sheet_no = @sheet_no,limited_qty=@limited_qty,sort_no=@sort_no,cls_flag = @cls_flag,cls_list = @cls_list,
													 cls_no=@cls_no,pp_no=@pp_no,num_flag=@num_flag,send_val=@send_val
						                           where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type)  
						            	end 
		               	    			else
							            begin
									    If @trans_no='PH' and @add_val = 0 and @new_price <> 0 
										begin
											select @add_val = @new_price
										end
									    if @trans_no = 'PG' 
										begin
											set @sort_no = @group_no
										end
							
							                    insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
							                        sale_qty, start_date, end_date, week, begin_time, end_time, sheet_no,limited_qty,sort_no,reach_val,add_val,dec_rate,reach_vip,cls_flag,cls_list,other1,cls_no,pp_no,num_flag,send_val)  
							                      values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, @discount, 
							                        @buy_qty, @start_date, @end_date, @week, @begin_time, @end_time,@sheet_no,@limited_qty,@sort_no,@reach_val,@add_val,@dec_rate,@reach_vip,@cls_flag,@cls_list,@mother1,@cls_no,@pp_no,@num_flag,@send_val)   
							             end
		            			  end
		            			  fetch branch_cur_all_bsc into @branch_no
		         	end 
		        	deallocate  branch_cur_all_bsc 
			end
		else
			begin
				declare branch_cur_all cursor for
				select branch_no from t_bd_branch_info where trade_type = '3' or  trade_type = '4'or  trade_type = '5' or  trade_type = '7' or  trade_type = '8'
			        open branch_cur_all
			        fetch branch_cur_all into @branch_no
			        while @@fetch_status=0
			        begin
			            if @trans_no='PN' or @trans_no='PE' --特价
			               insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type, old_price, spe_price, discount,
			                  sale_qty, start_date, end_date, week, begin_time, end_time,  sheet_no,limited_qty,sort_no,dec_rate)  
			                values(@branch_no, @item_no, @price_type, @vip_type, @old_price, @new_price, @discount, 
			                  @buy_qty, @start_date, @end_date, @week, @begin_time, @end_time, @sheet_no,@limited_qty,@sort_no,@dec_rate)   
			
		    		    else if @trans_no='PU'  --类别促销折扣
				       		IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
			                  		begin   -- begin Exist , Update
					                       	if (@discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
					                           delete from t_rm_spec_price where (item_no = @item_no and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type)
					                       	else -- 更新特价信息（有效数据）
						                   update t_rm_spec_price set
						                          discount = @discount,
						                          start_date = @start_date,  end_date = @end_date, 
						                          begin_time = @begin_time, end_time = @end_time, 
									  									sheet_no = @sheet_no,week=@week
						                          where (item_no=@item_no and special_type = @price_type and 
					                                         branch_no = @branch_no  and vip_type = @vip_type)  
			          			end   -- end Exist , Update
				          	else  -- No Exist, Insert
					 		  begin                      
					 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
					                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate)  
					                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
					                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate)  
						          end
						   else if @trans_no='P2'  --类别+品牌折扣
				       			IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
			                  		begin   -- begin Exist , Update
					                       	if (@discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
					                           delete from t_rm_spec_price where (item_no = @item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type)
					                       	else -- 更新特价信息（有效数据）
						                   update t_rm_spec_price set
						                          discount = @discount,
						                          start_date = @start_date, end_date = @end_date, 
						                          begin_time = @begin_time, end_time = @end_time, 
									  sheet_no = @sheet_no,week=@week
						                          where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and 
					                                         branch_no = @branch_no and vip_type = @vip_type)  
			          			end   -- end Exist , Update
				          	else  -- No Exist, Insert
					 		  begin                      
					 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
					                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
					                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
					                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate,@ppno)  
						          end
						else if @trans_no='P5'  --类别+品牌折扣
				       			IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
			                  		begin   -- begin Exist , Update
					                      
						                   update t_rm_spec_price set
						                          discount = @discount,
						                          start_date = @start_date, end_date = @end_date, 
						                          begin_time = @begin_time, end_time = @end_time, 
									  			sheet_no = @sheet_no,week=@week
						                          where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and 
					                                         branch_no = @branch_no and vip_type = @vip_type)  
			          			end   -- end Exist , Update
				          	else  -- No Exist, Insert
					 		  begin                      
					 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
					                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
					                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
					                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate,@ppno)  
						          end

		    		    else if @trans_no='P3'  --类别+品牌N数N价促销
					         begin
				                   	delete from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and sheet_no<>@sheet_no and special_type=@price_type and branch_no=@branch_no and vip_type=@vip_type)
				 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
				                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
				                    		values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, 0, 
				                           		@buy_qty, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
					           end

						 else if @trans_no='P4'  --类别+品牌N数N价促销
					         begin
				                   	delete from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and sheet_no<>@sheet_no and special_type=@price_type and branch_no=@branch_no and vip_type=@vip_type)
				 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
				                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
				                    		values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, 0, 
				                           		@buy_qty, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
					           end
		           	     
		           	     else if @trans_no='PS' or @trans_no='PH' or @trans_no = 'PA' or @trans_no = 'PG' or @trans_no = 'PL' or @trans_no='PY' or @trans_no='PV' or @trans_no='PM' or @trans_no='PK'or @trans_no='PP' or @trans_no='P1'
					          begin
					
					               if @trans_no='PH' select @price_type='9'
					
						               IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type and @begin_time is null))
						               begin 
						                    if (@new_price <= 0 and @discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
						                        delete from t_rm_spec_price where (item_no = @item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type)
						                    else --更新特价信息（有效数据）
						                         update t_rm_spec_price set
						                             special_type = @price_type, old_price = @old_price,
						                             spe_price = @new_price, discount = @discount,
						                             sale_qty = @buy_qty, start_date = @start_date, end_date = @end_date,
						                             week = @week, 
						                             begin_time = @begin_time, end_time = @end_time, 
						                             sheet_no = @sheet_no,limited_qty=@limited_qty,sort_no=@sort_no,
									     			cls_flag = @cls_flag,cls_list = @cls_list,cls_no=@cls_no,pp_no=@pp_no,num_flag=@num_flag,send_val=@send_val
						                           where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type)  
						            	end 
		               	    			else
							            begin
									    If @trans_no='PH' and @add_val = 0 and @new_price <> 0 
										begin
											select @add_val = @new_price
										end
									    if @trans_no = 'PG' 
										begin
											set @sort_no = @group_no
										end
							
							                    insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
							                        sale_qty, start_date, end_date, week, begin_time, end_time, sheet_no,limited_qty,sort_no,reach_val,add_val,dec_rate,reach_vip,cls_flag,cls_list,other1,cls_no,pp_no,num_flag,send_val)    
							                      values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, @discount, 
							                        @buy_qty, @start_date, @end_date, @week, @begin_time, @end_time,@sheet_no,@limited_qty,@sort_no,@reach_val,@add_val,@dec_rate,@reach_vip,@cls_flag,@cls_list,@mother1,@cls_no,@pp_no,@num_flag,@send_val)  
							             end
		            			  end
		            			  fetch branch_cur_all into @branch_no
		         	end 
		        	deallocate  branch_cur_all 
			end
	        
      	end 
	---------

      if len(@branch_list) > 0 and len(@branch_no) = 3 --门店列表调价
		begin		
	        while len(substring(@branch_list,2,6) ) > 0
	
      		begin

		    select @branch_no = substring(@branch_list,2,6)
		    select @branch_list = right(@branch_list, len(@branch_list) - 7 )

	            if @trans_no='PN' or @trans_no='PE' --特价
	               insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type, old_price, spe_price, discount,
	                  sale_qty, start_date, end_date, week, begin_time, end_time,  sheet_no,limited_qty,sort_no,dec_rate)  
	                values(@branch_no, @item_no, @price_type, @vip_type, @old_price, @new_price, @discount, 
	                  @buy_qty, @start_date, @end_date, @week, @begin_time, @end_time, @sheet_no,@limited_qty,@sort_no,@dec_rate)   
			    else if @trans_no='PU'  --类别促销折扣
			       IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
		                  begin   -- begin Exist , Update
		                       if (@discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
		                           delete from t_rm_spec_price where (item_no = @item_no and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type)
		                       else -- 更新特价信息（有效数据）
			                   update t_rm_spec_price set
			                          discount = @discount,
			                          start_date = @start_date,  end_date = @end_date, 
			                          begin_time = @begin_time, end_time = @end_time, 
						  			  sheet_no = @sheet_no,week=@week
			                          where (item_no=@item_no and special_type = @price_type and 
		                                         branch_no = @branch_no  and vip_type = @vip_type)  
					          end   -- end Exist , Update
					          else  -- No Exist, Insert
				 		  begin                      
				 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
				                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate)  
				                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
				                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate)  
					          end
				 else if @trans_no='P2'  --类别+品牌折扣
				       IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
			                  begin   -- begin Exist , Update
			                       if (@discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
			                           delete from t_rm_spec_price where (item_no = @item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type)
			                       else -- 更新特价信息（有效数据）
				                   update t_rm_spec_price set
				                          discount = @discount,
				                          start_date = @start_date, end_date = @end_date, 
				                          begin_time = @begin_time, end_time = @end_time, 
							  sheet_no = @sheet_no,week=@week
				                          where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and 
			                                         branch_no = @branch_no and vip_type = @vip_type)  
				          end   -- end Exist , Update
				          else  -- No Exist, Insert
			 		  begin                      
			 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
			                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
			                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
			                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate,@ppno)  
				          end
				  else if @trans_no='P5'  --类别+品牌折扣
				       IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
			                  begin   -- begin Exist , Update
			                       
				                   update t_rm_spec_price set
				                          discount = @discount,
				                          start_date = @start_date, end_date = @end_date, 
				                          begin_time = @begin_time, end_time = @end_time, 
							  			  sheet_no = @sheet_no,week=@week
				                          where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and 
			                                         branch_no = @branch_no and vip_type = @vip_type)  
				          end   -- end Exist , Update
				          else  -- No Exist, Insert
			 		  begin                      
			 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
			                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
			                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
			                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate,@ppno)  
				          end
		
		
				 else if @trans_no='P3'  --类别+品牌N数N价促销
				         begin
			                   	delete from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and sheet_no<>@sheet_no and special_type=@price_type and branch_no=@branch_no and vip_type=@vip_type)
			 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
			                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
			                    		values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, 0, 
			                           		@buy_qty, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
				           end
		
				else if @trans_no='P4'  --类别+品牌N数N价促销
				         begin
			                   	delete from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and sheet_no<>@sheet_no and special_type=@price_type and branch_no=@branch_no and vip_type=@vip_type)
			 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
			                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
			                    		values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, 0, 
			                           		@buy_qty, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
				           end
	            else if @trans_no='PS' or @trans_no='PH' or @trans_no = 'PA' or @trans_no = 'PG' or @trans_no = 'PL' or @trans_no = 'PY' or @trans_no='PV' or @trans_no='PM' or @trans_no='PK'or @trans_no='PP' or @trans_no='P1'
	             begin
		
	               if @trans_no='PH' select @price_type='9'
	
	               IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type and @begin_time is null))
	               begin 
	                    if (@new_price <= 0 and @discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
	                        delete from t_rm_spec_price where (item_no = @item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type)
	                    else --更新特价信息（有效数据）
	                         update t_rm_spec_price set
	                             special_type = @price_type, old_price = @old_price,
	                             spe_price = @new_price, discount = @discount,
	                             sale_qty = @buy_qty, start_date = @start_date, end_date = @end_date,
	                             week = @week, 
	                             begin_time = @begin_time, end_time = @end_time, 
	                             sheet_no = @sheet_no,limited_qty=@limited_qty,sort_no=@sort_no,
				     			cls_flag = @cls_flag,cls_list = @cls_list,cls_no=@cls_no,pp_no=@pp_no,num_flag=@num_flag,send_val=@send_val
	                           where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type)  
	                end 
	                else
	                begin

			    If @trans_no='PH' and @add_val = 0 and @new_price <> 0 
				begin
		   			select @add_val = @new_price
				end
			    if @trans_no = 'PG' 
				begin
					set @sort_no = @group_no
				end

	                   insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
	                   sale_qty, start_date, end_date, week, begin_time, end_time, sheet_no,limited_qty,sort_no,reach_val,add_val,dec_rate,reach_vip,cls_flag,cls_list,other1,cls_no,pp_no,num_flag,send_val)    
	                    values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, @discount, 
	                        @buy_qty, @start_date, @end_date, @week, @begin_time, @end_time,@sheet_no,@limited_qty,@sort_no,@reach_val,@add_val,@dec_rate,@reach_vip,@cls_flag,@cls_list,@mother1,@cls_no,@pp_no,@num_flag,@send_val)   
	               	end
	            end
          
	       end 
	end

    end 
	
    if @branch_list<>'' and len(@branch_no) = 2 --按分店区域调价. add by zhangmz 2009-10-13
	      begin
	        declare branch_cur_area cursor for
	      	select branch_no from t_bd_branch_info where branch_clsno = @branch_no and (trade_type = '3' or  trade_type = '4'or  trade_type = '5' or  trade_type = '7' or  trade_type = '8')
	        open branch_cur_area
	        fetch branch_cur_area into @branch_no
	        while @@fetch_status=0
	        begin
	            if @trans_no='PN' or @trans_no='PE' --特价
	               insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type, old_price, spe_price, discount,
	                  sale_qty, start_date, end_date, week, begin_time, end_time,  sheet_no,limited_qty,sort_no,dec_rate)  
	                values(@branch_no, @item_no, @price_type, @vip_type, @old_price, @new_price, @discount, 
	                  @buy_qty, @start_date, @end_date, @week, @begin_time, @end_time, @sheet_no,@limited_qty,@sort_no,@dec_rate)   
	
    		    else if @trans_no='PU'  --类别促销折扣
		       		IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
	                  		begin   -- begin Exist , Update
			                       	if (@discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
			                           delete from t_rm_spec_price where (item_no = @item_no and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type)
			                       	else -- 更新特价信息（有效数据）
				                   update t_rm_spec_price set
				                          discount = @discount,
				                          start_date = @start_date,  end_date = @end_date, 
				                          begin_time = @begin_time, end_time = @end_time, 
							  									sheet_no = @sheet_no,week=@week
				                          where (item_no=@item_no and special_type = @price_type and 
			                                         branch_no = @branch_no  and vip_type = @vip_type)  
	          			end   -- end Exist , Update
		          	else  -- No Exist, Insert
			 		  begin                      
			 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
			                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate)  
			                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
			                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate)  
				          end
				 else if @trans_no='P2'  --类别+品牌折扣
		       		IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
	                  		begin   -- begin Exist , Update
			                       	if (@discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
			                           delete from t_rm_spec_price where (item_no = @item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type)
			                       	else -- 更新特价信息（有效数据）
				                   update t_rm_spec_price set
				                          discount = @discount,
				                          start_date = @start_date, end_date = @end_date, 
				                          begin_time = @begin_time, end_time = @end_time, 
							  sheet_no = @sheet_no,week=@week
				                          where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and 
			                                         branch_no = @branch_no and vip_type = @vip_type)  
	          			end   -- end Exist , Update
		          	else  -- No Exist, Insert
			 		  begin                      
			 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
			                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
			                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
			                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate,@ppno)  
				          end
				else if @trans_no='P5'  --类别+品牌折扣
		       		IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and branch_no = @branch_no  and vip_type = @vip_type and @begin_time is null))
	                  		begin   -- begin Exist , Update
			                       	
				                   update t_rm_spec_price set
				                          discount = @discount,
				                          start_date = @start_date, end_date = @end_date, 
				                          begin_time = @begin_time, end_time = @end_time, 
							  			  sheet_no = @sheet_no,week=@week
				                   where (item_no=@item_no and pp_no=@ppno and special_type = @price_type and 
			                                         branch_no = @branch_no and vip_type = @vip_type)  
	          			end   -- end Exist , Update
		          	else  -- No Exist, Insert
			 		  begin                      
			 		       insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
			                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
			                    		values(@branch_no, @item_no, @price_type, @vip_type, 0, 0, @discount, 
			                           		0, @start_date, @end_date, @begin_time, @end_time, @week, @sheet_no,@sort_no,@dec_rate,@ppno)  
				          end
	
	    		else if @trans_no='P3'  --类别+品牌N数N价促销
			         begin
		                   	delete from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and sheet_no<>@sheet_no and special_type=@price_type and branch_no=@branch_no and vip_type=@vip_type)
		 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
		                         		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
		                    		values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, 0, 
		                           		@buy_qty, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
			           end
	
			   else if @trans_no='P4'  --类别+品牌N数N价促销
			         begin
		                   	delete from t_rm_spec_price where (item_no=@item_no and pp_no=@ppno and sheet_no<>@sheet_no and special_type=@price_type and branch_no=@branch_no and vip_type=@vip_type)
		 		       insert into  t_rm_spec_price (branch_no, item_no, special_type, vip_type,old_price, spe_price, discount,
		    
	                     		sale_qty, start_date, end_date, begin_time, end_time, week, sheet_no,sort_no,dec_rate,pp_no)  
		                    		values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, 0, 
		                           		@buy_qty, @start_date, @end_date, @begin_time, @end_time, @week,@sheet_no,@sort_no,@dec_rate,@ppno)  
			           end

           	    
           	    else if @trans_no='PS' or @trans_no='PH' or @trans_no = 'PA' or @trans_no = 'PG' or @trans_no = 'PL' or @trans_no = 'PY' or @trans_no='PV' or @trans_no='PM' or @trans_no='PK'or @trans_no='PP'or @trans_no='P1'
			          begin
			
			               if @trans_no='PH' select @price_type='9'
			
			               IF exists(select item_no from t_rm_spec_price where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type and @begin_time is null))
			              	    begin 
				                    if (@new_price <= 0 and @discount <= 0)   --不作特价商品,将其价格和折扣都设置为零即可
				                        delete from t_rm_spec_price where (item_no = @item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type)
				                    else --更新特价信息（有效数据）
				                         update t_rm_spec_price set
				                             special_type = @price_type, old_price = @old_price,
				                             spe_price = @new_price, discount = @discount,
				                             sale_qty = @buy_qty, start_date = @start_date, end_date = @end_date,
				                             week = @week, 
				                             begin_time = @begin_time, end_time = @end_time, 
				                             sheet_no = @sheet_no,limited_qty=@limited_qty,sort_no=@sort_no,cls_flag = @cls_flag,cls_list = @cls_list,
											 cls_no=@cls_no,pp_no=@pp_no,num_flag=@num_flag,send_val=@send_val
				                           where (item_no=@item_no and special_type = @price_type and branch_no = @branch_no and special_type<>'4' and vip_type = @vip_type)  
				             end 
               	    			else
				            begin
						    If @trans_no='PH' and @add_val = 0 and @new_price <> 0 
							begin
								select @add_val = @new_price
							end
						    if @trans_no = 'PG' 
							begin
								set @sort_no = @group_no
							end
				
				                    insert into  t_rm_spec_price (branch_no, item_no, special_type,vip_type, old_price, spe_price, discount,
				                        sale_qty, start_date, end_date, week, begin_time, end_time, sheet_no,limited_qty,sort_no,reach_val,add_val,dec_rate,reach_vip,cls_flag,cls_list,other1,cls_no,pp_no,num_flag,send_val)  
				                      values(@branch_no, @item_no, @price_type,@vip_type, @old_price, @new_price, @discount, 
				                        @buy_qty, @start_date, @end_date, @week, @begin_time, @end_time,@sheet_no,@limited_qty,@sort_no,@reach_val,@add_val,@dec_rate,@reach_vip,@cls_flag,@cls_list,@mother1,@cls_no,@pp_no,@num_flag,@send_val) 
				             end
            			end
            		fetch branch_cur_area into @branch_no
         	end 
        	deallocate  branch_cur_area 
      	end 

    if @trans_no='PC' -- 成本调价单
    begin
       --modify by dailj因启用数据传输服务,成本调价改为在t_im_flow表执行pr_pc_change_costprice更新价格
       --exec pr_pc_change_costprice @item_no, @branch_no, @new_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)
	     values(@sheet_no,@work_date,@item_no,0,@new_price,@new_price,
	       0,@branch_no,'','PC','+','',@oper_id,0,@work_date)
	     if @@error != 0 
                begin
                deallocate  priceflow_cur
                return
             end
    end
    
    select @branch_no=ltrim(rtrim(sys_var_value)) from t_sys_system where lower(sys_var_id)='g_branch_no'
    update t_pc_price_flow_master set branch_flag=@branch_no where sheet_no=@sheet_no
    
    if @trans_no='PX' -- or @trans_no='PA' or @trans_no='PB' or @trans_no='PR' or @trans_no='PJ'
         exec pr_pc_price_flow_effect
    
    if @@error != 0 
    begin
       deallocate  priceflow_cur
       return
    end
    fetch priceflow_cur into @trans_no, @branch_no, @sheet_no, @supcust_no,@oper_id,
       @item_no, @price_type, @start_date, @end_date, @week, @begin_time, @end_time, @old_price, 
       @new_price,  @discount, @buy_qty, @limited_qty, @vip_type,@reach_val,@add_val,@dec_rate,@branch_list,@reach_vip,@group_no,
       @cls_flag,@cls_list,@work_date,@mother1,@cls_no,@pp_no,@num_flag,@send_val,@ppno
      
  end

  deallocate  priceflow_cur 
end 
go



if exists(select 1 from sysobjects where name='tr_im_flow')
  drop trigger tr_im_flow
go

Create Trigger tr_im_flow on t_im_flow
for Insert
as
begin
  declare
    @flow_id        char(18) ,--流水号
    @voucher_no      char(18) ,--原始凭证号
    @oper_date       datetime   ,--操作日期
    @item_no         char(20)        ,--货品代码
    @real_qty        numeric(16,4)   ,--数量
    @price      numeric(16,4)   ,
    @cost_price      numeric(16,4)   ,--成本单价
    @sheet_amt       numeric(16,4)   ,--金额
    @branch_no       char(6)        ,--本店代码
    @d_branch_no     char(6)        ,--目的地代码
    @trans_no        char(2)         ,--库存操作方式
    @db_no           char(1)      ,--出入库方式
    @supcust_no      char(8)        ,--供应商客户代码
    @sale_way        char(1)      ,--销售方式
    @t_in_price       numeric(16,4) ,  --基准进价
    @branch_type  char(1), --机构类型
    @stock_money  numeric(16,4),--库存金额
    @ls_operdate  char(10),
    @stock_qty    numeric(16,4),--库存数量
    @stock_price  numeric(16,4),--成本价
    @sale_price      numeric(16,4),--售价
    @sup_no       char(8),        --供应商客户代码
    @so_price     numeric(16,4),--批发价
    @lb_change_price char(1),
    @stock_type char(1) ,  --门店类型
    @local_branch char(6),
    @adjust_num1  integer,
    @adjust_num2  integer,
    @item_stock  char(1), ---商品是否维护库存
    @item_sup_flag char(1), ---经营方式 A 购销，C 联营
    @item_sup_rate decimal(16,4), ----商品档案联营扣率
    @spec_sup_rate decimal(16,4), ----特价联营扣率
    @cost_qty     numeric(16,4),----影响成本价入库商品数量
    @cost_money     numeric(16,4),---影响成本入库成本金额
    @init_qty     numeric(16,4),----需计算成本价期初数量
    @init_amt     numeric(16,4),--期初成本金额
    @cost_change_amt numeric(16,4),--调价单生成成本差额
    @sheet_zb_manage char(1), --总部代管实时型门店的单据
    @shop_zb_manage char(1), --托管型门店
    @local_branch_no char(3),--机构代码
    @cost_price_pc      numeric(16,4),@as_version_flag char(1),
 	@daysalenum 	numeric(16,4) --日销售数量合计
   select @flow_id = flow_id, @voucher_no = voucher_no, @oper_date = oper_date, @item_no = item_no, @real_qty = real_qty,
       @price = price, @sheet_amt = sheet_amt, @branch_no = branch_no, @d_branch_no = d_branch_no,
       @trans_no = trans_no, @db_no = db_no, @supcust_no = supcust_no, @sale_way = sale_way,
       @sale_price = sale_price,@cost_price_pc = price
   from inserted
   SELECT @stock_type = trade_type FROM t_bd_branch_info WHERE branch_no = @branch_no
   SELECT @local_branch = isnull(sys_var_value, '') FROM t_sys_system WHERE sys_var_id = 'g_stock_no'
    --读取机构类型
  SELECT @branch_type = trade_type FROM t_bd_branch_info WHERE branch_no = left(@branch_no,3)

  If @stock_type = '7'
   begin
      SELECT @shop_zb_manage = isnull(zbtg_flag, '0') FROM t_bd_branch_info WHERE branch_no = @branch_no
      IF (@shop_zb_manage is null or @shop_zb_manage = '') select @shop_zb_manage = '0'
   end
 -----如果数据为门店上传，则不再往下执行。（分公司目前有传t_im_flow）
  if ( @local_branch <> @branch_no ) and ( @stock_type = '7' or @stock_type = '8' or ((@branch_type ='1' or @branch_type='2') and (left(@local_branch,3) <> left(@branch_no,3)) ))
	begin
		if @shop_zb_manage ='0'
		begin
			select @as_version_flag='0'--version_flag from t_bd_branch_info where branch_no = @branch_no 
			if @as_version_flag = '1' 
			return
		end
	end
	
   SELECT @sheet_zb_manage = isnull(sys_var_value, '0') FROM t_sys_system WHERE sys_var_id = 'sheet_zb_manager'
   IF @sheet_zb_manage is null or @sheet_zb_manage = '' 
      select @sheet_zb_manage = '0'

   SELECT @branch_type = trade_type FROM t_bd_branch_info WHERE branch_no = left(@branch_no,3)
   
   SELECT @ls_operdate = convert(char(10),@oper_date,121)
   SELECT @item_stock = item_stock FROM t_bd_item_info WHERE item_no = @item_no 
   
   SELECT @so_price = base_price, @sup_no = main_supcust, @sale_price = sale_price,@t_in_price = price ,@item_sup_flag = item_sup_flag ,@item_sup_rate = sup_rate
     FROM t_bd_item_info WHERE item_no = @item_no
   
   SELECT @lb_change_price = isnull(change_cost,'0') FROM t_sys_sheet_no WHERE sheet_id = @trans_no
   
    select @adjust_num1 = 0
   select @adjust_num1 = convert(numeric,sys_var_value) from t_sys_system where sys_var_id = 'adjust_cost_settle1'
   select @adjust_num1 = isnull(@adjust_num1, 0)
   
   select @adjust_num2 = 0
   select @adjust_num2 = convert(numeric,sys_var_value) from t_sys_system where sys_var_id = 'adjust_cost_settle2'
   select @adjust_num2 = isnull(@adjust_num2, 0)
   
   select @stock_qty = 0
   select @stock_money = 0
   select @stock_price = 0
   
   SELECT @stock_qty = isnull(stock_qty,0), @stock_price = isnull(avg_cost,0), @stock_money = isnull(cost_amt ,0)
    FROM t_im_branch_stock WHERE branch_no=@branch_no and item_no=@item_no
   
   select @stock_money = isnull(@stock_money,0)
   select @stock_qty   = isnull(@stock_qty,  0)
   select @cost_qty=0
   select @cost_money=0
   select @init_qty=0
   select @init_amt=0
   select @cost_change_amt=0
   
   if @trans_no='PC' 
   begin
       exec pr_pc_change_costprice @item_no, @branch_no, @cost_price_pc
   end

   if ( (@branch_type='0' or @branch_type='2' )  and (@stock_type='5' or @stock_type='6' or (@stock_type='3' and @sheet_zb_manage = '1') or (@stock_type = '7' and @shop_zb_manage = '1'))  and charindex(@trans_no,'PI/RO/GI/CS')>=1) 
   
       SELECT @cost_qty = isnull(pi_qty,0), @cost_money = isnull(pi_amt ,0),@init_qty=isnull(init_qty,0),@init_amt=isnull(init_amt,0),@cost_change_amt=isnull(cost_change_amt,0)
       FROM t_da_jxc_daysum WHERE branch_no=@branch_no and item_no=@item_no and oper_date = @ls_operdate
   
   else if ( not ( (@branch_type='0' or @branch_type='2' )  and (@stock_type='5' or @stock_type='6' or (@stock_type='3' and @sheet_zb_manage = '1') or (@stock_type = '7' and @shop_zb_manage = '1')) ) and charindex(@trans_no,'PI/RO/GI/CS/MI/DI')>=1)
   
       SELECT @cost_qty = isnull(pi_qty+mi_qty+di_qty,0), @cost_money = isnull(pi_amt+mi_amt+di_amt ,0),@init_qty=isnull(init_qty,0),@init_amt=isnull(init_amt,0),@cost_change_amt=isnull(cost_change_amt,0)
       FROM t_da_jxc_daysum WHERE branch_no=@branch_no and item_no=@item_no and oper_date = @ls_operdate
   
   select @cost_money = isnull(@cost_money, 0.0000)
   select @cost_qty   = isnull(@cost_qty  , 0)
   select @init_amt   = isnull(@init_amt  , 0)
   select @init_qty   = isnull(@init_qty  , 0)
   
   select @cost_qty=@cost_qty+@init_qty
   select @cost_money=@cost_money+@init_amt+@cost_change_amt
   
   if (@cost_qty is null or @cost_qty=0) 
      select @cost_qty=@stock_qty ,@cost_money=@stock_money
   
   IF (@cost_qty is null or @cost_qty = 0) and (@stock_qty is null or @stock_qty = 0) 
      Select @cost_qty = 0,@cost_money = 0,@stock_price = @t_in_price
   if ( ( @branch_type='0' or @branch_type='2' )  and(  (@stock_type = '5') or  (@stock_type='6') or(@stock_type = '3'  and @sheet_zb_manage = '1') or (@stock_type = '7'  and @shop_zb_manage = '1'))and charindex(@trans_no,'PI/RO/GI/CS')>=1) or ( not ( (@branch_type='0' or @branch_type='2' )  and @stock_type='5' ) and charindex(@trans_no,'PI/RO/GI/CS/MI/DI')>=1 )
   begin
      if @db_no = '+'
      begin
        if @stock_qty < 0 and @stock_qty + @real_qty > 0 
           select @cost_price = @price
        else if @stock_qty + @real_qty <= 0 or @stock_money + @real_qty * @price < 0 or @cost_qty + @real_qty <=0
           select @cost_price = @price
        else
           select @cost_price = abs(@cost_money + @real_qty * @price)/abs(@cost_qty + @real_qty)
      end 
      else  
begin
        if @stock_qty < 0 and @stock_qty - @real_qty > 0
           select @cost_price = @price
        else if @stock_qty - @real_qty <= 0 or @stock_money - @real_qty * @price < 0 or @cost_qty - @real_qty <=0
           select @cost_price = @price
        else
           select @cost_price = abs(@cost_money - @real_qty * @price)/abs(@cost_qty - @real_qty)
      end
   end 
   else
      select @cost_price = @stock_price
   
   update t_im_flow set cost_price = @cost_price where flow_id = @flow_id
   if @@error != 0 goto myerror
  
   if @trans_no='GI'  --进货入库
   BEGIN
      
      IF exists(select item_no from t_im_branch_stock where item_no=@item_no and branch_no = @branch_no)
         update t_im_branch_stock 
	    set last_inprice=@price,oper_date=getdate(),
		avg_cost = case (@real_qty + @stock_qty) when 0 then avg_cost else (case when cost_amt/(@real_qty + @stock_qty)<0 then avg_cost else cost_amt/(@real_qty + @stock_qty) end) end,
		cost_amt = case (@real_qty + @stock_qty) when 0 then 0 else (case when cost_amt/(@real_qty + @stock_qty)<0 then (@real_qty + @stock_qty)*avg_cost else cost_amt end) end,
                stock_qty=stock_qty + @real_qty ,com_flag='0'
          where branch_no=@branch_no and item_no=@item_no
      ELSE
         insert into t_im_branch_stock (branch_no, item_no, stock_qty, avg_cost,last_inprice,oper_date,cost_amt)
                values(@branch_no, @item_no, @real_qty, @cost_price,@price,getdate(),@real_qty * @cost_price)
      
      IF @@error != 0 goto myerror
	      IF exists(select * from t_da_jxc_daysum where(item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no))
	           UPDATE t_da_jxc_daysum
	            SET   avg_cost_amt = (case (@stock_qty + @real_qty) when 0 then 0 else (case when settle_amt/(@stock_qty + @real_qty)<0 then cost_price else settle_amt/(@stock_qty + @real_qty) end) end) * (so_qty + pos_qty + pos_ret_qty + pos_send_qty), 
			    cost_price = (case (@stock_qty + @real_qty) when 0 then cost_price else (case when settle_amt/(@stock_qty + @real_qty)<0 then cost_price else (settle_amt/(@stock_qty + @real_qty)) end) end),
	                  avg_profit_amt = (so_amt + pos_amt + pos_ret_amt) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty),
	                        pi_qty = pi_qty + @real_qty, 
	                        settle_qty = @stock_qty + @real_qty,
				settle_amt = case (@stock_qty + @real_qty) when 0 then 0 else (case when settle_amt/(@stock_qty + @real_qty)<0 then (@stock_qty + @real_qty)*cost_price else settle_amt end) end,
	                        settle_amt2 = (@stock_qty + @real_qty)* @sale_price, com_flag = '0'
	           WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
	      
	      ELSE
	           INSERT INTO t_da_jxc_daysum (
	               item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,
	               avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,
	               mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,
	               pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,
	               cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag,item_sup_flag )
	           VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,
	                  0,0,@real_qty,@real_qty * @price,0,0,0,0,
	               0,0,0,0,0,0,0,0,0,0,0,0,0,0,@stock_qty + @real_qty,round((@stock_qty + @real_qty) * @cost_price,4),(@stock_qty + @real_qty)* @sale_price,
	               0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)

      IF @@error != 0 goto myerror
   
   END  --- if @trans_no='GI'  --进货入库
   
   
   if @trans_no='PI' or @trans_no='RO'  --进货入库
   BEGIN
 
      
      IF exists(select item_no from t_im_branch_stock where item_no=@item_no and branch_no = @branch_no)
         update t_im_branch_stock set last_inprice=@price,oper_date=getdate(),avg_cost = @cost_price ,
                cost_amt = (stock_qty + @real_qty)*@cost_price, stock_qty=stock_qty + @real_qty ,com_flag='0'
          where branch_no=@branch_no and item_no=@item_no
      ELSE
         insert into t_im_branch_stock (branch_no, item_no, stock_qty, avg_cost,last_inprice,oper_date,cost_amt)
             values(@branch_no, @item_no, @real_qty, @cost_price,@price,getdate(),@real_qty * @cost_price)
      
      if @@error != 0 goto myerror
	     IF exists(select * from t_da_jxc_daysum where(item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no))
	      
	          UPDATE  t_da_jxc_daysum
	            SET   avg_cost_amt = @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty), cost_price = @cost_price,
	            avg_profit_amt = (so_amt + pos_amt + pos_ret_amt) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty),
	                  pi_qty = pi_qty + @real_qty, pi_amt = pi_amt + @real_qty * @price,
	                  settle_qty = @stock_qty + @real_qty,settle_amt = (@stock_qty + @real_qty) * @cost_price,
	                  settle_amt2 = (@stock_qty + @real_qty)* @sale_price, com_flag = '0'
	          WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
	        
	      ELSE
	         INSERT INTO t_da_jxc_daysum (
	            item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,
	            avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,
	            mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,
	            pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,
	            cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag,item_sup_flag )
	          VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,
	                     0,0,@real_qty,@real_qty * @price,0,0,0,0,
	            0,0,0,0,0,0,0,0,0,0,0,0,0,0,@stock_qty + @real_qty,(@stock_qty + @real_qty) * @cost_price,(@stock_qty + @real_qty)* @sale_price,
	            0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)

      If @@error != 0 goto myerror
   END  --if @trans_no='PI' or @trans_no='RO'  --进货入库
   
   if @trans_no='SO' or @trans_no='RI' or @trans_no='SI' --销售
   BEGIN

      IF exists(select item_no from t_im_branch_stock where item_no=@item_no and branch_no = @branch_no)
        
          update t_im_branch_stock set last_baseprice=@price,oper_date=getdate(),avg_cost = @cost_price ,
                cost_amt = (stock_qty - @real_qty) * @cost_price , stock_qty=stock_qty - @real_qty ,com_flag='0'
               where branch_no=@branch_no and item_no=@item_no
      ELSE
          insert into t_im_branch_stock (branch_no, item_no, stock_qty, avg_cost,last_baseprice,oper_date,cost_amt)
          values(@branch_no, @item_no, -@real_qty, @cost_price,@price,getdate(),-@real_qty * @cost_price)
     
      if @@error != 0 goto myerror
	     IF exists(select * from t_da_jxc_daysum where(item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no))
	       
	          UPDATE t_da_jxc_daysum
	               SET   avg_cost_amt = @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty + @real_qty), cost_price = @cost_price,
	               avg_profit_amt = (so_amt + pos_amt + pos_ret_amt + @real_qty * @price) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty + @real_qty),
	                     so_qty = so_qty + @real_qty, so_amt = so_amt + @real_qty * @price,
	                     settle_qty = @stock_qty - @real_qty,settle_amt = (@stock_qty - @real_qty) * @cost_price,
	                     settle_amt2 = (@stock_qty - @real_qty)* @sale_price, com_flag = '0' WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
	      ELSE
	          INSERT INTO t_da_jxc_daysum (
	            item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,
	            avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,
	            mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,
	            pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,
	            cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag,item_sup_flag )
	          VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,
	                     @cost_price * @real_qty, @real_qty * (@price - @cost_price),0,0,@real_qty,@real_qty * @price,0,0,
	            0,0,0,0,0,0,0,0,0,0,0,0,0,0,@stock_qty - @real_qty,(@stock_qty - @real_qty) * @cost_price ,(@stock_qty - @real_qty)* @sale_price,
	            0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)

   END  --if @trans_no='SO' or @trans_no='RI' --销售
   
   if @trans_no='PT'  OR @trans_no='LP' --pos销售
   BEGIN
      IF exists(select item_no from t_im_branch_stock where item_no=@item_no and branch_no = @branch_no)
       
         update t_im_branch_stock set last_baseprice=@price,oper_date=getdate(),avg_cost = @cost_price ,
          cost_amt = (stock_qty - @real_qty) * @cost_price , stock_qty=stock_qty - @real_qty ,com_flag='0'
         where branch_no=@branch_no and item_no=@item_no
       
      ELSE
         insert into t_im_branch_stock (branch_no, item_no, stock_qty, avg_cost,last_baseprice,oper_date,cost_amt)
          values(@branch_no, @item_no, -@real_qty, @cost_price,@price,getdate(),-@real_qty * @cost_price)
      
      if @@error != 0 goto myerror
     
      IF exists(select * from t_da_jxc_daysum where(item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no))
      begin    
         if @sale_way = 'A' 
                UPDATE  t_da_jxc_daysum
                  SET   avg_cost_amt = @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty + @real_qty), cost_price = @cost_price,
                        avg_profit_amt = (so_amt + pos_amt + pos_ret_amt + @real_qty * @price) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty + @real_qty),
                        pos_qty = pos_qty + @real_qty, pos_amt = pos_amt + @real_qty * @price,
                        settle_qty = @stock_qty - @real_qty,settle_amt = (@stock_qty - @real_qty) * @cost_price,
                        settle_amt2 = (@stock_qty - @real_qty)* @sale_price, com_flag = '0'
                WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
           
         else if @sale_way = 'C'
                UPDATE  t_da_jxc_daysum
                  SET   avg_cost_amt = @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty + @real_qty), cost_price = @cost_price,
                        avg_profit_amt = (so_amt + pos_amt + pos_ret_amt + @real_qty * @price) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty + @real_qty),
                        pos_send_qty = pos_send_qty + @real_qty, pos_send_amt = pos_send_amt + @real_qty * @price,
                        settle_qty = @stock_qty - @real_qty,settle_amt = (@stock_qty - @real_qty) * @cost_price,
                        settle_amt2 = (@stock_qty - @real_qty)* @sale_price, com_flag = '0'
                WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
          
         else
                UPDATE  t_da_jxc_daysum
                  SET   avg_cost_amt = @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty + @real_qty), cost_price = @cost_price,
                        avg_profit_amt = (so_amt + pos_amt + pos_ret_amt + @real_qty * @price) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty + @real_qty),
      
                  pos_ret_qty = pos_ret_qty + @real_qty, pos_ret_amt = pos_ret_amt + @real_qty * @price,
                        settle_qty = @stock_qty - @real_qty,settle_amt = (@stock_qty - @real_qty) * @cost_price,
                        settle_amt2 = (@stock_qty - @real_qty)* @sale_price, com_flag = '0'
                WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
      end
      ELSE
      begin
         if @sale_way = 'A'
                INSERT INTO t_da_jxc_daysum (
                  item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,
                  avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,
                  mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,
                  pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,
                  cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag ,item_sup_flag)
                VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,
                           @cost_price * @real_qty, @real_qty * (@price - @cost_price),0,0,0,0,0,0,0,0,0,0,0,0,@real_qty,@real_qty * @price,0,0,
                  0,0,0,0,@stock_qty - @real_qty,(@stock_qty - @real_qty) * @cost_price ,(@stock_qty - @real_qty)* @sale_price,
                  0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)
          
         else if @sale_way = 'B'
                INSERT INTO t_da_jxc_daysum (
                  item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,
                  avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,
                  mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,
                  pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,
                  cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag,item_sup_flag )
                VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,
                           @cost_price * @real_qty, @real_qty * (@price - @cost_price),0,0,0,0,0,0,0,0,0,0,0,0,0,0,@real_qty,@real_qty * @price,0,0,
                  0,0,@stock_qty - @real_qty,(@stock_qty - @real_qty) * @cost_price ,(@stock_qty - @real_qty)* @sale_price,
                  0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)
         
         else
                INSERT INTO t_da_jxc_daysum (
                  item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,
                  avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,
                  mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,
                  pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,
                  cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag,item_sup_flag )
                VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,
                           @cost_price * @real_qty, @real_qty * (@price - @cost_price),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,@real_qty,@real_qty * @price,
                  0,0,@stock_qty - @real_qty,(@stock_qty - @real_qty) * @cost_price ,(@stock_qty - @real_qty)* @sale_price,
                  0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)
      end
   END  --if @trans_no='PT'  OR @trans_no='LP' --pos销售
   
   
   if @trans_no='IO' --转仓单 dutz 2007-05-22
   BEGIN
       if @db_no = '+'
       begin
          IF exists(select item_no from t_im_branch_stock where item_no=@item_no and branch_no = @branch_no)
              update t_im_branch_stock set last_baseprice=@price,oper_date=getdate(),avg_cost = @cost_price ,
                     cost_amt = (stock_qty + @real_qty) * @cost_price , stock_qty=stock_qty + @real_qty ,com_flag='0'
               where branch_no=@branch_no and item_no=@item_no
          ELSE
              insert into t_im_branch_stock (branch_no, item_no, stock_qty, avg_cost,last_baseprice,oper_date,cost_amt)
                values(@branch_no, @item_no, @real_qty, @cost_price,@price,getdate(),@real_qty * @cost_price)
      
          if @@error != 0 goto myerror

          IF exists(select * from t_da_jxc_daysum where(item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no))
              UPDATE    t_da_jxc_daysum
                     SET   avg_cost_amt = @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty), cost_price = @cost_price,
                     avg_profit_amt = (so_amt + pos_amt + pos_ret_amt) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty),
                           di_qty = di_qty + @real_qty, di_amt = di_amt + @real_qty * @price,
                           settle_qty = @stock_qty + @real_qty,settle_amt = (@stock_qty + @real_qty) * @cost_price,
                           settle_amt2 = (@stock_qty + @real_qty)* @sale_price, com_flag = '0'
                   WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
          ELSE
              INSERT INTO t_da_jxc_daysum (
                     item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,
                     avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,
                     mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,
                     pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,
                     cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag,item_sup_flag )
                  VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,
                              0, 0,0,0,0,0,0,0,0,0,@real_qty,@real_qty * @price,
                     0,0,0,0,0,0,0,0,0,0,@stock_qty + @real_qty,(@stock_qty + @real_qty) * @cost_price ,(@stock_qty + @real_qty)* @sale_price,
                     0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)
        end

       else
       begin
          IF exists(select item_no from t_im_branch_stock where item_no=@item_no and branch_no = @branch_no)
              update t_im_branch_stock set last_baseprice=@price,oper_date=getdate(),avg_cost = @cost_price ,
                     cost_amt = (stock_qty - @real_qty) * @cost_price , stock_qty=stock_qty - @real_qty ,com_flag='0'
               where branch_no=@branch_no and item_no=@item_no
          ELSE
              insert into t_im_branch_stock (branch_no, item_no, stock_qty, avg_cost,last_baseprice,oper_date,cost_amt)
                values(@branch_no, @item_no, -@real_qty, @cost_price,@price,getdate(),-@real_qty * @cost_price)
          
          If @@error != 0 goto myerror

	          IF exists(select * from t_da_jxc_daysum where(item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no))
	              UPDATE    t_da_jxc_daysum
	                     SET   avg_cost_amt = @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty), cost_price = @cost_price,
	                     avg_profit_amt = (so_amt + pos_amt + pos_ret_amt) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty),
	                           do_qty = do_qty + @real_qty, do_amt = do_amt + @real_qty * @price,
	                           settle_qty = @stock_qty - @real_qty,settle_amt = (@stock_qty - @real_qty) * @cost_price,
	                           settle_amt2 = (@stock_qty - @real_qty)* @sale_price, com_flag = '0'
	                   WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
	          ELSE
	              INSERT INTO t_da_jxc_daysum (
	                     item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,
	                     avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,
	                     mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,
	                     pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,
	                     cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag,item_sup_flag )
	                  VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,
	                              0, 0,0,0,0,0,0,0,0,0,0,0,@real_qty,@real_qty * @price,
	                     0,0,0,0,0,0,0,0,@stock_qty - @real_qty,(@stock_qty - @real_qty) * @cost_price ,(@stock_qty - @real_qty)* @sale_price,
	                     0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)

       end
   END  --if @trans_no='IO' --转仓单 dutz 2007-05-22
   
   if @trans_no='MI'
   BEGIN
      IF exists(select item_no from t_im_branch_stock where item_no=@item_no and branch_no = @branch_no)
         update t_im_branch_stock set last_inprice=@price,oper_date=getdate(),avg_cost = @cost_price ,  
              cost_amt = (stock_qty + @real_qty) * @cost_price, stock_qty=stock_qty + @real_qty ,com_flag='0'
          where branch_no=@branch_no and item_no=@item_no
      ELSE
         insert into t_im_branch_stock (branch_no, item_no, stock_qty, avg_cost,last_baseprice,oper_date,cost_amt)
          values(@branch_no, @item_no, @real_qty, @cost_price,@price,getdate(),@real_qty * @cost_price)
   
      if @@error != 0 goto myerror

	      IF exists(select * from t_da_jxc_daysum where(item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no))
	         UPDATE   t_da_jxc_daysum
	               SET   avg_cost_amt = @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty), cost_price = @cost_price,
	                     avg_profit_amt = (so_amt + pos_amt + pos_ret_amt) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty),
	                     mi_qty = mi_qty + @real_qty, mi_amt = mi_amt + @real_qty * @price,
	                     settle_qty = @stock_qty + @real_qty,settle_amt = (@stock_qty + @real_qty) * @cost_price,
	                     settle_amt2 = (@stock_qty + @real_qty)* @sale_price, com_flag = '0'
	             WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
	      ELSE
	         INSERT INTO t_da_jxc_daysum (
	               item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,
	               avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,
	               mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,
	               pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,
	               cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag,item_sup_flag )
	            VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,
	                        0, 0,0,0,0,0,@real_qty,@real_qty * @price,
	               0,0,0,0,0,0,0,0,0,0,0,0,0,0,@stock_qty + @real_qty,(@stock_qty + @real_qty) * @cost_price ,(@stock_qty + @real_qty)* @sale_price,
	               0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)

   END  --if @trans_no='MI'
   
   if @trans_no='DI'
   BEGIN
       IF exists(select item_no from t_im_branch_stock where item_no=@item_no and branch_no = @branch_no)
          update t_im_branch_stock set last_inprice=@price,oper_date=getdate(),avg_cost = @cost_price ,
                 cost_amt = (stock_qty + @real_qty) * @cost_price, stock_qty=stock_qty + @real_qty ,com_flag='0'
          where branch_no=@branch_no and item_no=@item_no
       ELSE
         insert into t_im_branch_stock (branch_no, item_no, stock_qty, avg_cost,last_baseprice,oper_date,cost_amt)
          values(@branch_no, @item_no, @real_qty, @cost_price,@price,getdate(),@real_qty * @cost_price)
       
       if @@error != 0 goto myerror

	       IF exists(select * from t_da_jxc_daysum where(item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no))
	           UPDATE    t_da_jxc_daysum
	               SET   avg_cost_amt = @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty), cost_price = @cost_price,
	               avg_profit_amt = (so_amt + pos_amt + pos_ret_amt) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty),
	                     di_qty = di_qty + @real_qty, di_amt = di_amt + @real_qty * @price,
	                     settle_qty = @stock_qty + @real_qty,settle_amt = (@stock_qty + @real_qty) * @cost_price,
	                     settle_amt2 = (@stock_qty + @real_qty)* @sale_price, com_flag = '0'
	             WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
	       ELSE
	          INSERT INTO t_da_jxc_daysum (
	               item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,
	               avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,
	               mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,
	               pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,
	               cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag,item_sup_flag )
	            VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,
	                        0, 0,0,0,0,0,0,0,0,0,@real_qty,@real_qty * @price,
	               0,0,0,0,0,0,0,0,0,0,@stock_qty + @real_qty,(@stock_qty + @real_qty) * @cost_price ,(@stock_qty + @real_qty)* @sale_price,
	               0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)

   END
   
   if @trans_no='MO'
   BEGIN
       IF exists(select item_no from t_im_branch_stock where item_no=@item_no and branch_no = @branch_no)
          update t_im_branch_stock set last_inprice=@price,oper_date=getdate(),avg_cost = @cost_price ,
             cost_amt = (stock_qty - @real_qty) * @cost_price , stock_qty=stock_qty - @real_qty ,com_flag='0'
           where branch_no=@branch_no and item_no=@item_no  
       ELSE
          insert into t_im_branch_stock (branch_no, item_no, stock_qty, avg_cost,last_baseprice,oper_date,cost_amt)
          values(@branch_no, @item_no, -@real_qty, @cost_price,@price,getdate(),-@real_qty * @cost_price)
       
       if @@error != 0 goto myerror

	       IF exists(select * from t_da_jxc_daysum where(item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no))
	            UPDATE    t_da_jxc_daysum
	               SET   avg_cost_amt = @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty), cost_price = @cost_price,
	               avg_profit_amt = (so_amt + pos_amt + pos_ret_amt) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty),
	                     mo_qty = mo_qty + @real_qty, mo_amt = mo_amt + @real_qty * @price,
	                     settle_qty = @stock_qty - @real_qty,settle_amt = (@stock_qty - @real_qty) * @cost_price,
	                     settle_amt2 = (@stock_qty - @real_qty)* @sale_price, com_flag = '0'
	             WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
	       ELSE
	           INSERT INTO t_da_jxc_daysum (
	               item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,
	               avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,
	               mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,
	               pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag,item_sup_flag )
	            VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,
	                        0, 0,0,0,0,0,0,0,@real_qty,@real_qty * @price,
	               0,0,0,0,0,0,0,0,0,0,0,0,@stock_qty - @real_qty,(@stock_qty - @real_qty) * @cost_price ,(@stock_qty - @real_qty)* @sale_price,
	               0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)

   END
   
   if @trans_no='DO'
   BEGIN
       IF exists(select item_no from t_im_branch_stock where item_no=@item_no and branch_no = @branch_no)
          update t_im_branch_stock set last_inprice=@price,oper_date=getdate(),avg_cost = @cost_price ,
                 cost_amt = (stock_qty - @real_qty) * @cost_price , stock_qty=stock_qty - @real_qty ,com_flag='0'
           where branch_no=@branch_no and item_no=@item_no
       ELSE
          insert into t_im_branch_stock (branch_no, item_no, stock_qty, avg_cost,last_baseprice,oper_date,cost_amt)
          values(@branch_no, @item_no, -@real_qty, @cost_price,@price,getdate(),-@real_qty * @cost_price)
     
      if @@error != 0 goto myerror

	      IF exists(select * from t_da_jxc_daysum where(item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no))
	          UPDATE    t_da_jxc_daysum
	             SET   avg_cost_amt = @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty), cost_price = @cost_price,
	                     avg_profit_amt = (so_amt + pos_amt + pos_ret_amt) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty),
	                     do_qty = do_qty + @real_qty, do_amt = do_amt + @real_qty * @price,
	                     settle_qty = @stock_qty - @real_qty,settle_amt = (@stock_qty - @real_qty) * @cost_price,
	                     settle_amt2 = (@stock_qty - @real_qty)* @sale_price, com_flag = '0'
	          WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
	      ELSE
	          INSERT INTO t_da_jxc_daysum ( 
	               item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,   
	               avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,   
	               mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,
	               pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,
	               cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag,item_sup_flag )
	            VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,
	                        0, 0,0,0,0,0,0,0,0,0,0,0,@real_qty,@real_qty * @price,
	               0,0,0,0,0,0,0,0,@stock_qty - @real_qty,(@stock_qty - @real_qty) * @cost_price ,(@stock_qty - @real_qty)* @sale_price,
	               0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)

   END
   
   if @trans_no='OO' or @trans_no='PD' or @trans_no='CS' --Add By Huangsc 2006.06.28 添加组合拆分单
   BEGIN
       IF @db_no = '-'
         select @real_qty = - @real_qty
       
       IF exists(select item_no from t_im_branch_stock where item_no=@item_no and branch_no = @branch_no)
          update t_im_branch_stock set last_inprice=@price,oper_date=getdate(),avg_cost = @cost_price ,
             cost_amt = (stock_qty + @real_qty) * @cost_price, stock_qty=stock_qty + @real_qty ,com_flag='0'
           where branch_no=@branch_no and item_no=@item_no
       ELSE
          insert into t_im_branch_stock (branch_no, item_no, stock_qty, avg_cost,last_inprice,oper_date,cost_amt)
          values(@branch_no, @item_no, @real_qty, @cost_price,@price,getdate(),@real_qty * @cost_price)
       
       if @@error != 0 goto myerror

	       IF exists(select * from t_da_jxc_daysum where(item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no))
	     
	          UPDATE  t_da_jxc_daysum
	            SET   avg_cost_amt = @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty), cost_price = @cost_price,avg_profit_amt = (so_amt + pos_amt + pos_ret_amt) - @cost_price * (so_qty + pos_qty + pos_ret_qty + pos_send_qty),oo_qty = oo_qty + @real_qty, oo_amt = oo_amt + @real_qty * @price,settle_qty = @stock_qty + @real_qty,settle_amt = (@stock_qty + @real_qty) * @cost_price,settle_amt2 = (@stock_qty + @real_qty)* @sale_price, com_flag = '0'
	          WHERE (item_no=@item_no and oper_date=@ls_operdate and branch_no=@branch_no)
	       ELSE
	          INSERT INTO t_da_jxc_daysum (item_no, oper_date, branch_no, cost_price, init_qty, init_amt, init_amt2,avg_cost_amt, avg_profit_amt,pi_qty, pi_amt, so_qty, so_amt, mi_qty,mi_amt,mo_qty, mo_amt, di_qty, di_amt,do_qty, do_amt, pos_qty,pos_amt, pos_ret_qty,pos_ret_amt,pos_send_qty,pos_send_amt, oo_qty, oo_amt, settle_qty,settle_amt,settle_amt2,cost_change_amt, pos_dct_amt, main_sup, dec_rate,so_price, sale_price, com_flag,item_sup_flag )
	         VALUES ( @item_no, @ls_operdate ,@branch_no, @cost_price,@stock_qty,@stock_money,@stock_qty*@sale_price,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,@real_qty,@real_qty * @price,@stock_qty + @real_qty,(@stock_qty + @real_qty) * @cost_price,(@stock_qty + @real_qty)* @sale_price,0,0,@sup_no,0,@so_price, @sale_price, '0',@item_sup_flag)
      
      if @@error != 0 goto myerror
   END   
   if @item_stock = '1'  
      delete t_im_branch_stock where item_no = @item_no
   
   if @@error != 0 goto myerror
myerror:
  return
end
go


if exists(select 1 from sysobjects where name='fn_get_scheme_price')
  drop function fn_get_scheme_price
go

create function fn_get_scheme_price(
	@branch_no		varchar(6)='000001', -- from 
    @d_branch_no	varchar(6)='000002', -- to 
    @sheet_branch	varchar(6)='000001',	--制单机构
	@item_no		varchar(20)='02030113'
)
returns numeric(16, 4)
begin
	declare 
        @price_value_branch		varchar(6),
        @price_option_branch	varchar(6),
	@price				numeric(16, 4),
	@price_ratio		numeric(16, 4),
	@ratio_flag			char(1),
	@price_option		char(1),
        @f_trade_type		char(1),
        @t_trade_type		char(1),
	@p_trade_type		char(1),
        @Flag				char(1),
        @is_scheme_db		char(1)
		--trade_type:[总部	0 ] [自营店	3] [加盟店	4] [代管店	6] [仓库	5]
		--price_option:  [进价	1] [加价率	2] [配送价	3] [零售价	4] [成本价	0]
        select @f_trade_type = trade_type from t_bd_branch_info where branch_no = @branch_no	
        select @t_trade_type = trade_type from t_bd_branch_info where branch_no = @d_branch_no
		if @f_trade_type = '5' set @f_trade_type = '0'
		if @t_trade_type = '5' set @t_trade_type = '0'
        
        set @is_scheme_db ='0'
	    if @f_trade_type='0'  
	    begin
	       if (select price_option from t_bd_branch_info where branch_no = @d_branch_no)='3' 
	          select @is_scheme_db='1'
	    end
	    if @t_trade_type='0'
	    begin
	       if (select price_option from t_bd_branch_info where branch_no = @branch_no)='3' 
	          select @is_scheme_db='1'
	    end 
	    
        select @Flag ='0', @ratio_flag = '0'

		--加盟店参与的调拨
        if (@f_trade_type ='4' or @t_trade_type ='4')--总部给加盟店调拨
        begin 
           if @f_trade_type ='4'
           begin
              select @price_value_branch = @branch_no
              select @price_option_branch = @branch_no
           end
           else
           begin
              select @price_value_branch = @d_branch_no
              select @price_option_branch = @d_branch_no
           end
           select @Flag ='1'
        end -- end with: @f_trade_type ='4' or @t_trade_type ='4'
        else if @is_scheme_db ='1' and (@f_trade_type ='3' or @t_trade_type = '3') --总部 与 配送价选项为 配送价 的自营店调拨
        begin
           if @f_trade_type ='3'
           begin
              select @price_value_branch = @branch_no
              select @price_option_branch = @branch_no
           end
           else
           begin
              select @price_value_branch = @d_branch_no
              select @price_option_branch = @d_branch_no
           end
           select @Flag ='1'
        end 
		else
		begin	
			--总部与自营店之间的调拨			
			if (@f_trade_type ='0' or @t_trade_type = '0')	
			begin
			   if @f_trade_type ='0'
			   begin
					if exists(select 1 from t_bd_branch_info where branch_no = @d_branch_no and price_option = '2')
						select @price_value_branch = @branch_no, @ratio_flag = '1'
					--else if exists(select 1 from t_bd_branch_info where branch_no = @d_branch_no and price_option = '3')--自营店的调拨取门店的价格
					--	select @price_value_branch = @d_branch_no
					else
						select @price_value_branch = @sheet_branch
					select @price_option_branch = @d_branch_no
			   end
			   else
			   begin
					if exists(select 1 from t_bd_branch_info where branch_no = @branch_no and price_option = '2')
						select @price_value_branch = @d_branch_no, @ratio_flag = '1'
					else
						select @price_value_branch = @sheet_branch
					select @price_option_branch = @branch_no
			   end
			end -- end with: if (@f_trade_type ='0' or @t_trade_type = '0')

			--自营店与自营店之间的调拨
			else
			begin
			   select @price_value_branch = @sheet_branch
			   select @price_option_branch = @sheet_branch
			end
	
		end
	
		select @p_trade_type = trade_type from t_bd_branch_info where branch_no = @price_value_branch
	    select @price_option = price_option from t_bd_branch_info where branch_no = @price_option_branch
		if @price_option = '' or @price_option is null 
			set @price_option = '1'

		--自营店与自营店之间的调拨(不存在加价率的情况，如果指定为加价率，则直接为进价)
		if @price_option = '2' and @ratio_flag = '0' 
			set @price_option = '1'
		
        if @price_option = '1'		--进价
        begin
          select @price = price from t_pc_branch_price where item_no = @item_no and branch_no = @price_value_branch
          if @price is null and @p_trade_type ='5' 
              select @price = price from t_bd_item_info where item_no = @item_no
        end
        else if @price_option ='0'	--成本价
		begin
			select top 1 @price = avg_cost from t_im_branch_stock where item_no = @item_no and branch_no = @price_value_branch
			if @price is null
			begin
				select @price = price from t_pc_branch_price where item_no = @item_no and branch_no = @price_value_branch
				if @price is null and @p_trade_type ='5' 
					select @price = price from t_bd_item_info where item_no = @item_no
			end
		end
		else if @price_option = '2'	--进价加价率
		begin
			select @price_ratio = isnull(price_ratio,0) from t_bd_branch_info where branch_no = @price_option_branch
			if @price_ratio is null or @price_ratio < 0 
				set @price_ratio = 0
			select @price = price * (1 + @price_ratio) from t_bd_item_info where item_no = @item_no
		end
		else if @price_option = '3'	--配送价
        begin
			select @price = scheme_price from t_pc_branch_price where item_no = @item_no and branch_no = @price_value_branch
			if @price is null and (@p_trade_type ='5' or @Flag ='1')
				select @price = scheme_price from t_bd_item_info where item_no = @item_no
        end
		else if @price_option = '4'	--零售价
		begin
			select @price = sale_price from t_pc_branch_price where item_no = @item_no and branch_no = @price_value_branch
			if @price is null and @p_trade_type ='5'
				select @price = sale_price from t_bd_item_info where item_no = @item_no
		end
        else
           select @price = 0
	if @price is null 
		set @price = 0
	return @price
end
go
