金蝶ERP实现产品入库冲减生产现场虚仓毛坯数，销售成品代码前一位数对应毛坯件的唯一代码，一一对应，其中增加委外加工时的半成品冲减功能。具体语句如下：
if exists(select 1 from sysobjects where name = 'icstockbill_jade01') drop trigger icstockbill_jade01
go
go
create trigger icstockbill_jade01 on icstockbill
for insert,update,delete
as
declare @frob int,@finterid int,@ftrantype int,@fstatus int
select @frob = frob,@finterid = finterid,@ftrantype = ftrantype,@fstatus = fstatus
from inserted
--from icstockbill where finterid = 26864
select @frob = frob,@finterid = finterid,@ftrantype = ftrantype,@fstatus = fstatus
from inserted
--from icstockbill where finterid = 26864
declare @fstatus1 smallint,@fstatus2 smallint,@fbillno varchar(40)
select @fbillno = fbillno,@fstatus1 = fstatus from inserted
--from icstockbill where finterid = 26864
select @fstatus2 = fstatus from deleted
--select @fstatus2 = 0
--select @fstatus2 = 0
--更新蓝字,未审核状态的 '销售出库单','领料单','委外出库单','其他出库单'的单价和金额
--更新步骤:供应商供货信息,以前月份的期末单价,以前月份的发出单价
--供应商供货信息只取RMB的平均单价
--更新步骤:供应商供货信息,以前月份的期末单价,以前月份的发出单价
--供应商供货信息只取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
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
--更新以前月的平均单价
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
if @ftrantype = 2 and ((isnull(@fstatus1,0) = 1 and isnull(@fstatus2,0) = 0) or (isnull(@fstatus1,0) = 0 and isnull(@fstatus2,0) = 1))
begin
declare @finterid_zp int
declare @fitemid_mp int,@fstockid_mp as int,@fqty_mp decimal(18,2)
--虚仓入库单最大内码
select @finterid_zp = isnull(max(finterid),0) + 1 from zpstockbill
--产品入库单第一行产品对应的毛坯
select @fitemid_mp = c.fitemid from icstockbillentry a,t_icitem b,t_icitem c
where a.fitemid = b.fitemid and finterid = @finterid
and c.fnumber = case left(b.fnumber,3) when '11.' then '15.001' when '12.' then '15.001' when '13.' then '15.002' when '14.' then '15.003' when '20.' then '15.002' end
and fentryid = 1
declare @fitemid_mp int,@fstockid_mp as int,@fqty_mp decimal(18,2)
--虚仓入库单最大内码
select @finterid_zp = isnull(max(finterid),0) + 1 from zpstockbill
--产品入库单第一行产品对应的毛坯
select @fitemid_mp = c.fitemid from icstockbillentry a,t_icitem b,t_icitem c
where a.fitemid = b.fitemid and finterid = @finterid
and c.fnumber = case left(b.fnumber,3) when '11.' then '15.001' when '12.' then '15.001' when '13.' then '15.002' when '14.' then '15.003' when '20.' then '15.002' end
and fentryid = 1
if isnull(@fitemid_mp,0) = 0 goto ext
--产品入库单第一行产品对应的毛坯虚仓
select @fstockid_mp = c.fitemid from icstockbillentry a,t_icitem b,t_stock c
where a.fitemid = b.fitemid and finterid = @finterid
and c.fnumber = case left(b.fnumber,3) when '11.' then '22' when '12.' then '22' when '13.' then '23' when '14.' then '22' when '20.' then '23' end
and fentryid = 1
--仓库入库单总数量
select @fqty_mp = sum(fqty) from icstockbillentry where finterid = @finterid
--审核过程
if isnull(@fstatus1,0) = 1 and isnull(@fstatus2,0) = 0
begin
--新增单据头
insert into zpstockbill(fbrno,finterid,ftrantype,frob,fdate,fbillno,fcheckerid,ffmanagerid,fsmanagerid,fbillerid,fdeptid,fstatus,fcheckdate,fbilltypeid)
select fbrno,@finterid_zp as finterid,26 as ftrantype,frob,fdate,fbillno,fcheckerid,ffmanagerid,fsmanagerid,fbillerid,fdeptid,fstatus,fcheckdate,12551 as fbilltypeid
from icstockbill where finterid = @finterid
--新增单据体
insert into zpstockbillentry(fbrno,finterid,fentryid,fitemid,fqty,funitid,fauxqty,fdcstockid)
select 0 as fbrno,@finterid_zp as finterid,1 as fentryid,
@fitemid_mp as fitemid,
@fqty_mp as fqty,@fqty_mp as fauxqty,
(select funitid from t_icitem where fitemid = @fitemid_mp) as funitid,
@fstockid_mp as fdcstockid
select @fstockid_mp = c.fitemid from icstockbillentry a,t_icitem b,t_stock c
where a.fitemid = b.fitemid and finterid = @finterid
and c.fnumber = case left(b.fnumber,3) when '11.' then '22' when '12.' then '22' when '13.' then '23' when '14.' then '22' when '20.' then '23' end
and fentryid = 1
--仓库入库单总数量
select @fqty_mp = sum(fqty) from icstockbillentry where finterid = @finterid
--审核过程
if isnull(@fstatus1,0) = 1 and isnull(@fstatus2,0) = 0
begin
--新增单据头
insert into zpstockbill(fbrno,finterid,ftrantype,frob,fdate,fbillno,fcheckerid,ffmanagerid,fsmanagerid,fbillerid,fdeptid,fstatus,fcheckdate,fbilltypeid)
select fbrno,@finterid_zp as finterid,26 as ftrantype,frob,fdate,fbillno,fcheckerid,ffmanagerid,fsmanagerid,fbillerid,fdeptid,fstatus,fcheckdate,12551 as fbilltypeid
from icstockbill where finterid = @finterid
--新增单据体
insert into zpstockbillentry(fbrno,finterid,fentryid,fitemid,fqty,funitid,fauxqty,fdcstockid)
select 0 as fbrno,@finterid_zp as finterid,1 as fentryid,
@fitemid_mp as fitemid,
@fqty_mp as fqty,@fqty_mp as fauxqty,
(select funitid from t_icitem where fitemid = @fitemid_mp) as funitid,
@fstockid_mp as fdcstockid
--审核减少库存数量
select @fqty_mp = - @fqty_mp
end
select @fqty_mp = - @fqty_mp
end
--反审核过程
if isnull(@fstatus1,0) = 0 and isnull(@fstatus2,0) = 1
begin
update zpstockbill set fstatus = 0,fcheckerid = 0 where fbillno = @fbillno
delete zpstockbill where fbillno = @fbillno
end
if isnull(@fstatus1,0) = 0 and isnull(@fstatus2,0) = 1
begin
update zpstockbill set fstatus = 0,fcheckerid = 0 where fbillno = @fbillno
delete zpstockbill where fbillno = @fbillno
end
--更新库存
if exists (select * from poinventory where fitemid = @fitemid_mp and fstockid = @fstockid_mp)
update poinventory set fqty = fqty + @fqty_mp where fitemid = @fitemid_mp and fstockid = @fstockid_mp
else
insert into poinventory(fbrno,fitemid,fstockid,fqty,fstocktypeid)
select 0,@fitemid_mp,@fstockid_mp,@fqty_mp,(select ftypeid from t_stock where fitemid = @fstockid_mp)
if exists (select * from poinventory where fitemid = @fitemid_mp and fstockid = @fstockid_mp)
update poinventory set fqty = fqty + @fqty_mp where fitemid = @fitemid_mp and fstockid = @fstockid_mp
else
insert into poinventory(fbrno,fitemid,fstockid,fqty,fstocktypeid)
select 0,@fitemid_mp,@fstockid_mp,@fqty_mp,(select ftypeid from t_stock where fitemid = @fstockid_mp)
end
ext:
/*
alter table icstockbill disable trigger icstockbill_jade01
alter table icstockbill enable trigger icstockbill_jade01
alter table icstockbill enable trigger icstockbill_jade01
*/
附件：http://down.51cto.com/data/2347799
本文转自redking51CTO博客，原文链接：http://blog.51cto.com/redking/22192，如需转载请自行联系原作者