金蝶K3实现自动核算（供应商管理单价）.doc

　　金蝶K3实现自动核算（供应商管理单价）教育资源库 　　原金蝶K3的存货核算都是手动核算，比较麻烦！HOHO！现编写SQL语句搞定自动核算！因金蝶K3实现自动核算（物料单价）一文只对物料里面的采购单价获取价格，对于一个物料有多个供应商后有多个采购价格，上文不能实现，因此重新编写SQL语句。 （想尽办法偷懒，HOHO！） 　　　　if exists (select * from sysobjects e = #39;icstockbill_jade01#39;) drop trigger icstockbill_jade01 go 　　 create trigger icstockbill_jade01 on icstockbill for insert,update as 　　 declare frob int,finterid int,ftrantype int,fstatus int select　frob = frob,finterid = finterid,ftrantype = ftrantype,fstatus = fstatus from inserted 　　--更新蓝字,未审核状态的 #39;销售出库单#39;,#39;领料单#39;,#39;委外出库单#39;,#39;其他出库单#39;的单价和金额 　　--更新步骤:供应商供货信息,以前月份的期末单价,以前月份的发出单价 　　--供应商供货信息只取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 id) b id = 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 icinvbal 　　 id,fyear * 100 + fperiod) y, 　　 (select fstockid,fitemid,max(fyear * 100 + fperiod) as fperiods from icinvbal 　　 id) z 　　 id = 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 icinvbal 　　 id,fyear * 100 + fperiod) y, 　　 (select fstockid,fitemid,max(fyear * 100 + fperiod) as fperiods from icinvbal 　　 id) z 　　 id = z.fitemid and y.fperiods = z.fperiods 　　 and x.fdcstockid = y.fstockid 12下一页 这篇文章来