SQl存储过程：销售出库更新生产周计划单出货数量和产品入库自动生成领料单

---销售出库更新生产周计划单出货数量和产品入库自动生成领料单
ALTER trigger [GF_UpdateFOUTQty] on [dbo].[ICStockBill]
for update
as

declare @FinterID int,@FStatus int,@FOldStatus int,@FTranType int

select @FinterID = FinterID,@FStatus = FStatus,@FTranType = FTranType from inserted 
select @FOldStatus = FStatus from deleted 

--销售出库单审核
    if update(FStatus) and @FStatus = 1 and @FOldStatus = 0 and @FTranType = 21
Begin
    update t2 set  t2.FOUTQty = t2.FOUTQty+b.FQty  from icstockbill a
    inner join icstockbillentry b on a.FinterID = b.FinterID
    inner join seorderentry s2 on b.FSourceInterID = s2.FinterID and b.FSourceEntryID = s2.FEntryID
    inner join t_bossczjhdentry t2 on s2.FinterID = t2.FID_SRC and s2.FEntryID = t2.FEntryID_SRC
    where a.FTranType = '21' and a.FinterID = @FinterID
END

--产品入库单审核
    if update(FStatus) and @FStatus = 1 and @FOldStatus = 0 and @FTranType = 2
Begin
--循环产品入库单每一行
        declare @i int,@j int  
        set @i=1
        select @j = max(FEntryID) from 
            (select DENSE_RANK()over(order by b.FEntryID) as FEntryID,b.FinterID from icstockbill a
            inner join icstockbillentry b on a.FinterID = b.FinterID
            where a.FinterID = @FinterID) a
        group by FinterID
        while @i<=@j
        begin
--循环产品入库关联的生产投料单每一行
                declare @FBillNO varchar(255),@FName varchar(255),@FQty decimal(28, 10),@FEntryID int

                select @FBillNO = s2.FBillNo,@FName = t1.FName,@FQty = b.FQty,@FEntryID = s1.FEntryID from icstockbill a
                inner join icstockbillentry b on a.FinterID = b.FinterID
                inner join t_icitem t1 on b.FItemID = t1.FItemID
                inner join icmo m1 on b.FSourceInterID = m1.FInterID
                inner join seorderentry s1 on m1.FOrderInterID = s1.FInterID and m1.FSourceEntryID = s1.FEntryID
                inner join seorder s2 on s1.FInterID = s2.FInterID
                where a.FInterID = @FinterID and b.FEntryID = @i