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

背景： 现有财务使用 销售成本表 rb_finance_Settlement  数据来源于利润表 RB_Report_OrderSettlement，

但因为店铺数据存在差异，现通过SQL 覆盖方式进行调整。 

问题来源：平台其他费用差异（ordersourceotherdebits），广告费差异（CostOfAdvertising）

select sum(ordersourceotherdebits) from RB_Report_OrderSettlement(NOLOCK) where customerid=1 and OrderSourceType in (1,2)
 and shiptime>'2021-11-01'
 and shiptime<'2021-11-30'
 --and ordersourceID='8596'
 --and ordersourceotherdebits <-5
 union
 select sum(ordersourceotherdebits)  from dbo.rb_finance_Settlement(NOLOCK) where customerid=1 and OrderSourceType in (1,2)
     and shiptime>'2021-11-01'
and shiptime<'2021-11-30'
--and ordersourceID='8596'
--and ordersourceotherdebits <-5

解决方案：

DECLARE @CustomerID INT 
DECLARE @OrderSourceType INT 
DECLARE @BeginTime NVARCHAR(20)
DECLARE @EndTime NVARCHAR(20)

SET @CustomerID=1
SET @OrderSourceType=2
SET @BeginTime='2021-11-01'
SET @EndTime='2021-12-01'
--修改Finance 11月Ebay OrderSourceOtherDebits<>0 数据 商户号为-2

UPDATE dbo.RB_Finance_Settlement SET CustomerID=-2 WHERE
CustomerID=@CustomerID
AND  OrderSourceType=@OrderSourceType
AND ShipTime>=@BeginTime
AND ShipTime<@EndTime
AND OrderSourceOtherDebits<>0;

--插入利润表到财务结算表
INSERT INTO RB_Finance_Settlement
(
    [CustomerID],
    [CheckDate],
    [SettlementDate],
    [OrderSourceID],
    [OrderSourceName],
    [PayTime],
    [ShipTime],
    [WarehouseID],
    [WareHouseName],
    [OrderSourceType],
    [OrderSourceTypeName],
    [Ordercode],
    [ClientOrderCode],
    [TransactionId],
    [SKU],
    [ClientSKU],
    [SellerSKU],
    [ProductNameCN],
    [SaleNum],
    [Currency],
    [ExchangeRMB],
    [USD_ExchangeRMB],
    [Country],
    [Profits],
    [Incomes],
    [SalesPrice],
    [TransportPay],
    [SalesRefunds],
    [PromotionRebates],
    [OtherCredits],
    [CommisionFee],
    [FBATransactionFees],
    [OrderSourceOtherDebits],
    [CostOfAdvertising],
    [BuyPrice],
    [PackageFees],
    [SalesAdminID],
    [SalesAdminName],
    [SalesTeamID],
    [SalesTeamName],
    [remark],
    [AddTime],
    [UpdateTime],
    [AddCommisionFee],
    [TransportID],
    [Transport_API_Class]
)
SELECT CustomerID,SettlementDate,SettlementDate,OrderSourceID,OrderSourceName,
PayTime,ShipTime,
WareHouseID,WareHouseName,OrderSourceType,OrderSourceTypeName,OrderCode,ClientOrderCode,
TransactionId,SKU,ClientSKU,SellerSKU,ProductNameCN,(ProductNum+ProdutNumUpdate) AS SaleNum,
Currency,ExchangeRMB,USD_ExchangeRMB,OrderSourceContry,Profits,Incomes,SalesPrice,TransportPay,
SalesRefunds,PromotionRebates,OtherCredits,CommisionFee,FBATransactionFees,OrderSourceOtherDebits,
CostOfAdvertising,BuyPrice,PackageFees,SalesAdminID,SalesAdminName,SalesTeamID,SalesTeamName,
'利润覆盖20211216' AS remark,
SettlementDate AS AddTime,SettlementDate AS UpdateTime,0 AS AddCommisionFee,TransportID,0 AS Transport_API_Class
FROM dbo.RB_Report_OrderSettlement(NOLOCK)
WHERE 
CustomerID=@CustomerID
AND  OrderSourceType=@OrderSourceType
AND ShipTime>=@BeginTime
AND ShipTime<@EndTime
AND OrderSourceOtherDebits<>0


时间范围： 7 -11 月。

• #38921 rb_finance_Settlement与RB_Report_OrderSettlement 查询总数一致
• #38922 rb_finance_Settlement与RB_Report_OrderSettlement 导出ordersourceotherdebits金额一致
• #38923 rb_finance_Settlement与RB_Report_OrderSettlement 导出CostOfAdvertising金额一致