委外加工入庫(kù)審核未成功解決辦法

1、 -委外加工審核未成功的問題解決辦法,-可能原因是表 ICShop_ItemConsume 中FinterID的最大值問題，由于中間有表刪除，一些流水號(hào)丟失，所以更新下最大值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 ICSh

2、op_ItemConsume) where FTableName like 'ICShop_ItemConsume'以下代碼為其中一列報(bào)錯(cuò)中抓取的過程-Declare FSouceTrantype as int Declare WIPDeductMode as Decimal(28,10) -在制品扣減方式 Declare bWriteMethod as Int -是否按標(biāo)準(zhǔn)數(shù)量 Declare dRate as Decimal(28,10) -換算率 Declare FWIPQty as Decimal(28,10) -在制品數(shù)量 Declare FICMOInterID a

3、s 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

4、(u2.FSecQty,0) AS FSecStockQty FROM ICStockBillEntry u2 WHERE u2.FICMoInterID = 0 AND u2.FOrderInterID>0 AND u2.FInterID= 213494 GROUP BY FOrderInterID,FOrderEntryID,FItemID IF (select count(*) from #Data797A90FE46DE4D3DB0887B4794F98159 ) >0 UPDATE u1 SET u1.FStockQty=ISNULL(u1.FStockQty,0)+ m

5、2.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.FOrderE

6、ntryID 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.

7、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.FOrderInterID>0 AND u2.FSourc

8、eTranType = 73 AND u2.FQty< 0 AND u2.FInterID= 213494 GROUP BY FOrderInterID,FOrderEntryID,FItemID) 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 Declare #ICSTOCK797A90FE46DE4D3DB0887B479

9、4F98159 table (FICMOInterID int ,FPPBOMEntryID int ,FItemID int ,FStockQty decimal(28,10)Insert into #ICSTOCK797A90FE46DE4D3DB0887B4794F98159 SELECT u2.FICMOInterID,u2.FPPBOMEntryID,u2.FItemID,SUM(ISNULL(u2.FQty,0) AS FStockQty FROM ICStockBillEntry u2 WHERE u2.FOrderInterID>0 AND FICMOInterID &g

10、t; 0 and u2.FPPBOMEntryID>0 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)+

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

12、mID=t1.FItemID INNER JOIN t_MeasureUnit t2 ON u1.FUnitID=t2.FMeasureUnitID WHERE u1.FMaterielType IN (372,373,374) -聯(lián)副產(chǎn)品等級(jí)品件 SET NOCOUNT ONDECLARE DeductPoint AS INT,DeductMode AS INTSET DeductMode=0SET DeductPoint=0-構(gòu)建臨時(shí)表 記錄源單信息，如入庫(kù)單、任務(wù)單匯報(bào)單CREATE TABLE #SourceBill( FInterID INT-源單內(nèi)碼 ,FEntryID INT D

13、EFAULT -1-源單分錄 ,FItemID INT -物料 ,FICMOInterID INT-任務(wù)單或委外訂單的內(nèi)碼 ,FICMOEntryID INT DEFAULT 0-委外訂單的分錄 ,FMOCoefficient DECIMAL(23,10) DEFAULT 1-任務(wù)單單位的換算率 ,FPPBOMEntryID INT DEFAULT 0-投料單的分錄 ,FSourceInterID INT DEFAULT 0-源單內(nèi)碼（藍(lán)字推紅字） ,FSourceEntryID INT DEFAULT 0-源單分錄（藍(lán)字推紅字） ,FSourceTranType INT DEFAULT 0-

14、源單類型（藍(lán)字推紅字） ,FQty DECIMAL(23,10)-本次入庫(kù)數(shù)量，如果是等級(jí)品需要換算成產(chǎn)成品 ,FROB INT DEFAULT 1-紅藍(lán)字標(biāo)記 ,FBillNO NVARCHAR(255)-源單編號(hào) ,FTranType INT DEFAULT -1-源單類型 ,FItemConsumeBillNO NVARCHAR(255) DEFAULT ''-材料耗用編號(hào) ,FDeleted INT DEFAULT 0-刪除單據(jù) )CREATE TABLE #ItemConsume( FInterID INT DEFAULT -1 ,FEntryID INT DEFAU

