新功能上线：客户端写博客，随时保存，图片批量贴　 IT北斗星争霸，看看咱博友的真面目！

金蝶K3实现自动核算（物料单价）

2007-03-08 16:44:34

原金蝶K3的存货核算都是手动核算，比较麻烦！HOHO！现编写SQL语句搞定自动核算！（想尽办法偷懒，HOHO！）

if exists (select * from sysobjects where name = 'icstockbill_jade01') 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

--更新蓝字,未审核状态的 '销售出库单','领料单','委外出库单','其他出库单'的单价和金额
--更新步骤:物料的采购单价,以前月份的期末单价,以前月份的发出单价

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.forderprice,0),famount = isnull(b.forderprice,0) * fqty,fauxprice = isnull(b.forderprice,0)
    from icstockbillentry a ,t_icitem 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),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) 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

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),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) 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
    end

/*

alter table icstockbill disable trigger icstockbill_jade01
alter table icstockbill enable trigger icstockbill_jade01

*/

============================================

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

本文出自 “大唐网络” 博客，请务必保留此出处http://redking.blog.51cto.com/27212/19679

附件下载：
　　

自动核算（物料单价）