深圳智汇创想科技有限公司

请产品经理配置 脚本

操作步骤：

关键脚本：

  osa.UserFullPathName [账号首选业务人员一级部门],
  osa.UserFullPathName [账号首选业务人员二级部门],
  osa.UserFullPathName [账号首选业务人员三级部门],

SELECT
  w.WareHouseName [仓库],
  ia.OrderSourceName [渠道编码],
  os.SaleAdminName [账号首选业务人员],
  osa.EmpoyeeNumber [账号首选业务人员工号],
  osa.UserFullPathName [账号首选业务人员一级部门],
  osa.UserFullPathName [账号首选业务人员二级部门],
  osa.UserFullPathName [账号首选业务人员三级部门],
  (
    SELECT
      top 1 poss.BusinessAdminName
    FROM
      dbo.RB_WareHouse_Product wp (nolock)
      INNER JOIN dbo.RB_Product_OrderSourceSKU poss (nolock) on poss.CustomerID = wp.CustomerID
      and poss.OrderSourceID = wp.OrderSourceID
      and poss.OrderSourceSKU = wp.OrderSourceSKU
    where
      wp.CustomerID = ia.CustomerId
      and wp.OrderSourceID = ia.OrderSourceId
      and isnull(wp.FNSKU, '') <> ''
      and wp.FNSKU = ia.FNSKU
    order by
      wp.id desc
  ) [销售人员],
  (
    SELECT
      top 1 rba.EmpoyeeNumber
    FROM
      dbo.RB_WareHouse_Product wp (nolock)
      INNER JOIN dbo.RB_Product_OrderSourceSKU poss (nolock) on poss.CustomerID = wp.CustomerID
      and poss.OrderSourceID = wp.OrderSourceID
      and poss.OrderSourceSKU = wp.OrderSourceSKU
      LEFT JOIN dbo.RB_Admin rba (nolock) ON rba.ID = poss.BusinessAdminID
    where
      wp.CustomerID = ia.CustomerId
      and wp.OrderSourceID = ia.OrderSourceId
      and isnull(wp.FNSKU, '') <> ''
      and wp.FNSKU = ia.FNSKU
    order by
      wp.id desc
  ) [销售人员工号],
  (
    SELECT
      top 1 rbt.DeptFullPathName
    FROM
      dbo.RB_WareHouse_Product wp (nolock)
      INNER JOIN dbo.RB_Product_OrderSourceSKU poss (nolock) on poss.CustomerID = wp.CustomerID
      and poss.OrderSourceID = wp.OrderSourceID
      and poss.OrderSourceSKU = wp.OrderSourceSKU
      LEFT JOIN dbo.RB_Admin rba (nolock) ON rba.ID = poss.BusinessAdminID
      LEFT JOIN dbo.RB_BusinessTeam rbt (nolock) ON rbt.ID = rba.BusinessTeamID
    where
      wp.CustomerID = ia.CustomerId
      and wp.OrderSourceID = ia.OrderSourceId
      and isnull(wp.FNSKU, '') <> ''
      and wp.FNSKU = ia.FNSKU
    order by
      wp.id desc
  ) [销售人员所属部门],
  (
    SELECT
      top 1 tbtp.BusinessTeamName
    FROM
      dbo.RB_WareHouse_Product wp (nolock)
      INNER JOIN dbo.RB_Product_OrderSourceSKU poss (nolock) on poss.CustomerID = wp.CustomerID
      and poss.OrderSourceID = wp.OrderSourceID
      and poss.OrderSourceSKU = wp.OrderSourceSKU
      LEFT JOIN dbo.RB_Admin rba (nolock) ON rba.ID = poss.BusinessAdminID
      AND rba.CustomerID = poss.CustomerID
      LEFT JOIN dbo.RB_BusinessTeam rbt (nolock) ON rbt.ID = rba.BusinessTeamID
      AND rbt.CustomerID = rba.CustomerID
      LEFT JOIN dbo.RB_BusinessTeam tbtp (nolock) ON tbtp.ID = rbt.BusinessTeamParentID
      AND tbtp.CustomerID = rbt.CustomerID
    where
      wp.CustomerID = ia.CustomerId
      and wp.OrderSourceID = ia.OrderSourceId
      and isnull(wp.FNSKU, '') <> ''
      and wp.FNSKU = ia.FNSKU
    order by
      wp.id desc
  ) [上级销售小组],
  (
    SELECT
      top 1 rbt.BusinessTeamName
    FROM
      dbo.RB_WareHouse_Product wp (nolock)
      INNER JOIN dbo.RB_Product_OrderSourceSKU poss (nolock) on poss.CustomerID = wp.CustomerID
      and poss.OrderSourceID = wp.OrderSourceID
      and poss.OrderSourceSKU = wp.OrderSourceSKU
      LEFT JOIN dbo.RB_Admin rba (nolock) ON rba.ID = poss.BusinessAdminID
      LEFT JOIN dbo.RB_BusinessTeam rbt (nolock) ON rbt.ID = rba.BusinessTeamID
    where
      wp.CustomerID = ia.CustomerId
      and wp.OrderSourceID = ia.OrderSourceId
      and isnull(wp.FNSKU, '') <> ''
      and wp.FNSKU = ia.FNSKU
    order by
      wp.id desc
  ) [销售小组],
  ISNULL(pc1.ClassNameCn, '') [产品一级类别],
  ISNULL(pc2.ClassNameCn, '') [产品二级类别],
  ISNULL(pc3.ClassNameCn, '') [产品三级类别],
  ISNULL(pb.BrandNameCN, '无') [品牌],
  case
    ia.StorageModeId
    when 1 then '直发'
    when 2 then '中转箱产品'
    when 3 then '中转箱'
  end [库存类型],
  ia.ItemCode [商品编码],
  ia.FNSKU,
  ia.ProductSku [海外仓SKU],
  ia.RelationSKU [系统SKU],
  case
    when isnull(ia.FNSKU, '') = '' then ''
    else (
      SELECT
        top 1 wp.OrderSourceSKU
      FROM
        dbo.RB_WareHouse_Product wp (nolock)
      where
        wp.CustomerID = ia.CustomerId
        and wp.OrderSourceID = ia.OrderSourceId
        and isnull(wp.FNSKU, '') <> ''
        and wp.FNSKU = ia.FNSKU
    )
  end [渠道SKU],
  case
    when isnull(ia.FNSKU, '') = '' then ''
    else (
      SELECT
        top 1 wp.ASIN
      FROM
        dbo.RB_WareHouse_Product wp (nolock)
      where
        wp.CustomerID = ia.CustomerId
        and wp.OrderSourceID = ia.OrderSourceId
        and isnull(wp.FNSKU, '') <> ''
        and wp.FNSKU = ia.FNSKU
    )
  end [ASIN],
  case
    p.ProductProperty
    when 1 then '非仿牌'
    when 2 then '仿牌'
    when 3 then '灰色产品'
  end [侵权状态],
  ia.TotalQty [数量],
  p.ProductNameCN [产品中文名称],
  p.ProductName [产品英文名称],
  case
    when p.LastBuyPrice > 0 then p.LastBuyPrice
    else p.LastSupplierPrice
  end [最新采购价],
  (
    case
      when p.LastBuyPrice > 0 then p.LastBuyPrice
      else p.LastSupplierPrice
    end
  ) * ia.TotalQty [库存总额],
  ia.A30 [0-30天库龄数],
  ia.A60 [31-60天库龄数],
  ia.A90 [61-90天库龄数],
  ia.A180 [91-180天库龄数],
  ia.A360 [181-360天库龄数],
  ia.G360 [大于360天库龄数],
  ia.A30 * (
    case
      when p.LastBuyPrice > 0 then p.LastBuyPrice
      else p.LastSupplierPrice
    end
  ) [0-30天库存金额],
  ia.A60 * (
    case
      when p.LastBuyPrice > 0 then p.LastBuyPrice
      else p.LastSupplierPrice
    end
  ) [31-60天库存金额],
  ia.A90 * (
    case
      when p.LastBuyPrice > 0 then p.LastBuyPrice
      else p.LastSupplierPrice
    end
  ) [61-90天库存金额],
  ia.A180 * (
    case
      when p.LastBuyPrice > 0 then p.LastBuyPrice
      else p.LastSupplierPrice
    end
  ) [91-180天库存金额],
  ia.A360 * (
    case
      when p.LastBuyPrice > 0 then p.LastBuyPrice
      else p.LastSupplierPrice
    end
  ) [181-360天库存金额],
  ia.G360 * (
    case
      when p.LastBuyPrice > 0 then p.LastBuyPrice
      else p.LastSupplierPrice
    end
  ) [大于360天库存金额],
  convert(varchar(10), pa.OnlineTime, 120) [产品首次上架时间]