15、LT -1 ,FSourceInterID INT-源單內(nèi)碼 ,FSourceEntryID INT-源單分錄 ,FICMOInterID INT-任務(wù)單內(nèi)碼 ,FICMOEntryID INT DEFAULT 0-委外訂單分錄 ,FProductID INT-產(chǎn)品ID ,FPPBOMBillNO NVARCHAR(255)-投料單編號(hào) ,FPPBOMInterID INT-投料單內(nèi)碼 ,FPPBOMEntryID INT-投料單分錄 ,FItemID INT-原材料分錄 ,FUnitID INT-單位 ,FQtyConsume DECIMAL(23,10)-材料耗用 ,FAuxQtyCons

16、ume DECIMAL(23,10)-常用單位材料耗用 ,FAccumulateQtyConsume DECIMAL(23,10)-累計(jì)耗用 根據(jù)總的任務(wù)單入庫(kù)或匯報(bào)信息計(jì)算所得 ,FAccumulateAuxQtyConsume DECIMAL(23,10)-常用單位累計(jì)耗用 根據(jù)總的任務(wù)單入庫(kù)或匯報(bào)信息計(jì)算所得 ,FPPBOMQtyConsume DECIMAL(23,10)-投料單上的累積耗用 ,FPPBOMAuxQtyConsume DECIMAL(23,10)-投料單上的常用單位累積耗用 ,FCancellation INT DEFAULT 0-作廢單據(jù) ,FAuxQtyMust D

17、ECIMAL(23,10) -常用單位應(yīng)發(fā)數(shù)量 ,FQtyMust DECIMAL(23,10) -基本單位應(yīng)發(fā)數(shù)量 ,FOperSN INT DEFAULT(0) -工序號(hào) ,FOperID INT DEFAULT(0) -工序 )CREATE TABLE #StockBill( FInterID INT-入庫(kù)單內(nèi)碼 ,FICMOInterID INT-任務(wù)單內(nèi)碼 ,FICMOEntryID INT DEFAULT 0-委外訂單分錄 ,FStockQty Decimal(23,10)-本次入庫(kù)數(shù)量 ,FAccumulateStockQty Decimal(23,10) DEFAULT 0-累

18、計(jì)入庫(kù)數(shù)量 ,FPlanProductQty Decimal(23,10) DEFAULT 0-預(yù)計(jì)生產(chǎn)數(shù)量數(shù)量 ,FQtyFinish DECIMAL(23,10)-任務(wù)單實(shí)作數(shù) )INSERT INTO #SourceBill(FInterID,FTranType,FDeleted)VALUES(213494,5,0)-更新FTranType及FBillNO相關(guān)信息-更新FTranType及FBillNO相關(guān)信息UPDATE t SET FBillNO=v.FBillNO,FTranType=v.FTranType,FROB=v.FRobFROM #SourceBill tINNER JO

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

20、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 #Sou

21、rceBill 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.FPP

