深圳智汇创想科技有限公司

第一步：前提修复

1.修复主库存，可用和锁定为0的数据

DECLARE @CustomerId INT;
DECLARE @WarehouseId INT;
SET @CustomerId=1;
SET @WarehouseId=5312;

-- 处理锁定数为负数的和可用为负数的
-- UPDATE RB_WareHouse_Product SET GoodNum=0 WHERE CustomerID=@CustomerId AND WareHouseID=@WarehouseId AND GoodNum<0
-- UPDATE RB_WareHouse_Product SET LockNum=0 WHERE CustomerID=@CustomerId AND WareHouseID=@WarehouseId AND LockNum<0

DECLARE @CustomerId INT;
DECLARE @WarehouseId INT;
DECLARE @PackageAddTime DATETIME;
SET @CustomerId=1;
SET @WarehouseId=5312;
SET @PackageAddTime='2021-01-01' --包裹创建时间
 --修复有锁定详情无库位库存记录的数据
 --update wpl set wpl.CustomerID=-1 from RB_WareHouse_LockPositionDetail wpl
 --left join RB_WareHouse_OneProductMorePosition_Product wopp(nolock) on wpl.SKU=wopp.SKU and wpl.PositionID=wopp.PositionID and wpl.CustomerID=wopp.CustomerID and wpl.WarehouseID=wopp.WareHouseID
 --where isnull(wopp.ID,0)=0 and wpl.CustomerID=@CustomerId and wpl.WarehouseID=@WarehouseId

第二步：修复主库存锁定异常

DECLARE @CustomerId INT;
DECLARE @WarehouseId INT;
SET @CustomerId=1;
SET @WarehouseId=5312;
IF( OBJECT_ID('tempdb..#WarehouseProduct') IS NOT NULL  )
BEGIN
  DROP Table #WarehouseProduct
END 

SELECT wp.ID,
       wp.CustomerID,
	   wp.WareHouseID,
       wp.SKU,
       wp.GoodNum,
       wp.LockNum,
       wp.AddTime,
       wp.UpdateTime,
	   wp.PositionID,
	   0 RealLockNum,
	   0 IsUpdate
	   INTO #WarehouseProduct
FROM dbo.RB_WareHouse_Product wp (nolock)   
WHERE wp.CustomerID=@CustomerId AND wp.WareHouseID=@WarehouseId


--1. 订单锁定
UPDATE a SET a.RealLockNum=a.RealLockNum+b.LockNum
FROM #WarehouseProduct a INNER JOIN 
(
SELECT wp.ID,
       SUM(ISNULL(l.LockNum,0)) LockNum 
FROM #WarehouseProduct wp (NOLOCK)   
    LEFT JOIN RB_OrderList l (NOLOCK)
        ON wp.SKU = l.SKU
           AND wp.CustomerID = l.CustomerID
    LEFT JOIN RB_OrderInfo o (NOLOCK)
        ON wp.WareHouseID = o.WareHouseID
           AND wp.CustomerID = o.CustomerID
           AND l.OrderCode = o.OrderCode
    LEFT JOIN RB_OrderSource s (NOLOCK)
        ON o.OrderSourceID = s.ID
           AND o.CustomerID = s.CustomerID
WHERE   
       l.LockNum > 0
      AND o.ID > 0 
GROUP BY wp.ID
) b ON b.ID = a.ID

--2. 包裹锁定
UPDATE a SET a.RealLockNum=a.RealLockNum+b.ProductNum
FROM #WarehouseProduct a INNER JOIN 
(
SELECT wp.ID,
	  SUM(ISNULL(d.ProductNum,0)) ProductNum
FROM #WarehouseProduct wp (NOLOCK)
    LEFT JOIN RB_Procurement_Order_Package_OrderDetail d (NOLOCK)
        ON wp.SKU = d.SKU
           AND wp.CustomerID = d.CustomerID
    LEFT JOIN RB_Procurement_Order_Package p (NOLOCK)
        ON p.ID = d.PackageID
           AND p.CustomerID = d.CustomerID
           AND wp.WareHouseID = p.WareHouseID
    LEFT JOIN RB_Procurement_Order_PositionCode c
        ON d.OrderListID = c.OrderListID
           AND d.CustomerID = c.CustomerID
WHERE p.PackageState IN  (0,1,2,5) 
      AND p.ID > 0
      AND c.StockOperateTime IS NULL
GROUP BY wp.ID
) b ON b.ID = a.ID



