go
for insert,update
as
select @frob = frob,@finterid = finterid,@ftrantype = ftrantype,@fstatus = fstatus from inserted
--更新步骤:物料的采购单价,以前月份的期末单价,以前月份的发出单价
begin
--更新采购单价
update a set fprice = isnull(b.forderprice,0),famount = isnull(b.forderprice,0) * fqty,fauxprice = isnull(b.forderprice,0)
from icstockbillentry a ,t_icitem b where a.fitemid = b.fitemid and a.finterid = @finterid
--更新以前月的平均单价
update x set fprice = y.fprice,famount = y.fprice * fqty,fauxprice = y.fprice
from icstockbillentry x,
(select fstockid,fitemid,fyear * 100 + fperiod as fperiods,
convert(decimal(18,2),case when fendqty = 0 then case when fsend <> 0 then fcredit/fsend end else fendbal / fendqty end) as fprice
from icinvbal
where fsend <> 0 or fendqty <> 0) y,
(select fstockid,fitemid,max(fyear * 100 + fperiod) as fperiods from icinvbal
where fsend <> 0 or fendqty <> 0
group by fstockid,fitemid) z
where y.fstockid = z.fstockid and y.fitemid = z.fitemid and y.fperiods = z.fperiods
and x.fscstockid = y.fstockid and x.fitemid = y.fitemid
--更新以前月的平均单价
update x set fprice = y.fprice,famount = y.fprice * fqty,fauxprice = y.fprice
from icstockbillentry x,
(select fstockid,fitemid,fyear * 100 + fperiod as fperiods,
convert(decimal(18,2),case when fendqty = 0 then case when fsend <> 0 then fcredit/fsend end else fendbal / fendqty end) as fprice
from icinvbal
where fsend <> 0 or fendqty <> 0) y,
(select fstockid,fitemid,max(fyear * 100 + fperiod) as fperiods from icinvbal
where fsend <> 0 or fendqty <> 0
group by fstockid,fitemid) z
where y.fstockid = z.fstockid and y.fitemid = z.fitemid and y.fperiods = z.fperiods
and x.fdcstockid = y.fstockid and x.fitemid = y.fitemid
end
/*
alter table icstockbill enable trigger icstockbill_jade01