请在分店和总部都执行下面存储过程后再核实
--调价单审核生成t_pc_price_flow_branch数据 200622-970331 yanghc 2020-07-04
if exists(select * from sysobjects where id=object_id('pr_pc_price_flow_branch_gen'))
drop procedure pr_pc_price_flow_branch_gen
go
create procedure pr_pc_price_flow_branch_gen
(
@as_sheetno u_sheet_no --单号
)
with encryption
as
begin
declare @branchlist varchar(4000),
@branch_no varchar(2),
@err varchar(100)
delete t_pc_price_flow_branch where sheet_no = @as_sheetno
if @@error != 0
begin
set @err = '删除各门店调价数据失败！'
goto myerror
end
--从调价单头表取机构
create table #temp_branch (branch_no varchar(2) null)
select @branchlist = branchlist from t_pc_price_flow_master where sheet_no = @as_sheetno
while len(@branchlist) > 0
begin
select @branch_no=substring(@branchlist,1,dbo.fn_gethqlen()) -- 逐个分店更新价格
select @branchlist = (case len(@branchlist) when dbo.fn_gethqlen() then ''
else substring(@branchlist,4,len(@branchlist)-3) end )
insert into #temp_branch (branch_no) values (@branch_no)
end
--按调价单明细取机构价格数据
select a.item_no,a.branch_no,isnull(c.price,a.old_price) old_price,isnull(c.sup_ly_rate,a.old_price3) old_price3,
isnull(c.sale_price,old_price1) as old_price1, isnull(c.vip_price, a.old_price2) old_price2, a.old_price4
into #temp_branch_price
from
( select a.item_no, b.branch_no, a.old_price, a.old_price3, a.old_price1, a.old_price2, a.old_price4
from t_pc_price_flow_detail a,
#temp_branch b
where a.sheet_no = @as_sheetno
) a left join t_pc_branch_price c on a.item_no = c.item_no and a.branch_no = c.branch_no
create index i_temp_branch_price on #temp_branch_price(item_no,branch_no)
if @@error != 0
begin
set @err = '生成#temp_branch_price数据失败！'
goto myerror
end
--生成t_pc_price_flow_branch数据
insert into t_pc_price_flow_branch
(sheet_no,item_no,price_type,start_date,end_date,
old_price,new_price,
old_price1,old_price2,old_price3,old_price4,
new_price1,new_price2,new_price3,new_price4,
discount,stock_qty,num1,num2,other1,other2,other3,discount_flag,branch_no)
select a.sheet_no,b.item_no,'1',b.start_date,b.end_date,
c.old_price,case when substring(a.chgtype,1,1) = '1' then b.new_price else c.old_price end,
c.old_price1,c.old_price2,c.old_price3,b.old_price4,
case when substring(a.chgtype,3,1) = '1' then b.new_price1 else c.old_price1 end,
case when substring(a.chgtype,4,1) = '1' then b.new_price2 else c.old_price2 end,
case when substring(a.chgtype,2,1) = '1' then b.new_price3 else c.old_price3 end,
case when substring(a.chgtype,5,1) = '1' then b.new_price4 else c.old_price4 end,
b.discount,b.stock_qty,b.num1,b.num2,b.other1,b.other2,b.other3,b.discount_flag,c.branch_no
from t_pc_price_flow_master a, t_pc_price_flow_detail b left join #temp_branch_price c on b.item_no = c.item_no
where a.sheet_no = @as_sheetno and a.sheet_no = b.sheet_no
and b.item_no = c.item_no
order by c.branch_no,b.flow_id
if @@error != 0
begin
set @err = '生成#t_pc_price_flow_branch数据失败！'
goto myerror
end
drop table #temp_branch_price
return
myerror:
raiserror(@err,16,1)
return
end
go