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

1、精品文档- 委外加工审核未成功的问题解决办法 ,- 可能原因是表 ICShop_ItemConsume 中FinterID 的最大值问题， 由于中间有表删除， 一些流水号丢失，所以更新下最大值select top 1 * from ICShop_ItemConsume order by FInterID descselect * from ICMaxNumwhere FTableName like 'ICShop_ItemConsume' - 保证最大值一致 , 如不是则更新update ICMaxNum set FMaxNum = (select max(finterid)

2、from ICShop_ItemConsume) where FTableName like 'ICShop_ItemConsume'以下代码为其中一列报错中抓取的过程-Declare FSouceTrantype as intDeclareWIPDeductModeas Decimal ( 28, 10) - 在制品扣减方式DeclarebWriteMethod as Int- 是否按标准数量DeclaredRate as Decimal ( 28,10) - 换算率DeclareFWIPQtyas Decimal ( 28, 10) - 在制品数量DeclareFICMOI

3、nterID as intDeclare#Data797A90FE46DE4D3DB0887B4794F98159table ( FOrderInterIDint,FOrderEntryIDINT , FItemIDINT,FStockQtyDecimal ( 28, 10), FSecStockQty DECIMAL( 28, 10)Insertinto#Data797A90FE46DE4D3DB0887B4794F98159SELECTFOrderInterID, FOrderEntryID , FItemID , SUM( ISNULL( u2. FQty, 0)asFStockQty

