需求背景

目前FBA库存信息通过listingV2的接口获取数据，存在延迟与数据错误的问题，导致运营备货等判断库存的数据是错误过滤或未过滤而被执行。现在需要对接虎鲸的数据。

需求内容

1、FBA打标表库存数据对接新接口获取：优先以这个接口的库龄数据为准，如果这个接口为获取不到，继续按listingV2的接口数据为准。

注意：虎鲸接口与listingV2接口是在一个流程里同时获取

接口链接：http://showdoc.zhcxkj.com/web/#/88/4852

2、建单待发（采购中）数据，直接按下方逻辑规则查询

SELECT
o.CustomerID as customer_id,
o.OrderSourceID as source_id,
l.SellerSKU msku,
Sum(l.ProductNum + l.ProductNumUpdate - Isnull(pod.ProductNum, 0)) / Isnull (( SELECT Sum(SkuNum) AS Expr1
FROM dbo.RB_Product_GroupSku AS g WITH (NOLOCK) WHERE g.CustomerID = o.CustomerID AND ( GroupSku = ( CASE WHEN l.GroupSKU = '' THEN l.SKU ELSE l.GroupSKU END ) )), 1) AS product_num,
( CASE WHEN l.GroupSKU = '' THEN l.SKU ELSE l.GroupSKU END ) as sku,
o.ToWareHouseID as to_warehouse_id
FROM dbo.RB_OrderInfo AS o WITH (NOLOCK)
INNER JOIN dbo.RB_OrderSource s ON o.OrderSourceID = s.ID AND o.CustomerID = s.CustomerID
INNER JOIN dbo.RB_OrderList AS l WITH (NOLOCK) ON o.OrderCode = l.OrderCode AND o.CustomerID = l.CustomerID
LEFT JOIN dbo.RB_OrderInfo_BeikuExpand h WITH (NOLOCK) ON o.OrderCode = h.OrderCode AND o.CustomerID = h.CustomerID
LEFT JOIN ( SELECT d.OrderCode, OrderListID, d.CustomerID, Sum(ProductNum) AS ProductNum FROM RB_Procurement_Order_Package p WITH (NOLOCK)
INNER JOIN dbo.RB_Procurement_Order_Package_OrderDetail d WITH (NOLOCK) ON d.CustomerID = p.CustomerID AND d.PackageID = p.ID
LEFT JOIN RB_OrderInfo o WITH (nolock) ON d.OrderCode = o.OrderCode AND d.CustomerID = o.CustomerID
LEFT JOIN dbo.RB_OrderInfo_BeikuExpand h WITH (NOLOCK) ON o.OrderCode = h.OrderCode AND o.CustomerID = h.CustomerID
WHERE p.PackageState = 3 AND o.AddTime > Dateadd(month, -6, Getdate()) AND o.IsStockOrder = 1 AND h.orderBuildType in (1, 2) AND o.OrderStatus = 0 AND o.OrderState < 2 GROUP BY d.OrderCode, OrderListID, d.CustomerID ) AS pod ON pod.OrderCode = l.OrderCode AND pod.CustomerID = l.CustomerID AND pod.OrderListID = l.ID WHERE l.ProductNum + l.ProductNumUpdate - Isnull(pod.ProductNum, 0) > 0 AND s.OrderSourceType = 1 AND o.OrderStatus = 0 AND o.OrderState < 2 AND o.IsStockOrder = 1 AND h.orderBuildType in (1, 2) AND o.AddTime > Dateadd(month, -6, Getdate()) GROUP BY o.ToWareHouseID, o.CustomerID, l.SellerSKU, o.OrderSourceID, CASE WHEN l.GroupSKU = '' THEN l.SKU ELSE l.GroupSKU END

注意：

可售库存：fulfillableQuantity  可售数量
预留库存：totalReservedQuantity  预留数量  -   pendingCustomerOrderQuantity 买家订单数量
在途数量（运输中）：transitQuantity在途数量

FBA打标表库存数据对接新接口获取