-- \\\|///
-- \\ - - //
-- ( @ @ )
--┏━━━━━━━━━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