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

原金蝶K3的存货核算都是手动核算，比较麻烦！HOHO！蝶K3实蝶协同 webim是干什么的，对于一个物料有多个供应商后有多个采购费用，上文不能实蝶协同 webim是干什么的，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

--更新蓝字,未审核状态的 '销售出库单','领料单','委外出库单','其他出库单'的单价和总额

--更新流程:供应商供货信息,以前季度的期末单价,以前月份的发出单价

--供应商供货信息只取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) 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),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 = @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 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.fdcstockid = y.fstockid and x.fitemid = y.fitemid and x.finterid = @finterid

end

alter table icstockbill disable trigger icstockbill_jade01

alter table icstockbill enable trigger icstockbill_jade01

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

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

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/ruanjian/article-121896-1.html