暂无
任务描述
需求描述
1.1. 背景
背景：亚马逊索赔报告历史逻辑中并无判断该货件是否找回丢失及其他异常情况，这样是导致索赔失败，或者已重复索赔情况，需要重新调研调整相关逻辑。
1.2. 验收目标
1.优化原索赔业务逻辑上添加官方内容
2.初始化半年数据，开发参与检验
2. 功能设计
2.1. 页面路径
财务管理---Amazon 追款
2.2.3 优化原索赔业务逻辑上添加官方内容
2.2.3.1 功能点描述：
现在逻辑是，库存调整报告对应已索赔报告，得出未索赔数量。
-------亚马逊索赔Case产生,库存丢失没有考虑到库存找回，和库存损害没有考虑找回所产生逻辑问题。
原基础上库存调整报告，对应类型再细分调整：
1. 库存报告如果SKU 对应 Unreconciled 这列 = 0 值 ，就不匹配已索赔内容；
2. 库存报告如果SKU 有丢失情况，则需要根据再匹配是否丢失找回，如果丢失找回则不用索赔 = N ，M
3. 库存报告如果SKU 有移除情况，则需要根据再匹配是否移回，如果移回则不用索赔 = P ，Q
如下是亚马逊官方回复，及代码解释作为参考
--------------------------------------------------
验收标准
暂无
影响范围
亚马逊索赔case是否正确生成
提交记录
自测报告
1、执行索赔服务（下次索赔同步时间必须小于当前时间、报告类型必须有）
2、查看索赔任务是否生成
SELECT * FROM RB_Amazon_Reimburse_Record(NOLOCK)
3、查看库存损毁、库存丢失差异是否生成
SELECT * FROM RB_Amazon_Reimburse_InventoryDamage(NOLOCK)
SELECT * FROM RB_Amazon_Reimburse_InventoryAdjustmentsLost(NOLOCK)
该需求调整差异生成服务，自测不继续往下继续。
涉及相关业务表
共享库表：
ERP表：
附库存损毁差异对比脚本，如下：
DECLARE @CustomerID INT;
DECLARE @DevIDSellerId NVARCHAR(50);
DECLARE @StartTime DATETIME;
DECLARE @EndTime DATETIME;
SET @CustomerID = 1;
SET @DevIDSellerId = N'A2LZ3VQMY1C6ZE';
SET @StartTime = '2022-05-01';
SET @EndTime = '2022-06-01';
SELECT a.CustomerID,
a.DevIDSellerId,
a.OrderSourceID,
a.fnsku,
a.quantity AS 'lost_quantity'
FROM RB_Report_FULFILLMENT_INVENTORY_ADJUSTMENTS (NOLOCK) a
WHERE a.CustomerID = @CustomerID
AND a.DevIDSellerId = @DevIDSellerId
AND a.reason IN ( 'M' )
AND a.disposition IN ( 'UNSELLABLE', 'SELLABLE' )
AND a.[adjusted-date-utc] >= @StartTime
AND a.[adjusted-date-utc] < @EndTime
AND a.SKU='L18OJW24WTM607I1LFH'
UNION ALL
SELECT a.CustomerID,
a.DevIDSellerId,
a.OrderSourceID,
a.fnsku,
a.quantity AS 'lost_quantity'
FROM RB_Report_FULFILLMENT_INVENTORY_ADJUSTMENTS (NOLOCK) a
WHERE a.CustomerID = @CustomerID
AND a.DevIDSellerId = @DevIDSellerId
AND a.reason IN ( 'F','N' )
AND a.disposition IN ( 'UNSELLABLE', 'SELLABLE' )
AND a.[adjusted-date-utc] >= @StartTime
AND a.SKU='L18OJW24WTM607I1LFH'
WITH tb_adjustment
AS (SELECT a.CustomerID,
a.DevIDSellerId,
a.OrderSourceID,
a.fnsku,
a.quantity AS 'damage_quantity'
FROM RB_Report_FULFILLMENT_INVENTORY_ADJUSTMENTS (NOLOCK) a
WHERE a.CustomerID = @CustomerID
AND a.DevIDSellerId = @DevIDSellerId
AND a.reason IN ( 'E' )
AND a.disposition IN ( 'UNSELLABLE', 'SELLABLE' )
AND a.[adjusted-date-utc] >= @StartTime
AND a.[adjusted-date-utc] < @EndTime
UNION ALL
SELECT a.CustomerID,
a.DevIDSellerId,
a.OrderSourceID,
a.fnsku,
a.quantity AS 'damage_quantity'
FROM RB_Report_FULFILLMENT_INVENTORY_ADJUSTMENTS (NOLOCK) a
WHERE a.CustomerID = @CustomerID
AND a.DevIDSellerId = @DevIDSellerId
AND a.reason IN ( 'F','P','Q' )
AND a.disposition IN ('WAREHOUSE_DAMAGED')
AND a.[adjusted-date-utc] >= @StartTime
AND a.[adjusted-date-utc] < @EndTime),
temp_SellerSKU
AS (SELECT *
FROM
(
SELECT a.CustomerID,
a.DevIDSellerId,
a.OrderSourceID,
a.fnsku,
a.sku,
a.[adjusted-date-utc] AS 'last_adjusted_date_utc',
a.[transaction-item-id] AS 'last_transaction_item_id',
ROW_NUMBER() OVER (PARTITION BY a.CustomerID,
a.DevIDSellerId,
a.OrderSourceID,
a.fnsku
ORDER BY a.[adjusted-date-utc] DESC
) rowID
FROM RB_Report_FULFILLMENT_INVENTORY_ADJUSTMENTS (NOLOCK) a
WHERE a.CustomerID = @CustomerID
AND a.DevIDSellerId = @DevIDSellerId
AND a.reason IN ( 'E' )
AND a.disposition IN ( 'UNSELLABLE', 'SELLABLE' )
AND a.[adjusted-date-utc] >= @StartTime
AND a.[adjusted-date-utc] < @EndTime
) t
WHERE rowID = 1)
SELECT a.CustomerID,
a.StartTime,
a.EndTime,
a.DevIDSellerId,
a.OrderSourceID,
a.fnsku,
a.damage_quantity,
a.reimburse_date,
a.reimburse_inventory,
a.reimburse_quantity,
a.inventory_difference,
b.sku,
b.last_adjusted_date_utc,
b.last_transaction_item_id
FROM
(
SELECT a.CustomerID,
@StartTime 'StartTime',
@EndTime 'EndTime',
a.DevIDSellerId,
a.OrderSourceID,
a.fnsku,
ABS(damage_quantity) AS 'damage_quantity',
b.reimburse_date,
-ISNULL(b.reimburse_inventory, 0) AS 'reimburse_inventory',
-ISNULL(b.reimburse_quantity, 0) AS 'reimburse_quantity',
ABS(damage_quantity) - ISNULL(reimburse_quantity, 0) AS 'inventory_difference'
FROM
(
SELECT *
FROM
(
SELECT a.CustomerID,
a.OrderSourceID,
a.DevIDSellerId,
a.fnsku,
SUM(a.damage_quantity) AS 'damage_quantity'
FROM tb_adjustment a
GROUP BY a.CustomerID,
a.OrderSourceID,
a.DevIDSellerId,
a.fnsku
) t
) a
LEFT JOIN
(
SELECT a.CustomerID,
a.DevIDSellerId,
a.fnsku,
MAX(a.[approval-date-utc]) AS 'reimburse_date',
SUM(a.[quantity-reimbursed-inventory]) AS 'reimburse_inventory',
SUM(a.[quantity-reimbursed-total]) AS 'reimburse_quantity'
FROM RB_Report_REIMBURSEMENTS (NOLOCK) a
WHERE a.CustomerID = @CustomerID
AND a.DevIDSellerId = @DevIDSellerId
AND a.reason = 'Damaged_Warehouse'
AND a.[approval-date-utc] >= @StartTime
GROUP BY a.CustomerID,
a.DevIDSellerId,
a.fnsku
) b
ON b.CustomerID = a.CustomerID
AND b.DevIDSellerId = a.DevIDSellerId
AND b.fnsku = a.fnsku
) a
LEFT JOIN temp_SellerSKU b
ON b.CustomerID = a.CustomerID
AND b.DevIDSellerId = a.DevIDSellerId
AND b.OrderSourceID = a.OrderSourceID
AND b.fnsku = a.fnsku
WHERE inventory_difference != 0;