}'

已匹配接口数据

SELECT * FROM Fee_WarehouseChargeCountResult WHERE  IsMatched =1 and ChargingDate > '2024-03-14'

DECLARE @USD_Exchange MONEY;
SELECT @USD_Exchange = (
CASE
WHEN cs.IsUseCustomerExchangeRMB = 1
AND ISNULL( rcc.ExchangeRMB, 0 ) > 0 THEN
rcc.ExchangeRMB
ELSE ec.ExchangeRMB
END )
FROM E_Currency ec
WITH ( NOLOCK )
LEFT JOIN RB_Customer_Currency rcc
WITH ( NOLOCK )
ON rcc.Currency = ec.Currency
AND rcc.CustomerID = @CustomerID
LEFT JOIN RB_Customer_Setting cs
WITH ( NOLOCK )
ON cs.CustomerID = rcc.CustomerID
WHERE ec.Currency = 'USD'; INSERT INTO [dbo].[Report_OrderSettlement_StorageCharge] ( [CustomerID], [SettlementDate], [OrderSourceID], [OrderSourceName], [ClientOrderCode], [OrderCode], [TransactionId], [PayTime], [ShipTime], [OrderSourceContry], [WareHouseID], [WareHouseName], [TransportID], [TransportName], [TrackNumbers], [TotalPrice], [ExchangeRMB], [USD_ExchangeRMB], [Currency], [OperatorName], [OrderDescription], [SKU], [ClientSKU], [SellerSKU], [OrderItemId], [ProductSalesPrice], [ProductShipPrice], [ProductNameCN], [ProductGrossWeight], [LastSupplierPrice], [ProductNum], [ProdutNumUpdate], [GroupSKU], [GroupProductNum], [AssignDevelopAdminName], [DevelopAdminName], [EditAdminName], [ImageAdminName], [BusinessAdminID], [BusinessAdminName], [BuyerName], [CheckAdminName], [BusinessTeamID], [BusinessTeamName], [Profits], [Incomes], [Expenses], [SalesPrice], [TransportPay], [SalesRefunds], [PromotionRebates], [OtherCredits], [CommisionFee], [FBATransactionFees], [OrderSourceOtherDebits], [BuyPrice], [PackageFees], [OverSeasShipFees], [FirstLegFees], [TariffFees], [ExchangeLost], [ProductReturnsFee], [AddTime], [OrderSourceType], [PackagedCost], [OrderAddedTime], [OrderStatus], [OrderState], [SalesAdminID], [SalesAdminName], [SalesTeamID], [SalesTeamName], [ConfirmTime], [PayDate], [SettlementShortDate], [DevIDSellerId], [SellerUserID], [PaypalAccount], [CostOfAdvertising], [IsBiSycnComplete], [BiSycnCompleteTime], [ShipDate], [UpdateTime], [OrderListID], [SalesVatFee], [SubsidyPrice], [DistributionPrice], [ASINorItemID], [ProductGroupSKU], [AddOrderReasonType], [WareHouseManagementPrice], [CustomFeeOtherCostPrice], [TaxRefundFee], [Tax], [MarketplaceFacilitatorTax], [ImportFee], [PromotionType], [CostOfStorageFee], [RelationID], [IsBuildFee], [FeeWarehouseChargeID] )
SELECT @CustomerID CustomerID,
ff.ChargingDate AS SettlementDate,
ff.ErpChannelSourceId OrderSourceID,
os.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, 0 AS TotalPrice, x.ExchangeRMB AS ExchangeRMB, @USD_Exchange AS USD_ExchangeRMB, x.Currency AS Currency, '' AS OperatorName, '' AS OrderDescription, ff.ProductSku AS SKU, ff.ClientSKU AS ClientSKU, ff.SellerSKU, ff.ChildFnsku AS OrderItemId, 0 AS ProductSalesPrice, 0 AS ProductShipPrice, ff.ProductNameCN AS ProductNameCN, 0 AS ProductGrossWeight, 0 AS LastSupplierPrice, 0 AS ProductNum, 0 AS ProdutNumUpdate, '' AS GroupSKU, 0 AS GroupProductNum, '' AS AssignDevelopAdminName, '' AS DevelopAdminName, '' AS EditAdminName, '' AS ImageAdminName, ff.BusinessAdminID AS BusinessAdminID, ff.BusinessAdminName AS BusinessAdminName, '' AS BuyerName, '' AS CheckAdminName, ff.BusinessTeamID, isnull( ff.BusinessTeamName, '' ) AS BusinessTeamName, - ABS( ff.ChargingAmount * x.ExchangeRMB ) 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, 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, ff.BusinessTeamID AS SalesTeamID, isnull( ff.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, ff.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
FROM Fee_WarehouseChargeCountResult ff ( NOLOCK )
LEFT JOIN RB_OrderSource os ( NOLOCK )
ON ff.ErpChannelSourceId = os.ID
LEFT JOIN 
(SELECT ID AS Id,
WareHouseName AS WareHouseName,
UserKey AS UserKey,
OverseaWareHouseID AS OverseaWareHouseID
FROM dbo.RB_WareHouse
WITH ( NOLOCK )
WHERE CustomerID = @CustomerID ) Y
ON Y.OverseaWareHouseID = ff.WarehouseId
AND Y.UserKey = ff.CustomerCode
LEFT JOIN 
(SELECT ec.Currency,
(
CASE
WHEN cs.IsUseCustomerExchangeRMB = 1
AND ISNULL( rcc.ExchangeRMB, 0 ) > 0 THEN
rcc.ExchangeRMB
ELSE ec.ExchangeRMB
END ) AS ExchangeRMB, ec.CurrencyCN
FROM E_Currency ec
WITH ( NOLOCK )
LEFT JOIN RB_Customer_Currency rcc
WITH ( NOLOCK )
ON rcc.Currency = ec.Currency
AND rcc.CustomerID = @CustomerID
LEFT JOIN RB_Customer_Setting cs
WITH ( NOLOCK )
ON cs.CustomerID = rcc.CustomerID ) x
ON ff.CurrencyName = x.CurrencyCN
WHERE IsMatched = 1
AND ff.ChargingDate >= @StartTime
AND ff.ChargingDate < @EndTime
AND ff.CustomerCode IN ( '000060', '000067', '000079', '000322', '000288' )

已关联可匹配原币金额

SELECT SUM
( ff.ChargingAmount ) 
FROM
Fee_WarehouseChargeCountResult ff ( NOLOCK )
LEFT JOIN (
SELECT
ID AS Id,
WareHouseName AS WareHouseName,
UserKey AS UserKey,
OverseaWareHouseID AS OverseaWareHouseID 
FROM
dbo.RB_WareHouse WITH ( NOLOCK ) 
WHERE
CustomerID = 1 
) Y ON Y.OverseaWareHouseID = ff.WarehouseId 
AND Y.UserKey = ff.CustomerCode
WHERE
IsMatched = 1 
AND ff.ChargingDate >= '2024-03-14' 
AND ff.ChargingDate < '2024-03-15' 

未关联可匹配原币金额

SELECT SUM ( ChargingAmount )
FROM Fee_WarehouseChargeCountResult ff 
WHERE IsMatched = 0
AND ff.ChargingDate >= '2024-03-14'
AND ff.ChargingDate < '2024-03-15'
AND ff.CustomerCode IN ( '000060', '000067', '000079', '000322', '000288' )
AND DeliveryChannelSourceName LIKE 'Amazon-Z%'
AND DeliveryChannelSourceName NOT LIKE '%副'
AND ErpChannelSourceId > 0

DECLARE @USD_Exchange MONEY;SELECT @USD_Exchange =(CASE
WHEN cs.IsUseCustomerExchangeRMB = 1
AND ISNULL(rcc.ExchangeRMB, 0) > 0 THEN
rcc.ExchangeRMB
ELSE ec.ExchangeRMB END)
FROM E_Currency ec WITH(NOLOCK)
LEFT JOIN RB_Customer_Currency rcc
WITH (NOLOCK)
ON rcc.Currency = ec.Currency
AND rcc.CustomerID = @CustomerID
LEFT JOIN RB_Customer_Setting cs
WITH (NOLOCK)
ON cs.CustomerID = rcc.CustomerID
WHERE ec.Currency = 'USD'; INSERT INTO [dbo].[Report_OrderSettlement_StorageCharge] ( [CustomerID], [SettlementDate], [OrderSourceID], [OrderSourceName], [ClientOrderCode], [OrderCode], [TransactionId], [PayTime], [ShipTime], [OrderSourceContry], [WareHouseID], [WareHouseName], [TransportID], [TransportName], [TrackNumbers], [TotalPrice], [ExchangeRMB], [USD_ExchangeRMB], [Currency], [OperatorName], [OrderDescription], [SKU], [ClientSKU], [SellerSKU], [OrderItemId], [ProductSalesPrice], [ProductShipPrice], [ProductNameCN], [ProductGrossWeight], [LastSupplierPrice], [ProductNum], [ProdutNumUpdate], [GroupSKU], [GroupProductNum], [AssignDevelopAdminName], [DevelopAdminName], [EditAdminName], [ImageAdminName], [BusinessAdminID], [BusinessAdminName], [BuyerName], [CheckAdminName], [BusinessTeamID], [BusinessTeamName], [Profits], [Incomes], [Expenses], [SalesPrice], [TransportPay], [SalesRefunds], [PromotionRebates], [OtherCredits], [CommisionFee], [FBATransactionFees], [OrderSourceOtherDebits], [BuyPrice], [PackageFees], [OverSeasShipFees], [FirstLegFees], [TariffFees], [ExchangeLost], [ProductReturnsFee], [AddTime], [OrderSourceType], [PackagedCost], [OrderAddedTime], [OrderStatus], [OrderState], [SalesAdminID], [SalesAdminName], [SalesTeamID], [SalesTeamName], [ConfirmTime], [PayDate], [SettlementShortDate], [DevIDSellerId], [SellerUserID], [PaypalAccount], [CostOfAdvertising], [IsBiSycnComplete], [BiSycnCompleteTime], [ShipDate], [UpdateTime], [OrderListID], [SalesVatFee], [SubsidyPrice], [DistributionPrice], [ASINorItemID], [ProductGroupSKU], [AddOrderReasonType], [WareHouseManagementPrice], [CustomFeeOtherCostPrice], [TaxRefundFee], [Tax], [MarketplaceFacilitatorTax], [ImportFee], [PromotionType], [CostOfStorageFee], [RelationID], [IsBuildFee], [FeeWarehouseChargeID])
SELECT @CustomerID AS CustomerID,
ff.ChargingDate AS SettlementDate,
ff.ErpChannelSourceId AS OrderSourceID,
ff.DeliveryChannelSourceName AS OrderSourceName,
'' AS ClientOrderCode, '' AS OrderCode, '' AS TransactionId, ff.ChargingDate AS PayTime, ff.ChargingDate AS ShipTime, '' AS OrderSourceContry, Y.Id AS WareHouseID, Y.WareHouseName AS WareHouseName, 0 AS TransportID, '' AS TransportName, '' AS TrackNumbers, 0 AS TotalPrice, x.ExchangeRMB AS ExchangeRMB, @USD_Exchange AS USD_ExchangeRMB, x.Currency AS Currency, '' AS OperatorName, @OrderDesc AS OrderDescription, ff.ProductSku AS SKU, '' AS ClientSKU, '' AS SellerSKU, ff.ChildFnsku AS OrderItemId, 0 AS ProductSalesPrice, 0 AS ProductShipPrice, '' AS ProductNameCN, 0 AS ProductGrossWeight, 0 AS LastSupplierPrice, 0 AS ProductNum, 0 AS ProdutNumUpdate, '' AS GroupSKU, 0 AS GroupProductNum, '' AS AssignDevelopAdminName, '' AS DevelopAdminName, '' AS EditAdminName, '' AS ImageAdminName, '' AS BusinessAdminID, '' AS BusinessAdminName, '' AS BuyerName, '' AS CheckAdminName, '' AS BusinessTeamID, '' AS BusinessTeamName, -ABS(ff.ChargingAmount * x.ExchangeRMB) 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, 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, 0 AS SalesAdminID, '' AS SalesAdminName, '' AS SalesTeamID, '' 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, 0 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
FROM Fee_WarehouseChargeCountResult ff
LEFT JOIN 
(SELECT ID AS Id,
WareHouseName AS WareHouseName,
UserKey AS UserKey,
OverseaWareHouseID AS OverseaWareHouseID
FROM dbo.RB_WareHouse
WITH ( NOLOCK )
WHERE CustomerID = @CustomerID ) Y
ON Y.OverseaWareHouseID = ff.WarehouseId
AND Y.UserKey = ff.CustomerCode
LEFT JOIN 
(SELECT ec.Currency,
(
CASE
WHEN cs.IsUseCustomerExchangeRMB = 1
AND ISNULL( rcc.ExchangeRMB, 0 ) > 0 THEN
rcc.ExchangeRMB
ELSE ec.ExchangeRMB
END ) AS ExchangeRMB, ec.CurrencyCN
FROM E_Currency ec
WITH ( NOLOCK )
LEFT JOIN RB_Customer_Currency rcc
WITH ( NOLOCK )
ON rcc.Currency = ec.Currency
AND rcc.CustomerID = @CustomerID
LEFT JOIN RB_Customer_Setting cs
WITH ( NOLOCK )
ON cs.CustomerID = rcc.CustomerID ) x
ON ff.CurrencyName = x.CurrencyCN
WHERE IsMatched = 0
AND ff.ChargingDate >= @StartTime
AND ff.ChargingDate < @EndTime
AND ff.CustomerCode IN ( '000060', '000067', '000079', '000322', '000288' )
AND DeliveryChannelSourceName LIKE 'Amazon-Z%'
AND DeliveryChannelSourceName NOT LIKE '%副'
AND ErpChannelSourceId > 0