--3. 退货单锁定
UPDATE a SET a.RealLockNum=a.RealLockNum+b.ReturnQty
FROM #WarehouseProduct a INNER JOIN 
(
SELECT wp.ID,SUM(ISNULL(srd.ReturnQty,0)) ReturnQty
FROM #WarehouseProduct wp (NOLOCK)
    LEFT JOIN RB_SupplierReturnGood_Detail srd (NOLOCK)
        ON wp.SKU = srd.SKU
           AND wp.CustomerID = srd.CustomerID
           AND wp.WareHouseID = srd.WareHouseID
    LEFT JOIN RB_SupplierReturnGood sr (NOLOCK)
        ON sr.CustomerID = srd.CustomerID
           AND sr.ReturnCode = srd.ReturnCode
WHERE sr.Status IN ( 0, 1, 7, 8, 12 )
GROUP BY wp.ID
) b ON b.ID = a.ID


--4. 产品返修锁定
UPDATE a SET a.RealLockNum=a.RealLockNum+b.LockNumber
FROM #WarehouseProduct a INNER JOIN 
(
SELECT wp.ID , SUM(ISNULL(pd.LockNumber,0)) LockNumber
FROM RB_Procurement_Rework pr (NOLOCK)
    LEFT JOIN RB_Procurement_ReworkDetail pd (NOLOCK)
        ON pr.ID = pd.ReworkID
    LEFT JOIN RB_Procurement_Detail_SingleList ds (NOLOCK)
        ON pr.ProcurementSingleNo = ds.Single_NO
    LEFT JOIN RB_Procurement_List dl (NOLOCK)
        ON ds.P_Code = dl.P_Code
    LEFT JOIN #WarehouseProduct wp (NOLOCK)
        ON dl.WareHouseID = wp.WareHouseID
           AND dl.CustomerID = wp.CustomerID
           AND pd.SKU = wp.SKU
WHERE pr.ReworkState = 1
      AND pd.LockNumber > 0
	  AND wp.ID>0
GROUP BY wp.ID
) b ON b.ID = a.ID

SELECT * FROM #WarehouseProduct WHERE RealLockNum!=LockNum

第三步：修复子库存包裹锁定异常

DECLARE @CustomerId INT;
DECLARE @WarehouseId INT;
DECLARE @PackageAddTime DATETIME;
SET @CustomerId=1;
SET @WarehouseId=5312;
SET @PackageAddTime='2021-01-01' --包裹创建时间

--1.正常拣货的
IF( OBJECT_ID('tempdb..#tempOperateStock1') IS NOT NULL  )
BEGIN
  DROP Table #tempOperateStock1
END 
SELECT a.SKU,a.PositionID, SUM(a.LockNum) AS LockNum,b.PrintBatchNo 
INTO #tempOperateStock1
 FROM RB_Package_OperateStock(NOLOCK) a
INNER JOIN  RB_Procurement_Order_Package(NOLOCK) b ON a.CustomerID=b.CustomerID AND a.PackageID=b.ID
LEFT JOIN RB_Package_Picking_ChildWarehouseArea (NOLOCK) c ON a.AreaTaskNumber=c.AreaTaskNumber AND a.CustomerID=c.CustomerID
WHERE a.CustomerID=@CustomerId AND a.WareHouseID=@WarehouseId AND a.IsDelete=0 AND a.LockNum>0
AND  b.AddTime >= @PackageAddTime
 AND (c.ID IS NULL OR c.TaskSource!=1)
 GROUP BY a.SKU,a.PositionID,b.PrintBatchNo

--2.异常拣货的
IF( OBJECT_ID('tempdb..#tempOperateStock2') IS NOT NULL  )
BEGIN
  DROP Table #tempOperateStock2
