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 @finterid240 int
set @message='错误!领料日期不能小于生产任务单计划开工日期,请与生管人员联系!错误单号:'
set @message0='生产领料单发料仓库不正确,倒扣物料不能从 原料仓/半成品仓/成品仓 发料'
set @message00='已完工生产任务单不能跨月领料.请检查领料日期!'
set @message000='生产领料单必需关联源生产任务单号,请重新录入单据！'
if exists (
select t1.fbillno from inserted as t1
inner join icstockbillentry as t2 on t2.finterid=t1.finterid
where t2.ficmointerid=0
)
begin
RAISERROR(@message000,18,18)
ROLLBACK
end
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<t3.FPlanCommitDate )
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<t3.FPlanCommitDate
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 @finterid240=finterid from inserted
update t2 set t2.fentryselfb0445=t4.fbillno from icstockbill as t1
