資源描述：
《金蝶K3實自動核算.doc》由會員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。
1、金蝶K3實現(xiàn)自動核算（供應(yīng)商管理單價）原金蝶K3的存貨核算都是手動核算，比較麻煩！HOHO！現(xiàn)編寫SQL語句搞定自動核算！因金蝶K3實現(xiàn)自動核算（物料單價）一文只對物料里面的采購單價獲取價格，對于一個物料有多個供應(yīng)商后有多個采購價格，上文不能實現(xiàn)，因此重新編寫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--更新藍(lán)字,未審核狀態(tài)的'銷售出庫單','領(lǐng)料單','委外出庫單','其他出庫單'的單價和金額--更新步驟:供應(yīng)商供貨信息,以前月份的期末單價,以前月份
3、的發(fā)出單價--供應(yīng)商供貨信息只取RMB的平均單價if@frob=1and@fstatus=0and(@ftrantype=21or@ftrantype=24or@ftrantype=28or@ftrantype=29)??begin??????--更新供應(yīng)商供貨信息平均單價????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,
