原金蝶K3的存货核算都是手动核算,比较麻烦!HOHO!现编写SQL语句搞定自动核算!因
金蝶K3实现自动核算(物料单价)
一文只对物料里面的采购单价获取价格,对于一个物料有多个供应商后有多个采购价格,上文不能实现,因此重新编写SQL语句。 (想尽办法偷懒,HOHO!)
if exists (select * from sysobjects where name = 'icstockbill_jade01') drop trigger icstockbill_jade01
go
go
create trigger icstockbill_jade01 on icstockbill
for insert,update
as
for insert,update
as
declare @frob int,@finterid int,@ftrantype int,@fstatus int
select @frob = frob,@finterid = finterid,@ftrantype = ftrantype,@fstatus = fstatus from inserted
select @frob = frob,@finterid = finterid,@ftrantype = ftrantype,@fstatus = fstatus from inserted
--更新蓝字,未审核状态的 '销售出库单','领料单','委外出库单','其他出库单'的单价和金额
--更新步骤:供应商供货信息,以前月份的期末单价,以前月份的发出单价
--供应商供货信息只取RMB的平均单价
--更新步骤:供应商供货信息,以前月份的期末单价,以前月份的发出单价
--供应商供货信息只取RMB的平均单价
if @frob = 1 and @fstatus = 0 and (@ftrantype = 21 or @ftrantype = 24 or @ftrantype = 28 or @ftrantype = 29)
begin
--更新供应商供货信息平均单价
update a set fprice = isnull(b.fprice,0),famount = isnull(b.fprice,0) * fqty,fauxprice = isnull(b.fprice,0)
from icstockbillentry a ,(select fitemid,convert(decimal(18,2),avg(fprice)) as fprice from t_supplyentry where fcyid = 1 group by fitemid) b
where a.fitemid = b.fitemid and a.finterid = @finterid
begin
--更新供应商供货信息平均单价
update a set fprice = isnull(b.fprice,0),famount = isnull(b.fprice,0) * fqty,fauxprice = isnull(b.fprice,0)
from icstockbillentry a ,(select fitemid,convert(decimal(18,2),avg(fprice)) as fprice from t_supplyentry where fcyid = 1 group by fitemid) b
where a.fitemid = b.fitemid and a.finterid = @finterid
if @ftrantype = 24
--更新以前月的平均单价
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),avg(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
group by fstockid,fitemid,fyear * 100 + fperiod) 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 and x.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),avg(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
group by fstockid,fitemid,fyear * 100 + fperiod) 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 and x.finterid = @finterid
else
--更新以前月的平均单价
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),avg(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
group by fstockid,fitemid,fyear * 100 + fperiod) 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 and x.finterid = @finterid
end
/*
alter table icstockbill disable trigger icstockbill_jade01
alter table icstockbill enable trigger icstockbill_jade01
alter table icstockbill enable trigger icstockbill_jade01
*/
![](http://redking.blog.51cto.com/attachment/200703/200703101173503891226.jpg?x-oss-process=image/resize,w_1400/format,webp)
![](http://redking.blog.51cto.com/attachment/200703/200703101173503927818.jpg?x-oss-process=image/resize,w_1400/format,webp)
![](http://redking.blog.51cto.com/attachment/200703/200703101173503949573.jpg?x-oss-process=image/resize,w_1400/format,webp)
![](http://redking.blog.51cto.com/attachment/200703/200703101173503968368.jpg?x-oss-process=image/resize,w_1400/format,webp)
![](http://redking.blog.51cto.com/attachment/200703/200703101173503990420.jpg?x-oss-process=image/resize,w_1400/format,webp)
![](http://redking.blog.51cto.com/attachment/200703/200703101173504006425.jpg?x-oss-process=image/resize,w_1400/format,webp)
=========================================================
附件:http://down.51cto.com/data/2347747
本文转自redking51CTO博客,原文链接:http://blog.51cto.com/redking/19681,如需转载请自行联系原作者