END 
SELECT a.SKU,a.PositionID, SUM(a.LockNum) AS LockNum,b.PrintBatchNo 
INTO #tempOperateStock2
 FROM RB_Package_Picking_AbnormalLock_Detail(NOLOCK) a
INNER JOIN  RB_Procurement_Order_Package(NOLOCK) b ON a.CustomerID=b.CustomerID AND a.PackageID=b.ID
LEFT JOIN RB_Package_Picking_ChildWarehouseArea (NOLOCK) c ON a.AreaTaskNumber=c.AreaTaskNumber AND a.CustomerID=c.CustomerID
WHERE a.CustomerID=@CustomerId AND a.WareHouseID=@WarehouseId AND a.IsDelete=0 AND a.LockNum>0 AND a.IsAbnormal=0
AND  b.AddTime >= @PackageAddTime
 AND (c.ID IS NULL OR c.TaskSource!=1)
 GROUP BY a.SKU,a.PositionID,b.PrintBatchNo

 --3.统计子表包裹锁定
 IF( OBJECT_ID('tempdb..#tempLockPositionDetail') IS NOT NULL  )
BEGIN
  DROP Table #tempLockPositionDetail
END 
 SELECT SKU,PositionID,SUM(LockNum) AS LockNum,OrderCode 
 INTO #tempLockPositionDetail FROM RB_WareHouse_LockPositionDetail(NOLOCK) 
 WHERE CustomerID=@CustomerId AND WarehouseID=@WarehouseId AND OrderType=6 AND LockNum>0
 GROUP BY PositionID,SKU,OrderCode
 --4.对比
  IF( OBJECT_ID('tempdb..#tempLockPositionDetailDiff') IS NOT NULL  )
BEGIN
  DROP Table #tempLockPositionDetailDiff
END 
 SELECT a.SKU,a.PositionID,a.OrderCode,a.LockNum AS ZiLockNum,ISNULL(b.LockNum,0) AS NormalLockNum,ISNULL(c.LockNum,0) AS IsAbNormalLockNum,
 a.LockNum-ISNULL(b.LockNum,0)-ISNULL(c.LockNum,0) AS  DiffNum
 INTO  #tempLockPositionDetailDiff
  FROM #tempLockPositionDetail a
 LEFT JOIN #tempOperateStock1 b  ON a.SKU=b.SKU AND a.PositionID=b.PositionID AND a.OrderCode=b.PrintBatchNo
 LEFT JOIN #tempOperateStock2 c  ON a.SKU=c.SKU AND a.PositionID=c.PositionID AND a.OrderCode=c.PrintBatchNo
 DROP Table #tempOperateStock1;
 DROP Table #tempOperateStock2;
 DROP Table #tempLockPositionDetail

 SELECT * FROM #tempLockPositionDetailDiff WHERE DiffNum!=0
 select count(1) from  RB_WareHouse_LockPositionDetail a (NOLOCK) 
 INNER JOIN #tempLockPositionDetailDiff b ON a.PositionID=b.PositionID AND a.SKU=b.SKU AND a.OrderCode=b.OrderCode AND a.LockNum=b.ZiLockNum 
 WHERE a.CustomerID=@CustomerId AND a.WarehouseID=@WarehouseId 
 --修复
 --UPDATE a SET a.LockNum=(b.NormalLockNum+b.IsAbNormalLockNum)  FROM RB_WareHouse_LockPositionDetail a INNER JOIN #tempLockPositionDetailDiff b
 --ON a.PositionID=b.PositionID AND a.SKU=b.SKU AND a.OrderCode=b.OrderCode AND a.LockNum=b.ZiLockNum 
 --WHERE a.CustomerID=@CustomerId AND a.WarehouseID=@WarehouseId and b.DiffNum!=0

第四步：修复子库存锁定异常

DECLARE @CustomerId INT;
DECLARE @WarehouseId INT;
DECLARE @PackageAddTime DATETIME;
SET @CustomerId=1;
SET @WarehouseId=5312;
SET @PackageAddTime='2021-01-01' --包裹创建时间

--1.统计锁定
IF( OBJECT_ID('tempdb..#tempLockPositionDetailTotalLock') IS NOT NULL  )
BEGIN
  DROP Table #tempLockPositionDetailTotalLock
