资源描述：
《金蝶K3实自动核算.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、金蝶K3实现自动核算（供应商管理单价）原金蝶K3的存货核算都是手动核算，比较麻烦！HOHO！现编写SQL语句搞定自动核算！因金蝶K3实现自动核算（物料单价）一文只对物料里面的采购单价获取价格，对于一个物料有多个供应商后有多个采购价格，上文不能实现，因此重新编写SQL语句。（想尽办法偷懒，HOHO！） ifexists(select*fromsysobjectswherename='icstockbill_jade01')droptriggericstockbill_jade01gocreatetriggerics
2、tockbill_jade01onicstockbillforinsert,updateasdeclare@frobint,@finteridint,@ftrantypeint,@fstatusintselect @frob=frob,@finterid=finterid,@ftrantype=ftrantype,@fstatus=fstatusfrominserted--更新蓝字,未审核状态的'销售出库单','领料单','委外出库单','其他出库单'的单价和金额--更新步骤:供应商供货信息,以前月份的期末单价,以前月份
3、的发出单价--供应商供货信息只取RMB的平均单价if@frob=1and@fstatus=0and(@ftrantype=21or@ftrantype=24or@ftrantype=28or@ftrantype=29) begin --更新供应商供货信息平均单价 updateasetfprice=isnull(b.fprice,0),famount=isnull(b.fprice,0)*fqty,fauxprice=isnull(b.fprice,0) fromicstockbillentrya,(
4、selectfitemid,convert(decimal(18,2),avg(fprice))asfpricefromt_supplyentrywherefcyid=1groupbyfitemid)bwherea.fitemid=b.fitemidanda.finterid=@finteridif@ftrantype=24 --更新以前月的平均单价 updatexsetfprice=y.fprice,famount=y.fprice*fqty,fauxprice=y.fprice fromicstock
5、billentryx, (selectfstockid,fitemid,fyear*100+fperiodasfperiods, convert(decimal(18,2),avg(casewhenfendqty=0thencasewhenfsend 0thenfcredit/fsendendelsefendbal/fendqtyend))asfprice fromicinvbal wherefsend 0orfendqty 0 groupbyfstockid,fitemid,fyear
6、*100+fperiod)y, (selectfstockid,fitemid,max(fyear*100+fperiod)asfperiodsfromicinvbal wherefsend 0orfendqty 0 groupbyfstockid,fitemid)z wherey.fstockid=z.fstockidandy.fitemid=z.fitemidandy.fperiods=z.fperiods andx.fscstockid=y.fstockidandx.fitemid=
7、y.fitemidandx.finterid=@finteridelse --更新以前月的平均单价 updatexsetfprice=y.fprice,famount=y.fprice*fqty,fauxprice=y.fprice fromicstockbillentryx, (selectfstockid,fitemid,fyear*100+fperiodasfperiods, convert(decimal(18,2),avg(casewhenfendqty=0thencasewhenf
8、send 0thenfcredit/fsendendelsefendbal/fendqtyend))asfprice fromicinvbal wherefsend 0orfendqty 0 groupbyfstockid,fitemid,fyear*100+fperiod)y,
