k3 生成备品出库单的触发器

本文介绍了一个用于K3系统的SQL Server触发器,该触发器在销售出库单审核时自动生成备品出库单,并更新相关库存记录。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

转载 http://blog.youkuaiyun.com/wai631/article/details/6599142

k3 生成备品出库单的触发器


create trigger [dbo].[ICStockBill_ProcessSOFreeQty1] ON [dbo].[ICStockBill]
	for insert,update
as
set nocount on
/******************************************************************************
 * TABLE NAME:    ICStockBill                                                 *
 * TRIGGER NAME:  ICStockBill_ProcessSOFreeQty1                               *              *
 * CREATED BY:                                                                *
 * CREATION DATE: 2007/08/01 17:04                                            *
 * DESCRIPTION:                                                               *
 ******************************************************************************/

if exists (select * from Inserted as i join Deleted as d on i.FInterID=d.FInterID join ICStockBillEntry as e on i.FInterID=e.FInterID
	where isnull(e.FEntrySelfB0158,0)<>0 and i.FTranType=21 and i.FCheckerID<>0 and isnull(d.FCheckerID,0)=0)
begin
                
	declare @id int, 
                @entry int
	declare @FROB int    --红蓝单标志


	exec GetICMaxNum 'ICStockBill', @id output

	if @@error<>0 or isnull(@id, -1)=-1
	begin
	
		if @@trancount > 0
		rollback
		return
	end
	insert ICStockBill (FBrNo, FInterID, FTranType, FOrgBillInterID, FDate, FBillNo, FExplanation, FDeptID, FEmpID, FSupplyID, 
		FFManagerID, FSManagerID, FBillerID, FCheckerID, FCheckDate, FSaleStyle, FROB, FUpStockWhenSave,
		FPOStyle, FPurposeID, FMarketingStyle, FFetchAdd, FRefType, FUse, FBillTypeID, FStatus)
	select i.FBrNo, @id, 29, i.FInterID, i.FDate, i.FBillNo+'B', '销售出库单'+rtrim(i.FBillNo)+'备品出库', i.FDeptID, i.FEmpID, i.FSupplyID, 
		i.FFManagerID, i.FSManagerID, i.FBillerID, i.FCheckerID, i.FCheckDate, i.FSaleStyle, i.FROB, i.FUpStockWhenSave,
		null, i.FPurposeID, i.FMarketingStyle, i.FFetchAdd, i.FRefType, i.FUse, 1000, 1
	        from Inserted as i 
                join Deleted as d on i.FInterID=d.FInterID 
	        where i.FTranType=21 and i.FCheckerID<>0 and isnull(d.FCheckerID,0)=0
          --状态由0变成1时,即审核时
if @@error<>0
	begin

		rollback
		return
	end
	
	declare @FBrNo int, @FItemID int, @FQty decimal(28,10), @FPrice decimal(28,10), @FAuxQty decimal(28,10), @FAuxPrice decimal(28,10), 
		@FBatchNo varchar(200),	@FNote varchar(255), @FUnitID int, @FDCSPID int, @FDCStockID int, @FKFDate datetime, @FKFPeriod int, @FAuxPropID int
	
	declare temp_cursor cursor for
	select e.FBrNo, e.FItemID, round(e.FEntrySelfB0158*u.FCoefficient, b.FQtyDecimal), e.FPrice, e.FEntrySelfB0158, e.FAuxPrice, 
		e.FBatchNo, e.FNote, e.FUnitID, e.FDCSPID, e.FDCStockID, e.FKFDate, e.FKFPeriod, e.FAuxPropID
	from ICStockBillEntry as e 
                join Inserted as i on e.FInterID=i.FInterID 
                join Deleted as d on i.FInterID=d.FInterID 
		join t_ICItemBase as b on e.FItemID=b.FItemID 
                join t_MeasureUnit as u on e.FUnitID=u.FItemID
	where e.FEntrySelfB0158<>0 and i.FTranType=21 and i.FCheckerID<>0 and isnull(d.FCheckerID,0)=0
        order by e.fentryid

          --状态由0变成1时,即审核时
	if @@error<>0
	begin
		rollback
		return
	end

	open temp_cursor
	fetch temp_cursor into @FBrNo, @FItemID, @FQty, @FPrice, @FAuxQty, @FAuxPrice, @FBatchNo, 
		@FNote, @FUnitID, @FDCSPID, @FDCStockID, @FKFDate, @FKFPeriod, @FAuxPropID

	select @entry = 0
	while(@@fetch_status = 0)
	begin
		select @entry = @entry + 1
                if @FQty>0 
                        select @FROB=1
                else
                        select @FROB=-1
		insert ICStockBillEntry (FBrNo, FInterID, FEntryID, FItemID, FQty, FPrice, FAuxQty, FAuxPrice, FAmount, 
			FBatchNo, FNote, FUnitID, FDCSPID, FDCStockID, FKFDate, FKFPeriod, FAuxPropID)
		values (@FBrNo, @id, @entry, @FItemID, @FQty, 0.01, @FAuxQty, 0, 0, 
			@FBatchNo, @FNote, @FUnitID, @FDCSPID, @FDCStockID, @FKFDate, @FKFPeriod, @FAuxPropID)
		if @@error<>0
		begin

			rollback
			return
		end
	
		fetch temp_cursor into @FBrNo, @FItemID, @FQty, @FPrice, @FAuxQty, @FAuxPrice, @FBatchNo, 
			@FNote, @FUnitID, @FDCSPID, @FDCStockID, @FKFDate, @FKFPeriod, @FAuxPropID
	end
