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

INSERT INTO #AddInventoryTransferLog SELECT TOP 1 * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) WHERE CustomerID=1 AND SKU=@Sku AND ToWareHouseID=@WareHouseID ORDER BY ShipTime DESC 

--获取最近一次头程  
IF EXISTS(SELECT TOP 1 * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) WHERE CustomerID=1 AND SKU=@Sku AND ToWareHouseID=@WareHouseID AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE()) 
BEGIN                                                                                                                                                                                            
--1.先找进销存 SKU + 仓库，找对应运费单 。 距今 1.5 年，中位数 运费单。
INSERT INTO #AddInventoryTransferLog
SELECT * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) WHERE CustomerID=1 AND SKU=@Sku AND ToWareHouseID=@WareHouseID AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() ORDER BY ShipFee DESC     
END   
Else IF EXISTS(SELECT TOP 1 * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) WHERE CustomerID=1 AND SKU=@Sku AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE()) 
BEGIN  
--2.如上取空，改为取SKU 距今 1.5 年，本地发货，不限收货位置的，中位数 运费单。
INSERT INTO #AddInventoryTransferLog
SELECT * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) WHERE CustomerID=1 AND SKU=@Sku  AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and FromWareHouseID in(SELECT ID FROM RB_WareHouse(NOLOCK) WHERE WareHouseType=1) ORDER BY ShipFee DESC 
END
ELSE 
BEGIN  
--3.如取空情况，则还是取最近一次。按原有逻辑不变。
INSERT INTO #AddInventoryTransferLog
SELECT TOP 1 * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) WHERE CustomerID=1 AND SKU=@Sku AND ToWareHouseID=@WareHouseID ORDER BY ShipTime DESC 
END
 

取中位数示例：

SELECT top 1 * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK)
where ID=(select CONVERT(INT,MIN(ID)+(MAX(ID)-MIN(ID))/2)  FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK)
where ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and FromWareHouseID in(SELECT ID FROM RB_WareHouse(NOLOCK) WHERE WareHouseType=1)) ;

上传了附件 RB_WareHouse_Inventory_Transfer_Log_MonthlyStatistics.txt

按运费取中位数：

select * from (select ROW_NUMBER() over(order by ShipFee desc) as RowNum,* FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) 
where ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and FromWareHouseID in(SELECT ID FROM RB_WareHouse(NOLOCK) WHERE WareHouseType=1)) a
where a.RowNum=(
select CONVERT(INT,MIN(RowNum)+(MAX(RowNum)-MIN(RowNum))/2) middleID FROM  (select ROW_NUMBER() over(order by ShipFee desc) as RowNum,* FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) 
where ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and FromWareHouseID in(SELECT ID FROM RB_WareHouse(NOLOCK) WHERE WareHouseType=1)) as a
)

CREATE PROCEDURE [dbo].[RB_WareHouse_Inventory_Transfer_Log_MonthlyStatistics]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Month VARCHAR(10),@LastSyncMonth DATETIME,@IsEnable BIT=0
--获取头程盘点开关
SELECT @IsEnable=IsAllowInventoryeMonthlyStatistics FROM RB_Customer_Setting(NOLOCK) WHERE CustomerID=1
--获取最后一次头程盘点的月份，如果从未对账则取上个月
SELECT TOP 1 @LastSyncMonth=SynTime FROM RB_Warehouse_Record_SynTime(NOLOCK) WHERE SynType=11 
--初始化只盘点上个月的，当前月盘点上个月的头程，在当前月结束前会对未盘点的库存继续盘点，如5月盘点4月数据，没到6月1日前继续盘点4月份未盘点数据
SET @Month =  CASE WHEN @LastSyncMonth IS NULL OR DATEADD(MONTH,2,@LastSyncMonth)>GETDATE() THEN CONVERT(VARCHAR(7),DATEADD(MONTH,-1,GETDATE()),120) ELSE CONVERT(VARCHAR(7),DATEADD(MONTH,1,@LastSyncMonth),120) END

SELECT *
INTO #WareHouseIssales
FROM RB_WareHouse_Issales_difference(NOLOCK) 
WHERE DevIDSellerId<>'' AND [Month]=@Month AND [Status]=1
--新上线限定5个账号做验证
AND DevIDSellerId IN('A2IWCHDS14I39N','A2LQA0GPO5HG67','A2LZ3VQMY1C6ZE','A2N4L7AZF4SX4Q','A32EF7C73R2YQZ')

