TASK#35299 亚马逊FBA库龄库存报告逻辑调整 / 技术研发中心 2月第1版本（2023年）

任务描述

暂无

需求描述

1.1. 背景

现有库龄库存是按业务时间约 3- 7 天更新一次，而我们提供数据给业务数据是按天更新，导致数据出现空或0值情况；经调研可过滤为0无用数据。

1.1. 1价值

每月绩效依靠该报告关联销售工资相关计算，空数据出现影响约计金额达 1千万, 另外增加批量导入功能是为了提升业务导入已挂掉店铺的录入。

1.2. 验收目标

1. 亚马逊FBA库龄库存报告V3，按导出取最大非0数据逻辑

2. 采集库龄库存报告V2，按导出取最大非0数据逻辑

3. 采集库龄库存报告V2，增加批量来源渠道导入功能

4. 亚马逊FBA库龄库存报告(采集版)，按导出取最大非0数据逻辑

2. 功能设计

2.1. 页面路径

Excel报表导出--亚马逊FBA库龄库存报告V3

Excel报表导出--采集库龄库存报告V2

自定义报告导出--亚马逊FBA库龄库存报告(采集版)

2.2.3 亚马逊FBA库龄库存报告V3，按导出取最大非0数据逻辑

2.2.3.1 功能点描述：

原设计：

按时间范围取最后一天有数据的内容进行导出；

调整：

按时间范围取最后一天有数据，并且非0的内容进行导出；

2.2.4 采集库龄库存报告V2，按导出取最大非0数据逻辑

2.2.4.1 功能点描述：

原设计：

按时间范围取最后一天有数据的内容进行导出；

调整：

按时间范围取最后一天有数据，并且非0的内容进行导出；

2.2.5 采集库龄库存报告V2，增加批量来源渠道导入功能

2.2.5.1 功能点描述：

原设计：

无

调整：

页面新增批量来源渠道录入，换行符或者逗号作为分割，输入框固定大小，批量上限为 5千个来源渠道录入。超出情况提示渠道过多。

没录入来源渠道情况则默认全部，

来源渠道，和批量来源渠道不能作为共同录入项，如果其中之一选择了另一项则置灰色。

2.2.6 亚马逊FBA库龄库存报告(采集版)

2.2.6.1 功能点描述：

原设计：

按时间范围取最后一天有数据的内容进行导出；

调整：

按时间范围取最后一天有数据，并且非0的内容进行导出；

验收标准

暂无

历史记录

2023-01-28 11:40:48, 由 许瑞丰 创建。
2023-01-28 16:56:21, 由肖雄启动。
修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2023-01-28 16:56:16"。
修改了 任务状态，旧值为 "wait"，新值为 "doing"。

2023-01-29 16:59:10, 由肖雄完成。

修改了 总计消耗，旧值为 "0"，新值为 "6"。
修改了 指派给 ，旧值为 "xiaoxiong"，新值为 "xuruifeng"。
修改了 实际完成，旧值为 ""，新值为 "2023-01-29 16:48:06"。
修改了 任务状态，旧值为 "doing"，新值为 "done"。
修改了 预计剩余，旧值为 "6"，新值为 "0"。
修改了 由谁完成，旧值为 ""，新值为 "xiaoxiong"。

一、功能自测

a.亚马逊FBA库龄库存报告V3，按导出取最大非0数据逻辑

SELECT   *
FROM      RB_FBA_INVENTORY_PLANNING_DATA
WHERE   (sku = 'xx_001')

b.采集库龄库存报告V2，按导出取最大非0数据逻辑

SELECT    *
FROM      RB_Crawler_Amazon_InventoryPlanningReport
WHERE   (ID = 157945)
ORDER BY ID DESC

c.采集库龄库存报告V2，增加批量来源渠道导入功能

d.亚马逊FBA库龄库存报告(采集版)，按导出取最大非0数据逻辑

IF OBJECT_ID('Tempdb..#Tb_Temp_Amazon_Lable') IS NOT NULL DROP TABLE #Tb_Temp_Amazon_Lable;

select distinct
lo.OrderSourceID,
lo.OrderSourceSKU,
isnull(STUFF((select ','+LableName
from RB_Amazon_Lable(NOLOCK)
where CustomerID=@CustomerID AND LableType=1 AND OrderSourceSKUID=lo.ID
for xml
path('')),1,1,''),'') AS LableName  into #Tb_Temp_Amazon_Lable
from RB_Amazon_Lable(NOLOCK) lb inner join  RB_Product_OrderSourceSKU(NOLOCK) lo on lb.OrderSourceSKUID=lo.ID 
where lb.CustomerID=@CustomerID  AND lb.LableType=1;