END 
SELECT CustomerID,SKU,PositionID,SUM(LockNum) AS LockNum 
INTO #tempLockPositionDetailTotalLock
FROM dbo.RB_WareHouse_LockPositionDetail (NOLOCK) 
WHERE CustomerID=@CustomerId AND WarehouseID=@WarehouseId AND LockNum>0
GROUP BY CustomerID,SKU,PositionID


--2.查询子表库存

IF( OBJECT_ID('tempdb..#tempwarehousePosition') IS NOT NULL  )
BEGIN
 DROP Table #tempwarehousePosition
END 
SELECT a.*,(a.LockNum-a.RealLockNum) DiffNum INTO #tempwarehousePosition FROM (
SELECT	wopp.ID,wopp.SKU,wopp.PositionID,wopp.LockNum,wopp.GoodNum,ISNULL(tl.LockNum,0) RealLockNum FROM RB_WareHouse_OneProductMorePosition_Product wopp(NOLOCK)
LEFT JOIN #tempLockPositionDetailTotalLock tl(NOLOCK) ON wopp.SKU=tl.SKU AND wopp.PositionID=tl.PositionID AND wopp.CustomerID=tl.CustomerID
WHERE wopp.CustomerID=@CustomerId AND wopp.WareHouseID=@WarehouseId) a

第五步：修复主子库存不一致情况

DECLARE @CustomerId INT;
DECLARE @WarehouseId INT;
DECLARE @PackageAddTime DATETIME;

SET @CustomerId=1;
SET @WarehouseId=333; -- 333 为义乌仓库   5312 为东莞仓库
SET @PackageAddTime='2021-01-01' --包裹创建时间
--1.子库存汇总
IF( Object_id('tempdb..#tempOneProductMorePositionProduct') IS NOT NULL )
  BEGIN
      DROP TABLE #tempOneProductMorePositionProduct
  END

SELECT SKU,
       WareHouseID,
       CustomerID,
       Sum(GoodNum + LockNum) Num,
       Max(ID)                ziId
INTO   #tempOneProductMorePositionProduct
FROM   dbo.RB_WareHouse_OneProductMorePosition_Product (NOLOCK)
WHERE  CustomerID = @CustomerId
       AND WareHouseID = @WarehouseId
GROUP  BY SKU,
          WareHouseID,
          CustomerID

--2.主库存包裹锁定
IF( Object_id('tempdb..#MainPackageLock') IS NOT NULL )
  BEGIN
      DROP TABLE #MainPackageLock
  END

SELECT Sum(d.ProductNum)ProductNum,
       wp.SKU,
       @CustomerID      AS CustomerID,
       @WarehouseID     AS WarehouseID
INTO   #MainPackageLock
FROM   RB_WareHouse_Product wp (nolock)-- 包裹锁定
       LEFT JOIN RB_Procurement_Order_Package_OrderDetail d (nolock)
         ON wp.SKU = d.SKU
            AND wp.CustomerID = d.CustomerID
       LEFT JOIN RB_Procurement_Order_Package p (nolock)
         ON p.ID = d.PackageID
            AND p.CustomerID = d.CustomerID
            AND wp.WareHouseID = p.WareHouseID
       LEFT JOIN RB_Procurement_Order_PositionCode (nolock) c
         ON d.OrderListID = c.OrderListID
            AND d.CustomerID = c.CustomerID
WHERE  wp.CustomerID = @CustomerID
       AND wp.WareHouseID = @WarehouseID
       ---AND p.PackageState IN( 0, 1, 2, 5 )
       AND p.PackageState = 2--已打印的锁定
       AND p.ID > 0
       AND c.StockOperateTime IS NULL
GROUP  BY wp.SKU

--3.子库存包裹批次锁定
IF( Object_id('tempdb..#SubPackageLock') IS NOT NULL )
  BEGIN
      DROP TABLE #SubPackageLock
  END

SELECT Sum(LockNum) AS LockNum,
       SKU,
       @CustomerID  AS CustomerID,
       @WarehouseID AS WarehouseID
