Page History

-- SP

IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_AS_UnlinkItemFromWH') AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE _AS_UnlinkItemFromWH


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Asamco BV, Alexander Toufexis
-- Create date: 07/05/2018
-- Description:	Remove item from WH
-- =============================================
Create Procedure [dbo].[_AS_UnlinkItemFromWH]
@StockCode Varchar(100),
@WHCode Varchar(100)
AS
BEGIN

Declare @LogMessage as Varchar(1024)
  
	
	--1) Get basic information
	Declare @stockLink int
	Declare @Group varchar(100)
	
	Select @stockLink =  (Select stocklink from stkitem where Code = @StockCode)
	IF (COALESCE(@stockLink,0) = 0)
		BEGIN
		Select @LogMessage = 'Stock code does not exist, aborting ('+@StockCode+')'
		print @LogMessage
		return
	END  

	--2) Get WH ID.
	DECLARE @WhseLink int
	SELECT @WhseLink = (Select WhseLink from dbo.WhseMst WHERE WhseMst.Code = @WHCode)
	IF (COALESCE(@WhseLink,0) = 0) --early out, if WH doesn't exist
		BEGIN
			Select @LogMessage = 'WH code does not exist, aborting ('+@stockLink@WHCode+')'
			print @LogMessage
			return
		END 

			
	--3) Check if stock item has balances in WH
	DECLARE @QtyInWH float
	SELECT @QtyInWH = (SELECT  ABS([WHQtyOnHand]) + ABS([WHQtyOnSO]) + ABS([WHQtyOnPO]) + ABS([WHQtyReserved]) FROM WhseStk W WHERE W.WHStockLink = @stockLink AND W.[WHWhseID] = @WhseLink)
	IF (@QtyInWH > 0) --early out. Note qty is handled using Absolutes, so qty check works.
		BEGIN
			Select @LogMessage = 'Stock Item ('+@StockCode+') has Qtys in the WH ('+@WHCode+') (on hand, or on PO, or on SO) aborting ('+@stockLink+')'
			print @LogMessage
			return
		END

	--4) Remove record from WhseStk
	DECLARE @idWHStk bigint
	SELECT @idWHStk = COALESCE((SELECT IdWhseStk FROM WhseStk WHERE WhseStk.[WHWhseID] = @WhseLink AND WhseStk.WHStockLink = @stockLink),0)
	IF (@idWHStk > 0)
		BEGIN
			DELETE FROM WhseStk WHERE WhseStk.[WHWhseID] = @WhseLink AND WhseStk.WHStockLink = @stockLink
			Select @LogMessage = 'SUCCESS: Stock Item ('+@StockCode+') was deleted from WH ('+@WHCode+')'
			print @LogMessage
		END
	ELSE
		BEGIN
			Select @LogMessage = 'INFO: Stock Item ('+@StockCode+') was not found in WH ('+@WHCode+')'
			print @LogMessage
		END



END