4、, SUM( ISNULL( u2. FSecQty, 0)AS FSecStockQtyFROMICStockBillEntryu2WHEREu2. FICMoInterID= 0ANDu2. FOrderInterID>0 ANDu2. FInterID= 213494GROUPBY FOrderInterID, FOrderEntryID , FItemIDIF ( selectcount (*)from#Data797A90FE46DE4D3DB0887B4794F98159) >0UPDATEu1 SETu1. FStockQty =ISNULL( u1. FStockQ

5、ty , 0)+m2. FStockQty ,u1. FAuxStockQty =ISNULL( u1. FAuxStockQty , 0)+m2. FStockQty / cast ( t2 . FCoEfficientas float),u1. FSecStockQty =ISNULL( u1. FSecStockQty, 0)+m2. FSecStockQtyFROMICSubContractEntryu1 INNERJOIN #Data797A90FE46DE4D3DB0887B4794F98159m2 onu1. FInterID =m2. FOrderInterIDANDu1. F

6、EntryID= m2. FOrderEntryIDANDu1. FItemID =m2. FItemIDINNER JOIN t_MeasureUnitt2ON u1. FUnitID =t2 . FMeasureUnitIDUPDATEu1 SETu1. FBCommitQty=ISNULL( u1. FBCommitQty, 0)+ m2. FBackQty,u1. FAuxBCommitQty=( ISNULL( u1. FBCommitQty, 0)+ m2. FBackQty)/ cast ( t2 . FCoEfficientas float),u1. FSecBCommitQt

7、y=ISNULL( u1. FSecBCommitQty, 0)+ m2. FSecBackQtyFROMICSubContractEntryu1 INNERJOIN( SELECTFOrderInterID, FOrderEntryID , FItemID , SUM( ISNULL( u2. FQty, 0)AS FBackQty ,SUM( ISNULL( u2. FAuxQty, 0)AS FAuxBackQty, SUM( ISNULL( u2. FSecQty, 0)AS FSecBackQty FROMICStockBillEntryu2WHEREu2. FICMoInterID

8、= 0 ANDu2. FOrderInterID>0 ANDu2. FSourceTranType = 73 ANDu2. FQty<0 ANDu2. FInterID = 213494GROUPBY FOrderInterID, FOrderEntryID , FItemID ) m2ONu1. FInterID =m2. FOrderInterIDANDu1. FEntryID= m2. FOrderEntryIDANDu1. FItemID =m2. FItemIDINNER JOIN t_MeasureUnitt2ON u1. FUnitID =t2 . FMeasureU

9、nitIDDeclare#ICSTOCK797A90FE46DE4D3DB0887B4794F98159table( FICMOInterIDint, FPPBOMEntryIDint, FItemIDint, FStockQtydecimal ( 28, 10)Insertinto#ICSTOCK797A90FE46DE4D3DB0887B4794F98159SELECTu2. FICMOInterID , u2. FPPBOMEntryID, u2. FItemID , SUM( ISNULL( u2. FQty, 0)AS FStockQtyFROMICStockBillEntryu2。

10、1欢迎下载精品文档WHEREu2. FOrderInterID>0 ANDFICMOInterID> 0 and u2. FPPBOMEntryID>0 andu2. FInterID =213494GROUPBY u2. FICMOInterID , u2. FPPBOMEntryID, u2. FItemIDIF ( selectcount (*)from#ICSTOCK797A90FE46DE4D3DB0887B4794F98159)>UPDATEu1 SETu1. FStockQty =ISNULL( u1. FStockQty , 0)+ISNULL( m2.

11、 FStockQty , 0),u1. FAuxStockQty =ISNULL( u1. FAuxStockQty , 0)+ ISNULL( ROUND(ISNULL( cast ( m2. FStockQtyas float ), 0)/ cast ( t2 . FCoEfficientas float), t1 . FQtyDecimal ), 0)FROMPPBOMEntryu1 INNER JOIN #ICSTOCK797A90FE46DE4D3DB0887B4794F98159m2ONu1. FItemID =m2. FItemID ANDu1. FEntryID =m2. FP

12、PBOMEntryIDANDu1. FInterID =m2. FICMOinterIDINNER JOIN t_ICItemt1ONm2. FItemID =t1 . FItemIDINNER JOIN t_MeasureUnitt2ON u1. FUnitID =t2 . FMeasureUnitIDWHEREu1. FMaterielTypeIN ( 372, 373, 374)- 联副产品等级品件SET NOCOUNTONDECLAREDeductPoint AS INT, DeductModeAS INTSET DeductMode=0SET DeductPoint =0- 构建临时

13、表 记录源单信息，如入库单、任务单汇报单CREATETABLE#SourceBill (FInterIDINT- 源单内码, FEntryID INT DEFAULT- 1- 源单分录, FItemID INT - 物料, FICMOInterID INT- 任务单或委外订单的内码, FICMOEntryID INT DEFAULT0 - 委外订单的分录, FMOCoefficientDECIMAL( 23, 10) DEFAULT1 - 任务单单位的换算率, FPPBOMEntryIDINT DEFAULT0 - 投料单的分录, FSourceInterIDINT DEFAULT0 - 源单

14、内码（蓝字推红字）, FSourceEntryIDINT DEFAULT0 - 源单分录（蓝字推红字）, FSourceTranType INT DEFAULT0 - 源单类型（蓝字推红字）, FQty DECIMAL( 23, 10) - 本次入库数量，如果是等级品需要换算成产成品, FROBINT DEFAULT1 - 红蓝字标记, FBillNONVARCHAR( 255) - 源单编号, FTranType INT DEFAULT- 1- 源单类型, FItemConsumeBillNO NVARCHAR( 255) DEFAULT'' - 材料耗用编号, FDelet

15、ed INT DEFAULT0 - 删除单据)CREATETABLE#ItemConsume(FInterIDINT DEFAULT- 1, FEntryIDINT DEFAULT- 1, FSourceInterIDINT - 源单内码, FSourceEntryID INT - 源单分录, FICMOInterID INT- 任务单内码, FICMOEntryID INT DEFAULT0 - 委外订单分录, FProductIDINT- 产品 ID, FPPBOMBillNONVARCHAR( 255) - 投料单编号, FPPBOMInterID INT- 投料单内码, FPPBOME

16、ntryIDINT- 投料单分录, FItemID INT- 原材料分录, FUnitID INT- 单位, FQtyConsumeDECIMAL( 23, 10) - 材料耗用, FAuxQtyConsumeDECIMAL( 23, 10) - 常用单位材料耗用。2欢迎下载精品文档, FAccumulateQtyConsume DECIMAL( 23, 10) - 累计耗用根据总的任务单入库或汇报信息计算所得, FAccumulateAuxQtyConsume DECIMAL( 23, 10) - 常用单位累计耗用根据总的任务单入库或汇报信息计算所得, FPPBOMQtyConsumeDEC

17、IMAL( 23, 10) - 投料单上的累积耗用, FPPBOMAuxQtyConsumeDECIMAL( 23, 10) - 投料单上的常用单位累积耗用, FCancellationINT DEFAULT0 - 作废单据, FAuxQtyMust DECIMAL( 23, 10)- 常用单位应发数量, FQtyMust DECIMAL( 23, 10)- 基本单位应发数量, FOperSN INT DEFAULT( 0) - 工序号, FOperID INT DEFAULT( 0) - 工序)CREATETABLE#StockBill(FInterIDINT- 入库单内码, FICMOIn

18、terIDINT- 任务单内码, FICMOEntryID INT DEFAULT0 - 委外订单分录, FStockQtyDecimal ( 23, 10) - 本次入库数量, FAccumulateStockQtyDecimal ( 23, 10) DEFAULT0 - 累计入库数量, FPlanProductQtyDecimal ( 23, 10)DEFAULT0 - 预计生产数量数量, FQtyFinishDECIMAL( 23, 10) - 任务单实作数)INSERT INTO #SourceBill( FInterID , FTranType, FDeleted )VALUES(

19、213494, 5, 0)- 更新 FTranType及 FBillNO 相关信息- 更新 FTranType及 FBillNO 相关信息UPDATEtSET FBillNO =v. FBillNO , FTranType=v. FTranType, FROB=v. FRobFROM#SourceBilltINNERJOIN ICStockBillv On t . FInterID =v. FInterID- 获取符合条件的分录信息INSERT INTO #SourceBill( FInterID , FEntryID , FItemID , FICMOInterID , FICMOEntry

