-- ///|///
-- // - - //
-- ( @ @ )
--┏━━━━━━━━━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 [email protected]_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 [email protected]_matnr and [email protected]_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 [email protected] and [email protected]_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 @[email protected] / @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,[email protected],[email protected],[email protected],[email protected] where [email protected]_matnr and [email protected]_werks and convert(varchar(10),kriqi,120)=convert(varchar(10),@out_datetime,120)
go
