1.1. 背景

    背景：亚马逊索赔功能，存在数据来源渠道不准确及赔偿数据表无更新等问题，需要做调整优化

1.2. 验收目标

            1.下载数据中心 _GET_FBA_REIMBURSEMENTS_DATA_ 赔偿报告至ERP （RB_Report_FBA_REIMBURSEMENTS）

            2.调整索赔渠道SKU，来源渠道不一致问题

2.   功能设计

2.1. 业务流程

无

2.2.1 操作路径

1. 财务管理---Amazon 追款

2.2.3 下载数据中心 _GET_FBA_REIMBURSEMENTS_DATA_ 赔偿报告至ERP （RB_Report_FBA_REIMBURSEMENTS）

2.2.3.1  功能点描述：

原设计：

索赔功能中，存在逻辑 15个月无连续数据则视该店铺为不做索赔。

调整：下载数据中心 _GET_FBA_REIMBURSEMENTS_DATA_ 至ERP

报告命名： RB_Report_FBA_REIMBURSEMENTS，应用于亚马逊索赔功能替换旧同样报告。

索赔功能中，调整逻辑 12个月无连续数据则视该店铺为不做索赔。

2.2.3 调整索赔渠道SKU，来源渠道不一致问题

2.2.3.1  功能点描述：

原设计：

为处理泛欧产品，取产品表多来源渠道时，按排序选择第一个来源渠道。

SELECT b.*
FROM RB_Amazon_Reimburse_InventoryAdjustmentsLost a ( NOLOCK )
LEFT JOIN RB_Amazon_Product_Base b ( NOLOCK ) ON b.CustomerID = a.CustomerID
AND b.DevIDSellerId = a.DevIDSellerId
AND b.OrderSourceSKU = a.sku and b.IsDelete=0

调整：

下载的索赔表，取FNSKU作为真实的来源渠道源。

FNSKU：X002EBMBFL

解决问题来源2：

索赔显示在了Amazon-Z01466-TR

;WITH temp_main
AS ( SELECT DISTINCT
a.CustomerID ,
a.DevIDSellerId ,
a.OrderSourceID
FROM RB_OrderSource_EbayAmazonAPI a ( NOLOCK )
LEFT JOIN RB_OrderSource b ( NOLOCK ) ON b.CustomerID = a.CustomerID
AND b.ID = a.OrderSourceID
WHERE a.CustomerID = 1
AND a.DevIDSellerId = 'AMEQMGVON01Q4'
AND b.OrderSourceCountry IN ( 'GB', 'US' )
)
SELECT t.CustomerID ,
t.StartTime ,
t.EndTime ,
t.DevIDSellerId ,
t.OrderSourceID ,
t.SellerSKU ,
t.FNSKU ,
t.InventoryDifference ,
t.BatchTime ,
os.SaleAdminID ,
os.SaleAdminName ,
t.BusinessAdminID ,
t.BusinessUserName ,
t.ListingOrderSourceID ,
0 AS 'StartingInventoryLost' ,
0 AS 'ReveivedInventoryLost' ,
0 AS 'CustomerOrderLost' ,
0 AS 'CustomerReturnLost' ,
0 AS 'AdjustmentLost' ,
0 AS 'RemovalReturnLost' ,
0 AS 'RemovalDisposalLost' ,
0 AS 'EndingInventoryLost' ,
0 AS 'CalcEndingInventoryLost' ,
t.LostQuantity ,
t.LastTransactionItemId ,
t.LastAdjustedDateUtc ,
'' AS 'AmazonOrderId' ,
NULL AS 'RefundDate' ,
0 AS 'RefundQuantity' ,
0 AS 'ReturnQuantity' ,
t.ReimburseQuantity ,
os.OrderSourceName AS 'ListingOrderSourceName'
FROM ( SELECT a.CustomerID ,
a.StartTime ,
a.EndTime ,
a.DevIDSellerId ,
a.OrderSourceID ,
a.sku AS 'SellerSKU' ,
a.fnsku AS 'FNSKU' ,
a.inventory_difference AS 'InventoryDifference' ,
a.lost_quantity AS 'LostQuantity' ,
a.reimburse_quantity AS 'ReimburseQuantity' ,
a.last_transaction_item_id AS 'LastTransactionItemId' ,
a.last_adjusted_date_utc AS 'LastAdjustedDateUtc' ,
a.BatchTime ,
b.BusinessAdminID ,
b.BusinessUserName ,
ISNULL(ISNULL(b.OrderSourceID, c.OrderSourceID),
a.OrderSourceID) AS 'ListingOrderSourceID' ,
ROW_NUMBER() OVER ( PARTITION BY a.ID ORDER BY a.ID ) AS 'rowID'
FROM RB_Amazon_Reimburse_InventoryAdjustmentsLost a ( NOLOCK )
LEFT JOIN RB_Amazon_Product_Base b ( NOLOCK ) ON b.CustomerID = a.CustomerID
AND b.DevIDSellerId = a.DevIDSellerId
AND b.OrderSourceSKU = a.sku and b.IsDelete=0
LEFT JOIN temp_main c ON c.CustomerID = a.CustomerID
AND c.DevIDSellerId = a.DevIDSellerId
WHERE --a.IsHistory = 0
a.inventory_difference > 0
AND a.CustomerID = 1
AND a.DevIDSellerId = 'AMEQMGVON01Q4'
AND a.BatchTime = '2022-08-02 02:20:22.207'
) t
LEFT JOIN RB_OrderSource os ( NOLOCK ) ON os.CustomerID = t.CustomerID
AND os.ID = t.ListingOrderSourceID
WHERE t.rowID = 1 AND t.SellerSKU='Z2650QP309W';