任务描述
调整销售成本数据，与158一起验证
需求描述
背景： 现有财务使用 销售成本表 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 @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 月。
验收标准
暂无