DROP TABLE IF EXISTS #tmpSkus
SELECT CONVERT(VARCHAR(50), item) AS Sku INTO #tmpSkus
FROM Charsplitforin ( '6239503,8657629,2612808,5586940,5603843,2612805,9255079,4172228,6099023,3917515,3917752,1942555,7847801,2566127,6099026,6099024,2955734,6099025,3816299,4348699,6509608,6903442,10797962,2941362,6094320,3115595,8250386,5792130,3604377,4102244,2615174,2396398,8632469,5344398,8657488,5726586,8413772,6059709,1637260,3837342,7625393,3078369,3784219,1891417,5892135,2665142,3499532,2619648,3886809,5439251,3836946,3811864,2504268,8657372,6878186,2916974,9624262,8063294,4406339,6687941,3305831,2329961,8648352,1641643,3917619,1849550,3206874,9474875,8560103,3746994,4445017,2665055,6139850,4218380,3863611,3991252,5495372,8226735,2817189,2598532,6461915,6415384,6389812,10949606,4598068,3454888,2315301,6879491,8511304,9513394,2655002,4406476,3051552,3837572,8308502,4348777,6069035,4005565,5651093,5999633', ',' )
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 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 oi.AmazonShipmentId IS NULL
AND wh.WareHouseType NOT IN ( 1, 3 )