22、BOMEntryID AND u1.FMaterielType=374-等級(jí)品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)-聯(lián)副產(chǎn)品不考慮WHERE s.FEntryID=-1 AND (u.FPPBOMEntryID=0 OR (u.FPPBOMEntryID>0 AND u1.FInterID IS NOT N

23、ULL)-主產(chǎn)品或等級(jí)品 AND u2.FInterID IS NULLDELETE FROM #SourceBill WHERE FEntryID=-1-更新單據(jù)編號(hào)Update s SET FItemConsumeBillNO='MCR133337'FROM #SourceBill sINNER JOIN (SELECT TOP 1 FInterID,FEntryID FROM #SourceBill WHERE FItemConsumeBillNO='') st ON st.FInterID=s.FInterID AND st.FEntryID=s.FEn

24、tryIDUpdate s SET FItemConsumeBillNO='MCR133338'FROM #SourceBill sINNER JOIN (SELECT TOP 1 FInterID,FEntryID FROM #SourceBill WHERE FItemConsumeBillNO='') st ON st.FInterID=s.FInterID AND st.FEntryID=s.FEntryID-獲取本次各個(gè)入庫(kù)單的累積入庫(kù)數(shù)量，等級(jí)品按單位用量換算成產(chǎn)成品INSERT INTO #StockBill(FInterID,FICMOInter

25、ID,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-更新委外訂單的產(chǎn)量信息和已經(jīng)生產(chǎn)信息的信息(主要處理等級(jí)品的問題)UPDATE d SET

26、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 FICM

27、OInterID,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,

28、FICMOEntryID,FProductID,FPPBOMBillNO,FPPBOMInterID,FPPBOMEntryID,FItemID,FUnitID ,FPPBOMQtyConsume,FPPBOMAuxQtyConsume ,FQtyConsume ,FAuxQtyConsume ,FAccumulateQtyConsume ,FAccumulateAuxQtyConsume ,FQtyMust ,FAuxQtyMust ,FOperSN -工序號(hào) ,FOperID -工序代碼 )-委外加工入庫(kù)單SELECT u1.FInterID,u1.FEntryID,u.FICMOInte

29、rID,u.FOrderEntryID AS FICMOEntryID,u1.FItemID,v.FBillNO,u.FInterID,u.FEntryID,u.FItemID,u.FUnitID ,u.FQtyConsume,u.FAuxQtyConsume -基本單位預(yù)計(jì)耗用 ,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.

30、FQtyDecimal+1) ELSE (CASE WHEN (u1.FTranType=5 AND u1.FROB=-1) -藍(lán)字下推紅字,用紅字的分?jǐn)偹{(lán)字耗用 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.FQt

31、y/(st.FPlanProductQty-st.FAccumulateStockQty+st.FStockQty) END),t.FQtyDecimal) END) END -預(yù)計(jì)耗用 ,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.FTranT

32、ype=5 AND u1.FROB=-1) -藍(lán)字下推紅字,用紅字的分?jǐn)偹{(lán)字耗用 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.FAccumulateStock

33、Qty+st.FStockQty) END)/(CASE WHEN tm.FCoefficient=0 THEN 1 ELSE tm.FCoefficient END),t.FQtyDecimal) END) END -基本單位累計(jì)耗用 ,CASE WHEN DeductMode=0 THEN ROUND(u.FQtyMust*st.FAccumulateStockQty/st.FPlanProductQty,t.FQtyDecimal) -計(jì)劃用量 WHEN DeductMode=1 THEN ROUND(st.FAccumulateStockQty*u.FQtyScrap/u1.FMOCo

34、efficient,t.FQtyDecimal) -標(biāo)準(zhǔn)用量 ELSE 0 END-剩余用量 -累計(jì)耗用 ,CASE WHEN DeductMode=0 THEN ROUND(u.FAuxQtyMust*st.FAccumulateStockQty/st.FPlanProductQty,t.FQtyDecimal) -計(jì)劃用量 WHEN DeductMode=1 THEN ROUND(st.FAccumulateStockQty*u.FAuxQtyScrap/u1.FMOCoefficient,t.FQtyDecimal)-標(biāo)準(zhǔn)用量 ELSE 0 END-剩余用量 -基本單位應(yīng)發(fā)數(shù)量=基本單位

35、預(yù)計(jì)耗用 后面統(tǒng)一更新 3.1 ,-1 AS FQtyMust -應(yīng)發(fā)數(shù)量=預(yù)計(jì)耗用 后面統(tǒng)一更新 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-投料單使用物料，為了獲取基本計(jì)量單位INNER JOIN t_MeasureUnit tm ON tm.FMeasureUnitID=u.FUnitID-投料單使用的單位INNER JOIN #StockBill st ON

36、st.FICMOInterID=u.FICMOInterID AND u.FOrderEntryID=st.FICMOEntryID-獲取該入庫(kù)單的上累計(jì)入庫(kù)信息（主要是為了考慮等級(jí)品等信息，所以不直接獲取任務(wù)單信息）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.FSourceI

