|
單據(jù)上加自定義字段,在后臺(tái)用sql觸發(fā)器 CREATE trigger icstockbill_check --實(shí)現(xiàn)對(duì)物料單據(jù)管控 on Icstockbill for insert as Declare @Ftrantype int --單據(jù)類別定義/生產(chǎn)領(lǐng)料單24/調(diào)拔單41/銷售出庫單21 Select @Ftrantype=Ftrantype from inserted --控制領(lǐng)料單的領(lǐng)料日期不能小于生產(chǎn)任務(wù)單的計(jì)劃開工日期 倒扣物料只能車間倉庫發(fā)料 if (@Ftrantype=24) begin declare @icmo varchar(20) declare @message varchar(200) declare @message0 varchar(200) declare @message00 varchar(200) declare @message000 varchar(200) declare @message24000 varchar(200) declare @finterid240 int declare @fsumcount int set @message='錯(cuò)誤!領(lǐng)料日期不能小于生產(chǎn)任務(wù)單計(jì)劃開工日期,請(qǐng)與生管人員聯(lián)系!錯(cuò)誤單號(hào):' set @message00='已完工生產(chǎn)任務(wù)單不能跨月領(lǐng)料.請(qǐng)檢查領(lǐng)料日期!' set @message24000='一張生產(chǎn)領(lǐng)料單只能領(lǐng)同一類別的物料.請(qǐng)檢查領(lǐng)料單!' set @fsumcount=0 --控制領(lǐng)料單的日期不能小于生產(chǎn)任務(wù)單計(jì)劃開工日期 if exists( select t3.fbillno from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join icmo as t3 on t3.finterid=t2.fsourceinterid where t2.fsourceinterid>0 and t1.fdate begin select top 1 @icmo=t3.fbillno from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join icmo as t3 on t3.finterid=t2.fsourceinterid where t2.fsourceinterid>0 and t1.fdate set @message=@message+@icmo RAISERROR(@message,18,18) ROLLBACK end --完工生產(chǎn)任務(wù)單不能跨月領(lǐng)料 if exists( select t3.fbillno,t1.fdate,max(t4.fdate) from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join icmo as t3 on t3.finterid=t2.fsourceinterid and t3.fqty=t3.fstockqty inner join icstockbill as t4 on t4.ftrantype=2 inner join icstockbillentry as t5 on t5.finterid=t4.finterid and t5.fsourceinterid=t2.fsourceinterid group by t3.fbillno,t1.fdate having Cast(year(t1.fdate) as int(10))*100+Cast(month(t1.fdate) as int(5)) >Cast(year(max(t4.fdate)) as int(10))*100+Cast(month(max(t4.fdate)) as int(5)) ) begin RAISERROR(@message00,18,18) ROLLBACK end --限制一張領(lǐng)料單只能領(lǐng)同一類型的物料 select @fsumcount=count(distinct(left(t4.fnumber,1))) from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join icmo as t3 on t3.finterid=t2.fsourceinterid and t3.fworkshop=84 inner join t_icitem as t4 on t4.fitemid=t2.fitemid where t1.fbillno>'wout.07.013400' if (@fsumcount>1 ) begin RAISERROR(@message24000,18,18) ROLLBACK end --更新領(lǐng)料單上的銷售訂單號(hào) select @finterid240=finterid from inserted update t2 set t2.fentryselfb0445=t4.fbillno from icstockbill as t1 inner join icstockbillentry as t2 on t1.ftrantype=24 and t1.finterid=t2.finterid inner join icmo as t3 on t3.finterid=t2.ficmointerid left join seorder as t4 on t4.finterid=t3.forderinterid where isnull(t4.fbillno,'no')<>'no' and t2.finterid=@finterid240 return end if (@Ftrantype=28) begin --更新委外加工出庫單銷售訂單號(hào) update t2 set t2.FEntrySelfB0842=t4.fbillno from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join icmo as t3 on t3.finterid=t2.fsourceinterid inner join seorder as t4 on t4.finterid=t3.forderinterid return end if (@Ftrantype=2) begin declare @message4 varchar(200) declare @message41 varchar(200) declare @icmo4 varchar(20) declare @finterid20 int set @message4='產(chǎn)品入庫日期不能小于生產(chǎn)任務(wù)單計(jì)劃開工日期,錯(cuò)誤單號(hào):' --產(chǎn)品入庫單入庫日期不能小于生產(chǎn)任務(wù)單計(jì)劃開工日期 if exists( select t3.fbillno from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join icmo as t3 on t3.finterid=t2.fsourceinterid where t2.fsourceinterid>0 and t1.fdate begin select top 1 @icmo4=t3.fbillno from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join icmo as t3 on t3.finterid=t2.fsourceinterid where t2.fsourceinterid>0 and t1.fdate set @message4=@message4+@icmo4 RAISERROR(@message4,18,18) ROLLBACK end ; --更新產(chǎn)品入庫單上的銷售訂單號(hào) select @finterid20=finterid from inserted update t2 set t2.fentryselfa0236=t4.fbillno from icstockbill as t1 inner join icstockbillentry as t2 on t1.ftrantype=2 and t1.finterid=t2.finterid inner join icmo as t3 on t3.finterid=t2.ficmointerid left join seorder as t4 on t4.finterid=t3.forderinterid where isnull(t4.fbillno,'no')<>'no' and t2.finterid=@finterid20 return end --更新委外加工入單銷售訂單號(hào) if (@Ftrantype=5) begin update t2 set t2.fentryselfa0548=t4.fbillno from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join icmo as t3 on t3.finterid=t2.fsourceinterid inner join seorder as t4 on t4.finterid=t3.forderinterid return end --控制出全部出貨的銷售訂單所對(duì)應(yīng)的采購訂單不能退料(紅字外購入庫單) /*if (@Ftrantype=1) begin declare @message500 varchar(200) set @message500='已全部出貨的銷售訂單所對(duì)應(yīng)的采購訂單不能退料!請(qǐng)檢查您所退料的采購訂單號(hào)碼是否正確!' if exists (select t1.fbillno from inserted as t1 inner join icstockbillentry as t2 on t2.finterid=t1.finterid inner join seorder as t3 on t3.fbillno=t2.fentryselfa0152 where t2.fqty ) begin RAISERROR(@message500,18,18) ROLLBACK end return end */ |
|
|
