物料变更

alter TRIGGER [GF_物料变更] 
ON [dbo].[t_BOSPosqbg]
FOR update
AS 
SET ANSI_WARNINGS OFF

declare @FID int,@FMultiCheckStatus int,@FOldStatus int 

select @FID = FID,@FMultiCheckStatus = FMultiCheckStatus  from inserted
select @FOldStatus = FMultiCheckStatus from deleted 

if update (FMultiCheckStatus) and @FOldStatus = 2 and @FMultiCheckStatus = 4  --单据启动审核时
begin        
        update g2 set g2.FitemIDbcp = b1.FitemID,g2.FBOMNumber = b1.FBOMNumber from t_bosposqbg  g1
        inner join t_bosposqbgentry1 g2 on g1.fid = g2.fid
        inner join t_bosposqbgentry2 g3 on g1.fid = g3.fid and g2.findex = g3.findex
        inner join porequestentry    p2 on g2.FID_SRC = p2.FinterID and g2.FEntryID_SRC = p2.FEntryID
        inner join porequest         p1 on p2.FinterID = p1.FinterID
        inner join icmrpresult m1 on p2.FPlanOrderinterID = m1.finterid ---采购申请的计划订单 -变更-可不变更
        inner join ICMRPresult  m2 on m1.FSrcPlanOrderinterID = m2.FinterID  --取半成品名称,计划订单为空的为合并计算导致--半成品计划订单
        inner join icmo  r   on m2.finterid = r.FPlanOrderinterID --半成品生产任务单
        inner join icbom b1 on r.fitemid = b1.fitemid --半成品bom表
        inner join icbomchild b2 on b1.finterid = b2.finterid and p2.fitemid = b2.fitemid  --半成品bom明细表 -变更
end

if update (FMultiCheckStatus) and @FOldStatus = 4 and @FMultiCheckStatus = 16  --单据审核时
begin
        if exists (select 1 from t_bosposqbg where FID  = @FID and FBOMChange = 1)
        begin
        update b2 set b2.FitemID = g3.FitemID,b2.FunitID = g3.FunitID from t_bosposqbg  g1
        inner join t_bosposqbgentry1 g2 on g1.fid = g2.fid
        inner join t_bosposqbgentry2 g3 on g1.fid = g3.fid and g2.findex = g3.findex
        inner join porequestentry    p2 on g2.FID_SRC = p2.FinterID and g2.FEntryID_SRC = p2.FEntryID
        inner join porequest         p1 on p2.FinterID = p1.FinterID
        inner join icmrpresult m1 on p2.FPlanOrderinterID = m1.finterid ---采购申请的计划订单 -变更-可不变更
        inner join ICMRPresult  m2 on m1.FSrcPlanOrderinterID = m2.FinterID  --取半成品名称,计划订单为空的为合并计算导致--半成品计划订单
        inner join icmo  r   on m2.finterid = r.FPlanOrderinterID --半成品生产任务单
        inner join icbom b1 on r.fitemid = b1.fitemid --半成品bom表
        inner join icbomchild b2 on b1.finterid = b2.finterid and p2.fitemid = b2.fitemid  --半成品bom明细表 -变更
        where g1.FID = @FID
        end

update p2 set p2.FitemID = g3.FitemID,p2.FunitID = g3.FunitID from t_bosposqbg  g1
inner join t_bosposqbgentry1 g2 on g1.fid = g2.fid
inner join t_bosposqbgentry2 g3 on g1.fid = g3.fid and g2.findex = g3.findex
inner join porequestentry    p2 on g2.FID_SRC = p2.FinterID and g2.FEntryID_SRC = p2.FEntryID
where g1.FID = @FID

end

if update (FMultiCheckStatus) and @FOldStatus = 16 and @FMultiCheckStatus = 4  --单据审核时
begin
        if exists (select 1 from t_bosposqbg where FID  = @FID and FBOMChange = 1)
        begin
        update b2 set b2.FitemID = g2.FitemID,b2.FunitID = g2.FunitID from t_bosposqbg  g1
        inner join t_bosposqbgentry1 g2 on g1.fid = g2.fid
        inner join t_bosposqbgentry2 g3 on g1.fid = g3.fid and g2.findex = g3.findex
        inner join porequestentry    p2 on g2.FID_SRC = p2.FinterID and g2.FEntryID_SRC = p2.FEntryID
        inner join porequest         p1 on p2.FinterID = p1.FinterID
        inner join icmrpresult m1 on p2.FPlanOrderinterID = m1.finterid ---采购申请的计划订单 -变更-可不变更
        inner join ICMRPresult  m2 on m1.FSrcPlanOrderinterID = m2.FinterID  --取半成品名称,计划订单为空的为合并计算导致--半成品计划订单
        inner join icmo  r   on m2.finterid = r.FPlanOrderinterID --半成品生产任务单
        inner join icbom b1 on r.fitemid = b1.fitemid --半成品bom表
        inner join icbomchild b2 on b1.finterid = b2.finterid and p2.fitemid = b2.fitemid  --半成品bom明细表 -变更
        where g1.FID = @FID
        end

update p2 set p2.FitemID = g2.FitemID,p2.FunitID = g2.FunitID from t_bosposqbg  g1
inner join t_bosposqbgentry1 g2 on g1.fid = g2.fid
inner join t_bosposqbgentry2 g3 on g1.fid = g3.fid and g2.findex = g3.findex
inner join porequestentry    p2 on g2.FID_SRC = p2.FinterID and g2.FEntryID_SRC = p2.FEntryID
where g1.FID = @FID

end