20、ID, FPPBOMEntryID, FBillNO , FTranType, FROB, FMOCoefficient, FQty, FSourceTranType , FSourceInterID, FSourceEntryID , FDeleted )SELECTu. FInterID , u. FEntryID , u. FItemID , u. FOrderInterID, u. FOrderEntryID , u. FPPBOMEntryID, s. FBillNO , s. FTranType, s . FROB, ISNULL( tm. FCoefficient, 1), CA

21、SEWHENu. FPPBOMEntryID=0 THENu. FQty ELSEu. FQty* ISNULL( tm. FCoefficient, 1)/ ISNULL( u1. FQtyScrap, 1) ENDASFQty, u. FSourceTranType, u. FSourceInterID, u. FSourceEntryID , s. FDeletedFROMICStockBillEntryuINNERJOIN #SourceBillsON s. FInterID=u. FInterIDINNERJOIN ICSubContractEntrym ONm. FInterID

22、=u. FOrderInterID ANDm. FEntryID =u. FOrderEntryIDLEFT JOIN t_MeasureUnittm ON tm. FMeasureUnitID =m. FUnitIDLEFT JOIN PPBOMEntryu1ONu1. FICMOInterID =u. FOrderInterIDANDu1. FOrderEntryID =u. FOrderEntryIDANDu1. FEntryID =u. FPPBOMEntryIDANDu1. FMaterielType=374-等级品LEFT JOIN PPBOMEntryu2ONu2. FICMOI

23、nterID =u. FOrderInterIDANDu2. FOrderEntryID =u. FOrderEntryIDANDu2 . FEntryID =u. FPPBOMEntryIDANDu2. FMaterielTypeIN( 372, 373) - 联副产品不考虑WHEREs . FEntryID =- 1 AND( u. FPPBOMEntryID=0 OR ( u. FPPBOMEntryID>0 ANDu1. FInterID ISNOTNULL)- 主产品或等级品ANDu2. FInterIDISNULLDELETEFROM#SourceBillWHEREFEntr

24、yID =- 1。3欢迎下载精品文档- 更新单据编号Update s SET FItemConsumeBillNO ='MCR133337'FROM#SourceBillsINNERJOIN ( SELECTTOP1FInterID , FEntryIDFROM#SourceBillWHEREFItemConsumeBillNO='' )stON st . FInterID=s . FInterIDANDst . FEntryID =s. FEntryIDUpdate s SET FItemConsumeBillNO ='MCR133338'FR

25、OM#SourceBillsINNERJOIN ( SELECTTOP1FInterID , FEntryIDFROM#SourceBillWHEREFItemConsumeBillNO='' )stON st . FInterID=s . FInterIDANDst . FEntryID =s. FEntryID- 获取本次各个入库单的累积入库数量，等级品按单位用量换算成产成品INSERT INTO #StockBill ( FInterID , FICMOInterID , FICMOEntryID, FStockQty ) SELECTu. FInterID , u. F

26、OrderInterID , u. FOrderEntryID , SUM( u1. FQty) AS FStockQtyFROMICStockBillEntryuINNERJOIN #SourceBill u1 ONu1. FInterID =u. FInterID ANDu. FEntryID =u1. FEntryID GROUPBY u. FInterID , u. FOrderInterID , u. FOrderEntryID- 更新委外订单的产量信息和已经生产信息的信息 ( 主要处理等级品的问题 ) UPDATEd SETFAccumulateStockQty =m. FStoc

27、kQty +ISNULL( djp . FAccumulateStockQty , 0)* ISNULL( tm. FCoefficient , 1 ), FPlanProductQty =m. FQtyFROM#StockBilldINNERJOIN ICSubContractEntry m ONm. FInterID =d. FICMOInterID ANDm. FEntryID =d. FICMOEntryID LEFT JOIN t_MeasureUnit tm ON tm. FMeasureUnitID =m. FUnitIDLEFT JOIN ( SELECTFICMOInterI

28、D , FOrderEntryID , SUM( FStockQty / FQtyScrap) AS FAccumulateStockQty FROMPPBOMEntryWHEREFMaterielType =374 GROUPBY FICMOInterID , FOrderEntryID ) djp ON djp . FICMOInterID =d. FICMOInterID ANDdjp . FOrderEntryID =d. FICMOEntryID- 获取理论的耗用表INSERT INTO#ItemConsume( FSourceInterID , FSourceEntryID , F

