金蝶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