金蝶ERP实现产品入库及委外加工冲减生产现场虚仓毛坯数(终结版)

秒速五厘米 2022-02-02 04:21 209阅读 0赞

金蝶ERP实现产品入库及委外加工冲减生产现场虚仓毛坯数(修正委外出库的入库及销售出库的入库冲减两次毛坯的问题)

SET QUOTED\_IDENTIFIER ON  
GO  
SET ANSI\_NULLS ON  
GO

ALTER 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

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

\--更新蓝字,未审核状态的 '销售出库单','领料单','委外出库单','其他出库单'的单价和金额  
\--更新步骤:供应商供货信息,以前月份的期末单价,以前月份的发出单价  
\--供应商供货信息只取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

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

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 @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 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)  
update icmaxnum set fmaxnum = (select max(finterid) from zpstockbill) where ftablename = 'zpstockbill'  
end

ext:

/\*

alter table icstockbill disable trigger icstockbill\_jade01  
alter table icstockbill enable trigger icstockbill\_jade01

\*/

GO  
SET QUOTED\_IDENTIFIER OFF  
GO  
SET ANSI\_NULLS ON  
GO

[附件：http://down.51cto.com/data/2347817][http_down.51cto.com_data_2347817]

本文转自redking51CTO博客，原文链接：http://blog.51cto.com/redking/23235，如需转载请自行联系原作者

[http_down.51cto.com_data_2347817]: http://down.51cto.com/data/2347817