|
单据上加自定义字段,在后台用sql触发器 CREATE trigger icstockbill_check --实现对物料单据管控 on Icstockbill for insert as Declare @Ftrantype int --单据类别定义/生产领料单24/调拔单41/销售出库单21 Select @Ftrantype=Ftrantype from inserted --控制领料单的领料日期不能小于生产任务单的计划开工日期 倒扣物料只能车间仓库发料 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='错误!领料日期不能小于生产任务单计划开工日期,请与生管人员联系!错误单号:' set @message00='已完工生产任务单不能跨月领料.请检查领料日期!' set @message24000='一张生产领料单只能领同一类别的物料.请检查领料单!' set @fsumcount=0 --控制领料单的日期不能小于生产任务单计划开工日期 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 --完工生产任务单不能跨月领料 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 --限制一张领料单只能领同一类型的物料 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 --更新领料单上的销售订单号 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 --更新委外加工出库单销售订单号 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='产品入库日期不能小于生产任务单计划开工日期,错误单号:' --产品入库单入库日期不能小于生产任务单计划开工日期 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 ; --更新产品入库单上的销售订单号 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 --更新委外加工入单销售订单号 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 --控制出全部出货的销售订单所对应的采购订单不能退料(红字外购入库单) /*if (@Ftrantype=1) begin declare @message500 varchar(200) set @message500='已全部出货的销售订单所对应的采购订单不能退料!请检查您所退料的采购订单号码是否正确!' 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 */ |
|
|