from
  (
    select
      oia.CustomerId,
      oia.WarehouseId,
      oia.OrderSourceId,
      oia.OrderSourceName,
      oia.StorageModeId,
      oia.ItemCode,
      oia.ProductSku,
      oia.RelationSKU,
      case
        when oia.MultiCodeType = 2 then oia.MultiCode
        else ''
      end FNSKU,
      convert(varchar(10), oia.InventoryAgeDate, 120) InventoryAgeDate,
      sum(oia.Number) TotalQty,
      sum(
        case
          when oia.Age between 0
          and 30 then oia.Number
          else 0
        end
      ) A30,
      sum(
        case
          when oia.Age between 31
          and 60 then oia.Number
          else 0
        end
      ) A60,
      sum(
        case
          when oia.Age between 61
          and 90 then oia.Number
          else 0
        end
      ) A90,
      SUM(
        CASE
          WHEN oia.Age BETWEEN 91
          AND 180 THEN oia.Number
          ELSE 0
        END
      ) A180,
      SUM(
        CASE
          WHEN oia.Age BETWEEN 181
          AND 360 THEN oia.Number
          ELSE 0
        END
      ) A360,
      SUM(
        CASE
          WHEN oia.Age > 360 THEN oia.Number
          ELSE 0
        END
      ) G360
    from
      RB_Oversea_InventoryAge oia (nolock)
    where
      oia.StorageModeId = 1
      and oia.CustomerId = @CustomerId
      and convert(varchar(10), oia.InventoryAgeDate, 120) = convert(varchar(10), @day, 120)
      and {{ oia.WarehouseId in (@WarehouseId) }}
      and {{ oia.StorageModeId = @StorageModeId }}
    group by
      oia.CustomerId,
      oia.WarehouseId,
      oia.OrderSourceId,
      oia.OrderSourceName,
      oia.StorageModeId,
      oia.ItemCode,
      oia.ProductSku,
      oia.RelationSKU,
      case
        when oia.MultiCodeType = 2 then oia.MultiCode
        else ''
      end,
      convert(varchar(10), oia.InventoryAgeDate, 120)
    union all
    select
      oia.CustomerId,
      oia.WarehouseId,
      oia.OrderSourceId,
      oia.OrderSourceName,
      oia.StorageModeId,
      oia.ItemCode,
      oiad.ProductSku,
      oiad.RelationSKU,
      oiad.MultiCode FNSKU,
      convert(varchar(10), oia.InventoryAgeDate, 120) InventoryAgeDate,
      sum(oia.Number * oiad.ProductNumber) TotalQty,
      sum(
        case
          when oia.Age between 0
          and 30 then oia.Number * oiad.ProductNumber
          else 0
        end
      ) A30,
      sum(
        case
          when oia.Age between 31
          and 60 then oia.Number * oiad.ProductNumber
          else 0
        end
      ) A60,
      sum(
        case
          when oia.Age between 61
          and 90 then oia.Number * oiad.ProductNumber
          else 0
        end
      ) A90,
      SUM(
        CASE
          WHEN oia.Age BETWEEN 91
          AND 180 THEN oia.Number * oiad.ProductNumber
          ELSE 0
        END
      ) A180,
      SUM(
        CASE
          WHEN oia.Age BETWEEN 181
          AND 360 THEN oia.Number * oiad.ProductNumber
          ELSE 0
        END
      ) A360,
      SUM(
        CASE
          WHEN oia.Age > 360 THEN oia.Number * oiad.ProductNumber
          ELSE 0
        END
      ) G360
    from
      RB_Oversea_InventoryAge oia (nolock)
      INNER JOIN dbo.RB_Oversea_InventoryAgeDetail oiad (nolock) on oiad.CustomerId = oia.CustomerId
      and oiad.InventoryAgeId = oia.InventoryAgeId
    where
      oia.StorageModeId <> 1
      and oia.CustomerId = @CustomerId
      and convert(varchar(10), oia.InventoryAgeDate, 120) = convert(varchar(10), @day, 120)
      and {{ oia.WarehouseId in (@WarehouseId) }}
      and {{ oia.StorageModeId = @StorageModeId }}
    group by
      oia.CustomerId,
      oia.WarehouseId,
      oia.OrderSourceId,
      oia.OrderSourceName,
      oia.StorageModeId,
      oia.ItemCode,
      oiad.ProductSku,
      oiad.RelationSKU,
      oiad.MultiCode,
      convert(varchar(10), oia.InventoryAgeDate, 120)
  ) ia
  INNER JOIN RB_Warehouse w (nolock) on w.CustomerID = ia.CustomerId
  and ia.WarehouseId = w.ID
  LEFT JOIN dbo.RB_OrderSource os (nolock) on os.CustomerID = ia.CustomerId
  and ia.OrderSourceId = os.Id
  LEFT JOIN RB_Product p (nolock) on p.CustomerID = ia.CustomerId
  and p.SKU = ia.RelationSKU
  LEFT JOIN dbo.RB_Product_Class pc1 (nolock) ON pc1.CustomerID = p.CustomerID
  AND pc1.ID = p.ClassID1
  LEFT JOIN dbo.RB_Product_Class pc2 (nolock) ON pc2.CustomerID = p.CustomerID
  AND pc2.ID = p.ClassID2
  LEFT JOIN dbo.RB_Product_Class pc3 (nolock) ON pc3.CustomerID = p.CustomerID
  AND pc3.ID = p.ClassID
  LEFT JOIN dbo.RB_Product_Brand pb (nolock) ON pb.CustomerID = p.CustomerID
  AND p.BrandID = pb.ID
  LEFT JOIN dbo.RB_Product_Admin pa (NOLOCK) ON pa.CustomerID = p.CustomerID
  AND pa.SKU = p.SKU
  LEFT JOIN dbo.RB_Admin osa(NOLOCK) ON os.CustomerID = osa.CustomerID
  AND os.SaleAdminID = osa.ID

http://pm.zhcxkj.com/zentao/story-view-10662.html
这个需求我理解的影响范围如下
1. 报表名称=赛盒wms库存库龄报告
1.1 导出excel（不知道如何操作）
1.2 导出csv
1.3 预约导出（不知道如何操作）
2. 报表名称!=赛盒wms库存库龄报告
3. 账号首选业务人员部门取值
例如：
账号首选业务人员部门：全公司 > 亚马逊平台运营中心 > 亚马逊销售E部 > E3组
账号首选业务人员一级部门：亚马逊平台运营中心
账号首选业务人员二级部门：亚马逊销售E部
账号首选业务人员三级部门：E3组