29、ICMOInterID , FICMOEntryID, FProductID , FPPBOMBill NO, FPPBOMInterID, FPPBOMEntryID, FItemID , FUnitID, FPPBOMQtyConsume, FPPBOMAuxQtyConsume, FQtyConsume, FAuxQtyConsume, FAccumulateQtyConsume, FAccumulateAuxQtyConsume, FQtyMust, FAuxQtyMust, FOperSN - 工序号, FOperID - 工序代码)- 委外加工入库单SELECTu1. FInter

30、ID , u1. FEntryID , u. FICMOInterID , u. FOrderEntryID AS FICMOEntryID, u1. FItemID , v. FBillNO , u. FInterID , u. FEntryID , u. FItemID , u. FUnitID, u. FQtyConsume, u. FAuxQtyConsume - 基本单位预计耗用, CASEWHENDeductMode=0 THENROUND( u. FQtyMust*u1. FQty/ st . FPlanProductQty , t . FQtyDecimal +1)WHENDe

31、ductMode=1 THENROUND( u1. FQty* u. FQtyScrap/ u1. FMOCoefficient , t . FQtyDecimal +1)ELSE ( CASEWHEN( u1. FTranType=5 ANDu1. FROB=- 1)- 蓝字下推红字 , 用红字的分摊蓝字耗用THEN。4欢迎下载精品文档ROUND( ISNULL( B. FQtyConsume, 0)* u1. FQty/ ISNULL( B. FStockQty , 1), t . FQtyDecimal )ELSE u1. FROB* ABS( ROUND( u. FQtyMust- u

32、. FQtyConsume)*( CASEWHEN( st . FPlanProductQty - st . FAccumulateStockQty +st . FStockQty )= 0THEN1ELSEu1. FQty/( st . FPlanProductQty - st . FAccumulateStockQty +st . FStockQty )END), t . FQtyDecimal )END)END- 预计耗用, CASEWHENDeductMode=0 THENROUND( u. FAuxQtyMust* u1. FQty/ st . FPlanProductQty , t

33、 . FQtyDecimal +1)WHENDeductMode=1 THENROUND( u1. FQty* u. FAuxQtyScrap/ u1. FMOCoefficient , t . FQtyDecimal +1)ELSE ( CASEWHEN( u1. FTranType=5 ANDu1. FROB=- 1)- 蓝字下推红字 , 用红字的分摊蓝字耗用THENROUND( ISNULL( B. FQtyConsume, 0)* u1. FQty/ ISNULL( B. FStockQty , 1), t . FQtyDecimal )ELSE u1. FROB* ABS( ROUN

34、D( u. FQtyMust- u. FQtyConsume)*( CASEWHEN( st . FPlanProductQty - st . FAccumulateStockQty +st . FStockQty )= 0THEN1ELSEu1. FQty/( st . FPlanProductQty - st . FAccumulateStockQty +st . FStockQty )END)/( CASEWHENtm . FCoefficient=0 THEN1ELSEtm. FCoefficientEND), t . FQtyDecimal )END)END- 基本单位累计耗用, C

