ALTER PROCEDURE [dbo].[PRO_SuoDingkuCun_init]
(
@sBillNo NVARCHAR(50)=''
)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--DECLARE @sBillNo NVARCHAR(50)='SMC230811005' --exec PRO_SuoDingkuCun_init 'SMC230811005'
IF OBJECT_ID(N'tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP;
DELETE FROM SuoDingkuCun WHERE sBillNo=@sBillNo;
SELECT
ROW_NUMBER() over(ORDER by A.sBillNo,A.sOrderNo,b.sStyleNo,b.sMaterialCode,b.sRealColorNo) as XH,
A.sBillNo,A.sOrderNo,b.sStyleNo,b.sMaterialCode, B.sMaterialName,b.sRealColorNo,b.uGUID AS BuGUID,
ISNULL(b.nQty,0)-ISNULL(B.nTotalReplaceableQty,0) AS nQty
INTO #TEMP
FROM pbCommonStoreMaterialCheckMst A WITH(NOLOCK)
INNER JOIN dbo.pbCommonStoreMaterialCheckDtl B WITH(NOLOCK) ON A.uGUID=B.uHdrGUID
WHERE A.sBillNo = @sBillNo
ORDER by A.sBillNo,A.sOrderNo,b.sStyleNo,b.sMaterialCode,b.sRealColorNo
UPDATE b SET b.nactivateStock=(
SELECT
/*这是库存数量减去 占用的数量 =可用数量 */
ISNULL(SUM(A.nStockQty),0)
-ISNULL((SELECT SUM(bb.nStockQtylock) FROM SuoDingkuCun BB (NOLOCK)
WHERE bb.sMaterialCode=a.sMaterialNo AND bb.sRealColorNo=a.sRealColorNo AND bb.sBillNo<>@sBillNo ),0) AS nStockQty
FROM dbo.vwmmFPInStore A(NOLOCK) WHERE A.iStoreInStatus=1 AND A.sStoreName='总部面料仓'
AND A.sMaterialNo = B.sMaterialCode AND A.sRealColorNo = B.sRealColorNo
GROUP BY a.sRealColorNo,a.sMaterialNo
HAVING ISNULL(SUM(A.nStockQty),0) -ISNULL((SELECT SUM(bb.nStockQtylock)
FROM SuoDingkuCun BB (NOLOCK)
WHERE bb.sMaterialCode=a.sMaterialNo
AND bb.sRealColorNo=a.sRealColorNo
AND bb.sBillNo<>@sBillNo ),0)
>0
), B.nzhanyongshu=NULL,B.nLackQty=0
FROM pbCommonStoreMaterialCheckMst A WITH(NOLOCK) INNER JOIN dbo.pbCommonStoreMaterialCheckDtl B WITH(NOLOCK) ON A.uGUID=B.uHdrGUID
WHERE A.sBillNo =@sBillNo
DECLARE @i INT, @j INT
DECLARE @nQty NUMERIC(18,3)--需求数量
DECLARE @sMaterialCode NVARCHAR(50)='',@sRealColorNo NVARCHAR(50)='',@nStockQty NUMERIC(18,3),@nStockQtylock NUMERIC(18,3);
DECLARE @uGUIDStore UNIQUEIDENTIFIER ,@BuGUID UNIQUEIDENTIFIER ;
DECLARE @sOrderNo NVARCHAR(50)='',@sStyleNo NVARCHAR(50)='',@sMaterialName NVARCHAR(50);
DECLARE @ii INT, @jj INT;
SELECT @i=MIN(xh),@j=MAX(xh)FROM #TEMP;
WHILE @i<=@j
BEGIN
SELECT @sMaterialCode=sMaterialCode,@sRealColorNo=sRealColorNo,@nQty=nQty,@sOrderNo=sOrderNo,@sStyleNo=sStyleNo,@sMaterialName=sMaterialName,@BuGUID=BuGUID FROM #TEMP WHERE xh=@i;
IF OBJECT_ID(N'tempdb..#vwmmFPInStore') IS NOT NULL DROP TABLE #vwmmFPInStore;
SELECT a.uGUID,ROW_NUMBER() over(ORDER by a.uGUID,a.nStockQty) as newxh,
a.nStockQty-ISNULL((SELECT SUM(bb.nStockQtylock) FROM SuoDingkuCun bb(NOLOCK) WHERE bb.uGUID=A.uGUID),0) AS nStockQty
INTO #vwmmFPInStore
FROM dbo.vwmmFPInStore A(NOLOCK)
WHERE A.iStoreInStatus=1 AND A.sStoreName='总部面料仓' AND sMaterialNo=@sMaterialCode AND sRealColorNo=@sRealColorNo
AND a.nStockQty-ISNULL((SELECT SUM(bb.nStockQtylock) FROM SuoDingkuCun bb(NOLOCK) WHERE bb.uGUID=A.uGUID),0)>0
SELECT @ii=MIN(newxh),@jj=MAX(newxh)FROM #vwmmFPInStore;
WHILE @ii<=@jj
BEGIN
SELECT @nStockQty=nStockQty,@uGUIDStore=uGUID FROM #vwmmFPInStore WHERE newxh=@ii;
IF @nQty>0
BEGIN
IF @nQty>=@nStockQty -- 需求大于等库存
BEGIN
SET @nStockQtylock=@nStockQty
INSERT INTO SuoDingkuCun(XH,sBillNo,sOrderNo,sStyleNo,sMaterialCode,sMaterialName,sRealColorNo,nStockQtylock,nStockQty,uGUID,nQty)
SELECT 1,@sBillNo,@sOrderNo,@sStyleNo,@sMaterialCode,@sMaterialName,@sRealColorNo,@nStockQtylock,@nStockQty,@uGUIDStore,@nQty
UPDATE pbCommonStoreMaterialCheckDtl SET nzhanyongshu=ISNULL(nzhanyongshu,0)+ @nStockQtylock WHERE @BuGUID=uGUID
set @nQty=@nQty-@nStockQty
END
else
BEGIN
SET @nStockQtylock=@nQty --库存大于需求
INSERT INTO SuoDingkuCun(XH,sBillNo,sOrderNo,sStyleNo,sMaterialCode,sMaterialName,sRealColorNo,nStockQtylock,nStockQty,uGUID,nQty)
SELECT 1,@sBillNo,@sOrderNo,@sStyleNo,@sMaterialCode,@sMaterialName,@sRealColorNo,@nStockQtylock,@nStockQty,@uGUIDStore,@nQty
UPDATE pbCommonStoreMaterialCheckDtl SET nzhanyongshu=ISNULL(nzhanyongshu,0)+ @nStockQtylock WHERE @BuGUID=uGUID
SET @nQty=@nQty-@nStockQty;
END;
END;
SET @ii=@ii+1;
END;
SET @i=@i+1;
END;
DROP TABLE #TEMP
UPDATE b SET B.nLackQty=ABS(ISNULL(B.nzhanyongshu,0)-ISNULL(b.nQty,0))
FROM pbCommonStoreMaterialCheckMst A WITH(NOLOCK)
INNER JOIN dbo.pbCommonStoreMaterialCheckDtl B ON A.uGUID=B.uHdrGUID
WHERE A.sBillNo = @sBillNo AND ISNULL(B.nzhanyongshu,0)-ISNULL(b.nQty,0)<0
COMMIT TRAN
RETURN
END TRY
BEGIN CATCH
ROLLBACK TRAN
RETURN
END CATCH
END;