WITH t1 AS (   
SELECT * FROM  (  
	SELECT  t.CustomerID,  b.DevIDSellerId,  t.OrderSourceID,  t.[snapshot-date],  t.sku,  t.[asin],  t.[AddTime],  [total-quantity],  [sellable-quantity],  [unsellable-quantity],  [inv-age-0-to-90-days],  [inv-age-91-to-180-days],  [inv-age-181-to-270-days],  [inv-age-271-to-365-days],  [inv-age-365-plus-days],  ROW_NUMBER() OVER (  PARTITION BY t.CustomerID,  b.DevIDSellerId,  t.sku  ORDER BY  t.[snapshot-date] DESC  ) rowid 
		FROM  RB_Report_FULFILLMENT_INVENTORY_MANAGE t (NOLOCK)  
		LEFT JOIN dbo.RB_OrderSource_EbayAmazonAPI b (NOLOCK) ON b.CustomerID = t.CustomerID AND b.OrderSourceID = t.OrderSourceID  
		WHERE  t.CustomerID = @CustomerID  AND [snapshot-date] >= DATEADD(day, -10, @Enddate)  AND [snapshot-date] <= @Enddate  AND {{ t.OrderSourceID IN (@OrderSourceID) }} AND (t.[inv-age-0-to-90-days]+t.[inv-age-91-to-180-days] + t.[inv-age-181-to-270-days] + 
	t.[inv-age-271-to-365-days] + t.[inv-age-365-plus-days]+t.[unsellable-quantity])>0
) a  WHERE  a.rowid = 1  )

select 
  --(CASE
      --WHEN oss.OrderSourceCountry in ('FR', 'DE', 'NL', 'ES', 'IT', 'PL', 'SE', 'TR') and possb.r >1 THEN os.OrderSourceName
      --ELSE oss.OrderSourceName
   --END) as [账号],   
   isnull(os.OrderSourceName,oss.OrderSourceName) AS [账号],   
   (
    CASE
      poss.[isDelete]
      WHEN '1' THEN '不在线'
      ELSE '在线'
    END
  ) AS [在线状态],  (
    CASE
      possa.[rowid]
      WHEN '2' THEN 'EFN'
      ELSE '非EFN'
    END
  ) AS [EFN],  isnull(os.OrderSourceCountry,oss.OrderSourceCountry) AS [站点],  ISNULL(tem.BusinessTeamName, tem2.BusinessTeamName)  AS [销售组],  ISNULL(os.SaleAdminName, oss.SaleAdminName)  AS [账号首选销售人员],  a.UserName AS [listing销售人员],  rep.[asin] AS [ASIN],  rep.sku AS [渠道SKU],  pro.SKU AS [系统SKU],