SELECT *
INTO #WareHouseTransfer
FROM RB_WareHouse_Inventory_Transfer_MonthlyStatistics_Log(NOLOCK) 
WHERE [Month]=@Month AND AddTime>CONVERT(DATETIME,@Month+'-20')

--生成空临时表结构
--盘盈更新头程临时表
SELECT TOP 0 0 AS ID
,[CustomerID]
,[BaseID]
,[BaseOrderCode]
,[BaseOrderListID]
,[BasePackageID]
,[BasePackageOrderDetailID]
,[BaseShipTime]
,[BaseQuantity]
,[BaseClearedQuantity]
,[BaseLastClearTime]
,[BaseShipFee]
,[BaseTariffFee]
,[FromWareHouseID]
,[ToWareHouseID]
,[OrderCode]
,[OrderListID]
,[PackageID]
,[PackageOrderDetailID]
,[ShipTime]
,[SKU]
,[Quantity]
,[ClearedQuantity]
,[LastClearTime]
,[ShipFee]
,[TariffFee]
,[BuyPrice]
,[TaxRefundFee]
,[AddTime]
,[UpdateTime]
,[IsBiSycnComplete]
,[BiSycnCompleteTime]
,0 AS DeductNum
INTO #UpdateInventoryTransferLog 
FROM dbo.RB_WareHouse_Inventory_Transfer_Log(NOLOCK)
--盘亏添加头程临时表
SELECT TOP 0 0 AS ID
,[CustomerID]
,[BaseID]
,[BaseOrderCode]
,[BaseOrderListID]
,[BasePackageID]
,[BasePackageOrderDetailID]
,[BaseShipTime]
,[BaseQuantity]
,[BaseClearedQuantity]
,[BaseLastClearTime]
,[BaseShipFee]
,[BaseTariffFee]
,[FromWareHouseID]
,[ToWareHouseID]
,[OrderCode]
,[OrderListID]
,[PackageID]
,[PackageOrderDetailID]
,[ShipTime]
,[SKU]
,[Quantity]
,[ClearedQuantity]
,[LastClearTime]
,[ShipFee]
,[TariffFee]
,[BuyPrice]
,[TaxRefundFee]
,[AddTime]
,[UpdateTime]
,[IsBiSycnComplete]
,[BiSycnCompleteTime]
INTO #AddInventoryTransferLog 
FROM dbo.RB_WareHouse_Inventory_Transfer_Log(NOLOCK)

