[步骤]
请紧急修复
(@customerId int,@wareHouseType int,@Tb_SKUs [Tb_StringIds] READONLY) SELECT wp.SKU, wh.WareHouseName, wh.WareHouseType, wp.WareHouseID, ISNULL( wpaf.ProcessingNum, 0 ) ProcessingNum, wp.OrderSourceSKU, wp.FNSKU ItemCode, wp.GoodNum, wp.LockNum, ISNULL( wp.OnWayNum, 0 ) TransitNum, wh.WareHouseCountry , wh.AutonomousWareHouseID OrderSourceID FROM RB_WareHouse_Product wp WITH ( NOLOCK ) INNER JOIN @Tb_SKUs skus ON skus.Id =wp.SKU INNER JOIN RB_WareHouse wh WITH ( NOLOCK ) ON wp.CustomerID= wh.CustomerID AND wp.WareHouseID= wh.ID AND wh.IsActive= 1 LEFT JOIN RB_Warehouse_Product_ActiveInfo wpaf WITH (NOLOCK) ON wp.CustomerID= wpaf.CustomerID AND wp.SKU= wpaf.SKU AND wp.WarehouseID= wpaf.WarehouseID WHERE wp.CustomerID = @customerId AND wh.WareHouseType NOT IN (1,3,60) UNION ALL SELECT wpd.RelationSku SKU, wh.WareHouseName, wh.WareHouseType, wp.WareHouseID, 0 ProcessingNum, wpd.RelationSku OrderSourceSKU, wpd.FNSKU ItemCode, wp.GoodNum* wpd.ProductQuantity GoodNum, wp.LockNum* wpd.ProductQuantity LockNum, 0 TransitNum, wh.WareHouseCountry, wpd.ErpChannelId OrderSourceID FROM RB_WareHouse_Product wp WITH ( NOLOCK ) INNER JOIN RB_WareHouse wh WITH ( NOLOCK ) ON wp.CustomerID= wh.CustomerID AND wp.WareHouseID= wh.ID AND wh.IsActive= 1 INNER JOIN RB_WareHouse_ProductDetail wpd WITH ( NOLOCK ) ON wh.ID = wpd.WarehouseId AND wp.OrderSourceSKU = wpd.ItemCode INNER JOIN @Tb_SKUs skus ON skus.Id =wpd.ProductSKU WHERE wp.CustomerID= @customerId AND wp.StockType in (2,3) UNION ALL SELECT wpd.RelationSku SKU, wh.WareHouseName, wh.WareHouseType, wp.WareHouseID, 0 ProcessingNum, wpd.RelationSku OrderSourceSKU, wpd.FNSKU ItemCode, wpd.ProductQuantity GoodNum, wp.LockNum, 0 TransitNum, wh.WareHouseCountry, wpd.ErpChannelId OrderSourceID FROM RB_WareHouse_Product wp WITH ( NOLOCK ) INNER JOIN RB_WareHouse wh WITH ( NOLOCK ) ON wp.CustomerID= wh.CustomerID AND wp.WareHouseID= wh.ID AND wh.IsActive= 1 INNER JOIN RB_WareHouse_ProductDetail wpd WITH ( NOLOCK ) ON wh.ID = wpd.WarehouseId AND wp.FNSKU = wpd.ItemCode INNER JOIN @Tb_SKUs skus ON skus.Id =wpd.RelationSku WHERE wp.CustomerID= 1 AND ISNULL(wp.StockType, 1) = 1 AND wh.WareHouseType = 60 UNION ALL SELECT CASE WHEN ISNULL( ol.GroupSKU, '' ) = '' THEN ol.SKU ELSE ol.GroupSKU END SKU, wh.WareHouseName, wh.WareHouseType, wh.ID WareHouseID, 0 ProcessingNum, ol.SellerSKU OrderSourceSKU, '' FNSKU, 0 GoodNum, 0 LockNum, CASE WHEN ISNULL( ol.GroupSKU, '' ) = '' THEN ol.ProductNum + ol.ProductNumUpdate ELSE ISNULL( ol.GroupProductNum, 0 ) + ISNULL( ol.GroupProductNumUpdate, 0 ) END TransitNum, wh.WareHouseCountry , oi.OrderSourceID FROM RB_OrderInfo oi (NOLOCK) INNER JOIN RB_OrderList ol (NOLOCK) ON oi.OrderCode = ol.OrderCode AND oi.CustomerID = ol.CustomerID INNER JOIN @Tb_SKUs skus ON skus.Id = ol.SKU INNER JOIN RB_WareHouse wh (NOLOCK) ON oi.ToWareHouseID = wh.ID AND oi.CustomerID = wh.CustomerID INNER JOIN RB_Procurement_Order_Package_OrderDetail opd (NOLOCK) ON ol.ID = opd.OrderListID AND ol.CustomerID = opd.CustomerID INNER JOIN RB_Procurement_Order_Package op (NOLOCK) ON opd.PackageID = op.ID AND opd.CustomerID = op.CustomerID WHERE op.PackageState = 3 AND oi.OrderStatus = 0 AND oi.IsStockOrder = 1 AND oi.CustomerID = @customerId AND ol.AmazonDeliveryStatus = 0 AND oi.OverDay >= 0 AND oi.AmazonShipmentId IS NULL AND wh.WareHouseType NOT IN (1,3)
[结果]
[期望]
虎鲸模块的