K3WISE 投料单物料自动替代

K3 WISE 物料替代，根据替代关系、库存状况、实现自动替代，包含多替一

-- =========================================================
-- Author:		HUANGHAOYUN
-- Create date: 2020-07-30
-- Description:	委外订单入库时检查投料单子项物料是否需要替代
-- =========================================================
ALTER PROCEDURE HHY_PROC_AutoSubstitute
@PPBOMBillNo VARCHAR(20)--投料单编号
AS
BEGIN --1
	SET NOCOUNT ON;
	DECLARE @PPBOMinterId INT ,
			@HeadQty DECIMAL(13,4);
			
	SELECT @PPBOMinterId=FInterID ,@HeadQty=FAuxQty FROM PPBOM WHERE FBillNo =@PPBOMBillNo AND ISNULL(FCancellation,0)=0		
			
	--查询出库存小于应发数量的子项
	SELECT ROW_NUMBER() OVER ( ORDER BY B.FEntryID) ROWNUM,A.FInterID,A.FItemID ProductItem,A.FAuxQty,
		B.FEntryID,B.FItemID subItem,B.FAuxQtyScrap,B.FAuxQtyMust,C.FStockQty
	INTO #TempPPBOMEntry
	FROM PPBOM A INNER JOIN PPBOMEntry B ON A.FInterID =B.FInterID 
	LEFT JOIN (SELECT a.FItemID,SUM(ROUND(a.FQty, b.FQtydecimal)) AS FStockQty 
		FROM ICinventory a inner join t_ICItem b on a.FItemID=b.FItemID
		LEFT JOIN t_Stock C ON A.FStockID=C.FItemID 
		WHERE A.FStockID=507
		GROUP BY a.FItemID ,a.FStockID
	) C ON B.FItemID=C.FItemID
	WHERE A.FInterID =@PPBOMinterId
	AND C.FStockQty<B.FAuxQtyMust


	--检查子项物料库存物料不够的子项是否有替代关系，如果没有则提示错误
	DECLARE @MAXEntryId INT;--投料单最大分录号
	SELECT @MAXEntryId=MAX(FEntryID) FROM PPBOMEntry WHERE FInterID =@PPBOMInterId
	DECLARE @Index INT ; SET @Index=1;
	DECLARE @Count INT;
	DECLARE @ERROR VARCHAR(MAX);
	SELECT @Count=COUNT(*) FROM #TempPPBOMEntry
	SET @ERROR='';
	WHILE @Index<=@Count 
	BEGIN 
		IF NOT EXISTS(SELECT 1 FROM ICSubsItem_MainItems WHERE FItemID=(SELECT subItem FROM #TempPPBOMEntry WHERE ROWNUM=@Index))
		BEGIN
			SELECT @ERROR='物料:'+B.FNumber+CHAR(13)+CHAR(10) FROM #TempPPBOMEntry A INNER JOIN t_ICItem B ON A.subItem=B.FItemID WHERE ROWNUM=@Index
		END
		SELECT @Index=@Index+1
	END

	IF @ERROR<>''
	BEGIN
		SELECT @ERROR=@ERROR+'在系统不存在替代关系,请检查所有材料是否已完成入库操作';
		raiserror(@ERROR,18,18);
	END
	ELSE --如果有替代关系则进行替代更新投料单信息
	BEGIN --2
		SET @Index =1
		WHILE @Index<=@Count
		BEGIN --3
			DECLARE @FEntryId int,--投料单分录
					@FItemId int,--子项物料代码
					@STCOKQTY DECIMAL(13,4),--主料库存
					@AUXQTYMUST DECIMAL(13,4),--应发数量
					@AUXQTYMUSTSOR DECIMAL(13,4),--原始应发数量
					@FAuxQtyScrap DECIMAL(13,4)--单位用量
					
			SELECT @FEntryId=FEntryID,@FItemId=subItem,@STCOKQTY=ISNULL(FStockQty,0),@AUXQTYMUST=ISNULL(FAuxQtyMust,0),@AUXQTYMUSTSOR=ISNULL(FAuxQtyMust,0),@FAuxQtyScrap=ISNULL(FAuxQtyScrap,0) FROM #TempPPBOMEntry WHERE ROWNUM=@Index
			
			--拿出需要替代的料插入到临时表#PPBOMEntry，后面插入替代料时调用
			SELECT * INTO #PPBOMEntry FROM PPBOMEntry WHERE FInterID =@PPBOMinterId AND FEntryID=@