IF (@IsEnable=1) AND EXISTS(SELECT TOP 1 ID FROM #WareHouseIssales) AND EXISTS(SELECT TOP 1 ID FROM #WareHouseTransfer)
BEGIN
/*
盘点头程：逐条进行盘盈、盘亏操作
--盘盈：头程数量大于实际库存数量，详细处理方式
-----1、对头程数量进行扣除
-----2、生成虚拟销售单（如：PD20220507000323835300）带走扣除的头程数量
--盘亏：头程数量小于实际库存数量，添加头程信息，发货数量未库存差异数，运费及关税等费用取最近头程；没有最近头程不做盘点
*/
DECLARE @DevIDSellerId VARCHAR(50),@Sku VARCHAR(50),@WareHouseID INT,@Number INT,@IsEFN BIT,@ID INT,@TransferNumber INT,@DiffNumber INT
DECLARE @DeductNum INT = 0,@TransferLogID INT=0,@Quantity INT=0,@ClearedQuantity INT=0
WHILE EXISTS(SELECT 1 FROM #WareHouseIssales WHERE [Status]=1)
BEGIN
--初始化可用头程信息(更新)
IF OBJECT_ID(N'tempdb..#UpdateInventoryTransferLog',N'U') IS NOT NULL
BEGIN
TRUNCATE TABLE #UpdateInventoryTransferLog
END 
ELSE
BEGIN
RETURN
END
--初始化可用头程信息(添加)
IF OBJECT_ID(N'tempdb..#AddInventoryTransferLog',N'U') IS NOT NULL
BEGIN
TRUNCATE TABLE #AddInventoryTransferLog
END
ELSE
BEGIN
RETURN
END

--初始化参数
SELECT @DevIDSellerId='',@Sku='',@WareHouseID=0,@Number=0,@IsEFN=0,@ID=0,@TransferNumber=0,@DiffNumber=0,@DeductNum=0,@TransferLogID=0,@Quantity=0,@ClearedQuantity=0
SELECT TOP 1 @DevIDSellerId=DevIDSellerId,@Sku=Sku,@WareHouseID=WareHouseID,@Number=Number,@IsEFN=IsEFN,@ID=ID FROM #WareHouseIssales ORDER BY ID DESC
 
--泛欧，根据账号和SKU对比头程和实际库存差异进行盘点
IF(@IsEFN=1)
BEGIN
SELECT @TransferNumber=SUM(Quantity) FROM #WareHouseTransfer WHERE DevIDSellerId=@DevIDSellerId AND SKU=@Sku
SET @DiffNumber=ISNULL(@Number,0)-ISNULL(@TransferNumber,0)  --差异数量=实际库存数-头程数

IF OBJECT_ID(N'tempdb..#EFN_WareHouse',N'U') IS NOT NULL
BEGIN
DROP TABLE #EFN_WareHouse
END 
SELECT ID AS WareHouseID
INTO #EFN_WareHouse
FROM dbo.RB_WareHouse
WHERE CustomerID=1 AND AutonomousWareHouseID IN(SELECT OrderSourceID FROM  dbo.RB_OrderSource_EbayAmazonAPI(NOLOCK) WHERE DevIDSellerId=@DevIDSellerId)

IF(@DiffNumber>0) --盘亏
BEGIN
--获取最近一次头程  
IF EXISTS(SELECT TOP 1 * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) WHERE CustomerID=1 AND SKU=@Sku AND ToWareHouseID=@WareHouseID AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE()) 
BEGIN                                                                                                                                                                                            
--1.先找进销存 SKU + 仓库，找对应运费单 。 距今 1.5 年，中位数 运费单。
INSERT INTO #AddInventoryTransferLog
    select TOP 1 * from (select ROW_NUMBER() over(order by ShipFee desc) as RowNum,* FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) 
where CustomerID=1 AND SKU=@Sku AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and ToWareHouseID=@WareHouseID) a
where a.RowNum=(
select AVG(RowNum) middleID FROM  (select ROW_NUMBER() over(order by ShipFee desc) as RowNum,* FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) 
where CustomerID=1 AND SKU=@Sku AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and ToWareHouseID=@WareHouseID) as a
);
END   
else IF EXISTS(SELECT TOP 1 * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) WHERE CustomerID=1 AND SKU=@Sku AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and FromWareHouseID in(SELECT ID FROM RB_WareHouse(NOLOCK) WHERE WareHouseType=1)) 
BEGIN  
--2.如上取空，改为取SKU 距今 1.5 年，本地发货，不限收货位置的，中位数 运费单。
INSERT INTO #AddInventoryTransferLog
select TOP 1 * from (select ROW_NUMBER() over(order by ShipFee desc) as RowNum,* FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) 
where CustomerID=1 AND SKU=@Sku AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and FromWareHouseID in(SELECT ID FROM RB_WareHouse(NOLOCK) WHERE WareHouseType=1)) a
where a.RowNum=(
select AVG(RowNum) middleID FROM  (select ROW_NUMBER() over(order by ShipFee desc) as RowNum,* FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) 
where CustomerID=1 AND SKU=@Sku AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and FromWareHouseID in(SELECT ID FROM RB_WareHouse(NOLOCK) WHERE WareHouseType=1)) as a
);
END
ELSE 
BEGIN  
--3.如取空情况，则还是取最近一次。按原有逻辑不变。
INSERT INTO #AddInventoryTransferLog
SELECT TOP 1 * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) WHERE CustomerID=1 AND SKU=@Sku AND ToWareHouseID=@WareHouseID ORDER BY ShipTime DESC 
END
IF EXISTS(SELECT 1 FROM #AddInventoryTransferLog)
BEGIN
   
UPDATE #AddInventoryTransferLog 
SET 
ShipTime=GETDATE(),
UpdateTime=GETDATE(),
AddTime=GETDATE(),
Quantity=@DiffNumber,
ClearedQuantity=0,
PackageID=0,
PackageOrderDetailID=0,
OrderCode='PD'+REPLACE(CONVERT(VARCHAR(10),GETDATE(),120),'-','')+RIGHT('000000000000'+CAST(CONVERT(INT,CEILING(RAND() * 1000000000)) AS VARCHAR(12)),12),
OrderListID=0,
BaseOrderCode='',
BasePackageID=0,
BasePackageOrderDetailID=0,
BaseQuantity=0,
BaseClearedQuantity=0
END
END
ELSE IF(@DiffNumber<0)  --盘盈
BEGIN
--获取该WareHouseID、SKU下可用的头程
INSERT INTO #UpdateInventoryTransferLog
SELECT *
FROM(
SELECT alias.*,0 AS DeductNum
FROM   RB_WareHouse_Inventory_Transfer_Log alias WITH(nolock)
INNER JOIN RB_Customer_Setting cs with(nolock) on alias.CustomerId=cs.CustomerID
LEFT JOIN RB_Procurement_Order_Package pop with(nolock) on alias.CustomerId=pop.CustomerID and alias.PackageId=pop.ID
LEFT JOIN RB_Procurement_Order_Package_OrderDetail od with(nolock) on alias.PackageOrderDetailID=od.ID 
WHERE  alias.CustomerID=1  
AND alias.ToWareHouseID IN(SELECT WareHouseID FROM #EFN_WareHouse)
AND alias.Quantity > alias.ClearedQuantity
AND pop.PackageState=3
AND (case when isnull(cs.FirstLegAssignType,0)=1 then pop.IsAccount else 1 end)=1
AND alias.SKU=@SKU
UNION ALL   
SELECT alias.*,0 AS DeductNum
FROM   RB_WareHouse_Inventory_Transfer_Log alias WITH(nolock)
WHERE  alias.CustomerID=1   
AND alias.ToWareHouseID IN(SELECT WareHouseID FROM #EFN_WareHouse)
AND alias.Quantity > alias.ClearedQuantity
and alias.PackageID=0
AND alias.SKU=@SKU
) T

IF EXISTS(SELECT 1 FROM #UpdateInventoryTransferLog)
BEGIN
--扣减头程数量
WHILE(@DeductNum<>ABS(@DiffNumber))
BEGIN
SELECT TOP 1 @TransferLogID=ID,@Quantity=Quantity,@ClearedQuantity=ClearedQuantity FROM #UpdateInventoryTransferLog WHERE Quantity>ClearedQuantity ORDER BY ShipTime DESC
IF(@TransferLogID<=0) --头程数量不足，部分扣减后，跳出本次循环
BEGIN
UPDATE #WareHouseIssales SET [Status]=3,ErrorMsg='头程数量不足，仅扣减部分头程，扣减数量：'+CONVERT(VARCHAR(10),@DeductNum)  WHERE ID=@ID
BREAK
END
ELSE IF(@Quantity-@ClearedQuantity>=ABS(@DiffNumber))
BEGIN
UPDATE #UpdateInventoryTransferLog SET ClearedQuantity=ClearedQuantity+ABS(@DiffNumber),DeductNum=ABS(@DiffNumber) WHERE ID=@TransferLogID
SET @DeductNum=ABS(@DiffNumber)
END
ELSE
BEGIN
UPDATE #UpdateInventoryTransferLog SET ClearedQuantity=@Quantity,DeductNum=@Quantity-@ClearedQuantity WHERE ID=@TransferLogID
SET @DeductNum+=@Quantity-@ClearedQuantity
END 
END

END
ELSE
BEGIN
UPDATE #WareHouseIssales SET [Status]=2,ErrorMsg='未找到头程信息' WHERE ID=@ID
END
END
END
ELSE
BEGIN
SELECT @TransferNumber=SUM(Quantity) FROM #WareHouseTransfer WHERE DevIDSellerId=@DevIDSellerId AND SKU=@Sku AND WareHouseID=@WareHouseID
SET @DiffNumber=ISNULL(@Number,0)-ISNULL(@TransferNumber,0)  --差异数量=实际库存数-头程数
IF(@DiffNumber>0) --盘亏
BEGIN
--获取最近一次头程  
IF EXISTS(SELECT TOP 1 * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) WHERE CustomerID=1 AND SKU=@Sku AND ToWareHouseID=@WareHouseID AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE()) 
BEGIN                                                                                                                                                                                            
--1.先找进销存 SKU + 仓库，找对应运费单 。 距今 1.5 年，中位数 运费单。
INSERT INTO #AddInventoryTransferLog
    select TOP 1 * from (select ROW_NUMBER() over(order by ShipFee desc) as RowNum,* FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) 
where CustomerID=1 AND SKU=@Sku AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and ToWareHouseID=@WareHouseID) a
where a.RowNum=(
select AVG(RowNum) middleID FROM  (select ROW_NUMBER() over(order by ShipFee desc) as RowNum,* FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) 
where CustomerID=1 AND SKU=@Sku AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and ToWareHouseID=@WareHouseID) as a
);
END   
else IF EXISTS(SELECT TOP 1 * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) WHERE CustomerID=1 AND SKU=@Sku AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and FromWareHouseID in(SELECT ID FROM RB_WareHouse(NOLOCK) WHERE WareHouseType=1)) 
BEGIN  
--2.如上取空，改为取SKU 距今 1.5 年，本地发货，不限收货位置的，中位数 运费单。
INSERT INTO #AddInventoryTransferLog
select TOP 1 * from (select ROW_NUMBER() over(order by ShipFee desc) as RowNum,* FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) 
where CustomerID=1 AND SKU=@Sku AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and FromWareHouseID in(SELECT ID FROM RB_WareHouse(NOLOCK) WHERE WareHouseType=1)) a
where a.RowNum=(
select AVG(RowNum) middleID FROM  (select ROW_NUMBER() over(order by ShipFee desc) as RowNum,* FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) 
where CustomerID=1 AND SKU=@Sku AND ShipTime>=dateadd(DAY,-365-182,GETDATE()) and ShipTime<=GETDATE() and FromWareHouseID in(SELECT ID FROM RB_WareHouse(NOLOCK) WHERE WareHouseType=1)) as a
);
END
ELSE 
BEGIN  
--3.如取空情况，则还是取最近一次。按原有逻辑不变。
INSERT INTO #AddInventoryTransferLog
SELECT TOP 1 * FROM RB_WareHouse_Inventory_Transfer_Log(NOLOCK) WHERE CustomerID=1 AND SKU=@Sku AND ToWareHouseID=@WareHouseID ORDER BY ShipTime DESC 
END
IF EXISTS(SELECT 1 FROM #AddInventoryTransferLog)
BEGIN
UPDATE #AddInventoryTransferLog
SET 
ShipTime=GETDATE(),
UpdateTime=GETDATE(),
AddTime=GETDATE(),
Quantity=@DiffNumber,
ClearedQuantity=0,
PackageID=0,
PackageOrderDetailID=0,
OrderCode='PD'+REPLACE(CONVERT(VARCHAR(10),GETDATE(),120),'-','')+RIGHT('000000000000'+CAST(CONVERT(INT,CEILING(RAND() * 1000000000)) AS VARCHAR(12)),12),
OrderListID=0,
BaseOrderCode='',
BasePackageID=0,
BasePackageOrderDetailID=0,
BaseQuantity=0,
BaseClearedQuantity=0
END
END
ELSE IF(@DiffNumber<0)  --盘盈
BEGIN
--获取该WareHouseID、SKU下可用的头程
INSERT INTO #UpdateInventoryTransferLog
SELECT *
FROM(
SELECT alias.*,0 AS DeductNum
FROM   RB_WareHouse_Inventory_Transfer_Log alias WITH(nolock)
inner join RB_Customer_Setting cs with(nolock) on alias.CustomerId=cs.CustomerID
left join RB_Procurement_Order_Package pop with(nolock) on alias.CustomerId=pop.CustomerID and alias.PackageId=pop.ID
left join RB_Procurement_Order_Package_OrderDetail od with(nolock) on alias.PackageOrderDetailID=od.ID 
WHERE  alias.CustomerID=1  
AND alias.ToWareHouseID=@WareHouseID
AND alias.Quantity > alias.ClearedQuantity
AND pop.PackageState=3
AND (case when isnull(cs.FirstLegAssignType,0)=1 then pop.IsAccount else 1 end)=1
AND alias.SKU=@SKU
UNION ALL   
SELECT alias.*,0 AS DeductNum
FROM   RB_WareHouse_Inventory_Transfer_Log alias WITH(nolock)
WHERE  alias.CustomerID=1   
AND alias.ToWareHouseID=@WareHouseID
AND alias.Quantity > alias.ClearedQuantity
and alias.PackageID=0
AND alias.SKU=@SKU
) T

IF EXISTS(SELECT 1 FROM #UpdateInventoryTransferLog)
BEGIN
--扣减头程数量
WHILE(@DeductNum<>ABS(@DiffNumber))
BEGIN
SELECT TOP 1 @TransferLogID=ID,@Quantity=Quantity,@ClearedQuantity=ClearedQuantity FROM #UpdateInventoryTransferLog WHERE Quantity>ClearedQuantity ORDER BY ShipTime DESC
IF(@TransferLogID<=0) --头程数量不足，部分扣减后，跳出本次循环
BEGIN
UPDATE #WareHouseIssales SET [Status]=3,ErrorMsg='头程数量不足，仅扣减部分头程，扣减数量：'+CONVERT(VARCHAR(10),@DeductNum)  WHERE ID=@ID
BREAK
END
ELSE IF(@Quantity-@ClearedQuantity>=ABS(@DiffNumber))
BEGIN
UPDATE #UpdateInventoryTransferLog SET ClearedQuantity=ClearedQuantity+ABS(@DiffNumber),DeductNum=ABS(@DiffNumber) WHERE ID=@TransferLogID
SET @DeductNum=ABS(@DiffNumber)
END
ELSE
BEGIN
UPDATE #UpdateInventoryTransferLog SET ClearedQuantity=@Quantity,DeductNum=@Quantity-@ClearedQuantity WHERE ID=@TransferLogID
SET @DeductNum+=@Quantity-@ClearedQuantity
END 
END

END
ELSE
BEGIN
UPDATE #WareHouseIssales SET [Status]=2,ErrorMsg='未找到头程信息' WHERE ID=@ID
END
END
END
BEGIN TRAN
BEGIN TRY
--生成销售运费池虚拟订单
IF OBJECT_ID(N'tempdb..#UpdateInventoryTransferLog',N'U') IS NOT NULL AND EXISTS(SELECT 1 FROM #UpdateInventoryTransferLog WHERE DeductNum>0)
BEGIN
INSERT INTO dbo.RB_WareHouse_Inventory_SalesOrder_Log(
CustomerID,
BaseID,
BaseOrderCode,
BaseOrderListID,
BasePackageID,
BasePackageOrderDetailID,
BaseShipTime,
BaseQuantity,
BaseClearedQuantity,
BaseLastClearTime,
WareHouseID,
OrderCode,
OrderListID,
PackageID,
PackageOrderDetailID,
ShipTime,
SKU,
Quantity,
ShipFee,
TariffFee,
BuyPrice,
TaxRefundFee,
AddTime,
UpdateTime,
IsBiSycnComplete,
BiSycnCompleteTime
)
SELECT CustomerID,
ID AS BaseID,
OrderCode AS BaseOrderCode,
0 AS BaseOrderListID,
PackageID AS BasePackageID,
PackageOrderDetailID AS BasePackageOrderDetailID,
ShipTime AS BaseShipTime,
Quantity AS BaseQuantity,
ClearedQuantity-DeductNum AS BaseClearedQuantity,
BaseLastClearTime AS BaseLastClearTime,
ToWareHouseID AS WareHouseID,
'PD'+REPLACE(CONVERT(VARCHAR(10),GETDATE(),120),'-','')+RIGHT('000000000000'+CAST(CONVERT(INT,CEILING(RAND() * 1000000000)) AS VARCHAR(12)),12) AS OrderCode,
0 AS OrderListID,
0 AS PackageID,
0 AS PackageOrderDetailID,
GETDATE() AS ShipTime,
@SKU AS SKU,
DeductNum AS Quantity,
0 AS ShipFee,
0 AS TariffFee,
0 AS BuyPrice,
0 AS TaxRefundFee,
GETDATE() AS AddTime,
GETDATE() AS UpdateTime,
0 AS IsBiSycnComplete,
'2000-01-01 00:00:00.000' AS BiSycnCompleteTime
FROM #UpdateInventoryTransferLog
WHERE DeductNum>0

--更新头程数量
UPDATE A SET A.ClearedQuantity=B.ClearedQuantity,A.UpdateTime=GETDATE() 
FROM dbo.RB_WareHouse_Inventory_Transfer_Log A INNER JOIN #UpdateInventoryTransferLog B
ON A.ID=B.ID
WHERE B.DeductNum>0
END
--生成调拨单
IF OBJECT_ID(N'tempdb..#UpdateInventoryTransferLog',N'U') IS NOT NULL AND EXISTS(SELECT 1 FROM #AddInventoryTransferLog)
BEGIN
INSERT INTO dbo.RB_WareHouse_Inventory_Transfer_Log
(
CustomerID,
BaseID,
BaseOrderCode,
BaseOrderListID,
BasePackageID,
BasePackageOrderDetailID,
BaseShipTime,
BaseQuantity,
BaseClearedQuantity,
BaseLastClearTime,
BaseShipFee,
BaseTariffFee,
FromWareHouseID,
ToWareHouseID,
OrderCode,
OrderListID,
PackageID,
PackageOrderDetailID,
ShipTime,
SKU,
Quantity,
ClearedQuantity,
LastClearTime,
ShipFee,
TariffFee,
BuyPrice,
TaxRefundFee,
AddTime,
UpdateTime,
IsBiSycnComplete,
BiSycnCompleteTime
)
SELECT CustomerID,
BaseID,
BaseOrderCode,
BaseOrderListID,
BasePackageID,
BasePackageOrderDetailID,
BaseShipTime,
BaseQuantity,
BaseClearedQuantity,
BaseLastClearTime,
BaseShipFee,
BaseTariffFee,
FromWareHouseID,
ToWareHouseID,
OrderCode,
OrderListID,
PackageID,
PackageOrderDetailID,
ShipTime,
SKU,
Quantity,
ClearedQuantity,
LastClearTime,
ShipFee,
TariffFee,
BuyPrice,
TaxRefundFee,
AddTime,
UpdateTime,
IsBiSycnComplete,
BiSycnCompleteTime
FROM #AddInventoryTransferLog
END

COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
BEGIN
ROLLBACK TRAN
END 
DECLARE @MESS VARCHAR(1000)
SET @MESS='SKU:'+@SKU+',仓库：'+CONVERT(VARCHAR(50),@WareHouseID)+',错误：'+  ERROR_MESSAGE()
UPDATE #WareHouseIssales SET [Status]=2,ErrorMsg='更新头程数量和生成销售运费池出现错误，'+@MESS WHERE ID=@ID
END CATCH

--更新状态
UPDATE #WareHouseIssales SET [Status]=CASE WHEN [Status]=1 THEN 4 ELSE [Status] END,TransferNumber=@TransferNumber WHERE ID=@ID
END

--同步库存差异表信息
UPDATE A SET A.[Status]=B.[Status],A.ErrorMsg=B.ErrorMsg,A.TransferNumber=B.TransferNumber,A.UpdateTime=GETDATE() FROM RB_WareHouse_Issales_difference A(NOLOCK) INNER JOIN  #WareHouseIssales B ON A.ID=B.ID WHERE B.[Status]<>1

--记录盘点时间
IF EXISTS(SELECT TOP 1 ID FROM RB_Warehouse_Record_SynTime(NOLOCK) WHERE SynType=11)
BEGIN
UPDATE RB_Warehouse_Record_SynTime SET SynTime=CONVERT(DATETIME,@Month+'-01'),UpdateTime=GETDATE() WHERE SynType=11
END
ELSE
BEGIN
INSERT INTO dbo.RB_Warehouse_Record_SynTime(SynType,SynTime,AddTime,UpdateTime,Remark,SynID) VALUES(11,CONVERT(DATETIME,@Month+'-01'),GETDATE(),GETDATE(),'头程盘点服务',NULL)
END

--删除临时表
IF OBJECT_ID(N'tempdb..#UpdateInventoryTransferLog',N'U') IS NOT NULL
BEGIN
DROP TABLE #UpdateInventoryTransferLog
END 
IF OBJECT_ID(N'tempdb..#AddInventoryTransferLog',N'U') IS NOT NULL
BEGIN
DROP TABLE #AddInventoryTransferLog
END 
IF OBJECT_ID(N'tempdb..#WareHouseIssales',N'U') IS NOT NULL
BEGIN
DROP TABLE #WareHouseIssales
END 
IF OBJECT_ID(N'tempdb..#WareHouseTransfer',N'U') IS NOT NULL
BEGIN
DROP TABLE #WareHouseTransfer
END
END
END

IF @@ERROR>0 PRINT 'Error:Failed to create procedure RB_WareHouse_Inventory_Transfer_Log_MonthlyStatistics!';

此功能是改：存储过程

取运费中位数