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

1、 -委外加工审核未成功的问题解决办法,-可能原因是表 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_ItemCon

2、sume) 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 #Data797A9

3、0FE46DE4D3DB0887B4794F98159 table(FOrderInterID int , FOrderEntryID INT ,FItemID INT, FStockQty Decimal(28,10),FSecStockQty DECIMAL(28,10)Insert into #Data797A90FE46DE4D3DB0887B4794F98159SELECT FOrderInterID,FOrderEntryID,FItemID,SUM(ISNULL(u2.FQty,0) as FStockQty,SUM(ISNULL(u2.FSecQty,0) AS FSecSto

4、ckQty FROM ICStockBillEntry u2 WHERE u2.FICMoInterID = 0 AND u2.FOrderInterID0 AND u2.FInterID= 213494GROUP BY FOrderInterID,FOrderEntryID,FItemID IF (select count(*) from #Data797A90FE46DE4D3DB0887B4794F98159 ) 0 UPDATE u1 SETu1.FStockQty=ISNULL(u1.FStockQty,0)+ m2.FStockQty,u1.FAuxStockQty=ISNULL(

5、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.FItemIDINNER

6、JOIN t_MeasureUnit t2 ON u1.FUnitID=t2.FMeasureUnitID UPDATE u1 SETu1.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.FSecBackQtyFROM ICSubContractEntry u1 INNE

7、R 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

8、and u2.FPPBOMEntryID0 and u2.FInterID= 213494GROUP BY u2.FICMOInterID,u2.FPPBOMEntryID,u2.FItemIDIF (select count(*) from #ICSTOCK797A90FE46DE4D3DB0887B4794F98159 ) 0 UPDATE u1 SETu1.FStockQty=ISNULL(u1.FStockQty,0)+ ISNULL(m2.FStockQty,0) ,u1.FAuxStockQty=ISNULL(u1.FAuxStockQty,0)+ISNULL(ROUND(ISNU

9、LL(cast(m2.FStockQty as float),0) /cast(t2.FCoEfficient as float),t1.FQtyDecimal),0)FROM PPBOMEntry u1 INNER JOIN #ICSTOCK797A90FE46DE4D3DB0887B4794F98159m2 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

10、 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

11、 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

12、)-本次入库数量，如果是等级品需要换算成产成品,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-

13、源单分录,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,

14、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 I

15、NT 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

16、(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(

17、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.FCoefficie

18、nt,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

19、.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

20、=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-更新单据编号Up

21、date 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

22、 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 uIN

23、NER 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 #StockB

24、ill 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 FICMOI

25、nterID,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,FQtyCons

26、ume,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-基本单位预计

27、耗用,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.FStockQ

28、ty,1),t.FQtyDecimal)ELSE u1.FROB*ABS(ROUND(u.FQtyMust-u.FQtyConsume)*(CASE WHEN (st.FPlanProductQty-st.FAccumulateStockQty+st.FStockQty)=0 THEN 1ELSE u1.FQty/(st.FPlanProductQty-st.FAccumulateStockQty+st.FStockQty)END),t.FQtyDecimal)END)END-预计耗用,CASE WHEN DeductMode=0 THEN ROUND(u.FAuxQtyMust*u1.FQt

29、y/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.FQtyM

30、ust-u.FQtyConsume)*(CASE WHEN (st.FPlanProductQty-st.FAccumulateStockQty+st.FStockQty)=0 THEN 1ELSE 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.FQ

31、tyMust*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) -计划

32、用量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.F

33、ItemID=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

34、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

35、.FQty AS FStockQty,bl.FPPBOMInterID,bl.FPPBOMEntryID,FQtyConsume,FAuxQtyConsumeFROM ICShop_ItemConsumeEntry blINNER JOIN ICShop_ItemConsume bv ON bv.FInterID=bl.FInterIDINNER JOIN ICStockBillEntry sun ON sun.FInterID=bv.FSourceInterID AND sun.FEntryID=bv.FSourceEntryID INNER JOIN #SourceBill sb ON s

36、b.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=5

37、WHERE 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 A

38、S 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 Devi

39、ationPermmited=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, FPPBOM

40、InterID,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,FPPBOMEn

