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

服务地址：http://test-trunk.zhcxkj.com/testcw.aspx

1. 采购中查询SQL

SELECT
                    o.OrderCode,
       l.SKU,
       o.CustomerID,
       o.WareHouseID,
       ( l.ProductNum + l.ProductNumUpdate - Isnull(pod.ProductNum, 0) )                    AS ProductNum,
       SellerSKU,
       pro.ProductName,
       pro.ProductNameCN,
       '' as SupplierName,
       pro.LastBuyPrice,
       o.AddTime,
       ( l.ProductNum + l.ProductNumUpdate - Isnull(pod.ProductNum, 0) ) * pro.LastBuyPrice AS totalPrice,
       o.ShipTime,
       pl.P_Code,
       pl.ExpectedTimeOfDelivery,
       CASE
         WHEN pl.ExpectedTimeOfDelivery IS NOT NULL THEN Datediff(DAY, Getdate(), pl.ExpectedTimeOfDelivery)
         ELSE ''
       END                                                                                  AS DeliveryDays,
       0 as 'putNum',
       0 as 'orderNeedNum',isnull(sku.IsFBA,1) isFBA,
        ISNULL(oiexpand.OrderBuildType, 0) OrderBuildType
                FROM   dbo.RB_OrderInfo AS o WITH (NOLOCK) INNER
                    JOIN dbo.RB_OrderSource AS 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 rb_product pro with(nolock)
                                       on pro.sku=l.sku
                                       and pro.CustomerID=l.CustomerID
                                LEFT JOIN dbo.RB_OrderInfo_BeikuExpand (NOLOCK) oiexpand ON oiexpand.CustomerID = o.CustomerID AND oiexpand.OrderCode = o.OrderCode
                                left join RB_Procurement_Order_PositionCode p with(nolock) on l.ID=p.OrderListID and l.CustomerID=p.CustomerID
                                left join RB_Procurement_List pl with(nolock) on p.P_Code=pl.P_Code and p.CustomerID=pl.CustomerID
--                     left JOIN RB_Procurement_Order_Package_OrderDetail AS  pod WITH(nolock)
                                  left JOIN (SELECT OrderCode,OrderListID,CustomerID,SUM(ProductNum) AS ProductNum FROM RB_Procurement_Order_Package_OrderDetail WITH(nolock)
     where PackageID IN(SELECT ID FROM RB_Procurement_Order_Package WITH(nolock) WHERE PackageState=3)
     GROUP BY OrderCode,OrderListID,CustomerID) pod
                       ON pod.OrderCode=l.OrderCode
                       AND pod.CustomerID=l.CustomerID
                       AND pod.OrderListID=l.ID
                                       AND ToWareHouseID IN(SELECT ID FROM RB_WareHouse WHERE WareHouseType=3 AND CustomerID=l.CustomerID)
 left join RB_Amazon_Product_Base sku (nolock) on sku.CustomerID=l.CustomerID and sku.OrderSourceID=o.OrderSourceID and sku.OrderSourceSKU=l.SellerSKU  AND sku.IsDelete=0    
                WHERE  ((l.ProductNum + l.ProductNumUpdate-isnull(pod.ProductNum,0)) >0) --订单产品数量 减去包裹产品数量，大于0 则进入到采购中
                                  AND o.AddTime > '2022/2/15 14:01:45'
                    AND (s.OrderSourceType <> 20)
                    AND (o.OrderStatus = 0)
                    AND o.IsStockOrder=1
                                  AND o.OrderState<>2   --订单状态等于全部发货
                    AND ToWareHouseID IN(SELECT ID FROM RB_WareHouse WHERE WareHouseType=3 AND CustomerID=l.CustomerID)
                                  AND o.CustomerID=1  and o.OrderSourceID=1000010 and sku.OrderSourceSKUID IN(270592420,270603162)
                                                                    AND ISNULL(oiexpand.OrderBuildType,0) <> 2

2. 本地到FBA备库订单，如果是全部发货，则进入到运输途中

3. 本地到FBA备库订单，部分发货，则进入到采购中

4. 海外仓到FBA备库订单，部分发货和全部发货，不进入到采购中

5. 海外仓到海外仓备库订单，部分发货和全部发货状态，不进入采购中