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











评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值