金蝶K3实现自动核算.docVIP

金蝶K3实现自动核算（供应商管理单价） 原金蝶K3的存货核算都是手动核算，比较麻烦！HOHO！现编写SQL语句搞定自动核算！因金蝶K3实现自动核算（物料单价）一文只对物料里面的采购单价获取价格，对于一个物料有多个供应商后有多个采购价格，上文不能实现，因此重新编写SQL语句。 （想尽办法偷懒，HOHO！）? ?? ???if exists (select * from sysobjects where name = icstockbill_jade01) drop trigger icstockbill_jade01gocreate trigger icstockbill_jade01 on icstockbillfor insert,updateasdeclare @frob int,@finterid int,@ftrantype int,@fstatus intselect??@frob = frob,@finterid = finterid,@ftrantype = ftrantype,@fstatus = fstatus from inserted--更新蓝字,未审核状态的 销售出库单,领料单,委外出库单,其他出库单的单价和金额--更新步骤:供应商供货信息,以前月份的期末单价,以前月份的发出单价--供应商供货信息只取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) bwhere a.fitemid = b.fitemid and a.finterid = @finteridif @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 = @finteridelse? ???--更新以前月的平均单价? ???update x set fprice = y.fprice,famount = y.fprice * fqty,fauxprice = y.fprice? ???from icstockbillentry x,? ???(select fstockid,