IDC资讯中心
— \\\|///
— \\ – – //
— ( @ @ )
–┏━━━━━━━━━oooo-(_)-oooo━┓
–┃ 定义于2004-07-16 ┃
–┃ 成本试算存储过程定义，计算成本 ┃
–┃ oooo ┃
–┗━━━━━━━━━ oooo━-( )━┛
— ( ) ) /
— \ ( (_/
— \_)
create procedure sp_costing_compute
( @out_matnr varchar(18),
@out_werks varchar(4),
@out_datetime datetime)
as
–插入赠品
declare @matnr varchar(18),@werks varchar(4),@kriqi datetime,@meins varchar(10)
declare @bomatnr varchar(18),@bomsehl varchar(10),@menge float
declare @price float,@danw varchar(20)
declare @umrez float,@aaprice float,@jjprice float,@arate float,@jrate float,@asy float,@jsy float
declare @cpjg float,@umren float
set @cpjg = 0
–计算产成品成本价格
declare mycursor cursor for select meins,aprice,jprice from tb_matnr where convert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120) and matnr = @out_matnr and plant=@out_werks
open mycursor
fetch mycursor into @meins,@aaprice,@jjprice
close mycursor
deallocate mycursor
declare bccursor cursor for select bomatnr,bomsehl,menge from tb_bom where cpmatnr=@out_matnr and werks=@out_werks and convert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
open bccursor
fetch bccursor into @bomatnr,@bomsehl,@menge
while @@fetch_status = 0
begin
declare jgcursor cursor for select price,meins from tb_bcjg where matnr=@bomatnr and plant=@out_werks and convert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
open jgcursor
fetch jgcursor into @price,@danw
close jgcursor
deallocate jgcursor
if @price = 0
begin
update tb_matnr set mstatus=置疑 where matnr = @out_matnr and plant = @out_werks and convert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
break
end
–如果价格单位和bom单位不同，需要进行转换
if @bomsehl <> @danw
begin
declare zhgxcursor cursor for select umrez from tb_switch where matnr = @bomatnr and msehl = @danw
open zhgxcursor
fetch zhgxcursor into @umrez
close zhgxcursor
deallocate zhgxcursor
end
if @umrez is null
set @umrez = 1
set @cpjg = @cpjg + @menge * @price / @umrez
fetch next from bccursor into @bomatnr,@bomsehl,@menge
end
close bccursor
deallocate bccursor
–查看bom成品单位与成品销售单位，如果不同添加转换关系
declare cpzhcursor cursor for select umren from tb_switch where matnr = @out_matnr and msehl = @meins
open cpzhcursor
fetch cpzhcursor into @umren
close cpzhcursor
deallocate cpzhcursor
if @umren is null
set @umren = 1
if @cpjg <> 0
set @cpjg = @cpjg / @umren
if not @aaprice is null
begin
set @arate = @aaprice – @cpjg
if @aaprice <> 0
set @asy=@arate / @aaprice
end
else
begin
set @arate=0
set @asy=0
end
if not @jjprice is null
begin
set @jrate = @jjprice – @cpjg
if @jjprice <> 0
set @jsy = @jrate / @jjprice
end
else
begin
set @jrate = 0
set @jsy = 0
end
update tb_matnr set cbprice = @cpjg,apeyoff=@arate,jpayoff=@jrate,arate=@asy,jrate=@jsy where matnr=@out_matnr and plant=@out_werks and convert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
go