(CASE
      WHEN pro.ProductProperty = 0 THEN '未选择'
      WHEN pro.ProductProperty = 1 THEN '非仿牌'
      WHEN pro.ProductProperty = 2 THEN '仿牌'
      WHEN pro.ProductProperty = 3 THEN '灰色产品'  ELSE ''
    END
  ) [产品侵权风险],
  pro.ClientSKU AS [自定义SKU],
  pro.ProductNameCN AS [产品中文名],  ISNULL(c1.ClassNameCn, '') AS [产品一级类别中文名],   (
    CASE
      WHEN pro.ProductState = 0 THEN '正常'
      WHEN pro.ProductState = 1 THEN '起批量'
      WHEN pro.ProductState = 2 THEN '停产'
      WHEN pro.ProductState = 3 THEN '锁定'
      WHEN pro.ProductState = 4 THEN '暂时缺货'
      WHEN pro.ProductState = 5 THEN '清库'
      ELSE ''
    END
  ) AS '产品状态',
 ISNULL(hd.HolidaysName,'') as '节日',
  (CASE
      WHEN ISNULL(os.SourceStatus,oss.SourceStatus) = 0 THEN '正常'
      WHEN ISNULL(os.SourceStatus,oss.SourceStatus) = 1 THEN '申诉中'
      WHEN ISNULL(os.SourceStatus,oss.SourceStatus) = 2 THEN '暂停'
      WHEN ISNULL(os.SourceStatus,oss.SourceStatus) = 3 THEN '注销'  ELSE ''
    END
  ) [来源渠道属性],  (
    CASE
      pro.IsGroup
      WHEN '1' THEN (rep.[inv-age-0-to-90-days]+rep.[inv-age-91-to-180-days] + rep.[inv-age-181-to-270-days] +   rep.[inv-age-271-to-365-days] + rep.[inv-age-365-plus-days]+rep.[unsellable-quantity])  * (GroupProduct.SumLastSupplierPrice)
      ELSE (rep.[inv-age-0-to-90-days]+rep.[inv-age-91-to-180-days] + rep.[inv-age-181-to-270-days] +   rep.[inv-age-271-to-365-days] + rep.[inv-age-365-plus-days]+rep.[unsellable-quantity])  * (pro.LastSupplierPrice)
    END
  ) AS [库存总额],  (
    CASE
      pro.IsGroup
      WHEN '1' THEN GroupProduct.SumLastSupplierPrice
      ELSE pro.LastSupplierPrice
    END
  ) AS [最新采购价],  (rep.[inv-age-0-to-90-days]+rep.[inv-age-91-to-180-days] + rep.[inv-age-181-to-270-days] +   rep.[inv-age-271-to-365-days] + rep.[inv-age-365-plus-days]+rep.[unsellable-quantity])  AS [库存总数量],
  (
    rep.[inv-age-0-to-90-days]+rep.[inv-age-91-to-180-days] + rep.[inv-age-181-to-270-days] + rep.[inv-age-271-to-365-days] + rep.[inv-age-365-plus-days]
  ) AS [库存可售数量],
  rep.[unsellable-quantity] AS [库存不可售数量],
  rep.[inv-age-0-to-90-days] AS [0-90天库龄数],
  rep.[inv-age-91-to-180-days] AS [91-180天库龄数],
  rep.[inv-age-181-to-270-days] AS [181-270天库龄数],
  rep.[inv-age-271-to-365-days] AS [271-365天库龄数],
  rep.[inv-age-365-plus-days] AS [大于365天库龄数],  (
    rep.[inv-age-91-to-180-days] + rep.[inv-age-181-to-270-days] + rep.[inv-age-271-to-365-days] + rep.[inv-age-365-plus-days]
  ) AS [超过90天总库龄数],
 (
    CASE
      pro.IsGroup
      WHEN '1' THEN (
        (rep.[inv-age-0-to-90-days]+rep.[inv-age-91-to-180-days] + rep.[inv-age-181-to-270-days] + rep.[inv-age-271-to-365-days] + rep.[inv-age-365-plus-days])   * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
      )
      ELSE (
        (rep.[inv-age-0-to-90-days]+rep.[inv-age-91-to-180-days] + rep.[inv-age-181-to-270-days] + rep.[inv-age-271-to-365-days] + rep.[inv-age-365-plus-days])   * (ISNULL(pro.LastSupplierPrice, 0))
      )
    END
  ) AS [可售金额],   (
    CASE
      pro.IsGroup
      WHEN '1' THEN (
        rep.[unsellable-quantity] * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
      )
      ELSE (
        rep.[unsellable-quantity] * (ISNULL(pro.LastSupplierPrice, 0))
      )
    END
  ) AS [不可售金额],
  (
    CASE
      pro.IsGroup
      WHEN '1' THEN (
        rep.[inv-age-0-to-90-days] * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
      )
      ELSE (
        rep.[inv-age-0-to-90-days] * (ISNULL(pro.LastSupplierPrice, 0))
      )
    END
  ) AS [0-90天库存金额],
  (
    CASE
      pro.IsGroup
      WHEN '1' THEN (
        rep.[inv-age-91-to-180-days] * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
      )
      ELSE (
        rep.[inv-age-91-to-180-days] * (ISNULL(pro.LastSupplierPrice, 0))
      )
    END
  ) AS [91-180天库存金额],
  (
    CASE
      pro.IsGroup
      WHEN '1' THEN (
        rep.[inv-age-181-to-270-days] * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
      )
      ELSE (
        rep.[inv-age-181-to-270-days] * (ISNULL(pro.LastSupplierPrice, 0))
      )
    END
  ) AS [181-270天库存金额],
  (
    CASE
      pro.IsGroup
      WHEN '1' THEN (
        rep.[inv-age-271-to-365-days] * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
      )
      ELSE (
        rep.[inv-age-271-to-365-days] * (ISNULL(pro.LastSupplierPrice, 0))
      )
    END
  ) AS [271-365天库存金额],
  (
    CASE
      pro.IsGroup
      WHEN '1' THEN (
        rep.[inv-age-365-plus-days] * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
      )
      ELSE (
        rep.[inv-age-365-plus-days] * (ISNULL(pro.LastSupplierPrice, 0))
      )
    END
  ) AS [超过365天库存金额],
  (
    CASE
      pro.IsGroup
      WHEN '1' THEN (
        (
          rep.[inv-age-91-to-180-days] + rep.[inv-age-181-to-270-days] + rep.[inv-age-271-to-365-days] + rep.[inv-age-365-plus-days]
        ) * (ISNULL(GroupProduct.SumLastSupplierPrice, 0))
      )
      ELSE (
        (
          rep.[inv-age-91-to-180-days] + rep.[inv-age-181-to-270-days] + rep.[inv-age-271-to-365-days] + rep.[inv-age-365-plus-days]

        ) * (ISNULL(pro.LastSupplierPrice, 0))
      )
    END
  ) AS [超过90天总金额],
  rep.[snapshot-date] AS [报告时间],
  rep.[AddTime] AS [ERP下载时间],
  lb.LableName AS [产品标签] 
