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

需求背景：因为现在仓租费用没有自动导入利润表，从而导致估算的毛利虚增，影响数据100万左右。为了解决这个问题现在每月手动导入盛宝隆仓租，需要花费技术、财务大量时间处理数据和核对数据，并且导入的数据也不一定准。大约每月耗费时间2人天/月

需求内容：

盛宝隆仓租数据，每天自动跑服务导入到利润表

详细内容：

一、定时从盛宝隆仓租费-费用明细列表API获取仓租费用数据，同步至Fee_WarehouseChargeCountdetails

每天获取结算时间为前一天的

二、Fee_WarehouseChargeCountdetails表数据按照仓租费用均摊逻辑把箱产品的仓租费用均摊至条码产品，并把均摊后的数据同步至Fee_WarehouseChargeCountResult

箱总仓租费用  *（箱内X条码产品体积  /  箱内所有产品体积之和）=  X条码产品的均摊费用

三、费用均摊至Fee_WarehouseChargeCountResult 表之后，对定时跑服务对数据进行匹配

匹配逻辑：
1）使用FNSKU+渠道SKU字段与RB_Amazon_Product_Base匹配，关联出需要的字段数据进入【利润临时表】
2)  使用上面1）逻辑匹配不到的数据，走下面的匹配逻辑匹配出来源渠道：
DeliveryChannelSourceName 包含“'Amazon-Z”
DeliveryChannelSourceName 不含中文
ErpChannelSourceId不等于0

从RB_Amazon_Product_Base表关联的字段：

CustomerID

OrderSourceID

OrderSourceName

ClientSKU AS ClientSKU
OrderSourceSKU AS SellerSKU

BusinessAdminID AS BusinessAdminID
BusinessUserName AS BusinessAdminName

BusinessTeam AS BusinessTeamID
BusinessTeamName AS BusinessTeamName

BusinessTeam AS SalesTeamID
BusinessTeamName AS SalesTeamName

ASINorItemID AS ASINorItemID
从RB_OrderSource表关联的字段：

OrderSourceCountry AS OrderSourceContry

SaleAdminID AS SalesAdminID

SaleAdminName AS SalesAdminName

四、从RB_Customer_Currency获取汇率信息把原币金额转换为人民币

-ABS(ff.ChargingAmount * x.ExchangeRMB) = ImportFee

五、第3步匹配处理的数据自动同步至Tmp_Report_OrderSettlement利润临时表之中

同步利润临时表逻辑：

   pb.CustomerID,
       ff.ChargingDate AS SettlementDate,
       pb.OrderSourceID,
       pb.OrderSourceName,
       '' AS ClientOrderCode,
       '' AS OrderCode,
       '' AS TransactionId,
       ff.ChargingDate AS PayTime,
       ff.ChargingDate AS ShipTime,
       os.OrderSourceCountry AS OrderSourceContry,
       Y.Id AS WareHouseID,
       Y.WareHouseName AS WareHouseName,
       0 AS TransportID,
       '' AS TransportName,
       '' AS TrackNumbers,
       -- -ABS(ff.ChargingAmount * x.ExchangeRMB) AS TotalPrice,
     0 AS TotalPrice,
       x.ExchangeRMB AS ExchangeRMB,
       --人民币兑美元汇率
       @USD_Exchange AS USD_ExchangeRMB,
       x.Currency AS Currency,
       '' AS OperatorName,
       '盛宝隆仓租导入2022010122020210-36092.536117675' AS OrderDescription,
       ff.ProductSku AS SKU,
       pb.ClientSKU AS ClientSKU,
       pb.OrderSourceSKU AS SellerSKU,
       ff.ChildFnsku AS OrderItemId,
       0 AS ProductSalesPrice,
       0 AS ProductShipPrice,
       pb.ProductNameCN AS ProductNameCN,
       0 AS ProductGrossWeight,
       0 AS LastSupplierPrice,
       1 AS ProductNum,
       0 AS ProdutNumUpdate,
       '' AS GroupSKU,
       0 AS GroupProductNum,
       '' AS AssignDevelopAdminName,
       '' AS DevelopAdminName,
       '' AS EditAdminName,
       '' AS ImageAdminName,
       pb.BusinessAdminID AS BusinessAdminID,
       pb.BusinessUserName AS BusinessAdminName,
       '' AS BuyerName,
       '' AS CheckAdminName,
       pb.BusinessTeam AS BusinessTeamID,
       pb.BusinessTeamName AS BusinessTeamName,
     -ABS(ff.ChargingAmount * x.ExchangeRMB) AS Profits,
     --0 AS  Profits,
       0 AS Incomes,
       -ABS(ff.ChargingAmount * x.ExchangeRMB) AS Expenses,
       0 AS SalesPrice,
       0 AS TransportPay,
       0 AS SalesRefunds,
       0 AS PromotionRebates,
       0 AS OtherCredits,
       0 AS CommisionFee,
       0 AS FBATransactionFees,
       -- -ABS(ff.ChargingAmount * x.ExchangeRMB) AS OrderSourceOtherDebits,
     0 AS OrderSourceOtherDebits,
       0 AS BuyPrice,
       0 AS PackageFees,
       0 AS OverSeasShipFees,
       0 AS FirstLegFees,
       0 AS TariffFees,
       0 AS ExchangeLost,
       0 AS ProductReturnsFee,
       ff.ChargingDate AS AddTime,
       1 AS OrderSourceType,
       0 AS PackagedCost,
       ff.ChargingDate AS OrderAddedTime,
       0 AS OrderStatus,
       3 AS OrderState,
       os.SaleAdminID AS SalesAdminID,
       os.SaleAdminName AS SalesAdminName,
       --销售小组
       pb.BusinessTeam AS SalesTeamID,
       pb.BusinessTeamName AS SalesTeamName,
       ff.ChargingDate AS ConfirmTime,
       ff.ChargingDate AS PayDate,
       ff.ChargingDate AS SettlementShortDate,
       '' AS DevIDSellerId,
       '' AS SellerUserID,
       '' AS PaypalAccount,
       0 AS CostOfAdvertising,
       0 AS IsBiSycnComplete,
       GETDATE() AS BiSycnCompleteTime,
       ff.ChargingDate AS ShipDate,
       GETDATE() AS UpdateTime,
       CONVERT(INT,ff.CustomerCode) AS OrderListID,
       0 AS SalesVatFee,
       0 AS SubsidyPrice,
       0 AS DistributionPrice,
       pb.ASINorItemID AS ASINorItemID,
       '' AS ProductGroupSKU,
       0 AS AddOrderReasonType,
       0 AS WareHouseManagementPrice,
       0 AS CustomFeeOtherCostPrice,
       0 AS TaxRefundFee,
       0 AS Tax,
       0 AS MarketplaceFacilitatorTax,
       -ABS(ff.ChargingAmount * x.ExchangeRMB)  AS ImportFee,
       0 AS PromotionType,
       0 AS CostOfStorageFee,
       0 AS RelationID,
       0 AS IsBuildFee,
       ff.ID AS FeeWarehouseChargeID