WMS补货逻辑--严格按照先进先出补货

才华有限青年_

已于 2024-04-26 16:48:52 修改

阅读量2.8k

点赞数

文章标签：安全

于 2018-08-30 10:26:50 首次发布

本文链接：https://blog.csdn.net/yx183/article/details/82217140

版权

仓库补货是手动补货，旧版补货是提供所有储存库位给仓库选择，仓库没有按照效期先后顺序进行补货，导致后入库的产品先出库，现在补货任务只提供最优批次，具体实现方法如下：

1、补货核实存储过程：SPUDF_ReplelishentLists3

USE [wmsa]
GO

/****** Object:  Table [dbo].[TMP_WTD_BuHuo3]    Script Date: 08/30/2018 10:20:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TMP_WTD_BuHuo3](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[warehouseid] [varchar](20) NULL,
	[CustomerID] [varchar](20) NULL,
	[sku] [varchar](20) NULL,
	[Qty1] [int] NULL,
	[Qty2] [int] NULL,
	[LotNum] [varchar](20) NULL,
	[LocationID] [varchar](20) NULL,
	[LotAtt02] [varchar](20) NULL,
	[LotAtt03] [varchar](20) NULL,
	[LotAtt08] [varchar](20) NULL,
	[AddWho] [varchar](10) NULL,
	[AddTime] [datetime] NULL,
	[Notes] [varchar](2000) NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [wmsa]
GO
/****** Object:  StoredProcedure [dbo].[SPUDF_ReplelishentLists3]    Script Date: 08/30/2018 10:18:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SPUDF_ReplelishentLists3]
@ReturnCode varchar(1000) output
as

declare @Warehouseid varchar(20),
		@CustomerID varchar(20),
		@SKU  varchar(20),
		@QtyOrdered_Each int,
		@QtyAllocated_Each int,
		@qty int,
		@rows int = 0,
		@LotNum varchar(20),
		@LocationID varchar(20),
		@QtyAvailed int,
		@LotAtt02 varchar(20),
		@LotAtt03 varchar(20),
		@LotAtt08 varchar(20),
		@qty_zy int,
		@qty2 int=0

declare cur_BuHuoList cursor
for
select h.Warehouseid,h.CustomerID,d.SKU,d.LotAtt08,
	   SUM(d.QtyOrdered_Each) as QtyOrdered_Each,SUM(d.QtyAllocated_Each) as QtyAllocated_Each  
from  DOC_Order_details(nolock) d
left join DOC_Order_Header(nolock) h on d.orderno = h.OrderNo and d.customerid = h.CustomerID
where 1 = 1
and h.ordertime between dateadd(d,-30,getdate()) and getdate() 
and h.Warehouseid in('BB1','FLC','HJC','HKBT')
and h.CustomerID <> 'WS'
and h.SOStatus < 40
and d.QtyOrdered_Each > d.QtyAllocated_Each
--and h.ordertype <>17
group by h.Warehouseid,h.CustomerID,d.SKU,d.LotAtt08
order by h.Warehouseid,h.CustomerID,d.SKU,d.LotAtt08


delete  from TMP_WTD_BuHuo3

open cur_BuHuoList

Fetch Next From cur_BuHuoList Into @warehouseid,@customerid,@sku,@LotAtt08,@QtyOrdered_Each,@QtyAllocated_Each

While @@FETCH_STATUS=0
	Begin
	
    set @qty = @QtyOrdered_Each - @QtyAllocated_Each
    
    select @rows = COUNT(1) from V_SelectINV_LOT_LOC_ID k
	where WarehouseID = @Warehouseid
	and CustomerID = @CustomerID
	and SKU = @SKU
	and LotAtt08 = @LotAtt08
	and QtyAvailed > 0
	and isnull(k.LocationID,'') not like 'SORTATION%'
	and isnull(k.LocationID,'') not like 'STAGE%'
	and isnull(k.LocationID,'') not like '%LOST%'
	and isnull(k.LocationID,'') not like 'DYNAMIC%'
	
	if(@rows <= 0)
		begin
			insert into TMP_WTD_BuHuo3(warehouseid, CustomerID, sku, Qty1,Qty2, LotNum, LocationID, LotAtt02, LotAtt03, LotAtt08, AddWho, AddTime, Notes)
			values(@warehouseid,@CustomerID,@sku,@qty,0,'','','','',@LotAtt08,'system',GETDATE(),'缺货')
		end
	else
	begin
	
	    --20180522 YANGXUN 增加食品批次属性和标准批次属性区分，标准属性不考虑 LotAtt02 字段
		declare @LotID1 varchar(20)
		select @LotID1 = LotID  from BAS_SKU where CustomerID=@CustomerID and SKU=@SKU
		
		if(isnull(@LotID1,'') = 'FOOD')	
		begin	
			declare cur_stagelocList cursor
			for
			select LotNum,k.LocationID,QtyAvailed,LotAtt02,LotAtt03,LotAtt08 
			from V_SelectINV_LOT_LOC_ID k
			left join V_selectLocation l on k.WarehouseID = l.WarehouseID and k.LocationID = l.LocationID
			where k.WarehouseID = @Warehouseid
			and CustomerID = @CustomerID
			and SKU = @SKU
			and k.LotAtt08 = @LotAtt08
			and l.LocationUsage = 'RS'
			and QtyAvailed > 0
			and isnull(k.LocationID,'') not like 'SORTATION%'
			and isnull(k.LocationID,'') not like 'STAGE%'
			and isnull(k.LocationID,'') not like '%LOST%'
			and isnull(k.LocationID,'') not like 'DYNAMIC%'
			order by LotAtt02,LotAtt03,LotAtt08
		end
		else if(isnull(@LotID1,'') = 'STANDARD')
		begin		
			declare cur_stagelocList cursor
			for
			select LotNum,k.LocationID,QtyAvailed,LotAtt02,LotAtt03,LotAtt08 
			from V_SelectINV_LOT_LOC_ID k
			left join V_selectLocation l on k.WarehouseID = l.WarehouseID and k.LocationID = l.LocationID
			where k.WarehouseID = @Warehouseid
			and CustomerID = @CustomerID
			and SKU = @SKU
			and k.LotAtt08 = @LotAtt08
			and l.LocationUsage = 'RS'
			and QtyAvailed > 0
			and isnull(k.LocationID,'') not like 'SORTATION%'
			and isnull(k.LocationID,'') not like 'STAGE%'
			and isnull(k.LocationID,'') not like '%LOST%'
			and isnull(k.LocationID,'') not like 'DYNAMIC%'
			order by LotAtt03,LotAtt08 
		end
		
		open cur_stagelocList

		Fetch Next From cur_stagelocList Into @LotNum,@LocationID,@QtyAvailed,@LotAtt02,@LotAtt03,@LotAtt08

		While @@FETCH_STATUS=0
			Begin
				
				--20180522  YANGXUN  增加临近效期产品提示,提示距离效期还有多少天
				declare @LotID2 varchar(20),@OutboundLifeDays varchar(20)
				select @LotID2 = LotID,@OutboundLifeDays = isnull(OutboundLifeDays,0) 
				from BAS_SKU where CustomerID=@CustomerID and SKU=@SKU
				
				if (@LotID2='FOOD' AND (DATEDIFF(D,GETDATE(),@LotAtt02) < case @OutboundLifeDays when 0 then 60 else @OutboundLifeDays end))
					begin
						insert into TMP_WTD_BuHuo3(warehouseid, CustomerID, sku, Qty1,Qty2, LotNum, LocationID, LotAtt02, LotAtt03, LotAtt08, AddWho, AddTime, Notes)
						values(@warehouseid,@CustomerID,@sku,@qty,@QtyAvailed,@LotNum,@LocationID,@LotAtt02,@LotAtt03,@LotAtt08,'system',GETDATE(),
						'有效期还剩下 '+cast((DATEDIFF(D,GETDATE(),@LotAtt02)) as varchar)+' 天！') 
					end
				else
					begin
						insert into TMP_WTD_BuHuo3(warehouseid, CustomerID, sku, Qty1,Qty2, LotNum, LocationID, LotAtt02, LotAtt03, LotAtt08, AddWho, AddTime, Notes)
						values(@warehouseid,@CustomerID,@sku,@qty,@QtyAvailed,@LotNum,@LocationID,@LotAtt02,@LotAtt03,@LotAtt08,'system',GETDATE(),'待补货')
					end
					
		         select @qty2 = SUM(Qty2) from TMP_WTD_BuHuo3 where warehouseid = @Warehouseid and CustomerID =@CustomerID and sku = @SKU
		         
		         if(@qty <= @qty2)
		         begin
					Fetch Next From cur_stagelocList Into @LotNum,@LocationID,@QtyAvailed,@LotAtt02,@LotAtt03,@LotAtt08
					break
		         end
		         
				Fetch Next From cur_stagelocList Into @LotNum,@LocationID,@QtyAvailed,@LotAtt02,@LotAtt03,@LotAtt08
			end
			
	    Close cur_stagelocList
        DeAllocate cur_stagelocList
	end
	
	Fetch Next From cur_BuHuoList Into @warehouseid,@customerid,@sku,@LotAtt08,@QtyOrdered_Each,@QtyAllocated_Each
	end

Close cur_BuHuoList
DeAllocate cur_BuHuoList

2、显示补货信息报表：SP_REPORTUDF_REPLENISHMENT

USE [wmsa]
GO

/****** Object:  Table [dbo].[TMP_SP_REPORTUDF_REPLENISHMENT]    Script Date: 08/30/2018 10:24:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TMP_SP_REPORTUDF_REPLENISHMENT](
	[仓库] [varchar](20) NULL,
	[客户] [varchar](20) NULL,
	[商品条码] [varchar](20) NULL,
	[订单数量] [int] NULL,
	[库存数量] [int] NULL,
	[存储库位] [varchar](20) NULL,
	[件拣货库位] [varchar](20) NOT NULL,
	[失效日期] [varchar](20) NULL,
	[入库日期] [varchar](20) NULL,
	[库存状态] [varchar](20) NULL,
	[备注] [varchar](2000) NULL,
	[tmpid] [varchar](30) NULL,
	[商品名称] [varchar](500) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [wmsa]
GO
/****** Object:  StoredProcedure [dbo].[SP_REPORTUDF_REPLENISHMENT]    Script Date: 08/30/2018 10:23:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		YANGXUN
-- Create date: 2018-03-28
-- Description:	根据发先进先出查询出需要补货的信息
-- =============================================
ALTER PROCEDURE [dbo].[SP_REPORTUDF_REPLENISHMENT]
	
@WareHouseID_R	Varchar(35),

@WareHouseID varchar(35),
@Language Char(1),
@UserID  Varchar(35),
@Return_Code Varchar(1000) output
AS
BEGIN
	
	IF @WareHouseID not in(select CustomerID from BAS_Customer where Customer_Type='WH')
	begin
		Set @Return_Code='999输入仓库代码不存在！！！请重新输入！！！'
		return
	end

    Delete from TMP_SP_REPORTUDF_REPLENISHMENT Where tmpID=@UserID 

	exec SPUDF_ReplelishentLists3 ''
	
	INSERT INTO [wmsa].[dbo].[TMP_SP_REPORTUDF_REPLENISHMENT]
           ([仓库]
           ,[客户]
           ,[商品条码]
           ,[商品名称]
           ,[订单数量]
           ,[库存数量]
           ,[存储库位]
           ,[件拣货库位]
           ,[失效日期]
           ,[入库日期]
           ,[库存状态]
           ,[备注]
           ,[tmpid])
           
select t3.warehouseid 仓库,
	   t3.CustomerID 客户, 
	   t3.SKU 商品条码,
	   bs.Descr_C 商品名称,
	   t3.qty1 订单数量, 
	   t3.qty2 库存数量,
	   t3.LocationID 存储库位, 
	   ISNULL(
				(select  top 1  t1.LocationID 
				 from  INV_LOT_LOC_ID  t1  left join  BAS_Location  t2  on  t1.LocationID=t2.LocationID
				 WHERE  t2.LocationUsage='EA' and  t1.SKU=t3.sku and t1.CustomerID=t3.CustomerID 				 
					and isnull(t2.LocationID,'') not like 'SORTATION%'
					and isnull(t2.LocationID,'') not like 'STAGE%'
					and isnull(t2.LocationID,'') not like '%LOST%'
					and isnull(t2.LocationID,'') not like 'DYNAMIC%'
				order by  t1.Qty
		     ),'自行确认库位') 件拣货库位,
		t3.LotAtt02 失效日期,
		t3.LotAtt03 入库日期,
		bc.CodeName_C 库存状态,
		t3.Notes 备注,
		@UserID

 from TMP_WTD_BuHuo3 t3 left join BAS_Codes bc on bc.Code=t3.LotAtt08 and bc.CodeID='DMG_FLG'
 left join BAS_SKU bs on bs.CustomerID=t3.CustomerID and bs.SKU=t3.SKU
 where t3.warehouseid=@WareHouseID


	set nocount on
	set @Return_Code='000'
	return
end

才华有限青年_

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
WMS补货逻辑--严格按照先进先出补货

仓库补货是手动补货，旧版补货是提供所有储存库位给仓库选择，仓库没有按照效期先后顺序进行补货，导致后入库的产品先出库，现在补货任务只提供最优批次，具体实现方法如下：1、补货核实存储过程：SPUDF_ReplelishentLists3USE [wmsa]GO/****** Object: Table [dbo].[TMP_WTD_BuHuo3] Script Date: 08...
复制链接

扫一扫