from t1 rep
left join RB_OrderSource oss with(nolock)  on oss.CustomerID = rep.CustomerID and rep.OrderSourceID = oss.ID -- 关联渠道 账号
inner JOIN (
    SELECT
      t.CustomerID,
      t.DevIDSellerId,
      --t.OrderSourceID,
      --t.ASINorItemID,
      t.OrderSourceSKU,
      t.SKU,
      t.BusinessAdminID,
      t.[isDelete]
    FROM
      (
        SELECT
          a.CustomerID,
          c.DevIDSellerId,
          --a.OrderSourceID,
          --a.ASINorItemID,
          a.OrderSourceSKU,
          a.SKU,
          a.BusinessAdminID,
          a.[isDelete],
          ROW_NUMBER() OVER (
            PARTITION BY a.CustomerID,
            --c.DevIDSellerId,
            a.OrderSourceSKU
            ORDER BY
              a.AddTime DESC
          ) rowid
        FROM
          dbo.RB_Product_OrderSourceSKU a (NOLOCK)
          INNER JOIN dbo.RB_OrderSource b (NOLOCK) ON b.CustomerID = a.CustomerID AND b.ID = a.OrderSourceID
          INNER JOIN dbo.RB_OrderSource_EbayAmazonAPI c (NOLOCK) ON c.CustomerID = a.CustomerID AND c.OrderSourceID = a.OrderSourceID
        WHERE
          a.CustomerID = @CustomerID
          AND b.OrderSourceType = 1
          AND a.ASINorItemID <> ''
          --AND {{ a.OrderSourceID IN (@OrderSourceID) }}
      ) t
    WHERE
      rowid = 1
  ) poss ON poss.OrderSourceSKU = rep.sku AND poss.CustomerID = rep.CustomerID AND poss.DevIDSellerId = rep.DevIDSellerId
left JOIN (
    SELECT
      t.CustomerID,
      t.DevIDSellerId,
      t.OrderSourceID,
      --t.ASINorItemID,
      t.OrderSourceSKU,
      t.SKU,
      t.rowid
    FROM
      (
        SELECT
          a.CustomerID,
          c.DevIDSellerId,
          a.OrderSourceID,
          --a.ASINorItemID,
          a.OrderSourceSKU,
          a.SKU,
          ROW_NUMBER () OVER (
            PARTITION BY a.CustomerID,
            --c.DevIDSellerId,
            --a.ASINorItemID,
            OrderSourceSKU
            ORDER BY
              a.AddTime DESC
          ) rowid
        FROM
          dbo.RB_Product_OrderSourceSKU a (NOLOCK)
          INNER JOIN dbo.RB_OrderSource b (NOLOCK) ON b.CustomerID = a.CustomerID
          AND b.ID = a.OrderSourceID
          INNER JOIN dbo.RB_OrderSource_EbayAmazonAPI c (NOLOCK) ON c.CustomerID = a.CustomerID
          AND c.OrderSourceID = a.OrderSourceID
        WHERE
          a.CustomerID = @CustomerID AND b.OrderSourceType = 1 AND a.ASINorItemID <> ''
          AND {{ a.OrderSourceID IN (@OrderSourceID) }}
      ) t
    WHERE
      rowid = 2
  ) possa ON possa.OrderSourceSKU = rep.sku AND possa.CustomerID = rep.CustomerID AND possa.DevIDSellerId = rep.DevIDSellerId