INTO   #SubPackageLock
FROM   dbo.RB_WareHouse_LockPositionDetail(NOLOCK)
WHERE  WarehouseID = @WarehouseId
       AND CustomerID = @CustomerId
       AND OrderType = 6
       AND LockNum > 0
GROUP  BY SKU

--4.进行统计
IF( Object_id('tempdb..#tempDiffWarehouseProduct') IS NOT NULL )
  BEGIN
      DROP TABLE #tempDiffWarehouseProduct
  END

SELECT a.ID,
       a.WareHouseID,
       a.CustomerID,
       a.SKU,
       --主表数据
       a.GoodNum                                                                                     AS ZhuGoodNum,--主库存的可用
       a.LockNum                                                                                     AS ZhuLockNum,--主库存的锁定
       a.GoodNum + a.LockNum                                                                         AS ZhuAllNum,--主库存的所有库存
       Isnull(c.ProductNum, 0)                                                                       AS ZhuPackageNum,--主表包裹锁定
       -- 子表数据
       Isnull (b.ziId, 0)                                                                            AS ZiID,-- 子表ID
       Isnull(b.Num, 0)                                                                              AS ZiAllNum,-- 子表存所有库存
       Isnull(d.LockNum, 0)                                                                          AS ZiPackageNum,--子表包裹锁定
       Isnull(c.ProductNum, 0) - Isnull(d.LockNum, 0)                                                AS PackageDiff,--包裹差
       a.GoodNum + a.LockNum - Isnull(b.Num, 0) - ( Isnull(c.ProductNum, 0) - Isnull(d.LockNum, 0) ) DiffNum --主子不一致的
INTO   #tempDiffWarehouseProduct
FROM   RB_WareHouse_Product(NOLOCK) a
       LEFT JOIN #tempOneProductMorePositionProduct b
         ON a.SKU = b.SKU
            AND a.CustomerID = b.CustomerID
            AND a.WareHouseID = b.WareHouseID
       LEFT JOIN #MainPackageLock c
         ON c.SKU = a.SKU
            AND c.CustomerID = a.CustomerID
            AND c.WarehouseID = a.WareHouseID
       LEFT JOIN #SubPackageLock d
         ON d.sku = a.SKU
            AND d.WarehouseID = a.WareHouseID
            AND d.CustomerID = a.CustomerID
WHERE  a.CustomerID = @CustomerId
       AND a.WareHouseID = @WarehouseId

DROP TABLE #tempOneProductMorePositionProduct;

DROP TABLE #MainPackageLock

DROP TABLE #SubPackageLock

--SELECT b.WareHouseName, a.* FROM  #tempDiffWarehouseProduct a
--LEFT JOIN dbo.RB_WareHouse b (NOLOCK) ON a.WareHouseID=b.ID
-- WHERE DiffNum!=0 
SELECT b.WareHouseName                          AS '仓库',
       a.SKU,
       a.ZhuAllNum                              AS '主库存所有库存',
       a.ZiAllNum                               AS '子库存所有',
       a.ZhuPackageNum                          AS '主库存已打印未扣除',
       a.ZiPackageNum                           AS '子库存已打印未扣除',
       a.PackageDiff                            AS '包裹锁定差值(主库存已打印未扣除- 子库存已打印未扣除=子库存已打印已扣除)',
	   --注意 如果这个值（包裹锁定差值）是负数的话：说明主库存锁定或者子库存的锁定是有异常的 会在前面一步修复
       a.ZhuAllNum - a.ZiAllNum - a.PackageDiff AS '主子差（主库存所有库存-子库存所有库存-包裹锁定差）'
FROM   #tempDiffWarehouseProduct a
       LEFT JOIN dbo.RB_WareHouse b (NOLOCK)
         ON a.WareHouseID = b.ID
WHERE  DiffNum != 0 
DROP TABLE #tempDiffWarehouseProduct

补充：第三步修复完 需要  处理锁定为0的数据

 --删除锁定为0的
 UPDATE RB_WareHouse_LockPositionDetail SET CustomerID=-1 WHERE CustomerID=@CustomerId AND WarehouseID=@WarehouseId AND LockNum<=0