- 博客(0)
- 资源 (5)
- 收藏
- 关注
RSS订阅K3触发器实现对物料单据管控 .txt
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.fdate0 and t1.fdateCast(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
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
declare @icmo3 varchar(20)
declare @message3 varchar(200)
set @message3='错误!委外加工发出日期不能小于对应委外加工生产任务单计划开工日期,请与采购人员联系变更!错误单号:'
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.fdate0 and t1.fdate0 and t1.fdate0 and t1.fdate<t3.FPlanCommitDate
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<0 and t3.fclosed=1
)
begin
RAISERROR(@message500,18,18)
ROLLBACK
end
return
end
*/
2009-11-05
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人