LEFT JOIN (
  SELECT *
    from
      (
        SELECT
          Row_Number() OVER (
            partition by o.CustomerID,
            api.DevIDSellerId,
            oi.SellerSKU
            ORDER BY
              shiptime desc
          ) as r,
          o.CustomerID,
          api.DevIDSellerId,
          o.OrderSourceID,
          o.OrderCode,
          oi.SellerSKU as OrdersourceSKU,
          o.ShipTime
        from
          RB_OrderInfo(nolock) o
          inner join RB_OrderList(nolock) oi on o.CustomerID = oi.CustomerID
          and o.OrderCode = oi.OrderCode
          inner join RB_WareHouse (nolock) w on o.CustomerID = w.CustomerID
          and o.ToWareHouseID = w.ID
          inner join RB_OrderSource_EbayAmazonAPI(nolock) api on o.CustomerID = api.CustomerID
          and o.OrderSourceID = api.OrderSourceID
        where    o.CustomerID = @CustomerID
          and o.IsStockOrder = 1 --取备库订单
          and w.WareHouseType = 3 --到货仓取FBA仓
          and o.OrderState != 0 --取发货的订单  AND {{ o.OrderSourceID IN (@OrderSourceID) }}
      ) t
    where
      t.r = 1
  ) possb on rep.CustomerID = possb.CustomerID and rep.DevIDSellerId = possb.DevIDSellerId and rep.sku = possb.OrdersourceSKU
LEFT JOIN RB_OrderSource os (NOLOCK) ON os.CustomerID = possb.CustomerID AND os.ID = possb.OrderSourceID
LEFT JOIN RB_Admin a (NOLOCK) ON a.CustomerID = poss.CustomerID AND a.ID = poss.BusinessAdminID
LEFT JOIN RB_Product pro (NOLOCK) ON pro.CustomerID = poss.CustomerID AND pro.SKU = poss.SKU
LEFT JOIN RB_Product_Class (NOLOCK) c1 ON pro.CustomerID = c1.CustomerID AND pro.ClassID1 = c1.ID
LEFT JOIN (
    SELECT
      b.GroupSku,
      b.CustomerID,
      SUM(c.LastSupplierPrice * b.SkuNum) AS SumLastSupplierPrice
    FROM
      RB_Product_GroupSku b (NOLOCK)
      LEFT JOIN RB_Product c (NOLOCK) ON c.SKU = b.SKU
      AND c.CustomerID = b.CustomerID
    WHERE
      b.CustomerID = @CustomerID
    GROUP BY
      b.GroupSku,
      b.CustomerID
  ) GroupProduct ON GroupProduct.GroupSku = pro.SKU AND GroupProduct.CustomerID = pro.CustomerID
LEFT JOIN RB_Admin (NOLOCK) bad ON bad.CustomerID = os.CustomerID AND bad.ID = os.SaleAdminID 
LEFT JOIN RB_BusinessTeam (NOLOCK) tem ON tem.CustomerID = os.CustomerID AND tem.ID = bad.BusinessTeamID 
LEFT JOIN RB_BusinessTeam (NOLOCK) tem2 ON tem2.CustomerID = oss.CustomerID AND (tem2.TeamLeaderName = os.SaleAdminName or tem2.TeamLeaderName = oss.SaleAdminName)
LEFT JOIN RB_SalesSactorHolidays hd (NOLOCK) ON pro.CustomerID = hd.CustomerID AND pro.HolidayID = hd.ID AND HD.IsActive = 1
LEFT JOIN #Tb_Temp_Amazon_Lable lb ON lb.OrderSourceSKU=rep.sku AND lb.OrderSourceID=ISNULL(os.ID, oss.ID)

二、svn提交

2023-10-31 14:24:50, 由周杰关闭。
修改了 任务状态，旧值为 "done"，新值为 "closed"。
修改了 指派给 ，旧值为 "xuruifeng"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "zhoujie"。
修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2023-10-31 14:24:50"。
修改了 关闭原因，旧值为 ""，新值为 "done"。

基本信息
任务的一生

所属迭代	技术研发中心 2月第1版本（2023年）
所属模块	/
相关需求	亚马逊FBA库龄库存报告逻辑调整
指派给	Closed 于 2023-10-31 14:24:50
任务类型	后端
任务标签	初始
开发组	多平台开发组
任务状态	已关闭
进度	100%
优先级	P2
关键词
抄送给

由谁创建	许瑞丰于 2023-01-28 11:40:48
由谁完成	肖雄于 2023-01-29 16:48:06
由谁取消	暂无
由谁关闭	周杰于 2023-10-31 14:24:50
关闭原因	已完成
最后编辑	周杰于 2023-10-31 14:24:50

团队	最初预计	总计消耗	预计剩余

工时信息

最初预计	6工时
总计消耗	6工时
预计剩余	0工时
预计开始	2023-01-28
实际开始	2023-01-28
截止日期	2023-01-29