end
insert ICInventory (FBrNo, FItemID, FBatchNo, FStockID, FStockPlaceID, FKFDate, FKFPeriod, FAuxPropID)
select r.FBrNo, r.FItemID, r.FBatchNo, r.FDCStockID, r.FDCSPID, r.FKFDate, r.FKFPeriod, r.FAuxPropID
from ICInventory as v 
     right join (select distinct e.FBrNo, e.FItemID, e.FBatchNo, e.FDCStockID, e.FDCSPID, 
		FKFDate=(case when e.FKFDate is null then '' else convert(char(10), e.FKFDate, 20) end), 
                e.FKFPeriod, e.FAuxPropID 
		from ICStockBillEntry as e 
                        join ICStockBill as s on e.FInterID=s.FInterID 
			join Inserted as i on s.FOrgBillInterID=i.FInterID 
                        join Deleted as d on i.FInterID=d.FInterID
		where s.FTranType=29 and s.FCheckerID<>0 and i.FTranType=21 and isnull(i.FCheckerID,0)<>isnull(d.FCheckerID,0))as r 
        on v.FItemID=r.FItemID and v.FBatchNo=r.FBatchNo and v.FStockID=r.FDCStockID and v.FStockPlaceID=r.FDCSPID and v.FKFDate=r.FKFDate 
           and v.FKFPeriod=r.FKFPeriod and v.FAuxPropID=r.FAuxPropID
     where v.FItemID is null

--更改库存
if @@error<>0
begin
	rollback
	return
end

update v set FQty = v.FQty - r.FQty
from ICInventory as v join
	(select e.FBrNo, e.FItemID, e.FBatchNo, e.FDCStockID, e.FDCSPID, 
		FKFDate=(case when e.FKFDate is null then '' else convert(char(10), e.FKFDate, 20) end), 
		e.FKFPeriod, e.FAuxPropID, FQty=sum(case when isnull(d.FCheckerID,0)=0 then e.FQty else -e.FQty end) 
		from ICStockBillEntry as e 
                     join ICStockBill as s on e.FInterID=s.FInterID 
		     join Inserted as i on s.FOrgBillInterID=i.FInterID 
                     join Deleted as d on i.FInterID=d.FInterID
		where s.FTranType=29 and s.FCheckerID<>0 and i.FTranType=21 and isnull(i.FCheckerID,0)<>isnull(d.FCheckerID,0)
		group by e.FBrNo, e.FItemID, e.FBatchNo, e.FDCStockID, e.FDCSPID, e.FKFDate, e.FKFPeriod, e.FAuxPropID) as r 
                on v.FItemID=r.FItemID and v.FBatchNo=r.FBatchNo and v.FStockID=r.FDCStockID 
		and v.FStockPlaceID=r.FDCSPID and v.FKFDate=r.FKFDate and v.FKFPeriod=r.FKFPeriod and v.FAuxPropID=r.FAuxPropID
--更改库存
if @@error<>0
begin
	rollback
	return
end

update s set FCheckerID=0
from ICStockBill as s 
        join Inserted as i on s.FOrgBillInterID=i.FInterID 
        join Deleted as d on i.FInterID=d.FInterID
where s.FTranType=29 and s.FCheckerID<>0 and i.FTranType=21 and isnull(i.FCheckerID,0)=0 and d.FCheckerID<>0
--反审核
if @@error<>0
begin
	rollback
	return
end

delete s
from ICStockBill as s join Inserted as i on s.FOrgBillInterID=i.FInterID join Deleted as d on i.FInterID=d.FInterID
where s.FTranType=29 and i.FTranType=21 and isnull(i.FCheckerID,0)=0 and d.FCheckerID<>0
if @@error<>0
begin
	rollback
	return
end

set nocount off











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=&#39;错误!领料日期不能小于生产任务单计划开工日期,请与生管人员联系!错误单号:&#39; set @message0=&#39;生产领料单发料仓库不正确,倒扣物料不能从 原料仓/半成品仓/成品仓 发料&#39; set @message00=&#39;已完工生产任务单不能跨月领料.请检查领料日期!&#39; set @message000=&#39;生产领料单必需关联源生产任务单号,请重新录入单据!&#39; 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,&#39;no&#39;)&#39;no&#39; and t2.finterid=@finterid240 return end --控制委外加工生产任务单领料日期不对小于计划开工日期 if (@Ftrantype=28) begin declare @icmo3 varchar(20) declare @message3 varchar(200) set @message3=&#39;错误!委外加工发出日期不能小于对应委外加工生产任务单计划开工日期,请与采购人员联系变更!错误单号:&#39; 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,&#39;no&#39;)&#39;no&#39; 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=&#39;已全部出货的销售订单所对应的采购订单不能退料!请检查您所退料的采购订单号码是否正确!&#39; 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 */
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值