35、ASEWHENDeductMode=0 THENROUND( u. FQtyMust*st . FAccumulateStockQty / st . FPlanProductQty , t . FQtyDecimal ) - 计划用量 WHENDeductMode=1 THENROUND( st . FAccumulateStockQty *u. FQtyScrap / u1. FMOCoefficient , t . FQtyDecimal ) - 标准用量 ELSE0 END- 剩余用量- 累计耗用, CASEWHENDeductMode=0 THENROUND( u. FAuxQtyMu

36、st* st . FAccumulateStockQty / st . FPlanProductQty , t . FQtyDecimal ) - 计划用量 WHENDeductMode=1 THENROUND( st . FAccumulateStockQty *u. FAuxQtyScrap/ u1. FMOCoefficient , t . FQtyDecimal ) - 标准用量ELSE0 END- 剩余用量- 基本单位应发数量 =基本单位预计耗用后面统一更新3.1,- 1 AS FQtyMust- 应发数量 =预计耗用后面统一更新3.1,- 1 AS FAuxQtyMust, 0,

37、0FROMPPBOMEntryuINNERJOIN PPBOMv ON v. FInterID =u. FInterIDINNERJOIN t_ICItemBasetONt . FItemID =u. FItemID - 投料单使用物料，为了获取基本计量单位INNERJOIN t_MeasureUnittm ON tm. FMeasureUnitID =u. FUnitID - 投料单使用的单位INNERJOIN #StockBillstON st . FICMOInterID =u. FICMOInterIDANDu. FOrderEntryID =st . FICMOEntryID- 获取

38、该入库单的上累计入库信息（主要是为了考虑等级品等信息，所以不直接获取任务单信息）。5欢迎下载精品文档INNERJOIN #SourceBillu1 ONu1. FICMOInterID =st . FICMOInterIDANDu1. FICMOEntryID=u. FOrderEntryIDANDu1 . FInterID =st . FInterIDLEFT JOIN ICShop_ItemConsume cn ONcn. FIsAutoGen =0 ANDcn. FSourceInterID=u1. FInterIDANDcn. FSourceEntryID =u1. FEntryIDA

39、NDcn. FSourceTranType =5- 不更新用户手工调整的数据LEFT JOIN ( - 获取蓝字单据及当初耗用信息SELECTbv. FSourceTranType , sun . FInterID , sun. FEntryID , sun. FQty ASFStockQty , bl . FPPBOMInterID, bl . FPPBOMEntryID, FQtyConsume, FAuxQtyConsumeFROMICShop_ItemConsumeEntry blINNER JOIN ICShop_ItemConsume bv ON bv. FInterID =bl

40、. FInterIDINNER JOIN ICStockBillEntrysun ON sun. FInterID =bv. FSourceInterIDANDsun. FEntryID =bv. FSourceEntryIDINNER JOIN #SourceBillsb ON sb. FSourceInterID=sun. FInterIDANDsb. FSourceEntryID =sun. FEntryIDANDsb. FSourceTranType =2 ANDsb . FROB=- 1- 当前红字单据)B ON B. FInterID =u1. FSourceInterIDANDB

41、. FEntryID =u1. FSourceEntryIDANDB. FSourceTranType =u1. FTranType ANDB. FPPBOMInterID=u. FInterIDANDB. FPPBOMEntryID=u. FEntryIDANDB. FSourceTranType =5WHEREu. FMaterielType NOTIN ( 372, 373, 374, 376) ANDu. FQtyMust>0 ANDcn. FInterID IS NULL ORDERBY u1. FInterID , u1. FEntryID , u. FInterID , u

42、. FEntryID - 按入库（汇报）、分录、投料单、投料单分录排序- 统一更新处 3.1UPDATE#ItemConsume SET FQtyMust=FQtyConsume, FAuxQtyMust=FAuxQtyConsume IF RowCount<>0- 没有需要生成的耗用记录BEGINDECLAREDeviationPermmitedAS DECIMAL( 23, 10) - 获取偏差值DECLAREPPBOMInterIDAS INT, PPBOMEntryIDAS INT, ICMOInterID AS INT, ICMOEntryIDAS INT, ProductID AS INTDECLAREMaxSizeAS INT, Lo