37、nterID=u1.FInterID AND cn.FSourceEntryID=u1.FEntryID AND cn.FSourceTranType=5-不更新用戶手工調(diào)整的數(shù)據(jù)LEFT JOIN (-獲取藍(lán)字單據(jù)及當(dāng)初耗用信息 SELECT bv.FSourceTranType,sun.FInterID,sun.FEntryID,sun.FQty AS FStockQty,bl.FPPBOMInterID,bl.FPPBOMEntryID,FQtyConsume,FAuxQtyConsume FROM ICShop_ItemConsumeEntry bl INNER JOIN ICShop

38、_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-當(dāng)前紅字單據(jù) ) B ON B.FInte

39、rID=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.FQtyMust>0 AND cn.FInterID IS NULLORDER BY u1.FInterID,u1.FEntryID,u.

40、FInterID,u.FEntryID-按入庫(kù)（匯報(bào)）、分錄、投料單、投料單分錄排序-統(tǒng)一更新處3.1UPDATE #ItemConsume SET FQtyMust=FQtyConsume,FAuxQtyMust=FAuxQtyConsumeIF RowCount<>0-沒有需要生成的耗用記錄BEGINDECLARE DeviationPermmited AS DECIMAL(23,10)-獲取偏差值DECLARE PPBOMInterID AS INT,PPBOMEntryID AS INT,ICMOInterID AS INT,ICMOEntryID AS INT,Produ

41、ctID 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

42、DeviationPermmited=CONVERT(DECIMAL(23,10),1.0/POWER(convert(decimal(23,10),10),ISNULL(DeviationPermmited,0)-調(diào)整差異 這里使用的變量計(jì)算邏輯需要特別注意，請(qǐng)不要隨意修改SELECT IDENTITY(INT,1,1) AS FIndex,FICMOInterID, FICMOEntryID, FPPBOMInterID,FPPBOMEntryID,FProductID,FItemID,SUM(FQtyConsume) AS FQtyConsume,SUM(FAuxQtyConsume)

43、AS FAuxQtyConsume-獲取累計(jì)預(yù)計(jì)耗用（一張單據(jù)存在多張相同任務(wù)單的時(shí)候）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.FQ

44、tyConsume)< 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)< CONV

45、ERT(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,FAccumulateAuxQty

46、Consume,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=FP

47、PBOMInterID,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(DECIMA

48、L(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/POWE

49、R(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.FQtyDe

50、cimal,0) THEN ROUND(SumConsume,t.FQtyDecimal) ELSE ROUND(u.FQtyConsume,t.FQtyDecimal) END ,SumAuxConsume=SumAuxConsume-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.FQ

51、tyDecimal) ELSE ROUND(u.FAuxQtyConsume,t.FQtyDecimal) END FROM #ItemConsume u INNER JOIN t_ICItemBase t ON t.FItemID=u.FItemID WHERE u.FPPBOMInterID=FPPBOMInterID AND u.FPPBOMEntryID=PPBOMEntryID AND u.FICMOInterID = ICMOInterID AND u.FICMOEntryID = ICMOEntryID AND u.FProductID=ProductID SET Loop=Lo

52、op+1END-調(diào)整基本單位和常用單位的換算差異 統(tǒng)一按照常用單位換算基本單位的原則UPDATE d SET FQtyConsume=ROUND(ROUND(d.FAuxQtyConsume,t.FQtyDecimal)*tm.FCoefficient,t.FQtyDecimal)FROM #ItemConsume dINNER JOIN PPBOMEntry u ON u.FInterID=d.FPPBOMInterID AND u.FEntryID=d.FPPBOMEntryIDINNER JOIN t_ICItemBase t ON t.FItemID=u.FItemIDINNER JOIN t_MeasureUnit tm ON tm.FMeasureUnitID=u.FUnitIDDROP TABLE #SUMItemConsumeUPDATE #ItemConsume SET FQtyMust=FQtyConsume,FAuxQtyMust=FAuxQtyConsume-產(chǎn)生耗用記錄內(nèi)碼耗用記錄DECLARE InterID AS INT,EntryID AS INT,Increase AS INTDECLARE SourceInterID AS INT,SourceEntryID INTSELECT InterID=1001,Increase