41、tryIDUPDATE u SET FQtyConsume=CASE WHEN ABS(u1.FAccumulateQtyConsume-u1.FPPBOMQtyConsume-u.FQtyConsume)0BEGINEXEC GetICMaxNum ICShop_ItemConsume,InterID OUTPUT,Increase-获取单据内码SELECT TOP 1 SourceInterID=FSourceInterID,SourceEntryID=FSourceEntryID FROM #ItemConsumeUPDATE #ItemConsume SET InterID=CASE

42、WHEN (SourceInterID=FSourceInterID AND SourceEntryID=FSourceEntryID) THEN InterID ELSE InterID+1 END,SourceInterID=FSourceInterID,SourceEntryID=FSourceEntryID,FInterID=InterID-记录对应的内码和材料耗用的内码SELECT TOP 1 EntryID=0,InterID=FInterID FROM #ItemConsumeUPDATE #ItemConsume SET EntryID=CASE WHEN InterID=FI

43、nterID THEN EntryID+1 ELSE 1 END,InterID=FInterID,FEntryID=EntryID-记录对应的内码和材料耗用的内码END-批量生成材料耗用记录INSERT INTO ICShop_ItemConsume(FInterID,FClassTypeID,FBillNO,FSourceInterID,FSourceEntryID,FSourceBillNO,FSourceTranType,FICMOInterID,FICMOEntryID,FItemID,FPPBOMInterID,FPPBOMBillNO,FPPBOMTranType,FIsAuto

44、Gen,FWIPDeductMode,FBillerID,FDate)SELECT DISTINCT u.FInterID,1002525,u1.FItemConsumeBillNO,u.FSourceInterID,u.FSourceEntryID,u1.FBillNO,u1.FTranType,u.FICMOInterID,u.FICMOEntryID,u.FProductID,u.FPPBOMInterID,u.FPPBOMBillNO,88,1,0,16407,CONVERT(NVARCHAR(10),GETDATE(),121)FROM #ItemConsume uINNER JOI

45、N #SourceBill u1 ON u1.FInterID=u.FSourceInterID AND u1.FEntryID=u.FSourceEntryID-批量生成材料耗用表体记录INSERT INTO ICShop_ItemConsumeEntry(FInterID,FIndex,FICMOInterID,FICMOEntryID,FPPBOMInterID,FPPBOMEntryID,FItemID,FUnitID,FQtyConsume,FAuxQtyConsume,FAuxQtyMust,FQtyMust,FOperSN,FOperID)SELECT DISTINCT u.FI

46、nterID,u.FEntryID,u.FICMOInterID,u.FICMOEntryID,u.FPPBOMInterID,u.FPPBOMEntryID,u.FItemID,p.FUnitID,u.FQtyConsume,u.FAuxQtyConsume,u.FAuxQtyMust,u.FQtyMust,u.FOperSN,u.FOperIDFROM #ItemConsume uINNER JOIN #SourceBill u1 ON u1.FInterID=u.FSourceInterID AND u1.FEntryID=u.FSourceEntryIDINNER JOIN PPBOM

47、Entry p ON p.FInterID=u.FPPBOMInterID AND p.FEntryID=u.FPPBOMEntryIDORDER BY u.FInterID,u.FEntryID-更新投料单UPDATE u SET FQtyConsume=u.FQtyConsume+(1*s.FQtyConsume),FAuxQtyConsume=u.FAuxQtyConsume+(1*s.FAuxQtyConsume),FWIPQty=u.FWIPQty-(1*s.FQtyConsume)-更新WIP,FWIPAuxQty=u.FWIPAuxQty-(1*s.FAuxQtyConsume)

48、FROM PPBOMEntry uINNER JOIN (SELECT FPPBOMInterID,FPPBOMEntryID,SUM(FQtyConsume) AS FQtyConsume,SUM(FAuxQtyConsume) AS FAuxQtyConsumeFROM #ItemConsume GROUP BY FPPBOMInterID,FPPBOMEntryID) s ON s.FPPBOMInterID=u.FInterID AND s.FPPBOMEntryID=u.FEntryIDEND-删除临时表DROP TABLE #SourceBillDROP TABLE #ItemConsumeDROP TABLE #StockBill运行以上代码 委外入审核后的更新动作存储过程-可检查问题，如下图：所以有第一段说明