CREATE INDEX idx_id
ON #tmpSkus ( Sku );
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 #tmpSkus
ON #tmpSkus.Sku = 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 #tmpSkus
ON #tmpSkus.Sku = wpd.ProductSKU
WHERE wp.CustomerID = @customerId
AND wp.StockType IN ( 2, 3 )
UNION ALL
SELECT adb.SKU AS SKU,
Max (wh.WareHouseName) WareHouseName,
Max (wh.WareHouseType) WareHouseType,
iab.InWareHouseID WareHouseID,
0 ProcessingNum,
adb.SKU OrderSourceSKU,
'' ItemCode,
0 GoodNum,
0 LockNum,
Sum (Isnull(adb.ShipNum, 0) - Isnull(adb.ReceiveNum, 0)) AS TransitNum,
Max(wh.WareHouseCountry) WareHouseCountry,
0 OrderSourceID
FROM RB_Inventory_AllocationBill iab WITH ( NOLOCK )
INNER JOIN RB_Inventory_AllocationDetailBill adb WITH ( NOLOCK )
ON iab.ID = adb.AllocationBillID
INNER JOIN RB_OrderInfo (nolock) oi
ON oi.OrderCode = adb.OrderCode
AND adb.CustomerID = oi.CustomerID
INNER JOIN RB_WareHouse wh (NOLOCK)
ON iab.InWareHouseID = wh.ID
INNER JOIN #tmpSkus
ON #tmpSkus.Sku = adb.SKU
WHERE iab.CustomerID = 1
AND adb.[Status] <= 1
AND oi.AmazonShipmentId IS NULL
AND wh.WareHouseType NOT IN ( 1, 3 )
GROUP BY iab.InWareHouseID,
adb.SKU
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 #tmpSkus
ON #tmpSkus.Sku = 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 #tmpSkus
ON #tmpSkus.Sku = 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 wh.WareHouseType NOT IN ( 1, 3 )
