10.智能货架数据

本页面记录各个客户的智能货架数据
智能货架亮灯日志

查询语句

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT a.[ip]
      ,(SELECT COUNT(*) from [Udit.Mms].[dbo].[mms_aibins] where [ip]=a.[ip]) as 储位数量
      ,[name] 货架名称
      ,[machine_type] self苏州zxbt工厂自有dwrc惠州睿诚
      ,b.bin 货架编号
      ,c.WarehouseCode 储位
  FROM [Udit.Mms].[dbo].[mms_aibininfo] a
  join [Udit.Mms].[dbo].[mms_aibins] b on a.ip=b.ip
  join [Udit.Mms].[dbo].[mms_warehouse] c on c.WarehouseCode=b.bin 
  where [lightindex]=1
  order by b.bin

查询更多

SELECT a.[ip]
      ,(SELECT COUNT(*) from [Udit.Mms].[dbo].[mms_aibins] where [ip]=a.[ip]) as 储位数量
      ,a.[warehouse_code]
      , (SELECT [WarehouseName]  FROM [Udit.Mms].[dbo].[mms_warehouse] where [WarehouseCode]=a.[warehouse_code]) 货架仓库
      ,[name] 货架名称
      ,[machine_type] self苏州zxbt工厂自有dwrc惠州睿诚
      ,b.bin 货架编号
      ,c.WarehouseCode 储位
      ,(SELECT [WarehouseName]  FROM [Udit.Mms].[dbo].[mms_warehouse] where [WarehouseCode]=c.[ParentCode]) 储位仓库
      --,c.[FParentCode]
  FROM [Udit.Mms].[dbo].[mms_aibininfo] a
  left join [Udit.Mms].[dbo].[mms_aibins] b on a.ip=b.ip
  left join [Udit.Mms].[dbo].[mms_warehouse] c on c.WarehouseCode=b.bin 
  where [lightindex]=1
  order by b.bin

货架亮灯高级查询

SELECT  a.[BillNo]
      ,a.[ContractCode]
      ,b.[WarehouseCode] 仓库编码
      ,w.[WarehouseName] 备料仓名
      ,b.[RowId] 备料项次
      ,(b.[Num]-b.[RemainNum]) 已发数
      ,b.[Num] 需求数
      ,b.[MaterialCode] 备料单料号
      ,c.[REEL_BARCODE] 有条码UID
      ,c.[LOCATION] UID储位
      ,color.[name] 备料单颜色
      ,color.[color] 备料单颜色
      ,d.[color] 货架亮灯颜色
      ,(select top 1 name from [Udit.Mms].[dbo].[mms_aibin_lightcolor] where [color]=d.[color]) 货架亮灯颜色
  FROM [Udit.Mms].[dbo].[mms_pick] a
  left join [Udit.Mms].[dbo].[mms_aibin_lightcolor] color on color.[pick_id]=a.[BillNo]
  left join [Udit.Mms].[dbo].[mms_pickDetail] b on b.[BillNo]=a.[BillNo]
  left join [Udit.Mms].[dbo].[mms_REELS] c on c.[MATERIAL_NO]=b.[MaterialCode] and c.STATUS=1 and c.[WAREHOUSE]=b.[WarehouseCode]
  left join [Udit.Mms].[dbo].[mms_aibins] d on d.[bin]=c.[LOCATION]
  left join [Udit.Mms].[dbo].[mms_warehouse] w on w.[WarehouseCode]=b.[WarehouseCode]
  where [ContractCode] like '%PI2022120513451600411'
  and  b.[RemainNum] >0 --只看没有发的物料
  order by b.[RowId] asc