委外加工入库审核未成功解决办法.docVIP

-委外加工审核未成功的问题解决办法,-可能原因是表 ICShop_ItemConsume 中FinterID的最大值问题，由于中间有表删除，一些流水号丢失，所以更新下最大值select top 1 * from ICShop_ItemConsume order by FInterID desc select * from ICMaxNum where FTableName like ICShop_ItemConsume - 保证最大值一致,如不是则更新update ICMaxNum set FMaxNum = (select max(finterid) from ICShop_ItemConsume) where FTableName like ICShop_ItemConsume以下代码为其中一列报错中抓取的过程-Declare FSouceTrantype as int Declare WIPDeductMode as Decimal(28,10) -在制品扣减方式 Declare bWriteMethod as Int -是否按标准数量 Declare dRate as Decimal(28,10) -换算率 Declare FWIPQty as Decimal(28,10) -在制品数量 Declare FICMOInterID as int Declare #Data797A90FE46DE4D3DB0887B4794F98159 table(FOrderInterID int , FOrderEntryID INT ,FItemID INT, FStockQty Decimal(28,10),FSecStockQty DECIMAL(28,10)Insert into #Data797A90FE46DE4D3DB0887B4794F98159 SELECT FOrderInterID,FOrderEntryID,FItemID,SUM(ISNULL(u2.FQty,0) as FStockQty,SUM(ISNULL(u2.FSecQty,0) AS FSecStockQty FROM ICStockBillEntry u2 WHERE u2.FICMoInterID = 0 AND u2.FOrderInterID0 AND u2.FInterID= 213494 GROUP BY FOrderInterID,FOrderEntryID,FItemID IF (select count(*) from #Data797A90FE46DE4D3DB0887B4794F98159 ) 0 UPDATE u1 SET u1.FStockQty=ISNULL(u1.FStockQty,0)+ m2.FStockQty, u1.FAuxStockQty=ISNULL(u1.FAuxStockQty,0)+ m2.FStockQty/cast(t2.FCoEfficient as float), u1.FSecStockQty =ISNULL(u1.FSecStockQty,0)+ m2.FSecStockQty FROM ICSubContractEntry u1 INNER JOIN #Data797A90FE46DE4D3DB0887B4794F98159 m2 on u1.FInterID=m2.FOrderInterID AND u1.FEntryID = m2.FOrderEntryID AND u1.FItemID=m2.FItemID INNER JOIN t_MeasureUnit t2 ON u1.FUnitID=t2.FMeasureUnitID UPDATE u1 SET u1.FBCommitQty=ISNULL(u1.FBCommitQty,0)+ m2.FBackQty, u1.FAuxBCommitQty=(ISNULL(u1.FBCommitQty,0)+ m2.FBackQty)/cast(t2.FCoEfficient as float), u1.FSecBCommitQty=ISNULL(u1.FSecBCommitQty,0)+m2.FSecBackQty FROM ICSubContractEntry u1 INNER JOIN (SELECT FOrderInterID,FOrderEntryID,FItemID,SUM(ISNULL(u2.FQty,0) AS FBackQty , SUM(ISNULL(u2.FAuxQty,0) AS FAuxBackQty,SUM(ISNULL(u2.FSecQty,0) AS FSecBackQty FROM ICStockBillEntry u2 WHERE u2.FICMoInterID = 0 AND u2.FOrderInterID0 AND u2.FSourceTranType = 73 AND u2.FQty0 AND FICMOInterID 0 and u2.FPPBOMEntryID0 and u2.FInterID= 213494 GROUP BY u2.FICMOInterID,u2.FPPBOMEntryID,u2.FItemID IF (select count(*) from #ICSTOCK797A90FE46DE4D3DB0887B4794F98159 ) 0 UPDATE u1 SET u1.FStockQty=ISNULL(u1.FStockQty,0)+ ISNULL(m2.FStockQty,0) , u1.FAuxStockQty=ISNULL(u1.FAuxStockQty,0)+ISNULL(ROUND( ISNULL(cast(m2.FStockQty as float),0) /cast(t2.FCoEfficient as float),t1.FQtyDecimal),0) FROM PPBOMEntry u1 INNER JOIN #ICSTOCK797A90FE46DE4D3DB0887B4794F98159 m2 ON u1.FItemID=m2.FItemID AND u1.FEntryID=m2.FPPBOMEntryID AND u1.FInterID=m2.FICMOinterID INNER JOIN t_ICItem t1 ON m2.FItemID=t1.FItemID INNER JOIN t_MeasureUnit t2 ON u1.FUnitID=t2.FMeasureUnitID WHERE u1.FMaterielType IN (372,373,374) -联副产品等级品件 SET NOCOUNT ONDECLARE DeductPoint AS INT,DeductMode AS INTSET DeductMode=0SET DeductPoint=0-构建临时表 记录源单信息，如入库单、任务单汇报单CREATE TABLE #SourceBill( FInterID INT-源单内码 ,FEntryID INT DEFAULT -1-源单分录 ,FItemID INT -物料 ,FICMOInterID INT-任务单或委外订单的内码 ,FICMOEntryID INT DEFAULT 0-委外订单的分录 ,FMOCoefficient DECIMAL(23,10) DEFAULT 1-任务单单位的换算率 ,FPPBOMEntryID INT DEFAULT 0-投料单的分录 ,FSourceInterID INT DEFAULT 0-源单内码（蓝字推红字） ,FSourceEntryID INT DEFAULT 0-源单分录（蓝字推红字） ,FSourceTranType INT DEFAULT 0-源单类型（蓝字推红字） ,FQty DECIMAL(23,10)-本次入库数量，如果是等级品需要换算成产成品 ,FROB INT DEFAULT 1-红蓝字标记 ,FBillNO NVARCHAR(255)-源单编号 ,FTranType INT DEFAULT -1-源单类型 ,FItemConsumeBillNO NVARCHAR(255) DEFAULT -材料耗用编号 ,FDeleted INT DEFAULT 0-删除单据 )CREATE TABLE #ItemConsume( FInterID INT DEFAULT -1 ,FEntryID INT DEFAULT -1 ,FSourceInterID INT-源单内码 ,FSourceEntryID INT-源单分录 ,FICMOInterID INT-任务单内码 ,FICMOEntryID INT DEFAULT 0-委外订单分录 ,FProductID INT-产品ID ,FPPBOMBillNO NVARCHAR(255)-投料单编号 ,FPPBOMInterID INT-投料单内码 ,FPPBOMEntryID INT-投料单分录 ,FItemID INT-原材料分录 ,FUnitID INT-单位 ,FQtyConsume DECIMAL(23,10)-材料耗用 ,FAuxQtyConsume DECIMAL(23,10)-常用单位材料耗用 ,FAccumulateQtyConsume DECIMAL(23,10)-累计耗用 根据总的任务单入库或汇报信息计算所得 ,FAccumulateAuxQtyConsume DECIMAL(23,10)-常用单位累计耗用 根据总的任务单入库或汇报信息计算所得 ,FPPBOMQtyConsume DECIMAL(23,10)-投料单上的累积耗用 ,FPPBOMAuxQtyConsume DECIMAL(23,10)-投料单上的常用单位累积耗用 ,FCancellation INT DEFAULT 0-作废单据 ,FAuxQtyMust DECIMAL(23,10) -常用单位应发数量 ,FQtyMust DECIMAL(23,10) -基本单位应发数量 ,FOperSN INT DEFAULT(0) -工序号 ,FOperID INT DEFAULT(0) -工序 )CREATE TABLE #StockBill( FInterID INT-入库单内码 ,FICMOInterID INT-任务单内码 ,FICMOEntryID INT DEFAULT 0-委外订单分录 ,FStockQty Decimal(23,10)-本次入库数量 ,FAccumulateStockQty Decimal(23,10) DEFAULT 0-累计入库数量 ,FPlanProductQty Decimal(23,10) DEFAULT 0-预计生产数量数量 ,FQtyFinish DECIMAL(23,10)-任务单实作数 )INSERT INTO #SourceBill(FInterID,FTranType,FDeleted)VALUES(213494,5,0)-更新FTranType及FBillNO相关信息-更新FTranType及FBillNO相关信息UPDATE t SET FBillNO=v.FBillNO,FTranType=v.FTranType,FROB=v.FRobFROM #SourceBill tINNER JOIN ICStockBill v On t.FInterID=v.FInterID-获取符合条件的分录信息INSERT INTO #SourceBill(FInterID,FEntryID,FItemID,FICMOInterID,FICMOEntryID,FPPBOMEntryID ,FBillNO,FTranType,FROB,FMOCoefficient ,FQty,FSourceTranType,FSourceInterID,FSourceEntryID,FDeleted)SELECT u.FInterID,u.FEntryID,u.FItemID,u.FOrderInterID,u.FOrderEntryID,u.FPPBOMEntryID ,s.FBillNO,s.FTranType,s.FROB,ISNULL(tm.FCoefficient,1) ,CASE WHEN u.FPPBOMEntryID=0 THEN u.FQty ELSE u.FQty*ISNULL(tm.FCoefficient,1)/ISNULL(u1.FQtyScrap,1) END AS FQty,u.FSourceTranType,u.FSourceInterID,u.FSourceEntryID,s.FDeletedFROM ICStockBillEntry uINNER JOIN #SourceBill s ON s.FInterID=u.FInterIDINNER JOIN ICSubContractEntry m ON m.FInterID=u.FOrderInterID AND m.FEntryID=u.FOrderEntryIDLEFT JOIN t_MeasureUnit tm ON tm.FMeasureUnitID=m.FUnitIDLEFT JOIN PPBOMEntry u1 ON u1.FICMOInterID=u.FOrderInterID AND u1.FOrderEntryID=u.FOrderEntryID AND u1.FEntryID=u.FPPBOMEntryID AND u1.FMaterielType=374-等级品LEFT JOIN PPBOMEntry u2 ON u2.FICMOInterID=u.FOrderInterID AND u2.FOrderEntryID=u.FOrderEntryID AND u2.FEntryID=u.FPPBOMEntryID AND u2.FMaterielType IN (372,373)-联副产品不考虑WHERE s.FEntryID=-1 AND (u.FPPBOMEntryID=0 OR (u.FPPBOMEntryID0 AND u1.FInterID IS NOT NULL)-主产品或等级品 AND u2.FInterID IS NULLDELETE FROM #SourceBill WHERE FEntryID=-1-更新单据编号Update s SET FItemConsumeBillNO=MCR133337FROM #SourceBill sINNER JOIN (SELECT TOP 1 FInterID,FEntryID FROM #SourceBill WHERE FItemConsumeBillNO=) st ON st.FInterID=s.FInterID AND st.FEntryID=s.FEntryIDUpdate s SET FItemConsumeBillNO=MCR133338FROM #SourceBill sINNER JOIN (SELECT TOP 1 FInterID,FEntryID FROM #SourceBill WHERE FItemConsumeBillNO=) st ON st.FInterID=s.FInterID AND st.FEntryID=s.FEntryID-获取本次各个入库单的累积入库数量，等级品按单位用量换算成产成品INSERT INTO #StockBill(FInterID,FICMOInterID,FICMOEntryID,FStockQty)SELECT u.FInterID,u.FOrderInterID,u.FOrderEntryID,SUM(u1.FQty) AS FStockQtyFROM ICStockBillEntry uINNER JOIN #SourceBill u1 ON u1.FInterID=u.FInterID AND u.FEntryID=u1.FEntryIDGROUP BY u.FInterID,u.FOrderInterID,u.FOrderEntryID-更新委外订单的产量信息和已经生产信息的信息(主要处理等级品的问题)UPDATE d SET FAccumulateStockQty=m.FStockQty+ISNULL(djp.FAccumulateStockQty,0)*ISNULL(tm.FCoefficient,1),FPlanProductQty=m.FQtyFROM #StockBill dINNER JOIN ICSubContractEntry m ON m.FInterID=d.FICMOInterID AND m.FEntryID=d.FICMOEntryIDLEFT JOIN t_MeasureUnit tm ON tm.FMeasureUnitID=m.FUnitIDLEFT JOIN (SELECT FICMOInterID,FOrderEntryID,SUM(FStockQty/FQtyScrap) AS FAccumulateStockQty FROM PPBOMEntry WHERE FMaterielType=374 GROUP BY FICMOInterID,FOrderEntryID) djp ON djp.FICMOInterID=d.FICMOInterID AND djp.FOrderEntryID=d.FICMOEntryID-获取理论的耗用表INSERT INTO #ItemConsume(FSourceInterID,FSourceEntryID,FICMOInterID,FICMOEntryID,FProductID,FPPBOMBillNO,FPPBOMInterID,FPPBOMEntryID,FItemID,FUnitID ,FPPBOMQtyConsume,FPPBOMAuxQtyConsume ,FQtyConsume ,FAuxQtyConsume ,FAccumulateQtyConsume ,FAccumulateAuxQtyConsume ,FQtyMust ,FAuxQtyMust ,FOperSN -工序号 ,FOperID -工序代码 )-委外加工入库单SELECT u1.FInterID,u1.FEntryID,u.FICMOInterID,u.FOrderEntryID AS FICMOEntryID,u1.FItemID,v.FBillNO,u.FInterID,u.FEntryID,u.FItemID,u.FUnitID ,u.FQtyConsume,u.FAuxQtyConsume -基本单位预计耗用 ,CASE WHEN DeductMode=0 THEN ROUND(u.FQtyMust*u1.FQty/st.FPlanProductQty,t.FQtyDecimal+1) WHEN DeductMode=1 THEN ROUND(u1.FQty*u.FQtyScrap/u1.FMOCoefficient,t.FQtyDecimal+1) ELSE (CASE WHEN (u1.FTranType=5 AND u1.FROB=-1) -蓝字下推红字,用红字的分摊蓝字耗用 THEN ROUND(ISNULL(B.FQtyConsume,0)*u1.FQty/ISNULL(B.FStockQty,1),t.FQtyDecimal) ELSE u1.FROB*ABS(ROUND(u.FQtyMust-u.FQtyConsume)* (CASE WHEN (st.FPlanProductQty-st.FAccumulateStockQty+st.FStockQty)=0 THEN 1 ELSE u1.FQty/(st.FPlanProductQty-st.FAccumulateStockQty+st.FStockQty) END),t.FQtyDecimal) END) END -预计耗用 ,CASE WHEN DeductMode=0 THEN ROUND(u.FAuxQtyMust*u1.FQty/st.FPlanProductQty,t.FQtyDecimal+1) WHEN DeductMode=1 THEN ROUND(u1.FQty*u.FAuxQtyScrap/u1.FMOCoefficient,t.FQtyDecimal+1) ELSE (CASE WHEN (u1.FTranType=5 AND u1.FROB=-1) -蓝字下推红字,用红字的分摊蓝字耗用 THEN ROUND(ISNULL(B.FQtyConsume,0)*u1.FQty/ISNULL(B.FStockQty,1),t.FQtyDecimal) ELSE u1.FROB*ABS(ROUND(u.FQtyMust-u.FQtyConsume)* (CASE WHEN (st.FPlanProductQty-st.FAccumulateStockQty+st.FStockQty)=0 THEN 1 ELSE u1.FQty/(st.FPlanProductQty-st.FAccumulateStockQty+st.FStockQty) END)/(CASE WHEN tm.FCoefficient=0 THEN 1 ELSE tm.FCoefficient END),t.FQtyDecimal) END) END -基本单位累计耗用 ,CASE WHEN DeductMode=0 THEN ROUND(u.FQtyMust*st.FAccumulateStockQty/st.FPlanProductQty,t.FQtyDecimal) -计划用量 WHEN DeductMode=1 THEN ROUND(st.FAccumulateStockQty*u.FQtyScrap/u1.FMOCoefficient,t.FQtyDecimal) -标准用量 ELSE 0 END-剩余用量 -累计耗用 ,CASE WHEN DeductMode=0 THEN ROUND(u.FAuxQtyMust*st.FAccumulateStockQty/st.FPlanProductQty,t.FQtyDecimal) -计划用量 WHEN DeductMode=1 THEN ROUND(st.FAccumulateStockQty*u.FAuxQtyScrap/u1.FMOCoefficient,t.FQtyDecimal)-标准用量 ELSE 0 END-剩余用量 -基本单位应发数量=基本单位预计耗用 后面统一更新 3.1 ,-1 AS FQtyMust -应发数量=预计耗用 后面统一更新 3.1 ,-1 AS FAuxQtyMust ,0 ,0FROM PPBOMEntry uINNER JOIN PPBOM v ON v.FInterID=u.FInterIDINNER JOIN t_ICItemBase t ON t.FItemID=u.FItemID-投料单使用物料，为了获取基本计量单位INNER JOIN t_MeasureUnit tm ON tm.FMeasureUnitID=u.FUnitID-投料单使用的单位INNER JOIN #StockBill st ON st.FICMOInterID=u.FICMOInterID AND u.FOrderEntryID=st.FICMOEntryID-获取该入库单的上累计入库信息（主要是为了考虑等级品等信息，所以不直接获取任务单信息）INNER JOIN #SourceBill u1 ON u1.FICMOInterID=st.FICMOInterID AND u1.FICMOEntryID=u.FOrderEntryID AND u1.FInterID=st.FInterID LEFT JOIN ICShop_ItemConsume cn ON cn.FIsAutoGen=0 AND cn.FSourceInterID=u1.FInterID AND cn.FSourceEntryID=u1.FEntryID AND cn.FSourceTranType=5-不更新用户手工调整的数据LEFT JOIN (-获取蓝字单据及当初耗用信息 SELECT bv.FSourceTranType,sun.FInterID,sun.FEntryID,sun.FQty AS FStockQty,bl.FPPBOMInterID,bl.FPPBOMEntryID,FQtyConsume,FAuxQtyConsume FROM ICShop_ItemConsumeEntry bl INNER JOIN ICShop_ItemConsume bv ON bv.FInterID=bl.FInterID INNER JOIN ICStockBillEntry sun ON sun.FInterID=bv.FSourceInterID AND sun.FEntryID=bv.FSourceEntryID INNER JOIN #SourceBill sb ON sb.FSourceInterID=sun.FInterID AND sb.FSourceEntryID=sun.FEntryID AND sb.FSourceTranType=2 AND sb.FROB=-1-当前红字单据 ) B ON B.FInterID=u1.FSourceInterID AND B.FEntryID=u1.FSourceEntryID AND B.FSourceTranType=u1.FTranType AND B.FPPBOMInterID=u.FInterID AND B.FPPBOMEntryID=u.FEntryID AND B.FSourceTranType=5WHERE u.FMaterielType NOT IN (372,373,374,376) AND u.FQtyMust0 AND cn.FInterID IS NULLORDER BY u1.FInterID,u1.FEntryID,u.FInterID,u.FEntryID-按入库（汇报）、分录、投料单、投料单分录排序-统一更新处3.1UPDATE #ItemConsume SET FQtyMust=FQtyConsume,FAuxQtyMust=FAuxQtyConsumeIF RowCount0-没有需要生成的耗用记录BEGINDECLARE DeviationPermmited AS DECIMAL(23,10)-获取偏差值DECLARE PPBOMInterID AS INT,PPBOMEntryID AS INT,ICMOInterID AS INT,ICMOEntryID AS INT,ProductID AS INTDECLARE MaxSize AS INT,Loop AS INTDECLARE SumConsume AS DECIMAL(23,10),SumAuxConsume AS DECIMAL(23,10),SumConsume1 AS DECIMAL(23,10),SumAuxConsume1 AS DECIMAL(23,10)SELECT DeviationPermmited=FValue FROM t_SystemProfile WHERE FCategory=SH AND FKey=BackFlushScale-SELECT DeviationPermmited=CONVERT(DECIMAL(23,10),1.0/POWER(convert(decimal(23,10),10),ISNULL(DeviationPermmited,0)-调整差异 这里使用的变量计算逻辑需要特别注意，请不要随意修改SELECT IDENTITY(INT,1,1) AS FIndex,FICMOInterID, FICMOEntryID, FPPBOMInterID,FPPBOMEntryID,FProductID,FItemID,SUM(FQtyConsume) AS FQtyConsume,SUM(FAuxQtyConsume) AS FAuxQtyConsume-获取累计预计耗用（一张单据存在多张相同任务单的时候）INTO #SUMItemConsumeFROM #ItemConsume GROUP BY FICMOInterID, FICMOEntryID, FPPBOMInterID,FPPBOMEntryID,FProductID,FItemIDORDER BY FProductID,FPPBOMInterID,FPPBOMEntryIDUPDATE u SET FQtyConsume=CASE WHEN ABS(u1.FAccumulateQtyConsume-u1.FPPBOMQtyConsume-u.FQtyConsume) CONVERT(DECIMAL(23,10),DeviationPermmited*1.0/POWER(convert(decimal(23,10),10),ISNULL(t.FQtyDecimal,0) THEN (u1.FAccumulateQtyConsume-u1.FPPBOMQtyConsume) ELSE u.FQtyConsume END ,FAuxQtyConsume=CASE WHEN ABS(u1.FAccumulateAuxQtyConsume-u1.FPPBOMAuxQtyConsume-u.FAuxQtyConsume) CONVERT(DECIMAL(23,10),DeviationPermmited*1.0/POWER(convert(decimal(23,10),10),ISNULL(t.FQtyDecimal,0) THEN (u1.FAccumulateAuxQtyConsume-u1.FPPBOMAuxQtyConsume) ELSE u.FAuxQtyConsume ENDFROM #SUMItemConsume uINNER JOIN ( SELECT DISTINCT FPPBOMInterID,FPPBOMEntryID,FAccumulateQtyConsume,FAccumulateAuxQtyConsume,FPPBOMQtyConsume,FPPBOMAuxQtyConsume FROM #ItemConsume) u1 ON u1.FPPBOMInterID=u.FPPBOMInterID AND u1.FPPBOMEntryID=u.FPPBOMEntryIDINNER JOIN t_ICItemBase t ON t.FItemID=u.FItemIDSELECT Loop=1,MaxSize=COUNT(FPPBOMInterID) FROM #SUMItemConsumeWHILE Loop=MaxSizeBEGIN SELECT PPBOMInterID=FPPBOMInterID,PPBOMEntryID=FPPBOMEntryID,ICMOInterID = FICMOInterID, ICMOEntryID = FICMOEntryID,SumConsume=FQtyConsume,SumAuxConsume=FAuxQtyConsume,ProductID=FProductID FROM #SUMItemConsume WHERE FIndex=Loop UPDATE u SET SumConsume1=FQtyConsume=CASE WHEN ABS(SumConsume-u.FQtyConsume)CONVERT(DECIMAL(23,10),DeviationPermmited*1.0/POWER(convert(decimal(23,10),10),ISNULL(t.FQtyDecimal,0) THEN ROUND(SumConsume,t.FQtyDecimal) ELSE ROUND(u.FQtyConsume,t.FQtyDecimal) END ,SumAuxConsume1=FAuxQtyConsume=CASE WHEN ABS(SumAuxConsume-u.FAuxQtyConsume)CONVERT(DECIMAL(23,10),DeviationPermmited*1.0/POWER(convert(decimal(23,10),10),ISNULL(t.FQtyDecimal,0) THEN ROUND(SumAuxConsume,t.FQtyDecimal) ELSE ROUND(u.FAuxQtyConsume,t.FQtyDecimal) END ,SumConsume=SumConsume-CASE WHEN ABS(SumConsume-u.FQtyConsume)CONVERT(DECIMAL(23,10),DeviationPermmited*1.0/POWER(convert(decimal(23,10),10),ISNULL(t.FQtyDecimal,0) THEN ROUND(SumConsume,t.FQtyDecimal) ELSE ROUND(u.FQtyConsume,t.FQtyDecimal) END ,SumAuxConsume=SumAuxConsume-CASE WHEN ABS(SumAuxConsume-u.FAuxQtyConsume)0BEGIN EXEC GetICMaxNum ICSh