TASK#33316 库龄库存报告增加标签属性值 / 技术研发中心 12月第1版本（2022年）

任务描述

需求描述

1.1. 背景

背景：有品需求针对库龄库存报告中，SKU关联产品标签属性值；

1.2. 验收目标

1.库龄库存报告涉及的表（以下操作路径）都添加该标签值

2. 功能设计

2.1. 业务流程

无

2.2.1 操作路径

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

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

3. Excel报表导出 ---库龄库存报告V3

2.2.3 库龄库存报告涉及的表（以下操作路径）都添加该标签值

2.2.3.1 功能点描述：

原设计：无

调整：

添加一列字段，根据SKU，查找产品标签属性值，逻辑参考这个需求：11604 Listing手工标签属性功能

验收标准

暂无

历史记录

2022-12-01 13:48:17, 由 许瑞丰 创建。
2022-12-02 08:58:18, 由肖雄记录工时，消耗 1 小时。
修改了 总计消耗，旧值为 "0"，新值为 "1"。
修改了 预计剩余，旧值为 "4"，新值为 "3"。
修改了 任务状态，旧值为 "wait"，新值为 "doing"。
修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2022-12-01"。

2022-12-02 15:42:16, 由肖雄添加备注。

一、报表：亚马逊FBA库龄库存报告(采集版)

重新配置自定义报表：亚马逊FBA库龄库存报告(采集版)，添加字段：[产品标签]

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) }}  ) 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)

二、报表：亚马逊FBA库龄库存报告V2

自测：快照截止时间选择2022.12.2号

三、报表：亚马逊FBA库龄库存报告V3

自测：快照截止时间选择2022.12.2号

2022-12-02 15:45:13, 由肖雄记录工时，消耗 1 小时。
修改了 总计消耗，旧值为 "1"，新值为 "2"。
2022-12-02 15:49:49, 由肖雄确认需求变动，最新版本为#3。
2022-12-02 17:02:22, 由肖雄编辑。
修改了 任务描述，区别为：
001-
001+

001-
001+ <p><br /></p>

SVN：提交
2022-12-02 17:03:25, 由肖雄记录工时，消耗 3 小时。
修改了 总计消耗，旧值为 "2"，新值为 "5"。
修改了 预计剩余，旧值为 "3"，新值为 "0"。
修改了 任务状态，旧值为 "doing"，新值为 "done"。
修改了 指派给 ，旧值为 "xiaoxiong"，新值为 "xuruifeng"。
修改了 由谁完成，旧值为 ""，新值为 "xiaoxiong"。
修改了 实际完成，旧值为 ""，新值为 "2022-12-02 17:03:25"。
2022-12-28 09:14:39, 由 zhengqiao 关闭。
修改了 任务状态，旧值为 "done"，新值为 "closed"。
修改了 指派给 ，旧值为 "xuruifeng"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "zhengqiao"。
修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2022-12-28 09:14:39"。
修改了 关闭原因，旧值为 ""，新值为 "done"。

基本信息
任务的一生

所属迭代	技术研发中心 12月第1版本（2022年）
所属模块	/
相关需求	库龄库存报告增加标签属性值
指派给	Closed 于 2022-12-28 09:14:39
任务类型	后端
任务标签	初始
开发组	多平台开发组
任务状态	已关闭
进度	100%
优先级	P2
关键词
抄送给

由谁创建	许瑞丰于 2022-12-01 13:48:17
由谁完成	肖雄于 2022-12-02 17:03:25
由谁取消	暂无
由谁关闭	zhengqiao 于 2022-12-28 09:14:39
关闭原因	已完成
最后编辑	zhengqiao 于 2022-12-28 09:14:39

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

工时信息

最初预计	4工时
总计消耗	5工时
预计剩余	0工时
预计开始	2022-12-01
实际开始	2022-12-01
截止日期	2022-12-02